I bet you haven't seen 62 hours of AWR Report

One of my customers is having extreme performance issues during batch load. The batch is consuing around 6 hours to complete which is supposed to complete in less than 90 minutes.

The guys shared their AWR report with us and here is the snapshot information for you guys:

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      4621 27-Nov-10 00:00:09        30       1.3
  End Snap:      4683 29-Nov-10 14:00:51        30       2.0
   Elapsed:            3,720.70 (mins)
   DB Time:            3,021.65 (mins)

This report is for whopping 62 hours. This long report results in a meaningless data as everything is averaged out and one doesn't see the real issues.

With this information at hand I can say:

1) Mostly likely they haven't changed the default AWR snapshot duration (i.e. 1 hour)

2) I would then request the guys to generate hourly AWR reports during the time they were experiencing extreme performance issues.

3) Lastly, suggest them to change the snapshot duration from (default) 1 hour to 15 mintues.

Oracle Database 11g R2: Interactive Quick Reference

Oracle has released "Interactive Quick Reference" for DBA's and Developer's to better understand Oracle Database architecture. It includes

1) List of DBA Views,

2) Database Architecture Diagram, and

3) List of all Database Background Processes by category.

Click here to read more about it and click here to download it.

"enq: XR - database force logging" Wait Event

“enq: XR - database force logging” wait event is observed when you try to place the database in FORCE LOGGING mode while one of the database sessions is executing a NOLOGGING operation. This can be easily demonstrated.

Connect to database (say session 1) and perform a NOLOGGING operation:

SQL> conn test/test
Connected.
SQL>
SQL> 
SQL> create table t1 (id number, name varchar2(200)) NOLOGGING;
Table created.
SQL>
SQL> insert /*+ append */into t1 select level, rpad('*', 200, '*') from dual connect by level <= 5000000;

Place the database in the FORCE LOGGING mode by executing the following SQL from a different session (say session 2):

SQL> conn /as sysdba
Connected.
SQL>
SQL> 
SQL> alter database force logging;

You will observe that Session-2 does not complete immediately but rather waits. Let us see what session-2 is waiting on by connecting to the database (say session – 3):

SQL>
SQL> @wait

SID_SER_USER            EVENT                              STATUS   STATE
----------------------- ---------------------------------- -------- -------------------
159 -     3 - SYS       enq: XR - database force logging   ACTIVE   WAITING
146 -    82 - TEST      control file sequential read       ACTIVE   WAITED SHORT TIME

SQL> 

Session – 2 is actually waiting on “enq: XR - database force logging” wait event for Session – 1 to complete the NOLOGGING operation. As soon as Session – 1 completes the transaction, Session – 2 completes.

Here is the “wait.sql” used above to identify the wait events:

set line 10000
set pagesize 500

column Sid_Ser_User format a23
column event format a34

select
sid || ' - ' || lpad(serial#, 5, ' ') || ' - ' || username Sid_Ser_User,
event,
status,
state
from gv$session
where 1=1
and wait_class# <> '6'
and sid <> sys_context('USERENV', 'SID')
order by username;

AIOUG Webcast: RMAN Backup and Recovery

I will be talking on RMAN Recovery Procedures on 13 Oct, 2010 during my AIOUG Webcast. The webcast is scheduled at 05:00 PM IST.

One of the most important responsibilities of a DBA is to ensure that a database can be quickly and completely recovered from any type of failure. The aim of this session is to provide a platform to help DBAs to be able to restore databse under different failure scenarios.

In this session, I will discuss various database failure scenario's followed by a detailed recovery solution providing screenshots.

This presentation should be of a great value to the Novoice & Intermediate DBAs and a good refresher for an Advanced DBA.

See you at the webcast.

Oracle 11g Release 2 Patchset - 1 Available for Download

As you all know Oracle 11g Release 2 Patchset - 1 (11.2.02) is available for download for most of the platforms. The Patchset release has been blogged by many Oracle Enthusiasts and I am the last one to post it on my blog.

I should admit that I did not read body of any of the blog posts. I thought, the patch is out and everyone's blogging. Now, when I trackback and read Kevin Closson's Oracle Blog and Surachart Opun's Blog, I find an interested piece of information and here it is:

"In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g Release 2, patch sets are full installations that replace existing installations."

If I had paid attention to this I wouldn't have gone through the pain of downloading Oracle 11gR2 base release and Oracle 11gR2 Patch-1.

Happy patching!!!

RMAN: Backup & Recovery - The Most Essential but the Most Snubbed -- (OOW - 2010 Presentation)

I gave this presentation on Wednesday morning, September 22, 2010 in San Francisco at Oracle OpenWorld 2010. I followed a Problem-Solution approach for this session. I described the problem scenario and then discussed the recovery solution(s).
I have been receiving emails from the attendees to upload the presentation. Finally, I managed to upload the presentation and you may download it from here. If you face any issues downloading then please drop me an email (asif.momen@gmail.com).
The room was full of attendees and I am glad about the positive feedback I received from them. Below are the pics I took soon after the presenation.

Oracle OpenWorld-2010: Session Downloads

I received an email from Oracle Content Team saying the OOW-2010 sessions are now available for download from OpenWorld and JavaOne and Oracle Develop On Demand.

Login to the portal using your username/password and enjoy wealth of information.

Sessions are available in Flash Audio, MP3 Audio, PPT, and PDF format. I noticed few sessions tagged as "Coming Soon". Do check back your favorite session again.

I have received many email requests from the attendees of my session asking for a place to download my OOW presentation. You may download it from the above link. Below is an example screenshot:

Oracle Open World 2010 - Day 2

Day-2 of OOW was as exciting as Day-1. I wanted to start my day with Steven's session "PL/SQL Developer, Quiz Thyself!" but couldn't make it. However, I attended the following sessions:

1) Enterprise Cloud Computing: What, Why, and How

2) Oracle RAC 10g R2 to Oracle RAC 11g R2 Upgrade: Keeping It Simple

3) A Detailed Analysis of Indexing New Features in Oracle Database 11g R1 and R2 by Richard Foote

4) Oracle Real Application Clusters (Oracle RAC): Best Practices at AT&T

5) Oracle Database 11g Internals: Five Key High-Availability Innovations

The best session was that of Richard Foote.

Also, I won an "apron" at the exhibition hall. Yes you got it right, an apron. There was a game organized by Confio team. I won by throwing 2 out of 4 sand bags into the hole.

Oracle OpenWorld 2010 - Day 1

It was the first day of OOW-2010. I attended the following sessions:

1) Oracle Indexing Tips, Tricks, and Traps by Richard Foote

2) Tuning Oracle at the Block Level--Beginners, Go Away! by Rich Niemiec

3) Resolving the Free Buffer Waits Event by Craig Shallahammer

4) Oracle Recovery Manger (RMAN) 11g New Features in Release 1 and Release 2 by Michael

All the sessions were extremely good but if I had to choose the best from the above four, I would choose Craig's session. It was awesome. I actually felt myself inside the SGA, watching buffers moving and background/server processes working.

At the end of the day it was Oracle ACE dinner. I got to meet all the industry experts under a single roof. Iggy Fernandez then dropped me to my hotel.

I am too tired and got to take some rest as I got to start early tomorrow morning again.

Oracle OpenWorld Schedule – Update

Today I was informed by the Oracle Open World Content Team that they have changed the location of my presentation from "Rm 301" to "Rm 308". The detailed information about my session is:

ID#: S315820

Title: Oracle RMAN: Backup and Recovery--The Most Essential but the Most Snubbed

Abstract: A solid understanding of backup-and-recovery procedures is essential for every DBA, but unfortunately these basics are often ignored. According to Symantec, the average midsize company backs up only 60 percent of customer data and doesn't even do it consistently. This session provides comprehensive coverage of Oracle Recovery Manager (Oracle RMAN) backup-and-recovery abilities. It walks through various scenarios of database failures and discusses their solutions. It covers recovery procedures related to various important database files (parameter file, control file, different types of datafiles, and redo log files)

Track: Database

Date: 22-SEP-10

Time: 10:00 - 11:00

Venue: Moscone South

Room: Rm 308

I’m looking forward to seeing you in San Francisco!

I am an Oracle ACE

Few days back, I got an email from the Oracle ACE program committee that I am awarded the Oracle ACE status. Murali Vallath has nominated me for this award.

It’s a great honor to be recognized by Oracle at such high level. Thanks to Murali for nominating me for the ACE award and thanks to Oracle ACE Program Committee for considering me worthy enough for this award.

Here is my Oracle ACE profile and here is my updated OTN forum profile.

Finally, thanks to all of you who read this blog. I hope I can live up to being an ACE.

Still waiting for ACE shirt to arrive.

Restoring Standby Controlfile results in ORA-00600

I received ORA-00600 error while restoring Controlfile on a RAC-standby database (Physical). We purposely disabled redo log shipping on the Primary database and as a result the Standby Database was lagging way behind the production. Instead of applying all the Archive logs I took the shortest path of taking SCN based RMAN backup on the Primary and apply it to the Standby.

While doing so, I took a backup of Controlfile from primary database and shipped it to the standby site. I was able to successfully restore the controlfile as shown below:

RMAN> restore controlfile from '/tmp/control01.ctl';

Starting restore at 08-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1083 instance=qtprod1 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATADG/control01.ctl
output filename=+FRADG/control02.ctl
output filename=+DATADG/control03.ctl
Finished restore at 08-JUN-10

RMAN> exit

However, while mounting the database with this new controlfile, I was greeted with ORA-00600. Below is the error:

SQL> alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kccsga_update_ckpt_5], [1453], [0],
[62], [0], [], [], []

Searching for the above error in "Troubleshoot an ORA-600 or ORA-7445 Error Using the Error Lookup Tool" did not provide any information. :(

Looking back at the steps that lead me to ORA-00600 error, I found the solution to the problem. The controlfile should have been restored as a standby CF. After correcting the mistake database happily mounted.

RMAN> restore standby controlfile from '/tmp/control01.ctl';

Starting restore at 08-JUN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1082 instance=biodb1 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATADG/control01.ctl
output filename=+FRADG/control02.ctl
output filename=+DATADG/control03.ctl
Finished restore at 08-JUN-10

RMAN> exit

SQL> alter database mount standby database;

Database altered.

After applying the RMAN backup, standby database got in sync with the primary.

Moving From the Standard Edition to the Enterprise Edition

One of our customers wanted to implement Oracle Advanced Security (OAS) Option on their Oracle Database 11g. This database was a Standard Edition and OAS option is available only for Enterprise Edition. So, we have to upgrade the Standard Edition database to Enterprise Edition.

Following are the steps required to upgrade a Standard Edition database to Enterprise Edition:

1) Shutdown.

2) Remove Oracle Standard Edition software.

3) Install Oracle Enterprise Edition software.

4) Startup database

5) Run catalog.sql and catproc.sql

Note:

1) Backup your database before you begin.

2) Make sure that the release number of your Standard Edition and Enterprise Edition softwares is same.

Oracle documentation does not mention of running catalog and catproc scripts. However, Oracle Support Note does mention it. It seems to be a documentation bug.

References:

1) Oracle® Database Upgrade Guide 11g Release 2 (11.2)

2) Converting An Enterprise Edition Database To Standard Edition [ID 139642.1]

AIOUG Webcast: The Basics of Cost Based Optimization - Part II by Jonathan Lewis

Jonathan Lewis will be presenting part-2 of "The Basics of Cost Based Optimization" on 09-June-2010 at 14:00 GMT.

About the Presentation:

This presentation is a short introduction to the two key questions that drive the optimizer which are: How much data do I have to visit How widely scattered is that data We will look at a simple model to demonstrate the way that the optimzer "thinks" when choosing an execution path, and then explain why the optimizer sometimes chooses very silly execution paths.

For more information:

http://www.aioug.org/aioug_webcast.php

Make sure you capitalize on this opportunity!!!

"node1:6200 already configured" while Installing Oracle Clusterware 10g

Recently while installing Oracle Clusterware 10g, I stumbled upon the following error:

"camrac1:6200 already configured"

This error occurred when Oracle Universal Installer was configuring "Oracle Notification Server Configuration Assistant" and as usual OUI just stopped after displaying the error. If you come across this error then follow this guidelines:

[oracle@camrac1 bin]$cd $ORA_CRS_HOME/bin
[oracle@camrac1 bin]$ racgons remove_config camrac1:6200
racgons: Existing key value on camrac1 = 6200.
racgons: camrac1:6200 removed from OCR.
[oracle@camrac1 bin]$ racgons remove_config camrac2:6200
racgons: Existing key value on camrac2 = 6200.
racgons: camrac2:6200 removed from OCR.
[oracle@camrac1 bin]$ 

After removing the configuration retry the operation.

AIOUG Webcast: Top 5 RMAN Features from Oracle 11gR1 and 11gR2

Next webcast in the series is "Top 5 RMAN Features from Oracle 11gR1 and 11gR2" on 12-MAY-2010 at 09:00 AM (Indian Standard Time). Vinod Haval is the speaker for this webcast. He is working in Bank of America as Global Database Product Manager.

Vinod will be discussing on the following RMAN features:

1. Active DUPLICATE

2. Duplicate without connection to source database

3. Data Recovery Advisor

4. Automatic Block Repair (i.e. Auto-BMR with Active Data Guard Standby)

5. Expanded Compression Settings

Note: This webcast is only for AIOUG members.

To know more about upcoming AIOUG webcast's click here

Job: Senior Software Integration Engineer

We are looking for a "Senior Software Integration Engineer" available in SA.

The Senior Integration Software Engineer will be responsible for:

• Designs and Develops integration interface across several platforms like JAVA, .NET, C++.

• Technical communication between the company overseas software development teams and local customers

• Determines and/or define system specifications, input/output processes and working parameters for hardware/software compatibility

• Participates and Coordinates the design analysis of subsystems and integration with the overall system.

• Provides technical support during preparation, installation and maintenance of the complete system (HW and SW)

• Compose professional technical documentations and presentation for internal and external use

Skills

• Excellent Arabic & English Communication skills

• Minimum Actual Demonstrable 5 years of technical experience in:

• Windows Development using ASP.NET & C# (MCAD certified)

• Interoperability and Interfaces with other platforms such as JAVA

• SQL Server and MS IIS Administration and Programming

• Leading Participation in the complete Software Development Life Cycle

• Direct and advanced knowledge of Windows Server setup, support, maintenance and troubleshooting.

• Large-scale system fundamentals; Enterprise Server Hardware, Enterprise Desktop Hardware, Enterprise Storage.

• Understanding of general system design and project management processes.

• Problem solving, System diagnostics, Statistical measurements and analysis of systems.

• Good desktop skills (e.g. Word, Excel, Project, E-mail, Version control systems).

• Image manipulation suites (e.g. Adobe Photoshop, Paint Shop Pro, Visio).

• Residency Status: Must have a Saudi transferable Residency Permit (Iqama)

Experience in either, is a plus:

• Java Axis and IBM Websphere

• Biometrics Systems deployment

Anybody interested may send their CV to: asif.momen@gmail.com

Heavy Rain in Riyadh

Heavy rains disrupted life here in the city. Here are few pics:

Understanding SKIP_UNUSABLE_INDEXES Initialization Parameter

Recently there was question on Technet discussion forum where the OP complained that the Optimizer is ignoring Index for a particular query:

Need to understand why optimizer unable to use index in case of "OR" whenn we set one partition index to unusable, the same query with between uses index.

He created a partitioned table with a local index and one on the partitioned index was set to UNUSABLE state. Later, when a query is executed with Optimizer refuses to use the index.

Let’s create the table and the index:

SQL>  CREATE TABLE t (
  2     id NUMBER NOT NULL,
  3     d DATE NOT NULL,
  4     n NUMBER NOT NULL,
  5     pad VARCHAR2(4000) NOT NULL
  6   )
  7   PARTITION BY RANGE (d) (
  8     PARTITION t_jan_2009 VALUES LESS THAN (to_date('2009-02-01','yyyy-mm-dd')),
  9     PARTITION t_feb_2009 VALUES LESS THAN (to_date('2009-03-01','yyyy-mm-dd')),
 10     PARTITION t_mar_2009 VALUES LESS THAN (to_date('2009-04-01','yyyy-mm-dd')),
 11     PARTITION t_apr_2009 VALUES LESS THAN (to_date('2009-05-01','yyyy-mm-dd')),
 12     PARTITION t_may_2009 VALUES LESS THAN (to_date('2009-06-01','yyyy-mm-dd')),
 13     PARTITION t_jun_2009 VALUES LESS THAN (to_date('2009-07-01','yyyy-mm-dd')),
 14     PARTITION t_jul_2009 VALUES LESS THAN (to_date('2009-08-01','yyyy-mm-dd')),
 15     PARTITION t_aug_2009 VALUES LESS THAN (to_date('2009-09-01','yyyy-mm-dd')),
 16     PARTITION t_sep_2009 VALUES LESS THAN (to_date('2009-10-01','yyyy-mm-dd')),
 17     PARTITION t_oct_2009 VALUES LESS THAN (to_date('2009-11-01','yyyy-mm-dd')),
 18     PARTITION t_nov_2009 VALUES LESS THAN (to_date('2009-12-01','yyyy-mm-dd')),
 19     PARTITION t_dec_2009 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))
 20   );

Table created.

SQL>
SQL>  INSERT INTO t
  2   SELECT rownum, to_date('2009-01-01','yyyy-mm-dd')+rownum/274, mod(rownum,11), rpad('*',100,'*')
  3   FROM dual
  4   CONNECT BY level <= 100000;

100000 rows created.

SQL>
SQL>  CREATE INDEX i ON t (d) LOCAL;

Index created.

SQL>
SQL>  execute dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL>

Now mark the last index partition as UNUSABLE.

SQL> ALTER INDEX i MODIFY PARTITION t_dec_2009 UNUSABLE;

Index altered.

Now execute the query in question:

SQL> set autotrace traceonly exp
SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 1473098910

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   135   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE OR|      |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
|*  3 |    TABLE ACCESS FULL| T    |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"<=TO_DATE(' 2009-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
              "D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "D"<=TO_DATE('
              2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D">=TO_DATE(' 2009-02-02
              01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL>
SQL>

From the execution plan, it’s clear that the Optimizer is performing a FULL TABLE SCAN on the partitions in question (t_jan_2009 & t_feb_2009).

By default starting from Oracle 10g, SKIP_UNUSABLE_INDEXES initialization parameter is set to TRUE. This means, ignore index any index or index partition with UNUSABLE state. Turning this to FALSE enables index usage when possible. Index usage was ignored by the above query as one of the index partitions is in UNUSABLE state.

Let’s disable (FALSE) this parameter and run the same query:

SQL>
SQL> alter session set skip_unusable_indexes = false;

Session altered.

SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-01-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-01-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 3795917108

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     1 |     8 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE          |      |     1 |     8 |            |          |       |       |
|   2 |   CONCATENATION          |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     2 |     2 |
|   5 |    PARTITION RANGE SINGLE|      |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     INDEX RANGE SCAN     | I    |    13 |   104 |     2   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("D">=TO_DATE(' 2009-01-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
       filter(LNNVL("D"<=TO_DATE(' 2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
              LNNVL("D">=TO_DATE(' 2009-02-02 01:00:00', 'syyyy-mm-dd hh24:mi:ss')))

SQL>
SQL> set autotrace off
SQL>

There you go; Optimizer is now picking the index. As we have disabled SKIP_UNUSABLE_INDEXES parameter at the session level, all the index partitions except the one in the UNUSABLE state will be used by the optimizer when appropriate.

When we try to access the last partition we will receive an error as shown below as the index is in UNUSABLE state.

SQL> set autotrace traceonly exp
SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-12-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );
SELECT count(d)
*
ERROR at line 1:
ORA-01502: index 'TEST.I' or partition of such index is in unusable state

SQL>

Enabling SKIP_UNUSABLE_INDEXES will cause the query to complete successfully instead of failing.

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL>
SQL> SELECT count(d)
  2      FROM t
  3      WHERE
  4      (
  5      (d >= to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-12-01 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  6      or
  7      (d >= to_date('2009-02-02 01:00:00','yyyy-mm-dd hh24:mi:ss') and d <= to_date('2009-02-02 02:00:00','yyyy-mm-dd hh24:mi:ss'))
  8      );

Execution Plan
----------------------------------------------------------
Plan hash value: 1473098910

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |   135   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE OR|      |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
|*  3 |    TABLE ACCESS FULL| T    |    27 |   216 |   135   (1)| 00:00:02 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D">=TO_DATE(' 2009-12-01 23:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "D"<=TO_DATE(' 2009-12-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR "D"<=TO_DATE('
              2009-02-02 02:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D">=TO_DATE(' 2009-02-02
              01:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL>

If you look at the 10053 trace when SKIP_UNUSABLE_INDEXES is set to TRUE, you will notice that the optimizer tends to ignore index access path and simply reports that the index is in UNUSABLE state. Below is an excerpt of 10053 trace.

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T  (Using composite stats)
  (making adjustments for partition skews)
  ORIGINAL VALUES::    #Rows: 100000  #Blks:  2928  AvgRowLen:  116.00
  PARTITIONS::
  PRUNED: 2
  ANALYZED: 2  UNANALYZED: 0
    #Rows: 100000  #Blks:  488  AvgRowLen:  116.00
Index Stats::
  Index: I  Col#: 2
    USING COMPOSITE STATS
    LVLS: 1  #LB: 270  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 1696.00
    UNUSABLE
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): D(DATE)
    AvgLen: 8.00 NDV: 100000 Nulls: 0 Density: 1.0000e-005 Min: 2454833 Max: 2455198
  Table:  T  Alias: T     
    Card: Original: 100000  Rounded: 27  Computed: 26.84  Non Adjusted: 26.84
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  135.09  Resp: 135.09  Degree: 0
      Cost_io: 134.00  Cost_cpu: 8050409
      Resp_io: 134.00  Resp_cpu: 8050409
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 135.09  Degree: 1  Resp: 135.09  Card: 26.84  Bytes: 0

Oracle is unable to locate control file (ORA-00205) when mounting database

I was testing RMAN restore scenario on one of the test databases and I came across “ORA-00205” error. It seemed very strange why Oracle is not able to find my control files when they are lying there.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      C:\DBTEST\ORADATA\CONTROL01.CTL,C:\DBTEST\ORADATA\CONTROL02.CTL
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL> 

To double check what I am seeing in correct, I ran OS command and it confirmed that the files do exist.

C:\>dir C:\DBTEST\ORADATA\CONTROL01.CTL
 Volume in drive C has no label.
 Volume Serial Number is D0FB-F138

 Directory of C:\DBTEST\ORADATA

12/26/2009  01:36 PM         7,061,504 CONTROL01.CTL
               1 File(s)      7,061,504 bytes
               0 Dir(s)  36,819,836,928 bytes free

C:\>dir C:\DBTEST\ORADATA\CONTROL02.CTL
 Volume in drive C has no label.
 Volume Serial Number is D0FB-F138

 Directory of C:\DBTEST\ORADATA

12/26/2009  01:36 PM         7,061,504 CONTROL02.CTL
               1 File(s)      7,061,504 bytes
               0 Dir(s)  36,819,836,928 bytes free

C:\>

Then what’s wrong? Alert log is always handy to drill down for details and even this time it was helpful. Following is the excerpt from the alert log.

ORA-00202: control file: 'C:\DBTEST\ORADATA\CONTROL01.CTL,C:\DBTEST\ORADATA\CONTROL02.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 123) The filename, directory name, or volume label syntax is incorrect.

If you notice, Oracle is trying to locate one control file and is failing to identify it and alas failing. It was a small mistake where I missed to separate the two files with a comma. After the correction, Oracle happily mounted my test database.

SQL> alter system set control_files='C:\DBTEST\oradata\control01.ctl','C:\DBTEST\oradata\control02.c
tl' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size              96469648 bytes
Database Buffers          192937984 bytes
Redo Buffers                2945024 bytes
SQL> alter database mount;

Database altered.

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      C:\DBTEST\ORADATA\CONTROL01.CT
                                                 L, C:\DBTEST\ORADATA\CONTROL02
                                                 .CTL
SQL>