Database Recovery #2 - Inactive redo log member (offline)

The following guide is for when your instance losses an INACTIVE REDO LOG MEMEBER file while the instance is offline. For online, see DATABASE RECOVERY #3.


--Firstly shutdown your instance neatly to perform a cold backup.
sqlplus '/ as sysdba' 
shutdown immediate;


--Clear out any old backups previously taken from the backup locations
!rm /u01/backup/*
!rm /u01/backup/log/*
!rm /redologs/stage1/*


--Make a brand new cold backup of your files, along with your log files
!cp /u01/app/oracle/oradata/orcl/*  /u01/backup/
!cp /redologs/stage1/* /u01/backup/log


--Startup the database and switch some logs
startup;
alter system switch logfile;


--Delete an inactive group.
set lines 200
select '!rm /u01/app/oracle/oradata/orcl/redo0'||group#||'.log' "DELETE",
       'alter database add logfile group '||group#||' ''/u01/app/oracle/oradata/orcl/redo0'||group#||'.log'' size 100M;' "CREATE"
  from v$log where status = 'INACTIVE' and rownum < 2;


shutdown immediate;


--Run the DELETE command generated by sql statement above
--!rm /u01/app/oracle/oradata/orcl/redo03.log


--Startup the database, instance will shutdown if you try to perform regular start.
startup mount;


--Drop the group
alter database drop logfile group 3;


--Recreate the group
--Run the CREATE command generated by sql statement above
--alter database add logfile group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 100M;


--Open the database
alter database open;