Re: RMAN Restore Failure ORA-01180 and ORA_01110

From: gazzag <gareth_at_jamms.org>
Date: Tue, 6 Oct 2009 09:12:52 -0700 (PDT)
Message-ID: <51019e42-296e-4c8c-827a-f91c277cb6b3_at_o13g2000vbl.googlegroups.com>

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 Received on Tue Oct 06 2009 - 11:12:52 CDT