We all know that redo log has three kinds of a group like active, inactive, current. They all have different use and as well as different meaning.Let’s simulate a loss of all redo log members of an inactive group.

If you have completely lost one redo log group your database won’t be able to open.
Next thing is to know the STATUS of the lost redo log group (we know it because we are simulating the loss, but…) and, just because this information is on V$LOG view, try to get also the ARCHIVED column.
Now if the redo log group lost was INACTIVE and archived (YES value) we can simply recreate all members with the “alter database clear logfile” command;
if it was INACTIVE and not archived (NO value) we’ll execute the “alter database clear unarchived logfile” command.

Let’s look at the example. Here is my redo log group state

SQL> select member, a.group#, a.status, b.status, a.archived from v$log a, v$logfile b where a.group# = b.group# order by a.group#, member;

MEMBER GROUP# STATUS STATUS ARC
------------------------------------------------------------ ---------- ---------------- ------- ---
/home/oracle/app/oracle/oradata/orcl/redo01.log 1 INACTIVE YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log 1 INACTIVE YES
/home/oracle/app/oracle/oradata/orcl/redo02.log 2 CURRENT NO
/home/oracle/app/oracle/oradata/orcl/redo02b.log 2 CURRENT NO
/home/oracle/app/oracle/oradata/orcl/redo03.log 3 INACTIVE YES
/home/oracle/app/oracle/oradata/orcl/redo03b.log 3 INACTIVE YES

Delete all members of redo log group 1 and kill your instance.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/redo01*
[oracle@localhost ~]$ ps -ef |grep smon
oracle 8176 1 0 06:44 ? 00:00:03 ora_smon_orcl
oracle 9057 3164 0 08:10 pts/5 00:00:00 grep smon
[oracle@localhost ~]$ kill -9 8176

Try to open your database

SQL> startup
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 385878712 bytes
Database Buffers 62914560 bytes
Redo Buffers 6008832 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9184
Session ID: 17 Serial number: 7

look at your alert log

...
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01.log'
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo01b.log'
USER (ospid: 9184): terminating the instance due to error 313
...

Start your database in MOUNT mode

SQL> startup mount
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 385878712 bytes
Database Buffers 62914560 bytes
Redo Buffers 6008832 bytes
Database mounted.

Query the V$LOG view

SQL> select member, a.group#, a.status, b.status, a.archived from v$log a, v$logfile b where a.group# = b.group# order by a.group#, member;

MEMBER GROUP# STATUS STATUS ARC
------------------------------------------------------------ ---------- ---------------- ------- ---
/home/oracle/app/oracle/oradata/orcl/redo01.log 1 INACTIVE YES
/home/oracle/app/oracle/oradata/orcl/redo01b.log 1 INACTIVE YES
/home/oracle/app/oracle/oradata/orcl/redo02.log 2 CURRENT NO
/home/oracle/app/oracle/oradata/orcl/redo02b.log 2 CURRENT NO
/home/oracle/app/oracle/oradata/orcl/redo03.log 3 INACTIVE YES
/home/oracle/app/oracle/oradata/orcl/redo03b.log 3 INACTIVE YES

The redo log group 1 is INACTIVE and ARCHIVED so simply issue the following command:

SQL> alter database clear logfile group 1;

Database altered.

Open your database

SQL> alter database open;

Database altered.

If you are in the situation to have lost an INACTIVE and NOT already ARCHIVED redo log group then your command should be the following:

SQL> alter database clear unarchived logfile group 1;

Database altered.

Next step is to perform a complete backup

RMAN> backup database;

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.