CONTROLFILE SCENARIOS
Case1: One control
lost
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 06:38:46 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> alter database backup controlfile to '/tmp/control.ctl';
Database altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_files string /u01/app/oracle/oradata/murali/c
ontrol01.ctl , /u01/app/oracle/
fast_recovery_area/murali/control02.ctl
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition
Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
[oracle@node1 tmp]$
rm /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 06:39:51 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as
sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> select name,open_mode from v$database;
select name,open_mode from v$database
*
ERROR at line 1:
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> shut immediate
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> shut abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition
Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
[oracle@node1 tmp]$
cp /u01/app/oracle/fast_recovery_area/murali/control02.ctl
/u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 tmp]$
sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 06:40:51 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle
instance.
SQL> startup
ORACLE instance
started.
Total System Global
Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 637537624 bytes
Database Buffers 197132288 bytes
Redo Buffers 2379776 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
MURALI READ WRITE
SQL>
Case 2: All control lost
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 06:44:57 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> show parameter control
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/murali/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/murali/contro
l02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter database backup controlfile to '/tmp/control.ctl';
Database altered.
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 tmp]$ rm /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 tmp]$ rm /u01/app/oracle/fast_recovery_area/murali/control02.ctl
[oracle@node1 tmp]$
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 06:45:52 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database
2 ;
select name from
v$database
*
ERROR at line 1:
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> select name from v$datafile;
select name from v$datafile
*
ERROR at line 1:
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> shut abort
ORACLE instance shut
down.
SQL>
SQL>
SQL> startup
ORACLE instance
started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 637537624 bytes
Database Buffers 197132288 bytes
Redo Buffers 2379776 bytes
ORA-00205: error in
identifying control file, check alert log for more info
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 trace]$ pwd
/u01/app/oracle/diag/rdbms/murali/murali/trace
[oracle@node1 trace]$ tail -20f alert_murali.log
starting up 1
dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jun 05 06:47:33
2013
MMNL started with
pid=16, OS id=2637
starting up 1 shared
server(s) ...
ORACLE_BASE from
environment = /u01/app/oracle
Wed Jun 05 06:47:33
2013
ALTER DATABASE MOUNT
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/fast_recovery_area/murali/control02.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-205 signalled
during: ALTER DATABASE MOUNT...
Wed Jun 05 06:47:34
2013
Checker run found 2 new
persistent data failures
[oracle@node1 trace]$ cp /tmp/control.ctl /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 trace]$ cp /tmp/control.ctl
/u01/app/oracle/fast_recovery_area/murali/control02.ctl
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 06:48:39 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode from v$database;
select name,open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not
mounted
SQL> alter database mount;
Database altered.
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-01194: file 1 needs
more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/murali/system01.dbf'
SQL> recover database;
ORA-00283: recovery
session canceled due to errors
ORA-01610: recovery
using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change
2859006 generated at 06/05/2013 06:41:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/MURALI/archivelog/2013_06_05/o1_mf_1_162_%u_.ar
c
ORA-00280: change
2859006 for thread 1 is in sequence #162
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/murali/redo03.log (Here
we need to put current redolog or active
redolog Just check in below).
Log applied.
Media recovery
complete.
SQL> alter database
open resetlogs;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
---------
--------------------
MURALI READ WRITE
Note: How to find current redo log file
SQL> select group#,members,status from v$log;
GROUP#
MEMBERS STATUS
---------- ----------
----------------
1 1 INACTIVE
3 1 CURRENT
2 1 INACTIVE
SQL> select group#,member from v$logfile;
GROUP# MEMBER
--------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/murali/redo01.log
2 /u01/app/oracle/oradata/murali/redo02.log
3 /u01/app/oracle/oradata/murali/redo03.log
Case 3: We lost all
controlfiles but we have backup controlfile in text format
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release 11.2.0.3.0
Production on Wed Jun 5 06:55:29 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/murali/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/murali/contro
l02.ctl
SQL>
SQL>
SQL>
SQL> alter database backup controlfile to trace as '/tmp/cttxt.txt';
Database altered.
SQL>
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 trace]$ rm /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 trace]$ rm /u01/app/oracle/fast_recovery_area/murali/control02.ctl
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:09:55 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> shut abort
ORACLE instance shut
down.
SQL> sqlplus
SP2-0042: unknown
command "sqlplus" - rest of line ignored.
SQL>
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release 11.2.0.3.0
Production on Wed Jun 5 07:10:19 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle
instance.
SQL> startup
ORACLE instance
started.
Total System Global
Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 637537624 bytes
Database Buffers 197132288 bytes
Redo Buffers 2379776 bytes
ORA-00205: error in
identifying control file, check alert log for more info
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 trace]$ pwd
/u01/app/oracle/diag/rdbms/murali/murali/trace
[oracle@node1 trace]$ tail -20f alert_murali.log
Wed Jun 05 07:10:30
2013
MMON started with
pid=15, OS id=3942
starting up 1
dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jun 05 07:10:30
2013
MMNL started with
pid=16, OS id=3944
starting up 1 shared
server(s) ...
ORACLE_BASE from
environment = /u01/app/oracle
Wed Jun 05 07:10:30
2013
ALTER DATABASE MOUNT
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/fast_recovery_area/murali/control02.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-205 signalled
during: ALTER DATABASE MOUNT...
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:12:33 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> CREATE CONTROLFILE REUSE DATABASE "MURALI"
NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/murali/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/murali/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/murali/redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 '/u01/app/oracle/oradata/murali/system01.dbf',
13 '/u01/app/oracle/oradata/murali/sysaux01.dbf',
14 '/u01/app/oracle/oradata/murali/undotbs01.dbf',
24 '/u01/app/oracle/oradata/murali/users01.dbf',
25 '/u01/app/oracle/oradata/murali/xdb01.dbf',
26 '/u01/app/oracle/oradata/murali/snapshot01.dbf'
27 CHARACTER SET AL32UTF8
28 ;
Control file created.
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/murali/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/murali/contro
l02.ctl
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
---------
--------------------
MURALI MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs
media recovery
ORA-01110: data file 1:
'/u01/app/oracle/oradata/murali/system01.dbf'
SQL> recover database;
Media recovery
complete.
SQL> alter database
open;
Database altered.
Note: If it shows using backup
controlfiles option then choose
steps in Case 2.
Case 4: Database dictionary
have extra datafiles compare to backup controlfiles
$sqlplus ‘/as sysdba’
SQL> alter database backup controlfile to '/tmp/control.ctl';
Database altered.
SQL> create tablespace t1 datafile '/u01/app/oracle/oradata/murali/t1.dbf'
size 10M;
Tablespace created.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_files string /u01/app/oracle/oradata/murali/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/murali/contro
l02.ctl
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 tmp]$ rm /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 tmp]$ rm /u01/app/oracle/fast_recovery_area/murali/control02.ctl
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:21:53 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
select name from
v$database
*
ERROR at line 1:
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> shutdown abort
ORACLE instance shut
down.
SQL> startup
ORACLE instance
started.
Total System Global
Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 637537624 bytes
Database Buffers 197132288 bytes
Redo Buffers 2379776 bytes
ORA-00205: error in
identifying control file, check alert log for more info
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 tmp]$ cd /u01/app/oracle/diag/rdbms/murali/murali/trace/
[oracle@node1 trace]$ tail -20f alert_murali.log
Wed Jun 05 07:22:11
2013
MMON started with
pid=15, OS id=4648
starting up 1
dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jun 05 07:22:11
2013
MMNL started with
pid=16, OS id=4650
starting up 1 shared
server(s) ...
ORACLE_BASE from
environment = /u01/app/oracle
Wed Jun 05 07:22:12
2013
ALTER DATABASE MOUNT
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/fast_recovery_area/murali/control02.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-205 signalled
during: ALTER DATABASE MOUNT...
[oracle@node1 trace]$ cp /tmp/control.ctl /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 trace]$ cp /tmp/control.ctl
/u01/app/oracle/fast_recovery_area/murali/control02.ctl
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:22:50 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> alter database mount;
Database altered.
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-01194: file 1 needs
more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/murali/system01.dbf'
SQL> recover database;
ORA-00283: recovery
session canceled due to errors
ORA-01610: recovery
using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change
2880507 generated at 06/05/2013 07:14:00 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/MURALI/archivelog/2013_06_05/o1_mf_1_2_%u_.arc
ORA-00280: change
2880507 for thread 1 is in sequence #2
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/murali/redo02.log (Note: Find current redo log in Case 2)
ORA-00283: recovery
session canceled due to errors
ORA-01244: unnamed
datafile(s) added to control file by media recovery
ORA-01110: data file
16: '/u01/app/oracle/oradata/murali/t1.dbf'
ORA-01112: media
recovery not started
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 trace]$ tail -20f alert_murali.log
it might be an online
backup taken without entering the begin backup command.
ORA-279 signalled
during: ALTER DATABASE RECOVER database
using backup controlfile ...
Wed Jun 05 07:23:55
2013
ALTER DATABASE
RECOVER LOGFILE
'/u01/app/oracle/oradata/murali/redo02.log'
Media Recovery Log
/u01/app/oracle/oradata/murali/redo02.log
File #16 added to
control file as 'UNNAMED00016'.
Originally created as:
'/u01/app/oracle/oradata/murali/t1.dbf'
Recovery interrupted!
Recovery stopped due to
failure in applying recovery marker (opcode 17.30).
Datafiles are recovered
to a consistent state at change 2880781 but controlfile could be ahead of
datafiles.
Media Recovery failed
with error 1244
ORA-283 signalled
during: ALTER DATABASE RECOVER LOGFILE
'/u01/app/oracle/oradata/murali/redo02.log'
...
ALTER DATABASE RECOVER
CANCEL
ORA-1112 signalled
during: ALTER DATABASE RECOVER CANCEL ...
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:24:58 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:26:04 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 trace]$ cat /etc/oratab | grep murali
murali:/u01/app/oracle/product/11.2.0.3/server:N
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:26:34 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> alter database rename file
'/u01/app/oracle/product/11.2.0.3/server/dbs/UNNAMED00016' to
'/u01/app/oracle/oradata/murali/t1.dbf';
Database altered.
Note: All UNNAMED00016 are
available in ORACLE_HOME/dbs location
SQL> recover database using backup controlfile;
ORA-00279: change
2880780 generated at 06/05/2013 07:21:09 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/MURALI/archivelog/2013_06_05/o1_mf_1_2_%u_.arc
ORA-00280: change
2880780 for thread 1 is in sequence #2
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/murali/redo02.log[current redo log]
Log applied.
Media recovery
complete.
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;
Database altered.
SQL>
Note:
If you have Text format backup then we need to add those extra datafiles paths
while creating controlfile.
Case 5: Dictionary having
less datafiles compare to controlfiles
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:35:51 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
T1
17 rows selected.
SQL> alter database backup controlfile to '/tmp/control.ctl';
Database altered.
SQL> drop tablespace t1 including contents and datafiles;
Tablespace dropped.
SQL>
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:37:05 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/murali/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/murali/contro
l02.ctl
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 tmp]$ rm /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 tmp]$ rm /u01/app/oracle/fast_recovery_area/murali/control02.ctl
[oracle@node1 tmp]$ sqlplus
SQL*Plus: Release
11.2.0.3.0 Production on Wed Jun 5 07:37:32 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
select name from
v$database
*
ERROR at line 1:
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> shut immediate
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27041: unable to
open file
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
SQL> shut abort
ORACLE instance shut
down.
SQL> startup
ORACLE instance
started.
Total System Global
Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 637537624 bytes
Database Buffers 197132288 bytes
Redo Buffers 2379776 bytes
ORA-00205: error in
identifying control file, check alert log for more info
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@node1 tmp]$ cd /u01/app/oracle/diag/rdbms/murali/murali/trace/
[oracle@node1 trace]$ tail -20f alert_murali.log
Wed Jun 05 07:37:57
2013
MMON started with
pid=15, OS id=5508
starting up 1
dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Jun 05 07:37:57
2013
MMNL started with
pid=16, OS id=5510
starting up 1 shared
server(s) ...
ORACLE_BASE from
environment = /u01/app/oracle
Wed Jun 05 07:37:57
2013
ALTER DATABASE MOUNT
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/fast_recovery_area/murali/control02.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-00210: cannot open
the specified control file
ORA-00202: control
file: '/u01/app/oracle/oradata/murali/control01.ctl'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional information:
3
ORA-205 signalled
during: ALTER DATABASE MOUNT...
[oracle@node1 trace]$ cp /tmp/control.ctl /u01/app/oracle/oradata/murali/control01.ctl
[oracle@node1 trace]$ cp /tmp/control.ctl /u01/app/oracle/fast_recovery_area/murali/control02.ctl
[oracle@node1 trace]$ sqlplus
SQL*Plus: Release 11.2.0.3.0
Production on Wed Jun 5 07:39:41 2013
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL> alter database mount;
Database altered.
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-01194: file 1 needs
more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/murali/system01.dbf'
SQL> recover database;
ORA-00283: recovery
session canceled due to errors
ORA-01610: recovery
using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00283: recovery
session canceled due to errors
ORA-01110: data file
16: '/u01/app/oracle/oradata/murali/t1.dbf'
ORA-01157: cannot
identify/lock data file 16 - see DBWR trace file
ORA-01110: data file
16: '/u01/app/oracle/oradata/murali/t1.dbf'
SQL> alter database create datafile '/u01/app/oracle/oradata/murali/t1.dbf';
Database altered.
SQL> recover database using
backup controlfile;
ORA-00279: change
2881053 generated at 06/05/2013 07:27:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/MURALI/archivelog/2013_06_05/o1_mf_1_1_%u_.arc
ORA-00280: change
2881053 for thread 1 is in sequence #1
Specify log:
{<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/murali/redo01.log [Current redo log]
Log applied.
Media recovery
complete.
SQL> alter database open resetlogs;
Database altered.
Note: If you have backup
controlfile in text format, while creating controlfile please extra datafiles
path
Simply Superb !
ReplyDelete