In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Sunday, October 31, 2010

Recover a missing/excluded datafile after re-create new control file from controlfile trace file

This is the situation in which lost all control file, and DBA accidently create control file (recreated) with old controlfile trace file backup. After recover,DBA found some datafile is missing.
Here is the steps to recover those missing database datafiles.
16:33:32 SQL> select file#,name from v$datafile;

     FILE# NAME
---------- ---------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf

16:33:43 SQL> set time off;
SQL> set time off;
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- ---------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter system switch logfile;

System altered.



SQL> alter database backup controlfile to trace;

Database altered.

SQL>

[oracle@rac1 udump]$ cp rev1_ora_6901.trc /home/oracle/Desktop/ctlnew.sql

[oracle@rac1 udump]$ pwd
/u01/app/oracle/admin/rev1/udump
[oracle@rac1 udump]$


SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/rev1/users02.dbf' size 2m;

Tablespace altered.

SQL> select file#,name from v$datafile; 
     FILE# NAME
---------- ---------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf
         5 /u01/app/oracle/oradata/rev1/users02.dbf

SQL>
CREATE CONTROLFILE REUSE DATABASE "REV1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/rev1/redo01.log'  SIZE 5M,
  GROUP 2 '/u01/app/oracle/oradata/rev1/redo02.log'  SIZE 5M,
  GROUP 3 '/u01/app/oracle/oradata/rev1/redo03.log'  SIZE 5M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/rev1/system01.dbf',
  '/u01/app/oracle/oradata/rev1/undotbs01.dbf',
  '/u01/app/oracle/oradata/rev1/sysaux01.dbf',
  '/u01/app/oracle/oradata/rev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@rac1 udump]$ rm -rf /u01/app/oracle/oradata/rev1/*.ctl

[oracle@rac1 udump]$ rm -rf /u01/app/oracle/oradata/rev1/*.ctl
[oracle@rac1 rev1]$ pwd
/u01/app/oracle/oradata/rev1
[oracle@rac1 rev1]$ ls -ltr
total 791480
-rw-r-----  1 oracle oinstall 104865792 Oct 31 15:44 temp01.dbf
-rw-r-----  1 oracle oinstall   5243392 Oct 31 16:30 redo02.log
-rw-r-----  1 oracle oinstall   5243392 Oct 31 16:39 redo03.log
-rw-r-----  1 oracle oinstall   2105344 Oct 31 16:53 users02.dbf
-rw-r-----  1 oracle oinstall  10493952 Oct 31 16:53 users01.dbf
-rw-r-----  1 oracle oinstall  26222592 Oct 31 16:53 undotbs01.dbf
-rw-r-----  1 oracle oinstall 503324672 Oct 31 16:53 system01.dbf
-rw-r-----  1 oracle oinstall 251666432 Oct 31 16:53 sysaux01.dbf
-rw-r-----  1 oracle oinstall   5243392 Oct 31 16:53 redo01.log
[oracle@rac1 rev1]$


SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             146801344 bytes
Database Buffers          293601280 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> select status from v$instance;

STATUS
----------
STARTED

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             146801344 bytes
Database Buffers          293601280 bytes
Redo Buffers                2973696 bytes

SQL> @/home/oracle/Desktop/recreate_ctlfile.sql

Control file created.


SQL> recover database using backup controlfile;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database using backup controlfile;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.
SQL> col name format a55
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -------------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf
         5 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00005

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00005' to '/u01/app/oracle/oradata/rev1/users02.dbf';

Database altered.

SQL> recover datafile 5;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo01.log ORA-00342: archived log does not have expected resetlogs SCN 805021
ORA-00334: archived log: '/u01/app/oracle/oradata/rev1/redo01.log'


SQL> recover datafile 5;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> select * from v$recover_file;

no rows selected

SQL> select file#,name from v$datafile; 
     FILE# NAME
---------- -------------------------------------------------------
         1 /u01/app/oracle/oradata/rev1/system01.dbf
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
         4 /u01/app/oracle/oradata/rev1/users01.dbf
         5 /u01/app/oracle/oradata/rev1/users02.dbf

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                    STATUS
---------- ------------------------------------------------------- ----------
         1 /u01/app/oracle/oradata/rev1/system01.dbf               SYSTEM
         2 /u01/app/oracle/oradata/rev1/undotbs01.dbf              ONLINE
         3 /u01/app/oracle/oradata/rev1/sysaux01.dbf               ONLINE
         4 /u01/app/oracle/oradata/rev1/users01.dbf                ONLINE
         5 /u01/app/oracle/oradata/rev1/users02.dbf                OFFLINE

SQL>
SQL> alter database datafile 5 online;

Database altered.

SQL> alter system switch logfile;

System altered.
reference:
http://hemantoracledba.blogspot.com/2010/08/adding-datafile-that-had-been-excluded.html
Posted by Rajeshkumar Govindarajan at Sunday, October 31, 2010
Labels: , ,

6 comments:

Jugal Kishore said...

Thank you Rajesh and Antony.
For excellent notes of dataguard.
Jugal Kishore

November 11, 2010 at 2:42 PM
Rajeshkumar Govindarajan said...

hearty thanks jugal kishore for your appreciation.

November 16, 2010 at 6:33 PM
Sajeeva Lakmal said...

Hi,
Did you specify the log or just hit to use the suggested one. If you specify the log, how did you know the correct log file which contains required scn.

January 12, 2012 at 7:43 PM
Unknown said...

Hi,

Brother an excellent document , well aligned , proper formatted. Helped a lot. Thank You for the same ! !

November 21, 2013 at 6:22 PM
Unknown said...

I faced the same issue like forget to add the datafile in the controlfile and brought up the database with open resetlogs.

after we got to know the file is missing from v$recover_file

QL> alter database open resetlogs;

Database altered.


SQL> select * from v$recover_file;
FILE# NAME
---------- -------------------------------------------------------
1 /u01/app/oracle/oradata/rev1/system01.dbf
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
4 /u01/app/oracle/oradata/rev1/users01.dbf
5 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING0001203

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING0001203' to '/oracle/test/oradata96/oeard_38.dbf';

Database altered.

SQL> recover datafile 1203;

ERROR at line 1:
ORA-01190: controlfile or data file 1203 is from before the last RESETLOGS
ORA-01110: data file 1203: '/oracle/test/oradata96/oeard_38.dbf'
Let me know any other work around

March 24, 2014 at 11:11 AM
Unknown said...

QL> alter database open resetlogs;

Database altered.


SQL> select * from v$recover_file;
FILE# NAME
---------- -------------------------------------------------------
1 /u01/app/oracle/oradata/rev1/system01.dbf
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
4 /u01/app/oracle/oradata/rev1/users01.dbf
5 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING0001203

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING0001203' to '/oracle/test/oradata96/oeard_38.dbf';

Database altered.

SQL> recover datafile 1203;

ERROR at line 1:
ORA-01190: controlfile or data file 1203 is from before the last RESETLOGS
ORA-01110: data file 1203: '/oracle/test/oradata96/oeard_38.dbf'
let us know can we recover the datafile

March 24, 2014 at 11:12 AM

Post a Comment

Subscribe to: Post Comments (Atom)
free counters
 
Share/Bookmark