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.

All Control File Missing Without Backup

Database 10.1.0.2.0
OS Windows
Archive log : Disable.



SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info


alert_db01.log file contents

Wed Mar 28 20:01:02 2007
ORA-00202: controlfile: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


My all control files deleted.


SQL> ED
Wrote file afiedt.buf

1 CREATE CONTROLFILE REUSE DATABASE "db01" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 7271
7 LOGFILE
8 GROUP 1 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\REDO01.LOG' SIZE 10M,
9 GROUP 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\REDO02.LOG' SIZE 10M,
10 GROUP 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\REDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\SYSTEM01.DBF' SIZE 600M,
14 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\UNDOTBS01.DBF' SIZE 65M,
15 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\SYSAUX01.DBF' SIZE 330M,
16 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\USERS01.DBF' SIZE 185600K,
17 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\EXAMPLE01.DBF' SIZE 150M
18* CHARACTER SET WE8MSWIN1252
SQL> /

Control file created.


SQL> alter database open;

Database altered.



Note : You must use SIZE clause with DATAFILE size specification.

Control File Inconsistent

Database Version : 10.1.0.2.0
OS Windows
NoArchivelog Mode



SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00214: controlfile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL02.CTL'
version 1302 inconsistent with file
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL' version 1292


SQL> shutdown abort
ORACLE instance shut down.
SQL> --FIRST DELETE "control01.ctl" and create new one from "control02.ctl"
SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL>

Control File Corrupted.

Database 10gr1.
NoArchiveLog Mode
OS Windows



SQL> create tablespace test
2 datafile 'c:\test01.dbf' size 1m;
create tablespace test
*
ERROR at line 1:
ORA-00227: corrupt block detected in controlfile: (block 1, # blocks 1)
ORA-00202: controlfile: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL'


SQL> shutdown abort;
ORACLE instance shut down.
SQL> --First Delete control01.ctl file and create new copy through control02.ctl

SQL> host del c:\oracle\product\10.1.0\oradata\db01\control01.ctl
ystem32\cmd.exe /c clsSQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> exit

C:\oracle\product\10.1.0\Db_1\BIN>sqlplus/nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 28 19:21:42 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string C:\ORACLE\PRODUCT\10.1.0\ORADA
TA\DB01\CONTROL01.CTL, C:\ORAC
LE\PRODUCT\10.1.0\ORADATA\DB01
\CONTROL02.CTL, C:\ORACLE\PROD
UCT\10.1.0\ORADATA\DB01\CONTRO
L03.CTL