We are going to have a session on recovery about corrupted/damaged/lost/canceled NONSYSTEM datafile (except system & sysaux). Before proceeding to be sure to have a complete backup of your test database and be sure your database is in ARCHIVELOG mode.
[oracle@localhost orcl]$ ps -ef|grep smon oracle 7200 2820 0 06:57 pts/1 00:00:00 grep smon
And it’s 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
Connecting through RMAN I can receive information about my datafiles
[oracle@localhost orcl]$ rman target / RMAN> report schema; using target database control file instead of recovery catalog 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 225 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf 5 82 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
I’m of course able to query the dictionary tables and see in which datafiles some tables are located.
SQL> set pagesize 999 SQL> set linesize 180 SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME from dba_tables where owner = 'HR'; OWNER TABLE_NAME TABLESPACE_NAME ------ -------------- -------------------- HR REGIONS USERS HR LOCATIONS USERS HR DEPARTMENTS USERS HR JOBS USERS HR EMPLOYEES USERS HR JOB_HISTORY USERS HR COUNTRIES USERS
What does it happens when I delete the datafile where USERS tablespace is based on.
[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf
I’m still able to query the dictionary tables for example but…
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 4 ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3
Looking at the log file, there’s the same clear error: the system is not able to obtain status information on file ‘/home/oracle/app/oracle/oradata/orcl/users01.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 4 ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.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 USERS tablespace. RMAN says its size is 0.
[oracle@localhost orcl]$ 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 0 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf 5 82 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 the USERS tablespace is not a system tablespace, we can recover it just putting it in offline mode.
RMAN> sql 'alter database datafile 4 offline'; sql statement: alter database datafile 4 offline RMAN> restore datafile 4; Starting restore at 19-07-2012 07:30:12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 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 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_07_17/o1_mf_nnndf_TAG20120717T090114_80c32cxk_.bkp tag=TAG20120717T090114 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:55 Finished restore at 19-07-2012 07:31:10 RMAN> recover datafile 4; Starting recover at 19-07-2012 07:31:20 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 19-07-2012 07:31:22 RMAN> sql 'alter database datafile 4 online'; sql statement: alter database datafile 4 online RMAN>
In the alert log you can see how the recovery process proceedes
[oracle@localhost orcl]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ... Thu Jul 19 07:30:01 2017 alter database datafile 4 offline Completed: alter database datafile 4 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 4 /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 4 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 4 Thu Jul 19 07:31:35 2017 alter database datafile 4 online Completed: alter database datafile 4 online ...
Just note how long the restore/recover process lasted: it began at Thu Jul 19 07:30:01 2017 to end at Thu Jul 19 07:31:35 2017 for a TOTAL TIME of 1:34 (one minute and 34 seconds). I’ll compare this result with another restore/recover approach in the next recovery scenario. 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 USERS tablespace
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 225 USERS *** /home/oracle/app/oracle/oradata/orcl/users01.dbf 5 82 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
