OraDataSystems.com

Showing posts with label Loss of Control Files. Show all posts
Showing posts with label Loss of Control Files. Show all posts

Thursday, November 11, 2010

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
Posted by at 1 comment:
Labels:
Subscribe to: Posts (Atom)