分享程序员开发的那些事...
更新时间：2022-04-04 08:01:34
[20151109]使用dgmgrl管理dataguard(15).txt
参考链接：
http://blog.itpub.net/267265/viewspace-1142649/
http://blog.itpub.net/267265/viewspace-1143027/
http://blog.itpub.net/267265/viewspace-1143058/
http://blog.itpub.net/267265/viewspace-1143126/
http://blog.itpub.net/267265/viewspace-1143480/
http://blog.itpub.net/267265/viewspace-1144742/
http://blog.itpub.net/267265/viewspace-1145573/
http://blog.itpub.net/267265/viewspace-1145697/
http://blog.itpub.net/267265/viewspace-1145727/
http://blog.itpub.net/267265/viewspace-1146558/
http://blog.itpub.net/267265/viewspace-1146575/
http://blog.itpub.net/267265/viewspace-1147481/
http://blog.itpub.net/267265/viewspace-1147509/
http://blog.itpub.net/267265/viewspace-1147618/
-- 我的测试环境：primary的tnsnames别名test，standby主机的tnsnames别名testdg。数据库版本：11GR2.
-- 基本学习完成.今天测试遇到一个问题,设置state='TRANSPORT-OFF',并不能马上阻止日志传输,实际上要等切换时才停止传输,做一个记录:
1.测试环境:
DGMGRL> show configuration ;
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DGMGRL> show database test
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Database Status:
SUCCESS
DGMGRL> show database testdg
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
testdg
Database Status:
2.停止传送日志:
DGMGRL> edit database test set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> show database test;
Database - test
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
test
Database Status:
SUCCESS
--而这个时候在dg上观察:
SYS@testdg> select * from v$standby_log ;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
4 2071943378 1 3991 52428800 512 320512 YES ACTIVE 1.3209E+10 2015-11-09 09:10:09 1.3209E+10 2015-11-09 09:16:36 1.3209E+10 2015-11-09 09:16:36
5 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
6 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
7 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
SYS@testdg> select * from v$standby_log ;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ ------------------- ------------ -------------------
4 2071943378 1 3991 52428800 512 321536 YES ACTIVE 1.3209E+10 2015-11-09 09:10:09 1.3209E+10 2015-11-09 09:16:38 1.3209E+10 2015-11-09 09:16:38
5 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
6 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
7 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
-- used 在变化.
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS 26792 IDLE N/A 0 0 0 0 0
ARCH 16572 CONNECTED N/A 0 0 0 0 0
RFS 26790 IDLE N/A 0 0 0 0 0
RFS 26777 IDLE 2 1 3991 648 1 0
ARCH 16574 CLOSING 4 1 3989 45056 225 0
ARCH 16570 CLOSING 5 1 3990 1 917 0
ARCH 16568 CLOSING 5 1 3988 77824 1348 0
MRP0 26629 APPLYING_LOG N/A 1 3991 648 102400 0
8 rows selected.
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS 26792 IDLE N/A 0 0 0 0 0
ARCH 16572 CONNECTED N/A 0 0 0 0 0
RFS 26790 IDLE N/A 0 0 0 0 0
RFS 26777 IDLE 2 1 3991 676 1 0
ARCH 16574 CLOSING 4 1 3989 45056 225 0
ARCH 16570 CLOSING 5 1 3990 1 917 0
ARCH 16568 CLOSING 5 1 3988 77824 1348 0
MRP0 26629 APPLYING_LOG N/A 1 3991 676 102400 0
8 rows selected.
--可以发现日志还在应用,也就是这个时候日志还在传输.
SCOTT@test> update dept1 set loc='AAA' where deptno=60 ;
1 row updated.
SCOTT@test> commit ;
Commit complete.
SYS@testdg> select * from scott.dept1 where deptno=60;
DEPTNO DNAME LOC
---------- -------------- -------------
60 cc AAA
--要避免这种情况,要在主库上执行一次日志切换,我观察主库的alert*.log文件:
Mon Nov 09 08:58:35 2015
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle11g/archivelog
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
Mon Nov 09 09:04:23 2015
ALTER SYSTEM ARCHIVE LOG <===日志切换
Mon Nov 09 09:04:24 2015
Beginning log switch checkpoint up to RBA [0xf96.2.10], SCN: 13209368349
Thread 1 advanced to log sequence 3990 (LGWR switch)
Current log# 1 seq# 3990 mem# 0: /u01/app/oracle11g/oradata/test/redo01.log
Mon Nov 09 09:04:24 2015
Archived Log entry 6113 added for thread 1 sequence 3989 ID 0x806ffa4c dest 1:
Mon Nov 09 09:08:55 2015
Completed checkpoint up to RBA [0xf96.2.10], SCN: 13209368349
Mon Nov 09 09:10:09 2015
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; <==设置state='TRANSPORT-ON';时执行的是这个,实际上同时做了1次日志切换(下面一条语句).
ALTER SYSTEM ARCHIVE LOG
Mon Nov 09 09:10:09 2015
Beginning log switch checkpoint up to RBA [0xf97.2.10], SCN: 13209369037
Thread 1 advanced to log sequence 3991 (LGWR switch)
Current log# 2 seq# 3991 mem# 0: /u01/app/oracle11g/oradata/test/redo02.log
Mon Nov 09 09:10:09 2015
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
--可以观察到实际上log_archive_dest_state_2参数
SCOTT@test> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------- ------- -------
log_archive_dest_state_2 string RESET
SCOTT@test> alter system set log_archive_dest_state_2='aaa' ;
alter system set log_archive_dest_state_2='aaa'
*
ERROR at line 1:
ORA-00096: invalid value aaa for parameter log_archive_dest_state_2, must be from among alternate, reset, defer, enable
--我的测试仅仅在执行 alter system archive log current ;后才停止传输.(在后面)
3.看看停止日志应用是否有效.
DGMGRL> edit database testdg set state='APPLY-Off';
Succeeded.
DGMGRL> show database test;
Database - test
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
test
Database Status:
SUCCESS
DGMGRL> show database testdg;
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 22 seconds
Real Time Query: OFF
Instance(s):
testdg
Database Status:
SUCCESS
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS 26790 IDLE N/A 0 0 0 0 0
ARCH 16572 CONNECTED N/A 0 0 0 0 0
RFS 26792 IDLE N/A 0 0 0 0 0
RFS 26777 IDLE 2 1 3991 2010 2 0
ARCH 16574 CLOSING 4 1 3989 45056 225 0
ARCH 16568 CLOSING 5 1 3988 77824 1348 0
ARCH 16570 CLOSING 5 1 3990 1 917 0
7 rows selected.
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS 26790 IDLE N/A 0 0 0 0 0
ARCH 16572 CONNECTED N/A 0 0 0 0 0
RFS 26792 IDLE N/A 0 0 0 0 0
RFS 26777 IDLE 2 1 3991 2012 1 0
ARCH 16574 CLOSING 4 1 3989 45056 225 0
ARCH 16568 CLOSING 5 1 3988 77824 1348 0
ARCH 16570 CLOSING 5 1 3990 1 917 0
7 rows selected.
--RFS进程依旧在传输日志.这样日志停止应用不会阻止日志传输.
DGMGRL> edit database testdg set state='APPLY-ON';
Succeeded.
--等1小会....
DGMGRL> show database testdg;
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
testdg
Database Status:
4.主库进行日志切换:
SYS@test> alter system archive log current ;
System altered.
DGMGRL> show database test;
Database - test
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
test
Database Status:
SUCCESS
DGMGRL> show database testdg;
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
testdg
Database Status:
SUCCESS
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS 26792 IDLE N/A 0 0 0 0 0
RFS 26790 IDLE N/A 0 0 0 0 0
ARCH 16572 CONNECTED N/A 0 0 0 0 0
ARCH 16574 CLOSING 4 1 3991 2048 327 0
ARCH 16570 CLOSING 5 1 3990 1 917 0
ARCH 16568 CLOSING 5 1 3988 77824 1348 0
MRP0 26972 WAIT_FOR_LOG N/A 1 3992 0 0 0
7 rows selected.
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS 26792 IDLE N/A 0 0 0 0 0
RFS 26790 IDLE N/A 0 0 0 0 0
ARCH 16572 CONNECTED N/A 0 0 0 0 0
ARCH 16574 CLOSING 4 1 3991 2048 327 0
ARCH 16570 CLOSING 5 1 3990 1 917 0
ARCH 16568 CLOSING 5 1 3988 77824 1348 0
MRP0 26972 WAIT_FOR_LOG N/A 1 3992 0 0 0
7 rows selected.
--可以发现这样在切换时才生效,这点在以后的测试后要注意.
--另外这个时候注意1点,切换后Apply Lag显示0,实际上这个时候已经停止日志应用(切记!!!).
DGMGRL> show database testdg;
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
testdg
Database Status:
SUCCESS
5.打开日志传输:
DGMGRL> edit database test set state='TRANSPORT-On';
Succeeded.
SYS@testdg> @dgs
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ------- ---------- ---------- ---------- ----------
RFS 26792 IDLE N/A 0 0 0 0 0
ARCH 16572 CONNECTED N/A 0 0 0 0 0
RFS 27015 IDLE N/A 0 0 0 0 0
RFS 27017 IDLE 1 1 3993 41 1 0
ARCH 16574 CLOSING 4 1 3991 2048 327 0
ARCH 16570 CLOSING 5 1 3990 1 917 0
ARCH 16568 CLOSING 5 1 3988 77824 1348 0
MRP0 26972 WAIT_FOR_LOG N/A 1 3992 0 0 0
8 rows selected.
DGMGRL> show database testdg;
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 2 minutes 35 seconds
Apply Lag: 2 minutes 35 seconds
Real Time Query: ON
Instance(s):
testdg
Database Status:
SUCCESS
...
DGMGRL> show database testdg;
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
testdg
Database Status:
SUCCESS
6.能快速停止日志传输吗?
SCOTT@test> alter system set log_archive_dest_state_2='defer' ;
System altered.
DGMGRL> show database test;
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Error: ORA-16738: redo transport service for database "testdg" is not running
Database Status:
ERROR
--使用了DGMGRL,不能在使用sqlplus修改这些相关参数.存在冲突.
SCOTT@test> alter system set log_archive_dest_state_2='enable' ;
System altered.
DGMGRL> edit database test set state='offline';
Operation requires shutdown of instance "test" on database "test"
Shutting down instance "test"...
Database closed.
Database dismounted.
ORACLE instance shut down.
--这个是关闭数据库命令,注意!!当然关闭testdg也不是我希望的.
DGMGRL> edit database test set state='log-TRANSPORT-Off';
Succeeded.
--这个与edit database test set state='TRANSPORT-On';一样.
DGMGRL> edit database test set state='log-TRANSPORT-On';
Error: ORA-16516: current state is invalid for the attempted operation
Failed.
DGMGRL> edit database test set state='TRANSPORT-On';
Succeeded.
--我看了相关文档,没有发现相关命令,实际上从前面的测试alert.log文件中应该可以发现设置TRANSPORT-OFF时没有同时执行日志切换命
--令,而TRANSPORT-ON时自动执行日志切换.
--看来最简单的方法就是设置state='TRANSPORT-OFF'时, 手工切换1次日志,才能停止日志的传输.