Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching, Zero-Downtime Upgrade, GoldenGate

Oracle Exadata ,Oracle ODA, Oracle ZDLRA


当前位置: 首页 » 技术文章 » Oracle 12c+

19c新特性之ADG DML重定向功能

在Oracle Database 19c之前,在ADG环境中,您只能更新备用数据库上的全局临时表。从Oracle 19c中,您还可以更新常规表,我们称这个特性为DML重定向。原理是
  1.更新被重定向到主库
  2.在主库上更新后,生成并发送该更新到备库
  3.重定向更新在提交之前仅对会话可见,提交后对所有会话可见,满足ACID的要求
要启用这个功能,需满足一下几点
1.不能是sys或system等common user
2.使用SRL(standby redo logfile)
3.如果使用了CDB,要确保主库所有的PDB处于read write状态,备库所有PDB处于read only状态
4.备库必须启用real time apply
如何开启DML Redirect
控制该功能的初始化参数是ADG_REDIRECT_DML,我们可以在会话级别或数据库级别设置
会话级别
SQL> alter session enable adg_redirect_dml;
数据库级别
SQL> alter system set adg_redirect_dml=true scope=both;
测试DML Redirect
SQL> select database_role,open_mode from v$database;
SQL> alter session enable adg_redirect_dml;

如果在此时我们禁用adg_redirect_dml,插入数据就会报错

异常情况和解决办法

常见的错误代码是ORA-16397,我们也可以看下Oracle的官方解释,可根据实际情况,适当调整

[oracle@xd08mdb01 ~]$ 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.
[oracle@xd08mdb01 ~]$

如果要执行CREATE TABLE等操作,需求启用adg_redirect_plsql

alter session enable adg_redirect_plsql;
begin
execute immediate 'create table test (a number)';
end;
/


Reference

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/managing-oracle-data-guard-physical-standby-databases.html#GUID-8AAD002C-ED06-4349-8BB5-EC8DB30B2628

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ADG_REDIRECT_DML.html#GUID-AC98F026-33BE-41FE-8F2F-EFA296723AD8


关键词:adg 

相关文章

基于PDB的Active Data Guard(Oracle 21.7+)
19c新特性之ADG DML重定向功能
Oracle事务卫士(Transaction Guard)和应用连续性(Application Continuity)
在Oracle RAC ADG中通过opatchauto应用补丁(19.5.0-->19.5.1)
Offload Backup on Physical Standby
Oracle DataGuard feature and workshop
expdp on physical standby
Oracle Database 12c之后DataGuard环境中的PDB操作
Oracle DataGuard in 18c & 19c
Oracle Active DataGuard 12.2新特性揭秘
Active Data Guard新功能演示
Oracle MAA汇总
Top