This document will detail two recovery scenarios dealing with a missing datafile using a RMAN disk based backup. If you need a document for configuring RMAN see the post Setting up RMAN for backup and recovery using Database Control
Scenario:
You receive an email from a user reporting the error below when attempting to create a table in their default tablespace.
SQL> create table test as select * from user_objects;
create table test as select * from user_objects
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u02/app/oracle/oradata/orcl/test.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
You look on the file system and you see that the file test.dbf is indeed no longer present.
[oracle@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/ arch ctl redo system01.dbf undotbs01.dbf backup example01.dbf sysaux01.dbf temp01.dbf users01.dbf [oracle@ora1 ~]$
Action:
Identify the tablespace in which the missing data file is a member.
SQL> select tablespace_name from dba_data_files where file_id = 6; TABLESPACE_NAME ------------------------------ TEST SQL>
In this scenario the database does not need to be shutdown in order to recover the missing data file, however, the tablespace TEST does need to be taken offline.
SQL> alter tablespace test offline immediate; Tablespace altered. SQL>
We now connect to the database through RMAN and restore the missing data file.
[oracle@ora1 ~]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 20 11:59:53 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: ORCL (DBID=1239150297) RMAN> restore datafile 6; Starting restore at 20-APR-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=44 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=46 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to /u02/app/oracle/oradata/orcl/test.dbf channel ORA_DISK_1: reading from backup piece /u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1 channel ORA_DISK_1: piece handle=/u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1 tag=TAG20100420T091928 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 20-APR-10 RMAN>
At this point data file 6 has been recovered now we perform recovery on the file.
RMAN> recover datafile 6; Starting recover at 20-APR-10 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:02 Finished recover at 20-APR-10 RMAN>
All that is left is to bring the tablespace TEST back online.
SQL> alter tablespace test online; Tablespace altered. SQL>
With recovery complete the user is now able to create tables in their default tablespace.
SQL> create table test as select * from user_objects; Table created. SQL> select tablespace_name from user_tables where table_name ='TEST'; TABLESPACE_NAME ------------------------------ TEST SQL>
Scenario:
You attempt to start the database and see the following error:
SQL> startup ORACLE instance started. Total System Global Area 849530880 bytes Fixed Size 1339824 bytes Variable Size 528485968 bytes Database Buffers 314572800 bytes Redo Buffers 5132288 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u02/app/oracle/oradata/orcl/test.dbf' SQL>
You check the location and verify that the file is indeed missing.
ACTION:
The missing datafile will need to be restored from a backup. Currently the database is mounted which is required for recovery so we can just go straight into RMAN.
After connecting we restore datafile 6.
[oracle@ora1 ~]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 20 15:17:14 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: ORCL (DBID=1239150297, not open) RMAN> restore datafile 6; Starting restore at 20-APR-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=17 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=22 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to /u02/app/oracle/oradata/orcl/test.dbf channel ORA_DISK_1: reading from backup piece /u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1 channel ORA_DISK_1: piece handle=/u02/app/oracle/oradata/orcl/backup/0qlbj7p1_1_1 tag=TAG20100420T091928 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-APR-10 RMAN>
Next we recover datafile 6.
RMAN> recover datafile 6; Starting recover at 20-APR-10 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 20-APR-10 RMAN> And finally we open the database. RMAN> alter database open; database opened RMAN>
I have a script that I use to run my rmans from. I manually move and delete the rman files, but I set an expire time in rman. Should I let rman delete the files? I do one day for a full backup and then I do incremental backups the rest of the week, so should I set me expire time for 7 days and just issue deleted expired backups, while doing my full and incremental backups?
If you move the RMAN backups and delete them outside of RMAN through OS commands you could run into some problems later as RMAN still thinks that backups are on the backup device.
Before coming up with a policy to remove the backups it would be wise to discuss with the data owners what their expectations are concerning the backups. These discussions will help you determine how to configure RMAN to handle old backups. Unfortunately the owners will most likely not be versed in Oracle terminology so you will need to be.
Oracle allows two ways to identify which backups to delete and you can only specify one or the other. One method is backup redundancy which stats how many backups you want to keep. For example if you set the redundancy to 7 after the 8th backup the 1st backup is eligible for deletion. Note that this number has no correlation to the day. If you take 7 backups in 1 day and 8th the next, the 1st back is still eligible for deletion.
The other method is based on a recovery window. If you set a recovery window of 7 days after the 8th day the backups taken the 1st day are eligible for deletion. The recovery window is based on days. Days will most likely be the terminology used when discussing backups with the data owners.
Once you discuss the backups with owners of the data and you have a plan to go forward make the appropriate changes in RMAN.
For example if the owners want the last 7 backups issue a command like below in RMAN while connected to the databases.
If the owners want the last 7 days issue this command.
Next you should clean up the old backups and records associated with them using crosscheck followed by a delete expired.
Thanks for the nice post.
I done with everything but at the last tablespace SA(in my case) back online.
I am getting this error. can you please help out here.
SQL> alter tablespace SA online;
alter tablespace SA online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: ‘/u01/app/oracle/product/11.2.0/db_1/dbs/tbs_data.dat’
Thanks
gajanan
SQL> select count (*) from M_LIC_SB;
ERROR:
ORA-12801: error signaled in parallel query server P002
ORA-01116: error in opening database file 45
ORA-01110: data file 45: ‘/home/bkp_kndnew/exp_custom20.dbf’
ORA-07368: sfofi: open error, unable to open database file.
SVR4 Error: 2: No such file or directory
have not taken the backup of this file now how i can recover this file