Skip to content
April 19, 2012 / Shivananda Rao P

Duplicating database to a new Host without connecting to the Target database in Oracle 10g

Lets say you have the incremental level 0 backup and also the archivelog backup of the Primary database (which in other terms you can call the Production Database) and you need to restore it or create a new database (Test Database) on a new host. In this case you can go with RMAN duplication method which is simple and no complexities invloved, but to go with RMAN duplication method you need to connect to the Primary database as the target database and the new database (Test database) as the Auxiliary database and optionally a catalog database. Say, if you are not permitted to connect to the Primary database and given only its backup to create the duplication, then you can go with the below method. This method is applicable to 10g and also to 11g, but there is new feature in 11g which allows you do it in a different method which I would be describing it in my next post (I’ll be posting the link here )

Here, you need to note down that the Database Name on the new host (test database) should be same as that of the Primary database. You can change it to the desired name using the NEWID utility once the database is restored and recovered. Also you need to have the DBID of the Primary database.

Step 1:

I have the incremetal level 0 and also the archivelog backup of the Primary database.

[oracle@uat bkp]$ pwd
/u02/bkp
[oracle@uat bkp]$ ls -lrt
total 1043604
-rw-r----- 1 oracle oinstall 1006632960 Apr 14 22:26 MYPRIM_inc0_02n8dugg_1_1.bak
-rw-r----- 1 oracle oinstall      12288 Apr 14 22:26 MYPRIM_inc0_04n8dujn_1_1.bak
-rw-r----- 1 oracle oinstall    9830400 Apr 14 22:26 MYPRIM_inc0_03n8dujg_1_1.bak
-rw-r----- 1 oracle oinstall   51111936 Apr 14 22:26 MYPRIM_inc0_05n8dujq_1_1.bak
[oracle@uat bkp]$

Step 2:

Now in the new host, add the entries for the new database to the listener.ora and also to the tnsnames.ora file.

Also, you need to create a password file using ORAPWD utility for the new database.

orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwmyprim password=<password>

Step 3:

Connect to the new database as the Target using RMAN in the new host.

[oracle@uat ~]$ rman target sys/<password>@myprim

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 1515:40:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

Step 4:

Set the dbid of the Primary DB that was noted down earlierin the RMAN prompt.

RMAN> set dbid=3322337342;

executing command: SET DBID

Step 5:

Start the database forcefully in nomount stage.

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmyprim.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2211448 bytes
Variable Size                 88080776 bytes
Database Buffers              62914560 bytes
Redo Buffers                   5455872 bytes

Step 6:

Restore the Spfile to PFILE using the backup piece.

RMAN> restore spfile to pfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmyprim.ora' from '/u02/bkp/MYPRIM_inc0_03n8dujg_1_1.bak';

Starting restore at 15-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u02/bkp/MYPRIM_inc0_03n8dujg_1_1.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-APR-12

Step 7: (Optional)

Make changes to the pfile (control_files,db_create_file_dest,FRA) if the ASM diskgroups are different than they are on the Primary db server. If the diskgroups are same, then no need to make any changes to the pfile. Just need to start the instance in nomount mode using the pfile restored in the previous step. In my case, the asm diskgroups were different from the primary db server. CONTROL_FILES & DATAFILES (db_create_file_dest) are stored in +DATA and flash recovery area is in +ARCH.

Step 8:

Shutdown the Database and start it in nomount stage using the PFILE that was modified/created in the previous step.

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmyprim.ora';

connected to target database (not started)
Oracle instance started

Total System Global Area     492707840 bytes

Fixed Size                     2214656 bytes
Variable Size                188744960 bytes
Database Buffers             297795584 bytes
Redo Buffers                   3952640 bytes

Step 9:

Now restore the controlfile from the backup piece and mount the database. The controlfile would be restored to the location depending on the parameter CONTROL_FILES in the PFILE.


RMAN> restore controlfile from '/u02/bkp/MYPRIM_inc0_03n8dujg_1_1.bak';

Starting restore at 15-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA/myprim/controlfile/current.293.780681475
Finished restore at 15-APR-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Step 10: (Optional)

If the backup pieces are stored in a different location than they were taken on the primary server, then the pieces need to be cataloged. If they are copied to the similar directory structure, then this step can be ignored.

In my case I had taken the backup to the location “/u02/bkp” in the primary server and created the similar direcotry on the auxiliary server as well (“/u02/bkp”) and copied the backups.

RMAN>catalog backuppiece '<backup piece name>';

or

RMAN>catalog start with '<backup piece starting name>';

Step 11:

From the sql prompt, notedown the file# & name of the datafiles if you are using filesystem, as you may have to use the file# accordingly to the file name if you want them to be restored to a different location than the primary.

In my case, I ignore it as I have added the parameter db_create_file_dest pointing to the desired disk group as I mentioned earlier.

[oracle@uat ~]$ sqlplus sys/<password>@myprim as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 15 16:56:13 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> select status,instance_name from v$instance;

STATUS   INSTANCE_NAME
-------- -------------------
MOUNTED  myprim

SQL> select file#,name from v$datafile;

FILE# NAME
----- ---------------------------------------------------------
1     +DATA_NEW/myprim/datafile/system.268.780583459
2     +DATA_NEW/myprim/datafile/sysaux.267.780583471
3     +DATA_NEW/myprim/datafile/undotbs1.269.780583481
4     +DATA_NEW/myprim/datafile/users.270.780583481

Step 12: (For Filesystem datafiles)

If you want the datafiles to be restored to a different location (in case of filesystem) than that of the primary, then specify the “set newname for datafile” clause as shown below.

RMAN> run{
2> set newname for datafile 1 to '<desired location>system01.dbf';
3> set newname for datafile 2 to '<desired location>sysaux.dbf';
4> set newname for datafile 3 to '<desired location>undotbs1.dbf';
5> set newname for datafile 4 to '<desired location>users.dbf';
6> RESTORE DATABASE;
7> SWITCH DATAFILE ALL;
8> RECOVER DATABASE;
9> }

Step 13:

If you are on ASM, then you can exclude the above step and restore and recover the database.


RMAN> restore database;

Starting restore at 15-APR-12
Starting implicit crosscheck backup at 15-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 15-APR-12

Starting implicit crosscheck copy at 15-APR-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-APR-12

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA_NEW/myprim/datafile/system.268.780583459
channel ORA_DISK_1: restoring datafile 00002 to +DATA_NEW/myprim/datafile/sysaux.267.780583471
channel ORA_DISK_1: restoring datafile 00003 to +DATA_NEW/myprim/datafile/undotbs1.269.780583481
channel ORA_DISK_1: restoring datafile 00004 to +DATA_NEW/myprim/datafile/users.270.780583481
channel ORA_DISK_1: reading from backup piece /u02/bkp/MYPRIM_inc0_02n8dugg_1_1.bak
channel ORA_DISK_1: piece handle=/u02/bkp/MYPRIM_inc0_02n8dugg_1_1.bak tag=TAG20120414T164256
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:27
Finished restore at 15-APR-12

RMAN> recover database;

Starting recover at 15-APR-12
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/15/2012 16:06:07
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 1002495

Step 14:

Open the database with resetlogs:

RMAN> alter database open resetlogs;

Database opened

RMAN>

SQL> select status,instance_name from v$Instance;

STATUS  INSTANCE_NAME
------- --------------------
OPEN    myprim

Step 15:

Check the location of the datafiles and the redo log files if they have been restored in the right ASM diskgroups.

SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------
+DATA/myprim/datafile/users.289.780681797
+DATA/myprim/datafile/undotbs1.290.780681795
+DATA/myprim/datafile/sysaux.291.780681795
+DATA/myprim/datafile/system.292.780681791

SQL> select group#,member from v$logfile;

GROUP# MEMBER
------ --------------------------------------------------------
3      +DATA/myprim/onlinelog/group_3.292.780686001
3      +ARCH/myprim/onlinelog/group_3.1075.780686005
2      +DATA/myprim/onlinelog/group_2.291.780685995
2      +ARCH/myprim/onlinelog/group_2.1076.780685999
1      +DATA/myprim/onlinelog/group_1.290.780685987
1      +ARCH/myprim/onlinelog/group_1.1080.780685991

6 rows selected.

You can change the database name to the desired name (TESTDB) using the NEWID utility by following the below steps

Step 16:

Shutdown the newly created database and mount it.

[oracle@uat ~]$ sqlplus sys/<password>@myprim as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 17 10:37:28 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  492707840 bytes
Fixed Size                  2214656 bytes
Variable Size             188744960 bytes
Database Buffers          297795584 bytes
Redo Buffers                3952640 bytes
Database mounted.

Step 17:

Now use the NEWID utility to change the database name. Use the parameters “dbname” and “setname” parameters while using the NEWID utility.

[oracle@uat ~]$ nid target=sys/<password>@myprim dbname=testdb setname=YES

DBNEWID: Release 11.2.0.1.0 - Production on Tue Apr 17 10:39:462012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database MYPRIM (DBID=2564150375)

Connected to server version 11.2.0

Control Files in database:
+DATA/myprim/controlfile/current.285.780684875

Change database name of database MYPRIM to TESTDB? (Y/[N]) => Y

Proceeding with operation
Changing database name from MYPRIM to TESTDB
Control File +DATA/myprim/controlfile/current.285.780684875 - modified
Datafile +DATA/myprim/datafile/system.286.78068527 - wrote new name
Datafile +DATA/myprim/datafile/sysaux.287.78068527 - wrote new name
Datafile +DATA/myprim/datafile/undotbs1.288.78068527 - wrote new name
Datafile +DATA/myprim/datafile/users.289.78068527 - wrote new name
Datafile +DATA/myprim/tempfile/temp.293.78068601 - wrote new name
Control File +DATA/myprim/controlfile/current.285.780684875 - wrote new name
Instance shut down

Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

Step 18:

[oracle@uat ~]$ sqlplus sys/<password>@myprim as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 17 10:43:40 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  492707840 bytes
Fixed Size                  2214656 bytes
Variable Size             188744960 bytes
Database Buffers          297795584 bytes
Redo Buffers                3952640 bytes
Database mounted.
Database opened.
SQL> select status,instance_name,name from v$database,v$instance;

STATUS   INSTANCE_NAME  NAME
-------- -------------- --------
OPEN     myprim         TESTDB

Step 19 (Optional):

Now the database name here is changed to TESTDB. If you want to change the instance name as well, then you can rename your INIT<SID>.ora file to INIT<DESIRED-SID>.ora but remember you need to recreate the password file using ORAPWD utility.

orapwd file=<$ORACLE_HOME/dbs/orapw<SID>> password=<passsword>

If you are changing the instance name (SID), note that you will have to make changes in the tnsnames.ora and listener.ora file of the newly created database server by replacing the old SID to the new one, if they are making use of the old SID.

Here you go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Filed under RMAN

Leave a comment