Re: RMAN Restore Failure ORA-01180 and ORA_01110

From: trub3101 <trub3101_at_sky.com>
Date: Wed, 7 Oct 2009 04:22:40 -0700 (PDT)
Message-ID: <b20a17a1-3b03-4f90-a735-08b25689f736_at_q14g2000vbi.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- Hide quoted text -
>
> - Show quoted text -

Thanks for your reply and input gazzag!

I managed to restore the original controlfiles and went back through the restore process. However, I am still getting the same error this time on the 'E:' drive which is present on this host!

creating datafile fno=1 name=E:\ORACLE\ORADATA\LIVE\SYSTEM01.DBF RMAN-00571:

---

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS

---

RMAN-00571:

---

RMAN-03002: failure of restore command at 10/07/2009 12:05:44 ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\LIVE\SYSTEM01.DBF'

Having successfully performed RMAN database duplication before I am in no doubt that what you have suggested will work. Still I am more than curious as to why the restore option should be failing in this manner.

Many thanks again,

tb3101 Received on Wed Oct 07 2009 - 06:22:40 CDT