Recovering from Lost Online Redo Logs in NOARCHIVELOG mode

The title above suggests a situation wherein data-loss is inevitable. However if are running your database in NOARCHIVELOG mode, it is more or less understood that transactional data is not what runs your business. You are however more bent on keeping your database alive for the maximum time possible with all the data (static) already committed to datafiles intact. Well, here is what you can do,

Scenarios:

a. You have lost one/all your Online Redo logs.

b. One/all of your redo logs are corrupt.

Example:

SQL> startup

ORACLE instance started.

Total System Global Area 293601280 bytes

Fixed Size 1248600 bytes

Variable Size 79692456 bytes

Database Buffers 205520896 bytes

Redo Buffers 7139328 bytes

Database mounted.

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1:

‘E:\ORACLE10.2\PRODUCT\10.2.0\ORADATA\TESTDB2\REDO03.LOG’

Recovery Procedure:

1. Startup the database in MOUNT

SQL> startup mount

ORACLE instance started.

Total System Global Area 293601280 bytes

Fixed Size 1248600 bytes

Variable Size 79692456 bytes

Database Buffers 205520896 bytes

Redo Buffers 7139328 bytes

Database mounted.

2. Fake a database recovery:

This is required to open the database with RESETLOGS option.

SQL> recover database until cancel;

Media recovery complete.

3. Open database with RESETLOGS option.

This will ensure all missing/corrupted redo logs are fixed.

SQL> alter database open resetlogs;

Database altered.

You will surely lose uncommitted transaction data though. Hence it is always a better bet to run your database in ARCHIVELOG mode.