LOSS of all controlfiles (Complete recovery and No need to open the database with resetlogs after recreating the controlfile manually)
Assumptions:
-
We are tracing the backup controlfile as part of backup activity or we are
having controlfile recreation script.
-
We have lossed all the controlfiles including mirrored controlfiles also.
1. I have deleted all the
controlfiles manually at OS level.
ls -ltr *.ctl
-rw-r----- 1 oracle
dba 6602752 Oct 26 14:01
SID_control03.ctl
-rw-r----- 1 oracle
dba 6602752 Oct 26 14:01
SID_control02.ctl
-rw-r----- 1 oracle
dba 6602752 Oct 26 14:01
SID_control01.ctl
rm
*.ctl
ls
-ltr *.ctl
ls:
0653-341 The file *.ctl does not exist.
2. After deleting the
controlfiles I have connected to the database and try to access the some
information from controlfile views,
while viewing the information from v$controlfile_record_section view got
the below error. Below are the details.
sqlplus
"/as sysdba"
SQL*Plus:
Release 10.2.0.2.0 - Production on Mon Oct 26 14:02:32 2009
Copyright
(c) 1982, 2005, Oracle. All Rights
Reserved.
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With
the Partitioning and Data Mining options
SQL>
select * from V$CONTROLFILE_RECORD_SECTION;
select
* from V$CONTROLFILE_RECORD_SECTION
*
ERROR
at line 1:
ORA-00210:
cannot open the specified control file
ORA-00202: control file:
'/restore1/oracle/SID/data/SID_control01.ctl'
ORA-27041:
unable to open file
IBM
AIX RISC System/6000 Error: 2: No such file or directory
Additional
information: 3
Note:
After above step I tried to
reproduce the same error in different ways,after lossing all the controlfiles
also, I'm able to do manual check point and swich logfile successfully. While
accessing most of the v$ views I got the controlfile error. Below are the
details.
SQL>
commit;
Commit
complete.
SQL>
alter system switch logfile;
System
altered.
SQL>
alter system checkpoint;
System
altered.
SQL>
show parameter control
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /restore1/oracle/SID/data
/SID_control01.ctl, /rest
ore1/oracle/SID/data/ATOP
RD01_control02.ctl, /restore1/
oracle/SID/data/SID_
control03.ctl
SQL>
archive log list
ORA-00210:
cannot open the specified control file
ORA-00202: control file:
'/restore1/oracle/SID/data/SID_control01.ctl'
ORA-27041:
unable to open file
IBM
AIX RISC System/6000 Error: 2: No such file or directory
Additional
information: 3
SQL>
select * from v$log;
select
* from v$log
*
ERROR
at line 1:
ORA-00210:
cannot open the specified control file
ORA-00202: control file:
'/restore1/oracle/SID/data/SID_control01.ctl'
ORA-27041:
unable to open file
IBM
AIX RISC System/6000 Error: 2: No such file or directory
Additional
information: 3
SQL>
select * from v$logfile;
select
* from v$logfile
*
ERROR
at line 1:
ORA-00210:
cannot open the specified control file
ORA-00202: control file:
'/restore1/oracle/SID/data/SID_control01.ctl'
ORA-27041:
unable to open file
IBM
AIX RISC System/6000 Error: 2: No such file or directory
Additional
information: 3
SQL>
alter system backup controlfile to trace;
alter
system backup controlfile to trace
*
ERROR
at line 1:
ORA-02065:
illegal option for ALTER SYSTEM
SQL>
select * from v$database;
select
* from v$database
*
ERROR
at line 1:
ORA-00210:
cannot open the specified control file
ORA-00202: control file:
'/restore1/oracle/SID/data/SID_control01.ctl'
ORA-27041:
unable to open file
IBM
AIX RISC System/6000 Error: 2: No such file or directory
Additional
information: 3
3. After that shut down
database using shut abort option.
SQL>
shut abort
ORACLE
instance shut down.
4. Below is the control file
recreation script make sure change the resetlogs option to noresetlogs
(Because we are having all archives and on line
redo logs for complete recovery).
cat
cr8contrl.sql
CREATE
CONTROLFILE REUSE DATABASE "SID" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
'/restore1/oracle/SID/data/SID_redo01a.log',
'/restore1/oracle/SID/data/SID_redo01b.log'
) SIZE 10M,
GROUP 2 (
'/restore1/oracle/SID/data/SID_redo02a.log',
'/restore1/oracle/SID/data/SID_redo02b.log'
) SIZE 10M
--
STANDBY LOGFILE
DATAFILE
'/restore1/oracle/SID/data/SID_system01.dbf',
'/restore1/oracle/SID/data/SID_undotbs01.dbf',
'/restore1/oracle/SID/data/SID_sysaux01.dbf',
'/restore1/oracle/SID/data/SID_users01.dbf',
'/restore1/oracle/SID/data/SID_autodata01.dbf',
'/restore1/oracle/SID/data/SID_autoindexes01.dbf'
CHARACTER
SET US7ASCII
;
5. start the instance in
nomount( this step is not required because startup nomount is part of
controlfile recreation script).
SQL>
startup nomount
ORACLE
instance started.
Total
System Global Area 838860800 bytes
Fixed
Size 2074928 bytes
Variable
Size 503318224 bytes
Database
Buffers 327155712 bytes
Redo
Buffers 6311936 bytes
6. run the controlfile
recreatin script as below.
SQL>@cr8contrl.sql
Control
file created.
7. Try to open the database
without recovery will get the error message like datafile 1 is inconsistent.
SQL>alter
database open;
alter
database open
*
ERROR
at line 1:
ORA-01113:
file 1 needs media recovery
ORA-01110: data file 1:
'/restore1/oracle/SID/data/SID_system01.dbf'
8. Recovery the database
without using backup controlfile option.
SQL>recover
database;
Media
recovery completed.
9. Open the database without resetlogs;
SQL>
alter database open;
Database
altered.
No comments:
Post a Comment