Oracle 19c Active Data Guard 重定向增強讀寫分離 DML Operations from a standby to the primary in Oracle Active Data Guard ( ADG_REDIRECT_DML )

記錄設定,技術過程些功能限制 Oracle 19c Active Data Guard ADG_REDIRECT_DML

1. Oracle 文件說明 ADG_REDIRECT_DML
(Oracle Documents description ADG_REDIRECT_DML)

Use ADG_REDIRECT_DML to enable or disable automatic redirection of DML operations from a standby to the primary in Oracle Active Data Guard environments.

2. 官網文件說明設定 ADG_REDIRECT_DML 共有 Database層級與 Session層級兩種設定方式 
(The official website document explains that ADG_REDIRECT_DML has two setting methods: Database level and Session level)

-- Database Level
SQL> alter system set ADG_REDIRECT_DML = true scope=both sid='*';

-- Session Level
SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

 

A. 測試 Database Level 
(Test Case for Database Level)

1. 檢查 Primary & Standby ADG_REDIRECT_DML 預設值為 False
(Check Primary and Standby ADG_REDIRECT_DML default is False)

-- Primary & Standby
SQL> show parameter adg_redirect_dml;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
adg_redirect_dml		     boolean	 FALSE

2. 檢查 Standby 目前為 ADG 並設定 ADG_REDIRECT_DML 為  True 
(Config ADG_REDIRECT_DML is True and check open status is ADG in Standby)

-- Standby
SQL> conn / as sysdba
Connected.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> alter system set ADG_REDIRECT_DML = true scope=both sid='*';

3. 建立測試資料 
(Create Test Data in Primary)

-- Primary
SQL> create table t1 (c1 int, c2 varchar2(20));
SQL> insert into t1 values (1, 'AAA');
SQL> insert into t1 values (2, 'BBB');
SQL> insert into t1 values (3, 'CCC');
SQL> commit;
-- Standby
SQL> select * from t1;

	C1 C2
---------- --------------------
	 1 AAA
	 2 BBB
	 3 CCC

4. 在 Standby 測試新增資料 
(Test Insert data in Standby)

-- Standby
SQL> insert into t1 values (4, 'DDD');

1 row created.

SQL> commit;

Commit complete.

 

B. 測試 Session Level 
(Test Case for Session Level)

1. 檢查 Primary & Standby ADG_REDIRECT_DML 預設值為 False
(Check Primary and Standby ADG_REDIRECT_DML default is False)

-- Primary & Standby
SQL> show parameter adg_redirect_dml;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
adg_redirect_dml		     boolean	 FALSE

2. 在 Standby 測試新增資料
(Test Insert data in Standby)

-- Standby
SQL> insert into t1 values (4, 'DDD');
insert into t1 values (4, 'DDD')
            *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

3. 啟用 ADG_REDIRECT_DML 設定,該設定只會存在該 session 直到斷線或手動取消
(Enable automatic redirection of DML operations from a standby to the primary for the current session until disconnected or manually canceled)

-- Standby
SQL> alter session enable adg_redirect_dml;

Session altered.

SQL> insert into t1 values (4, 'DDD');

1 row created.

SQL> commit;

Commit complete.

4. 取消 ADG_REDIRECT_DML 設定
(Disable automatic redirection of DML operations from a standby to the primary for the current session)

-- Standby
SQL> alter session disable adg_redirect_dml;

Note: ORA-00922,需要授權 alter session (Need grants alter session privilege)

-- Standby
SQL> alter session enable adg_recirect_dml;
alter session enable adg_recirect_dml
                     *
ERROR at line 1:
ORA-00922: missing or invalid option

-- Primary
SQL> grant alter session to schema_user;

Grant succeeded.

 

C. 測試 Row Lock 
(Test Case for Row Lock)

1. 在 Primary 測試更新資料但不做 Commit 
(Test updates in Primary without commit)

-- Primary
SQL> update t1 set c2='YYY' where c1 = 4;

1 row updated.

2. 在 Standby 測試更新資料會卡住無回應 (DBLINK取得不到lock資源,預設60秒後會 timeout)
(Testing updates in Standby will get stuck and unresponsive (DBLINK cannot get the lock resource, it will timeout after 60 seconds by default))

-- Standby
SQL> update t1 set c2='ZZZ' where c1 = 4;
...Lock No Response...

-- Cannot get the lock resource after 60 seconds timeout
update t1 set c2='ZZZ' where c1 = 4;
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
ORA-02063: preceding line from ADGREDIRECT

3. 查詢 Lock 狀況
(Query Lock Status)

-- Primary
set linesize 180
col user_status format a15
col sid_serial format a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
 SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,
     CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,
     (SELECT instance_name
      FROM v$instance)
      conn_instance,
     s.sid,
     s.program,
     s.osuser,
     s.machine,
     DECODE (l.TYPE,
         'RT', 'Redo Log Buffer',
         'TD', 'Dictionary',
         'TM', 'DML',
         'TS', 'Temp Segments',
         'TX', 'Transaction',
         'UL', 'User',
         'RW', 'Row Wait',
         l.TYPE)
      lock_type--,id1
           --,id2
     ,
     DECODE (l.lmode,
         0, 'None',
         1, 'Null',
         2, 'Row Share',
         3, 'Row Excl.',
         4, 'Share',
         5, 'S/Row Excl.',
         6, 'Exclusive',
         LTRIM (TO_CHAR (lmode, '990')))
      lock_mode,
     ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
     ,
     object_name
  FROM v$lock l
     JOIN v$session s ON (l.sid = s.sid)
     JOIN v$locked_object o
      ON (s.sid = o.session_id)
     JOIN dba_objects d ON (d.object_id = o.object_id)
  WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
                   FROM v$lock
                   WHERE request > 0)
ORDER BY id1, id2, ctime DESC; 
USER_STATUS	SID_SERIAL	CONN_INSTANCE	       SID PROGRAM			  OSUSER	  MACHINE	  LOCK_TYPE	  LOCK_MODE	   CTIME OBJECT_NAME
--------------- --------------- --------------- ---------- ------------------------------ --------------- --------------- --------------- ----------- ---------- -------------------------
Blocking ->	'464,45649'	orcl1		       464 sqlplus@cbs-db9 (TNS V1-V3)	  oracle	  cbs-db9	  Transaction	  Exclusive	     239 T1
Waiting 	'816,57770'	orcl1		       816 oracle@cbs-db4 (TNS V1-V3)	  oracle	  cbs-db4	  Transaction	  None		      48 T1

4. 在 Primary 下達 Commit 
(Commit at Primary)

-- Primary
SQL> commit;

Commit complete.

5. 此時 Standby 端回傳資料已更新,但是未 commit 
(At this point, the data returned by Standby has been updated, but it has not yet been committed)

-- Standby Return Message 1 row updated.
1 row updated.

6. 從 Primary 查資料會是 Primary commit 的資料 
(Checking data from Primary will be the data of Primary commit)

-- Primary
SQL> select * from t1 where c1 = 4;

	C1 C2
---------- --------------------
	 4 YYY

7. 從 Standby 查會是 Standby 更新而未 commit 的資料 
(But checking from Standby will be the data updated by Standby but not committed)

-- Standby
SQL> select * from t1 where c1 = 4;

	C1 C2
---------- --------------------
	 4 ZZZ

8. Standby 下 Commit 後,Primary 重新查詢 
(After Standby under Commit, Primary re-query)

-- Standby
SQL> commit;

Commit complete.
-- Primary
SQL> select * from t1 where c1 = 4;

	C1 C2
---------- --------------------
	 4 ZZZ

Important Note:

如果Lock資料是由Primary發起,Standby則會等待60秒後timeout,
反之Lock資料是由Standby發起,Primary則不會有60秒timeout的限制。

If the Lock data is initiated by Primary, Standby will wait for a timeout after 60 seconds.
On the contrary, Lock data is initiated by Standby, and Primary will not have a 60-second timeout limit.

 

D. 測試 global temporary table 
(Test Case for global temporary table)

1. 建立 global temp table,delete rows & preserve rows 均可
(Create a global temp table, and you can set delete rows & preserve rows)

-- Standby
SQL> CREATE GLOBAL TEMPORARY TABLE GTT_tab1(id NUMBER, description VARCHAR(20))
  2  on commit delete rows;

2. 分別在 Primary & Standby 新增資料
(Insert data in Primary and Standby respectively)

-- Primary
SQL> insert into gtt_tab1 values (1, 'jjj');
SQL> select * from gtt_tab1;

	ID DESCRIPTION
---------- --------------------
	 1 jjj

-- Standby
SQL> insert into gtt_tab1 values (1, 'aaa');
SQL> select * from gtt_tab1;

	ID DESCRIPTION
---------- --------------------
	 1 aaa

3. 分別在 Primary & Standby 執行 commit
(Execute commit on Primary and Standby respectively)

-- Primary & Standby
SQL> commit;

Commit complete.

SQL> select * from gtt_tab1;

no rows selected

 

E. 測試 DCL & 用sys執行命令會拋出 ORA-16397
(Testing DCL & executing command with sys throws ORA-16397 in Standby)

-- Standby
SQL> conn / as sysdba
Connected.

SQL> grant alter session to test_user;
grant alter session to test_user
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16397: statement redirection from Oracle Active Data Guard standby database
to primary database failed

SQL> conn system/oracle
Connected.

SQL> grant alter session to test_user;
grant alter session to test_user
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-02063: preceding line from ADGREDIRECT
[oracle@cbs-db4 ~]$ oerr ORA 16397
16397, 00000, "statement redirection from Oracle Active Data Guard standby database to primary database failed"
// *Cause:  The statement redirection failed because of one of the following reasons:
//          1. The primary database connect string was not established.
//          2. The primary database could not be reached.
//          3. The undo-mode or incarnation were not the same.
//          4. The current user and logged-in user were not the same.
//          5. Redirecting CREATE TABLE AS SELECT (CTAS) of the global temporary
//             table was not supported.
//          6. Redirecting PL/SQL execution having bind variable was not supported.
// *Action: Run the statement after fixing the condition that caused the failure.

 

F. 追蹤 ADG_RECIRECT_DML 內容 
(Track ADG_RECIRECT_DML content)

1. 測試新增資料並開啟 events 追蹤
(Insert data and enable events tracking)

-- Standby
SQL> alter session set events '10046 trace name context forever ,level 12';

Session altered.

SQL> insert into t1 values (5, 'EEE');

1 row created.

SQL> commit;

2. Trace File Path : $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/$ORACLE_SID_ora_xxxxxx.trc

=====================
PARSING IN CURSOR #140174936943752 len=32 dep=0 uid=90 oct=2 lid=90 tim=11409606185934 hv=3099357392 ad='76586840' sqlid='0ft2y3awbsw6h'
insert into t1 values (5, 'EEE')
END OF STMT
PARSE #140174936943752:c=16504,e=75318,p=0,cr=0,cu=3,mis=1,r=0,dep=0,og=1,plh=0,tim=11409606185933
WAIT #140174936943752: nam='SQL*Net message to dblink' ela= 1 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409606186255
WAIT #140174936943752: nam='SQL*Net message from dblink' ela= 3866 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409606190142
EXEC #140174936943752:c=922,e=4108,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=11409606190223
WAIT #140174936943752: nam='SQL*Net message to dblink' ela= 3 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409606190269
WAIT #140174936943752: nam='SQL*Net vector data to dblink' ela= 47 driver id=675562835 #bytes=26 p3=2 obj#=-1 tim=11409606190337
WAIT #140174936943752: nam='SQL*Net message from dblink' ela= 424 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409606190794

*** 2022-02-16T11:14:24.825050+08:00
WAIT #140174936943752: nam='standby query scn advance' ela= 1048564 p1=2037674177 p2=0 p3=12000 obj#=-1 tim=11409607239400
WAIT #140174936943752: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11409607239676

*** 2022-02-16T11:14:26.384766+08:00
WAIT #140174936943752: nam='SQL*Net message from client' ela= 1559315 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11409608799038
CLOSE #140174936943752:c=86,e=86,dep=0,type=0,tim=11409608799423
=====================
PARSING IN CURSOR #140174936943752 len=6 dep=0 uid=90 oct=44 lid=90 tim=11409608799679 hv=3480936638 ad='0' sqlid='23wm3kz7rps5y'
commit
END OF STMT
PARSE #140174936943752:c=42,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=11409608799678
XCTEND rlbk=0, rd_only=1, tim=11409608799958
WAIT #140174936943752: nam='SQL*Net message to dblink' ela= 10 driver id=675562835 #bytes=214 p3=0 obj#=-1 tim=11409608800649

*** 2022-02-16T11:14:26.393818+08:00
WAIT #140174936943752: nam='SQL*Net message from dblink' ela= 7493 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409608808182
WAIT #140174936943752: nam='SQL*Net message to dblink' ela= 4 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409608808543
WAIT #140174936943752: nam='SQL*Net vector data to dblink' ela= 61 driver id=675562835 #bytes=27 p3=2 obj#=-1 tim=11409608808637
WAIT #140174936943752: nam='SQL*Net message from dblink' ela= 1037 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409608809699

*** 2022-02-16T11:14:27.455980+08:00
WAIT #140174936943752: nam='standby query scn advance' ela= 1060594 p1=2037674183 p2=0 p3=12000 obj#=-1 tim=11409609870340
EXEC #140174936943752:c=1659,e=1070575,p=0,cr=0,cu=4,mis=0,r=0,dep=0,og=0,plh=0,tim=11409609870504
WAIT #140174936943752: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11409609870576

*** 2022-02-16T11:14:28.419922+08:00
WAIT #140174936943752: nam='SQL*Net message from client' ela= 963675 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11409610834288
XCTEND rlbk=0, rd_only=1, tim=11409610834480
CLOSE #140174936943752:c=8,e=8,dep=0,type=0,tim=11409610834560
WAIT #0: nam='SQL*Net message to dblink' ela= 4 driver id=675562835 #bytes=3 p3=0 obj#=-1 tim=11409610834811
WAIT #0: nam='SQL*Net message from dblink' ela= 1341 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=11409610836175

3. 可以看到 Trace 內容是透過 dblink 技術來執行
(It can be seen that the trace content is executed through dblink technology)

 

Z. 總結

1. DML Redirection 不能用於雲端授權 (not available in Authorized Cloud Environments)
2. 可以使用 global temporary table
3. Session 與 Database 的設定層級需要想清楚
4. 對 Standby 的系統保護需要更安全,避免濫用

 

參考:

Oracle 19c 新特性:ADG的自动DML重定向增强读写分离--ADG_REDIRECT_DML

Oracle Document :ADG_REDIRECT_DML

已轉向 blogger 記錄

https://slowlife-notes.blogspot.com