Environment: 10gR2 on CentOS 4
Assumptions:
1]The database is in archive log mode.
2]A solid cold backup is available.
3]The database is in OPEN mode.
$sqlplus scott/tiger
SQL> insert into j_emp select * from emp;
insert into j_emp select * from emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oradata/burp/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Since the missing datafile is a non-system, the recovery can be done online.
Users that does not use this tablespace data will continue to work as normal.
Steps
=====
1]Offline the tablespace with immediate option.
2]Restore the missing datafile from the latest backup.
3]Recover the datafile.
4]Online the tablespace.
ARENA
======
SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
NAME OPEN_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP READ WRITE 506486 495256
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
4 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;
SUBSTR(NAME,1,50) REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf NO YES 506486
/u01/oradata/burp/undotbs01.dbf NO YES 506486
/u01/oradata/burp/sysaux01.dbf NO YES 506486
0
/u01/oradata/burp/example01.dbf NO YES 506486
SQL>
The tablespace should be taken offline with immediate option, as just ‘offline’ would yield us the below.
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01191: file 4 is already offline - cannot do a normal offline
ORA-01110: data file 4: '/u01/oradata/burp/users01.dbf'
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL>
Restore the missing datafile to the oradata location.
cp -rp /u01/oradata/backup/users01.dbf /u01/oradata/burp/
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
4 OFFLINE OFFLINE 494404 20-SEP-08
SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;
SUBSTR(NAME,1,50) REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf NO YES 506486
/u01/oradata/burp/undotbs01.dbf NO YES 506486
/u01/oradata/burp/sysaux01.dbf NO YES 506486
/u01/oradata/burp/users01.dbf YES NO 494404
/u01/oradata/burp/example01.dbf NO YES 506486
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; 2 3
FILE_NUM FILE_NAME TYPE VALIDITY CHK TABLESPACE_NAME STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
1 /u01/oradata/burp/system01.dbf 3 0 506486 SYSTEM 8196 18
2 /u01/oradata/burp/undotbs01.dbf 3 0 506486 UNDOTBS1 4 18
3 /u01/oradata/burp/sysaux01.dbf 3 0 506486 SYSAUX 4 18
4 /u01/oradata/burp/users01.dbf 3 0 494404 USERS 0 12
5 /u01/oradata/burp/example01.dbf 3 0 506486 EXAMPLE 4 18
SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
1 1 17 1 YES INACTIVE 495261
2 1 18 1 NO CURRENT 495416
3 1 16 1 YES INACTIVE 495197
SQL>
We only need archive logs from sequence 12,13,14 and 15; as sequence 16,17 and 18 are still with the redo logs. Confirm that the archive logs from sequence 12,13,14 and 15 are present in the archive log destination.
SQL> recover datafile 4; Excerpt from alert.log file ========================================================= Tue Oct 7 00:48:01 2008 ALTER DATABASE RECOVER datafile 4 Tue Oct 7 00:48:01 2008 Media Recovery Start ORA-279 signalled during: ALTER DATABASE RECOVER datafile 4 ... Tue Oct 7 00:48:15 2008 ALTER DATABASE RECOVER CONTINUE DEFAULT Tue Oct 7 00:48:15 2008 Media Recovery Log /u01/oradata/arch/1_12_665715452.dbf ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... Tue Oct 7 00:48:15 2008 ALTER DATABASE RECOVER CONTINUE DEFAULT Tue Oct 7 00:48:15 2008 Media Recovery Log /u01/oradata/arch/1_13_665715452.dbf ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... Tue Oct 7 00:48:15 2008 ALTER DATABASE RECOVER CONTINUE DEFAULT Tue Oct 7 00:48:15 2008 Media Recovery Log /u01/oradata/arch/1_14_665715452.dbf ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... Tue Oct 7 00:48:16 2008 ALTER DATABASE RECOVER CONTINUE DEFAULT Tue Oct 7 00:48:16 2008 Media Recovery Log /u01/oradata/arch/1_15_665715452.dbf Tue Oct 7 00:48:22 2008 Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0 Mem# 0 errs 0: /u01/oradata/burp/redo03.log Tue Oct 7 00:48:23 2008 Recovery of Online Redo Log: Thread 1 Group 1 Seq 17 Reading mem 0 Mem# 0 errs 0: /u01/oradata/burp/redo01.log Tue Oct 7 00:48:23 2008 Recovery of Online Redo Log: Thread 1 Group 2 Seq 18 Reading mem 0 Mem# 0 errs 0: /u01/oradata/burp/redo02.log Tue Oct 7 00:48:23 2008 Media Recovery Complete (burp) Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT =========================================================== SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header; SUBSTR(NAME,1,50) REC FUZ CHECKPOINT_CHANGE# -------------------------------------------------- --- --- ------------------ /u01/oradata/burp/system01.dbf NO YES 506486 /u01/oradata/burp/undotbs01.dbf NO YES 506486 /u01/oradata/burp/sysaux01.dbf NO YES 506486 /u01/oradata/burp/users01.dbf NO NO 506487 /u01/oradata/burp/example01.dbf NO YES 506486 SQL>
The checkpoint of all the datafile are the same, so we can ‘online’ the tablespace.
SQL> alter tablespace users online; Tablespace altered. SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header; SUBSTR(NAME,1,50) REC FUZ CHECKPOINT_CHANGE# -------------------------------------------------- --- --- ------------------ /u01/oradata/burp/system01.dbf NO YES 506486 /u01/oradata/burp/undotbs01.dbf NO YES 506486 /u01/oradata/burp/sysaux01.dbf NO YES 506486 /u01/oradata/burp/users01.dbf NO YES 507489 /u01/oradata/burp/example01.dbf NO YES 506486 SQL>
[...] When checked the datafile booms.dbf is missing. But there is not any backup for this datafile to perform open recovery [...]
Pingback by Recovery of non system datafile [ without backup ] « My confrontations with oracle — October 14, 2008 @ 11:29 pm |
[...] Recovery of Non System datafile [ while the database is open ] [...]
Pingback by Index « My confrontations with oracle — April 6, 2009 @ 2:49 pm |