[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 Copyright (c) 1982, 2019, Oracle. All rights reserved. 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 Copyright (c) 1982, 2019, Oracle. All rights reserved. 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 Copyright (c) 1982, 2019, Oracle. All rights reserved. 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 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. 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 executing command: SET NEWNAME 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 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 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]