Showing posts with label PT. Show all posts
Showing posts with label PT. Show all posts

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


Posted by at 1 comment:
Labels: ,

Wednesday, February 27, 2013

Oracle 11g AUTO SAMPLING STATISTICS


Improvement of AUTO sampling statistics gathering feature in Oracle Database 11g
Optimizer statistics in Oracle are managed via a pl/sql package, dbms_stats. It provides several pl/sql procedures to gather statistics for a table, schema, or a database. For example, gather_table_statistics is used to gather statistics on a table. This procedure has an estimate_percent parameter, which specifies the sampling percentage of the statistics gathering. The users can specify any number between 0 ~ 100 for this parameter. For example, suppose you have a table BIGT, you can specify a 1% sampling percentage as follows:
exec dbms_stats.gather_table_stats(null, 'BIGT',
                       estimate_percent => 1);
It is not always easy for users to pick the appropriate sampling percentage. If the specified sampling percentage is too high, it can take longer to gather statistics. On the contray, if the data is skewed and the specified sampling percentage is too low, the resulting statistics can be inaccurate. For this reason, Oracle introduced the AUTO value for the estimate_percent parameter. For example, you can gather statistics on BIGT as follows:
exec dbms_stats.gather_table_stats(null, 'BIGT',
                       estimate_percent => dbms_stats.auto_sample_size);
The advantage of using AUTO sample size over a fixed number is two-folds. First, when AUTO sample size is specified, the system automatically determines the appropriate sampling percentage. Second, AUTO sample size is more flexible than a fixed sampling percentage. A fixed sampling percentage size that was good at some point in time may not be appropriate after the data distribution in the table has changed. On the other hand when AUTO value is used Oracle will adjust the sample size when the data distribution changes.
When AUTO is used Oracle picks a sample size where the statistics quality is good enough. However, it does not work very well under cases of extreme skew in the data. In Oracle 11g, we improved the behavior when the AUTO value is used. First, AUTO sampling now generates deterministic statistics. Second, and more importantly, AUTO sampling generates statistics that are almost as accurate as 100% sampling but takes much less time than 100% sampling. To illustrate these merits, we compare the performance of using a fixed sampling percentage, AUTO sample size in Oracle 10g and AUTO sample size in Oracle 11g. We used the standard TPC-D data generator to generate a Lineitem table. The Lineitem table is about 230G and contains 1.8 million rows with 16 columns. The schema of the lineitem table is as follows:
column name
column type
l_shipdate
date
l_orderkey
number
l_discount
number
l_extendedprice
number
l_suppkey
number
l_quantity
number
l_returnflag
varchar2
l_partkey
number
l_linestatus
varchar2
l_tax
number
l_commitdate
date
l_receiptdate
date
l_shipmode
varchar2
l_linenumber
number
l_shipinstruct
varchar2
l_comment
varchar2

Table 1 gives the elapsed time of gathering statistics on the Lineitem table by different sampling percentages.
Sampling Percentage
Elapsed Time (sec)
1% sampling
797
100% sampling (Compute)
18772
Auto sampling in Oracle 10g
2935
Auto sampling in Oracle 11g
1908

Table 1: Statistics gathering time on 230G TPC-D Lineitem Table Using Different Estimate Percentages We also compare the quality of the statistics gathered using different estimate percentages. Among all the statistics of a column, number of distinct values (NDV) is the one whose accuracy used to be an issue. We define the accuracy rate of NDV of a column as follows:
accuracy rate = 1 - (|estimated NDV - actual NDV|)/actual NDV.
The accuracy rate ranges from 0% to 100%. The higher the accuracy rate is, the more accurate the gathered statistics are. Since 100% sampling always lead to an accuracy rate of 100%, we do not report it. We focus on the columns which has at least one statistics accuracy rate below 99.9% when using different estimate percentages. Table 2 illustrates the accurate rates of the columns.
Column Name
Actual NDV
Auto Sampling in Oracle 11g
1% Sampling
orderkey
450,000,000
98.0%
50%
comment
181,122,127
98.60%
4.60%
partkey
60,000,000
99.20%
98.20%
suppkey
3,000,000
99.60%
99.90%
extendedprice
3,791,320
99.60%
94.30%

Table 2: Accuracy Rate of Gathering NDV LineItem Using Different Estimate Percentages In short, the elapsed time of ''AUTO sampling in Oracle 11g'' is 10 times faster than 100% sampling but yields the statistics of similar quality (accuracy rate close to 100%). 
Posted by at 1 comment:
Labels:
Subscribe to: Posts (Atom)