1 规划

规划	主库	备库
数据库版本	12.2.0.1	12.2.0.1
软件情况	软件 + ERP数据库	仅软件
容器数据库	CDB	CDB
主机名	host01	host02
IP地址	192.168.1.12	192.168.1.13
DB_UNIQUE_NAME	erpdb	erpdbs
DB_NAME	`erpdb`	`erpdb`
INSTANCE_NAME	erpdb	erpdbs
Data Files	E:\APP\ADMINISTRATOR\ORADATA\ERPDB	E:\APP\ADMINISTRATOR\ORADATA\ERPDB
Log Files	E:\APP\ADMINISTRATOR\ORADATA\ERPDB	E:\APP\ADMINISTRATOR\ORADATA\ERPDB
OS	Windows Server 2019	Windows Server 2019

注意：主库和备库的 DB_NAME 必须一致

2 OS安装及数据库搭建

主库OS安装，请参考 109 文档，注意主机名及IP设置（主）
备库OS安装，请参考 109 文档，注意主机名及IP设置（备无需建库）

3 Data Guard 搭建

3.1 主库配置

3.1.1 主库强制日志

C:\Users\Administrator>set nls_lang=american_america.zhs16gbk
C:\Users\Administrator>sqlplus / as sysdba

SQL> alter database force logging;

3.1.2 主库开启归档

SQL> alter system set db_recovery_file_dest_size=5g;SQL> alter system set db_recovery_file_dest='E:\app\Administrator\archive';SQL> shutdown immediate;SQL> startup mount;SQL> alter database archivelog;SQL> alter database open;

3.1.3 主库创建STANDBY日志组

# 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#;

alter system set standby_file_management='manual';

alter database add standby logfile group 4 'E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO04.LOG' size 200m;
alter database add standby logfile group 5 'E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO05.LOG' size 200m;
alter database add standby logfile group 6 'E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO06.LOG' size 200m;
alter database add standby logfile group 7 'E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO07.LOG' size 200m;

alter system set standby_file_management='auto';

# SQL> set linesize 200
# SQL> col group# for 99
# SQL> col member for a50
# SQL> select group#,type,member from v$logfile order by group#;

GROUP# TYPE         MEMBER
------ ----------   ----------------------------------------------
     1 ONLINE       E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO01.LOG
     2 ONLINE       E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO02.LOG
     3 ONLINE       E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO03.LOG
     4 STANDBY      E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO04.LOG
     5 STANDBY      E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO05.LOG
     6 STANDBY      E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO06.LOG
     7 STANDBY      E:\APP\ADMINISTRATOR\ORADATA\ERPDB\REDO07.LOG

3.1.4 主库修改参数文件

# 通过当前动态参数文件（SPFILE）创建静态参数文件（PFILE）
SQL> create pfile from spfile;
# E:\app\Administrator\product\12.2.0\dbhome_1\database
# 修改生成的静态参数文件 INITERPDB.ORA

# 以下是原有参数 #

erpdb.__data_transfer_cache_size=0
erpdb.__db_cache_size=1862270976
erpdb.__inmemory_ext_roarea=0
erpdb.__inmemory_ext_rwarea=0
erpdb.__java_pool_size=16777216
erpdb.__large_pool_size=33554432
erpdb.__oracle_base='E:\app\Administrator'#ORACLE_BASE set from environment
erpdb.__pga_aggregate_target=872415232
erpdb.__sga_target=2583691264
erpdb.__shared_io_pool_size=134217728
erpdb.__shared_pool_size=520093696
erpdb.__streams_pool_size=0
*.audit_file_dest='E:\app\Administrator\admin\erpdb\adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='E:\app\Administrator\oradata\erpdb\control01.ctl','E:\app\Administrator\oradata\erpdb\control02.ctl'
*.db_block_size=8192
*.db_name='erpdb'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='E:\app\Administrator\archive'
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=erpdbXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ERPDB'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=819m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2457m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'

# 以下是追加参数 #

DB_UNIQUE_NAME='erpdb'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(erpdb,erpdbs)'
CONTROL_FILES='E:\APP\ADMINISTRATOR\ORADATA\ERPDB\CONTROL01.CTL', 'E:\APP\ADMINISTRATOR\ORADATA\ERPDB\CONTROL02.CTL'
LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=erpdb'
LOG_ARCHIVE_DEST_2= 'SERVICE=erpdbs ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=erpdbs'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
FAL_SERVER=erpdbs
DB_FILE_NAME_CONVERT='E:\APP\ADMINISTRATOR\ORADATA\ERPDBS','E:\APP\ADMINISTRATOR\ORADATA\ERPDB'
LOG_FILE_NAME_CONVERT='E:\APP\ADMINISTRATOR\ORADATA\ERPDBS','E:\APP\ADMINISTRATOR\ORADATA\ERPDB' 
STANDBY_FILE_MANAGEMENT=AUTO

# 注意 DB_FILE_NAME_CONVERT 和 LOG_FILE_NAME_CONVERT 两个参数，指定位置必须备在前，主在后。

通过新的标准静态参数文件PFILE INITERPDB.ORA 生成新的动态参数文件SPFILE SPFILEERPDB.ORA 并重新启动数据库进行参数加载

SQL> shutdown immediate;SQL> create spfile from pfile;SQL> startup

3.1.5 主库配置TNS

# E:\app\Administrator\product\12.2.0\dbhome_1\network\admin
# 修改 tnsnames.ora
# 主库原有连接字符串，添加备库连接字符串

ERPDBS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = erpdbs)
    )
  )

3.1.6 主库配置监听（静态）

# C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
# 修改 listener.ora
# 在 SID_LIST 标签中添加以下描述信息

(SID_DESC=
    (GLOBAL_DBNAME=erpdb)
    (SID_NAME=erpdb)            
    (ORACLE_HOME=E:\app\Administrator\product\12.2.0\dbhome_1)
)

# 主库修改后内容

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\Administrator\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC=
        (GLOBAL_DBNAME=erpdb)
        (SID_NAME=erpdb)            
        (ORACLE_HOME=E:\app\Administrator\product\12.2.0\dbhome_1)
    )
  )

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

# 重启监听测试连接

# C:\Users\Administrator>lsnrctl stop
# C:\Users\Administrator>lsnrctl start

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

# C:\Users\Administrator>sqlplus sys/oracle@erpdb as sysdba

3.2 备库配置

3.2.1 备库修改参数文件

# 直接拷贝主库静态参数文件 INITERPDB.ORA 到备库并更名 INITERPDBS.ORA
# 备库路径 E:\app\Administrator\product\12.2.0\dbhome_1\database
# 修改备库参数文件 INITERPDB.ORA 参数进行主备互换（注意 db_name 保持一致）

*.audit_file_dest='E:\app\Administrator\admin\erpdbs\adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='E:\app\Administrator\oradata\erpdbs\control01.ctl','E:\app\Administrator\oradata\erpdbs\control02.ctl'
*.db_block_size=8192
*.db_name='erpdb'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='E:\app\Administrator\archive'
*.diagnostic_dest='E:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=erpdbsXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_erpdbs'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=819m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2457m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME='erpdbs'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(erpdbs,erpdb)'
CONTROL_FILES='E:\APP\ADMINISTRATOR\ORADATA\erpdbs\CONTROL01.CTL', 'E:\APP\ADMINISTRATOR\ORADATA\erpdbs\CONTROL02.CTL'
LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=erpdbs'
LOG_ARCHIVE_DEST_2= 'SERVICE=erpdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=erpdb'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
FAL_SERVER=erpdb
DB_FILE_NAME_CONVERT='E:\APP\ADMINISTRATOR\ORADATA\erpdb','E:\APP\ADMINISTRATOR\ORADATA\erpdbs'
LOG_FILE_NAME_CONVERT='E:\APP\ADMINISTRATOR\ORADATA\erpdb','E:\APP\ADMINISTRATOR\ORADATA\erpdbs' 
STANDBY_FILE_MANAGEMENT=AUTO

# 创建对应目录

E:\app\Administrator\admin\erpdbs
E:\app\Administrator\admin\erpdbs\adump
E:\app\Administrator\oradata\erpdbs
E:\app\Administrator\archive

3.2.2 备库配置TNS

# 可直接拷贝主库内容，也可以通过 Net Manager 工具进行创建
# E:\app\Administrator\product\12.2.0\dbhome_1\network\admin
# 修改 tnsnames.ora 

ERPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = erpdb)
    )
  )

ERPDBS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = erpdbs)
    )
  )

# 尝试在备库通过连接字符串登录主库
# C:\Users\Administrator>sqlplus sys/oracle@erpdb as sysdba

3.2.3 备库配置监听

# 参考主库内容，添加静态监听
# E:\app\Administrator\product\12.2.0\dbhome_1\network\admin
# 修改 listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\Administrator\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC=
        (GLOBAL_DBNAME=erpdbs)
        (SID_NAME=erpdbs)           
        (ORACLE_HOME=E:\app\Administrator\product\12.2.0\dbhome_1)
    )
  )

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

# 重启监听测试连接

# C:\Users\Administrator>lsnrctl stop
# C:\Users\Administrator>lsnrctl start

3.2.4 备库创建密码文件

# 可直接拷贝主库密码文件 PWDerpdb.ora 并进行更名 PWDerpdbs.ora
# 也可以通过命令创建

C:\Users\Administrator>orapwd file=E:\app\Administrator\product\12.2.0\dbhome_1\database\PWDerpdbs.ora password=oracle entries=20 format=12 force=y

3.2.5 备库手工创建实例

C:\Users\Administrator>oradim -new -sid erpdbsInstance created.

3.3 在线复制创建DG备库

3.3.1 将备库启动到 nomount

C:\Users\Administrator>set nls_lang=american_america.zhs16gbk

C:\Users\Administrator>sqlplus sys/oracle@erpdbs as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jun 26 20:23:52 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2583691264 bytes
Fixed Size                  8922232 bytes
Variable Size             687868808 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7852032 bytes

3.3.2 在线复制

# 以下命令可在 主库 中执行，也可在 备库 中执行

C:\Users\Administrator>rman target sys/oracle@erpdb auxiliary sys/oracle@erpdbs

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jun 26 20:26:25 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ERPDB (DBID=193221351)
connected to auxiliary database: ERPDB (not mounted)

RMAN> duplicate target database for standby from active database;

# 过程记录

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

contents of Memory Script:
{
   backup as copy reuse
   targetfile  'E:\app\Administrator\product\12.2.0\dbhome_1\DATABASE\PWDerpdb.ORA' auxiliary format
 'E:\app\Administrator\product\12.2.0\dbhome_1\DATABASE\PWDerpdbs.ORA'   ;
}
executing Memory Script

Starting backup at 26-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
Finished backup at 26-JUN-20

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

Starting restore at 26-JUN-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 erpdb
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\CONTROL01.CTL
output file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\CONTROL02.CTL
Finished restore at 26-JUN-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
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\TEMP01.DBF";
   set newname for tempfile  2 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\TEMP012020-06-26_18-02-49-653-PM.DBF";
   set newname for tempfile  3 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SYSTEM01.DBF";
   set newname for datafile  2 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\SYSTEM01.DBF";
   set newname for datafile  3 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SYSAUX01.DBF";
   set newname for datafile  4 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\SYSAUX01.DBF";
   set newname for datafile  5 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\UNDOTBS01.DBF";
   set newname for datafile  6 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\UNDOTBS01.DBF";
   set newname for datafile  7 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\USERS01.DBF";
   set newname for datafile  8 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\SYSTEM01.DBF";
   set newname for datafile  9 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\SYSAUX01.DBF";
   set newname for datafile  10 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\UNDOTBS01.DBF";
   set newname for datafile  11 to
 "E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\USERS01.DBF";
   restore
   from  nonsparse   from service
 'erpdb'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\TEMP01.DBF in control file
renamed tempfile 2 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\TEMP012020-06-26_18-02-49-653-PM.DBF in control file
renamed tempfile 3 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-JUN-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 erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\UNDOTBS01.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 erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\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 erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service erpdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\USERS01.DBF
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-JUN-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=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\SYSTEM01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\SYSAUX01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\UNDOTBS01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\PDBSEED\UNDOTBS01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\USERS01.DBF
datafile 8 switched to datafile copy
input datafile copy RECID=11 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\SYSTEM01.DBF
datafile 9 switched to datafile copy
input datafile copy RECID=12 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\SYSAUX01.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=13 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\UNDOTBS01.DBF
datafile 11 switched to datafile copy
input datafile copy RECID=14 STAMP=1044131520 file name=E:\APP\ADMINISTRATOR\ORADATA\ERPDBS\SALES\USERS01.DBF
Finished Duplicate Db at 26-JUN-20

4 备库同步测试

4.1 备库 MOUNT 状态开启日志同步应用

备库创建好之后，默认状态为 MOUNT，开启同步后，仅同步日志，是无法提供业务数据查询的

# 主库状态查询

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

4.2 备库开启 ADG

ADG （Active Data Guard）作为独立产品功能包含在 EE 企业版本中，其它版本不支持。备库开启 ADG 功能后，可进行同步查询。

备库取消日志应用

SQL> alter database recover managed standby database cancel;

备库开启 ADG

# 打开备库为OPEN模式

SQL> alter database open;

4.2.1 实时应用（Online Redo Log）

同步查询测试

# 备库开启日志应用(12c开始默认为实时应用)

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

# 主库打开PDB创建用户

SQL> alter pluggable database sales open;
SQL> alter session set container=sales;
SQL> create user scott identified by oracle;

# 备库打开PDB进行查询

SQL> alter pluggable database sales open;
SQL> alter session set container=sales;
SQL> select username from dba_users where username='SCOTT';

USERNAME
-----------
SCOTT

4.2.2 非实时应用（Archived Log）

同步查询测试

# 备库取消实时应用

SQL> alter database recover managed standby database cancel;

# 备库开启非实时应用

SQL> alter database recover managed standby database using archived logfile disconnect;

# 主库连接 PDB 创建表

SQL> show pdbs

    CON_ID CON_NAME      OPEN MODE  RESTRICTED
---------- ------------- ---------- ----------
         3 SALES         READ WRITE NO

SQL> create table scuti as select 1 as a from dual;

Table created.

# 备库连接 PDB 查看表

SQL> show pdbs

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

SQL> alter session set container=sales;

Session altered.

SQL> select * from scuti;
select * from scuti
              *
ERROR at line 1:
ORA-00942: table or view does not exist

# 查不到的原因是因为使用的是备库的归档日志进行的应用，所以只有当主库发生日志切换后才会应用。

# 主库连接 CDB 进行日志切换（日志切换无法在PDB级别完成）

SQL> conn sys/oracle@erpdb as sysdba
SQL> alter system switch logfile;

# 备库再次查询

SQL> select * from scuti;

         A
----------
         1

4.2.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），直接从备用重做日志文件应用重做。当前版本为12cR2，备库开启同步后，默认为非实时应用。

# 备库开启实时应用

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect [from session]; # 中括号内容可省略

Database altered.

# 查询是否为实时应用

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

# MANAGED REAL TIME APPLY 代表实时应用