[20160501]dg参数STANDBY_MAX_DATA_DELAY

[20160501]dg参数STANDBY_MAX_DATA_DELAY.txt

--11G dg 支持Active Data Guard应用,就是在dg上只读模式下应用日志.
--同时支持会话参数STANDBY_MAX_DATA_DELAY,如果大于参数指定的时间,查询会报错.
--自己测试看看:

1.环境:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

DGMGRL> show configuration

Configuration - study

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database
    testdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2.设置参数STANDBY_MAX_DATA_DELAY,注意这个参数仅仅可以设置在dg的会话中.并且sys用户不能设置:

SYS@testdg> alter session  set STANDBY_MAX_DATA_DELAY=10 ;
ERROR:
ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users

SCOTT@testdg> alter system set STANDBY_MAX_DATA_DELAY=10 scope=both ;
alter system set STANDBY_MAX_DATA_DELAY=10 scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SCOTT@testdg> alter session  set STANDBY_MAX_DATA_DELAY=10 ;
Session altered.

3.测试效果:
--停止日志应用:
DGMGRL> edit database testdg set  state='apply-off';
Succeeded.

SCOTT@testdg> select * from deptxxxx;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      LONDON

--马上执行有显示,等10每秒....

SCOTT@testdg> select * from deptxxxx;
select * from deptxxxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-03172: STANDBY_MAX_DATA_DELAY of 10 seconds exceeded
ORA-06512: at line 4
ORA-03172: STANDBY_MAX_DATA_DELAY of 10 seconds exceeded

--你可以发现无法查询,日志应用已经延后1分多钟.

DGMGRL> show database   testdg

Database - testdg

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       1 minute 1 second
  Real Time Query: OFF
  Instance(s):
    testdg

Database Status:
SUCCESS

DGMGRL> edit database testdg set  state='apply-on';
Succeeded.

SCOTT@testdg> select * from deptxxxx;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      LONDON

4.继续测试,看看是否可以设置为0.
SCOTT@testdg> alter session  set STANDBY_MAX_DATA_DELAY=0 ;
Session altered.

SCOTT@testdg> select * from deptxxxx;
select * from deptxxxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded
ORA-06512: at line 4
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded

--这样无论做任何查询都失败,主要问题在于:The standby database must receive redo data via the SYNC transport.

DGMGRL> edit database testdg set PROPERTY LogXptMode="SYNC";
Property "logxptmode" updated
--注意这个属性要修改testdg的,而不是test的.很容易搞错!!!

SCOTT@testdg> select * from deptxxxx;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 MARKETING      LONDON

DGMGRL> edit database testdg set PROPERTY LogXptMode="ASYNC";
Property "logxptmode" updated

SCOTT@testdg> select * from dual ;
select * from dual
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded
ORA-06512: at line 4
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded

5.因为退出会话,必须重新设置才生效.不做测试了.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2091459/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2091459/