How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile (database in ARCHIVELOG mode)
The following scenario describes what happens when a nonsystem datafile is lost. Before proceeding be sure to have a complete backup of your test database and be sure your database is in ARCHIVELOG mode.
[oracle@localhost orcl]$ sqlplus / as sysdba
SQL> startup
SQL> archive log list;
Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2
rman target / RMAN> report schema; Report of database schema for database with db_unique_name ORCL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM YES /u01/app/oracle/oradata/orcl/system01.dbf 3 620 SYSAUX NO /u01/app/oracle/oradata/orcl/sysaux01.dbf 4 75 UNDOTBS1 YES /u01/app/oracle/oradata/orcl/undotbs01.dbf 5 1243 EXAMPLE NO /u01/app/oracle/oradata/orcl/example01.dbf 6 5 USERS NO /u01/app/oracle/oradata/orcl/users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 60 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
SQL> set pagesize 999 SQL> set linesize 180 SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables 2 where owner = 'HR'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ----------------------- HR REGIONS EXAMPLE HR LOCATIONS EXAMPLE HR DEPARTMENTS EXAMPLE HR JOBS EXAMPLE HR EMPLOYEES EXAMPLE HR JOB_HISTORY EXAMPLE HR COUNTRIES EXAMPLE
What does it happens when I delete the datafile where EXAMPLE tablespace is based on.All the tables which is belong to EXAMPLE tablespace is also deleted.
I am moving my EXAMPLE datafile in different name.
[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf
SQL> select count(*) from dba_tables; COUNT(*) ---------- 3013
I obtain an error when I try to select some rows from the HR.EMPLOYEES table.
SQL> select count(*) from hr.EMPLOYEES;
select count(*) from hr.EMPLOYEES *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Now Check Alert Log file..
Looking at the same clear error: the systen is not able to obtain status information on file '/u01/app/oracle/oradata/orcl/example01.dbf'[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... Thu Jul 19 07:07:49 2012 Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_7476.trc: ORA-01116: error in opening database file 5 ORA-01110:data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 ...If you try to connect with RMAN the REPORT SCHEMA command is now unable to correctly size the EXAMPLE tablespace. RMAN says its size is 0.
rman target / RMAN> report schema; Report of database schema for database with db_unique_name ORCL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 831 SYSTEM *** /home/oracle/app/oracle/oradata/orcl/system01.dbf 2 1105 SYSAUX *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf 3 40 UNDOTBS1 *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf 4 1325 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf 5 0 EXAMPLE *** /home/oracle/app/oracle/oradata/orcl/example01.dbf 6 7 APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
Because theEXAMPLE tablespace is not a system tablespace, we can recover it just putting it in
offline mode.
RMAN> sql 'alter database datafile 5 offline';
sql statement: alter database datafile 5 offline
RMAN> restore datafile 5;
Starting restore at 27-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2017_12_27/o1_mf_nnndf_TAG20171227T122203_f46jrmy0_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2017_12_27/o1_mf_nnndf_TAG20171227T122203_f46jrmy0_.bkp tag=TAG20171227T122203
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 27-DEC-17
RMAN> recover datafile 5;
Starting recover at 27-DEC-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-DEC-17
RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
Now Again Check Alert log file
In the alert log you can see how the recovery process proceededtail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... Thu Jul 19 07:30:01 2012 alter database datafile 5 offline Completed: alter database datafile 5 offline Thu Jul 19 07:30:01 2012 Starting background process SMCO Thu Jul 19 07:30:02 2012 SMCO started with pid=46, OS id=8257 Thu Jul 19 07:31:03 2012 Full restore complete of datafile 5 /home/oracle/app/oracle/oradata/orcl/users01.dbf. Elapsed time: 0:00:47 checkpoint is 13569948 last deallocation scn is 13511135 Thu Jul 19 07:31:21 2012 alter database recover datafile list clear Completed: alter database recover datafile list clear alter database recover if needed datafile 5 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log Media Recovery Complete (orcl) Completed: alter database recover if needed datafile 5 Thu Jul 19 07:31:35 2012 alter database datafile 5 online Completed: alter database datafile 5 online ...Now I'm able to query again my hr.employees table
SQL> select count(*) from hr.employees; COUNT(*) ---------- 107
and even RMAN is able to size correctly the EXAMPLE tablespace
RMAN> report schema; Report of database schema for database with db_unique_name ORCL ===================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM YES /u01/app/oracle/oradata/orcl/system01.dbf 3 620 SYSAUX NO /u01/app/oracle/oradata/orcl/sysaux01.dbf 4 75 UNDOTBS1 YES /u01/app/oracle/oradata/orcl/undotbs01.dbf 5 1243 EXAMPLE NO /u01/app/oracle/oradata/orcl/example01.dbf 6 5 USERS NO /u01/app/oracle/oradata/orcl/users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 60 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
