时间：2021-07-01 10:21:17 帮助过：24人阅读
实例名：
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 做我们的主库。
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
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
在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.
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
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
可以参考主库的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
1 SQL> create pfile from spfile; 2 3 File created.
在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 将主库的口
我这里主备库的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]$
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‘
注意修改控制文件的路径，也使用新路径。
1 SQL> create spfile from pfile; 2 3 File created.
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>
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_A