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

Physical standby

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

posted @ 2018-01-07 11:48 一品堂.技术学习笔记阅读(2676) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

✧꧁一品堂.技术学习笔记꧂✧.

Oracle12c Data Guard搭建手册