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 内容
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 将主库的口
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'
注意修改控制文件的路径，也使用新路径。
1 SQL> create spfile from pfile;
2
3 File created.
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> alter system switch logfile;
2
3 System altered.
备库查询：
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