The following scenario describes what happens when a nonsystem datfile is lost. Before proceeding be sure to have a complete backup of your database and be sure your database in ARCHIVELOG mode.
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string test
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL>
Connect to RMAN to check information about datafiles
[oracle@alpha datafile]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Mar 13 21:02:30 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2300947395)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3 610 SYSAUX NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4 60 UNDOTBS1 YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5 10 EXAMPLE NO /u01/app/oracle/oradata/TEST/datafile/example.dbf
6 5 USERS NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp
RMAN>
Query the dictionary tables and see in which datafiles some tables are located.
SQL> col owner format a15
SQL> col table_name format a40
SQL> /
OWNER TABLE_NAME TABLESPACE_NAME
--------------- ---------------------------------------- ------------------------------
SCOTT DEPT USERS
SCOTT EMP USERS
SCOTT BONUS USERS
SCOTT SALGRADE USERS
SQL> select count(*) from scott.dept;
COUNT(*)
----------
4
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
What does it happens when I delete the datafile where USERS tablespace is based on.
[oracle@alpha datafile]$ ls -lrt
total 1523444
-rw-r----- 1 oracle oinstall 5251072 Mar 13 13:46 o1_mf_users_g8lf33c1_.dbf
-rw-r----- 1 oracle oinstall 10493952 Mar 13 13:46 example.dbf.back
-rw-r----- 1 oracle oinstall 10493952 Mar 13 15:31 example.dbf
-rw-r----- 1 oracle oinstall 62922752 Mar 13 19:31 o1_mf_temp_g8lf4w5d_.tmp
-rw-r----- 1 oracle oinstall 62922752 Mar 13 21:05 o1_mf_undotbs1_g8lf34lb_.dbf
-rw-r----- 1 oracle oinstall 817897472 Mar 13 21:05 o1_mf_system_g8lmhryv_.dbf
-rw-r----- 1 oracle oinstall 639639552 Mar 13 21:06 o1_mf_sysaux_g8lf08dm_.dbf
[oracle@alpha datafile]$ mv o1_mf_users_g8lf33c1_.dbf o1_mf_users_g8lf33c1_.dbf.corrupt
I'm still able to query the dictionary tables for example but...
SQL> select count(*) from dba_tables;
COUNT(*)
----------
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string test
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL>
Connect to RMAN to check information about datafiles
[oracle@alpha datafile]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Wed Mar 13 21:02:30 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2300947395)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3 610 SYSAUX NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4 60 UNDOTBS1 YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5 10 EXAMPLE NO /u01/app/oracle/oradata/TEST/datafile/example.dbf
6 5 USERS NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp
RMAN>
Query the dictionary tables and see in which datafiles some tables are located.
SQL> col owner format a15
SQL> col table_name format a40
SQL> /
OWNER TABLE_NAME TABLESPACE_NAME
--------------- ---------------------------------------- ------------------------------
SCOTT DEPT USERS
SCOTT EMP USERS
SCOTT BONUS USERS
SCOTT SALGRADE USERS
SQL> select count(*) from scott.dept;
COUNT(*)
----------
4
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
What does it happens when I delete the datafile where USERS tablespace is based on.
[oracle@alpha datafile]$ ls -lrt
total 1523444
-rw-r----- 1 oracle oinstall 5251072 Mar 13 13:46 o1_mf_users_g8lf33c1_.dbf
-rw-r----- 1 oracle oinstall 10493952 Mar 13 13:46 example.dbf.back
-rw-r----- 1 oracle oinstall 10493952 Mar 13 15:31 example.dbf
-rw-r----- 1 oracle oinstall 62922752 Mar 13 19:31 o1_mf_temp_g8lf4w5d_.tmp
-rw-r----- 1 oracle oinstall 62922752 Mar 13 21:05 o1_mf_undotbs1_g8lf34lb_.dbf
-rw-r----- 1 oracle oinstall 817897472 Mar 13 21:05 o1_mf_system_g8lmhryv_.dbf
-rw-r----- 1 oracle oinstall 639639552 Mar 13 21:06 o1_mf_sysaux_g8lf08dm_.dbf
[oracle@alpha datafile]$ mv o1_mf_users_g8lf33c1_.dbf o1_mf_users_g8lf33c1_.dbf.corrupt
I'm still able to query the dictionary tables for example but...
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2343
I obtain an error when I try to select some rows from the SCOTT.EMP table
select count(*) from scott.emp;
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
ORA-01110: data file 6: '/u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf'
ORA-27041: unable to open file
Linux-x86_64 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.
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3 610 SYSAUX NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4 60 UNDOTBS1 YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5 10 EXAMPLE NO /u01/app/oracle/oradata/TEST/datafile/example.dbf
6 0 USERS NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3 610 SYSAUX NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4 60 UNDOTBS1 YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5 10 EXAMPLE NO /u01/app/oracle/oradata/TEST/datafile/example.dbf
6 0 USERS NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp
Because the USERS tablespace is not a system tablespace, we can recover it just putting it in offline mode.
RMAN> sql 'alter database datafile 6 offline';
sql statement: alter database datafile 6 offline
RMAN> restore datafile 6;
Starting restore at 13-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 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 00006 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp tag=TAG20190313T134524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-MAR-19
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8lf33c1_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp tag=TAG20190313T134524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-MAR-19
RMAN> recover datafile 6;
Starting recover at 13-MAR-19
using channel ORA_DISK_1
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-MAR-19
RMAN> sql 'alter database datafile 6 online';
sql statement: alter database datafile 6 online
You can see in alert log how the restore and recovery process proceeds
Wed Mar 13 21:19:51 2019
alter database datafile 6 offline
Completed: alter database datafile 6 offline
Wed Mar 13 21:20:06 2019
Full restore complete of datafile 6 /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf. Elapsed time: 0:00:00
checkpoint is 2214185
last deallocation scn is 3
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Wed Mar 13 21:20:22 2019
Media Recovery Start
Wed Mar 13 21:20:22 2019
Serial Media Recovery started
Wed Mar 13 21:20:22 2019
Media Recovery failed with error 1124
ORA-283 signalled during: alter database recover if needed
datafile 4
...
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 6
Wed Mar 13 21:20:33 2019
Media Recovery Start
Wed Mar 13 21:20:33 2019
Serial Media Recovery started
Wed Mar 13 21:20:33 2019
Recovery of Online Redo Log: Thread 1 Group 3 Seq 12 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_3_g8lf4pjj_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_g8lf4plq_.log
Wed Mar 13 21:20:33 2019
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_1_g8lf4oc2_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_1_g8lf4ofn_.log
Wed Mar 13 21:20:33 2019
Media Recovery Complete (test)
Completed: alter database recover if needed
datafile 6
alter database datafile 6 online
Completed: alter database datafile 6 online
Now I am able to query SCOTT.EMP table
alter database datafile 6 offline
Completed: alter database datafile 6 offline
Wed Mar 13 21:20:06 2019
Full restore complete of datafile 6 /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf. Elapsed time: 0:00:00
checkpoint is 2214185
last deallocation scn is 3
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Wed Mar 13 21:20:22 2019
Media Recovery Start
Wed Mar 13 21:20:22 2019
Serial Media Recovery started
Wed Mar 13 21:20:22 2019
Media Recovery failed with error 1124
ORA-283 signalled during: alter database recover if needed
datafile 4
...
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 6
Wed Mar 13 21:20:33 2019
Media Recovery Start
Wed Mar 13 21:20:33 2019
Serial Media Recovery started
Wed Mar 13 21:20:33 2019
Recovery of Online Redo Log: Thread 1 Group 3 Seq 12 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_3_g8lf4pjj_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_g8lf4plq_.log
Wed Mar 13 21:20:33 2019
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/TEST/onlinelog/o1_mf_1_g8lf4oc2_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_1_g8lf4ofn_.log
Wed Mar 13 21:20:33 2019
Media Recovery Complete (test)
Completed: alter database recover if needed
datafile 6
alter database datafile 6 online
Completed: alter database datafile 6 online
Now I am able to query SCOTT.EMP table
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
----------
14
We can see recovered file
[oracle@alpha datafile]$ ls -lrt *user*.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 13 21:21 o1_mf_users_g8mglp3n_.dbf
Check the size of USERS tablesspace in RMAN
-rw-r----- 1 oracle oinstall 5251072 Mar 13 21:21 o1_mf_users_g8mglp3n_.dbf
Check the size of USERS tablesspace in RMAN
RMAN> report schema;
Report of database schema for database with db_unique_name TEST
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3 610 SYSAUX NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4 60 UNDOTBS1 YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5 10 EXAMPLE NO /u01/app/oracle/oradata/TEST/datafile/example.dbf
6 5 USERS NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_g8lmhryv_.dbf
3 610 SYSAUX NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_g8lf08dm_.dbf
4 60 UNDOTBS1 YES /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_g8lf34lb_.dbf
5 10 EXAMPLE NO /u01/app/oracle/oradata/TEST/datafile/example.dbf
6 5 USERS NO /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_g8mglp3n_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/TEST/datafile/o1_mf_temp_g8lf4w5d_.tmp
No comments:
Post a Comment