Recovering oracle DB after loss of datafiles
scenarios.
Scenario 1: Loss of system datafile
===========================
Step 1: Remove System File for scenario purpose:
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ---------------
db_name string virtuald
SQL> !uname -a
Linux oratst0001 3.10.0-957.1.3.el7.x86_64 #1 SMP
Thu Nov 15 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
$rm /u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf
SQL>
================================================================
Step 2: Verify to Create table for testing purpose:
SQL> create table xyz ( a varchar2(20));
create table xyz ( a varchar2(20))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1:
'/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Note: Since we removed system01.dbf,
now we are not able to create table |
=================================================================
Step 3: Shutdown Database:
SQL> shutdown immediate
ORA-01116: error in opening database file 1
ORA-01110: data file 1:
'/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
=====================================================================
Step 4: Restore Datafile 1:
RMAN> run
2> {
3> ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576,
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda.cfg)';
4> restore datafile 1;
5> release channel C1;
6> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=30 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 10-DEC-18
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00001 to
/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf
channel C1: reading from backup piece VIRTUALD_1atkd7so_1_1
released channel: C1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/10/2018 06:08:14
ORA-19870: error while restoring backup piece VIRTUALD_1atkd7so_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
RMAN>
| Note: receieved error in the above command since DB was not shutdown. |
============================================================================
Step 5: Shutdown Abort:
SQL> shut abort
ORACLE instance shut down.
SQL> exit
================================================================
Step 6: Startup DB in mount:
SQL> startup mount pfile='initvirtual.ora';
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 331350920 bytes
Database Buffers 729808896 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL>
=========================================================
Step 7: Restore Datafile 1:
$ rman target /
connected to target database: VIRTUALD (DBID=1142008032, not open)
RMAN> run
2> {
3> ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576,
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda.cfg)';
4> restore datafile 1;
5> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=18 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 10-DEC-18
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00001 to
/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf
channel C1: reading from backup piece VIRTUALD_1atkd7so_1_1
channel C1: piece handle=VIRTUALD_1atkd7so_1_1 tag=VIRTUALD_HOT_30DAYS
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:25
Finished restore at 10-DEC-18
released channel: C1
RMAN>
================================================================
Step 8: Recover Datafile 1:
RMAN> recover datafile 1;
Starting recover at 10-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
starting media recovery
archived log for thread 1 with sequence 8
is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_10/o1_mf_1_8_g0wodwmm_.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/10/2018 06:16:42
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06102: no channel to restore a backup or copy of archived
log for thread 1 with sequence 7 and starting SCN of 408226
RMAN-06102: no channel to restore a backup or copy of archived
log for thread 1 with sequence 6 and starting SCN of 408210
RMAN-06102: no channel to restore a backup or copy of archived
log for thread 1 with sequence 5 and starting SCN of 408076
RMAN-06102: no channel to restore a backup or copy of archived
log for thread 1 with sequence 4 and starting SCN of 407481
RMAN-06102: no channel to restore a backup or copy of archived
log for thread 1 with sequence 3 and starting SCN of 407468
RMAN>
Note: Above Command failed due to Missing Archive Logs.
=================================================================
Step 9: Restoring archive logfiles:
RMAN> run
2> {
3> ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576,
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda.cfg)';
4> restore archivelog from sequence 1 until sequence 7;
5> }
released channel: ORA_DISK_1
allocated channel: C1
channel C1: SID=18 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 10-DEC-18
channel C1: starting archived log restore to default destination
channel C1: restoring archived log
archived log thread=1 sequence=1
channel C1: restoring archived log
archived log thread=1 sequence=2
channel C1: restoring archived log
archived log thread=1 sequence=3
channel C1: restoring archived log
archived log thread=1 sequence=4
channel C1: restoring archived log
archived log thread=1 sequence=5
channel C1: restoring archived log
archived log thread=1 sequence=6
channel C1: restoring archived log
archived log thread=1 sequence=7
channel C1: reading from backup piece VIRTUALD_1etkd9di_1_1
channel C1: piece handle=VIRTUALD_1etkd9di_1_1 tag=TAG20181210T060119
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
Finished restore at 10-DEC-18
released channel: C1
RMAN>
==================================================================
Step 10: Recover datafile 1:
RMAN> recover datafile 1;
Starting recover at 10-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
starting media recovery
archived log for thread 1 with sequence 3 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_10/o1_mf_1_3_g0wpfm8w_.arc
archived log for thread 1 with sequence 4 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_10/o1_mf_1_4_g0wpfm5s_.arc
archived log for thread 1 with sequence 5 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_10/o1_mf_1_5_g0wpfm4s_.arc
archived log for thread 1 with sequence 6 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_10/o1_mf_1_6_g0wpfm60_.arc
archived log for thread 1 with sequence 7 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_10/o1_mf_1_7_g0wpfm5x_.arc
archived log for thread 1 with sequence 8 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_10/o1_mf_1_8_g0wodwmm_.arc
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_3_g0wpfm8w_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_4_g0wpfm5s_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_5_g0wpfm4s_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_6_g0wpfm60_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-DEC-18
RMAN>
================================================================
Step 11: Open Database:
RMAN> sql 'alter database open';
sql statement: alter database open
RMAN>
================================================================
step 12: Veriy database:
$ sqlplus "/as sysdba"
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,log_mode,open_mode from v$database;
NAME LOG_MODE OPEN_MODE
--------- ------------ --------------------
VIRTUALD ARCHIVELOG READ WRITE
SQL>
===================================================================
Step 13: Verify Archive Log list:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL>
=============================================================
Scenario 2: Loss of datafile from Users tablespace
===================================================
Step 1: Remove Users01.dbf file for scenario purpose:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------
/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/sysaux01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/undotbs01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
SQL> !rm /u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
===========================================================================
Step 2: Connect as test user:
SQL> conn pqrs/pqrs
Connected.
SQL> create table t1( a number);
Table created.
SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
===================================================================
Step 3 : Verify user01.dbf file:
SQL>
1* select FILE_NAME,TABLESPACE_NAME,STATUS
from dba_data_files where FILE_ID=4
SQL> /
FILE_NAME TABLESPACE_NAME STATUS
----------------------------------- ---------------- ---------
~/virtuald_COLD_restore/users01.dbf USERS AVAILABLE
==============================================================
Step 4: Flush Memory:
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> alter system flush GLOBAL CONTEXT;
System altered.
SQL> select FILE_NAME,TABLESPACE_NAME,STATUS
from dba_data_files where FILE_ID=4;
select FILE_NAME,TABLESPACE_NAME,STATUS
from dba_data_files where FILE_ID=4
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
=========================================================
Step 5: Verify Datafile Status:
SQL> select FILE# ,STATUS from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
========================================================
Step 6: Restore Datafile 4:
$ rman target /
connected to target database: VIRTUALD (DBID=1142008032)
RMAN> run
2> {
3> ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576,
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda.cfg)';
4> restore datafile 4;
5> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=35 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 10-DEC-18
channel C1: starting datafile backup set restore
channel C1: specifying datafile(s) to restore from backup set
channel C1: restoring datafile 00004 to
/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
channel C1: reading from backup piece VIRTUALD_1atkd7so_1_1
channel C1: piece handle=VIRTUALD_1atkd7so_1_1 tag=VIRTUALD_HOT_30DAYS
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
Finished restore at 10-DEC-18
released channel: C1
RMAN>
===============================================================
Step 7: Recover Datafile 4:
RMAN> recover datafile 4;
Starting recover at 10-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
starting media recovery
archived log for thread 1 with sequence 3 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_10/o1_mf_1_3_g0wpfm8w_.arc
archived log for thread 1 with sequence 4 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_10/o1_mf_1_4_g0wpfm5s_.arc
archived log for thread 1 with sequence 5 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_10/o1_mf_1_5_g0wpfm4s_.arc
archived log for thread 1 with sequence 6 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_10/o1_mf_1_6_g0wpfm60_.arc
archived log for thread 1 with sequence 7 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_10/o1_mf_1_7_g0wpfm5x_.arc
archived log for thread 1 with sequence 8 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_10/o1_mf_1_8_g0wodwmm_.arc
archived log for thread 1 with sequence 9 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_10/o1_mf_1_9_g0wpkgb8_.arc
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_3_g0wpfm8w_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_4_g0wpfm5s_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_5_g0wpfm4s_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_6_g0wpfm60_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_10/
o1_mf_1_7_g0wpfm5x_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-DEC-18
RMAN>
=================================================================
Step 8: Make Datafile 4 online:
RMAN> sql 'alter database datafile 4 online';
sql statement: alter database datafile 4 online
RMAN> exit
==============================================================
Step 9: Verify Datafiles:
$ sqlplus "/as sysdba"
Connected to:
Oracle Database 11g Enterprise Edition
Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and
Real Application Testing options
SQL> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------
/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/sysaux01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/undotbs01.dbf
/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
SQL>
====================================================================
Step 10: Verify database Mode:
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
VIRTUALD READ WRITE ARCHIVELOG
SQL>
SQL> select FILE# ,STATUS from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
SQL>
See Also:
Recovering After the Loss of Archived Redo Log Files
Restore Oracle database from Cold backup
Restore and recovering the Oracle database
Flashback RAC database to restore point
Recovery of Read-Only Files with a Re-Created Control File
Recover From a DROP/TRUNCATE/DELETE TABLE with RMAN
http://oracle.com
