duplicate DB without connecting to the target

Posted on 2009-04-21
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
    LVL 40

    Expert Comment

    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

    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

    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?

    LVL 11

    Author Comment

    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

    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

    mrjoltcola you are the man

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now