Oracle 19c 新特性 --- ADG备库支持DML重定向

最新推荐文章于 2023-02-01 14:59:09 发布

欣欣闹天下

最新推荐文章于 2023-02-01 14:59:09 发布

阅读量491

点赞数

分类专栏： Oracle Oracle19c dg

本文链接：https://blog.csdn.net/qq_40066204/article/details/114217702

版权

Oracle 同时被 3 个专栏收录

12 篇文章 0 订阅

订阅专栏

Oracle19c

3 篇文章 0 订阅

订阅专栏

1 篇文章 0 订阅

订阅专栏

在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

欣欣闹天下

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 19c 新特性 --- ADG备库支持DML重定向

在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
复制链接

扫一扫