In the last post, we have learned about multiple kinds of a recovery scenario. Now in this article, we are going to learn about the steps which are used How to recover from a loss of all redo log members of an ACTIVE group.  As we all know what is active group ACTIVE status means that there are still some buffers in buffer cache which haven’t been flushed to disk since a modification done when that log was CURRENT. A more formal explanation would be that the checkpoint position (the redo byte address (RBA) of the redo thread up to which all the corresponding dirty buffers have been flushed) has not passed beyond that logs and yet, thus some blocks of that redo log would be still needed for recovery in case of an instance crash.


According to the Oracle documentation, there are more steps to complete when you lose all redo log members of an ACTIVE group and exists the chance to have lost some transactions.
If you are able to force the database to perform a checkpoint then you can clear the missing redo log group, or, you need to execute an incomplete recovery.
I was able to recreate both the scenarios using some scripts and verify when you lose transactions.
Dealing with the loss of ACTIVE and, in the next post, CURRENT redo log group could mean discovering to have lost some transactions.

To simulate the loss of ACTIVE redo log group I use a script and, with a query, I try to identify the redo log members to be deleted manually.

The first scenario, while the instance was still up and running, I was able to execute a checkpoint and perform a complete recovery;
to simulate the second scenario, that is an incomplete recovery, I needed to kill the instance so no checkpoint could be completed.

Let’s see the examples.
Take a look at the script first. It executes for three times a query on v$log and v$logfile to show the status of the redo log group, insert 100 rows in three different tables, commit and force a logfile switch.
After the last insert it executes again the previous query.

Then I executed the script and this was the output:

At this moment redo log group number 2 was ACTIVE and still NOT ARCHIVED… but few second later it was simply ACTIVE and ARCHIVED:

Have a look at the contents of the three table before simulating the lost:

It was time to delete all redo log members of group 2:

The instance was still up and running and I was able to complete a checkpoint as Oracle documentation suggests. Because the operation was successful, the active redo log became inactive.

And now it’s time to clear the redo logfile group 2

The query shows those members are just created and never used.

Will the rows still be there if we perform a restore of our database ? Let’s look with an example

Connecting with RMAN I restore and recover the database:

The database is open and the rows are still there.

So our rows are all there. Now it’s time to simulate the second scenario. First of all I need to find the process id of the smon process.

Then in three different Unix terminals I prepared the following commands (each on one terminal of course). In this way with just one RETURN button I’m able to remove the ACTIVE redo log group and kill the instance, without any checkpoint and “perhaps” loosing transactions.

The script I’m going to execute inserting rows on others three different tables:

The output of the script just executed:

I’m going to remove all members of redo log group number 1 and kill the instance.

Let’s start the instance in MOUNT mode and query the v$ views as we don’t know what happened. I try to issue a checkpoint but it cannot complete because the database is not open.

The Oracle documentation says we have to perform an incomplete recovery up to the latest valid SCN that is indicated into FIRST_CHANGE# column of v$log view of the missing redo log group.

In my case I can restore and recover until SCN 14126367. Let’s look the following steps:

The database is open… but I’m missing a table and its rows.

There are instead the two other tables. As you can see above following the output of the running script the missing table was created just on the redo log group number 1

To summarize:
1. Try to perform “alter system checkpoint“. If successful clear the lost redo log group: “alter database clear logfile group ” (you have completely recovered your database)
2. If unsuccessful query FIRST_CHANGE# column of v$log to know the latest SCN for the missing redo log group. Then using RMAN: startup mount; restore database until scn ; recover database until scn ; alter database open (you have lost some transactions)

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


For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply