User Managed Recovery - redo log fileLog 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. « Previous Next Topic » (User Managed Recovery - controlfile) |