How to perform recovery on a database that is not in archive log mode, using the redo logs

In some situations, databases that have a small number of concurrent transactionscan be recovered using the online archive logs.

Example Case:

• A file system that contains a database is backed up every day at 10:00
• The database is not in archive log mode
• Redo logs are sitting on a separate disk and are not backed up
• Log switches occur once or twice a day and there are three groups
• At 13:30 the database was brought down and by mistake a datafile was damaged
• The datafiles are recovered from the backup done at 10:00

Following the recovery steps:

SVRMGR> connect internal

Connected.

SVRMGR> startup mount

ORACLE instance started.

Total System Global Area                         48087200 bytes
Fixed Size                                          73888 bytes

Variable Size                                    35192832 bytes

Database Buffers                                 12288000 bytes

Redo Buffers                                       532480 bytes

Database mounted.

SVRMGR> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /erp/app/applprod/applproddb/8.1.7/dbs/arch

Oldest online log sequence     5000

Current log sequence           5002

SVRMGR> select * from v$log;  – here we identify the current redo log

GROUP#     THREAD#    SEQUENCE#  BYTES      MEMBERS    ARC STATUS     FIRST_CHAN FIRST_TIM

———- ———- ———- ———- ———- — ———- ———- ———

         1          1       5002   20971520          1 NO  CURRENT    3.6631E+12 06-JAN-04

         2          1       5000   20971520          1 NO  INACTIVE   3.6631E+12 06-JAN-04

         3          1       5001   20971520          1 NO  INACTIVE   3.6631E+12 06-JAN-04

3 rows selected.

SVRMGR> select * from v$logfile ;  – here we identify the current redo log name

     2> /

GROUP#   STATUS  MEMBER  

——– ——- ——————————-

      1           /erp/or01/sncash/log_01_.log 

      2           /erp/or02/sncash/log_02_.log 

      3           /erp/or01/sncash/log_03_.log 

3 rows selected.

SVRMGR> recover database until cancel;

ORA-00279: change 3663136212477 generated at 01/06/2004 21:34:12 needed for thread 1

ORA-00289: suggestion : /erp/app/applprod/applproddb/8.1.7/dbs/archT0001S0000005002.arc

ORA-00280: change 3663136212477 for thread 1 is in sequence #5002

Specify log: {=suggested | filename | AUTO | CANCEL}

/erp/or01/sncash/log_01_.log – here we pass the current redo log name as the archive log name

Log applied.

Media recovery complete.

SVRMGR> alter database open NORESETLOGS; – here we perform noresetlogs to preserve the information on the redo?s

Statement processed.

SVRMGR> exit

Server Manager complete.