Backup and Recovery - Loss of controlfile

Scenario - I have taken HOT backup, then created new tablespace testing1 (the backup controlf file doesn't have new tablespace information). I lost all controlfiles, Restoring controlfile from backup will get new tablespace?

1. I have taken HOT backup.

SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF

UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF

TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TOOLS01.DBF


6 rows selected.

2. Created tablesapce testing1 (datafile testing1.dbf)

SQL> create tablespace testing1 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF' size 10m;
Tablespace created.
SQL>

SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------TESTING1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF

SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF

UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF

TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TOOLS01.DBF

7 rows selected.

3. Shutdown DB

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

I deleted all controlfile.

SQL> startup;
ORACLE instance started.
Total System Global Area 167772160bytes
Fixed Size 1247876 bytes
Variable Size 88081788 bytes
Database Buffers 71303168 bytes
Redo Buffers 7139328 bytes

ORA-00205: error in identifying control file, check alert log for more info
SQL>

4. Shutdown the DB, and copy control file from backup, and start the database.

SQL> startup;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 88081788 bytes
Database Buffers 71303168 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'
ORA-01207: file is more recent than control file - old control file
SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 651721784 generated at 11/26/2008 10:50:57 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_13_1_671795289.ARCORA-00280: change 651721784 for thread 1 is in sequence #13

Specify log: {=suggested filename AUTO CANCEL}

ORA-00279: change 651722448 generated at 11/26/2008 11:37:59 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_14_1_671795289.ARC
ORA-00280: change 651722448 for thread 1 is in sequence #14
ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_13_1_671795289.ARC' no longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}

ORA-00283: recovery session canceled due to errorsORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'
ORA-01112: media recovery not started

SQL> recover database using backup controlfile until cancel;

ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004'
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004'


SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF';

Database altered.
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs*
ERROR at line 1:ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'
SQL>

SQL> recover database using backup controlfile;
ORA-00279: change 651723802 generated at 11/26/2008 11:50:03 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_14_1_671795289.ARC
ORA-00280: change 651723802 for thread 1 is in sequence #14


Specify log: {=suggested filename AUTO CANCEL}
auto
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15
ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_14_1_671795289.ARC' no longer needed for this recovery

ORA-00308: cannot open archived log 'D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>

The Recovery Process is asking for sequence # 15 and archived log file ''D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC' which is not even generated at archive log folder.
SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
4 13 NO CURRENT
6 12 YES INACTIVE
5 11 YES INACTIVE
SQL>

The sequence# 15 must be available in Online Redo Log files. Instead of giving CANCEL or AUTO, we can give Online Redo Log file name.

SQL> recover database using backup controlfile;
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15

Specify log: {=suggested filename AUTO CANCEL}
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO04.LOG
ORA-00310: archived log contains sequence 13; sequence 15 required
ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO04.LOG'

SQL> recover database using backup controlfile;
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15

Specify log: {=suggested filename AUTO CANCEL}
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO05.LOG
ORA-00310: archived log contains sequence 14; sequence 15 required
ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO05.LOG'

SQL> recover database using backup controlfile;
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15

Specify log: {=suggested filename AUTO CANCEL}
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO06.LOG
Log applied.
Media recovery complete.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------TESTING1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF

SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF

UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF

TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TOOLS01.DBF

7 rows selected.

SQL>

Testing1 tablespace is recoverd.