Cardinality Analysis - A Review

After reading Michelle Deng’s “Tuning by Cardinality Feedback” paper on Jonathan's blog, I thought of re-creating the whole scenario to actually feel what is going on.

I did this test on Oracle 10g Release 2 (10.2.0.3) database with a block size of 16K. We first create the table and populate it in such a way that we match exactly with Michelle’s data.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

Elapsed: 00:00:00.03
SQL>

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
db_block_size                        integer     16384
SQL>

I performed couple of tests with different data distributions and here’s the first test case:

Test Case 1

Create and Populate Data

drop table prod_fctr_mv purge;

create table prod_fctr_mv(
 extract_mkt_ssk varchar2(5), 
 mkt_ssk     varchar2(20), 
 mkt_id      number(16), 
 hier_type    varchar2(20), 
 txt       varchar2(500));

insert into prod_fctr_mv 
 select '00006', '00006', 3, 'CORP', 
        rpad('*', 285, '*') 
   from dual 
  connect by level <= 42;


insert into prod_fctr_mv
  select
         lpad(mod(level, 100), 5, '0') extract_mkt_ssk ,
         lpad(mod(level, 73), 5, '0') mkt_ssk ,
         mod(level, 91) mkt_id ,
         decode(mod(level, 5), 0, 'CORP', 1, 'CALL', 2, 'CORP', 
                               3, 'WALL', 'CORP') hier_type ,
         rpad('*', 285, '*')
    from dual
   connect by level <= 4965 - 42 ;


update prod_fctr_mv set mkt_ssk = null 
 where mkt_ssk <> '00006' 
   and rownum <= 296;

update (select mkt_id 
          from prod_fctr_mv 
         where mkt_id <> 3) 
   set mkt_id = 3
 where rownum <= 72;

update (select extract_mkt_ssk  
          from prod_fctr_mv 
         where extract_mkt_ssk <> '00006'
           and mkt_id <> 3) 
   set extract_mkt_ssk = '00006' 
 where rownum <= 77;

select count(*) 
  from prod_fctr_mv
 where mkt_ssk  = '00006' ;

update (select mkt_ssk 
          from prod_fctr_mv 
         where mkt_ssk <> '00006' 
           and mkt_ssk is not null ) 
   set mkt_ssk = '00006' 
 where rownum <= 59;

update (select hier_type 
          from prod_fctr_mv 
         where hier_type = 'CORP' 
           and mkt_id <> 3
           and extract_mkt_ssk <> '00006')
   set hier_type = decode(mod(rownum, 2), 0, 'TALL', 'BALL') 
 where rownum <= 554;

commit;

Verify Data

Now that the data is populated, let’s see did we get on the right track.

SQL> exec dbms_stats.gather_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.42
SQL> 
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, 
  2         chain_cnt, avg_row_len
  3   from user_tables
  4  where table_name = 'PROD_FCTR_MV';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
PROD_FCTR_MV                         4965        108            0          0          0         305

Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> select column_name, num_distinct, density, num_buckets, 
  2         num_nulls, histogram
  3    from user_tab_columns
  4   where table_name = 'PROD_FCTR_MV';

COLUMN_NAME                    NUM_DISTINCT    DENSITY NUM_BUCKETS  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------- ---------------
TXT                                       1          1           1          0 NONE
HIER_TYPE                                 5 .000100705           5          0 FREQUENCY
MKT_ID                                   91 .000100705          91          0 FREQUENCY
MKT_SSK                                  73 .000107089          73        296 FREQUENCY
EXTRACT_MKT_SSK                         100 .000100705         100          0 FREQUENCY

Elapsed: 00:00:00.07
SQL> 
SQL> 
SQL> select count(decode(mkt_id, 3, 1)) mkt_id,
  2         count(decode(extract_mkt_ssk, '00006', 1)) extract_mkt_ssk,
  3         count(decode(mkt_ssk, '00006', 1)) mkt_ssk,
  4         count(decode(hier_type, 'CORP', 1)) hier_type
  5    from prod_fctr_mv ;

    MKT_ID EXTRACT_MKT_SSK    MKT_SSK  HIER_TYPE
---------- --------------- ---------- ----------
       169             169        169       2441

Elapsed: 00:00:00.01
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';

  COUNT(*)
----------
        42

Elapsed: 00:00:00.03
SQL> 
SQL>

Search an optimal value for OPTIMIZER_DYNAMIC_SAMPLING parameter

Yea, the total number of rows, the count for the key columns, count for the query in question, and the statistics, they all match to what was presented by Michelle.

The real fun starts here:

SQL> set autotrace traceonly exp
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    20 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |     1 |    20 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Delete the statistics and run the same query:

SQL> exec dbms_stats.delete_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Note
-----
   - dynamic sampling used for this statement

SQL>

Optimizer has considered dynamic sampling but still the number of rows reported is now “72”, which is nearly twice than the actual number of rows. Let’s gradually increase the value of OPTIMIZER_DYNAMIC_SAMPLING initialization parameter and check the plan.

SQL> alter session set optimizer_dynamic_sampling=3;

Session altered.

Elapsed: 00:00:00.00
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Note
-----
   - dynamic sampling used for this statement

SQL> alter session set optimizer_dynamic_sampling=4;

Session altered.

Elapsed: 00:00:00.00
SQL>  @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Note
-----
   - dynamic sampling used for this statement

SQL> alter session set optimizer_dynamic_sampling=5;

Session altered.

Elapsed: 00:00:00.00
SQL>   @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Note
-----
   - dynamic sampling used for this statement

SQL> alter session set optimizer_dynamic_sampling=6;

Session altered.

Elapsed: 00:00:00.01
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    42 |  1722 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Note
-----
   - dynamic sampling used for this statement

SQL>
SQL> set autotrace off
SQL>

Finally, when OPTIMIZER_DYNAMIC_SAMPLING parameter reached to a value of “6”, the plan is now reporting correct cardinality.

Test Case 2

Let’s run through the entire test again with a slight modification. Instead of inserting all the 42 rows together, we will insert them scattered in between.

Create and Populate Data

In this test case, we will insert one row of our interest after every 100 rows. So, here goes the script:

drop table prod_fctr_mv purge;

create table prod_fctr_mv(
 extract_mkt_ssk varchar2(5), 
 mkt_ssk     varchar2(20), 
 mkt_id      number(16), 
 hier_type    varchar2(20), 
 txt       varchar2(500));

begin
  for i in 1..42 loop
    insert into prod_fctr_mv 
     select '00006', '00006', 3, 'CORP', 
            rpad('*', 285, '*') 
       from dual 
      connect by level <= 1;

    insert into prod_fctr_mv
      select
            lpad(mod(level, 100), 5, '0') extract_mkt_ssk ,
            lpad(mod(level, 73), 5, '0') mkt_ssk ,
            mod(level, 91) mkt_id ,
            decode(mod(level, 5), 0, 'CORP', 1, 'CALL', 2, 'CORP', 
                                  3, 'WALL', 'CORP') hier_type ,
            rpad('*', 285, '*')
       from dual
      connect by level <= 100 ;
  end loop;

  commit;
end;
/

select count(*) 
  from prod_fctr_mv;

  insert into prod_fctr_mv
    select
          lpad(mod(level, 100), 5, '0') extract_mkt_ssk ,
          lpad(mod(level, 73), 5, '0') mkt_ssk ,
          mod(level, 91) mkt_id ,
          decode(mod(level, 5), 0, 'CORP', 1, 'CALL', 2, 'CORP', 
                                3, 'WALL', 'CORP') hier_type ,
          rpad('*', 285, '*')
     from dual
    connect by level <= 723 ;

update prod_fctr_mv set mkt_ssk = null 
 where mkt_ssk <> '00006' 
   and rownum <= 296;

update (select mkt_id 
          from prod_fctr_mv 
         where mkt_id <> 3
           and extract_mkt_ssk <> '00006') 
   set mkt_id = 3
 where rownum <= 35;

update (select extract_mkt_ssk  
          from prod_fctr_mv 
         where extract_mkt_ssk <> '00006'
           and mkt_id <> 3) 
   set extract_mkt_ssk = '00006' 
 where rownum <= 77;

update (select mkt_ssk 
          from prod_fctr_mv 
         where mkt_ssk <> '00006' 
           and mkt_ssk is not null ) 
   set mkt_ssk = '00006' 
 where rownum <= 33;

update (select hier_type 
          from prod_fctr_mv 
         where hier_type = 'CORP' 
           and mkt_id <> 3
           and extract_mkt_ssk <> '00006')
   set hier_type = decode(mod(rownum, 2), 0, 'TALL', 'BALL') 
 where rownum <= 554;

commit;

Verify Data

Let us run through the same queries to verify data population.

SQL> exec dbms_stats.gather_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.09
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, 
  2         chain_cnt, avg_row_len
  3   from user_tables
  4  where table_name = 'PROD_FCTR_MV';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
PROD_FCTR_MV                         4965        108            0          0          0         305

Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> select column_name, num_distinct, density, num_buckets, 
  2         num_nulls, histogram
  3    from user_tab_columns
  4   where table_name = 'PROD_FCTR_MV';

COLUMN_NAME                    NUM_DISTINCT    DENSITY NUM_BUCKETS  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------- ---------------
TXT                                       1          1           1          0 NONE
HIER_TYPE                                 5 .000100705           5          0 FREQUENCY
MKT_ID                                   91 .000100705          91          0 FREQUENCY
MKT_SSK                                  73 .000107089          73        296 FREQUENCY
EXTRACT_MKT_SSK                         100 .000100705         100          0 FREQUENCY

Elapsed: 00:00:00.03
SQL> 
SQL> select count(decode(mkt_id, 3, 1)) mkt_id,
  2         count(decode(extract_mkt_ssk, '00006', 1)) extract_mkt_ssk,
  3         count(decode(mkt_ssk, '00006', 1)) mkt_ssk,
  4         count(decode(hier_type, 'CORP', 1)) hier_type
  5    from prod_fctr_mv ;

    MKT_ID EXTRACT_MKT_SSK    MKT_SSK  HIER_TYPE
---------- --------------- ---------- ----------
       169             169        169       2441

Elapsed: 00:00:00.01
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';

  COUNT(*)
----------
        42

Elapsed: 00:00:00.01
SQL>

Search an optimal value for OPTIMIZER_DYNAMIC_SAMPLING parameter

We managed to get the number of records and other statistics same as that of the original document. Now, we will hunt for the optimal value of OPTIMIZER_DYNAMIC_SAMPLING parameter.

SQL> set autotrace traceonly exp
SQL> 
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    20 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |     1 |    20 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

SQL> exec dbms_stats.delete_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
SQL> 
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    36 |  1476 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Note
-----
   - dynamic sampling used for this statement

SQL>

We get a better cardinality reported by the Optimizer at the default level of dynamic sampling (OPTIMIZER_DYNAMIC_SAMPLING=2).

Conclusion

In the above test cases the Oracle optimizer started displaying correct number of rows when OPTIMIZER_DYNAMIC_SAMPLING was set to 6 with interested data being inserted together while the default setting for OPTIMIZER_DYNAMIC_SAMPLING worked when data was almost equally distributed.

Michelle’s data distribution might be such that the Optimizer was able report correct number of rows at OPTIMIZER_DYNAMIC_SAMPLING=4.

Lessons learned include:

1) Column correlation could really mislead Optimizer, and

2) Data distribution plays equally critical role.

ORA-07445 When Querying V$SQL_PLAN View

On one of our production databases, I was looking for a particular index usage as how often was it used and at what times it was used. To achieve this, I did a little mining on AWR repository tables and found out that it was used 11 times in the last one month (AWR retention period).

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.01
SQL>


SQL> select sp.sql_id,
  2         sp.options,
  3         count(1) cnt
  4    from dba_hist_sql_plan sp,
  5         dba_hist_sqlstat ss
  6    where sp.sql_id = ss.sql_id
  7      and sp.object_owner  =  'PAYMAST' 
  8      and sp.operation like '%INDEX%'
  9      and sp.object_name = 'IDX_COMMIT_DET'
 10  group by sp.sql_id, 
 11           sp.options
 12  order by sp.sql_id, 
 13           sp.options;

SQL_ID        OPTIONS                               CNT
------------- ------------------------------ ----------
1b4xf87ntvfgn SAMPLE FAST FULL SCAN                   1
8qaj7c6wqcyvg SAMPLE FAST FULL SCAN                   7
9jcrv4kunuhg4 SAMPLE FAST FULL SCAN                   1
afzq942kp848t RANGE SCAN                              1
ar6c0r4s4kntp RANGE SCAN                              1

Elapsed: 00:00:00.54
SQL>          

However, querying V$SQL_PLAN view for the same index threw ORA-03113 error. Hmm, I connected to the database and ran the same query again and it resulted in the same error.

SQL> select * from V$SQL_PLAN where  operation  ='INDEX' and object_name ='IDX_COMMIT_DET';

ADDRESS          HASH_VALUE SQL_ID        PLAN_HASH_VALUE CHILD_ADDRESS    CHILD_NUMBER TIMESTAMP       
---------------- ---------- ------------- --------------- ---------------- ------------ ------------
FILTER_PREDICATES                                                                                       
----------------------------------------------------------------------------------------------------
REMARKS                                                                                                 
----------------------------------------------------------------------------------------------------
07000000D98392B0 2773750041 afzq942kp848t       380836487 07000000D986D420            0 15-APR2009 12:2
"VOU_TYPE_NAME"=:B1                                                                                     


ERROR:
ORA-03113: end-of-file on communication channel



ERROR:
ORA-03114: not connected to ORACLE


Elapsed: 00:00:03.82
SQL>

Oracle has bumped some information into the alert log and here it is:

Errors in file /dbdata1/oradba/admin/PAYPROD/udump/payprod_ora_6488244.trc:
ORA-07445: exception encountered: core dump [msqsub+0008] [SIGSEGV] [Address not mapped to object] [0x35B000000000070] [] []

and it created a 18MB trace file in the USER_DUMP_DEST which has all the gory details for the Oracle Support to work on. Below is an excerpt from the trace file:

*** 2009-04-15 12:27:46.646
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [msqsub+0008] [SIGSEGV] [Address not mapped to object] [0x35B000000000070] [] []
Current SQL statement for this session:
select * from V$SQL_PLAN where  operation  ='INDEX' and object_name ='IDX_COMMIT_DET'

The ORA-00600/ORA-07445 Lookup Tool in Oracle Metalink reports “A description for this ORA-07445 error is not yet available”.

I have to end up by lodging an SR and wait for Oracle Support’s response.

Strange Data Guard Issue

We have recently failed over one of our database to the DRC. At that time the production archivelog sequence was 80,000 plus. After staying there for couple of hours we recreated a physical standby database on the primary and switched back to original primary location. Everything went fine without any hoo-ha.

The failover and switchover took place on February 20, 2009. We have an automated job on all our standby databases to check whether the Standby is lagging behind the Primary, if so, then it automatically restores the missing archivelogs and applies them.

This job was working fine until day before yesterday (March 15, 2009) but started reporting errors thereafter. It failed with “RMAN-20242” error.

Starting restore at 15-MAR-09
released channel: ch12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2009 07:26:59
RMAN-06004: ORACLE error from recovery catalog database: 
RMAN-20242: specification does not match any archive log in the recovery catalog

When notified, I queried V$ARCHIVE_GAP and the output of this query took me to a surprise:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL>
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1           776          82468

SQL> 

Well, all of a sudden Oracle thinks that it is 81 thousand archive logs behind the primary. I tried to switching couple of archive logs on the production and the gap was still intact. I don't really know from where did Oracle took this information.

The automated job was trying to restore archives between 776 and 82468 and was kicked out by the recovery catalog database saying, there are no such archive logs (in fact there exist no such archivelogs).

I created a standby controlfile on the production database and replaced the controlfile on the standby database with the new one. The standby database seems to be happy with this version of controlfile and have stopped reporting any gap and the automated job is also leading a happy life.

SQL> select * from v$archive_gap;

no rows selected

SQL>