All redolog group lost

Oracle Database: 10gr1
OS: Windows XP

Due to media failure if we lost all redolog group then recovery depand on database closed condition


When database cloase CONSISTENTLY OR CLEANLY

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host del c:\oracle\product\10.1.0\oradata\noorcl\*.log

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90961340 bytes
Database Buffers 192937984 bytes
Redo Buffers 524288 bytes
Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NOORCL\REDO01.LOG'


SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

When shutdown close with INCONSISTENT or DIRTY stage
SQL> shutdown abort;
ORACLE instance shut down.
all redolog files lost due to media failure.
SQL> host del c:\oracle\product\10.1.0\oradata\noorcl\*.log

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90961340 bytes
Database Buffers 192937984 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NOORCL\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> --now becuase database close was inconsistent so we have to restore last va
lid backup and open database with resetlogs options.
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
Restore last valid backup
SQL> host copy c:\backup\noorcl\*.* c:\oracle\product\10.1.0\oradata\noorcl\
c:\backup\noorcl\CONTROL01.CTL
c:\backup\noorcl\CONTROL02.CTL
c:\backup\noorcl\CONTROL03.CTL
c:\backup\noorcl\SYSAUX01.DBF
c:\backup\noorcl\SYSTEM01.DBF
c:\backup\noorcl\TEMP01.DBF
c:\backup\noorcl\UNDOTBS01.DBF
c:\backup\noorcl\USERS01.DBF
8 file(s) copied.

SQL> startup mount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90961340 bytes
Database Buffers 192937984 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> recover database until cancel
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.