Hello all,
First off, I apologize for the length of this post. After reading through a number of posts here, I saw how some people don't
include enough detail and I didn't want to make that mistake.
At the beginning of December 2011, one of the hard drives on our Oracle box started giving signs that it was going to
give up the ghost pretty soon (the machine had two drives in an LVM VG). I was able to take a full backup (including
an autobackup of the spfile and controlfile) and get it off of the machine before the drive failed completely. The
machine is not bootable but I can still access the other drive when I boot with a LiveCD. Unfortunately, most of the
Oracle datafiles are not accessible. Because of this, I have to restore the DB on a different host. I have been reading through
the RMAN documentation, specifically, Chapter 7 of the DB Backup and Recovery Advanced User's Guide which details how
to restore and recover the database on a new host. I have managed to restore the spfile and the control file and mount
the database but now, when I try to restore the database, I am getting an RMAN error saying that "no backup or copy of
datafile n found to restore" where n is datafiles 1 through 4. These just happen to be important datafiles like the
system, undotbs and sysaux datafiles. :) Here is what I have done so far:
--I installed 10g R2 and patchset 3 so the database on the new host is now on 10.2.0.4, just like the old host.
--I made the SID the same as the source database but the directory structure is different, i.e. the installation
directory/Oracle Home are in different physical locations than the source database.
--The backups are accessible on a network attached storage drive which I have mounted in the filesystem. I have
played around with mounting this in different locations, as I'll mention below, but I may not be mounting this
in the correct location so, if I'm doing something wrong, please correct me.
--I shut down the dbconsole and isqlplusctl and also shutdown the database. I left the listener running.
--Per the documentation, I connected to RMAN and set the DBID to match the source DBID with the following set of
commands (the results follow):
%RMAN target / nocatalog
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 27 15:38:46 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> set dbid 182290175;
executing command: SET DBID
RMAN> startup nomount
Oracle instance started
Total System Global Area 289406976 bytes
Fixed Size 1296332 bytes
Variable Size 125831220 bytes
Database Buffers 159383552 bytes
Redo Buffers 2895872 bytes
I was surprised that, contrary to what the documentation said, I received no error message that startup had failed
because the parameter file could not be opened. I didn't know if this was a problem or not, so I proceeded on.
--Next, I restored the spfile and shut down the database. Since the files were stored on a NAS, I mounted the directory at /mnt
and used the following commands (the results follow):
RMAN> restore spfile to pfile "/ora10g/app/oracle/product/10.2.0/db_1/dbs/initTARGETSID.ora" from "/mnt/flash_recovery_area/
TARGETSID/autobackup/2011_12_07/o1_mf_s_769259240_7fzbtt3g_.bkp";
Starting restore at 27-FEB-12
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /mnt/flash_recovery_ar
ea/TARGETSID/autobackup/2011_12_07/o1_mf_s_769259240_7fzbtt3g_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 27-FEB-12
RMAN> shutdown abort;
Oracle instance shut down
--At this point I edited the restored PFILE and changed the location specific parameters to be the correct locations
on this new machine. After that, I started the database with the new PFILE with the following commands (results
follow):
RMAN> startup force nomount pfile="/ora10g/app/oracle/product/10.2.0/db_1/dbs/initTARGETSID.ora";
Oracle instance started
Total System Global Area 364904448 bytes
Fixed Size 1296764 bytes
Variable Size 109053572 bytes
Database Buffers 251658240 bytes
Redo Buffers 2895872 bytes
--Next, I restored the control file from the autobackup and mounted the database with the following commands (results
follow):
RMAN> restore controlfile from "/mnt/flash_recovery_area/TARGETSID/autobackup/2011_12_07/o1_mf_s_769259240_7fzbtt3g_.bkp";
Starting restore at 27-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
output filename=/ora10g/app/oracle/oradata/TARGETSID/CONTROL01.CTL
output filename=/ora10g/app/oracle/oradata/TARGETSID/CONTROL02.CTL
output filename=/ora10g/app/oracle/oradata/TARGETSID/CONTROL03.CTL
Finished restore at 27-FEB-12
I then queried the v$datafile and v$logfile tables, as recommended in the docs, so that I could construct the RMAN
script to restore the database. Here is what the script looks like:
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
SET NEWNAME FOR DATAFILE 1 TO "/ora10g/app/oracle/oradata/TARGETSID/system01.dbf";
SET NEWNAME FOR DATAFILE 2 TO "/ora10g/app/oracle/oradata/TARGETSID/undotbs01.dbf";
SET NEWNAME FOR DATAFILE 3 TO "/ora10g/app/oracle/oradata/TARGETSID/sysaux01.dbf";
SET NEWNAME FOR DATAFILE 4 TO "/ora10g/app/oracle/oradata/TARGETSID/users01.dbf";
...Removed some lines to save space...
SET NEWNAME FOR DATAFILE 41 TO "/ora10g/app/oracle/oradata/TARGETSID/TESTDATA.DBF";
SQL "ALTER DATABASE RENAME FILE ''/opt/oracle/app/oracle/oradata/TARGETSID/redo01.log''
TO ''/ora10g/app/oracle/oradata/TARGETSID/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/opt/oracle/app/oracle/oradata/TARGETSID/redo02.log''
TO ''/ora10g/app/oracle/oradata/TARGETSID/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/opt/oracle/app/oracle/oradata/TARGETSID/redo03.log''
TO ''/ora10g/app/oracle/oradata/TARGETSID/redo03.log'' ";
SET UNTIL TIME "TO_DATE('12/07/2011','MM/DD/YYYY')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}
EXIT
--This is where things started to go wrong. At first, I tried to manually specify the backup file
that I wanted the restore operation to use. I did this by using the following command:
RESTORE DATABASE FROM "<path to backup file>".
I got an error message, though, saying that only SPFILEs and CONTROL FILEs could be restored from autobackup.
After that, I mounted the NAS directory which holds the backup files under the database's current flash_recovery_area
directory. This seemed to work, and I'm hoping that this was the right thing to do.
--After fixing the RESTORE DATABASE command, When I ran the script, I got the following error message:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 02/27/2012 14:37:59
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE RENAME FILE
'/opt/oracle/app/oracle/oradata/TARGETSID/redo01.log'TO '/ora10g/app/oracle/oradata/TARGETSID/redo01.log'
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile "/opt/oracle/app/oracle/oradata/TARGETSID/redo01.log"
--I played around with the script and found that I got this same error message for redo02.log and redo03.log as well.
I'm pretty sure that this is bad, but I can't find anything online regarding what to do to correct this. In the end,
I commented out those lines and reran the script. Once I did, here is the error message that I got:
RMAN> @/home/oracle/Documents/reco_script.rman
RMAN> RUN
2> {
3> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
4> SET NEWNAME FOR DATAFILE 1 TO "/ora10g/app/oracle/oradata/TARGETSID/system01.dbf";
5> SET NEWNAME FOR DATAFILE 2 TO "/ora10g/app/oracle/oradata/TARGETSID/undotbs01.dbf";
6> SET NEWNAME FOR DATAFILE 3 TO "/ora10g/app/oracle/oradata/TARGETSID/sysaux01.dbf";
7> SET NEWNAME FOR DATAFILE 4 TO "/ora10g/app/oracle/oradata/TARGETSID/users01.dbf";
8> SET NEWNAME FOR DATAFILE 5 TO "/ora10g/app/oracle/oradata/TARGETSID/example01.dbf";
...Removed some lines to save space...
44> SET NEWNAME FOR DATAFILE 41 TO "/ora10g/app/oracle/oradata/TARGETSID/TESTDATA.DBF";
45> #SQL "ALTER DATABASE RENAME FILE ''/opt/oracle/app/oracle/oradata/TARGETSID/redo01.log''
46> # TO ''/ora10g/app/oracle/oradata/TARGETSID/redo01.log'' ";
47> #SQL "ALTER DATABASE RENAME FILE ''/opt/oracle/app/oracle/oradata/TARGETSID/redo02.log''
48> # TO ''/ora10g/app/oracle/oradata/TARGETSID/redo02.log'' ";
49> #SQL "ALTER DATABASE RENAME FILE ''/opt/oracle/app/oracle/oradata/TARGETSID/redo03.log''
50> # TO ''/ora10g/app/oracle/oradata/TARGETSID/redo03.log'' ";
51>
52> #I tried this first: SET UNTIL TIME "TO_DATE('12/07/2011','MM/DD/YYYY')";
53> SET UNTIL SCN 111442173;
54> RESTORE DATABASE;
55> SWITCH DATAFILE ALL;
56>
57> RECOVER DATABASE;
58> }
allocated channel: c1
channel c1: sid=156 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
...Removed some lines to save space...
executing command: SET NEWNAME
executing command: SET until clause
Starting restore at 27-FEB-12
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/27/2012 14:42:56
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> EXIT
--I have tried running "RMAN>list backup;" and it shows that the 4 "missing" datafile backups are included in the full
backup which I'm trying to restore. I know, though, that since I'm not using a recovery catalog that the backup
information is being read from the restored control file. The output of that command shows a whole bunch of old
archived redo log backups that I don't think are there any more so I'm pretty sure that I'm just seeing what the
control file thinks is in the backup and not what is actually in the backup. Is there any way that I can point RMAN
to the backup file that I'm using and ask it to read the backup file to see if those backups are in there or not? I was
thinking of cataloging the backup, but the documentation seemed to indicate that I shouldn't do that at this point.
Perhaps I'm misunderstanding that, though...
--I searched around for a while online and saw that somebody suggested executing a RESTORE DATABASE PREVIEW command.
Here is what I get:
RMAN> restore database preview;
Starting restore at 29-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
data file 5 will be created automatically during restore operation
data file 6 will be created automatically during restore operation
...Removed some lines to save space...
data file 41 will be created automatically during restore operation
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/29/2012 15:00:14
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
I'm trying to figure out why RMAN is saying that those backups aren't there. After the full backup, I copied the entire
flash recovery area directory up to the NAS specifically so that I wouldn't miss copying any of the backup files. Is there
any way to have RMAN examine the backup that I have to see why it can't find those datafiles? In the restored control
file, it says that those files were included in the backup, so I don't understand what could be going wrong. Any help in
understanding this or pointing me in the right direction of something to read would really be appreciated.
Thanks,
John