duplicate DB without connecting to the target

Posted on 2009-04-21
Medium Priority
Last Modified: 2012-05-06
metalink doc ID 732624.1
\refers to duplicating youe target without connecting to target;
which exactly what I want to do;
the problem that I do not fully understand it;and I need help with it;
and here is my situation;
I need to restore a table back to 2 weeks  and the only way is to use TSPITR using 2 weeks old backups;
I have retrieved the beackups from tape but we can not use production for duplication;
so what are the possible ways that I can use these old backups with anew instance to duplicate production ;without using production "TARGET"

please help

here is the note body
metalink doc ID 732624.1
Applies to:
Oracle Server - Enterprise Edition - Version: to
Information in this document applies to any platform.

How to do duplicate database without connecting to target database using backups taken from RMAN on alternate host.
Follow the below steps
1)Export ORACLE_SID=<SID Name as of production>

create init.ora file and give db_name=<dbname of production> and control_files=<location where you
want controlfile to be restored>

2)Startup nomount pfile=<path of init.ora>;

3)Connect to RMAN and issue command :

RMAN>restore controlfile from '<backuppiece of controlfile which you took on production>;

controlfile should be restored.

4) Issue "alter database mount"

Make sure that backuppieces are on the same location where it were there on production db. If you
dont have the same location, then make RMAN aware of the changed location using "catalog" command.

RMAN>catalog backuppiece <piece name and path>;

If there are more backuppieces, than they can be cataloged using command :

RMAN>catalog start with <path where backuppieces are stored>;

5) After catalogging backuppiece, issue "restore database" command.

6) After database is restored, perform recovery and apply archives which were generated during
backup using RMAN and then open the database with resetlogs.

7) To change the dbname and dbid, please use the Note 224266.1 to change the same

Question by:it-rex
  • 3
  • 3
LVL 40

Expert Comment

ID: 24199542
The note refers to how to duplicate a database with RMAN. Whether you use TSPITR or just recover until... probably doesn't matter if you are restoring to an auxiliary copy. You just need to make sure the restore until date is the one you need. The reason you don't want TSPITR for your actual production is because it will restore ALL objects in the tablespace from that point in time. So you cannot do that, or you'll lose all current tables in that tablespace back to the date of the restore.

So, the point of the metalink note is you need to create a new instance, initialize it with a basic init.ora (which will need db_name) and then start the instance nomount. Restore the controlfile, then mount the instance. Then you are ready to just restore until ... / recover until ... and open resetlogs.

Not sure which part you are concerned about. Please be more specific.

LVL 11

Author Comment

ID: 24204200
here is what I understand;
I have a clean box with just Oracle software installed ;I copy my backup pieces to this box.
then I create a new instance ;then I iniate the rman client from the same machine without the need to connect to any current catalog;
then how should I make my rman client deal with this new instance ???

LVL 40

Expert Comment

ID: 24204763
By not using an existing catalog, you are making it a bit harder on yourself.

In your case, you need to catalog the backup pieces so RMAN will know about them, but you cannot catalog the pieces until you have a mounted instance to connect to, because RMAN stores the catalog in the control file. So you need to restore the controlfile from the backupset, or restore a copy taken from your live instance (alter database backup controlfile to ...), if it is the same database as the backupset.

Once the controlfile exists, you can mount the database and do restore until ...

So, to recap, on new box:

1) setup oracle user environment (.profile, etc. -> ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH)
    create all paths (/ora/.../<SID>/...) that will be needed to restore files to
2) Create a 2 line init<SID>.ora under the dbs directory, add db_name and control_files param
3) Connect with rman (nocatalog)
    rman target / nocatalog
4) restore controlfile from 'path of backupset';
5) alter database mount;
6) catalog the backup pieces
    RMAN> catalog start with '/ora/oldbackups';
7) Restore until time or scn, recover until time or scn, then open the database and start exporting the tables you need to recover

The metalink doc has all the steps, so I feel like I am repeating, so why not start just by getting the new instance up in nomount state and let me know which problems you have?

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Author Comment

ID: 24210910
the main issue is the server that used to have the db has a G drive that holds the oradata folder meanwhile the box used to create the duplicate only has a c: drive and it is creating an error...
please advice
LVL 40

Accepted Solution

mrjoltcola earned 2000 total points
ID: 24215135
You can set new pathnames to the datafiles before restore. You can see the file numbers by listing the backupset, or looking on  your original database. You must do this inside a run block. To update the repository with the new paths, you also need to add a line "switch datafile all;"

run {
  set newname for datafile 1 to 'D:/oradata/sid/system.dbf';
  set newname for datafile 2 to ...

  switch datafile all;

  restore database ...

LVL 11

Author Closing Comment

ID: 31573856
mrjoltcola you are the man

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question