Re: restore using rman

From: Charles Hooper <>
Date: Sat, 14 Feb 2009 05:37:23 -0800 (PST)
Message-ID: <>

On Feb 14, 1:31 am, Anthony <> wrote:
> Hi,
> I don't know if following is possible:
> 1. restore all datafiles from a rman backup
> 2. restore to the SAME server but to a different location
> 3. during the restore, the existing production database is up and
> open all the time
> The reason is because I got a request to restore a 200GB database. But
> I do not have a spare machine now to do a duplicate/cloning. I am not
> allowed to create another instance on the same box for the cloning. Is
> it possible at all?
> I tried a small database and it works fine but when I use the
> production, I got the error:
> un {
> allocate channel ch1 type 'sbt_tape'
> PARMS="BLKSIZE=262144,ENV=(CvClientName=svr-gis-db-
> p,CvInstanceName=Instance001)
> ";
> set newname for datafile 'G:\ORACLE\ORADATA\GPRODP\DCSE_DATA.D03.DBF'
> to 'I:\oracle\backup\20091003\DCSE_DATA.D03.DBF';
> restore datafile 21
> until time = "TO_DATE('10/03/2008 05:00:00','MM/DD/YYYY
> HH24:MI:SS')" ;
> release channel ch1;
> }
> The database is in open mode.
> My intention is to restore the data file to a different location,
> leaving the production running and intact.
> I did a redirect and do not execute the switch command. But I got the
> error:
> creating datafile fno=21 name=I:\ORACLE\BACKUP
> \20091003\DCSE_DATA.D03.DBF
> released channel: ch1
> RMAN-00571:
> ===========================================================
> RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
> ===============
> RMAN-00571:
> ===========================================================
> RMAN-03002: failure of restore command at 02/10/2009 17:53:44
> ORA-01182: cannot create database file 21 - file is in use or recovery
> ORA-01110: data file 21: 'G:\ORACLE\ORADATA\GPRODP\DCSE_DATA.D03.DBF'
> Any suggestion?

As stated by Mladen, you must restore the data files to a clone instance so that you do not alter the production database. I suggest using db_file_name_convert and log_file_name_convert rather than set newname, if possible. You will need to make certain that the production database is in archived redo log mode, a backup was created in archive redo log mode, modify the listener.ora to add a new entry for the clone database, modify the tnsnames.ora to include the clone database, issue ORADIM to create a new service for the clone instance (on Windows), set up an init.ora file to be used by the clone instance, and then use RMAN connected to the target, auxiliary, and possibly the catalog database instances. See the step by step explanation here: In the above, step 3.5 should have stated that on Windows you need to enter the following at the DOS command line to create a new service for the clone database instance (named TEST1): C:\> ORADIM -CREATE -SID TEST1 In the above, step 4 should have included an entry specifying SGA_TARGET=500M (or some other value).

Test the above before attempting it on a production database.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Feb 14 2009 - 07:37:23 CST

Original text of this message