Backup Options   «Prev  Next»

Lesson 6Recreating Redo Log Files
ObjectiveDescribe the Steps to recreate Inactive Redo Logs

Recreate Inactive Redo Logs

Recreating inactive redo logs in Oracle 12c involves identifying the inactive log files, dropping them, and recreating them. Here's a step-by-step guide:
  1. Identify Inactive Redo Log Files:
    • Connect to the Oracle database using SQL*Plus or any other Oracle client tool.
    • Execute the following query to identify inactive redo log files: 
      SELECT GROUP_ID, MEMBER, STATUS
FROM V$LOGFILE;
    • Look for log files with a status of 'INACTIVE'.
  2. Drop Inactive Redo Log Files:
    • For each inactive redo log file, execute the following query to drop it: 
      ALTER SYSTEM DROP REDO LOGFILE group <group_id>;
    • Replace `<group_id>`with the actual group ID of the inactive log file.
  3. Recreate Redo Log Files:
    • Once all inactive log files are dropped, you can recreate them using the ALTER DATABASE DROP command.
    • Execute the following query to recreate the redo log files: 
      ALTER DATABASE ADD LOGFILE group <group_id> ('<logfile_path1>','<logfile_path2>') SIZE <logfile_size>;
    • Replace `<group_id>` with the actual group ID of the log file to be recreated.
    • Replace `<logfile_path1>` and `` with the paths to the new redo log files.
    • Replace `<logfile_size>` with the desired size of the redo log files (in bytes).
  4. Switch Logfile Group (Optional):
    • If you want to switch to the newly created redo log group, execute the following query: 
      ALTER SYSTEM SWITCH LOGFILE group <group_id>;
    • Replace `<group_id>` with the actual group ID of the newly created redo log group.
  5. Checkpoint (Optional):
    • To ensure that all changes are committed to disk, execute the following query: 
      ALTER SYSTEM CHECKPOINT GLOBAL;

ALTER DATABASE CLEAR LOGFILE

The ALTER DATABASE CLEAR LOGFILE command drops and recreates the corrupt online redo file, but you can also perform this process manually. It's a helpful procedure to know if the ALTER DATABASE CLEAR LOGFILE command fails and reusing the currently configured log file name to recreate the log file becomes impossible. You may also preview all the steps.

Steps to recreate an Inactive Online Redo Log

To recreate an inactive online redo log manually, do the following:
  1. Find a location to recreate the online redo log.
  2. Drop the damaged or lost log file.
  3. Add the log group.
  4. Open the database and do not forget to multiplex all redo logs to reduce the chance of losing data.

Recovering from Loss of Multiplexed Redo Log File

When one of the multiplexed members of any group is corrupted or accidently dropped, the LGWR process ignores it and writes the information to the only available member. The following scenario gives a demonstration to easily understand the main concept: 

SQL>  
select
 b.group#, a.status, b.status, b.member 
 from
  v$log a, v$logfile b 
  where
   a.group#=b.group#
   order by
   1,2;
Add one member to each redo log group. Query all redo log members and their status: 

GROUP# STATUS         MEMBER
 ----  ------------  --------------------------------
1 			CURRENT        /u01/oracle/product/10.2.0/db_1/ora
                      data/testdb/redo01.log
2 			INACTIVE       /u01/oracle/product/10.2.0/db_1/ora
                      data/testdb/redo02.log
3 			ACTIVE         /u01/oracle/product/10.2.0/db_1/ora
                      data/testdb/redo03.log

The following series of images demonstrates how to perform an inactive redo log recovery.

Recreating an online redo log file

1) Since the database cannot be opened before the damaged online redo log is recreated, the DBA starts the database in the mount mode.
1. Since the database cannot be opened before the damaged online redo log is recreated, the DBA starts the database in the mount mode. The DBA issues the startup open command to mount the database(1) and gets the error message indicating what is wrong with the database. From the error message, the DBA knows that the online log #3 is corrupted (2)

2) The DBA queries the data dictionary view V$LOGFILE to find the location to create the damaged redo log file(3).
2. The DBA queries the data dictionary view V$LOGFILE to find the location to create the damaged redo log file(3). The result shows the location (4).

3) Since there are more than two online redo logs, the DBA drops the corrupted online redo log (5) and recreates it
3. Since there are more than two online redo logs, the DBA drops the corrupted online redo log (5) and recreates it (6) Now the database is ready to be opened (7).

  1. Since the database cannot be opened before the damaged online redo log is recreated, the DBA starts the database in the mount mode.
  2. The DBA queries the data dictionary view V$LOGFILE to find the location to create the damaged redo log file(3).
  3. Since there are more than two online redo logs, the DBA drops the corrupted online redo log (5)


The command lines and their results for the whole process are displayed below. 
SQL> STARTUP OPEN PFILE='C:\ORANT\DATABASE\INITORCL.ORA'
ORACLE instance started.
Total System Global Area                         11710464 bytes
Fixed Size                                          49152 bytes
Variable Size                                    11177984 bytes
Database Buffers                                   409600 bytes
Redo Buffers                                        73728 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'C:\ORANT\DATABASE\LOG2ORCL.ORA'
SQL> SELECT * FROM V$LOGFILE; 
GROUP#     STATUS  MEMBER
------     ------- ----------------------------------------------
     1             C:\ORANT\DATABASE\LOG4ORCL.ORA
     2      STALE  C:\ORANT\DATABASE\LOG3ORCL.ORA
     3      STALE  C:\ORANT\DATABASE\LOG2ORCL.ORA
     4             C:\ORANT\DATABASE\LOG1ORCL.ORA
4 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Statement processed.
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 'C:\ORANT\DATABASE\LOG2ORCL.ORA' SIZE 150k;
Statement processed.
SQL> ALTER DATABASE OPEN;
Statement processed.
SQL>

The next lesson demonstrates how to obtain recovery status information through the data dictionary views.