[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每秒....

select * from deptxxxx

*

ORA-00604: error occurred at recursive SQL level 1

ORA-03172: STANDBY_MAX_DATA_DELAY of 10 seconds exceeded

ORA-06512: at line 4

--你可以发现无法查询,日志应用已经延后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:

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

4.继续测试,看看是否可以设置为0.

SCOTT@testdg> alter session  set STANDBY_MAX_DATA_DELAY=0 ;

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的.很容易搞错!!!

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

SCOTT@testdg> select * from dual ;

select * from dual

              *

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