在19c版本中,ADG中的备库支持DML操作,原理是:是通过将备库上的DML重定向到主库上执行,然后备库应用DML变化数据,至此完成备库DML操作。
主库建测试表
SQL> create table itpux.gm as select * from itpux.JL;
Table created.
在备库上查
SQL> select * from itpux.gm;
EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
----------- ----------------- ----------------- ------------------------------ -------------
102 20010113 00:00:00 20060724 00:00:00 AD_PRES 10
101 19970921 00:00:00 20011027 00:00:00 AC_MGR 100
101 20011028 00:00:00 20050315 00:00:00 ST_MAN 130
201 20040217 00:00:00 20071219 00:00:00 MK_MAN 200
114 20060324 00:00:00 20071231 00:00:00 PU_CLERK 30
122 20070101 00:00:00 20071231 00:00:00 HR_REP 40
200 19950917 00:00:00 20010617 00:00:00 AD_ASST 90
176 20060324 00:00:00 20061231 00:00:00 IT_PROG 210
176 20070101 00:00:00 20071231 00:00:00 SH_CLERK 220
200 20020701 00:00:00 20061231 00:00:00 PR_REP 200
10 rows selected.
在备库
修改session的参数(测试)
SQL> conn dbmt/dbmt
Connected.
SQL>
ALTER SESSION ENABLE ADG_REDIRECT_DML;
alter session set events '10046 trace name context forever ,level 12';
insert into itpux.gm values (1,TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),TO_DATE(' 2021-02-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),'GWX',300);
commit;
alter session set events '10046 trace name context off';
查看trace文件
more /u01/app/oracle/diag/rdbms/sjz/ts/trace/ts_ora_21145.trc
WAIT #139842434272072: nam='SQL*Net message from client' ela= 28346310 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=9382162433
CLOSE #139842434272072:c=4,e=4,dep=0,type=0,tim=9382162553
WAIT #139842434272072: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382162787
WAIT #139842434272072: nam='SQL*Net message from dblink' ela= 1136 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382163939
WAIT #139842434272072: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382163992
WAIT #139842434272072: nam='SQL*Net message from dblink' ela= 283 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382164287
=====================
PARSING IN CURSOR #139842434272072 len=157 dep=0 uid=107 oct=2 lid=107 tim=9382186781 hv=52481518 ad='7108d2b8' sqlid='g5w0ykc1k1mgf'
insert into itpux.gm values (1,TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),TO_DATE(' 2021-02-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),'GWX',300)
END OF STMT
PARSE #139842434272072:c=1270,e=24180,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=9382186760
WAIT #139842434272072: nam='SQL*Net message to dblink' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382186955
WAIT #139842434272072: nam='SQL*Net message from dblink' ela= 5896 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382192872
EXEC #139842434272072:c=272,e=6139,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=9382193006
WAIT #139842434272072: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382193029
WAIT #139842434272072: nam='SQL*Net vector data to dblink' ela= 39 driver id=1413697536 #bytes=26 p3=2 obj#=-1 tim=9382193080
WAIT #139842434272072: nam='SQL*Net message from dblink' ela= 409 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9382193502
WAIT #139842434272072: nam='standby query scn advance' ela= 331772 p1=3080202 p2=0 p3=12000 obj#=-1 tim=9382525296
WAIT #139842434272072: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=9382525364
*** 2021-02-27T13:21:02.470434+08:00
WAIT #139842434272072: nam='SQL*Net message from client' ela= 4031416 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=9386556794
CLOSE #139842434272072:c=14,e=14,dep=0,type=0,tim=9386556873
=====================
PARSING IN CURSOR #139842434272072 len=7 dep=0 uid=107 oct=44 lid=107 tim=9386556996 hv=2773750085 ad='0' sqlid='d2knfy6kp84a5'
commit
END OF STMT
主库查询这个表是否insert?
SQL> select * from itpux.gm ;
EMPLOYEE_ID START_DATE END_DATE JOB_ID DEPARTMENT_ID
----------- ----------------- ----------------- ------------------------------ -------------
102 20010113 00:00:00 20060724 00:00:00 AD_PRES 10
101 19970921 00:00:00 20011027 00:00:00 AC_MGR 100
101 20011028 00:00:00 20050315 00:00:00 ST_MAN 130
201 20040217 00:00:00 20071219 00:00:00 MK_MAN 200
114 20060324 00:00:00 20071231 00:00:00 PU_CLERK 30
122 20070101 00:00:00 20071231 00:00:00 HR_REP 40
200 19950917 00:00:00 20010617 00:00:00 AD_ASST 90
176 20060324 00:00:00 20061231 00:00:00 IT_PROG 210
176 20070101 00:00:00 20071231 00:00:00 SH_CLERK 220
200 20020701 00:00:00 20061231 00:00:00 PR_REP 200
1 20210201 00:00:00 20210227 00:00:00 GWX 300 <<<<<<<<<<
遇到的问题(翻车)
一、有gap是不能做dml
ID 2739402.1
ORA-02055 ORA-40509 is expected if there is lag between Primary and Standby databases, the following should be met to avoid the errors:
1、Use LGWR ASYNC mode
2、Real-time apply should be activated
3、Standby SRLs should have the same size as the Primary ORLs, and STATUS of Standby SRLs should be ACTIVE.
二、不支持sys用户会话启用:
ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed
SQL>
SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML
2 ;
Session altered.
SQL> alter session set events '10046 trace name context forever ,level 12';
Session altered.
SQL> insert into itpux.gm values (1,TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),TO_DATE(' 2021-02-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),gwx,300);
insert into itpux.gm values (1,TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),TO_DATE(' 2021-02-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'),gwx,300)
*
ERROR at line 1:
ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed