[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.因為退出會話,必須重新設定才生效.不做測試了.