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

ITPub博客

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

最新文章