Database DB_UNIQUE_NAME Oracle Net Service Name
Primary chicago chicago(动态)10.19.100.191Physical standby boston boston(动态)10.19.100.192一、物理备库（Physical standby）
1. 确认主库处在归档模式，并开启强制日志
SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 1Next log sequence to archive 2Current log sequence 2 SQL> alter database force logging;
2. 为主库添加standby redo
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog1.rdo') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog2.rdo') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog3.rdo') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/chicago/slog4.rdo') SIZE 50M; 3. 主库配置
无需修改的默认参数：
DB_NAME=chicagoDB_UNIQUE_NAME=chicagoCONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arc
需要调整的参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' scope=both;alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' scope=both;alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' scope=both;alter system set FAL_SERVER=boston scope=both;alter system set DB_FILE_NAME_CONVERT='boston','chicago' scope=spfile;alter system set LOG_FILE_NAME_CONVERT='/boston/','/chicago/' scope=spfile;alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
重启主库以便生效
4. 以主库的配置文件为模板，修改成备库的配置文件
CREATE PFILE='/u01/initboston.ora' FROM SPFILE;
并修改,修改部分用红色标出
chicago.__db_cache_size=448790528chicago.__java_pool_size=4194304chicago.__large_pool_size=8388608chicago.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentchicago.__pga_aggregate_target=209715200chicago.__sga_target=629145600chicago.__shared_io_pool_size=0chicago.__shared_pool_size=159383552chicago.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/boston/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/u01/oradata/boston/control01.ctl','/u01/oraflash/boston/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_file_name_convert='chicago','boston'*.db_name='chicago'*.db_unique_name=boston*.db_recovery_file_dest='/u01/oraflash'*.db_recovery_file_dest_size=4385144832*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=chicagoXDB)'*.fal_server=chicago*.log_archive_config='DG_CONFIG=(chicago,boston)'*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'*.log_archive_dest_2='SERVICE=chicago ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'*.log_archive_format='%t_%s_%r.dbf'*.log_file_name_convert='/chicago/','/boston/'*.open_cursors=300*.pga_aggregate_target=209715200*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=629145600*.standby_file_management='AUTO'*.undo_tablespace='UNDOTBS1'
5. 复制参数文件密码文件到备库上,并创建必要路径
scp initboston.ora 10.19.100.192:/$ORACLE_HOME/dbs/scp $ORACLE_HOME/dbs/orapwchicago 10.19.100.192:/$ORACLE_HOME/dbs/orapwbostonmkdir -p /u01/app/oracle/admin/boston/adumpmkdir -p /u01/oraflash/boston/mkdir -p /u01/oradata/bostonmkdir -p /u01/app/oracle/diag/rdbms/boston/boston/trace/
启动备库到nomount并创建配置文件：
startup nomount pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initboston.ora'create spfile from pfile;shutdown immediate;startup nomount
6. 备份主库并用备份恢复备库
主库rman target /backup format '/u01/backup/controlfile_%U' current controlfile for standby;backup format '/u01/backup/db_%U' database plus archivelog;scp -r /u01/backup 10.19.100.192:/u01/
备库
rman target sys/123456@chicago auxiliary /duplicate target database for standby nofilenamecheck;
开启备库到只读
alter database open read only;
修改备库为恢复管理模式，使备库可以应用主库的redo数据，实现同步：
alter database recover managed standby database disconnect from session;或alter database recover managed standby database using current logfile disconnect from session;
7. 检查复制情况
在主库手工切换日志alter system archive log current;
备库查看日志应用情况
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;SEQUENCE# FIRST_TIM NEXT_TIME APPLIED---------- --------- --------- ---------19 14-JAN-17 14-JAN-17 YES20 14-JAN-17 14-JAN-17 YES21 14-JAN-17 14-JAN-17 YES
检查备库DG进程(主要是RFS和MRPn是否存在)
select process,status from v$managed_standby;PROCESS STATUS--------- ------------ARCH CLOSINGARCH CONNECTEDARCH CONNECTEDARCH CONNECTEDRFS IDLERFS IDLERFS IDLERFS IDLEMRP0 WAIT_FOR_LOG
查看日志gap
select * from v$archive_gap;no rows selected
如果存在日志断点，就要到主库中找到这些日志：
select sequence#,name from v$archived_log t1,v$archive_gap t2 where t1.thread#=t2.thread#;
找到这些日志文件之后，将这些文件拷贝到备库，并注册到备库进行应用：
alter database register logfile 'xxx';
检查是否有错误
主库上执行select error from v$archive_dest where target='STANDBY';no rows selected
检查standby模式
select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL---------------- -------------------- --------------------PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
在主库写入一条数据并切换日志
insert into t1 values (999);commit;alter system archive log current;
备库
select * from t1;COL1----------1213999 <----
停止复制并提升备库
alter database recover managed standby database cancel;shutdown immediatestartup
注意 这里alter database recover managed standby database cancel;只是停止了MRP进程，如果再次打开想恢复同步
只要执行 alter database recover managed standby database disconnect from session;或alter database recover managed standby database using current logfile disconnect from session;就好查看lag
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIMEfrom V$DATAGUARD_STATS WHERE NAME LIKE '%lag';
二、逻辑备库（Logical standby）
逻辑standby是由物理standby转换来的，也是和主库差1个redo，但是是以SQL的方式进行复制
1. 配置好物理standby并检查DG工作正常
主库
SQL> select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL---------------- -------------------- --------------------PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
备库
SQL> select database_role,protection_mode,protection_level from v$database;DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL---------------- -------------------- --------------------PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
检查Primary是否有不支持Logical Standby的对象
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;no rows selected
检查无索引或无主键表
SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUEWHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y';
如果有这些表，但你又不希望为其创建主键或唯一键则可以这么干(性能上考虑)
ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;
2. 停止Physical Standby上的Redo apply(即停掉MRP进程)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. 修改主库以支持Logical Standby
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago';alter system set LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=chicago';alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE;
4. 主库生成logminer字典
EXECUTE DBMS_LOGSTDBY.BUILD;ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
5. 把备库从Physical Standby转换为Logical Standby
ALTER DATABASE RECOVER TO LOGICAL STANDBY boston; -- db_name必须和主库不同，此操作会自动修改spfile
如果切不过去请注意主库和备库的archive log的差距是否>1 如果>1 需要再切回去,同步到只差1为止
6. 修改备库参数
SHUTDOWN;STARTUP MOUNT;alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston';alter system set LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=boston';alter system set LOG_ARCHIVE_DEST_STATE_1=enable;alter system set LOG_ARCHIVE_DEST_STATE_2=enable;alter system set LOG_ARCHIVE_DEST_STATE_3=enable;ALTER DATABASE OPEN RESETLOGS
7. 重新应用redo
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;select name,open_mode,database_role,protection_mode from v$database;SQL> select name,open_mode,database_role,protection_mode from v$database;NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE--------- -------------------- ---------------- --------------------BOSTON READ WRITE LOGICAL STANDBY MAXIMUM PERFORMANCE
检查redo应用情况
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED---------- ------------- ------------ --------- --------114 1087604 1088417 17-JAN-17 YES115 1088417 1088431 17-JAN-17 YES116 1088431 1088563 17-JAN-17 YES117 1088563 1088915 17-JAN-17 YES118 1088915 1089127 17-JAN-17 YES119 1089127 1090316 17-JAN-17 YES120 1090316 1091045 17-JAN-17 YES
注意，logical standby是没有MRP进程的,因为是靠sql的方式提供备份
select process,status from v$managed_standby;PROCESS STATUS--------- ------------ARCH CLOSINGARCH CLOSINGARCH CONNECTEDARCH CLOSINGRFS IDLERFS IDLERFS IDLERFS IDLE
向主库t1表写入数据做测试(注意，LogicalStandby不能用sys用户的表，但是PhysicalStandby是可以的)
SQL> insert into scott.dept values(1,'A','B');1 row created.SQL> commit;Commit complete.SQL> alter system archive log current;
查询备库
SQL> select * from scott.dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON1 A B
SELECT DEST_ID,STATUS,DESTINATION,ERROR FROM V$ARCHIVE_DEST;
查看lag
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIMEfrom V$DATAGUARD_STATS WHERE NAME LIKE '%lag';