Recovering After the Loss of Archived
Redo Log Files
Scenario:
Step 1: Verify Archives:
SQL> archive loglist
===========================================================
SQL> select
1* select NAME,ARCHIVED,DELETED,STATUS from v$archived_log
SQL> /
NAME ARC DEL S
------------------------------------------------------------ --- --- -
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_ YES NO A
area/VIRTUALDB/archivelog/2018_12_11/o1_mf_1_6_g0z1863s_.arc
9 rows selected.
Step 2: Connect to PQRS and insert some Data:
SQL> conn pqrs/pqrs
Connected.
SQL> create table xyz(a number);
Table created.
SQL> insert into xyz values (1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
=======================================================
Verify Archives:
NAME ARC DEL S
------------------------------------------------------------ --- --- -
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_ YES NO A
area/VIRTUALDB/archivelog/2018_12_11/o1_mf_1_6_g0z1863s_.arc
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_ YES NO A
area/VIRTUALDB/archivelog/2018_12_11/o1_mf_1_7_g0z27594_.arc
10 rows selected.
=============================================================
SQL> select count(1) from pqrs.xyz;
COUNT(1)
----------
6
========================================================
Step 3: Remove Archive Log file and Datafile:
Note: No backup of archive file and no recent backup of datafile
SQL> !rm /u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area
/VIRTUALDB/archivelog/2018_12_11/o1_mf_1_7_g0z27594_.arc
SQL> !rm /u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf
======================================================
Step 4: Insert some data into pqrs user:
SQL> insert into pqrs.xyz values(2);
insert into pqrs.xyz values(2)
*
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
Note: even if we delete the archived log , it will show in the DB
===========================================================
SQL> select NAME,ARCHIVED,DELETED,STATUS from v$archived_log;
NAME ARC DEL S
------------------------------------------------ --- --- -
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
YES YES D
/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/
2018_12_11/o1_mf_1_6_g0z1863s_.arc YES NO A
/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/
2018_12_11/o1_mf_1_7_g0z27594_.arc YES NO A
10 rows selected.
==========================================================
Step 5: Restore Datafile 4:
ramn target /
run
{
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)';
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)';
restore datafile 4;4>
5> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=31 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 11-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_01tkfl80_1_1
channel C1: piece handle=VIRTUALD_01tkfl80_1_1 tag=VIRTUALD_HOT_30DAYS
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:03
Finished restore at 11-DEC-18
released channel: C1
RMAN>
==============================================================
Step 6: Restore Archive log files:
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 5;
5> }
allocated channel: C1
channel C1: SID=31 device type=SBT_TAPE
channel C1: Database Application Agent Oracle v4.6.0.0
Starting restore at 11-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: reading from backup piece VIRTUALD_05tkfl90_1_1
channel C1: piece handle=VIRTUALD_05tkfl90_1_1
tag=TAG20181211T033557
channel C1: restored backup piece 1
channel C1: restore complete, elapsed time: 00:00:01
Finished restore at 11-DEC-18
released channel: C1
RMAN>
===================================================================
Note: Recovery failed
=============================
RMAN> recover datafile 4;
Starting recover at 11-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_11/o1_mf_1_2_g0z42wlg_.arc
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_11/o1_mf_1_3_g0z42wlw_.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_11/o1_mf_1_4_g0z42wm4_.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_11/o1_mf_1_5_g0z42wmq_.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_11/o1_mf_1_6_g0z1863s_.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/11/2018 04:26:51
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 7 and
starting SCN of 476599 found to restore
RMAN>
===============================================
Step 7: Recover Datafile 4 until sequence 6:
RMAN> recover datafile 4 until sequence 6;
Starting recover at 11-DEC-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file
/u01/app/oracle/oradata/virtuald_COLD_restore/fast_recovery_area/
VIRTUALDB/archivelog/2018_12_11/o1_mf_1_2_g0z42wlg_.arc
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_11/o1_mf_1_3_g0z42wlw_.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_11/o1_mf_1_4_g0z42wm4_.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_11/o1_mf_1_5_g0z42wmq_.arc
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_11/
o1_mf_1_2_g0z42wlg_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_11/
o1_mf_1_3_g0z42wlw_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_11/
o1_mf_1_4_g0z42wm4_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/oradata/virtuald_COLD_restore
/fast_recovery_area/VIRTUALDB/archivelog/2018_12_11/
o1_mf_1_5_g0z42wmq_.arc thread=1 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-DEC-18
RMAN>
=============================================================
Step 8: Verify Datafiles:
NAME STATUS ENABLED
----------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf SYSTEM READ WRITE
/u01/app/oracle/oradata/virtuald_COLD_restore/sysaux01.dbf ONLINE READ WRITE
/u01/app/oracle/oradata/virtuald_COLD_restore/undotbs01.dbf ONLINE READ WRITE
/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf OFFLINE READ WRITE
SQL>
SQL> alter database datafile 4 online;
Database altered.
SQL> select name,status ,enabled from v$datafile;
NAME STATUS ENABLED
------------------------------------------------------------ ------- ----------
/u01/app/oracle/oradata/virtuald_COLD_restore/system01.dbf SYSTEM READ WRITE
/u01/app/oracle/oradata/virtuald_COLD_restore/sysaux01.dbf ONLINE READ WRITE
/u01/app/oracle/oradata/virtuald_COLD_restore/undotbs01.dbf ONLINE READ WRITE
/u01/app/oracle/oradata/virtuald_COLD_restore/users01.dbf ONLINE READ WRITE
SQL>
See Also:
Recover oracle DB after loss of datafiles scenarios
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
