[20151109]使用dgmgrl管理dataguard(15)

[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次日志,才能停止日志的传输.

时间: 2024-09-20 23:32:58

[20151109]使用dgmgrl管理dataguard(15)的相关文章

[20140420]使用dgmgrl管理dataguard(10)

[20140420]使用dgmgrl管理dataguard(10).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.it

[20140416]使用dgmgrl管理dataguard(5).txt

[20140416]使用dgmgrl管理dataguard(5).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/ 前面我提到一旦使用dgmgrl

[20140418]使用dgmgrl管理dataguard(6).txt

[20140418]使用dgmgrl管理dataguard(6).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.itp

[20140422]使用dgmgrl管理dataguard(12)

[20140422]使用dgmgrl管理dataguard(12).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.it

[20140420]使用dgmgrl管理dataguard(9).txt

[20140420]使用dgmgrl管理dataguard(9).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.itp

[20140420]使用dgmgrl管理dataguard(8).txt

[20140420]使用dgmgrl管理dataguard(8).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.itp

[20140416]使用dgmgrl管理dataguard(4).txt

[20140416]使用dgmgrl管理dataguard(4).txt 参考链接: http://blog.itpub.net/267265/viewspace-1142649/ http://blog.itpub.net/267265/viewspace-1143027/ http://blog.itpub.net/267265/viewspace-1143058/ 下面讲解已经使用了dgmgrl来管理dataguard,再使用sqlplus修改一些相关参数会出现什么情况呢? 我的测试环境:

[20140422]使用dgmgrl管理dataguard(11)

[20140422]使用dgmgrl管理dataguard(11).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.it

[20140422]使用dgmgrl管理dataguard(13)

[20140422]使用dgmgrl管理dataguard(13).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.it