Posts

    [ + | - ]

Archives

    [ + | - ]

Resource Pages

    [ + | - ]
Uncategorized
Tuesday, 13 Jul 2010 3:23 AM

This post is a demonstration of duplicating an Oracle database to another server over the network. The article is for the scenario where ALL directory structures are the same on both the source and the destination database.

The setup information are as follows

?View Code LANGUAGE
1
2
3
4
5
6
7
8
9
10
11
12

Details		Source					Destination
OS		SunOS 5.10 Generic_141445-09 x86 64 bit	SunOS 5.10 Generic_141445-09 x86 64 bit
Hostname	solaris-si-node-01			solaris-si-node-02
IP Address	192.168.1.221				192.168.1.222
Oracle Home	/ora11g/app/ora11g/product/11.2.0/db_1	/ora11g/app/ora11g/product/11.2.0/db_1
SPFILE	$ORACLE_HOME/dbs/spfilePRODDBR2.ora		$ORACLE_HOME/dbs/spfilePRODDBR2.ora
Database Name	PRODDB2					PRODDB2
Data Location	/oradata11g/oradata/PRODDBR2/		/oradata11g/oradata/PRODDBR2/
Archive Location	/fra/PRODDBR2/arch		/fra/PRODDBR2/arch
Listener	LISTENER				LISTENER_DEST
Listener Port	1521					1521
Connect String	PRODDB2					PRODDB2_DEST

The steps involved are as follows

  1. Setup the source database
  2. Setup the auxiliary database
  3. Use RMAN to create duplicate database

Setup the source database

The source database should be configured to have connectivity to the auxiliary database via the listener. This can be configured by setting the tnsnames.ora entry for the auxiliary database. (This Listener needs to be created on the auxiliary database as seen later in this article).

?Download tnsnames.ora
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

	# tnsnames.ora Network Configuration File: /ora11g/app/ora11g/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
	# Generated by Oracle configuration tools.
 
	PRODDBR2 =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SERVICE_NAME = PRODDBR2)
	    )
	  )
 
	PRODDBR2_DEST =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
	    (CONNECT_DATA =
	      (SERVER = DEDICATED)
	      (SERVICE_NAME = PRODDBR2)
	    )
	  )

Setup the auxiliary database

In this section, we setup the auxiliary database. The following are the things that we need to take care of

  • Generate a password file

    • You could copy the password file from the source database or create a new one

    ?Download pwcreate.txt
    1
2
3
4

    $ scp orapwPRODDBR2 ora11g@solaris-si-node-02:/ora11g/app/ora11g/product/11.2>
	Password:
	orapwPRODDBR2        100% |*****************************|  1536       00:00
	$
  • Create directory structure same as that on production

    • Ensure that you create the directory structure same as the production server. Any location in the spfile MUST exist on the target

    ?Download text
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

    	audit_file_dest=/ora11g/app/ora11g/admin/PRODDBR2/adump
	background_dump_dest=/ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
	core_dump_dest=/ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/cdump
	user_dump_dest=/ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
 
	control_files=/oradata11g/oradata/PRODDBR2/control01.ctl, /ora11g/app/ora11g/flash_recovery_area/PRODDBR2/control02.ctl
	diagnostic_dest=/ora11g/app/ora11g
 
 
	mkdir -p /ora11g/app/ora11g/admin/PRODDBR2/adump
	mkdir -p /ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
	mkdir -p /ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/cdump
	mkdir -p /ora11g/app/ora11g/diag/rdbms/proddbr2/PRODDBR2/trace
	mkdir -p /oradata11g/oradata/PRODDBR2/
	mkdir -p /ora11g/app/ora11g/flash_recovery_area/PRODDBR2/

    Also create the directories for your datafiles and redo logfiles

    ?Download tnsnames.ora
    1

    	mkdir -p /oradata11g/oradata/PRODDBR2/ (in my case, it is already created earlier)
  • Create a bare minimum initialization parameter file

    • Add db_name=PRODDBR2 to initPRODDBR2.ora in $ORACLE_HOME/dbs. Since the SPFILE will be brought from the production, and the directory structure is the same, the other parameters can be ignored

  • Create the listener for remote logins

    • Create and start the listener for this instance

    ?Download listener.ora
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

    # Generated by Oracle configuration tools.
 
SID_LIST_LISTENER_DEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PRODDBR2)
      (ORACLE_HOME = /ora11g/app/ora11g/product/11.2.0/dbhome_1)
      (SID_NAME = PRODDBR2)
    )
  )
 
LISTENER_DEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
    )
  )
 
ADR_BASE_LISTENER = /ora11g/app/ora11g
  • Startup in nomount mode
    • ?Download tnsnames.ora
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    $ sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 13 16:08:36 2010
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area  217157632 bytes
Fixed Size                  2209600 bytes
Variable Size             159385792 bytes
Database Buffers           50331648 bytes
Redo Buffers                5230592 bytes
SQL> exit

Use RMAN to create duplicate database

Now that all the settings have been made, we can connect to the target database and auxiliary database as follows and start the duplication process.

?Download duplicate.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320

 
$ hostname
solaris-si-node-01
$ export ORACLE_SID=PRODDBR2
$ rman target /
 
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 13 16:23:25 2010
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: PRODDBR2 (DBID=2607472656)
 
RMAN> connect auxiliary sys@proddbr2_dest
 
auxiliary database Password:
connected to auxiliary database (not started)
 
RMAN> DUPLICATE TARGET DATABASE TO PRODDBR2
FROM ACTIVE DATABASE
PASSWORD FILE
SPFILE
NOFILENAMECHECK; 
Starting Duplicate Db at 13-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
 
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/orapwPRODDBR2' auxiliary format
 '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/orapwPRODDBR2'   targetfile
 '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora' auxiliary format
 '/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora'   ;
   sql clone "alter system set spfile= ''/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora''";
}
executing Memory Script
 
Starting backup at 13-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
Finished backup at 13-JUL-10
 
sql statement: alter system set spfile= ''/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/spfilePRODDBR2.ora''
 
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRODDBR2'' comment=
 ''duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
 
sql statement: alter system set  db_name =  ''PRODDBR2'' comment= ''duplicate'' scope=spfile
 
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
 
contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRODDBR2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRODDBR2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/oradata11g/oradata/PRODDBR2/control01.ctl';
   restore clone controlfile to  '/ora11g/app/ora11g/flash_recovery_area/PRODDBR2/control02.ctl' from
 '/oradata11g/oradata/PRODDBR2/control01.ctl';
   alter clone database mount;
}
executing Memory Script
 
sql statement: alter system set  db_name =  ''PRODDBR2'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
sql statement: alter system set  db_unique_name =  ''PRODDBR2'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
Oracle instance shut down
 
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
 
Starting backup at 13-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/ora11g/app/ora11g/product/11.2.0/dbhome_1/dbs/snapcf_PRODDBR2.f tag=TAG20100713T162459 RECID=5 STAMP=724263900
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-JUL-10
 
Starting restore at 13-JUL-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
 
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 13-JUL-10
 
database mounted
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
 
contents of Memory Script:
{
   set newname for datafile  1 to
 "/oradata11g/oradata/PRODDBR2/system01.dbf";
   set newname for datafile  2 to
 "/oradata11g/oradata/PRODDBR2/sysaux01.dbf";
   set newname for datafile  3 to
 "/oradata11g/oradata/PRODDBR2/undotbs01.dbf";
   set newname for datafile  4 to
 "/oradata11g/oradata/PRODDBR2/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata11g/oradata/PRODDBR2/system01.dbf"   datafile
 2 auxiliary format
 "/oradata11g/oradata/PRODDBR2/sysaux01.dbf"   datafile
 3 auxiliary format
 "/oradata11g/oradata/PRODDBR2/undotbs01.dbf"   datafile
 4 auxiliary format
 "/oradata11g/oradata/PRODDBR2/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
Starting backup at 13-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata11g/oradata/PRODDBR2/system01.dbf
output file name=/oradata11g/oradata/PRODDBR2/system01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf
output file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf
output file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata11g/oradata/PRODDBR2/users01.dbf
output file name=/oradata11g/oradata/PRODDBR2/users01.dbf tag=TAG20100713T162509
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-JUL-10
 
sql statement: alter system archive log current
 
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/fra/PRODDBR2/arch/1_7_724236691.dbf" auxiliary format
 "/fra/PRODDBR2/arch/1_7_724236691.dbf"   ;
   catalog clone archivelog  "/fra/PRODDBR2/arch/1_7_724236691.dbf";
   switch clone datafile all;
}
executing Memory Script
 
Starting backup at 13-JUL-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=5 STAMP=724264160
output file name=/fra/PRODDBR2/arch/1_7_724236691.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 13-JUL-10
 
cataloged archived log
archived log file name=/fra/PRODDBR2/arch/1_7_724236691.dbf RECID=5 STAMP=724264163
 
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=724264163 file name=/oradata11g/oradata/PRODDBR2/users01.dbf
 
contents of Memory Script:
{
   set until scn  924147;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script
 
executing command: SET until clause
 
Starting recover at 13-JUL-10
using channel ORA_AUX_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 7 is already on disk as file /fra/PRODDBR2/arch/1_7_724236691.dbf
archived log file name=/fra/PRODDBR2/arch/1_7_724236691.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-JUL-10
 
contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''PRODDBR2'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
 
database dismounted
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
 
sql statement: alter system set  db_name =  ''PRODDBR2'' comment= ''Reset to original value by RMAN'' scope=spfile
 
sql statement: alter system reset  db_unique_name scope=spfile
 
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1085640704 bytes
 
Fixed Size                     2210208 bytes
Variable Size                687867488 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9687040 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PRODDBR2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M
 DATAFILE
  '/oradata11g/oradata/PRODDBR2/system01.dbf'
 CHARACTER SET WE8MSWIN1252
 
 
contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oradata11g/oradata/PRODDBR2/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/oradata11g/oradata/PRODDBR2/sysaux01.dbf",
 "/oradata11g/oradata/PRODDBR2/undotbs01.dbf",
 "/oradata11g/oradata/PRODDBR2/users01.dbf";
   switch clone datafile all;
}
executing Memory Script
 
executing command: SET NEWNAME
 
renamed tempfile 1 to /oradata11g/oradata/PRODDBR2/temp01.dbf in control file
 
cataloged datafile copy
datafile copy file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf RECID=1 STAMP=724264213
cataloged datafile copy
datafile copy file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf RECID=2 STAMP=724264213
cataloged datafile copy
datafile copy file name=/oradata11g/oradata/PRODDBR2/users01.dbf RECID=3 STAMP=724264213
 
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=724264213 file name=/oradata11g/oradata/PRODDBR2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=724264213 file name=/oradata11g/oradata/PRODDBR2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=724264213 file name=/oradata11g/oradata/PRODDBR2/users01.dbf
 
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script
 
database opened
Finished Duplicate Db at 13-JUL-10
 
RMAN>

This article is a simple demonstration of RMAN’s duplicate over network feature. Oracle 11g provides various other options with duplicate command. These include duplicate database when directory structures are different, point in time duplication, incomplete (partial) duplication, duplicate for standby etc. Oracle documentation is the best place for beginers to start, in order to understand these features.

This option of duplicate opens the destination or auxiliary database in resetlogs mode.

Popularity: 15% [?]

Leave a Reply

Control Panel

Login



My Links

Blogroll

Project Management

Recent Posts

Recent Comments
Internet Advertisingundelete