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

Recover from Loss of All Control Files - from backup control file

RECOVER FROM CORRUPTION OF ALL CONTROL FILES USING BINARY BACKUP CONTROL FILE

Tue Oct 13 11:45:38 2009
Hex dump of (file 0, block 1) in trace file c:\oracle\product\10.2.0\admin\orcln
ew\bdump\orclnew_arc1_3816.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 11:45:38 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc1_3816.tr
c:
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
'

Tue Oct 13 11:45:39 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc1_3816.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 11:45:39 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc1_3816.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 11:45:39 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_arc1_3816.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
Tue Oct 13 11:46:09 2009
Hex dump of (file 0, block 1) in trace file c:\oracle\product\10.2.0\admin\orcln
ew\bdump\orclnew_lgwr_5128.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 11:46:09 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_lgwr_5128.tr
c:
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
'

Tue Oct 13 11:46:10 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_lgwr_5128.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
'

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

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

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

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

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

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

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

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

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

Tue Oct 13 11:46:29 2009
Instance terminated by LGWR, pid = 5128

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


SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1294680 bytes
Variable Size 465571496 bytes
Database Buffers 599785472 bytes
Redo Buffers 7090176 bytes
ORA-00205: error in identifying control file, check alert log for more info

All Control Files lost.


sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnewbkp
$ cp BACKUPCONTROLFILE.CTL ../orclnew/CONTROL01.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnewbkp
$ cd ../orclnew

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ ls -ltr CONTROL*
-rwxr-xr-x 1 sphilip mkgroup-l-d 7749632 Oct 13 11:50 CONTROL01.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ cp CONTROL01.CTL CONTROL02.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ cp CONTROL01.CTL CONTROL03.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ ls -ltr CONTROL*
-rwxr-xr-x 1 sphilip mkgroup-l-d 7749632 Oct 13 11:50 CONTROL01.CTL
-rwxr-xr-x 1 sphilip mkgroup-l-d 7749632 Oct 13 11:51 CONTROL02.CTL
-rwxr-xr-x 1 sphilip mkgroup-l-d 7749632 Oct 13 11:51 CONTROL03.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ chmod 777 CONTROL*

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ ls -ltr CONTROL*
-rwxrwxrwx 1 sphilip mkgroup-l-d 7749632 Oct 13 11:50 CONTROL01.CTL
-rwxrwxrwx 1 sphilip mkgroup-l-d 7749632 Oct 13 11:51 CONTROL02.CTL
-rwxrwxrwx 1 sphilip mkgroup-l-d 7749632 Oct 13 11:51 CONTROL03.CTL

ORACLE instance shut down.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1294680 bytes
Variable Size 469765800 bytes
Database Buffers 595591168 bytes
Redo Buffers 7090176 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSTEM01.DBF'

SQL> recover database using backup controlfile;
ORA-00279: change 14032132 generated at 10/13/2009 11:40:02 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_0684492530.001
ORA-00280: change 14032132 for thread 1 is in sequence #95


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_0684492530.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_0684492530.001'
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>

ALTER DATABASE RECOVER database using backup controlfile
Tue Oct 13 11:59:19 2009
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 6 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 8 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 9 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 10 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 11 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 12 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 13 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 14 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 15 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 16 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 17 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 18 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 19 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 20 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 21 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 22 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 23 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 24 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 25 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 26 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 27 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 28 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 29 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 30 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 31 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 32 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 33 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 34 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 35 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 36 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 37 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 38 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 39 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 40 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 41 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 42 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 43 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
parallel recovery started with 2 processes
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlf
ile ...
Tue Oct 13 12:01:32 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Oct 13 12:01:32 2009
Media Recovery Log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_068
4492530.001
Errors with log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_068449
2530.001
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Oct 13 12:01:32 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Oct 13 12:01:32 2009
Media Recovery Log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_068
4492530.001
Errors with log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_068449
2530.001
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Tue Oct 13 12:01:32 2009
ALTER DATABASE RECOVER CANCEL
Tue Oct 13 12:01:45 2009
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL

NAME FIRST_CHANGE# NEXT_CHANGE#
------------------------------------------------------------ ------------- ------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00089_06 14011324 14011364
84492530.001

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00090_06 14011364 14011367
84492530.001

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00091_06 14011367 14011370
84492530.001

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00092_06 14011370 14011380
84492530.001

NAME FIRST_CHANGE# NEXT_CHANGE#
------------------------------------------------------------ ------------- ------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00093_06 14011380 14011383
84492530.001

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00094_06 14011383 14031658
84492530.001


488 rows selected.

SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSTEM01.DBF'


SQL> recover database using backup controlfile;
ORA-00279: change 14032132 generated at 10/13/2009 11:40:02 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00095_0684492530.001
ORA-00280: change 14032132 for thread 1 is in sequence #95

SQL> select group#,sequence# from v$log;

GROUP# SEQUENCE#
---------- ----------
1 95
3 93
2 94


Specify log: {=suggested | filename | AUTO | CANCEL}
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO1A.LOG
Log applied.
Media recovery complete.

Tue Oct 13 12:04:21 2009
ALTER DATABASE RECOVER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO
1A.LOG'
Tue Oct 13 12:04:21 2009
Media Recovery Log C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO1A.LOG
Tue Oct 13 12:04:33 2009
Completed: ALTER DATABASE RECOVER LOGFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\O
RCLNEW\REDO1A.LOG'
Tue Oct 13 12:05:54 2009
alter database open resetlogs


1* 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$archived_log where to_char(first_time,'DD-MON-YY')='13-OCT-09' order by sequence#,resetlogs_id
SQL> /

SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME,' NEXT_CHANGE# TO_CHAR(NEXT_TIME,'D ARC APP
---------- ------------- -------------------- ------------ -------------------- --- ---
73 13987064 13-OCT-2009 09:31:35 13987105 13-OCT-2009 09:32:20 YES NO
74 13987105 13-OCT-2009 09:32:20 13987108 13-OCT-2009 09:32:24 YES NO
75 13987108 13-OCT-2009 09:32:24 13987708 13-OCT-2009 09:51:44 YES NO
76 13987708 13-OCT-2009 09:51:44 13987710 13-OCT-2009 09:51:45 YES NO
77 13987710 13-OCT-2009 09:51:45 14007712 13-OCT-2009 10:36:41 YES NO
78 14007712 13-OCT-2009 10:36:41 14008308 13-OCT-2009 10:45:31 YES NO
79 14008308 13-OCT-2009 10:45:31 14008310 13-OCT-2009 10:45:31 YES NO
80 14008310 13-OCT-2009 10:45:31 14008313 13-OCT-2009 10:45:37 YES NO
81 14008313 13-OCT-2009 10:45:37 14008325 13-OCT-2009 10:46:12 YES NO
82 14008325 13-OCT-2009 10:46:12 14008327 13-OCT-2009 10:46:13 YES NO
83 14008327 13-OCT-2009 10:46:13 14008329 13-OCT-2009 10:46:17 YES NO

SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME,' NEXT_CHANGE# TO_CHAR(NEXT_TIME,'D ARC APP
---------- ------------- -------------------- ------------ -------------------- --- ---
84 14008329 13-OCT-2009 10:46:17 14011258 13-OCT-2009 11:23:33 YES NO
85 14011258 13-OCT-2009 11:23:33 14011313 13-OCT-2009 11:23:44 YES NO
86 14011313 13-OCT-2009 11:23:44 14011317 13-OCT-2009 11:23:53 YES NO
87 14011317 13-OCT-2009 11:23:53 14011321 13-OCT-2009 11:24:01 YES NO
88 14011321 13-OCT-2009 11:24:01 14011324 13-OCT-2009 11:24:08 YES NO
89 14011324 13-OCT-2009 11:24:08 14011364 13-OCT-2009 11:25:12 YES NO
90 14011364 13-OCT-2009 11:25:12 14011367 13-OCT-2009 11:25:17 YES NO
91 14011367 13-OCT-2009 11:25:17 14011370 13-OCT-2009 11:25:23 YES NO
92 14011370 13-OCT-2009 11:25:23 14011380 13-OCT-2009 11:25:25 YES NO
93 14011380 13-OCT-2009 11:25:25 14011383 13-OCT-2009 11:25:29 YES NO
93 14011380 13-OCT-2009 11:25:25 14011383 13-OCT-2009 11:25:29 YES YES

SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME,' NEXT_CHANGE# TO_CHAR(NEXT_TIME,'D ARC APP
---------- ------------- -------------------- ------------ -------------------- --- ---
94 14011383 13-OCT-2009 11:25:29 14031658 13-OCT-2009 11:38:37 YES NO
94 14011383 13-OCT-2009 11:25:29 14031658 13-OCT-2009 11:38:37 YES YES
95 14031658 13-OCT-2009 11:38:37 14032304 13-OCT-2009 12:05:56 YES NO

25 rows selected.


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

CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_T
------------------ --------------------
14032305 13-OCT-2009 12:06:28

Recover from corruption of single control file

RECOVER CONTROL FILE; MULTIPLEXED COPIES OF CONTROL FILE EXISTS

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

Hex dump of (file 0, block 1) in trace file c:\oracle\product\10.2.0\admin\orcln
ew\bdump\orclnew_lgwr_3352.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: 0x2400
consistency value in tail: 0x00000000
check value in block header: 0x0
block checksum disabled
Tue Oct 13 11:34:08 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_lgwr_3352.tr
c:
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
'

Tue Oct 13 11:34:09 2009
Errors in file c:\oracle\product\10.2.0\admin\orclnew\bdump\orclnew_lgwr_3352.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
'

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

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

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

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

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

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

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

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

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

COPY CONTROL02.CTL TO CONTROL01.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ ls -ltr CONTROL*
-rwx------+ 1 sphilip ???????? 7749632 Oct 13 11:34 CONTROL03.CTL
-rwx------+ 1 sphilip ???????? 7749632 Oct 13 11:34 CONTROL02.CTL
-rwx------+ 1 sphilip ???????? 4644319 Oct 13 11:34 CONTROL01.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ cp CONTROL02.CTL CONTROL01.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew
$ ls -ltr CONTROL*
-rwx------+ 1 sphilip ???????? 7749632 Oct 13 11:34 CONTROL03.CTL
-rwx------+ 1 sphilip ???????? 7749632 Oct 13 11:34 CONTROL02.CTL
-rwx------ 1 sphilip mkgroup-l-d 7749632 Oct 13 11:37 CONTROL01.CTL

sphilip@seadba01 /cygdrive/c/oracle/product/10.2.0/oradata/orclnew

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1294680 bytes
Variable Size 461377192 bytes
Database Buffers 603979776 bytes
Redo Buffers 7090176 bytes
Database mounted.
Database opened.

Recover from Redo Group Corruption post resetlogs - No Backups, Flashback Off

RECOVER FROM REDO GROUP CORRUPTION IMMEDIATELY AFTER RESETLOGS - NO BACKUPS POST RESETLOGS, FLASHBACK OFF

SYS@orclnew AS SYSDBA>select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
3 CURRENT

SYS@orclnew AS SYSDBA>select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
3 CURRENT

SYS@orclnew AS SYSDBA>alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-00316: log of thread , type in header is not log file


SYS@orclnew AS SYSDBA>select * from v$instance;
ERROR:
ORA-03114: not connected to ORACLE


SYS@orclnew AS SYSDBA>shutdown immediate;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@orclnew AS SYSDBA>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\sphilip>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 17 09:21:34 2009

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

Connected to an idle instance.

SYS@orclnew AS SYSDBA>startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 339738928 bytes
Database Buffers 725614592 bytes
Redo Buffers 7135232 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.2.0\ORADATA\ORCLNEW\REDO3A.LOG'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 104835887)
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 104841780)


SYS@orclnew AS SYSDBA>alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance orclnew (thread 1) - cannot drop
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'


SYS@orclnew AS SYSDBA>alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SYS@orclnew AS SYSDBA>select group#,status,sequence# from v$log;

GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 UNUSED 0
3 CURRENT 1
2 UNUSED 0

SYS@orclnew AS SYSDBA>alter database drop logfile member 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG';
alter database drop logfile member 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'


SYS@orclnew AS SYSDBA>alter system archive log;
alter system archive log
*
ERROR at line 1:
ORA-01974: Illegal archive option


SYS@orclnew AS SYSDBA>alter system archive logfile;
alter system archive logfile
*
ERROR at line 1:
ORA-02000: missing LOG keyword


SYS@orclnew AS SYSDBA>alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-01109: database not open


SYS@orclnew AS SYSDBA>alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving


SYS@orclnew AS SYSDBA>alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance orclnew (thread 1)
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'


SYS@orclnew AS SYSDBA>alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance orclnew (thread 1)
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3B.LOG'
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO3A.LOG'

SYS@orclnew AS SYSDBA>recover database until cancel;
ORA-00279: change 9860070 generated at 04/16/2009 12:13:27 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0684331970.001
ORA-00280: change 9860070 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSTEM01.DBF'


ORA-01112: media recovery not started


SYS@orclnew AS SYSDBA>select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
3 CURRENT
2 UNUSED

SYS@orclnew AS SYSDBA>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\SYSTEM01.DBF'


SYS@orclnew AS SYSDBA>desc v$datafile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE

SYS@orclnew AS SYSDBA>select distinct(checkpoint_change#), checkpoint_time from v$datafile;

CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
9860070 16-APR-09

SYS@orclnew AS SYSDBA>select distinct(checkpoint_change#),to_char(checkpoint_time,'dd-MON-yy hh24:mi:ss') from v$datafile;

CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT
------------------ ------------------
9860070 16-APR-09 12:13:27

SYS@orclnew AS SYSDBA>desc v$archived_log
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID NUMBER
STAMP NUMBER
NAME VARCHAR2(513)
DEST_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
RESETLOGS_ID NUMBER
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
BLOCKS NUMBER
BLOCK_SIZE NUMBER
CREATOR VARCHAR2(7)
REGISTRAR VARCHAR2(7)
STANDBY_DEST VARCHAR2(3)
ARCHIVED VARCHAR2(3)
APPLIED VARCHAR2(3)
DELETED VARCHAR2(3)
STATUS VARCHAR2(1)
COMPLETION_TIME DATE
DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END VARCHAR2(3)
END_OF_REDO VARCHAR2(3)
BACKUP_COUNT NUMBER
ARCHIVAL_THREAD# NUMBER
ACTIVATION# NUMBER
IS_RECOVERY_DEST_FILE VARCHAR2(3)
COMPRESSED VARCHAR2(3)
FAL VARCHAR2(3)
END_OF_REDO_TYPE VARCHAR2(10)

SYS@orclnew AS SYSDBA>column name format A90
SYS@orclnew AS SYSDBA>set lines 1000

select name, first_change#, to_char(first_time,'dd-MON-yy hh24:mi:ss'), next_change#,to_char(next_time,'dd-MON-yy hh24:mi:ss') from v$archived_log
/
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00005_0683717387.001 9555080 09-APR-09 16:53:56 9555127 09-APR-09 16:54:24
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00007_0683717387.001 9642083 14-APR-09 15:06:45 9662098 14-APR-09 15:14:17
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00006_0683717387.001 9555127 09-APR-09 16:54:24 9642083 14-APR-09 15:06:45
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00008_0683717387.001 9662098 14-APR-09 15:14:17 9691409 14-APR-09 16:19:15
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00009_0683717387.001 9691409 14-APR-09 16:19:15 9757691 15-APR-09 09:37:15
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00010_0683717387.001 9757691 15-APR-09 09:37:15 9760219 15-APR-09 10:05:16
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00011_0683717387.001 9760219 15-APR-09 10:05:16 9760221 15-APR-09 10:05:17
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00012_0683717387.001 9760221 15-APR-09 10:05:17 9760224 15-APR-09 10:05:21
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00013_0683717387.001 9760224 15-APR-09 10:05:21 9760232 15-APR-09 10:05:38
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00020_0683717387.001 9760551 15-APR-09 10:10:54 9859309 16-APR-09 11:09:42
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00021_0683717387.001 9859309 16-APR-09 11:09:42 9860008 16-APR-09 11:11:56

NAME FIRST_CHANGE# TO_CHAR(FIRST_TIME NEXT_CHANGE# TO_CHAR(NEXT_TIME,
------------------------------------------------------------------------------------------ ------------- ------------------ ------------ ------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00022_0683717387.001 9860008 16-APR-09 11:11:56 9860069 16-APR-09 12:12:59

SYS@orclnew AS SYSDBA>FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2009-04-16 12:12:59','YYYY-MM-DD HH24:MI:SS');
FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2009-04-16 12:12:59','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38726: Flashback database logging is not on.

RESTORE DATABASE; -- from last full backup


RMAN> recover database until SCN 9860069;

Starting recover at 17-APR-09
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/17/2009 10:04:12
RMAN-20208: UNTIL CHANGE is before RESETLOGS change

Issue because I reset logs yesterday 04/16
RMAN> list incarnation of database orclnew;


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 CURRENT 9860069 16-APR-09

and after reset logs there have been no logs archived and the CURRENT redo log got corrupted.


Now I am restoring Control file to before resetlogs


RMAN> restore controlfile from 'C:\Oracle\product\10.2.0\admin\orclnew\bdump\C-3405949784-20090415-01';

Starting restore at 17-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=385 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\CONTROL03.CTL
Finished restore at 17-APR-09

RMAN> list incarnation of database orclnew;

released channel: ORA_DISK_1

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 CURRENT 9535505 09-APR-09



NAME FIRST_CHANGE# TO_CHAR(FIRST_TIME NEXT_CHANGE# TO_CHAR(NEXT_TIME,
------------------------------------------------------------------------------------------ ------------- ------------------ ------------ ------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00005_0683717387.001 9555080 09-APR-09 16:53:56 9555127 09-APR-09 16:54:24
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00007_0683717387.001 9642083 14-APR-09 15:06:45 9662098 14-APR-09 15:14:17
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00006_0683717387.001 9555127 09-APR-09 16:54:24 9642083 14-APR-09 15:06:45
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00008_0683717387.001 9662098 14-APR-09 15:14:17 9691409 14-APR-09 16:19:15
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00009_0683717387.001 9691409 14-APR-09 16:19:15 9757691 15-APR-09 09:37:15
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00010_0683717387.001 9757691 15-APR-09 09:37:15 9760219 15-APR-09 10:05:16
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00011_0683717387.001 9760219 15-APR-09 10:05:16 9760221 15-APR-09 10:05:17
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00012_0683717387.001 9760221 15-APR-09 10:05:17 9760224 15-APR-09 10:05:21
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00013_0683717387.001 9760224 15-APR-09 10:05:21 9760232 15-APR-09 10:05:38

SYS@orclnew AS SYSDBA>recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


Failed because FRA was full and some archivelogs had to cleaned out.

SYS@orclnew AS SYSDBA>recover database using backup controlfile;
ORA-00279: change 9369624 generated at 04/03/2009 11:01:00 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00005_0682424915.001
ORA-00280: change 9369624 for thread 1 is in sequence #5


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 9422740 generated at 04/04/2009 10:57:31 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00006_0682424915.001
ORA-00280: change 9422740 for thread 1 is in sequence #6
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00005_0682424915.001' no longer needed for this recovery


ORA-00279: change 9517452 generated at 04/06/2009 04:00:17 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00007_0682424915.001
ORA-00280: change 9517452 for thread 1 is in sequence #7
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00006_0682424915.001' no longer needed for this recovery


ORA-00279: change 9533465 generated at 04/08/2009 12:51:05 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0683643065.001
ORA-00280: change 9533465 for thread 1 is in sequence #1


ORA-00279: change 9535505 generated at 04/09/2009 09:29:47 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0683717387.001
ORA-00280: change 9535505 for thread 1 is in sequence #1


ORA-00279: change 9555057 generated at 04/09/2009 16:53:14 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00002_0683717387.001
ORA-00280: change 9555057 for thread 1 is in sequence #2
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00001_0683717387.001' no longer needed for this recovery


ORA-00279: change 9555069 generated at 04/09/2009 16:53:46 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00003_0683717387.001
ORA-00280: change 9555069 for thread 1 is in sequence #3
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00002_0683717387.001' no longer needed for this recovery


ORA-00279: change 9555072 generated at 04/09/2009 16:53:51 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00004_0683717387.001
ORA-00280: change 9555072 for thread 1 is in sequence #4
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00003_0683717387.001' no longer needed for this recovery


ORA-00279: change 9555080 generated at 04/09/2009 16:53:56 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00005_0683717387.001
ORA-00280: change 9555080 for thread 1 is in sequence #5
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00004_0683717387.001' no longer needed for this recovery


ORA-00279: change 9555127 generated at 04/09/2009 16:54:24 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00006_0683717387.001
ORA-00280: change 9555127 for thread 1 is in sequence #6
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00005_0683717387.001' no longer needed for this recovery


ORA-00279: change 9642083 generated at 04/14/2009 15:06:45 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00007_0683717387.001
ORA-00280: change 9642083 for thread 1 is in sequence #7
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00006_0683717387.001' no longer needed for this recovery


ORA-00279: change 9662098 generated at 04/14/2009 15:14:17 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00008_0683717387.001
ORA-00280: change 9662098 for thread 1 is in sequence #8
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00007_0683717387.001' no longer needed for this recovery


ORA-00279: change 9691409 generated at 04/14/2009 16:19:15 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00009_0683717387.001
ORA-00280: change 9691409 for thread 1 is in sequence #9
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00008_0683717387.001' no longer needed for this recovery


ORA-00279: change 9757691 generated at 04/15/2009 09:37:15 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00010_0683717387.001
ORA-00280: change 9757691 for thread 1 is in sequence #10
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00009_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760219 generated at 04/15/2009 10:05:16 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00011_0683717387.001
ORA-00280: change 9760219 for thread 1 is in sequence #11
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00010_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760221 generated at 04/15/2009 10:05:17 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00012_0683717387.001
ORA-00280: change 9760221 for thread 1 is in sequence #12
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00011_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760224 generated at 04/15/2009 10:05:21 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00013_0683717387.001
ORA-00280: change 9760224 for thread 1 is in sequence #13
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00012_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760232 generated at 04/15/2009 10:05:38 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00014_0683717387.001
ORA-00280: change 9760232 for thread 1 is in sequence #14
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00013_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760482 generated at 04/15/2009 10:10:18 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00015_0683717387.001
ORA-00280: change 9760482 for thread 1 is in sequence #15
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00014_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760485 generated at 04/15/2009 10:10:24 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00016_0683717387.001
ORA-00280: change 9760485 for thread 1 is in sequence #16
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00015_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760492 generated at 04/15/2009 10:10:30 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00017_0683717387.001
ORA-00280: change 9760492 for thread 1 is in sequence #17
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00016_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760525 generated at 04/15/2009 10:10:39 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00018_0683717387.001
ORA-00280: change 9760525 for thread 1 is in sequence #18
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00017_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760530 generated at 04/15/2009 10:10:44 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00019_0683717387.001
ORA-00280: change 9760530 for thread 1 is in sequence #19
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00018_0683717387.001' no longer needed for this recovery


ORA-00279: change 9760551 generated at 04/15/2009 10:10:54 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00020_0683717387.001
ORA-00280: change 9760551 for thread 1 is in sequence #20
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00019_0683717387.001' no longer needed for this recovery


ORA-00279: change 9859309 generated at 04/16/2009 11:09:42 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00021_0683717387.001
ORA-00280: change 9859309 for thread 1 is in sequence #21
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00020_0683717387.001' no longer needed for this recovery


ORA-00279: change 9860008 generated at 04/16/2009 11:11:56 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00022_0683717387.001
ORA-00280: change 9860008 for thread 1 is in sequence #22
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00021_0683717387.001' no longer needed for this recovery


Log applied.
Media recovery complete.

NAME FIRST_CHANGE# TO_CHAR(FIRST_TIME NEXT_CHANGE# TO_CHAR(NEXT_TIME,
------------------------------------------------------------------------------------------ ------------- ------------------ ------------ ------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00005_0683717387.001 9555080 09-APR-09 16:53:56 9555127 09-APR-09 16:54:24
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00007_0683717387.001 9642083 14-APR-09 15:06:45 9662098 14-APR-09 15:14:17
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00006_0683717387.001 9555127 09-APR-09 16:54:24 9642083 14-APR-09 15:06:45
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00008_0683717387.001 9662098 14-APR-09 15:14:17 9691409 14-APR-09 16:19:15
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00009_0683717387.001 9691409 14-APR-09 16:19:15 9757691 15-APR-09 09:37:15
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00010_0683717387.001 9757691 15-APR-09 09:37:15 9760219 15-APR-09 10:05:16
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00011_0683717387.001 9760219 15-APR-09 10:05:16 9760221 15-APR-09 10:05:17
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00012_0683717387.001 9760221 15-APR-09 10:05:17 9760224 15-APR-09 10:05:21
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ORCLNEW\ARC00013_0683717387.001 9760224 15-APR-09 10:05:21 9760232 15-APR-09 10:05:38
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO1A.LOG 0 0
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO1B.LOG 0 0

NAME FIRST_CHANGE# TO_CHAR(FIRST_TIME NEXT_CHANGE# TO_CHAR(NEXT_TIME,
------------------------------------------------------------------------------------------ ------------- ------------------ ------------ ------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO2A.LOG 0 0
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLNEW\REDO2B.LOG 0 0

alter database open resetlogs;

SYS@orclnew AS SYSDBA>select group#,status,sequence#,first_change#,to_char(first_time,'dd-MON-yy hh24:mi:ss') from v$log;

GROUP# STATUS SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME
---------- ---------------- ---------- ------------- ------------------
1 CURRENT 1 9860069 17-APR-09 10:35:05
2 UNUSED 0 0
3 UNUSED 0 0

RMAN> list incarnation of database orclnew;


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 CURRENT 9860069 17-APR-09