Wednesday, June 5, 2013

CONTROLFILE SCENARIOS

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


1 comment:

Share your knowledge it really improves, don't show off...