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

郑全 · 发表于 2019-12-19 18:25:38

说明

在19c版本中，ADG中的备库支持DML操作，原理是：是通过将备库上的DML重定向到主库上执行，然后备库应用DML变化数据，至此完成备库DML操作。

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

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

配置
自动重定向DML操作支持会话级别和系统级别，会话级别会覆盖系统级别配置。

在所有备库上启用DML重定向
将初始化参数ADG_REDIRECT_DML设置为trueALTER SYSTEM SET ADG_REDIRECT_DML=true SCOPE=BOTH;
1

当前会话启用DML重定向ALTER SESSION ENABLE ADG_REDIRECT_DML;
1

具体例子
会话级别的DML重定向

确认当前数据库角色
SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------------------------------------- --------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY
12345

在备库上启用会话级别DML重定向
SQL> conn system/oracle --非sys用户
Connected.
SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
Session altered.
1234

确认表数据
SQL> select * from test;

no rows selected
123

在备库上执行DML操作
SQL> insert into test values(1);
1 row created.

SQL> select * from test;

   ID
  ----------
   1
12345678

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

错误可能原因：
[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.

123456789101112
系统级别启用DML重定向

查看ADG_REDIRECT_DML参数值

SQL> SHOW PARAMETER ADG_REDIRECT_DML;

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

在备库上设置为trueSQL> ALTER SYSTEM SET ADG_REDIRECT_DML=true SCOPE=BOTH;

System altered.
123

执行DML SQL> conn system/oracle
Connected.

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

SQL> COMMIT;

Commit complete.
123456789

验证数据 SQL> select * from system.test;

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