How to recover the Oracle Online Redo Logs

This is a task that all Oracle DBA should know and understand. We see how to recover our database after a redo log failure. We can simulate this scenario by deleting all the online redo log files at the OS level./assets/undo-1.jpg

  • Connect to SQL Plus as sysdba and find out where the online redo log are located
    •  SQL> conn / as sysdba
Connected.
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/oracle/oradata/oracle/redo03.log
/opt/oracle/oradata/oracle/redo02.log
/opt/oracle/oradata/oracle/redo01.log

SQL>

  • Now shutdown the database.
    •  SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Move or delete the redologs.

    • Before doing this you must have your database backed up with RMAN as well the archive logs as well, otherwise the restore will no be possible.

     [oracle@ ~]$ cd /opt/oracle/oradata/oracle/
[oracle@ oracle]$ ll
total 1398344
-rw-r-----. 1 oracle oinstall   9748480 Oct 24 12:32 control01.ctl
-rw-r-----. 1 oracle oinstall   9748480 Oct 24 12:32 control02.ctl
-rw-r-----. 1 oracle oinstall  52429312 Oct 24 12:28 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Oct 24 12:32 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Oct 24 12:28 redo03.log
-rw-r-----. 1 oracle oinstall 513810432 Oct 24 12:32 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 Oct 24 12:32 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Oct 23 23:00 temp01.dbf
-rw-r-----. 1 oracle oinstall  31465472 Oct 24 12:32 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Oct 24 12:32 users01.dbf
[oracle@ oracle]$ mv redo01.log redo01.log.old
[oracle@ oracle]$ mv redo02.log redo02.log.old
[oracle@ oracle]$ mv redo03.log redo03.log.old
    For safety sake you are just going to alter their name as seen up.
  • Now start your database and see the results
    •  [oracle@ oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 24 12:34:20 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             314574800 bytes
Database Buffers           96468992 bytes
Redo Buffers                4288512 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 31147
Session ID: 1 Serial number: 8
    oppsss .. problem, we can see that he is complaining that
  • Now we need to find at what current log sequence he is. To do this you need to put your database in mount mode first.
    •  SQL> startup mount ;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             314574800 bytes
Database Buffers           96468992 bytes
Redo Buffers                4288512 bytes
Database mounted.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   8
Current log sequence           8

    We see that he is at sequence 8.

  • Now let's go to RMAN and restore the database until the Current log sequence 8.
    • RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
set until sequence 8 thread 1;
ALLOCATE CHANNEL ch1 TYPE Disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
     [oracle@ home]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Oct 24 14:59:15 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     417546240 bytes

Fixed Size                     2213936 bytes
Variable Size                331352016 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4288512 bytes

RMAN> RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
set until sequence 8 thread 1;
ALLOCATE CHANNEL ch1 TYPE Disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     417546240 bytes

Fixed Size                     2213936 bytes
Variable Size                331352016 bytes
Database Buffers              79691776 bytes
Redo Buffers                   4288512 bytes

executing command: SET until clause

allocated channel: ch1
channel ch1: SID=18 device type=DISK

Starting restore at 24-OCT-13
flashing back control file to SCN 1055301

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /opt/oracle/oradata/oracle/system01.dbf
channel ch1: restoring datafile 00002 to /opt/oracle/oradata/oracle/sysaux01.dbf
channel ch1: restoring datafile 00003 to /opt/oracle/oradata/oracle/undotbs01.dbf
channel ch1: restoring datafile 00004 to /opt/oracle/oradata/oracle/users01.dbf
channel ch1: reading from backup piece /opt/oracle/diag/fra/ORACLE/backupset/2013_10_24/o1_mf_nnndf_TAG20131024T145456_96ln716f_.bkp
channel ch1: piece handle=/opt/oracle/diag/fra/ORACLE/backupset/2013_10_24/o1_mf_nnndf_TAG20131024T145456_96ln716f_.bkp tag=TAG20131024T145456
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:01:05
Finished restore at 24-OCT-13

Starting recover at 24-OCT-13

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /opt/oracle/diag/fra/ORACLE/archivelog/2013_10_24/o1_mf_1_5_96lmtqv8_.arc
archived log for thread 1 with sequence 6 is already on disk as file /opt/oracle/diag/fra/ORACLE/archivelog/2013_10_24/o1_mf_1_6_96ln6wt3_.arc
archived log for thread 1 with sequence 7 is already on disk as file /opt/oracle/diag/fra/ORACLE/archivelog/2013_10_24/o1_mf_1_7_96ln9rf4_.arc
archived log file name=/opt/oracle/diag/fra/ORACLE/archivelog/2013_10_24/o1_mf_1_5_96lmtqv8_.arc thread=1 sequence=5
archived log file name=/opt/oracle/diag/fra/ORACLE/archivelog/2013_10_24/o1_mf_1_6_96ln6wt3_.arc thread=1 sequence=6
archived log file name=/opt/oracle/diag/fra/ORACLE/archivelog/2013_10_24/o1_mf_1_7_96ln9rf4_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-OCT-13

database opened
released channel: ch1

RMAN>

    Database is open and ready for use.

    Copyright 2020. All Rights Reserved