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>
