Recovery of non system datafile [ without backup ]

Environment: 10gR2 on CentOS 4

Assumptions:
1]The database is in archive log mode.

A non-system datafile can be recovered even if no backups are available for that datafile, provided all the archive logs since the creation of the datafile are available and the datafile is added after the control file is created. Datafiles created before the creation of the controlfile cannot be recoverd by this method.

[oracle@spade ~]$ sqlplus scott/tiger

SQL> insert into j_emp select * from j_emp;
insert into j_emp select * from j_emp
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/burp/oradata/booms.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

When checked the datafile booms.dbf is missing. But there is not any backup for this datafile
to perform open recovery

Steps for Database in OPEN mode
===============================
1] Take tablespace offline with immediate option;
2] SQL> alter database create datafile ‘<datafile_name>’;
3] Recover the tablespace
4] Online the tablespace

Steps for Database in SHUTDOWN
===============================
1] Mount the database
2] Offline the datafile [SQL>alter database datafile 5 offline;]
3] Open the database
4] SQL> alter database create datafile ‘<datafile_name>’;
5] Recover the tablespace
6] Online the tablespace
To restore the datafile to a different location:

SQL>alter database create datafile '<datafile_name_old>' as '<datafile_name_new>';

**examples down are for the database in ‘open’ mode.

===ARENA===

SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME      OPEN_MODE  CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP      READ WRITE             208496          206897

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/burp/oradata/system01.dbf                     NO  YES             208496
/u01/burp/oradata/undotbs01.dbf                    NO  YES             208496
/u01/burp/oradata/sysaux01.dbf                     NO  YES             208496
/u01/burp/oradata/users01.dbf                      NO  YES             208496
                                                                            0
SQL>

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5                                                   0          5 0                                              0          0
SQL>

Since the datafile isn’t available we need to take the tablespace offline with IMMEDIATE option

SQL> alter tablespace booms offline immediate;

Tablespace altered.

Now we will create the datafile

SQL> alter database create datafile '/u01/burp/oradata/booms.dbf';

Database altered

The datafile is created but the checkpoint is lagging behind other datafiles.

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 205403           BOOMS                         0        139

SQL> recover tablespace booms;

Excerpt from alert.log
==============

ALTER DATABASE RECOVER  tablespace booms
Wed Oct  8 11:38:39 2008
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER  tablespace booms  ...
Wed Oct  8 11:38:56 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_139_667494933.arch
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
:
:
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_144_667494933.arch
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Oct  8 11:38:56 2008
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Oct  8 11:38:56 2008
Media Recovery Log /u01/burp/arch/1_145_667494933.arch
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 146 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo02.log
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 3 Seq 147 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo03.log
Wed Oct  8 11:38:57 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 148 Reading mem 0
  Mem# 0 errs 0: /u01/burp/oradata/redo01.log
Wed Oct  8 11:38:58 2008
Media Recovery Complete (burp)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT

==============================

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 208695           BOOMS                         0          0

SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;

SUBSTR(NAME,1,50)                                  REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/burp/oradata/system01.dbf                     NO  YES             208496
/u01/burp/oradata/undotbs01.dbf                    NO  YES             208496
/u01/burp/oradata/sysaux01.dbf                     NO  YES             208496
/u01/burp/oradata/users01.dbf                      NO  YES             208496
/u01/burp/oradata/booms.dbf                        NO  NO              208695
SQL>

The archive logs until 148 were applied and booms.dbf has become consistent and can be ONLINED

SQL> alter tablespace booms online;

Tablespace altered.

SQL>  select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

  FILE_NUM FILE_NAME                                      TYPE   VALIDITY CHK              TABLESPACE_NAME          STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- -------------------- ---------- ----------
         1 /u01/burp/oradata/system01.dbf                    3          0 208496           SYSTEM                     8196        148
         2 /u01/burp/oradata/undotbs01.dbf                   3          0 208496           UNDOTBS1                      4        148
         3 /u01/burp/oradata/sysaux01.dbf                    3          0 208496           SYSAUX                        4        148
         4 /u01/burp/oradata/users01.dbf                     3          0 208496           USERS                         4        148
         5 /u01/burp/oradata/booms.dbf                       3          0 208973           BOOMS                         4        148

SQL>
Advertisements

3 thoughts on “Recovery of non system datafile [ without backup ]

  1. Pingback: Index « My confrontations with oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s