The most crucial and vital structure for recovery operations is the online redo log, which consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle database has an associated online redo log to protect the database in case of an instance failure.

When your database loses all members of the CURRENT redo log group, it means you have lost transactions contained in that group: to be able opening your database you have to perform an incomplete recovery until latest valid SCN. Let’s simulate this scenario with an example. I will get information about redo log groups, create a table, insert some rows and commit them, force a log switch, create another table, insert other rows and commit them on the second table. I’m expecting to drop the CURRENT redo log group will force me to perform an incomplete recovery, being able to get back rows of the first table, but no rows of the second… Here is the script:

The output of the script:

My CURRENT redo log group is number 1 and rows are inserted.

Let’s remove all members of the CURRENT redo log group

Killing the instance

Let’s startup the instance

The instance alerts you it cannot open your database because of the missing files. It’s time to perform an incomplete recovery. Query the v$log view to obtain the latest valid SCN of the missing group. In my case it was 14133031

Issue the following commands from RMAN

As you can see I’ve lost table and rows “recorded” in the previous CURRENT redo log group I have “inadvertently” deleted; the first table and its rows is instead recovered…

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