Re: RMAN Restore Failure ORA-01180 and ORA_01110

From: gazzag <gareth_at_jamms.org>
Date: Wed, 7 Oct 2009 02:06:40 -0700 (PDT)
Message-ID: <1e4c7672-0936-409a-98a8-fd094f539a14_at_o36g2000vbl.googlegroups.com>

On 6 Oct, 17:12, gazzag <gar..._at_jamms.org> wrote:
> On 6 Oct, 16:23, trub3101 <trub3..._at_sky.com> wrote:
>
>
>
>
>
> > Thanks for your reply gazzag.
>
> > The script is pretty much as described. I have replaced part of the
> > datafile names with asterisks for privacy.
>
> > run
> > {
> > SET NEWNAME FOR DATAFILE 1 TO 'E:\ORACLE\ORADATA\LIVE\SYSTEM01.DBF';
> > SET NEWNAME FOR DATAFILE 10 TO 'E:\ORACLE\ORADATA\LIVE\***_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 11 TO 'E:\ORACLE\ORADATA\LIVE\***_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 12 TO 'E:\ORACLE\ORADATA\LIVE
> > \NLS_****_CONV_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 13 TO 'E:\ORACLE\ORADATA\LIVE
> > \NLS_****_CONV_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 14 TO 'E:\ORACLE\ORADATA\LIVE
> > \********_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 15 TO 'E:\ORACLE\ORADATA\LIVE
> > \********_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 16 TO 'E:\ORACLE\ORADATA\LIVE
> > \********_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 17 TO 'E:\ORACLE\ORADATA\LIVE
> > \RATINGS_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 18 TO 'E:\ORACLE\ORADATA\LIVE
> > \RATINGS_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 19 TO 'E:\ORACLE\ORADATA\LIVE
> > \RATINGS_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 2 TO 'E:\ORACLE\ORADATA\LIVE\UNDOTBS01.DBF';
> > SET NEWNAME FOR DATAFILE 20 TO 'E:\ORACLE\ORADATA\LIVE
> > \****DYNM_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 21 TO 'E:\ORACLE\ORADATA\LIVE
> > \****DYNM_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 22 TO 'E:\ORACLE\ORADATA\LIVE
> > \****DYNM_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 23 TO 'E:\ORACLE\ORADATA\LIVE
> > \WORKFLOW_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 24 TO 'E:\ORACLE\ORADATA\LIVE
> > \WORKFLOW_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 25 TO 'E:\ORACLE\ORADATA\LIVE
> > \WORKFLOW_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 26 TO 'E:\ORACLE\ORADATA\LIVE
> > \****AUDIT_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 27 TO 'E:\ORACLE\ORADATA\LIVE
> > \****AUDIT_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 28 TO 'E:\ORACLE\ORADATA\LIVE
> > \****TASK_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 29 TO 'E:\ORACLE\ORADATA\LIVE
> > \****TASK_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 3 TO 'E:\ORACLE\ORADATA\LIVE\SYSAUX01.DBF';
> > SET NEWNAME FOR DATAFILE 30 TO 'E:\ORACLE\ORADATA\LIVE
> > \****TASK_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 31 TO 'E:\ORACLE\ORADATA\LIVE
> > \******DBA_DATA1.DBF';
> > SET NEWNAME FOR DATAFILE 32 TO 'E:\ORACLE\ORADATA\LIVE
> > \******DBA_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 33 TO 'E:\ORACLE\ORADATA\LIVE
> > \******DBA_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 34 TO 'E:\ORACLE\ORADATA\LIVE
> > \****LOB_DATA1.DBF';
> > SET NEWNAME FOR DATAFILE 35 TO 'E:\ORACLE\ORADATA\LIVE
> > \****ARCH_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 36 TO 'E:\ORACLE\ORADATA\LIVE
> > \****ARCH_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 37 TO 'E:\ORACLE\ORADATA\LIVE
> > \****ARCH_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 38 TO 'E:\ORACLE\ORADATA\LIVE
> > \********_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 39 TO 'E:\ORACLE\ORADATA\LIVE
> > \********_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 4 TO 'E:\ORACLE\ORADATA\LIVE\USERS01.DBF';
> > SET NEWNAME FOR DATAFILE 40 TO 'E:\ORACLE\ORADATA\LIVE
> > \********_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 41 TO 'E:\ORACLE\ORADATA\LIVE
> > \****LOB_DATA2.DBF';
> > SET NEWNAME FOR DATAFILE 42 TO 'E:\ORACLE\ORADATA\LIVE\INDX01.DBF';
> > SET NEWNAME FOR DATAFILE 43 TO 'E:\ORACLE\ORADATA\LIVE\TOOLS01.DBF';
> > SET NEWNAME FOR DATAFILE 5 TO 'E:\ORACLE\ORADATA\LIVE
> > \***_CONSTRAINT.DBF';
> > SET NEWNAME FOR DATAFILE 6 TO 'E:\ORACLE\ORADATA\LIVE\***_DATA.DBF';
> > SET NEWNAME FOR DATAFILE 7 TO 'E:\ORACLE\ORADATA\LIVE\***_INDEX.DBF';
> > SET NEWNAME FOR DATAFILE 8 TO 'E:\ORACLE\ORADATA\LIVE\DBCC_REPOS.DBF';
> > SET NEWNAME FOR DATAFILE 9 TO 'E:\ORACLE\ORADATA\LIVE\***_CTL.DBF';
> > SQL "ALTER DATABASE RENAME FILE ''G:\ORACLE\ORADATA\LIVE\REDO01A.LOG''
> > TO ''E:\ORACLE\ORADATA\LIVE\REDO01A.LOG''
> > ";
> > SQL "ALTER DATABASE RENAME FILE ''G:\ORACLE\ORADATA\LIVE\REDO01B.LOG''
> > TO ''E:\ORACLE\ORADATA\LIVE\REDO01B.LOG''
> > ";
> > SQL "ALTER DATABASE RENAME FILE ''G:\ORACLE\ORADATA\LIVE\REDO02A.LOG''
> > TO ''E:\ORACLE\ORADATA\LIVE\REDO02A.LOG''
> > ";
> > SQL "ALTER DATABASE RENAME FILE ''G:\ORACLE\ORADATA\LIVE\REDO02B.LOG''
> > TO ''E:\ORACLE\ORADATA\LIVE\REDO02B.LOG''
> > ";
> > SQL "ALTER DATABASE RENAME FILE ''G:\ORACLE\ORADATA\LIVE\REDO03A.LOG''
> > TO ''E:\ORACLE\ORADATA\LIVE\REDO03A.LOG''
> > ";
> > SQL "ALTER DATABASE RENAME FILE ''G:\ORACLE\ORADATA\LIVE\REDO03B.LOG''
> > TO ''E:\ORACLE\ORADATA\LIVE\REDO03B.LOG'' ";
> > RESTORE DATABASE;
> > SWITCH DATAFILE ALL;
> > RECOVER DATABASE;
>
> > }
>
> > Prior to running this I set the dbid and restored the spfile from the
> > backup to the pfile. After this I started the database in nomount mode
> > using the pfile, restored the controlfiles from the backup, set the
> > database to mount mode and then ran the above.
>
> > Thanks
> > tb3101
>
> Restoring the controlfile from your backup was your mistake. �The
> steps are, of the top of my head, as follows (assuming Oracle 10gR2):
>
> 1. �RMAN backup source database.
> 2. �Copy backup set to new host and place in the same directory as you
> put the backup in the step above.
> 3. �The *duplicate* the database as follows:
>
> On the new host:
>
> set ORACLE_SID appropriately
> rman target /_at_<source_database> auxiliary /
>
> This will log RMAN into your source database *and* your destination
> (auxiliary) database,
>
> Then edit your RMAN script as follows before running it:
>
> run
> {
> SET NEWNAME FOR DATAFILE 1 TO 'E:\ORACLE\ORADATA\LIVE\SYSTEM01.DBF';
> SET NEWNAME FOR DATAFILE 10 TO 'E:\ORACLE\ORADATA\LIVE\***_DATA.DBF';
> SET NEWNAME FOR DATAFILE 11 TO 'E:\ORACLE\ORADATA\LIVE
> \***_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 12 TO 'E:\ORACLE\ORADATA\LIVE
> \NLS_****_CONV_DATA.DBF';
> SET NEWNAME FOR DATAFILE 13 TO 'E:\ORACLE\ORADATA\LIVE
> \NLS_****_CONV_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 14 TO 'E:\ORACLE\ORADATA\LIVE
> \********_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 15 TO 'E:\ORACLE\ORADATA\LIVE
> \********_DATA.DBF';
> SET NEWNAME FOR DATAFILE 16 TO 'E:\ORACLE\ORADATA\LIVE
> \********_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 17 TO 'E:\ORACLE\ORADATA\LIVE
> \RATINGS_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 18 TO 'E:\ORACLE\ORADATA\LIVE
> \RATINGS_DATA.DBF';
> SET NEWNAME FOR DATAFILE 19 TO 'E:\ORACLE\ORADATA\LIVE
> \RATINGS_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 2 TO 'E:\ORACLE\ORADATA\LIVE\UNDOTBS01.DBF';
> SET NEWNAME FOR DATAFILE 20 TO 'E:\ORACLE\ORADATA\LIVE
> \****DYNM_DATA.DBF';
> SET NEWNAME FOR DATAFILE 21 TO 'E:\ORACLE\ORADATA\LIVE
> \****DYNM_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 22 TO 'E:\ORACLE\ORADATA\LIVE
> \****DYNM_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 23 TO 'E:\ORACLE\ORADATA\LIVE
> \WORKFLOW_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 24 TO 'E:\ORACLE\ORADATA\LIVE
> \WORKFLOW_DATA.DBF';
> SET NEWNAME FOR DATAFILE 25 TO 'E:\ORACLE\ORADATA\LIVE
> \WORKFLOW_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 26 TO 'E:\ORACLE\ORADATA\LIVE
> \****AUDIT_DATA.DBF';
> SET NEWNAME FOR DATAFILE 27 TO 'E:\ORACLE\ORADATA\LIVE
> \****AUDIT_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 28 TO 'E:\ORACLE\ORADATA\LIVE
> \****TASK_DATA.DBF';
> SET NEWNAME FOR DATAFILE 29 TO 'E:\ORACLE\ORADATA\LIVE
> \****TASK_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 3 TO 'E:\ORACLE\ORADATA\LIVE\SYSAUX01.DBF';
> SET NEWNAME FOR DATAFILE 30 TO 'E:\ORACLE\ORADATA\LIVE
> \****TASK_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 31 TO 'E:\ORACLE\ORADATA\LIVE
> \******DBA_DATA1.DBF';
> SET NEWNAME FOR DATAFILE 32 TO 'E:\ORACLE\ORADATA\LIVE
> \******DBA_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 33 TO 'E:\ORACLE\ORADATA\LIVE
> \******DBA_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 34 TO 'E:\ORACLE\ORADATA\LIVE
> \****LOB_DATA1.DBF';
> SET NEWNAME FOR DATAFILE 35 TO 'E:\ORACLE\ORADATA\LIVE
> \****ARCH_DATA.DBF';
> SET NEWNAME FOR DATAFILE 36 TO 'E:\ORACLE\ORADATA\LIVE
> \****ARCH_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 37 TO 'E:\ORACLE\ORADATA\LIVE
> \****ARCH_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 38 TO 'E:\ORACLE\ORADATA\LIVE
> \********_DATA.DBF';
> SET NEWNAME FOR DATAFILE 39 TO 'E:\ORACLE\ORADATA\LIVE
> \********_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 4 TO 'E:\ORACLE\ORADATA\LIVE\USERS01.DBF';
> SET NEWNAME FOR DATAFILE 40 TO 'E:\ORACLE\ORADATA\LIVE
> \********_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 41 TO 'E:\ORACLE\ORADATA\LIVE
> \****LOB_DATA2.DBF';
> SET NEWNAME FOR DATAFILE 42 TO 'E:\ORACLE\ORADATA\LIVE\INDX01.DBF';
> SET NEWNAME FOR DATAFILE 43 TO 'E:\ORACLE\ORADATA\LIVE\TOOLS01.DBF';
> SET NEWNAME FOR DATAFILE 5 TO 'E:\ORACLE\ORADATA\LIVE
> \***_CONSTRAINT.DBF';
> SET NEWNAME FOR DATAFILE 6 TO 'E:\ORACLE\ORADATA\LIVE\***_DATA.DBF';
> SET NEWNAME FOR DATAFILE 7 TO 'E:\ORACLE\ORADATA\LIVE\***_INDEX.DBF';
> SET NEWNAME FOR DATAFILE 8 TO 'E:\ORACLE\ORADATA\LIVE
> \DBCC_REPOS.DBF';
> SET NEWNAME FOR DATAFILE 9 TO 'E:\ORACLE\ORADATA\LIVE\***_CTL.DBF';
>
> DUPLICATE TARGET DATABASE TO <destination_database>;
>
> }
>
> I've attempted to find the RMAN documentation entitled "Duplicating a
> database to a different host" but tahiti.oracle.com appears to be
> playing up again.
>
> HTH
>
> -g

tahiti.oracle.com is available. The following documentation will help you:

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1006672

Specifically, look into the initialisation parameters DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT

HTH -g Received on Wed Oct 07 2009 - 04:06:40 CDT

This message: [ Message body ]
Next message: Noons: "Re: Anyone out there successfully using Pro*C/C++ in C++ mode?"
Previous message: briandba: "Re: Index creation monitoring + select/inserts"
Maybe in reply to: trub3101: "RMAN Restore Failure ORA-01180 and ORA_01110"
Next in thread: trub3101: "Re: RMAN Restore Failure ORA-01180 and ORA_01110"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message