Recover from Loss of all control files - No backup control file

RMAN> list incarnation of database orclnew;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCLNEW 3405949784 PARENT 1 30-AUG-05
2 2 ORCLNEW 3405949784 PARENT 534907 04-AUG-08
3 3 ORCLNEW 3405949784 PARENT 7648711 19-FEB-09
6 6 ORCLNEW 3405949784 PARENT 7648714 23-FEB-09
7 7 ORCLNEW 3405949784 PARENT 7656456 24-FEB-09
4 4 ORCLNEW 3405949784 ORPHAN 7671524 23-FEB-09
5 5 ORCLNEW 3405949784 ORPHAN 7736513 23-FEB-09
8 8 ORCLNEW 3405949784 PARENT 9060462 25-MAR-09
9 9 ORCLNEW 3405949784 PARENT 9533465 08-APR-09
10 10 ORCLNEW 3405949784 PARENT 9535505 09-APR-09
11 11 ORCLNEW 3405949784 PARENT 9860069 17-APR-09
12 12 ORCLNEW 3405949784 PARENT 9918323 18-APR-09
13 13 ORCLNEW 3405949784 CURRENT 14032304 13-OCT-09

RMAN> exit


SQL> shutdown immediate;
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL'
SQL>

Tue Oct 13 13:16:28 2009
Hex dump of (file 0, block 1) in trace file c:\oracle\product\10.2.0\admin\orcln
ew\bdump\orclnew_arc0_5696.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block:
type: 0 format: 0 rdba: 0x00000000
last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x0
block checksum disabled
Tue Oct 13 13:16:29 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc0_5696.tr
c:
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
'

Tue Oct 13 13:16:29 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc0_5696.tr
c:
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
'

Tue Oct 13 13:16:29 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc0_5696.tr
c:
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
'

Tue Oct 13 13:16:29 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc0_5696.tr
c:
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
'

Master background archival failure: 227

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-00227: corrupt block detected in control file: (block , # blocks )

LGWR: terminating instance due to error 227
Tue Oct 13 13:16:52 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_q001_3856.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:16:52 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_ckpt_3320.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:16:52 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_pmon_4300.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:16:52 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_j000_2044.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:16:53 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_dbw0_3604.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:16:55 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_psp0_4248.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:16:55 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_mman_164.trc
:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:17:04 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_reco_5912.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:17:04 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_smon_3708.tr
c:
ORA-00227: corrupt block detected in control file: (block , # blocks )

Tue Oct 13 13:17:09 2009
Instance terminated by LGWR, pid = 5412

startup nomount


CREATE CONTROLFILE REUSE DATABASE "ORCLNEW" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO1A.LOG',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO1B.LOG'
) SIZE 100M,
GROUP 2 (
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO2A.LOG',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO2B.LOG'
) SIZE 100M,
GROUP 3 (
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'
) SIZE 100M
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\PORTAL.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\PTLDOC.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\PTLIDX.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\PTLLOG.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\IAS_META01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\DISCOPLTM1.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\DISCOPLTC1.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\DCM.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\OSS_SYS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\WCRSYS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\B2B_RT.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\B2B_DT.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\B2B_IDX.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\B2B_LOB.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\ORABPEL.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\BAM.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\OCA.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\UDDISYS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\ATTRS1_OID.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\BATTRS1_OID.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\GCATS1_OID.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\GDEFAULT1_OID.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SVRMG1_OID.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONSTANT_GROW_INDEXES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONSTANT_GROW_TABLES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\DEPENDENCY_INDEXES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\DEPENDENCY_TABLES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\DIAGRAM_INDEXES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\DIAGRAM_TABLES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\LOB_DATA.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\RAPID_GROW_INDEXES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\RAPID_GROW_TABLES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSTEM_META_INDEXES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSTEM_META_TABLES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\TEMPORARY_INDEXES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\TEMPORARY_TABLES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\VERSION_INDEXES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\VERSION_TABLES.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\TESTINCBKP01.DBF'
CHARACTER SET WE8MSWIN1252
;

SQL>
SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 2
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 3
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''C:\Oracle\product\10.2.0\admin\orclnew\bdump
\%F''');

PL/SQL procedure successfully completed.

All past records of archive logs have been wiped out because new control file was created.

SQL> select distinct(resetlogs_id), to_char(first_time,'DD-MON-YYYY HH:MI:SS'), to_char(next_time,'DD-MON-YYYY HH:MI:SS') from v$archived_log
2 /

no rows selected


RMAN> list incarnation of database orclnew;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCLNEW 3405949784 CURRENT 14032304 13-OCT-09

SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0567697796.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0567697796.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0661871515.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0661871515.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679241278.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679241278.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679578206.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679578206.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679580844.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679580844.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679593390.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679593390.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679663003.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0679663003.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0682424915.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0682424915.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0683643065.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0683643065.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0683717387.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0683717387.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0684412505.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0684412505.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0684492530.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0684492530.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


SQL> ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0700142755.001';
ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0700142755.001'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level

This is a known issue and is currently being worked in an internal bug.
Use the workaround :
ALTER DATABASE REGISTER PHYSICAL LOGFILE '//1_234_636797930.dbf';

SQL> RECOVER DATABASE;
Media recovery complete.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM ARCHIVE LOG ALL
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving


SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\TEMP01.DBF'
2 SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER TABLESPACE REPOS_TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REPOS_TEMP.DBF'
2 SIZE 15745024 REUSE AUTOEXTEND ON NEXT 16384 MAXSIZE 32767M;

Tablespace altered.

SQL> select sequence#,FIRST_CHANGE#,TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH:MI:SS'),NEXT_CHANGE#,TO_CHAR(NEXT_TIME,'DD-MON-YYYY HH:MI:SS'),ARCHIVED,APPLIED from v$ar
chived_log where to_char(first_time,'DD-MON-YY')='13-OCT-09' order by sequence#,resetlogs_id
2 ;

SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME,' NEXT_CHANGE# TO_CHAR(NEXT_TIME,'D
---------- ------------- -------------------- ------------ --------------------
ARC APP
--- ---
1 14032304 13-OCT-2009 12:05:55 14055760 13-OCT-2009 01:25:38
YES NO

SQL> select distinct(checkpoint_change#),to_char(CHECKPOINT_TIME,'DD-MON-YYYY HH:MI:SS') from v$datafile;

CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_T
------------------ --------------------
14055761 13-OCT-2009 01:25:38