DG备库上做备库，并延迟应用日志
有时候备库不需要查询实时数据，如OLAP报表分析，又比如防止主库有误操作后找回原来的数据，故意让备库延迟几小时应用主库日志。
如何让备库延迟应用主库日志呢？
方法1: 在备库应用主库日志的语句中指定delay属性
如alter database recover managed standby database delay 120 disconnect from session; --备库延迟120分钟应用主库日志
方法2: log_ archive_dest_n参数中指定了delay属性
alter system set log_archive_dest_3='service=db3 lgwr async delay=120 valid_for=(all_logfiles,all_roles) db_unique_name=db3';
注意：delay属性并不是说延迟发送主库日志到备库，而是指日志到备库后，延迟多长时间应用主库日志。
但是，如果在备库应用主库日志的语句中指定了实时应用，也就是使用了using current logfile，如alter database recover managed standby database using current logfile disconnect from session;
那么，即使在log_ archive_dest_n参数中指定了delay属性，备库也会忽略delay属性。
另外，备库还可以在启动redo应用时，通过附加nodelay子句的方式，取消延迟应用主库日志，如alter database recover managed standby database disconnect from session nodelay;
下面我们通过方法1，做一个复杂点的DG实验，如下图，主库的备库是实时应用主库日志，备库的备库延迟应用日志。
本实验我分了两个步骤，第一步建立主库的备库，第二步建立备库的备库。
一．建立主库的备库
主库ip及主机名：192.168.1.68 db
主库的备库ip及主机名：192.168.1.69
db21.建立主备库的归档目录
[root@db ~]# su -
oracle[oracle@db ~]$ cd /u01/
[oracle@db ~]$ mkdir arclog
2.主库改为强制日志模式:
[oracle@db ~]$ sqlplus / as sysdba
SQL> alter database force logging;
3.主库建立备库的文本参数文件
SQL> create pfile from spfile;
4.rman备份主库
[oracle@db ~]$ cd /u01
[oracle@db u01]$ mkdir rman
[oracle@db ~]$ rman target /
RMAN> backup database format '/u01/rman/db_%d_%s_%p_%u_%T.dbf';
RMAN> sql 'alter system archive log current';
RMAN> backup archivelog all format '/u01/rman/arc_%d_%s_%p_%u_%T.arc'; --不要加delete all input，危险！;
RMAN> exit
5.主库建立备库的控制文件
[oracle@db ~]$ sqlplus / as sysdba
SQL> alter database create standby controlfile as '/u01/standby.ctl';
SQL> exit
6.把主库的rman备份、主库密码文件、文本参数文件、standby.ctl复制到备库相应位置
①主库的rman备份
[oracle@db ~]$ scp -r /u01/rman/* 192.168.1.69:/u01/rman/
②主库密码和文本参数文件
[oracle@db ~]$ cd /u01/oracle/orcl/dbs/
[oracle@db ~]$ scp orapworcl initorcl.ora 192.168.1.69:/u01/oracle/orcl/dbs/
③主库上建立的备库控制文件standby.ctl
[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.69:/u01/oracle/oradata/orcl/control01.ctl
[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.69:/u01/oracle/flash_recovery_area/orcl/control02.ctl
7.备库文本参数文件修改
[oracle@db2 orcl]$ cd $ORACLE_HOME/dbs
[oracle@db2 dbs]$ vi initorcl.ora
添加下面内容:
db_unique_name=db2
log_archive_config='dg_config=(orcl,db2)'
log_archive_dest_1='location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=db2'
log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
log_archive_max_processes=6
fal_server='orcl'
fal_client='db2'
standby_file_management=auto
原归档路径注释掉
8.备库建立新的spfile
[oracle@db2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@db2 dbs]$ rm -rf spfileorcl.ora
[oracle@db2 dbs]$ sqlplus / as sysdba
SQL> create spfile from pfile;
9.主备库配置监听、tnsnames文件
注意：tnsnames中也要设置访问自己的配置，否则broker的failover时不成功。
非RAC下，建议配置监听的静态注册。
主备库tnsnames.ora配置如下：
vi $ORACLE_HOME/network/admin/tnsnames.ora
DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.68)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
10.分别在主备库上重启或启动监听
lsnrctl reload 或lsnrctl start
11.分别在主备库上测试tns
tnsping orcl
tnsping db2
12.使用rman恢复备库
rman target /
RMAN> startup mount;
RMAN> list backup;
RMAN> restore database;
RMAN> recover database;
RMAN> exit
13.备库启动建立standby redolog
检查是否有standby redolog的记录，有则删除
SQL> select 'alter database drop standby logfile group '||group#||';' from v$standby_log;
备库建立standby logfile，接收主库传送过来的redo条目。
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo01.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo02.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo03.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo04.log' size 50m;
查看备库的standby logfile：
SQL> select group#,thread#,sequence#,status from v$standby_log;
14.主库参数修改
sqlplus / as sysdba
alter system set log_archive_config='dg_config=(orcl,db2)';
alter system set log_archive_dest_1='location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl';
alter system set log_archive_dest_2='service=db2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db2';
alter system set log_archive_max_processes=6;
alter system set fal_server='db2';
alter system set fal_client='orcl';
alter system set standby_file_management=auto;
15.主备库中查看存档参数是否正常
主库：
col dest_name for a30
col error for a20
select dest_name,status,error,target,process from v$archive_dest where rownum<=2;
DEST_NAME STATUS ERROR TARGET PROCESS
------------------------------ --------- -------------------- ------- ----------
LOG_ARCHIVE_DEST_1 VALID PRIMARY ARCH
LOG_ARCHIVE_DEST_2 VALID STANDBY LGWR
备库:
col dest_name for a30
col error for a20
select dest_name,status,error,target,process from v$archive_dest where rownum<=2;
DEST_NAME STATUS ERROR TARGET PROCESS
------------------------------ --------- -------------------- ------- ----------
LOG_ARCHIVE_DEST_1 VALID LOCAL ARCH
LOG_ARCHIVE_DEST_2 VALID REMOTE LGWR
16. 查看主备库保护模式
查看主库保护模式：
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE READ WRITE
查看备库保护模式：
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED
17.开启物理备库的实时redo应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
18.验证备库应用了日志
主库：
SQL> alter system switch logfile;
备库：
SQL> select sequence#,applied from v$archived_log; --最后一行APPLIED为YES则表示应经应用
19.备库停止日志应用，启动到open
SQL> alter database recover managed standby database cancel;
备库启动到open
SQL> alter database open;
20.物理备库继续日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
21.测试物理备库是否数据更新：
主库上进行增删改操作，查看备库是否实时更新。
22.主库建立standby redolog
为了备库切换成主库时，原主库也可以接受新主库发来的redolog，所以在主库上添加standby logfile
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo01.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo02.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo03.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo04.log' size 50m;
查看主库的standby logfile：
SQL> select group#,thread#,sequence#,status from v$standby_log;
二、建立备库的备库
备库的备库ip及主机名：192.168.1.70 db3
1.建立备库的备库db3的归档目录
[root@db3 ~]# su - oracle
[oracle@db3 ~]$ cd /u01/
[oracle@db3 ~]$ mkdir arclog
4.rman备份备库（备份主库当然更好）
[oracle@db2 ~]$ rman target /
RMAN> backup database format '/u01/rman/db_%d_%s_%p_%u_%T.dbf';
RMAN> exit
[oracle@db2 ~]$ sqlplus
system/oracle@orclSQL> alter system switch logfile;
SQL> exit
[oracle@db2 ~]$ rman target /
RMAN> backup archivelog all format '/u01/rman/arc_%d_%s_%p_%u_%T.arc';
RMAN> exit
5.主库建立备库的控制文件：
SQL> alter database create standby controlfile as '/u01/standby.ctl' reuse;
6.把备库的rman备份、密码文件、文本参数文件、主库standby.ctl复制到db3的相应位置
①备库的rman备份
[oracle@db2 ~]$ scp -r /u01/rman/* 192.168.1.70:/u01/rman/
②备库库密码和文本参数文件
[oracle@db2 ~]$ cd /u01/oracle/orcl/dbs/
[oracle@db2 ~]$ scp orapworcl initorcl.ora 192.168.1.70:/u01/oracle/orcl/dbs/
③主库上建立的备库控制文件standby.ctl
[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.70:/u01/oracle/oradata/orcl/control01.ctl
[oracle@db ~]$ scp /u01/standby.ctl 192.168.1.70:/u01/oracle/flash_recovery_area/orcl/control02.ctl
7.备库的备库文本参数修改
[oracle@db3 orcl]$ cd $ORACLE_HOME/dbs
[oracle@db3 dbs]$ vi initorcl.ora
修改下面内容:
db_unique_name=db3
log_archive_config='dg_config=(db2,db3)'
log_archive_dest_1='location=/u01/arclog/ valid_for=(all_logfiles,all_roles) db_unique_name=db3'
#log_archive_dest_2='service=db2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db2'
log_archive_max_processes=6
fal_server='db2'
fal_client='db3'
standby_file_management=auto
8.主备库配置监听、tnsnames文件
注意：tnsnames中也要设置访问自己的配置，否则broker的failover时不成功。
非RAC下，建议配置监听的静态注册。
备库db2和db3的tnsnames.ora配置如下：
vi /u01/oracle/orcl/network/admin/tnsnames.ora
DB3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.70)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.68)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
9.分别在主备库上重启监听
lsnrctl reload
或lsnrctl start
10.分别在备库db2和备库的备库db3上测试tns
tnsping db2
tnsping db3
11.备库db3建立新的spfile
cd /u01/oracle/orcl/dbs
rm -rf spfileorcl.ora
sqlplus / as sysdba
SQL> create spfile from pfile;
检查spfile
12.rman恢复备库
rman target /
RMAN> startup mount;
RMAN> catalog start with '/u01/rman/';
RMAN> crosscheck backup;
RMAN> delete noprompt obsolete;
RMAN> delete noprompt expired backup;
RMAN> list backup;
RMAN> restore database;
RMAN> recover database;
RMAN> exit
13.重建standby redolog
SQL> select 'alter database drop standby logfile group '||group#||';' from v$standby_log;
备库建立standby logfile，接收主库传送过来的redo条目。
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo01.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo02.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo03.log' size 50m;
SQL> alter database add standby logfile '/u01/oracle/oradata/orcl/std_redo04.log' size 50m;
注：如果使用ARCH方式传输日志，这一步不用执行。
14.备库db2参数修改
sqlplus / as sysdba
alter system set log_archive_config='dg_config=(orcl,db2,db3)';
alter system set log_archive_dest_3='service=db3 lgwr async valid_for=(all_logfiles,all_roles) db_unique_name=db3';
注：虽然这里是lgwr，但测试发现，只有主库归档后，db3才接收到归档，开始应用日志。也就是这里使用lgwr和arch没有区别。
15.主备库中查看存档参数是否正常
主库：
col dest_name for a30
col error for a20
select dest_name,status,error,target,process from v$archive_dest where rownum<=2;
DEST_NAME STATUS ERROR TARGET PROCESS
------------------------------ --------- -------------------- ------- ----------
LOG_ARCHIVE_DEST_1 VALID PRIMARY ARCH
LOG_ARCHIVE_DEST_2 VALID STANDBY LGWR
备库db2:
col dest_name for a30
col error for a20
select dest_name,status,error,target,process from v$archive_dest where rownum<=3;
DEST_NAME STATUS ERROR TARGET PROCESS
------------------------------ --------- -------------------- ------- ----------
LOG_ARCHIVE_DEST_1 VALID LOCAL ARCH
LOG_ARCHIVE_DEST_2 VALID REMOTE LGWR
LOG_ARCHIVE_DEST_3 VALID REMOTE LGWR
备库db3:
col dest_name for a30
col error for a20
select dest_name,status,error,target,process from v$archive_dest where rownum=1;
DEST_NAME STATUS ERROR TARGET PROCESS
------------------------------ --------- -------------------- ------- ----------
LOG_ARCHIVE_DEST_1 VALID LOCAL ARCH
16. 查看主备库保护模式
查看主库保护模式：
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PRIMARY MAXIMUM PERFORMANCE READ WRITE
查看备库db2保护模式：
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY
查看备库db3保护模式：
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED
17.开启备库db3的日志实时应用:
SQL> alter database recover managed standby database using current logfile disconnect from session;
18.验证备库应用了日志:
主库：SQL> alter system switch logfile;
备库：SQL> select sequence#,applied from v$archived_log; --最后一行APPLIED为YES则表示应经应用
19.备库启动到open
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
20.备库db3的日志实时应用:
SQL> alter database recover managed standby database using current logfile disconnect from session;
测试发现，只有主库归档后，db3才接收到归档，开始应用日志，所以这里有无using current logfile并没有区别。
orcl主库切换日志后，db2和db3的alert日志为：
db2：
Tue Aug 20 21:37:03 2013
RFS[1]: Selected log 4 for thread 1 sequence 53 dbid 1309755207 branch 781974475
Recovery of Online Redo Log: Thread 1 Group 4 Seq 53 Reading mem 0 --db2是从redo中recover
Mem# 0: /u01/oracle/oradata/orcl/std_redo01.log
db3：
Tue Aug 20 21:37:03 2013
Media Recovery Log /u01/arclog/1_52_781974475.dbf --db3是从归档文件中recover
Media Recovery Waiting for thread 1 sequence 53
三、设置备库的备库db3延迟应用日志
[oracle@db3 ~]$ sqlplus / as sysdba
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database delay 120 disconnect from session;
这样主库DML或DDL操作完成120分钟后，备库的备库db3才开始应用日志。
四、尝试db2到db3的归档参数改为ARCH模式
上面db2到db3的log_archive_dest参数用的是lgwr，但效果感觉跟arch没有区别，干脆改为ARCH试试。
db2:
alter system set log_archive_dest_3='service=db3 arch async valid_for=(all_logfiles,all_roles) db_unique_name=db3';
db3 依然是：
SQL> alter database recover managed standby database delay 120 disconnect from session;
经测试db2到db3的log_archive_dest参数用的是arch也是完全一样的效果，即可以做到延迟120分应用主库日志。
主库切换日志后，备库日志：
db2：
Wed Aug 21 07:49:21 2013
ARC4: Standby redo logfile selected for thread 1 sequence 56 for destination LOG_ARCHIVE_DEST_3
Archived Log entry 35 added for thread 1 sequence 56 ID 0x4e110c47 dest 1:
Recovery of Online Redo Log: Thread 1 Group 4 Seq 57 Reading mem 0
Mem# 0: /u01/oracle/oradata/orcl/std_redo01.log
db3：
Wed Aug 21 07:49:20 2013
RFS[9]: Assigned to RFS process 2026
RFS[9]: Identified database type as 'physical standby': Client is ARCH pid 15778
RFS[9]: Selected log 4 for thread 1 sequence 56 dbid 1309755207 branch 781974475
Wed Aug 21 07:49:21 2013
Archived Log entry 17 added for thread 1 sequence 56 ID 0x4e110c47 dest 1:
