[TOC]

# 您需要知道的

- 您需要熟悉如何去搭建 Linux 作業系統，可參考 OS-INS 類相關文件完成此操作
- 您需要熟悉如何在 Linux 作業系統上安裝 Oracle 資料庫，可參考 ORACLE-INS-FS 類相關文件完成此操作
- 本文不適合零基礎，如果可以，請按照上述兩條進行逐步學習
- 本文采用的是 ORACLE 19c 版本在紅帽 RHEL 8.2 上進行的容器資料庫CDB一主一備環境搭建

# 1 主機環境規劃

> 主庫和備庫的 DB_NAME 必須一致

| 規劃        | 主庫         |  備庫       |
| :-----:      | :-----:      | :----:      |
| 資料庫版本  | 19.3.0.0.0   | 19.3.0.0.0  |
| 是否為CDB   | Yes/CDB      | Yes/CDB     |
| 主機名      | henry  | henrys |
| IP地址      | 192.168.1.82 | 192.168.1.83|
| DB_UNIQUE_NAME |  cdb1   |  cdb1s     |
| DB_NAME     | `cdb1 `      |  `cdb1` |
| INSTANCE_NAME | cdb1     |  cdb1s     |
| Data Files  | /oracle/app/oracle/oradata/CDB1 | /oracle/app/oracle/oradata/CDB1S |
| Log Files   |/oracle/app/oracle/oradata/CDB1 |/oracle/app/oracle/oradata/CDB1S |
| OS        |    RHEL 8.2       |  RHEL 8.2  |

# 2 作業系統安裝

- 主庫OS安裝，請參考 G005-OS-INS-02 一文，注意主機名及IP設定（主）
- 備庫OS安裝，請參考 G005-OS-INS-02 一文，注意主機名及IP設定（備）

# 3 資料庫安裝

> 主備中，主庫是完整的資料庫軟體及資料庫；備庫僅安裝資料庫軟體即可。

## 3.1 主庫安裝

- 您可參考 G006-ORACLE-INS-SIFS-01 一文來完成主庫安裝

## 3.2 備庫安裝

> 備庫只需完成 1-3 部分的資料庫軟體安裝即可，注意備庫主機名、IP及環境變數

- 您可參考 G006-ORACLE-INS-SIFS-01 一文來完成備庫安裝

```shell
# 備庫主機名
[root@henrys ~]# hostname
henrys

# 備庫IP地址
[root@henrys ~]# ifconfig
inet 192.168.1.83

# /etc/hosts
[root@henrys ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.83 henrys

# 備庫環境變數

[root@henrys scripts]# vim setEnv.sh

export ORACLE_HOSTNAME=henrys
export ORACLE_UNQNAME=CDB1S
export ORACLE_SID=CDB1S
```

# 4 Data Guard 搭建

## 4.1 主庫配置
### 4.1.1 主庫強制日誌

```sql
SQL> alter database force logging;
```
### 4.1.2 主庫開啟歸檔

```sql
SQL> archive log list;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> !mkdir /oracle/arch
SQL> alter system set log_archive_dest_1='location=/oracle/arch';
SQL> alter database open;
```

### 4.1.3 主庫建立STANDBY日誌組

```sql
# SQL> set linesize 300
# SQL> col members for 99
# SQL> col member for a55
# SQL> col group# for 99
# SQL> select a.group#,a.sequence#,b.status,a.members,b.member,a.bytes/1024/1024 M from v$log a,v$logfile b where a.group#=b.group# order by a.group#;

SQL> alter system set standby_file_management=manual;
SQL> alter database add standby logfile group 4 '/oracle/app/oracle/oradata/CDB1/redo04.log' size 200M;
SQL> alter database add standby logfile group 5 '/oracle/app/oracle/oradata/CDB1/redo05.log' size 200M;
SQL> alter database add standby logfile group 6 '/oracle/app/oracle/oradata/CDB1/redo06.log' size 200M;
SQL> alter database add standby logfile group 7 '/oracle/app/oracle/oradata/CDB1/redo07.log' size 200M;
SQL> alter system set standby_file_management=auto;

SQL> select group#,type,member from v$logfile order by group#;

GROUP# 	TYPE    MEMBER
---------- ------- -------------------------------------------------------
	 1 ONLINE  /oracle/app/oracle/oradata/CDB1/redo01.log
	 2 ONLINE  /oracle/app/oracle/oradata/CDB1/redo02.log
	 3 ONLINE  /oracle/app/oracle/oradata/CDB1/redo03.log
	 4 STANDBY /oracle/app/oracle/oradata/CDB1/redo04.log
	 5 STANDBY /oracle/app/oracle/oradata/CDB1/redo05.log
	 6 STANDBY /oracle/app/oracle/oradata/CDB1/redo06.log
	 7 STANDBY /oracle/app/oracle/oradata/CDB1/redo07.log
```

### 4.1.4 主庫修改引數檔案

```sql
# 透過當前動態引數檔案（SPFILE）建立靜態引數檔案（PFILE）
# !ls $ORACLE_HOME/dbs/
SQL> create pfile from spfile;
# !ls $ORACLE_HOME/dbs/
# 修改生成的靜態引數檔案 vim initCDB1.ora

CDB1.__data_transfer_cache_size=0
CDB1.__db_cache_size=1325400064
CDB1.__inmemory_ext_roarea=0
CDB1.__inmemory_ext_rwarea=0
CDB1.__java_pool_size=0
CDB1.__large_pool_size=16777216
CDB1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
CDB1.__pga_aggregate_target=637534208
CDB1.__sga_target=1879048192
CDB1.__shared_io_pool_size=100663296
CDB1.__shared_pool_size=419430400
CDB1.__streams_pool_size=0
CDB1.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/CDB1/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/CDB1/control01.ctl','/oracle/app/oracle/oradata/CDB1/control02.ctl'
*.db_block_size=8192
*.db_name='CDB1'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1XDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='location=/oracle/arch'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=596m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1787m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

# 以下是追加引數 #
DB_NAME=CDB1
DB_UNIQUE_NAME=CDB1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB1,CDB1S)'
CONTROL_FILES='/oracle/app/oracle/oradata/CDB1/control01.ctl','/oracle/app/oracle/oradata/CDB1/control02.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1'
LOG_ARCHIVE_DEST_2='SERVICE=CDB1S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1S'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_CLIENT=CDB1
FAL_SERVER=CDB1S
DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1S','/oracle/app/oracle/oradata/'CDB1''
LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1S','/oracle/app/oracle/oradata/'CDB1''
STANDBY_FILE_MANAGEMENT=AUTO
```

透過新的靜態引數檔案PFILE **initCDB1.ora** 生成新的動態引數檔案SPFILE **spfileCDB1.ora** 並重新啟動資料庫進行引數載入

```sql
SQL> shutdown immediate;
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initCDB1.ora';
SQL> startup
```

### 4.1.5 主庫配置TNS

```sql
# 新增備庫連線字串

[oracle@henry admin]$ vim tnsnames.ora

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

CDB1S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1S)
    )
  )
```

### 4.1.6 主庫配置靜態監聽

```sql
[oracle@henry admin]$ vim listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = henry)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = CDB1)
                (ORACLE_HOME = /oracle/app/oracle/product/19.0.0/dbhome_1)
                (SID_NAME = CDB1)
        )
)

# 重啟監聽

# lsnrctl status
# lsnrctl stop
# lsnrctl start

Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

# 測試連線

[oracle@henry admin]$ sqlplus sys/oracle@cdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 30 11:48:14 2020
Version 19.3.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 
```

## 4.2 備庫配置
### 4.2.1 備庫修改引數檔案

```sql
# cd $ORACLE_HOME/dbs
# 主備引數進行互換 vim initCDB1S.ora
CDB1S.__data_transfer_cache_size=0
CDB1S.__db_cache_size=1325400064
CDB1S.__inmemory_ext_roarea=0
CDB1S.__inmemory_ext_rwarea=0
CDB1S.__java_pool_size=0
CDB1S.__large_pool_size=16777216
CDB1S.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
CDB1S.__pga_aggregate_target=637534208
CDB1S.__sga_target=1879048192
CDB1S.__shared_io_pool_size=100663296
CDB1S.__shared_pool_size=419430400
CDB1S.__streams_pool_size=0
CDB1S.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/CDB1S/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/CDB1S/control01.ctl','/oracle/app/oracle/oradata/CDB1S/control02.ctl'
*.db_block_size=8192
*.db_name='CDB1'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1SXDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='location=/oracle/arch'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=596m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1787m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
DB_NAME=CDB1
DB_UNIQUE_NAME=CDB1S
LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB1S,CDB1)'
CONTROL_FILES='/oracle/app/oracle/oradata/CDB1S/control01.ctl','/oracle/app/oracle/oradata/CDB1S/control02.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1S'
LOG_ARCHIVE_DEST_2='SERVICE=CDB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_CLIENT=CDB1S
FAL_SERVER=CDB1
DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1','/oracle/app/oracle/oradata/CDB1S'
LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1','/oracle/app/oracle/oradata/CDB1S'
STANDBY_FILE_MANAGEMENT=AUTO

# 備庫建立對應目錄
mkdir -p /oracle/app/oracle/admin/CDB1S/adump
mkdir -p /oracle/app/oracle/oradata/CDB1S
mkdir /oracle/arch
```

### 4.2.2 備庫配置TNS

```sql
# 進入備庫目錄
cd $ORACLE_HOME/network/admin

# 複製主庫檔案至備庫
scp root@192.168.1.82:/oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora .

The authenticity of host '192.168.1.82 (192.168.1.82)' can't be established.
ECDSA key fingerprint is SHA256:IsxJz8PQu+9spn5xbVGzZkkqJ36x3AJIkYqW7ECxbW8.
Are you sure you want to continue connecting (yes/no/[fingerprint])?  yes
Warning: Permanently added '192.168.1.82' (ECDSA) to the list of known hosts.
root@192.168.1.82's password:
tnsnames.ora                           100%  355   235.7KB/s   00:00

[oracle@henrys admin]$ cat tnsnames.ora

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

CDB1S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1S)
    )
  )

# 嘗試在備庫透過連線字串登入主庫

[oracle@henrys admin]$ sqlplus sys/oracle@cdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 30 12:10:44 2020
Version 19.3.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
```

### 4.2.3 備庫配置監聽

```sql

# 進入備庫目錄
cd $ORACLE_HOME/network/admin

# 複製主庫檔案至備庫
scp root@192.168.1.82:/oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora .

# 注意備庫中的 （對應的主機IP、GLOBAL_DBNAME、ORACLE_HOME和SID_NAME） vim listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = CDB1S)
                (ORACLE_HOME = /oracle/app/oracle/product/19.0.0/dbhome_1)
                (SID_NAME = CDB1S)
        )
)
```

### 4.2.4 備庫配置密碼檔案

```sql
# 進入備庫目錄
[oracle@henrys admin]$ cd $ORACLE_HOME/dbs/

# 複製主庫密碼檔案至備庫
[oracle@henrys dbs]$ scp root@192.168.1.82:/oracle/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCDB1 .

# 重新命名備庫密碼檔案
[oracle@henrys dbs]$ mv orapwCDB1 orapwCDB1S
```

### 4.2.5 備庫建立動態引數檔案

- 備庫重啟監聽，測試TNS連線字串

```sql
lsnrctl stop
lsnrctl start

Services Summary...
Service "CDB1S" has 1 instance(s).
  Instance "CDB1S", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle@henrys ~]$ sqlplus sys/oracle@cdb1s as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 30 12:24:48 2020
Version 19.3.0.0.0

Connected to an idle instance.

SQL>
```

- 備庫建立動態引數檔案

```sql
SQL> !ls $ORACLE_HOME/dbs
initCDB1S.ora  init.ora  orapwCDB1S

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initCDB1S.ora';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1879046112 bytes
Fixed Size		    9136096 bytes
Variable Size		  436207616 bytes
Database Buffers	 1426063360 bytes
Redo Buffers		    7639040 bytes
SQL>
```

## 4.3 DG 建立物理備庫

### 4.3.1 透過 RMAN 備份方式建立 DG 備庫

- 主庫進行 RMAN 全備

```sql
mkdir /oracle/bak
rman target /
RMAN> show all;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/bak/%F.ctl';

RMAN> backup database format '/oracle/bak/%s_%d_%U.full';
```

- 主庫備份檔案複製至備庫相同目錄中

```sql
scp root@192.168.1.82:/oracle/bak/* .
```

- 透過 RMAN 還原備庫（命令可在主庫執行，也可在備庫執行）

```sql
RMAN> rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb1s
RMAN> duplicate target database for standby;
```

### 4.3.2 透過 Active 複製技術建立 DG 備庫

- 命令可在主庫執行，也可在備庫執行

```sql
RMAN> rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb1s
RMAN> duplicate target database for standby from active database;
```

- 本次採用後者方式建立，過程記錄如下

```sql
[oracle@henrys ~]$ rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb1s

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Oct 30 13:00:10 2020
Version 19.3.0.0.0

connected to target database: CDB1 (DBID=1028799411)
connected to auxiliary database: CDB1 (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 30-OCT-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/oracle/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCDB1S'   ;
}
executing Memory Script

Starting backup at 30-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
Finished backup at 30-OCT-20

contents of Memory Script:
{
   restore clone from service  'cdb1' standby controlfile;
}
executing Memory Script

Starting restore at 30-OCT-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/app/oracle/oradata/CDB1S/control01.ctl
output file name=/oracle/app/oracle/oradata/CDB1S/control02.ctl
Finished restore at 30-OCT-20

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/oracle/app/oracle/oradata/CDB1S/temp01.dbf";
   set newname for tempfile  2 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/temp012020-10-29_14-22-15-414-PM.dbf";
   set newname for tempfile  3 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/oracle/app/oracle/oradata/CDB1S/system01.dbf";
   set newname for datafile  3 to 
 "/oracle/app/oracle/oradata/CDB1S/sysaux01.dbf";
   set newname for datafile  4 to 
 "/oracle/app/oracle/oradata/CDB1S/undotbs01.dbf";
   set newname for datafile  5 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/system01.dbf";
   set newname for datafile  6 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to 
 "/oracle/app/oracle/oradata/CDB1S/users01.dbf";
   set newname for datafile  8 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/system01.dbf";
   set newname for datafile  10 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/sysaux01.dbf";
   set newname for datafile  11 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/undotbs01.dbf";
   set newname for datafile  12 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/users01.dbf";
   restore
   from  nonsparse   from service 
 'cdb1'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oracle/app/oracle/oradata/CDB1S/temp01.dbf in control file
renamed tempfile 2 to /oracle/app/oracle/oradata/CDB1S/pdbseed/temp012020-10-29_14-22-15-414-PM.dbf in control file
renamed tempfile 3 to /oracle/app/oracle/oradata/CDB1S/PDB1/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 30-OCT-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/CDB1S/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/CDB1S/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/CDB1S/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/app/oracle/oradata/CDB1S/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oracle/app/oracle/oradata/CDB1S/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oracle/app/oracle/oradata/CDB1S/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oracle/app/oracle/oradata/CDB1S/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /oracle/app/oracle/oradata/CDB1S/PDB1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oracle/app/oracle/oradata/CDB1S/PDB1/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /oracle/app/oracle/oradata/CDB1S/PDB1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /oracle/app/oracle/oradata/CDB1S/PDB1/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-OCT-20

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/users01.dbf
Finished Duplicate Db at 30-OCT-20
```

# 5 主備同步測試

## 5.1 備庫開啟應用

> 備庫預設狀態為 MOUNT，開啟同步應用後，僅同步應用日誌，無法提供實時業務資料查詢

- 確認主備環境

```sql
# 主庫狀態查詢
SQL> select database_role,open_mode,protection_mode from v$database;

DATABASE_ROLE    OPEN_MODE        	PROTECTION_MODE
---------------- -------------------- --------------------
PRIMARY          READ WRITE       	MAXIMUM PERFORMANCE

# 備庫狀態查詢
SQL> select database_role,open_mode,protection_mode from v$database;

DATABASE_ROLE    OPEN_MODE        	PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED          	MAXIMUM PERFORMANCE
```

- 備庫開啟應用

```sql
SQL> alter database recover managed standby database disconnect from session;
```

- 主庫切日誌並查詢當前最新歸檔

```sql
SQL> alter system switch logfile;

System altered.

SQL> select unique thread#,max(sequence#) over(partition by thread#) last from v$archived_log;

THREAD#	 LAST
---------- ----------
	 1	   	18
```

- 備庫查詢當前最新歸檔

```sql
SQL> select unique thread#,max(sequence#) over(partition by thread#) last from v$archived_log;

THREAD#	 LAST
---------- ----------
	 1	   	18
```

## 5.2 備庫開啟ADG

> ADG （Active Data Guard）作為獨立產品功能包含在 EE 企業版本中，其它版本不支援。備庫開啟 ADG 功能後，可進行同步查詢。

- 備庫取消同步

```sql
SQL> alter database recover managed standby database cancel;
```

- 備庫開啟 ADG

```sql
SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.
```

- 備庫開啟 PDB1 資料庫

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

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

- 主庫 PDB1 插入測試資料

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

Session altered.

SQL> create table askscuti(id number);

Table created.

SQL> insert into askscuti values(1);

1 row created.

SQL> commit;

Commit complete.
```

- 備庫 PDB1 查詢

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

Session altered.

SQL> select * from askscuti;

ID
----------
	 1
```

## 5.3 關於實時應用的說明

> 2.5.6.5 Real-Time Apply is Default Setting for Data Guard
In previous releases, when creating a Data Guard configuration using the SQL command line, the default configuration was to apply redo from archived log files on the standby database. In Oracle Database 12c Release 1 (12.1), the default configuration is to use real-time apply so that redo is applied directly from the standby redo log file.

- 在12c之前版本中，MRP的預設配置是對備用資料庫上的歸檔日誌檔案進行應用重做。從Oracle資料庫12c Release 1（12.1）開始，預設配置是使用實時應用（real-time apply），直接從備用重做日誌檔案應用重做。當前版本為19c，備庫開啟同步後，預設為實時應用。

- 備庫開啟應用語句

```sql
SQL> alter database recover managed standby database disconnect;(應用線上日誌實時同步)
SQL> alter database recover managed standby database using archived logfile disconnect;（應用歸檔日誌非實時同步）

# 查詢當前備庫是否為實時應用（MANAGED REAL TIME APPLY 代表實時應用）
SQL> select dest_name,status,recovery_mode from v$archive_dest_status;

DEST_NAME            STATUS    RECOVERY_MODE
-------------------- --------- ----------------------------------
LOG_ARCHIVE_DEST_1   VALID     MANAGED REAL TIME APPLY
```

- 12c之前版本中，如11g開啟實時應用，需執行以下命令

```sql
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
```

[End]

G007-ORACLE-INS-DG-01 ORACLE 19C DG 1P1S-CDB Ins ON RHEL 8.2

相關文章