Linux 7平台搭建Oracle 19.3 物理DG
👉 本文共约1719个字，系统预计阅读时间或需7分钟。
1 环境说明
这里准备2台虚拟机，操作系统安装Redhat 7.7。 其他信息规划如下：
| 源数据库 | 目标数据库 | |
|---|---|---|
| IP地址 | 192.168.74.203 | 192.168.74.204 |
| 数据库SID | cndba | cndba |
| DB_UNIQUE_NAME | dave_p | dave_s |
在2台虚拟机分别安装19.3 的数据库软件，主库创建CDB实例 cndba，PDB实例：dave_p。 备库只安装数据库软件，不创建实例。
2 主库启动FORCE LOGGING
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DAVE READ WRITE NO SQL> col name for a20 SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE -------------------- ------------------------------ PDB$SEED READ ONLY DAVE READ WRITE SQL> select force_logging from v$database; FORCE_LOGGING -------------------------------------------------------------------------------- NO SQL> alter database force logging; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING -------------------------------------------------------------------------------- YES SQL> |
3 主库启动归档模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST SQL> alter system set log_archive_dest_1='location=/u01/archivelog/' scope=spfile ; System altered. SQL>alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DAVE MOUNTED SQL> alter pluggable database dave open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DAVE READ WRITE NO SQL> SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/archivelog/ Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9 SQL> |
4 在主库添加 standby redo logfile
在Oracle 19c的多租户架构里，online redo log 和控制文件是保存在CDB中的，PDB中只有运行需要的数据文件，所以我们这里加standby redo log，也是在CDB中加。
1 2 3 4 5 6 7 | SQL> select group#, members, bytes from v$log; GROUP# MEMBERS BYTES ---------- ---------- ---------- 1 2 209715200 2 2 209715200 3 2 209715200 |
注意这里online redo log大小的变化，详细参考：
Oracle 12cR2 默认 online redo log 的大小从50M 增加到 200M
https://www.cndba.cn/dave/article/2054
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_3_h6go2p4q_.log /u01/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_3_h6go2pn6_.log /u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_2_h6go2p47_.log /u01/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_2_h6go2ph2_.log /u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_1_h6go2p3k_.log /u01/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_1_h6go2pgm_.log 6 rows selected. SQL> alter database add standby logfile '/u01/app/oracle/oradata/CNDBA/stdredo01.log' size 200M; SQL> alter database add standby logfile '/u01/app/oracle/oradata/CNDBA/stdredo02.log' size 200M; SQL> alter database add standby logfile '/u01/app/oracle/oradata/CNDBA/stdredo03.log' size 200M; SQL> alter database add standby logfile '/u01/app/oracle/oradata/CNDBA/stdredo04.log' size 200M; SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_3_h6go2p4q_.log /u01/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_3_h6go2pn6_.log /u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_2_h6go2p47_.log /u01/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_2_h6go2ph2_.log /u01/app/oracle/oradata/CNDBA/onlinelog/o1_mf_1_h6go2p3k_.log /u01/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_1_h6go2pgm_.log /u01/app/oracle/oradata/CNDBA/stdredo01.log /u01/app/oracle/oradata/CNDBA/stdredo02.log /u01/app/oracle/oradata/CNDBA/stdredo03.log /u01/app/oracle/oradata/CNDBA/stdredo04.log 10 rows selected. |
5 在主库创建pfile 文件并修改pfile 内容
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> create pfile from spfile; File created. 在pfile中添加如下内容： [oracle@www.cndba.cn1 ~]$ cd /u01/app/oracle/product/19.3.0/dbhome_1/dbs/ [oracle@www.cndba.cn1 dbs]$ ls hc_cndba.dat initcndba.ora init.ora lkCNDBA orapwcndba spfilecndba.ora [oracle@www.cndba.cn1 dbs]$ vim initcndba.ora [oracle@www.cndba.cn1 dbs]$ cat initcndba.ora ## add for dg *.db_unique_name='cndba_p' *.log_archive_config='dg_config=(cndba_p, cndba_s)' *.log_archive_dest_1='location=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=cndba_p' *.log_archive_dest_2='service=cndba_s lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=cndba_s' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.standby_file_management='auto' *.fal_server='cndba_s' #*.log_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba' #*.db_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba' |
用新参数重启数据库：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [oracle@www.cndba.cn1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 10 20:05:21 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> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> create spfile from pfile; File created. SQL> startup ORACLE instance started. Total System Global Area 1962932472 bytes Fixed Size 9136376 bytes Variable Size 1107296256 bytes Database Buffers 838860800 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. SQL> |
6 分别在主备库配置监听并启动
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | [oracle@www.cndba.cn1 admin]$ pwd /u01/app/oracle/product/19.3.0/dbhome_1/network/admin [oracle@www.cndba.cn1 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cndba) (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1) (SID_NAME = cndba) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521)) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@www.cndba.cn1 admin]$ [oracle@www.cndba.cn1 admin]$ lsnrctl reload LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-MAR-2020 20:20:15 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521))) The command completed successfully [oracle@www.cndba.cn1 admin]$ |
7 分别在主备库配置tnsnames.ora
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [oracle@www.cndba.cn1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. CNDBA_S = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.204)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cndba) ) ) CNDBA_P = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.203)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cndba) ) ) [oracle@www.cndba.cn1 admin]$ |
配置完成后，使用tnsping 命令校验，切记要相互ping 通方可继续后面的操作，如果不通的话检查防火墙有没有关或者1521 端口有没有关，tnsname.ora 配置是否正确，一般是这些原因，防火墙占多数原因。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [oracle@www.cndba.cn2 admin]$ tnsping cndba_p TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-MAR-2020 20:23:58 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.203)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cndba))) OK (0 msec) [oracle@www.cndba.cn2 admin]$ tnsping cndba_s TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 10-MAR-2020 20:23:59 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.204)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cndba))) OK (0 msec) [oracle@www.cndba.cn2 admin]$ |
8 将主库的口令文件copy到备库
这里主备库的CDB实例相同，如果不同，可以使用orapwd命令重建。
1 2 3 4 | [oracle@www.cndba.cn1 dbs]$ scp orapwcndba dg2:`pwd` oracle@www.cndba.cn2's password: orapwcndba 100% 2048 2.8MB/s 00:00 [oracle@www.cndba.cn1 dbs]$ |
9 将主库的参数文件copy到备库并修改
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | --主库操作 [oracle@www.cndba.cn1 dbs]$ scp initcndba.ora dg2:`pwd` oracle@www.cndba.cn2's password: initcndba.ora 100% 1810 2.7MB/s 00:00 [oracle@www.cndba.cn1 dbs]$ --备库修改参数文件添加以下内容在重新生成spfile： ## add for dg *.db_unique_name='cndba_s' *.log_archive_config='dg_config=(cndba_p, cndba_s)' *.log_archive_dest_1='location=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=cndba_s' *.log_archive_dest_2='service=cndba_p lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=cndba_p' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.standby_file_management='auto' *.fal_server='cndba_p' 生成spfile： [oracle@www.cndba.cn2 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 10 20:42:29 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
10 在备库创建必要的目录
根据pfile 文件，将该文件中出现的所有路径全部检查一遍，如果不存在，则创建之。 否在启动备库会报错。
1 2 3 | [oracle@www.cndba.cn2 dbs]$ mkdir -p /u01/app/oracle/admin/cndba/adump [oracle@www.cndba.cn2 dbs]$ mkdir -p /u01/app/oracle/oradata/CNDBA/controlfile [oracle@www.cndba.cn2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/CNDBA/controlfile |
11 用spfile 将备库启动到nomount 状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [oracle@www.cndba.cn2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 10 20:45:21 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 1962932472 bytes Fixed Size 9136376 bytes Variable Size 1107296256 bytes Database Buffers 838860800 bytes Redo Buffers 7639040 bytes SQL> |
12 开始进行Active duplicate
这里可能遇到以下错误，供参考：
Oracle 18c ADG RMAN duplicate ORA-01017错误解决方法
https://www.cndba.cn/dave/article/2994
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 | [oracle@www.cndba.cn2 ~]$ rman target sys/oracle@cndba_p auxiliary sys/oracle@cndba_s; Recovery Manager: Release 19.0.0.0.0 - Production on Tue Mar 10 20:50:35 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: CNDBA (DBID=299163283) connected to auxiliary database: CNDBA (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck dorecover; Starting Duplicate Db at 2020:03:1020:50:51 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=10 device type=DISK current log archived contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwcndba' ; } executing Memory Script Starting backup at 2020:03:1020:50:51 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=146 device type=DISK Finished backup at 2020:03:1020:50:54 duplicating Online logs to Oracle Managed File (OMF) location duplicating Datafiles to Oracle Managed File (OMF) location contents of Memory Script: { sql clone "alter system set control_files = ''/u01/app/oracle/oradata/CNDBA/controlfile/o1_mf_h6go2myb_.ctl'', ''/u01/app/oracle/fast_recovery_area/CNDBA/controlfile/o1_mf_h6go2mz4_.ctl'' comment= ''Set by RMAN'' scope=spfile"; restore clone from service 'cndba_p' standby controlfile; } executing Memory Script sql statement: alter system set control_files = ''/u01/app/oracle/oradata/CNDBA/controlfile/o1_mf_h6go2myb_.ctl'', ''/u01/app/oracle/fast_recovery_area/CNDBA/controlfile/o1_mf_h6go2mz4_.ctl'' comment= ''Set by RMAN'' scope=spfile Starting restore at 2020:03:1020:50:55 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 cndba_p channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/CNDBA/controlfile/o1_mf_h6go2myb_.ctl output file name=/u01/app/oracle/fast_recovery_area/CNDBA/controlfile/o1_mf_h6go2mz4_.ctl Finished restore at 2020:03:1020:50:58 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 clone tempfile 1 to new; set newname for clone tempfile 2 to new; set newname for clone tempfile 3 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; set newname for clone datafile 5 to new; set newname for clone datafile 6 to new; set newname for clone datafile 7 to new; set newname for clone datafile 8 to new; set newname for clone datafile 9 to new; set newname for clone datafile 10 to new; set newname for clone datafile 11 to new; set newname for clone datafile 12 to new; restore from nonsparse from service 'cndba_p' 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 /u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_temp_%u_.tmp in control file renamed tempfile 2 to /u01/app/oracle/oradata/CNDBA_S/A07D3DDE6882372BE053CB4AA8C075F7/datafile/o1_mf_temp_%u_.tmp in control file renamed tempfile 3 to /u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_temp_%u_.tmp 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 2020:03:1020:51:02 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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_undotbs1_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/CNDBA_S/A07D3DDE6882372BE053CB4AA8C075F7/datafile/o1_mf_system_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/CNDBA_S/A07D3DDE6882372BE053CB4AA8C075F7/datafile/o1_mf_sysaux_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_users_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/CNDBA_S/A07D3DDE6882372BE053CB4AA8C075F7/datafile/o1_mf_undotbs1_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_system_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_sysaux_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_undotbs1_%u_.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 cndba_p channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_users_%u_.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 2020:03:1020:51:35 sql statement: alter system archive log current current log archived contents of Memory Script: { restore clone force from service 'cndba_p' archivelog from scn 2378139; switch clone datafile all; } executing Memory Script Starting restore at 2020:03:1020:51:35 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service cndba_p channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=11 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service cndba_p channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=12 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service cndba_p channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=13 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 2020:03:1020:51:38 datafile 1 switched to datafile copy input datafile copy RECID=15 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_h6h39q6b_.dbf datafile 3 switched to datafile copy input datafile copy RECID=16 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_h6h39y8h_.dbf datafile 4 switched to datafile copy input datafile copy RECID=17 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_undotbs1_h6h3b5cw_.dbf datafile 5 switched to datafile copy input datafile copy RECID=18 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/A07D3DDE6882372BE053CB4AA8C075F7/datafile/o1_mf_system_h6h3b8hs_.dbf datafile 6 switched to datafile copy input datafile copy RECID=19 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/A07D3DDE6882372BE053CB4AA8C075F7/datafile/o1_mf_sysaux_h6h3bcj7_.dbf datafile 7 switched to datafile copy input datafile copy RECID=20 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/datafile/o1_mf_users_h6h3bgkx_.dbf datafile 8 switched to datafile copy input datafile copy RECID=21 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/A07D3DDE6882372BE053CB4AA8C075F7/datafile/o1_mf_undotbs1_h6h3bhq3_.dbf datafile 9 switched to datafile copy input datafile copy RECID=22 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_system_h6h3bjvm_.dbf datafile 10 switched to datafile copy input datafile copy RECID=23 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_sysaux_h6h3bkx2_.dbf datafile 11 switched to datafile copy input datafile copy RECID=24 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_undotbs1_h6h3bo0p_.dbf datafile 12 switched to datafile copy input datafile copy RECID=25 STAMP=1034715098 file name=/u01/app/oracle/oradata/CNDBA_S/A07D60C80C1F3EE0E053CB4AA8C073B5/datafile/o1_mf_users_h6h3bp1g_.dbf contents of Memory Script: { set until scn 2378573; recover standby clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 2020:03:1020:51:38 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 11 is already on disk as file /u01/archivelog/arch_1_11_1034700502.arc archived log for thread 1 with sequence 12 is already on disk as file /u01/archivelog/arch_1_12_1034700502.arc archived log for thread 1 with sequence 13 is already on disk as file /u01/archivelog/arch_1_13_1034700502.arc archived log file name=/u01/archivelog/arch_1_11_1034700502.arc thread=1 sequence=11 archived log file name=/u01/archivelog/arch_1_12_1034700502.arc thread=1 sequence=12 archived log file name=/u01/archivelog/arch_1_13_1034700502.arc thread=1 sequence=13 media recovery complete, elapsed time: 00:00:01 Finished recover at 2020:03:1020:51:40 contents of Memory Script: { delete clone force archivelog all; } executing Memory Script released channel: ORA_DISK_1 released channel: ORA_AUX_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=146 device type=DISK deleted archived log archived log file name=/u01/archivelog/arch_1_11_1034700502.arc RECID=1 STAMP=1034715095 deleted archived log archived log file name=/u01/archivelog/arch_1_12_1034700502.arc RECID=2 STAMP=1034715096 deleted archived log archived log file name=/u01/archivelog/arch_1_13_1034700502.arc RECID=3 STAMP=1034715097 Deleted 3 objects Finished Duplicate Db at 2020:03:1020:51:45 RMAN> |
13 打开备库并并启动apply
duplicate 完成之后，备库是mount的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | SQL> select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED SQL> alter database open; Database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 DAVE READ ONLY NO SQL> --查看主库 SQL> set lines 150 SQL> col log_mode for a15 SQL> col open_mode for a15 SQL> col database_role for a20 SQL> select log_mode,open_mode ,database_role from v$database; LOG_MODE OPEN_MODE DATABASE_ROLE --------------- --------------- --------------- ARCHIVELOG READ WRITE PRIMARY SQL> --查看备库 SQL> select log_mode,open_mode ,database_role from v$database; LOG_MODE OPEN_MODE DATABASE_ROLE --------------- --------------- -------------------- ARCHIVELOG READ ONLY PHYSICAL STANDBY SQL> --启动日志应用 SQL> alter database recover managed standby database disconnect; Database altered. SQL> select open_mode from v$database; OPEN_MODE ------------------------------ READ ONLY WITH APPLY SQL> |
14 验证DG
主库：
1 2 3 4 5 6 7 | SQL> col status for a15 SQL> col error for a20 SQL> select status,error from v$archive_dest where dest_id=2; STATUS ERROR --------------- -------------------- VALID |
这里可能会遇到如下错误：
Oracle 19c Data Guard ORA-12154 错误解决方法
https://www.cndba.cn/dave/article/4066
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | SQL> alter system switch logfile; System altered. SQL> select sequence#,applied from v$archived_log order by 1; SEQUENCE# APPLIED ---------- --------------------------- 9 NO 10 NO 11 NO 12 NO 13 NO 14 NO 14 YES 15 YES 15 NO 16 YES 16 NO SEQUENCE# APPLIED ---------- --------------------------- 17 NO 17 NO 13 rows selected. |
备库：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> select sequence#,applied from v$archived_log; SEQUENCE# APPLIED ---------- --------------------------- 11 YES 12 YES 13 YES 15 YES 14 YES 16 YES 17 IN-MEMORY 7 rows selected. SQL> |
同步正常。
15 DG 模式切换
DG的模式切换之前参考18c，这里不再单独描述。
Oracle 18c Data Guard 切换保护模式 和 主备库角色操作手册
https://www.cndba.cn/dave/article/2996
