[TOC]

# 您需要知道的

- ORACLE 19c 版本中 ADG 备库 DML 重定向功能，一个很 Interesting 的新特性
- Data Guard 环境是基于CDB模式，主库 CDB1（PDB1），备库 CDB1（PDB1）
- 实验是在备库的 PDB1 数据库中进行的 DML 重定向
- 重定向功能不支持 SYS 用户
- 如有必要，您可参考 [G007-ORACLE-INS-DG-01](http://blog.itpub.net/69986162/viewspace-2731012/) 一文构建本实验环境

# 1 备库DML重定向流程

![](http://img.blog.itpub.net/blog/2020/10/30/17c9621a4f825c7a.png?x-oss-process=style/bb)

- 1 备库执行 DML 语句
- 2 语句被重定向到主库
- 3 主库应用 DML 语句
- 4 数据更改流传备库执行
- 5 数据展示给客户端

# 2 主库查询条目数

```sql
SQL> alter session set container=pdb1;

Session altered.

SQL> create user askscuti identified by askscuti;

User created.

SQL> grant dba to askscuti;

Grant succeeded.

SQL> conn askscuti/askscuti@pdb1;
Connected.

SQL> create table askscuti as select object_id from dba_objects;

Table created.

SQL> select count(1) from askscuti;

COUNT(1)
----------
     72374
```

# 3 备库查询条目数

```sql
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME			  		 OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 	PDB$SEED			  		 READ ONLY  NO
	 3 	PDB1 			  			READ ONLY  NO

SQL> conn askscuti/askscuti@pdb1;
Connected.

SQL> select count(1) from askscuti;

COUNT(1)
----------
     72374
```

# 4 主库插入数据

```sql
SQL> insert into askscuti values(72375);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(1) from askscuti;

COUNT(1)
----------
     72375
```

# 5 备库插入数据

```sql
SQL> select count(1) from askscuti;

COUNT(1)
----------
     72375

SQL> insert into askscuti values(72376);
insert into askscuti values(72376)
            *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

# 备库开启当前会话重定向功能

SQL> alter session enable adg_redirect_dml;

Session altered.

SQL> insert into askscuti values(72376);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(1) from askscuti;

COUNT(1)
----------
     72376
```

# 6 主库再次查询条目数

```sql
SQL> select count(1) from askscuti;

COUNT(1)
----------
     72376
```

[End]

ITPub博客

G008-ORACLE-DG ORACLE 19C Active Data Guard DML Redirection

最新文章