Oracle Backup - 2

OERR: ORA 1244 unnamed datafile(s) added to controlfile by media recovery

Error: ORA 1244
Text: "unnamed datafile(s) added to controlfile by media recovery"
-------------------------------------------------------------------------------
Cause:  Media recovery with a backup controlfile or a controlfile that
          was rebuilt, encountered the creation of a datafile that was not
          in the controlfile. An entry has been added to the controlfile
          for the new datafiles, but with the file name UNNAMEDnnnn, where
          nnnn is the file number. Attached errors describe the file names
          that were originally used to create the files. 
Action: Rename the files to valid file names and resume recovery. If
          necessary the command ALTER DATABASE CREATE DATAFILE may be used
          to create a file suitable for recovery and do the rename. If the
          file is not going to be recovered then take it offline with the
          DROP option.

Scenario when the controlfile is lost and there was a datafile added to an existing table space after backup of the controlfile was taken so the controlfile does not know about the new datafile.

1) Start database in nomount state
SQL> connect /as sysdba

SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

2) Connect to rman to restore control file, to restore the controfile it requires setting DBID
$ $ORACLE_HOME/bin/rman

RMAN> SET DBID 1998785623

executing command: SET DBID

run {
restore controlfile from '/u01/datafile/rmanbkp/TEST_3llv5k9u_117';
}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 13-JAN-14
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20101212
channel ORA_DISK_1: autobackup found: /u01/datafile/rmanbkp/c-1998785623-20101212-01
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/datafile/control1.ora
output filename=/u01/datafile/control2.ora
output filename=/u01/datafile/control3.ora
Finished restore at 13-JAN-14

3) Mount database so one can recover the database
SQL> alter database mount;

Database altered.

4) Recover database (It will issue error about the controlfile does not know about the new datafile)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12645623 generated at 12/12/2010 11:16:24 needed for thread 1
ORA-00289: suggestion : /u01/datafile/arch/1_43_737899751.dbf
ORA-00280: change 12645623 for thread 1 is in sequence #43

Note here the archive log file specified by Oracle is 1_43_737899751.dbf but it does not exists so the file needed is the redo log so using ls find the redo log file needed in this case is redo3b.log so it is specified when restoring
$ ls -lrt
..
-rw-r—– 1 oracle oinstall 10087936 JAN 13 22:11 1_40_737899751.dbf
-rw-r—– 1 oracle oinstall 10087936 JAN 13 23:56 1_41_737899751.dbf
-rw-r—– 1 oracle oinstall 10087936 JAN 13 11:16 1_42_737899751.dbf

$ ls -lrt redo*.log
total 1518908
-rw-r—– 1 oracle oinstall 10486272 JAN 13 23:56 redo1b.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 23:56 redo1a.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 11:16 redo2b.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 11:16 redo2a.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 19:44 redo3b.log
-rw-r—– 1 oracle oinstall 10486272 JAN 13 19:44 redo3a.log

So restoring specifying the redo log file redo3b.log

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/datafile/redo3b.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 8: '/u01/datafile/test_04.dbf’

Note the recovery didn’t start as the control file found a datafile it does not know about.

ORA-01112: media recovery not started

5) As datafile is in mount state V$datafile can be queried it shows the new datafile is called /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/datafile/system_01.dbf
/u01/datafile/undo_01.dbf
/u01/datafile/sysaux_01.dbf
/u01/datafile/test_01.dbf
/u01/datafile/test.dbf
/u01/oradata/test_02.dbf
/u01/datafile/test_03.dbf
/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008

6) Shows the datafile does not physical exists
$ ls -l /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
ls: /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008: No such file or directory

7) Trying to recover database again it reports the filename needs to be updated in the controfile before proceeding
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008'

8) Create datafile from UNNAMED00008 to update controfile
SQL> alter database create datafile '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008' as '/u01/datafile/test_04.dbf’;

Database altered.

9) Check v$datafile after the datafile is created note the control file knows about the datafile
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/datafile/system_01.dbf
/u01/datafile/undo_01.dbf
/u01/datafile/sysaux_01.dbf
/u01/datafile/test_01.dbf
/u01/datafile/test.dbf
/u01/oradata/test_02.dbf
/u01/datafile/test_03.dbf
/u01/datafile/test_04.dbf

10) Recover the database
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12654692 generated at 12/12/2010 19:43:13 needed for thread 1
ORA-00289: suggestion : /u01/datafile/arch/1_43_737899751.dbf
ORA-00280: change 12654692 for thread 1 is in sequence #43

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/datafile/redo3b.log
Log applied.
Media recovery complete.

11) Open database with resetlogs
SQL> alter database open resetlogs;

Database altered.


Enjoy !!