Oracle12c Data Guard搭建手册
注：本文来源： 红黑联盟 《 Oracle12c Data Guard搭建手册 》
Oracle 12c 的DataGuard 是在CDB 级别进行的，所以我们的配置都是从CDB角度出发。
测试里主备库的数据库CDB名称相同。
1 环境说明
1 OS Version： 2 3 [root@dave etc]# cat /etc/oracle-release 4 5 Oracle Linux Server release 6.3 6 7 [root@dave etc]# uname -r 8 9 2.6.39-200.29.3.el6uek.x86_64 10 11 DB Version: 12 13 SQL> select * from v$version; 14 15 BANNER CON_ID 16 17 ------------------------------------------------------------------------------------------ 18 19 Oracle Database 12c Enterprise EditionRelease 12.1.0.1.0 - 64bit Production 0 20 21 PL/SQL Release 12.1.0.1.0 - Production 0 22 23 CORE 12.1.0.1.0 Production 0 24 25 TNS for Linux: Version 12.1.0.1.0 -Production 0 26 27 NLSRTL Version 12.1.0.1.0 - Production 0 28 29 30 31 SQL> show pdbs 32 33 34 35 CON_ID CON_NAME OPEN MODE RESTRICTED 36 37 ---------- ---------------------------------------- ---------- 38 39 2 PDB$SEED READ ONLY NO 40 41 3 PCNDBA READ WRITE NO 42 43 SQL>
实例名：
Database
DB_UNIQUE_NAME
Oracle Net Service Name
Primary
PCNDBA_P
PCNDBA_P
Physical standby
PCNDBA_S
PCNDBA_S
IP 地址：
1 [root@dave network-scripts]# cat /etc/hosts 2 3 127.0.0.1 localhost dave 4 5 192.168.56.3 dg1 6 7 192.168.56.4 dg2 8 9 [root@dave network-scripts]#
这里用主库上的PDB：PCNDBA 做我们的主库。
2 主库启动FORCE LOGGING
1 SQL> select name,open_mode from v$pdbs; 2 3 4 5 NAME OPEN_MODE 6 7 ------------------------------ ---------- 8 9 PDB$SEED READ ONLY 10 11 PCNDBA READ WRITE 12 13 14 15 SQL> alter database force logging; 16 17 Database altered. 18 19 20 21 SQL> select force_logging fromv$database; 22 23 FORCE_LOGGING 24 25 --------------------------------------- 26 27 YES
3 启动归档模式
1 SQL> show con_name 2 3 4 5 CON_NAME 6 7 ------------------------------ 8 9 CDB$ROOT 10 11 SQL> archive log list; 12 13 Database log mode No Archive Mode 14 15 Automatic archival Disabled 16 17 Archive destination USE_DB_RECOVERY_FILE_DEST 18 19 Oldest online log sequence 14 20 21 Current log sequence 16 22 23 SQL> shutdown immediate 24 25 Database closed. 26 27 Database dismounted. 28 29 ORACLE instance shut down. 30 31 SQL> startup mount 32 33 ORACLE instance started. 34 35 36 37 Total System Global Area 1620115456 bytes 38 39 Fixed Size 2288920 bytes 40 41 Variable Size 1040188136 bytes 42 43 Database Buffers 570425344 bytes 44 45 Redo Buffers 7213056 bytes 46 47 Database mounted. 48 49 SQL> alter database archivelog; 50 51 52 53 Database altered. 54 55 56 57 SQL>
这里归档直接放在FRA里了：
1 SQL> show parameter recovery 2 3 4 5 NAME TYPE VALUE 6 7 ------------------------------------ ----------------------------------------- 8 9 db_recovery_file_dest string /home/ora12c/app/oracle/fast_recovery_area 10 11 db_recovery_file_dest_size big integer 4800M 12 13 recovery_parallelism integer 0 14 15 16 17 SQL> alter system setdb_recovery_file_dest_size=10G; 18 19 System altered. 20 21 22 23 SQL> show parameter recovery 24 25 NAME TYPE VALUE 26 27 ----------------------------------------------- ------------------------------ 28 29 db_recovery_file_dest string /home/ora12c/app/oracle/fast_recovery_area 30 31 db_recovery_file_dest_size biginteger 10G 32 33 recovery_parallelism integer 0 34 35 SQL> 36 37 38 39 SQL> alter database open; 40 41 42 43 Database altered. 44 45 46 47 SQL> select name,open_mode from v$pdbs; 48 49 50 51 NAME OPEN_MODE 52 53 ------------------------------ ---------- 54 55 PDB$SEED READ ONLY 56 57 PCNDBA MOUNTED 58 59 60 61 SQL> alter pluggable database pcndbaopen; 62 63 Pluggable database altered. 64 65 66 67 SQL> select name,open_mode from v$pdbs; 68 69 NAME OPEN_MODE 70 71 ------------------------------ ---------- 72 73 PDB$SEED READ ONLY 74 75 PCNDBA READ WRITE
4 在主库添加 standby redo logfile
在Oracle 12c的架构里，online redo log 和控制文件是保存在CDB中的，PDB中只有运行需要的数据文件，所以我们这里加standby redo log，也是在CDB中加。
查看 Primary 库的 REDO 相关信息：
1 SQL> show con_name 2 3 4 5 CON_NAME 6 7 ------------------------------ 8 9 CDB$ROOT 10 11 SQL> select group#, members, bytes from v$log; 12 13 14 15 GROUP# MEMBERS BYTES 16 17 ---------- ---------- ---------- 18 19 1 2 52428800 20 21 2 2 52428800 22 23 3 2 52428800 24 25 26 27 SQL> select member from v$logfile; 28 29 30 31 MEMBER 32 33 ----------------------------------------------------------------------------------------- 34 35 /home/ora12c/app/oracle/oradata/CNDBA/onlinelog/o1_mf_3_9y3rrb3v_.log 36 37 /home/ora12c/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_3_9y3rrb9n_.log 38 39 /home/ora12c/app/oracle/oradata/CNDBA/onlinelog/o1_mf_2_9y3rr54v_.log 40 41 /home/ora12c/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_2_9y3rr5b2_.log 42 43 /home/ora12c/app/oracle/oradata/CNDBA/onlinelog/o1_mf_1_9y3rqznr_.log 44 45 /home/ora12c/app/oracle/fast_recovery_area/CNDBA/onlinelog/o1_mf_1_9y3rr09s_
添加 4(3+1)个standby logfile：
1 SQL> alter database add standby logfile '/home/ora12c/app/oracle/oradata/CNDBA/onlinelog/stdredo01.log' size 50M; 2 3 Database altered. 4 5 6 7 SQL> alter database add standby logfile '/home/ora12c/app/oracle/oradata/CNDBA/onlinelog/stdredo02.log' size 50M; 8 9 Database altered. 10 11 12 13 SQL> alter database add standby logfile '/home/ora12c/app/oracle/oradata/CNDBA/onlinelog/stdredo03.log' size 50M; 14 15 Database altered. 16 17 18 19 SQL> alter database add standby logfile '/home/ora12c/app/oracle/oradata/CNDBA/onlinelog/stdredo04.log' size 50M; 20 21 Database altered.
5 分别在主备库配置监听并启动
1 --这里直接使用netmgr工具生成：注意静态监听注册中配置的pdb。 2 3 [ora12c@dave admin]$ cat listener.ora 4 5 # listener.ora Network Configuration File:/home/ora12c/app/oracle/product/12.1.0/db_1/network/admin/listener.ora 6 7 # Generated by Oracle configuration tools. 8 9 10 11 SID_LIST_LISTENER = 12 13 (SID_LIST = 14 15 (SID_DESC = 16 17 (GLOBAL_DBNAME = cndba) 18 19 (ORACLE_HOME = /home/ora12c/app/oracle/product/12.1.0/db_1) 20 21 (SID_NAME = cndba) 22 23 ) 24 25 ) 26 27 28 29 LISTENER = 30 31 (DESCRIPTION_LIST = 32 33 (DESCRIPTION = 34 35 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) 36 37 ) 38 39 (DESCRIPTION = 40 41 (ADDRESS = (PROTOCOL = TCP)(HOST = dave)(PORT = 1521)) 42 43 ) 44 45 ) 46 47 48 49 ADR_BASE_LISTENER = /home/ora12c/app/oracle 50 51 52 53 [ora12c@dave admin]$ lsnrctl reload 54 55 56 57 LSNRCTL for Linux: Version 12.1.0.1.0 -Production on 06-AUG-2014 19:26:50 58 59 60 61 Copyright (c) 1991, 2013, Oracle. All rights reserved. 62 63 64 65 Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) 66 67 The command completed successfully
6 分别在主备库配置tnsnames.ora
1 [ora12c@dave admin]$ cat tnsnames.ora 2 3 # tnsnames.ora Network Configuration File: /home/ora12c/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora 4 5 # Generated by Oracle configuration tools. 6 7 8 9 CNDBA_S = 10 11 (DESCRIPTION = 12 13 (ADDRESS_LIST = 14 15 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.4)(PORT = 1521)) 16 17 ) 18 19 (CONNECT_DATA = 20 21 (SERVICE_NAME = cndba) 22 23 ) 24 25 ) 26 27 28 29 CNDBA_P = 30 31 (DESCRIPTION = 32 33 (ADDRESS_LIST = 34 35 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521)) 36 37 ) 38 39 (CONNECT_DATA = 40 41 (SERVICE_NAME = cndba) 42 43 ) 44 45 ) 46 47 48 49 [ora12c@dave admin]$ tnsping cndba_s 50 51 [ora12c@dave admin]$ tnsping cndba_p
7 在备库创建必要的目录
可以参考主库的pfile中的路径：
1 [ora12c@dave admin]$ mkdir -p /home/ora12c/app/oracle/fast_recovery_area 2 3 [ora12c@dave admin]$ mkdir -p/home/ora12c/app/oracle/oradata 4 5 [ora12c@dave admin]$ mkdir -p/home/ora12c/app/oracle/admin/cndba/adump
8 在主库创建pfile 文件并修改pfile 内容
在pfile中添加如下内容：
1 #add for primary dg 2 3 *.db_name='cndba' 4 5 *.db_unique_name='cndba_p' 6 7 *.log_archive_config='dg_config=(cndba_p,cndba_s)' 8 9 *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DESTvalid_for=(all_logfiles,all_roles) db_unique_name=cndba_p' 10 11 *.log_archive_dest_2='service=cndba_s valid_for=(online_logfiles,primary_role) lgwr affirmsync db_unique_name=cndba_s' 12 13 *.log_archive_dest_state_1=enable 14 15 *.log_archive_dest_state_2=enable 16 17 *.standby_file_management='auto' 18 19 *.fal_server='cndba_s'
如果主备库CDB名称不同，还需要加如下参数：
1 *.DB_FILE_NAME_CONVERT='cndba','dave' 2 3 *.LOG_FILE_NAME_CONVERT='cndba','dave'
用新参数重启数据库：
1 SQL> shutdown immediate 2 3 Database closed. 4 5 Database dismounted. 6 7 ORACLE instance shut down. 8 9 10 11 SQL> create spfile from pfile; 12 13 File created. 14 15 16 17 SQL> startup 18 19 ORACLE instance started. 20 21 22 23 Total System Global Area 1620115456 bytes 24 25 Fixed Size 2288920 bytes 26 27 Variable Size 1040188136 bytes 28 29 Database Buffers 570425344 bytes 30 31 Redo Buffers 7213056 bytes 32 33 Database mounted. 34 35 Database opened. 36 37 SQL> 38 9 将主库的口
9 将主库的口令文件copy到备库
我这里主备库的CDB实例相同，如果不同，可以使用orapwd命令重建。
1 [ora12c@dave dbs]$ scp orapwcndba192.168.56.4:`pwd` 2 3 ora12c@192.168.56.4's password: 4 5 orapwcndba 100% 7680 7.5KB/s 00:00 6 7 [ora12c@dave dbs]$
10 将主库的参数文件copy到备库并修改
1 [ora12c@dave dbs]$ scp initcndba.ora192.168.56.4:`pwd` 2 3 ora12c@192.168.56.4's password: 4 5 initcndba.ora 100% 1593 1.6KB/s 00:00 6 7 [ora12c@dave dbs]$ 8 9 10 11 修改如下内容，在重新生成spfile： 12 13 #add for standby dg 14 15 *.db_unique_name='cndba_s' 16 17 *.log_archive_config='dg_config=(cndba_p,cndba_s)' 18 19 *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DESTvalid_for=(all_logfiles,all_roles) db_unique_name=cndba_s' 20 21 *.log_archive_dest_2='service=cndba_pvalid_for=(online_logfiles,primary_role) lgwr affirm sync db_unique_name=cndba_p' 22 23 *.log_archive_dest_state_1=enable 24 25 *.log_archive_dest_state_2=enable 26 27 *.standby_file_management='auto' 28 29 *.fal_server='cndba_p'
注意修改控制文件的路径，也使用新路径。
11 用spfile 将备库启动到nomount 状态
1 SQL> startup nomount 2 3 ORACLE instance started. 4 5 6 7 Total System Global Area 1620115456 bytes 8 9 Fixed Size 2288920 bytes 10 11 Variable Size 1040188136 bytes 12 13 Database Buffers 570425344 bytes 14 15 Redo Buffers 7213056 bytes 16 17 SQL>
12 开始进行Active duplicate
1 [ora12c@dave network-scripts]$ rman targetsys/oracle@cndba_p auxiliary sys/oracle@cndba_s; 2 3 4 5 Recovery Manager: Release 12.1.0.1.0 -Production on Wed Aug 6 20:41:02 2014 6 7 8 9 Copyright (c) 1982, 2013, Oracle and/or itsaffiliates. All rights reserved. 10 11 12 13 connected to target database: CNDBA(DBID=119362621) 14 15 connected to auxiliary database: CNDBA (notmounted) 16 17 18 19 20 21 RMAN>duplicatetarget database for standby from active database nofilenamecheck dorecover; 22 23 24 25 [ora12c@dave network-scripts]$ rman targetsys/oracle@cndba_p auxiliary sys/oracle@cndba_s; 26 27 28 29 Recovery Manager: Release 12.1.0.1.0 -Production on Wed Aug 6 20:41:02 2014 30 31 32 33 Copyright (c) 1982, 2013, Oracle and/or itsaffiliates. All rights reserved. 34 35 36 37 connected to target database: CNDBA(DBID=119362621) 38 39 connected to auxiliary database: CNDBA (notmounted) 40 41 42 43 RMAN> duplicate target database forstandby from active database nofilenamecheck dorecover; 44 45 46 47 Starting Duplicate Db at 06-AUG-14 48 49 using target database control file insteadof recovery catalog 50 51 allocated channel: ORA_AUX_DISK_1 52 53 channel ORA_AUX_DISK_1: SID=21 devicetype=DISK 54 55 current log archived 56 57 58 59 contents of Memory Script: 60 61 { 62 63 backup as copy reuse 64 65 targetfile '/home/ora12c/app/oracle/product/12.1.0/db_1/dbs/orapwcndba' auxiliaryformat 66 67 '/home/ora12c/app/oracle/product/12.1.0/db_1/dbs/orapwcndba' ; 68 69 } 70 71 executing Memory Script 72 73 74 75 Starting backup at 06-AUG-14 76 77 allocated channel: ORA_DISK_1 78 79 channel ORA_DISK_1: SID=48 device type=DISK 80 81 Finished backup at 06-AUG-14 82 83 84 85 contents of Memory Script: 86 87 { 88 89 sql clone "alter system set control_files = 90 91 ''/home/ora12c/app/oracle/oradata/CNDBA/controlfile/o1_mf_9y3rqtn9_.ctl'',''/home/ora12c/app/oracle/fast_recovery_area/CNDBA/controlfile/o1_mf_9y3rqvky_.ctl''comment= 92 93 ''Set by RMAN'' scope=spfile"; 94 95 restore clone from service 'cndba_p' standby controlfile; 96 97 } 98 99 executing Memory Script 100 101 102 103 sql statement: alter system set control_files = ''/home/ora12c/app/oracle/oradata/CNDBA/controlfile/o1_mf_9y3rqtn9_.ctl'',''/home/ora12c/app/oracle/fast_recovery_area/CNDBA/controlfile/o1_mf_9y3rqvky_.ctl''comment= ''Set by RMAN'' scope=spfile 104 105 106 107 Starting restore at 06-AUG-14 108 109 using channel ORA_AUX_DISK_1 110 111 112 113 channel ORA_AUX_DISK_1: starting datafilebackup set restore 114 115 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 116 117 channel ORA_AUX_DISK_1: restoring controlfile 118 119 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:07 120 121 output file name=/home/ora12c/app/oracle/oradata/CNDBA/controlfile/o1_mf_9y3rqtn9_.ctl 122 123 output filename=/home/ora12c/app/oracle/fast_recovery_area/CNDBA/controlfile/o1_mf_9y3rqvky_.ctl 124 125 Finished restore at 06-AUG-14 126 127 128 129 contents of Memory Script: 130 131 { 132 133 sql clone 'alter database mount standby database'; 134 135 } 136 137 executing Memory Script 138 139 140 141 sql statement: alter database mount standbydatabase 142 143 144 145 contents of Memory Script: 146 147 { 148 149 set newname for clone tempfile 1to new; 150 151 set newname for clone tempfile 2to new; 152 153 set newname for clone tempfile 3to new; 154 155 switchclone tempfile all; 156 157 set newname for clone datafile 1to new; 158 159 set newname for clone datafile 3to new; 160 161 set newname for clone datafile 4to new; 162 163 set newname for clone datafile 5to new; 164 165 set newname for clone datafile 6to new; 166 167 set newname for clone datafile 7to new; 168 169 set newname for clone datafile 8to new; 170 171 set newname for clone datafile 9to new; 172 173 set newname for clone datafile 10to new; 174 175 restore 176 177 from service 'cndba_p' clone database 178 179 ; 180 181 sql 'alter system archive log current'; 182 183 } 184 185 executing Memory Script 186 187 188 189 executing command: SET NEWNAME 190 191 192 193 executing command: SET NEWNAME 194 195 196 197 executing command: SET NEWNAME 198 199 200 201 renamed tempfile 1 to/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_temp_%u_.tmp in controlfile 202 203 renamed tempfile 2 to/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_temp_%u_.tmp in controlfile 204 205 renamed tempfile 3 to/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_temp_%u_.tmp in controlfile 206 207 208 209 executing command: SET NEWNAME 210 211 212 213 executing command: SET NEWNAME 214 215 216 217 executing command: SET NEWNAME 218 219 220 221 executing command: SET NEWNAME 222 223 224 225 executing command: SET NEWNAME 226 227 228 229 executing command: SET NEWNAME 230 231 232 233 executing command: SET NEWNAME 234 235 236 237 executing command: SET NEWNAME 238 239 240 241 executing command: SET NEWNAME 242 243 244 245 Starting restore at 06-AUG-14 246 247 using channel ORA_AUX_DISK_1 248 249 250 251 channel ORA_AUX_DISK_1: starting datafilebackup set restore 252 253 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 254 255 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 256 257 channel ORA_AUX_DISK_1: restoring datafile00001 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_%u_.dbf 258 259 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:01:37 260 261 channel ORA_AUX_DISK_1: starting datafilebackup set restore 262 263 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 264 265 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 266 267 channel ORA_AUX_DISK_1: restoring datafile00003 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_%u_.dbf 268 269 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:01:25 270 271 channel ORA_AUX_DISK_1: starting datafilebackup set restore 272 273 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 274 275 channel ORA_AUX_DISK_1: specifying datafile(s)to restore from backup set 276 277 channel ORA_AUX_DISK_1: restoring datafile00004 to/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_undotbs1_%u_.dbf 278 279 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:25 280 281 channel ORA_AUX_DISK_1: starting datafilebackup set restore 282 283 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 284 285 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 286 287 channel ORA_AUX_DISK_1: restoring datafile00005 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_%u_.dbf 288 289 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:35 290 291 channel ORA_AUX_DISK_1: starting datafilebackup set restore 292 293 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 294 295 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 296 297 channel ORA_AUX_DISK_1: restoring datafile00006 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_users_%u_.dbf 298 299 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:01 300 301 channel ORA_AUX_DISK_1: starting datafilebackup set restore 302 303 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 304 305 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 306 307 channel ORA_AUX_DISK_1: restoring datafile00007 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_%u_.dbf 308 309 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:01:15 310 311 channel ORA_AUX_DISK_1: starting datafilebackup set restore 312 313 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 314 315 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 316 317 channel ORA_AUX_DISK_1: restoring datafile00008 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_%u_.dbf 318 319 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:35 320 321 channel ORA_AUX_DISK_1: starting datafilebackup set restore 322 323 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 324 325 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 326 327 channel ORA_AUX_DISK_1: restoring datafile00009 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_%u_.dbf 328 329 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:01:15 330 331 channel ORA_AUX_DISK_1: starting datafilebackup set restore 332 333 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 334 335 channel ORA_AUX_DISK_1: specifyingdatafile(s) to restore from backup set 336 337 channel ORA_AUX_DISK_1: restoring datafile00010 to /home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_users_%u_.dbf 338 339 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:03 340 341 Finished restore at 06-AUG-14 342 343 344 345 sql statement: alter system archive logcurrent 346 347 current log archived 348 349 350 351 contents of Memory Script: 352 353 { 354 355 restore clone force from service 'cndba_p' 356 357 archivelog from scn 1922781; 358 359 switch clone datafile all; 360 361 } 362 363 executing Memory Script 364 365 366 367 Starting restore at 06-AUG-14 368 369 using channel ORA_AUX_DISK_1 370 371 372 373 channel ORA_AUX_DISK_1: starting archivedlog restore to default destination 374 375 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 376 377 channel ORA_AUX_DISK_1: restoring archivedlog 378 379 archived log thread=1 sequence=18 380 381 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:01 382 383 channel ORA_AUX_DISK_1: starting archivedlog restore to default destination 384 385 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 386 387 channel ORA_AUX_DISK_1: restoring archivedlog 388 389 archived log thread=1 sequence=19 390 391 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:02 392 393 channel ORA_AUX_DISK_1: starting archivedlog restore to default destination 394 395 channel ORA_AUX_DISK_1: using networkbackup set from service cndba_p 396 397 channel ORA_AUX_DISK_1: restoring archivedlog 398 399 archived log thread=1 sequence=20 400 401 channel ORA_AUX_DISK_1: restore complete,elapsed time: 00:00:01 402 403 Finished restore at 06-AUG-14 404 405 406 407 datafile 1 switched to datafile copy 408 409 input datafile copy RECID=12STAMP=854916586 filename=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_9y48opp7_.dbf 410 411 datafile 3 switched to datafile copy 412 413 input datafile copy RECID=13STAMP=854916586 filename=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_9y48rr3z_.dbf 414 415 datafile 4 switched to datafile copy 416 417 input datafile copy RECID=14STAMP=854916586 file name=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_undotbs1_9y48vf5v_.dbf 418 419 datafile 5 switched to datafile copy 420 421 input datafile copy RECID=15STAMP=854916586 filename=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_9y48w6bx_.dbf 422 423 datafile 6 switched to datafile copy 424 425 input datafile copy RECID=16STAMP=854916586 filename=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_users_9y48x8jb_.dbf 426 427 datafile 7 switched to datafile copy 428 429 input datafile copy RECID=17STAMP=854916586 file name=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_9y48xb36_.dbf 430 431 datafile 8 switched to datafile copy 432 433 input datafile copy RECID=18STAMP=854916586 filename=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_system_9y48zp0f_.dbf 434 435 datafile 9 switched to datafile copy 436 437 input datafile copy RECID=19STAMP=854916586 filename=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_sysaux_9y490r5z_.dbf 438 439 datafile 10 switched to datafile copy 440 441 input datafile copy RECID=20STAMP=854916586 file name=/home/ora12c/app/oracle/oradata/CNDBA_S/datafile/o1_mf_users_9y493301_.dbf 442 443 444 445 contents of Memory Script: 446 447 { 448 449 set until scn 1923489; 450 451 recover 452 453 standby 454 455 clone database 456 457 delete archivelog 458 459 ; 460 461 } 462 463 executing Memory Script 464 465 466 467 executing command: SET until clause 468 469 470 471 Starting recover at 06-AUG-14 472 473 using channel ORA_AUX_DISK_1 474 475 476 477 starting media recovery 478 479 480 481 archived log for thread 1 with sequence 18is already on disk as file/home/ora12c/app/oracle/fast_recovery_area/CNDBA_S/archivelog/2014_08_06/o1_mf_1_18_9y49378o_.arc 482 483 archived log for thread 1 with sequence 19is already on disk as file/home/ora12c/app/oracle/fast_recovery_area/CNDBA_S/archivelog/2014_08_06/o1_mf_1_19_9y49387d_.arc 484 485 archived log for thread 1 with sequence 20is already on disk as file/home/ora12c/app/oracle/fast_recovery_area/CNDBA_S/archivelog/2014_08_06/o1_mf_1_20_9y493976_.arc 486 487 archived log filename=/home/ora12c/app/oracle/fast_recovery_area/CNDBA_S/archivelog/2014_08_06/o1_mf_1_18_9y49378o_.arcthread=1 sequence=18 488 489 archived log filename=/home/ora12c/app/oracle/fast_recovery_area/CNDBA_S/archivelog/2014_08_06/o1_mf_1_19_9y49387d_.arcthread=1 sequence=19 490 491 archived log filename=/home/ora12c/app/oracle/fast_recovery_area/CNDBA_S/archivelog/2014_08_06/o1_mf_1_20_9y493976_.arcthread=1 sequence=20 492 493 media recovery complete, elapsed time:00:00:01 494 495 Finished recover at 06-AUG-14 496 497 Finished Duplicate Db at 06-AUG-14 498 499 500 501 RMAN>
13 打开备库并并启动apply
duplicate 完成之后，备库是mount的。
1 SQL> select open_mode from v$database; 2 3 4 5 OPEN_MODE 6 7 ---------------------------------------- 8 9 MOUNTED 10 11 12 13 SQL> show pdbs 14 15 16 17 CON_ID CON_NAME OPEN MODE RESTRICTED 18 19 ---------- ---------------------------------------- ---------- 20 21 2 PDB$SEED MOUNTED 22 23 3 PCNDBA MOUNTED 24 25 SQL> alter database open; 26 27 28 29 Database altered. 30 31 32 33 SQL> show pdbs 34 35 36 37 CON_ID CON_NAME OPEN MODE RESTRICTED 38 39 ---------- ---------------------------------------- ---------- 40 41 2 PDB$SEED READ ONLY NO 42 43 3 PCNDBA MOUNTED 44 45 46 47 SQL> alter pluggable database pcndbaopen; 48 49 50 51 Pluggable database altered. 52 53 54 55 SQL> show pdbs 56 57 58 59 CON_ID CON_NAME OPEN MODE RESTRICTED 60 61 ---------- ---------------------------------------- ---------- 62 63 2 PDB$SEED READ ONLY NO 64 65 3 PCNDBA READ ONLY NO
备库是只读的。
1 --查看主库： 2 3 SQL> select log_mode,open_mode ,database_role from v$database; 4 5 6 7 LOG_MODE OPEN_MODE DATABASE_ROLE 8 9 ------------ ------------------------------------ 10 11 ARCHIVELOG READ WRITE PRIMARY 12 13 14 15 --备库： 16 17 SQL> select log_mode,open_mode ,database_role from v$database; 18 19 20 21 LOG_MODE OPEN_MODE DATABASE_ROLE 22 23 ------------ ------------------------------------ 24 25 ARCHIVELOG READ ONLY PHYSICAL STANDBY 26 27 28 29 30 31 --启动real-time apply： 32 33 SQL> alter database recover managedstandby database using current logfile disconnect from session; 34 35 Database altered. 36 37 38 39 SQL> select open_mode from v$database; 40 41 42 43 OPEN_MODE 44 45 -------------------- 46 47 READ ONLY WITH APPLY
14 验证DG
1 --在主库创建一个table： 2 3 4 5 SQL> alter session set container=pcndba; 6 7 Session altered. 8 9 10 11 SQL> create table cndba as select * fromdba_users; 12 13 create table cndba as select * fromdba_users 14 15 * 16 17 ERROR at line 1: 18 19 ORA-01109: database not open 20 21 22 23 24 25 SQL> show pdbs 26 27 28 29 CON_ID CON_NAME OPEN MODE RESTRICTED 30 31 ---------- ---------------------------------------- ---------- 32 33 3 PCNDBA MOUNTED 34 35 SQL> alter database open; 36 37 Database altered. 38 39 40 41 SQL> create table cndba as select * fromdba_users; 42 43 Table created. 44 45 46 47 SQL> alter system switch logfile; 48 49 alter system switch logfile 50 51 * 52 53 ERROR at line 1: 54 55 ORA-65040: operation not allowed fromwithin a pluggable database
注意：这里可以看到，对于12c的库，日志的切换只能在CDB中进行，也就是我们前面讲的，DG 是在CDB 级别进行的。
备库查询：
1 SQL> select count(1) from cndba; 2 3 select count(1) from cndba 4 5 * 6 7 ERROR at line 1: 8 9 ORA-00942: table or view does not exist
提示表不存在，实际上，我们是在PDB里创建的，切换到对应的PDB下，就可以查询到了：
1 SQL> alter session container=pcndba; 2 3 alter session container=pcndba 4 5 * 6 7 ERROR at line 1: 8 9 ORA-00922: missing or invalid option 10 11 12 13 14 15 SQL> alter session set container=pcndba; 16 17 Session altered. 18 19 20 21 SQL> select count(1) from cndba; 22 23 24 25 COUNT(1) 26 27 ---------- 28 29 36