RMAN Restore Failure ORA-01180 and ORA_01110
trub3101
I am trying to restore database a to another server. However, after
including a script to change the datafile and redo log locations from
drive G: to drive E:, restore database, switch datafile all and
recover database commands in the RMAN run command and run it, I get
the following error message:
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/06/2009 14:51:44
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'G:\ORACLE\ORADATA\LIVE\SYSTEM01.DBF'
The server which I am restoring database a to does not have a drive G:
I have checked the permissions for the RMAN backup files, there is
more than enough room on the E: drive I have even set the database
incarnation to 1 hoping this might help.
Why is restore attempting to create the system datafile on the a non-
existent drive, G: ?
Thanks in advance for any assistance received.
tb3101
gazzag
Could we see the script please?
HTH
-g
trub3101
>
> - Show quoted text -
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
gazzag
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 /@<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:
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
gazzag
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
trub3101
>
> - 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
gazzag
<snip>
> 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
You shouldn't be restoring your control files. That's your problem.
You should be using RMAN's DUPLICATE DATABASE functionality.
HTH
-g
trub3101
>
> - Show quoted text -
Hi Gazzag,
So having managed to restore my original control files i.e. as they
were before any rename/restore/switch datafile/recover attempts, I
cannot now just rename/restore/switch datafile and recover?
Sorry to bleat on about this.
Thanks,
tb3101
gazzag
>
Perhaps I'm misunderstanding you, what do you mean by "original
control files"?
trub3101
>
> - Show quoted text -
HI gazzag,
Apologises for not getting back to you sooner. I believe I may
confusing the issue here. What I am aiming to do is to re-create the
database on another host without having to use the use the original
'target' database which is no longer available i.e. the host server
motherboard had fried!
I know I should have a standby in place but lets just say for all
intents and purposes that the host for the standby was also
unavailable (flood damage!), I was hoping that my original restore
procedure would be able to re-create the database on a brand new
host.
I am quite surprise that this does not seem to be the case.
tb3101
trub3101
Hi all,
It now transpires that the RMAN backup files (pieces) had somehow been
transferred across from the original host in an incomplete state.
After transferring the backup files correctly I ran through the
restore procedure again and re-created the database on the desired
host.
Thanks again for your input, gazzag. Much apprecitated.
tb3101