签到成功

知道了

CNDBA社区CNDBA社区

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

2019-06-21 14:40 2884 0 原创 Oracle 19c
作者: Expect-乐

说明

在19c版本中,ADG中的备库支持DML操作,原理是:是通过将备库上的DML重定向到主库上执行,然后备库应用DML变化数据,至此完成备库DML操作。http://www.cndba.cn/Expect-le/article/3430

注:避免在备库上执行过多的DML操作,实际上是在主库上执行。

不支持XA事务中的DML操作。

http://www.cndba.cn/Expect-le/article/3430

配置

自动重定向DML操作支持会话级别和系统级别,会话级别会覆盖系统级别配置。http://www.cndba.cn/Expect-le/article/3430http://www.cndba.cn/Expect-le/article/3430

  • 在所有备库上启用DML重定向
    将初始化参数ADG_REDIRECT_DML设置为true
    ALTER SYSTEM SET ADG_REDIRECT_DML=true SCOPE=BOTH;
    
  • 当前会话启用DML重定向
    ALTER SESSION ENABLE ADG_REDIRECT_DML;
    

具体例子

会话级别的DML重定向

  • 确认当前数据库角色

      SQL>  select open_mode,database_role from v$database;
    
      OPEN_MODE                                DATABASE_ROLE
      ---------------------------------------- --------------------------------
      READ ONLY WITH APPLY                     PHYSICAL STANDBY
    
  • 在备库上启用会话级别DML重定向

    SQL> conn system/oracle   --非sys用户
    Connected.
    SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
    Session altered.
    
  • 确认表数据

      SQL> select * from test;
    
      no rows selected
    
  • 在备库上执行DML操作

    SQL> insert into test values(1);
    1 row created.
    
    SQL> select * from test;
    
          ID
      ----------
           1
    

注意:不支持sys用户会话启用:ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed

错误可能原因:http://www.cndba.cn/Expect-le/article/3430http://www.cndba.cn/Expect-le/article/3430

[oracle@19dg_p admin]$ 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.

系统级别启用DML重定向

1) 查看ADG_REDIRECT_DML参数值

SQL> SHOW PARAMETER ADG_REDIRECT_DML;

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

2) 在备库上设置为true

   SQL> ALTER SYSTEM SET ADG_REDIRECT_DML=true SCOPE=BOTH;

   System altered.

3) 执行DML

http://www.cndba.cn/Expect-le/article/3430

    SQL> conn system/oracle
    Connected.

    SQL> insert into system.test values(2);
    1 row created.

    SQL> COMMIT;

    Commit complete.

4) 验证数据http://www.cndba.cn/Expect-le/article/3430

http://www.cndba.cn/Expect-le/article/3430
http://www.cndba.cn/Expect-le/article/3430

    SQL> select * from system.test;

            ID
    ----------
            1
            2

以上就是关于ADG中备库DML重定向特性测试,

版权声明:本文为博主原创文章,未经博主允许不得转载。

ADG_REDIRECT_DML

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问:1351001次
  • 积分:1957
  • 等级:核心会员
  • 排名:第3名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ