[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]

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

相關文章