User Managed Recovery - redo log file

Log file recovery in Oracle refers to the process of restoring and recovering lost or damaged redo log files, also known as transaction log files. Redo log files are crucial for Oracle database recovery and consistency because they record all changes (transactions) made to the database. If a redo log file becomes corrupted or is accidentally deleted, it can potentially lead to data loss or database inconsistencies.


Step 1 : Check the list of INACTIVE LOGFILE and check the path 
  SQL> select GROUP#,MEMBERS,STATUS from v$log;

      GROUP#    MEMBERS STATUS
  ---------- ---------- ----------------
    1	    1 INACTIVE
    2	    1 INACTIVE
    3	    1 CURRENT

  SQL> select member from v$logfile;

  MEMBER
  -------------------------------------------
  /u01/app/oracle/oradata/dev/redo03.log
  /u01/app/oracle/oradata/dev/redo02.log
  /u01/app/oracle/oradata/dev/redo01.log

Step 2 : Remove any of log file(for demo purpose) 
  [oracle@tesdb dev]$ rm -rf redo02.log

Step 3 : Start the database 
  [oracle@tesdb dev]$ sqlplus / as sysdba

  SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 30 06:44:28 2023
  Copyright (c) 1982, 2016, Oracle.  All rights reserved.

  Connected to:
  Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Step 4 : Switch the log file multiple times 
  SQL> alter system switch logfile;

  System altered.

  SQL> /

  System altered.

  SQL> /

Step 5 : Start the database and error wil occur 
  SQL> shut immediate
  Database closed.
  Database dismounted.
  ORACLE instance shut down.
  SQL> startup
  ORACLE instance started.

  Total System Global Area  713031680 bytes
  Fixed Size		    8624792 bytes
  Variable Size		  566232424 bytes
  Database Buffers	  130023424 bytes
  Redo Buffers		    8151040 bytes
  Database mounted.
  ORA-03113: end-of-file on communication channel
  Process ID: 5789
  Session ID: 237 Serial number: 7788

Step 6 : Start the database in mount stage 
  SQL> startup mount
  ORACLE instance started.

  Total System Global Area  713031680 bytes
  Fixed Size		    8624792 bytes
  Variable Size		  566232424 bytes
  Database Buffers	  130023424 bytes
  Redo Buffers		    8151040 bytes
  Database mounted.

Step 7: truncate the logfile using unarchive option and open database 
 
  1* alter database clear unarchived logfile group 2
  SQL> /

  Database altered.

  SQL> alter database open;

  Database altered.

  -------------current

Step 8 : Choose the current_scn 
SQL> select current_Scn from v$database;

CURRENT_SCN
-----------
    1675392

SQL>  select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
	 1 CURRENT
	 2 UNUSED
	 3 INACTIVE

Step 9 : Restart the database 
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  713031680 bytes
Fixed Size		    8624792 bytes
Variable Size		  566232424 bytes
Database Buffers	  130023424 bytes
Redo Buffers		    8151040 bytes
Database mounted.

Step 10 : Recover database using control file 
SQL> recover database using backup controlfile until change 1675392;
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.


(User Managed Recovery - controlfile)