Thursday, August 14, 2014

Non critical error ORA-48913 caught while writing to trace file "/oracle/saptrace/diag/rdbms/XXX/trace/XXX_lgwr_36503556.trc" Error message: ORA-48913: Writing into trace file failed, file size limit [10240000] reached End of report

Oracle 11g Recommendations are  below.

SQL> show parameter max_dump_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 20000
SQL> alter system set max_dump_file_size=UNLIMITED scope=both;

System altered.

SQL> show parameter max_dump_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string UNLIMITED

Wednesday, August 13, 2014

Statistics In Oracle

In this post I'll try to summarize all sorts of statistics in Oracle, I strongly recommend reading the full article, as it contains information you may find it valuable in understanding Oracle statistics.

################################
Database | Schema | Table | Index Statistics
################################

Gather Database Stats:
===================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
     ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',
     CASCADE => TRUE,
     degree => 4,
     OPTIONS => 'GATHER STALE',
     GATHER_SYS => TRUE,
     STATTAB => PROD_STATS);

CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collected or not.
DEGREE => 4 :Degree of parallelism.
options: 
       =>'GATHER' :Gathers statistics on all objects in the schema.
       =>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to gather those statistics.
       =>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
       =>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
        =>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
        =>'LIST STALE': Returns a list of stale objects as determined by looking at the *_tab_modifications views.
        =>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT STATISTICS section -last third in this post-.

Note: All above parameters are valid for all stats kind (schema,table,..) except Gather_SYS.

For faster execution:
------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
     ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);

What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not recommended to be gathered on all columns.
Removed  "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled the "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU overhead during gathering DB statistics.

This task became an automated task starting with 10g to gathers statistics on all objects in the database having stale or missing statistics, To check the status of that task:
SQL> select status from dba_autotask_client where client_name = 'auto optimizer stats collection';

To Enable automatic optimizer statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection', 
     operation => NULL, 
     window_name => NULL);
     END;
     /

In case you want to Disable automatic optimizer statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.DISABLE(
     client_name => 'auto optimizer stats collection', 
     operation => NULL, 
     window_name => NULL);
     END;
     /

To check the tables that have stale statistics:

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';



Gather SCHEMA Stats:
=====================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
     ownname =>'SCOTT',
     estimate_percent=>10,
     degree=>1,
     cascade=>TRUE,
     options=>'GATHER STALE');


Gather TABLE Stats:
===================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'SCOTT',
     tabname => 'EMP',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 2:  Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=>  :  For gathering Histograms:
 FOR COLUMNS SIZE AUTO :  You can specify one column between "" instead of all columns.
 FOR ALL COLUMNS SIZE REPEAT :  Prevent deletion of histograms and collect it only for columns already have histograms.
 FOR ALL COLUMNS  :  Collect histograms on all columns.
 FOR ALL COLUMNS SIZE SKEWONLY :  Collect histograms for columns have skewed value should test skewness first>.
 FOR ALL INDEXED COLUMNS :  Collect histograms for columns have indexes only.


Note: Truncating a table will not update table statistics, it will only reset the High Water Mark, you've to re-gather statistics on that table.

Inside "DBA BUNDLE", there is a script called "gather_stats.sh", it will help you easily & safely gather statistics on specific schema or table plus providing advanced features such as backing up/ restore new statistics in case of fallback.
To learn more about "DBA BUNDLE" please visit this post:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html


Gather Index Stats:
==================
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',indname => 'EMP_I',
     estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);

###################
Fixed OBJECTS Statistics
###################

What are Fixed objects:
---------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.

How frequent to gather stats on fixed objects?
------------------------------------------
Only one time for a representative workload unless you've one of these cases:

- After a major database or application upgrade.
- After implementing a new module.
- After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
- Poor performance/Hang encountered while querying dynamic views e.g. V$ views.


Note:
- It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
- Also note that performance degradation may be experienced while the statistics are gathering.
- Having no statistics is better than having a non representative statistics.

How to gather stats on fixed objects:
----------------------------------

First Check the last analyzed date:
---- --------------------------
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED
        from dba_tab_statistics where table_name='X$KGLDP'; 
Second Export the current fixed stats in a table: (in case you need to revert back)
------- -----------------------------------
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE
        ('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');

SQL> EXEC dbms_stats.export_fixed_objects_stats
        (stattab=>'STATS_TABLE_NAME',statown=>'OWNER'); 
Third Gather the fixed objects stats:
------  ------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats; 

Note:
In case you experienced a bad performance on fixed tables after gathering the new statistics:

SQL> exec dbms_stats.delete_fixed_objects_stats(); 
SQL> exec DBMS_STATS.import_fixed_objects_stats
        (stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER'); 


#################
SYSTEM STATISTICS
#################

What is system statistics:
-----------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.

Why gathering system statistics:
------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.

There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):

NOWORKLOAD statistics:
--------------------------
This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats(); 

WORKLOAD statistics:
----------------------
This will gather statistics during the current workload [which supposed to be representative of actual system I/O and CPU workload on the DB].
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start'); 
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop'); 
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60); 

Check the system values collected:
-------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$; 

cpuspeedNW:  Shows the noworkload CPU speed, (average number of CPU cycles per second).
ioseektim:    The sum of seek time, latency time, and OS overhead time.
iotfrspeed:  I/O transfer speed,tells optimizer how fast the DB can read data in a single read request.
cpuspeed:      Stands for CPU speed during a workload statistics collection.
maxthr:          The maximum I/O throughput.
slavethr:      Average parallel slave I/O throughput.
sreadtim:     The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim:     The average time (seconds) for a sequential multiblock read.
mbrc:             The average multiblock read count in blocks.

Notes:
-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW, ioseektim, iotfrspeed) system statistics only.
-Above values can be modified manually using DBMS_STATS.SET_SYSTEM_STATS procedure.
-According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.

Delete system statistics:
---------------------
SQL> execute dbms_stats.delete_system_stats(); 


####################
Data Dictionary Statistics
####################

Facts:
-----
> Dictionary tables are the tables owned by SYS and residing in the system tablespace.
> Normally data dictionary statistics in 9i is not required unless performance issues are detected.
> In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.

If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:

SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');  

When to gather Dictionary statistics:
---------------------------------
-After DB upgrades.
-After creation of a new big schema.
-Before and after big datapump operations.

Check last Dictionary statistics date:
----------------------------------
SQL> select table_name, last_analyzed from dba_tables
     where owner='SYS' and table_name like '%$' order by 2; 

Gather Dictionary Statistics:  
-------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(gather_sys=>TRUE);
->Will gather stats on the whole DB+SYS schema.



#############
Extended Statistics "11g onwards"
#############

Extended statistics can be gathered on columns based on functions or column groups.

Gather extended stats on column function:
=====================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott'; 

In order to make optimizer work with function based terms you need to gather extended stats:

1-Create extended stats:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;

2-Gather histograms:
>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP', method_opt=> 'for all columns size skewonly');

OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> Begin dbms_stats.gather_table_stats
     (ownname => 'SCOTT',tabname => 'EMP',
     method_opt => 'for all columns size skewonly for
     columns (lower(ENAME))');
     end;
     /

To check the Existance of extended statistics on a table:
---------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB  (LOWER("ENAME"))

Drop extended stats on column function:
-------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');

Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statement  are correlated e.g.(country,state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each columns. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in USA so the value of state_name are always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement  with the column group the optimizer will make use of column group statistics.

1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)')from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers',method_opt=> 'for all columns size skewonly');

OR
---

*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> Begin dbms_stats.gather_table_stats
     (ownname => 'SH',tabname => 'CUSTOMERS',
     method_opt => 'for all columns size skewonly for
     columns (country_id,cust_state_province)');
     end; 
     /

Drop extended stats on column group:
--------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');


########
Histograms
########

What are Histograms?
-----------------------
> Holds data about values within a column in a table for number of occurrences for a specific value/range.
> Used by CBO to optimize a query to use whatever index Fast Full scan or table full scan.
> Usually being used against columns have data being repeated frequently like country or city column.
> gathering histograms on a column having distinct values (PK) is useless because values are not repeated.
> Two types of Histograms can be gathered:
  -Frequency histograms: is when distinct values (buckets) in the column is less than 255 (e.g. the number of countries is always less than 254).
  -Height balanced histograms: are similar to frequency histograms in their design, but distinct values  > 254
    See an Example: http://aseriesoftubes.com/articles/beauty-and-it/quick-guide-to-oracle-histograms
> Collected by DBMS_STATS (which by default doesn't collect histograms, it deletes them if you didn't use the parameter).
> Mainly being gathered on foreign key columns/columns in WHERE statement.
> Help in SQL multi-table joins.
> Column histograms like statistics are being stored in data dictionary.
> If application exclusively uses bind variables, Oracle recommends deleting any existing histograms and disabling Oracle histograms generation.

Cautions:
   – Do not create them on Columns that are not being queried.
   – Do not create them on every column of every table.
   – Do not create them on the primary key column of a table.

Verify the existence of histograms:
--------------------------------------
SQL> select column_name,histogram from dba_tab_col_statistics
     where owner='SCOTT' and table_name='EMP'; 

Creating Histograms:
-----------------------
e.g.
SQL> Exec dbms_stats.gather_schema_stats
     (ownname => 'SCOTT',
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt => 'for all columns size auto',
     degree => 7); 


method_opt:
FOR COLUMNS SIZE AUTO                 => Fastest. you can specify one column instead of all columns.
FOR ALL COLUMNS SIZE REPEAT     => Prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS => collect histograms on all columns .
FOR ALL COLUMNS SIZE SKEWONLY => collect histograms for columns have skewed value .
FOR ALL INDEXES COLUMNS      => collect histograms for columns have indexes.

Note: AUTO & SKEWONLY will let Oracle decide to create the Histograms or not.

Check the existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms
     where OWNER='SCOTT' table_name='EMP' group by column_name; 

Drop Histograms: 11g
------------------
e.g.
SQL> Exec dbms_stats.delete_column_stats
     (ownname=>'SH', tabname=>'SALES',
     colname=>'PROD_ID', col_stat_type=> HISTOGRAM); 


Stop gather Histograms: 11g
----------------------
[This will change the default table options]
e.g.
SQL> Exec dbms_stats.set_table_prefs
     ('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS SIZE 1 PROD_ID');
>Will continue to collect histograms as usual on all columns in the SALES table except for PROD_ID column.

Drop Histograms: 10g
------------------
e.g.
SQL> exec dbms_stats.delete_column_stats(user,'T','USERNAME');


##################################
Save/IMPORT & RESTORE STATISTICS:
##################################
===================
Export /Import Statistics:
===================
In this way statistics will be exported into table then imported later from that table.

1-Create STATS TABLE:
-  -------------------------
SQL> Exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'prod_stats',tblspace => 'USERS'); 

2-Export the statistics to the STATS table:
---------------------------------------------
For Database stats:
SQL> Exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Schema stas:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA','STATS_TABLE',NULL,'STATS_TABLE_OWNER');
For Table: 
SQL> Conn scott/tiger
SQL> Exec dbms_stats.export_TABLE_stats(ownname => 'SCOTT',tabname => 'EMP',stattab => 'prod_stats');
For Index:
SQL> Exec dbms_stats.export_INDEX_stats(ownname => 'SCOTT',indname => 'PK_EMP',stattab => 'prod_stats');
For Column:
SQL> Exec dbms_stats.export_COLUMN_stats (ownname=>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');

3-Import the statistics from PROD_STATS table to the dictionary:
--------------------------------------------------------------------
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For Schema stats:
SQL> Exec DBMS_STATS.IMPORT_SCHEMA_STATS
     (ownname => 'SCOTT',stattab => 'prod_stats', statown => 'SYSTEM');
For Table stats and it's indexes: 
SQL> Exec dbms_stats.import_TABLE_stats
     ( ownname => 'SCOTT', stattab => 'prod_stats',tabname => 'EMP');
For Index:
SQL> Exec dbms_stats.import_INDEX_stats
     ( ownname => 'SCOTT', stattab => 'prod_stats', indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.import_COLUMN_stats
     (ownname=>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');

4-Drop Stat Table:
-------------------
SQL> Exec dbms_stats.DROP_STAT_TABLE (stattab => 'prod_stats',ownname => 'SYSTEM');

===============
Restore statistics: -From Dictionary-
===============
Old statistics are saved automatically in SYSAUX for 31 day.

Restore Dictionary stats as of timestamp:
-----------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DICTIONARY_STATS(sysdate-1); 

Restore Database stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DATABASE_STATS(sysdate-1); 

Restore SYSTEM stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_SYSTEM_STATS(sysdate-1); 

Restore FIXED OBJECTS stats as of timestamp:
-----------------------------------------------
SQL> Exec DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(sysdate-1); 

Restore SCHEMA stats as of timestamp:
---------------------------------------
SQL> Exec dbms_stats.restore_SCHEMA_stats
     (ownname=>'SYSADM',AS_OF_TIMESTAMP=>sysdate-1); 
OR:
SQL> Exec dbms_stats.restore_schema_stats
     (ownname=>'SYSADM',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');

Restore Table stats as of timestamp:
-----------------------------------
SQL> Exec DBMS_STATS.RESTORE_TABLE_STATS
     (ownname=>'SYSADM', tabname=>'T01POHEAD',AS_OF_TIMESTAMP=>sysdate-1);
=========
Advanced:
=========

To Check current Stats history retention period (days):
-------------------------------------------------
SQL> select dbms_stats.get_stats_history_retention from dual;
SQL> select dbms_stats.get_stats_history_availability from dual;
To modify current Stats history retention period (days):
-------------------------------------------------
SQL> Exec dbms_stats.alter_stats_history_retention(60); 

Purge statistics older than 10 days:
------------------------------
SQL> Exec DBMS_STATS.PURGE_STATS(SYSDATE-10); 

Procedure To claim space after purging statstics:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Space will not be claimed automatically when you purge stats, you must claim it manually using this procedure:

Check Stats tables size:
>>>>>>
        col Mb form 9,999,999
        col SEGMENT_NAME form a40
        col SEGMENT_TYPE form a6
        set lines 120
        select sum(bytes/1024/1024) Mb,
        segment_name,segment_type from dba_segments
         where  tablespace_name = 'SYSAUX'
        and segment_name like 'WRI$_OPTSTAT%'
        and segment_type='TABLE'
        group by segment_name,segment_type order by 1 asc
        /

Check Stats indexes size:
>>>>>
        col Mb form 9,999,999
        col SEGMENT_NAME form a40
        col SEGMENT_TYPE form a6
        set lines 120
        select sum(bytes/1024/1024) Mb, segment_name,segment_type
        from dba_segments
        where  tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%'
        and segment_type='INDEX'
        group by segment_name,segment_type order by 1 asc
        / 
Move Stats tables in same tablespace:
>>>>>
        select 'alter table '||segment_name||'  move tablespace
        SYSAUX;' from dba_segments
        where tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%' and segment_type='TABLE'
        / 
Rebuild stats indexes:
>>>>>>
        select 'alter index '||segment_name||'  rebuild online;'
        from dba_segments where tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%' and segment_type='INDEX'
        /

Check for un-usable indexes:
>>>>>
        select  di.index_name,di.index_type,di.status  from
        dba_indexes di , dba_tables dt
        where  di.tablespace_name = 'SYSAUX'
        and dt.table_name = di.table_name
        and di.table_name like '%OPT%'
        order by 1 asc
        / 

Delete Statistics:
===============
For Database stats:
SQL> Exec DBMS_STATS.DELETE_DATABASE_STATS (); 
For System stats:
SQL> Exec DBMS_STATS.DELETE_SYSTEM_STATS (); 
For Dictionary stats:
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS (); 
For Fixed Tables stats:
SQL> Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS (); 
For Schema stats:
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCOTT'); 
For Table stats and it's indexes:
SQL> Exec dbms_stats.DELETE_TABLE_stats(ownname=>'SCOTT',tabname=>'EMP');
For Index:
SQL> Exec dbms_stats.DELETE_INDEX_stats(ownname => 'SCOTT',indname => 'PK_EMP');
For Column:
SQL> Exec dbms_stats.DELETE_COLUMN_stats(ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO');

Note: This procedure can be rollback by restoring STATS using DBMS_STATS.RESTORE_ procedure.


Pending Statistics:  "11g onwards"
===============
What is Pending Statistics:
Pending statistics is a feature let you test the new gathered statistics without letting the CBO (Cost Based Optimizer) use them "system wide" unless you publish them.

How to use Pending Statistics:
Switch on pending statistics mode:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','FALSE');
Note: Any new statistics will be gathered on the database will be marked PENDING unless you change back the previous parameter to true: 
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');

Gather statistics: "as you used to do"
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES'); 
Enable using pending statistics on your session only:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
Then any SQL statement you will run will use the new pending statistics...

When proven OK, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS(); 

Once you finish don't forget to return the Global PUBLISH parameter to TRUE:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');
>If you didn't do so, all new gathered statistics on the database will be marked as PENDING, the thing may confuse you or any DBA working on this DB in case he is not aware of that parameter change.

Reference:
http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm

This task became an automated task starting from 10g, To check the status of that task:
SQL> select * from dba_autotask_client where client_name = "auto optimizer stats collection" ;


As per Oracle documentation, this task gathers statistics on all objects in the database which have stale or missing statistics.

Also you can:

Enable automatic optimizer statistics:
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection', 
operation => NULL, 
window_name => NULL);
END;
/

Disable automatic optimizer statistics:
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection', 
operation => NULL, 
window_name => NULL);
END;
/

To check the tables that have stale statistics:
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

I've updated the post with these information, I recommend you to check this reference:
http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i41282

Wednesday, April 24, 2013

Handling Block Corruptions


Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g / 11g

Contents

Introduction Overview of Steps to handle a Corruption Corruption due to NOLOGGING or UNRECOVERABLE (1) Determine the Extent of the Corruption Problem (2) Replace or Move Away from Suspect Hardware (3) Which Objects are Affected ? Options for various Segment Types: CACHE CLUSTER INDEX PARTITION INDEX LOBINDEX LOBSEGMENT ROLLBACK TABLE PARTITION TABLE TEMPORARY IOT TYPE2 UNDO Other Segment Types No Segment (4) Choosing a Recovery Option (4A) Complete Recovery Block Level Recovery , Datafile Recovery , Database Recovery , After Complete Recovery (4B) Recreating Indexes (4C) Salvaging Data from Tables Methods of extracting data from a corrupt table AROUND a corrupt block Methods of extracting data from a table with a corrupt LOBSEGMENT block Extracting data from the corrupt block itself (4D) Leaving the Corruption in Place Warnings when Leaving a Corruption in Place (4E) Last Options Document History All SQL statements here are for use in SQL*Plus (in 8.1 or higher) or Server Manager (Oracle7 / 8.0) when connected as a SYSDBA user. (Eg: "connect / as sysdba" or "connect internal")

Introduction

This article discusses how to handle one or more block corruptions on an Oracle datafile and describes the main actions to take to deal with them. Please read the complete article before taking any action. This note does not cover memory corruption issues (typically ORA-600 [17xxx] type errors). Note: If the problem is an ORA-1578 on STARTUP then please contact your local support center for advice referencing Note:106638.1 - this note is not visible to customers but the relevant steps from it can be supplied by an experienced support analyst. You may be referred to this article from many places for many forms of error - it is important that you have the following information for each corrupt block:
  • An absolute FILE NUMBER of the file containing the corrupt block. Referred to as "&AFN" in this article.
  • The file name of the file containing the corrupt block. Referred to as "&FILENAME" in this article. ( If you know the FILE NUMBER but not its name then V$DATAFILE can be used to get the file name: SELECT name FROM v$datafile WHERE file#=&AFN; If the file number does not appear in V$DATAFILE in Oracle8i AND &AFN is greater than the DB_FILES parameter value then it is probably a TEMPFILE. In this case the filename can be found using: SELECT name FROM v$tempfile WHERE file#=(&AFN - &DB_FILES_value); )
  • The BLOCK NUMBER of the corrupt block in that file. Referred to as "&BL" in this article.
  • The tablespace number and name containing the affected block. Referred to as "&TSN" (tablespace number) and "&TABLESPACE_NAME" in this article. If you do not know these then you can find them using: SELECT ts# "TSN" FROM v$datafile WHERE file#=&AFN; SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN;
  • The block size of the tablespace where the corruption lies. Referred to as "&TS_BLOCK_SIZE" in this article. For Oracle 9i+, run the following query to determine the appropriate block size: SELECT block_size FROM dba_tablespaces WHERE tablespace_name = (SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN); For Oracle 7, 8.0 and 8.1: Every tablespace in the database has the same block size. For these versions, issue "SHOW PARAMETER DB_BLOCK_SIZE" and use this value as your &TS_BLOCK_SIZE.
Eg: For the ORA-1578 error: ORA-01578: ORACLE data block corrupted (file # 7, block # 12698) ORA-01110: data file 22: '/oracle1/oradata/V816/oradata/V816/users01.dbf' then: &AFN is "22" (from the ORA-1110 portion of the error) &RFN is "7" (from the "file #" in the ORA-1578) &BL is "12698" (from the "block #" in the ORA-1578) &FILENAME is '/oracle1/oradata/V816/oradata/V816/users01.dbf' &TSN etc.. should be determined from the above SQL For other errors (ORA-600 , ORA-1498 etc...) the above values should either be given to you by Oracle Support, or be given to you from the article which covers the relevant error. Some errors, such as ORA-1410 "invalid ROWID" , ORA-12899 "value too large for column" etc.., do not give details of the corrupt file / block. For such cases Note:869305.1 may help in locating the corrupt row.

Overview of Steps to handle a Corruption

There are many possible causes of a block corruption including: - Bad IO hardware / firmware - OS problems - Oracle problems - Recovering through "UNRECOVERABLE" or "NOLOGGING" database actions (in which case ORA-1578 is expected behaviour - see below) The point in time when an Oracle error is raised may be much later than when any corruption initially occurred. As the root cause is not usually known at the time the corruption is encountered, and as in most cases the key requirement is to get up and running again, then the steps used tackle corruption problems in this article are: 1) Determine the extent of the corruption problems and also determine if the problems are permanent or transient. If the problem is widespread or the errors move about then focus on identifying the cause first (check hardware etc..). This is important as there is no point recovering a system if the underlying hardware is faulty. 2) Replace or move away from any faulty or suspect hardware. 3) Determine which database objects are affected. 4) Choose the most appropriate database recovery / data salvage option. For all steps above it is sensible to collect evidence and document exactly what actions are being taken. The 'Evidence>>' tags in this article list the information which should be collected to assist with identifying the root cause of the problem. Corruption due to NOLOGGING or UNRECOVERABLE If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed. In Oracle8i an ORA-26040 is also signalled ("ORA-26040: Data block was loaded using the NOLOGGING option" ) which makes the cause fairly obvious, but earlier releases have no additional error message. If a block is corrupt due to recovery through a NOLOGGING operation then you can use this article from Section 3 "Which Objects are Affected ?" onwards but note that: (a) Recovery cannot retrieve the NOLOGGING data (b) No data is salvagable from inside the block

(1) Determine the Extent of the Corruption Problem

Whenever a corruption error occurs note down the FULL error message/s and look in the instance's alert log and trace files for any associated errors. It is important to do this first to assess whether this is a single block corruption, an error due to an UNRECOVERABLE operation or a more severe issue. It is a good idea to scan affected files (and any important files) with DBVERIFY to check for other corruptions in order to determine the extent of the problem. For details of using DBVERIFY see Note:35512.1 Once you have determined a list of corrupt file/block combinations then the steps below can be used to help determine what action can be taken. Evidence>> - Record the original error in full, along with details of the application which encountered the error. - Save an extract from the alert log from a few hours before the FIRST recorded problem up to the current point in time. - Save any tracefiles mentioned in the alert log. - Record any recent OS problems you have encountered. - Note if you are using any special features - Eg: ASYNC IO, fast write disk options etc.. - Record your current BACKUP position (Dates, Type etc...) - Note if your database is in ARCHIVELOG mode or not Eg: Issue "ARCHIVE LOG LIST" in SQL*Plus (or Server Manager)

(2) Replace or Move Away from Suspect Hardware

The vast majority of corruption problems are caused by faulty hardware. If there is a hardware fault or a suspect component then it is sensible to either repair the problem, or make disk space available on a separate disk sub-system prior to proceeding with a recovery option. You can move datafiles about using the following steps: 1. Make sure the file to be relocated is either OFFLINE or the instance is in the MOUNT state (not open) 2. Physically restore (or copy) the datafile to its new location eg: /newlocation/myfile.dbf 3. Tell Oracle the new location of the file. eg: ALTER DATABASE RENAME FILE '/oldlocation/myfile.dbf' TO '/newlocation/myfile.dbf'; (Note that you cannot RENAME a TEMPFILE - TEMPFILEs should be dropped and recreated at the new location) 4. Online the relevant file / tablespace (if database is open) IMPORTANT: If there are multiple errors (which are NOT due to NOLOGGING) OR You have OS level errors against the affected file OR The errors are transient and keep moving about then there is little point proceeding until the underlying problem has been addressed or space is available on alternative disks. Get your hardware vendor to check the system over and contact Oracle Support with details of all errors. Please note: Whilst a failed hardware check is a good indication that there is a hardware issue, a successful hardware check should not be taken as proof that there is no hardware related issue - it is very common for hardware tests to report success when there really is some underlying fault. If using any special IO options such as direct IO , async IO or similar it may be worth disabling them in order to eliminate such options as a potential source of problems.

(3) Which Objects are Affected ?

It is best to determine which objects are affected BEFORE making any decisions about how to recover - this is because the corruption/s may be on object/s which can easily be re-created. Eg: For a corruption on a 5 row lookup table it may be far quicker to drop and recreate the table than to perform a recovery. For each corruption collect the information in the following table. The steps to do this are explained below.
  • Information to Record for each Corruption
    Original
    Error
    Absolute
    File#
    &AFN
    Relative
    File#
    &RFN
    Block#

    &BL
    TablespaceSegment
    Type
    Segment
    Owner.Name
    Related
    Objects
    Recovery
    Options


















  The notes below will help you fill in this table for each corruption.

 "Original Error"
  This is the error as initially reported. 
  Eg: ORA-1578 / ORA-1110 , ORA-600 with all arguments etc..
 
 "Absolute File#", "Relative File#" and "Block#"
  The File# and Block# should have been given to you either by the
  error, by Oracle Support, or by the steps in an error article which
  directed you to this article. 

  In Oracle8/8i/9i/10g:  
  The absolute and relative file numbers are often the
               same but can differ (especially if the database has 
        been migrated from Oracle7). It is important to get 
        the correct numbers for &AFN and &RFN
               or you may end up salvaging the wrong object !!

        An ORA-1578 reports the RELATIVE file number, with the
               ABSOLUTE file number given in the accompanying ORA-1110
               error. For ORA-600 errors you should be told an absolute
        file number.

        The following query will show the absolute and relative
        file numbers for datafiles in the database:

                SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
    FROM dba_data_files;

  In Oracle8i/9i/10g: 
        In addition to the notes above about Oracle8, Oracle8i onwards
        can have TEMPFILES.  The following query will show the 
        absolute and relative file numbers for tempfiles in the 
        database:

  SELECT tablespace_name, file_id+value "AFN", relative_fno "RFN"
    FROM dba_temp_files, v$parameter
   WHERE name='db_files';

  In Oracle7:  Use the same file number for both the "Absolute File#"
               and the "Relative File#"


 
 "Segment Type", "Owner", "Name" and "Tablespace"
  The following query will tell you the object TYPE , OWNER and NAME of
  a segment given the absolute file number "&AFN" and block number "&BL" of the
  corrupt block - the database must be open in order to use this query:

        SELECT tablespace_name, segment_type, owner, segment_name
          FROM dba_extents
         WHERE file_id = &AFN
           and &BL between block_id AND block_id + blocks - 1
         ;

  If the block is in a TEMPFILE the above query will return no data.
  For TEMPFILES the "Segment Type" will be "TEMPORARY".

  If the above query does not return rows, it can also be that the corrupted block is a segment header
  in a Locally Managed Tablespace (LMT).  When the corrupted block is a segment 
  header block in a LMT,  the above query produces a corruption message in the alert.log 
  but the query does not not fail.  In that case use this query:

       SELECT owner, segment_name, segment_type, partition_name 
         FROM dba_segments
        WHERE header_file = &AFN
          and header_block = &BL
        ;
   
   Reference Note 819533.1

 
 "Related Objects" and Possible "Recovery Options" by SEGMENT_TYPE:
  The related objects and recovery options which can be used depend on the
  SEGMENT_TYPE. The additional queries and possible recovery options are 
  listed below for each of the most common segment types. 

 CACHE  
 CLUSTER  
 INDEX PARTITION  INDEX   
 LOBINDEX   LOBSEGMENT
 ROLLBACK          
        TABLE PARTITION  TABLE
 TEMPORARY  
 TYPE2 UNDO  
   Some other Segment Type
 "no rows" from the query

  CACHE
 - If the segment type is CACHE recheck you have entered the SQL 
          and parameters correctly.
   If you get the same result contact Oracle support with all 
   information you have. 

 Options:
      The database is likely to require recovery.

 {Continue}  {Back to Segment List}

  CLUSTER 
 - If the segment is a CLUSTER determine which tables it contains.
   Eg: 
  SELECT owner, table_name 
    FROM dba_tables 
   WHERE owner='&OWNER'
     AND cluster_name='&SEGMENT_NAME' 
  ;

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary clusters possible options include:
  Recovery
     OR  Salvage data from all tables in the cluster
     THEN Recreate the cluster and all its tables
  
   As the cluster may contain a number of tables, it is best to
   collect information for each table in the cluster before making a
          decision. 

 {Collect TABLE information}  {Back to Segment List}

  INDEX PARTITION
  - If the segment is an INDEX PARTITION note the NAME and OWNER 
   and then determine which partition is affected thus:

  SELECT partition_name 
           FROM dba_extents
           WHERE file_id = &AFN
               AND &BL BETWEEN block_id AND block_id + blocks - 1
  ;

   then continue below as if the segment was an INDEX segment.

 Options:
   Index partitions can be rebuilt using:
     ALTER INDEX xxx REBUILD PARTITION ppp;    
   (take care with the REBUILD option as described in
     "Recreating Indexes" below)

  
  INDEX 
 - If the segment is an INDEX then if the OWNER is "SYS" contact 
   Oracle support with all details.

          For a non-dictionary INDEX or INDEX PARTITIONs find out which table 
   the INDEX is on:
   Eg: 
     SELECT table_owner, table_name
    FROM dba_indexes 
   WHERE owner='&OWNER'
         AND index_name='&SEGMENT_NAME'
  ;

   and determine if the index supports a CONSTRAINT:

   Eg:  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE owner='&TABLE_OWNER' 
     AND constraint_name='&INDEX_NAME'
  ;

   Possible values for CONSTRAINT_TYPE are:

  P The index supports a primary key constraint.
  U The index supports a unique constraint.


   If the INDEX supports a PRIMARY KEY constraint (type "P") then
   check if the primary key is referenced by any foreign key constraints:
   Eg: 
  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE r_owner='&TABLE_OWNER' 
     AND r_constraint_name='&INDEX_NAME'
  ;

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary indexes possible options include:
  Recovery
     OR  Recreate the index (with any associated constraint
    disables/enables)
  (take care with the REBUILD option as described in
   "Recreating Indexes" below)

 {Continue}  {Back to Segment List}

  ROLLBACK
 - If the segment is a ROLLBACK segment contact Oracle support as
   rollback segment corruptions require special handling. 

 Options:
   The database is likely to require recovery.


 {Continue}  {Back to Segment List}

  TYPE2 UNDO
 - TYPE2 UNDO is a system managed undo segment which is a special
   form of rollback segment. Corruptions in these segments require
   special handling.

 Options:
   The database is likely to require recovery.


 {Continue}  {Back to Segment List}

  TABLE PARTITION 
  - If the segment is a TABLE PARTITION note the NAME and OWNER 
   and then determine which partition is affected thus:

  SELECT partition_name 
           FROM dba_extents
           WHERE file_id = &AFN
               AND &BL BETWEEN block_id AND block_id + blocks - 1
  ;

   then continue below as if the segment was a TABLE segment.

 Options:
   If all corruptions are in the same partition then one option
   at this point is to EXCHANGE the corrupt partition with an
   empty TABLE - this can allow the application to continue (without
   access to the data in the corrupt partition) whilst any good 
   data can then be extracted from the table.

   For other options see the TABLE options below.
 

  TABLE 
 - If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

          For a non-dictionary TABLE or TABLE PARTITIONs find out which 
   INDEXES exist on the TABLE:
   Eg: 
     SELECT owner, index_name, index_type
    FROM dba_indexes 
   WHERE table_owner='&OWNER'
         AND table_name='&SEGMENT_NAME'
  ;

   and determine if there is any PRIMARY key on the table:

   Eg:  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE owner='&OWNER' 
     AND table_name='&SEGMENT_NAME'
     AND constraint_type='P'
  ;

   If there is a primary key then check if this is referenced by any 
   foreign key constraints:
   Eg: 
  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE r_owner='&OWNER' 
     AND r_constraint_name='&CONSTRAINT_NAME'
  ;

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (or partition)
     THEN Recreate the table (or partition)
     OR  Leave the corruption in place
  (eg: Use DBMS_REPAIR to mark the problem blocks to be skipped)

 {Continue}  {Back to Segment List}

  IOT (Index Organized Table)
         The corruption in IOT table should be handled in the same way as in a heap or partitioned table.
         The only exception is if the PK is corrupted.
         PK of an IOT table is the table itself and can't be dropped and recreated.

        Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (or partition)
     THEN Recreate the table (or partition)
     OR  Leave the corruption in place
  (DBMS_REPAIR cannot be used with IOTs)

{Continue}  {Back to Segment List}

  LOBINDEX 
        - Find out which table the LOB belongs to:

   SELECT table_name, column_name 
    FROM dba_lobs 
   WHERE owner='&OWNER'
     AND index_name='&SEGMENT_NAME';

 - If the table is owned by "SYS" then contact Oracle support with all 
   details.  The database is likely to require recovery.

 - It is not possible to rebuild LOB indexes and so you have to
   treat the problem as a corruption on the LOB column of the
   affected table.

   Get index and constraint information for the table which has
   the corrupt LOB index using the SQL in the TABLE
   section, then return here.

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (and its LOB column/s)
     THEN Recreate the table 
   It is not generally sensible just to leave the corruption in 
   place unless the table is unlikely to have any further DML on 
   the problem column.


 {Continue}  {Back to Segment List}
 

  LOBSEGMENT 

        - Find out which table the LOB belongs to:
   Eg: 
   SELECT table_name, column_name 
    FROM dba_lobs 
   WHERE owner='&OWNER'
     AND segment_name='&SEGMENT_NAME';

 - If the table is owned by "SYS" then contact Oracle support with all 
   details.  The database is likely to require recovery.

 - For non-dictionary tables ...

   Get index and constraint information for the table which has
   the corrupt LOB data using the SQL in the TABLE
   section, then return here to find details of the exact rows 
   affected.

   Finding the exact row which references the corrupt LOB block
   can be a challenge as the errors reported do not show any
   detail about which table row owns the lob entry which is corrupt.

   Typically one can refer to application logs or any SQL_TRACE
   or 10046 trace of a session hitting the error (if available) or
   see if having event "1578 trace name errorstack level 3" 
   set in the session helps identify the current SQL/binds/row.
   eg:
  ALTER SYSTEM SET EVENTS '1578 trace name errorstack level 3';
  
  Then wait for the error to be hit by the application
  and find the trace file. 

   If there are no clues then you can construct a PLSQL block
   to scan the problem table row by row extracting the LOB
   column data which loops until it hits an error. Such a technique
   may take a while but it should be possible to get a primary key
   or rowid of any row which references a corrupt LOB block.

   eg: 
  set serverout on
  exec dbms_output.enable(100000);
  declare
   error_1578 exception;
   pragma exception_init(error_1578,-1578);
   n number;
   cnt number:=0;
   badcnt number:=0;
  begin
    for cursor_lob in
          (select rowid r, &LOB_COLUMN_NAME L from &OWNER..&TABLE_NAME)
    loop
      begin
        n:=dbms_lob.instr(cursor_lob.L,hextoraw('AA25889911'),1,999999) ;
      exception
       when error_1578 then
         dbms_output.put_line('Got ORA-1578 reading LOB at '||cursor_lob.R);
         badcnt:=badcnt+1;
      end;
      cnt:=cnt+1;
    end loop;
    dbms_output.put_line('Scanned '||cnt||' rows - saw '||badcnt||' errors');
  end;
  /


     It is possible to have a corrupt LOB block which is only 
   present as an old version (for consistent read) and which has
   not yet been re-used in which case all table rows will be
   accessible but it may not be possible to insert / update
   the LOB columns once that block is reclaimed for reuse.


 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (and its LOB column/s)
     THEN Recreate the table 
     OR  Leave the corruption in place 
    (It is not possible to use DBMS_REPAIR on LOB segments)


 {Continue}  {Back to Segment List}

  TEMPORARY 
 - If the segment type is TEMPORARY then the corruption does not
   affect a permanent object. Check if the tablespace where the
   problem occurred is being used as a TEMPORARY tablespace thus:

  SELECT count(*) FROM dba_users
   WHERE temporary_tablespace='&TABLESPACE_NAME'
  ;

 Options:
   If this is a TEMPORARY_TABLESPACE then it may be possible
   to create a NEW temporary tablespace and switch all users
   to that tablespace then DROP the problem tablespace.

   If this is not a temporary tablespace then the block should
   not be read again and should get re-formatted next time the
   block is used - the error should not repeat PROVIDED any 
     underlying cause has been cured.

   No restore is normally required, although if the disk is
   suspect and the tablespace contains useful data then a 
   database recovery of the affected file/s may be wise.


 {Continue}  {Back to Segment List}

  Some other SEGMENT_TYPE
 - If the segment type returned is not covered above then contact
   Oracle support for advice with all information collected so far.

 {Continue}  {Back to Segment List}

  "no rows returned"
 - If there appears to be no extent containing the corrupt block
   then first double check the figures used in the query. If you 
   are sure the file and block are correct and do not appear as
   belonging to an object in DBA_EXTENTS then:

  - Double check if the file involved is a TEMPFILE.
    Note that TEMPFILE file numbers depend on the init.ora
    parameter DB_FILES so any changes to this parameter
    change the absolute file number reported in errors.

         - DBA_EXTENTS does not include blocks which are used
    for local space management in locally managed tablespaces.

  - If the database you are now querying is from a different
    point in time to the datafile with the error then the
    problem object may have been dropped and so queries against
    DBA_EXTENTS may show no rows.

  - If the error you are investigating was reported by DBVERIFY
    then DBV checks all blocks regardless of whether they 
    belong to an object or not. This it is possible for a
      corrupt block to exist in the datafile but in a block
    not in use by any object.

 Options:
   An error on an UNUSED Oracle block can be ignored as Oracle will
   create a new block image should the block need to be used so any
   existing problem on the block will never get read.

   If you suspect that the block may be a space management
   block then you can use DBMS_SPACE_ADMIN to help check
   this by running:
   
     exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('&TABLESPACE_NAME');

   This should write inconsistencies to the trace file but
   if it encounters a fatally corrupt block it will report an
   error like:
     ORA-03216: Tablespace/Segment Verification cannot proceed
        
   An error on a bitmap space management block can often be corrected
   by running:

    exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('&TABLESPACE_NAME');

 {Continue}  {Back to Segment List}

  Evidence>>  
   - For each corrupt block it is also a good idea to collect 
   the following physical evidence if there is a need to try 
   and identify the actual cause of the corruption:

   i)  An operating system HEX dump of the bad block and the block 
       either side of it.
       On UNIX:  
         dd if=&FILENAME bs=&TS_BLOCK_SIZE skip=&BL-1 count=3 of=BL.dd
         ^^^^^^^^      ^^^^^^^^^^^^^^         ^^^
       Eg: For BL=1224:
         dd if=ts11.dbf bs=4k skip=1223 count=3 of=1223_1225.dd 

       On VMS:    
  DUMP/BLOCKS=(start:XXXX,end:YYYY)/out=dump.out &FILENAME

  Where XXXX=Operating system block number (in 512 byte chunks)
  To calculate this multiply the block number reported by
  '&TS_BLOCK_SIZE/512'. 

   ii) If you are in ARCHIVELOG mode make a safe copy of the archived 
       log files around the time of the error, and preferably for a few 
       hours before the error was reported. Also secure any backup/s
       of the problem datafile from before the errors as the before
       image PLUS redo can help point towards a cause.
       (DBV can often be used to check if the problem exists in a
        backup copy of a file). The ideal scenario is to have a 
       datafile backup image which does not have any corruption
       and all the redo from that point in time up to and just past
       the time when the corruption is first reported.

   iii) Obtain an Oracle dump of the problem block/s:

  ALTER SYSTEM DUMP DATAFILE '&FILENAME' 
    BLOCK &BL
  ;

       (The output will go to a tracefile in the USER_DUMP_DEST).

 {Continue}  {Back to Segment List}

(4) Choosing a Recovery Option

The best recovery option now depends on the objects affected. The notes in Section (3) above should have highlighted the main options available for each affected object. The actual recovery method chosen may include a mix or one or more methods thus: Is any Recovery Required ? If the error is in a TEMPORARY tablespace, or is in a block which is no longer part of any database object then no action is required, although it may be wise to relocate the problem tablespace to a different storage device. See Warnings. Is Complete Recovery an option ? In order for complete recovery to be an option the following must be true: - The database is in ARCHIVELOG mode (The "ARCHIVE LOG LIST" command shows Archivelog Mode) - You have a good backup of affected files. Note that in some cases, the corruption may have been present, but undetected, for a long period of time. If the most recent datafile backup still contains the corruption, you can try an earlier backup as long as you have all the necessary ARCHIVELOGS. (You can often use the DBV START= / END= options to check if specific block/s in a restored copy of a backup file are corrupt) - All ARCHIVELOGS are available from the time of the backup to the current point in time - The current online log/s are available and intact - The errors are NOT due to recovery through a NOLOGGING operation When the above criteria are satisfied then complete recovery is usually the preferred option *BUT NOTE* (a) If the rollback of a transaction has seen a corrupt block on an object other than the rollback segment itself then UNDO may have been discarded. In this case you may need to rebuild indexes / check data integrity AFTER the recovery completes. (b) If the files to be recovered contain data from NOLOGGING operations performed since the last backup then those blocks will be marked corrupt if datafile or database recovery is used. In some cases this can put you in a worse scenario than the current position. If database recovery has already been performed and the corruption is still there then either all of your backups contain the corruption, the underlying fault is still present or the problem is replaying through redo. In these cases you will need to choose some other recovery option. See "(4A) Complete Recovery" for complete recovery steps. Can the object be Dropped or Re-created without needing to extract any data from the object itself ? It may be possible to lose the object, or to recreate it from a script / recent export. Once an object is dropped then blocks in that object are marked as "free" and will be re-formatted when the block gets allocated to a new object. It is advisable to RENAME rather than DROP a table unless you are absolutely sure that you do not need any data in it. In the case of a table partition then only the affected partition needs to be dropped. eg: ALTER TABLE ... DROP PARTITION ... If the corruption affects the partition segment header, or the file containing the partition header is offline, then DROP PARTITION may fail. In this case it may still be possible to drop the partition by first exchanging it with a table of the same definition. eg: ALTER TABLE .. EXCHANGE PARTITION .. WITH TABLE ..; The most common object which can be re-created is an index. Always address TABLE corruptions before INDEX problems on a table. See "(4B) Recreating Indexes" for more details. For any segment, a quick way to extract the DDL for an object, when you have the absolute file number and block number of the corrupt block, is: set long 64000 select dbms_metadata.get_ddl(segment_type, segment_name, owner) FROM dba_extents WHERE file_id=&AFN AND &BL BETWEEN block_id AND block_id + blocks -1; Is it required to salvage data before recreating the object ? If the problem is on a critical application table which is regularly updated then it may be required to salvage as much data from the table as possible, then recreate the table. See "(4C) Salvaging Data from Tables" for more details. Is it acceptable to leave the corruption in place for the moment? In some cases the best immediate option may be to leave the corruption in place and isolate it from application access. See "(4D) Leaving the Corruption In Place" for more details. Last Options Are any of the following possible ? Recovery to an old point-in-time (via point in time recovery) of either the database or tablespace point in time recovery OR Restore of a COLD backup from before the corruption OR Use of an existing export file See "(4E) Last Options" for more details.

(4A) Complete Recovery

If the database is in ARCHIVELOG mode and you have a good backup of the affected files then recovery is usually the preferred option. This is not GUARANTEED to clear a problem, but is effective for the majority of corruption issues. If recovery re-introduces the problem then return to the list of options above and choose another method. If you are using Oracle9i (or higher) then it may be possible to perform block level recovery using the RMAN BLOCKRECOVER command. If using an earlier Oracle release then you can either perform datafile recovery (which can be done while the rest of the database is still up and running), or database recovery (which requires the database to be taken down) . If you are using Oracle 11g (or higher) then it may be possible to use Data Recovery Advisor (see Note 1317849.1) Block Level Recovery ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ As of Oracle9i RMAN allows individual blocks to be recovered whilst the rest of the database (including other blocks in the datafile) are available for normal access. Note that block level recovery can only be used to recover a block fully to the current point in time. It is not necessary to be using RMAN for backups to be able to use this option for recovery of individual blocks. eg: Consider that you have an ORA-1578 on file #6 block #30 which is likely due to a media corruption problem and there is a good cold backup image of that file which has been restored to '.../RESTORE/filename.dbf'. Provided all archivelogs exist (in the default location) then you can use RMAN to perform a block level recovery using a command sequence like: rman nocatalog connect target catalog datafilecopy '.../RESTORE/filename.dbf'; run {blockrecover datafile 6 block 30;} This will use the registered datafile backup image and any required archivelogs to perform block recovery of just the one problem block to current point in time. Please see the documentation for full details of the RMAN BLOCKRECOVER command and limitations. See note 144911.1 for an example. Datafile Recovery ~~~~~~~~~~~~~~~~~~ Datafile recovery of a file involves the following steps. If there are several files repeat the steps for each file or see "Database Recovery" below. These steps can be used if the database is either OPEN or MOUNTED. OFFLINE the affected data file eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE; Copy it to a safe location (in case the backup is bad) Restore the latest backup of the file onto a GOOD disk Check the restored file for obvious corruptions with DBVERIFY For details of using DBVERIFY see Note:35512.1 Assuming the restored file is OK, then RENAME the datafile to the NEW location (if different from the old location) eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name'; Recover the datafile eg: RECOVER DATAFILE 'name_of_file'; Online the file/s eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE; {Continue} Database Recovery ~~~~~~~~~~~~~~~~~ Database recovery generally involves the following steps: Shutdown (Immediate or Abort) Copy the current copy of all files to be recovered to a safe location Restore the backup files to a GOOD disk location DO NOT RESTORE THE CONTROL FILES or ONLINE REDO LOG FILES Check restored files with DBVERIFY For details of using DBVERIFY see Note:35512.1 Startup MOUNT Rename any relocated files eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name'; Ensure all required files are online eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE; Recover the database eg: RECOVER DATABASE Open the database eg: ALTER DATABASE OPEN; After a Complete Recovery ~~~~~~~~~~~~~~~~~~~~~~~~~~ Once a complete recovery has been performed it is advisable to check the database before allowing it to be used: - Run "ANALYZE <table_name> VALIDATE STRUCTURE CASCADE" against each problem object to check for table/index mis-matches. If there has been any UNDO discarded this may show a mismatch requiring indexes to be re-created. - Check the logical integrity of data in the table at the application level.

(4B) Recreating Indexes

If the corrupt object is a user INDEX you can simply drop and re-create it PROVIDED the underlying table is not also corrupt. If the underlying table is also corrupt it is advisable to sort out the TABLE before recreating any indexes. If the information collected shows that the index has dependent FOREIGN KEY constraints then you will need to do something like this: - ALTER TABLE <child_table> DISABLE CONSTRAINT <fk_constraint>; for each foreign key - Rebuild the primary key using ALTER TABLE <table> DISABLE CONSTRAINT <pk_constraint>; DROP INDEX <index_name>; CREATE INDEX <index_name> .. with appropriate storage clause ALTER TABLE <table> ENABLE CONSTRAINT <pk_constraint>; - Enable the foreign key constraints ALTER TABLE <child_table> ENABLE CONSTRAINT <fk_constraint>; For an index partition you can: ALTER INDEX ... REBUILD PARTITION ...; Notes: (1) It is important not to REBUILD a non-partitioned corrupt index using an "ALTER INDEX .. REBUILD" command as this will usually try to build the new index from the existing index segment, which contains a corrupt block. "ALTER INDEX ... REBUILD ONLINE" and "ALTER INDEX ... REBUILD PARTITION ..." do not build the new index from the old index segment and so can be used. (2) Create INDEX can use the data from an existing index if the new index is a sub-set of the columns in the existing index. Hence if you have 2 corrupt indexes drop them BOTH before re-creating them. (3) Be sure to use the correct storage details when recreating indexes.

(4C) Salvaging Data from Tables

If the corrupt object is a TABLE or CLUSTER or LOBSEGMENT then it must be understood that the data within the corrupt block is lost. Some of the data may be salvageable from a HEX dump of the block, or from columns covered by indexes. Important: As it may be required to salvage data in the corrupt block from the indexes it is a good idea NOT to drop any existing index until any required data has been extracted. There are many ways to get data out of a table which contains a corrupt block. Choose the most appropriate method as detailed below. The aim of these methods is to extract as much data as possible from the table blocks which can be accessed. It is usually a good idea to RENAME the corrupt table so that the new object can be created with the correct name. Eg: RENAME <emp> TO <emp_corrupt>; Methods of extracting data from a corrupt table AROUND a corrupt block (1) From Oracle 7.2 onwards, including Oracle 8.0, 8.1, and 9i, it is possible to SKIP over corrupt blocks in a table. This is by far the simplest option to extract table data and is discussed in: Extracting data using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS or Event 10231 Note:33405.1 If the corruption is in an IOT overflow segment then the same method should be followed, but using event 10233 together with a full index scan. Note that this method can only be used if the block "wrapper" is marked corrupt. Eg: If the block reports ORA-1578. If the problem is an ORA-600 or other error which does not report and ORA-1578 error then it is often possible to use DBMS_REPAIR to mark the problem blocks in a table as "soft corrupt" such that they will then signal ORA-1578 when accessed which then allows you to use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS. Note: Any blocks which are marked corrupt by the "FIX_CORRUPT_BLOCKS" procedure will also be marked corrupt following any restore / recover operation through the time of the FIX_CORRUPT_BLOCKS. Full details of using DBMS_REPAIR for this can be found in the documentation but in summary the steps are: - Use DBMS_REPAIR.ADMIN_TABLES to create the admin tables - Use DBMS_REPAIR.CHECK_OBJECT to find problem blocks - Get any good data out of problem blocks before corrupting them. - Use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark the found problem blocks as corrupt so that they will then signal ORA-1578 - If required use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip corrupt blocks on the table. (2) From Oracle 7.1 onwards you can use a ROWID range scan. The syntax for this is a little tricky but it is possible to select around a corrupt block using a ROWID hint. As the format of ROWIDs changed between Oracle7 and Oracle8 there are 2 articles which discuss this: Using ROWID Range Scans to extract data in Oracle8 and higher Note:61685.1 Using ROWID Range Scans to extract data in Oracle7 Note:34371.1 (3) If there is a primary key you can select table data via this index. It may also be possible to select some of data via any other index. This can be slow and time consuming and is only normally needed for Oracle 7.0 releases. This method is described in Note:34371.1 (which also describes the ROWID range scans) (4) There are various salvage programs / PLSQL scripts which can be used to salvage data from a table. These can take longer to set up and use than the above methods but can often cope with various kinds of corruption besides an ORA-1578. As these methods typically require much hand-holding from support then some of these articles may not be visible to customers. These require Pro*C to be available and an understanding of how to build Pro*C executables: SALVAGE.PC for Oracle7 Note:2077307.6 These requires manual interaction: SALVAGE.SQL for Oracle7/8 Note:2064553.4 Methods of extracting data from a table with a corrupt LOBSEGMENT block It is not possible to used DBMS_REPAIR on LOB segments. If the corrupt LOB block is NOT referenced by any row in the table then it should be possible to CREATE TABLE as SELECT (CTAS) or export / drop / import the table as is. If the corrupt LOB block is referenced by a row then it should be possible to select or export with a WHERE predicate that excludes the problem row/s. WARNING: It is possible to update the LOB column value of a problem row to NULL which will then clear ORA-1578 on SELECT operations *BUT* the corrupt block will then be waiting to be reclaimed and will eventually signal an ORA-1578 on attempts to get a new LOB for INSERT or UPDATE operations on any row which can be a worse situation than having a corruption on a known row. Hence you should only really set the LOB column to NULL if you intend to immediately recreate the table. Extracting data from the corrupt block itself As the corrupt block itself is "corrupt" then any data extracted from the block should be treated as suspect. The main methods of getting the rows from the corrupt block itself are: - For TABLE blocks Oracle Support can use a tool which attempts to interpret the block contents. - Use any existing indexes on the table to extract data for columns covered by the index where the ROWID falls inside the corrupt block. This is described towards the end of the ROWID range scan articles mentioned above: For Oracle8/8i see Note:61685.1 For Oracle7 see Note:34371.1 - It may be possible to use LogMiner on the redo stream to find the original inserts/updates which loaded the data to the problem block. The main factor here is WHEN the data was actually put in the block. eg; row 2 may have been inserted yesterday but row 1 may have been inserted 5 years ago.

(4D) Leaving A Corruption In Place

It is possible to leave a corruption in place and just accept the errors reported, or prevent access to the problem rows at an application level. eg: If the problem block / row is in a child table then it may be possible at application level to prevent access via the parent row/s such that the child rows are never accessed. (Be wary of cascade type constraints though) This may not help with reports and other jobs which access data in bulk so it may also be desirable to use the DBMS_REPAIR options shown in 4C above to prevent the block/s erroring when accessed. Marking a corruption like this and leaving it around may give a short term solution allowing full data salvage and/or recovery to be attempted at scheduled outage, or allowing time to check other recovery options on a second (clone) database. Note though that marking a block corrupt with DBMS_REPAIR.FIX_CORRUPT_BLOCKS will cause the marked block/s to also be corrupt after recovery through the time that FIX_CORRUPT_BLOCKS was executed. Leaving a corruption may be sensible for data which rapidly ages and is subsequently purged (eg: In a date partitioned table where older partitions are dropped at some point). Leaving Corruptions in LOB segments At application level it can be possible to leave a corrupt LOB column in place until such time as the table can be rebuilt. One way to ensure you do not hit the "WARNING" scenario above is to ensure that the table is only ever accessed via a view which includes a WHERE predicate to prevent the problem row/s from being seen. eg: Consider table MYTAB( a number primary key, b clob ) has one or more rows pointing at corrupt LOB data. ALTER TABLE MYTAB ADD ( BAD VARCHAR2(1) ); CREATE VIEW MYVIEW AS SELECT a,b FROM MYTAB WHERE BAD is null; Set BAD='Y' for any problem row/s If you only access MYTAB via MYVIEW and the row will never be visible and so cannot be updated keeping the corrupt entry isolated until it can be dealt with. Clearly this example is more of a design time solution but some applications may already have similar mechanisms and may only access data via a view (or via an RLS policy) giving some option/s to hide the problem row/s. Warnings when Leaving a Corruption in Place Whilst it is possible to leave a corruption in place it should be noted that the corrupt blocks will still show up in runs of DBVERIFY, in RMAN backup warnings / errors etc.. It is important to make a careful record of any corruption you expect to see from these tools, particularly any blocks you expect to skip with RMAN (eg: having MAX_CORRUPT set) and be sure to remove any "acceptance" of the errors once the corruptions have been cleared. eg: Consider that a corrupt block has been handled by leaving the corruption in place and avoiding the problem row/s at application level. RMAN may be configured to allow the corruptions during backup. The table is then recreated at a later date during some table reorganisation. If RMAN is not updated to reflect that no errors should now be expected then RMAN may ignore some other corruption which occurs at a later time. It is also important to note that leaving corrupt blocks around in table segments can lead to mismatched results from queries eg: different results can occur for tables with SKIP_CORRUPT set depending on whether an index scan or table access occurs. Other reports may just error . Note that leaving a corruption in place but marking the block with DBMS_REPAIR.FIX_CORRUPT_BLOCKS writes redo to corrupt the block which may limit subsequent recovery options.

(4E) Last Options

If you have a standby setup (physical or logical) then check that first. Whatever sort of block the problem occurred on, one possible option is to recover the database, or problem tablespace, to a point in time BEFORE the corruption appeared. The difficulty with this option is that it is not always possible to know when the problem first appeared. DBVERIFY can be often be used to check a restored file for corruptions. For details of using DBVERIFY see Note:35512.1 . In particular the START= / END= DBV options can be used to give a quick first test of whether the problem block itself is bad on a restored backup image. This section outlines some final options available for recovering. If you have come here then one or more of the following have happened: - You have lost a "vital" datafile (or have a corruption on it) and have no good backup of the problem file/s (without the corruption) - Are either not in ARCHIVELOG mode OR do not have all archivelogs since the file was first created - Complete recovery keeps reintroducing the problem Last chance: Please note if you have lost all copies of a datafile but DO still have the ARCHIVE logs from when the file was first created it is still possible to recover the file. Eg: ALTER DATABASE CREATE DATAFILE '....' [as '...'] ; RECOVER DATAFILE '....' ALTER DATABASE DATAFILE '....' ONLINE; If you are in this scenario try to recover the datafile using these steps before proceeding below. If you have reached this line there are no options left to recover to the current point in time. It is advisable to shutdown the instance and take a BACKUP of the current database NOW in order to provide a fall-back position if the chosen course of action fails. (Eg: if you find your backup is bad). Some outline options available are: Revert to an old COLD backup - eg: If in NOARCHIVELOG mode Set up a clone database from a COLD backup - and extract (export) the problem table/s or transport the problem tablespace Point in time recovery to an older point in time that is consistent - requires a good backup and any necessary archive logs - ALL files have to be restored and the whole DB rolled forward to a suitable point in time. - It may be possible to do the point in time recovery in a clone database and then transport the problem tablespace to the problem database, or export / import the problem table from the clone to the problem database . Tablespace point in time recovery - It may be possible to perform a point in time recovery of the affected tablespace only. There are many notes describing tablespace point in time recovery such as Note:223543.1. Rebuild of DB from some logical export / copy - Requires there to already be a good logical backup of the database - NB: You have to RE-CREATE the database for this option. - As with other options the rebuild could be in a clone database just to get a good image of the problem table/s. If you have a good backup then rolling forwards with DB_BLOCK_CHECKING=TRUE can help find the first point in time where something started to go wrong. It is not generally necessary to take the problem database down while investigating the recovery options. eg: You can restore the system tablespace and problem tablespace datafiles only to a totally different location and/or machine as a different instance to investigate how far you can roll forwards etc.. As of Oracle9i you can also use "Trial Recovery" options to save having to keep restoring a backup while looking into your options.



Script To Run DBV On All Datafiles Of the Database

 
This script will simplify the task of running DBV on the all datafiles of a database.
When user experiences ORA-01578 / ORA-08103 or any other kind of corrupt messages this utility can be used to scan the datafiles at OS level.



Thursday, August 14, 2014

Non critical error ORA-48913 caught while writing to trace file "/oracle/saptrace/diag/rdbms/XXX/trace/XXX_lgwr_36503556.trc" Error message: ORA-48913: Writing into trace file failed, file size limit [10240000] reached End of report

Oracle 11g Recommendations are  below.

SQL> show parameter max_dump_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 20000
SQL> alter system set max_dump_file_size=UNLIMITED scope=both;

System altered.

SQL> show parameter max_dump_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string UNLIMITED

Wednesday, August 13, 2014

Statistics In Oracle

In this post I'll try to summarize all sorts of statistics in Oracle, I strongly recommend reading the full article, as it contains information you may find it valuable in understanding Oracle statistics.

################################
Database | Schema | Table | Index Statistics
################################

Gather Database Stats:
===================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
     ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',
     CASCADE => TRUE,
     degree => 4,
     OPTIONS => 'GATHER STALE',
     GATHER_SYS => TRUE,
     STATTAB => PROD_STATS);

CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collected or not.
DEGREE => 4 :Degree of parallelism.
options: 
       =>'GATHER' :Gathers statistics on all objects in the schema.
       =>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to gather those statistics.
       =>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
       =>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
        =>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
        =>'LIST STALE': Returns a list of stale objects as determined by looking at the *_tab_modifications views.
        =>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT STATISTICS section -last third in this post-.

Note: All above parameters are valid for all stats kind (schema,table,..) except Gather_SYS.

For faster execution:
------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
     ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);

What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not recommended to be gathered on all columns.
Removed  "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled the "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU overhead during gathering DB statistics.

This task became an automated task starting with 10g to gathers statistics on all objects in the database having stale or missing statistics, To check the status of that task:
SQL> select status from dba_autotask_client where client_name = 'auto optimizer stats collection';

To Enable automatic optimizer statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection', 
     operation => NULL, 
     window_name => NULL);
     END;
     /

In case you want to Disable automatic optimizer statistics task:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.DISABLE(
     client_name => 'auto optimizer stats collection', 
     operation => NULL, 
     window_name => NULL);
     END;
     /

To check the tables that have stale statistics:

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';



Gather SCHEMA Stats:
=====================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
     ownname =>'SCOTT',
     estimate_percent=>10,
     degree=>1,
     cascade=>TRUE,
     options=>'GATHER STALE');


Gather TABLE Stats:
===================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'SCOTT',
     tabname => 'EMP',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 2:  Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=>  :  For gathering Histograms:
 FOR COLUMNS SIZE AUTO :  You can specify one column between "" instead of all columns.
 FOR ALL COLUMNS SIZE REPEAT :  Prevent deletion of histograms and collect it only for columns already have histograms.
 FOR ALL COLUMNS  :  Collect histograms on all columns.
 FOR ALL COLUMNS SIZE SKEWONLY :  Collect histograms for columns have skewed value should test skewness first>.
 FOR ALL INDEXED COLUMNS :  Collect histograms for columns have indexes only.


Note: Truncating a table will not update table statistics, it will only reset the High Water Mark, you've to re-gather statistics on that table.

Inside "DBA BUNDLE", there is a script called "gather_stats.sh", it will help you easily & safely gather statistics on specific schema or table plus providing advanced features such as backing up/ restore new statistics in case of fallback.
To learn more about "DBA BUNDLE" please visit this post:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html


Gather Index Stats:
==================
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',indname => 'EMP_I',
     estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);

###################
Fixed OBJECTS Statistics
###################

What are Fixed objects:
---------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.

How frequent to gather stats on fixed objects?
------------------------------------------
Only one time for a representative workload unless you've one of these cases:

- After a major database or application upgrade.
- After implementing a new module.
- After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
- Poor performance/Hang encountered while querying dynamic views e.g. V$ views.


Note:
- It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
- Also note that performance degradation may be experienced while the statistics are gathering.
- Having no statistics is better than having a non representative statistics.

How to gather stats on fixed objects:
----------------------------------

First Check the last analyzed date:
---- --------------------------
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED
        from dba_tab_statistics where table_name='X$KGLDP'; 
Second Export the current fixed stats in a table: (in case you need to revert back)
------- -----------------------------------
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE
        ('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');

SQL> EXEC dbms_stats.export_fixed_objects_stats
        (stattab=>'STATS_TABLE_NAME',statown=>'OWNER'); 
Third Gather the fixed objects stats:
------  ------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats; 

Note:
In case you experienced a bad performance on fixed tables after gathering the new statistics:

SQL> exec dbms_stats.delete_fixed_objects_stats(); 
SQL> exec DBMS_STATS.import_fixed_objects_stats
        (stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER'); 


#################
SYSTEM STATISTICS
#################

What is system statistics:
-----------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.

Why gathering system statistics:
------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.

There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):

NOWORKLOAD statistics:
--------------------------
This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats(); 

WORKLOAD statistics:
----------------------
This will gather statistics during the current workload [which supposed to be representative of actual system I/O and CPU workload on the DB].
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start'); 
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop'); 
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60); 

Check the system values collected:
-------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$; 

cpuspeedNW:  Shows the noworkload CPU speed, (average number of CPU cycles per second).
ioseektim:    The sum of seek time, latency time, and OS overhead time.
iotfrspeed:  I/O transfer speed,tells optimizer how fast the DB can read data in a single read request.
cpuspeed:      Stands for CPU speed during a workload statistics collection.
maxthr:          The maximum I/O throughput.
slavethr:      Average parallel slave I/O throughput.
sreadtim:     The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim:     The average time (seconds) for a sequential multiblock read.
mbrc:             The average multiblock read count in blocks.

Notes:
-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW, ioseektim, iotfrspeed) system statistics only.
-Above values can be modified manually using DBMS_STATS.SET_SYSTEM_STATS procedure.
-According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.

Delete system statistics:
---------------------
SQL> execute dbms_stats.delete_system_stats(); 


####################
Data Dictionary Statistics
####################

Facts:
-----
> Dictionary tables are the tables owned by SYS and residing in the system tablespace.
> Normally data dictionary statistics in 9i is not required unless performance issues are detected.
> In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.

If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:

SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');  

When to gather Dictionary statistics:
---------------------------------
-After DB upgrades.
-After creation of a new big schema.
-Before and after big datapump operations.

Check last Dictionary statistics date:
----------------------------------
SQL> select table_name, last_analyzed from dba_tables
     where owner='SYS' and table_name like '%$' order by 2; 

Gather Dictionary Statistics:  
-------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(gather_sys=>TRUE);
->Will gather stats on the whole DB+SYS schema.



#############
Extended Statistics "11g onwards"
#############

Extended statistics can be gathered on columns based on functions or column groups.

Gather extended stats on column function:
=====================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott'; 

In order to make optimizer work with function based terms you need to gather extended stats:

1-Create extended stats:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;

2-Gather histograms:
>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP', method_opt=> 'for all columns size skewonly');

OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> Begin dbms_stats.gather_table_stats
     (ownname => 'SCOTT',tabname => 'EMP',
     method_opt => 'for all columns size skewonly for
     columns (lower(ENAME))');
     end;
     /

To check the Existance of extended statistics on a table:
---------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB  (LOWER("ENAME"))

Drop extended stats on column function:
-------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');

Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statement  are correlated e.g.(country,state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each columns. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in USA so the value of state_name are always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement  with the column group the optimizer will make use of column group statistics.

1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)')from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers',method_opt=> 'for all columns size skewonly');

OR
---

*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>

SQL> Begin dbms_stats.gather_table_stats
     (ownname => 'SH',tabname => 'CUSTOMERS',
     method_opt => 'for all columns size skewonly for
     columns (country_id,cust_state_province)');
     end; 
     /

Drop extended stats on column group:
--------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');


########
Histograms
########

What are Histograms?
-----------------------
> Holds data about values within a column in a table for number of occurrences for a specific value/range.
> Used by CBO to optimize a query to use whatever index Fast Full scan or table full scan.
> Usually being used against columns have data being repeated frequently like country or city column.
> gathering histograms on a column having distinct values (PK) is useless because values are not repeated.
> Two types of Histograms can be gathered:
  -Frequency histograms: is when distinct values (buckets) in the column is less than 255 (e.g. the number of countries is always less than 254).
  -Height balanced histograms: are similar to frequency histograms in their design, but distinct values  > 254
    See an Example: http://aseriesoftubes.com/articles/beauty-and-it/quick-guide-to-oracle-histograms
> Collected by DBMS_STATS (which by default doesn't collect histograms, it deletes them if you didn't use the parameter).
> Mainly being gathered on foreign key columns/columns in WHERE statement.
> Help in SQL multi-table joins.
> Column histograms like statistics are being stored in data dictionary.
> If application exclusively uses bind variables, Oracle recommends deleting any existing histograms and disabling Oracle histograms generation.

Cautions:
   – Do not create them on Columns that are not being queried.
   – Do not create them on every column of every table.
   – Do not create them on the primary key column of a table.

Verify the existence of histograms:
--------------------------------------
SQL> select column_name,histogram from dba_tab_col_statistics
     where owner='SCOTT' and table_name='EMP'; 

Creating Histograms:
-----------------------
e.g.
SQL> Exec dbms_stats.gather_schema_stats
     (ownname => 'SCOTT',
     estimate_percent => dbms_stats.auto_sample_size,
     method_opt => 'for all columns size auto',
     degree => 7); 


method_opt:
FOR COLUMNS SIZE AUTO                 => Fastest. you can specify one column instead of all columns.
FOR ALL COLUMNS SIZE REPEAT     => Prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS => collect histograms on all columns .
FOR ALL COLUMNS SIZE SKEWONLY => collect histograms for columns have skewed value .
FOR ALL INDEXES COLUMNS      => collect histograms for columns have indexes.

Note: AUTO & SKEWONLY will let Oracle decide to create the Histograms or not.

Check the existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms
     where OWNER='SCOTT' table_name='EMP' group by column_name; 

Drop Histograms: 11g
------------------
e.g.
SQL> Exec dbms_stats.delete_column_stats
     (ownname=>'SH', tabname=>'SALES',
     colname=>'PROD_ID', col_stat_type=> HISTOGRAM); 


Stop gather Histograms: 11g
----------------------
[This will change the default table options]
e.g.
SQL> Exec dbms_stats.set_table_prefs
     ('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS SIZE 1 PROD_ID');
>Will continue to collect histograms as usual on all columns in the SALES table except for PROD_ID column.

Drop Histograms: 10g
------------------
e.g.
SQL> exec dbms_stats.delete_column_stats(user,'T','USERNAME');


##################################
Save/IMPORT & RESTORE STATISTICS:
##################################
===================
Export /Import Statistics:
===================
In this way statistics will be exported into table then imported later from that table.

1-Create STATS TABLE:
-  -------------------------
SQL> Exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'prod_stats',tblspace => 'USERS'); 

2-Export the statistics to the STATS table:
---------------------------------------------
For Database stats:
SQL> Exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats(statown => 'SYSTEM', stattab => 'prod_stats');
For Schema stas:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA','STATS_TABLE',NULL,'STATS_TABLE_OWNER');
For Table: 
SQL> Conn scott/tiger
SQL> Exec dbms_stats.export_TABLE_stats(ownname => 'SCOTT',tabname => 'EMP',stattab => 'prod_stats');
For Index:
SQL> Exec dbms_stats.export_INDEX_stats(ownname => 'SCOTT',indname => 'PK_EMP',stattab => 'prod_stats');
For Column:
SQL> Exec dbms_stats.export_COLUMN_stats (ownname=>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');

3-Import the statistics from PROD_STATS table to the dictionary:
--------------------------------------------------------------------
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS
     (stattab => 'prod_stats',statown => 'SYSTEM'); 
For Schema stats:
SQL> Exec DBMS_STATS.IMPORT_SCHEMA_STATS
     (ownname => 'SCOTT',stattab => 'prod_stats', statown => 'SYSTEM');
For Table stats and it's indexes: 
SQL> Exec dbms_stats.import_TABLE_stats
     ( ownname => 'SCOTT', stattab => 'prod_stats',tabname => 'EMP');
For Index:
SQL> Exec dbms_stats.import_INDEX_stats
     ( ownname => 'SCOTT', stattab => 'prod_stats', indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.import_COLUMN_stats
     (ownname=>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');

4-Drop Stat Table:
-------------------
SQL> Exec dbms_stats.DROP_STAT_TABLE (stattab => 'prod_stats',ownname => 'SYSTEM');

===============
Restore statistics: -From Dictionary-
===============
Old statistics are saved automatically in SYSAUX for 31 day.

Restore Dictionary stats as of timestamp:
-----------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DICTIONARY_STATS(sysdate-1); 

Restore Database stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DATABASE_STATS(sysdate-1); 

Restore SYSTEM stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_SYSTEM_STATS(sysdate-1); 

Restore FIXED OBJECTS stats as of timestamp:
-----------------------------------------------
SQL> Exec DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(sysdate-1); 

Restore SCHEMA stats as of timestamp:
---------------------------------------
SQL> Exec dbms_stats.restore_SCHEMA_stats
     (ownname=>'SYSADM',AS_OF_TIMESTAMP=>sysdate-1); 
OR:
SQL> Exec dbms_stats.restore_schema_stats
     (ownname=>'SYSADM',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');

Restore Table stats as of timestamp:
-----------------------------------
SQL> Exec DBMS_STATS.RESTORE_TABLE_STATS
     (ownname=>'SYSADM', tabname=>'T01POHEAD',AS_OF_TIMESTAMP=>sysdate-1);
=========
Advanced:
=========

To Check current Stats history retention period (days):
-------------------------------------------------
SQL> select dbms_stats.get_stats_history_retention from dual;
SQL> select dbms_stats.get_stats_history_availability from dual;
To modify current Stats history retention period (days):
-------------------------------------------------
SQL> Exec dbms_stats.alter_stats_history_retention(60); 

Purge statistics older than 10 days:
------------------------------
SQL> Exec DBMS_STATS.PURGE_STATS(SYSDATE-10); 

Procedure To claim space after purging statstics:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Space will not be claimed automatically when you purge stats, you must claim it manually using this procedure:

Check Stats tables size:
>>>>>>
        col Mb form 9,999,999
        col SEGMENT_NAME form a40
        col SEGMENT_TYPE form a6
        set lines 120
        select sum(bytes/1024/1024) Mb,
        segment_name,segment_type from dba_segments
         where  tablespace_name = 'SYSAUX'
        and segment_name like 'WRI$_OPTSTAT%'
        and segment_type='TABLE'
        group by segment_name,segment_type order by 1 asc
        /

Check Stats indexes size:
>>>>>
        col Mb form 9,999,999
        col SEGMENT_NAME form a40
        col SEGMENT_TYPE form a6
        set lines 120
        select sum(bytes/1024/1024) Mb, segment_name,segment_type
        from dba_segments
        where  tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%'
        and segment_type='INDEX'
        group by segment_name,segment_type order by 1 asc
        / 
Move Stats tables in same tablespace:
>>>>>
        select 'alter table '||segment_name||'  move tablespace
        SYSAUX;' from dba_segments
        where tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%' and segment_type='TABLE'
        / 
Rebuild stats indexes:
>>>>>>
        select 'alter index '||segment_name||'  rebuild online;'
        from dba_segments where tablespace_name = 'SYSAUX'
        and segment_name like '%OPT%' and segment_type='INDEX'
        /

Check for un-usable indexes:
>>>>>
        select  di.index_name,di.index_type,di.status  from
        dba_indexes di , dba_tables dt
        where  di.tablespace_name = 'SYSAUX'
        and dt.table_name = di.table_name
        and di.table_name like '%OPT%'
        order by 1 asc
        / 

Delete Statistics:
===============
For Database stats:
SQL> Exec DBMS_STATS.DELETE_DATABASE_STATS (); 
For System stats:
SQL> Exec DBMS_STATS.DELETE_SYSTEM_STATS (); 
For Dictionary stats:
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS (); 
For Fixed Tables stats:
SQL> Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS (); 
For Schema stats:
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCOTT'); 
For Table stats and it's indexes:
SQL> Exec dbms_stats.DELETE_TABLE_stats(ownname=>'SCOTT',tabname=>'EMP');
For Index:
SQL> Exec dbms_stats.DELETE_INDEX_stats(ownname => 'SCOTT',indname => 'PK_EMP');
For Column:
SQL> Exec dbms_stats.DELETE_COLUMN_stats(ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO');

Note: This procedure can be rollback by restoring STATS using DBMS_STATS.RESTORE_ procedure.


Pending Statistics:  "11g onwards"
===============
What is Pending Statistics:
Pending statistics is a feature let you test the new gathered statistics without letting the CBO (Cost Based Optimizer) use them "system wide" unless you publish them.

How to use Pending Statistics:
Switch on pending statistics mode:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','FALSE');
Note: Any new statistics will be gathered on the database will be marked PENDING unless you change back the previous parameter to true: 
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');

Gather statistics: "as you used to do"
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES'); 
Enable using pending statistics on your session only:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
Then any SQL statement you will run will use the new pending statistics...

When proven OK, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS(); 

Once you finish don't forget to return the Global PUBLISH parameter to TRUE:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PUBLISH','TRUE');
>If you didn't do so, all new gathered statistics on the database will be marked as PENDING, the thing may confuse you or any DBA working on this DB in case he is not aware of that parameter change.

Reference:
http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm

This task became an automated task starting from 10g, To check the status of that task:
SQL> select * from dba_autotask_client where client_name = "auto optimizer stats collection" ;


As per Oracle documentation, this task gathers statistics on all objects in the database which have stale or missing statistics.

Also you can:

Enable automatic optimizer statistics:
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection', 
operation => NULL, 
window_name => NULL);
END;
/

Disable automatic optimizer statistics:
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection', 
operation => NULL, 
window_name => NULL);
END;
/

To check the tables that have stale statistics:
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES';

I've updated the post with these information, I recommend you to check this reference:
http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i41282

Wednesday, April 24, 2013

Handling Block Corruptions


Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g / 11g

Contents

Introduction Overview of Steps to handle a Corruption Corruption due to NOLOGGING or UNRECOVERABLE (1) Determine the Extent of the Corruption Problem (2) Replace or Move Away from Suspect Hardware (3) Which Objects are Affected ? Options for various Segment Types: CACHE CLUSTER INDEX PARTITION INDEX LOBINDEX LOBSEGMENT ROLLBACK TABLE PARTITION TABLE TEMPORARY IOT TYPE2 UNDO Other Segment Types No Segment (4) Choosing a Recovery Option (4A) Complete Recovery Block Level Recovery , Datafile Recovery , Database Recovery , After Complete Recovery (4B) Recreating Indexes (4C) Salvaging Data from Tables Methods of extracting data from a corrupt table AROUND a corrupt block Methods of extracting data from a table with a corrupt LOBSEGMENT block Extracting data from the corrupt block itself (4D) Leaving the Corruption in Place Warnings when Leaving a Corruption in Place (4E) Last Options Document History All SQL statements here are for use in SQL*Plus (in 8.1 or higher) or Server Manager (Oracle7 / 8.0) when connected as a SYSDBA user. (Eg: "connect / as sysdba" or "connect internal")

Introduction

This article discusses how to handle one or more block corruptions on an Oracle datafile and describes the main actions to take to deal with them. Please read the complete article before taking any action. This note does not cover memory corruption issues (typically ORA-600 [17xxx] type errors). Note: If the problem is an ORA-1578 on STARTUP then please contact your local support center for advice referencing Note:106638.1 - this note is not visible to customers but the relevant steps from it can be supplied by an experienced support analyst. You may be referred to this article from many places for many forms of error - it is important that you have the following information for each corrupt block:
  • An absolute FILE NUMBER of the file containing the corrupt block. Referred to as "&AFN" in this article.
  • The file name of the file containing the corrupt block. Referred to as "&FILENAME" in this article. ( If you know the FILE NUMBER but not its name then V$DATAFILE can be used to get the file name: SELECT name FROM v$datafile WHERE file#=&AFN; If the file number does not appear in V$DATAFILE in Oracle8i AND &AFN is greater than the DB_FILES parameter value then it is probably a TEMPFILE. In this case the filename can be found using: SELECT name FROM v$tempfile WHERE file#=(&AFN - &DB_FILES_value); )
  • The BLOCK NUMBER of the corrupt block in that file. Referred to as "&BL" in this article.
  • The tablespace number and name containing the affected block. Referred to as "&TSN" (tablespace number) and "&TABLESPACE_NAME" in this article. If you do not know these then you can find them using: SELECT ts# "TSN" FROM v$datafile WHERE file#=&AFN; SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN;
  • The block size of the tablespace where the corruption lies. Referred to as "&TS_BLOCK_SIZE" in this article. For Oracle 9i+, run the following query to determine the appropriate block size: SELECT block_size FROM dba_tablespaces WHERE tablespace_name = (SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN); For Oracle 7, 8.0 and 8.1: Every tablespace in the database has the same block size. For these versions, issue "SHOW PARAMETER DB_BLOCK_SIZE" and use this value as your &TS_BLOCK_SIZE.
Eg: For the ORA-1578 error: ORA-01578: ORACLE data block corrupted (file # 7, block # 12698) ORA-01110: data file 22: '/oracle1/oradata/V816/oradata/V816/users01.dbf' then: &AFN is "22" (from the ORA-1110 portion of the error) &RFN is "7" (from the "file #" in the ORA-1578) &BL is "12698" (from the "block #" in the ORA-1578) &FILENAME is '/oracle1/oradata/V816/oradata/V816/users01.dbf' &TSN etc.. should be determined from the above SQL For other errors (ORA-600 , ORA-1498 etc...) the above values should either be given to you by Oracle Support, or be given to you from the article which covers the relevant error. Some errors, such as ORA-1410 "invalid ROWID" , ORA-12899 "value too large for column" etc.., do not give details of the corrupt file / block. For such cases Note:869305.1 may help in locating the corrupt row.

Overview of Steps to handle a Corruption

There are many possible causes of a block corruption including: - Bad IO hardware / firmware - OS problems - Oracle problems - Recovering through "UNRECOVERABLE" or "NOLOGGING" database actions (in which case ORA-1578 is expected behaviour - see below) The point in time when an Oracle error is raised may be much later than when any corruption initially occurred. As the root cause is not usually known at the time the corruption is encountered, and as in most cases the key requirement is to get up and running again, then the steps used tackle corruption problems in this article are: 1) Determine the extent of the corruption problems and also determine if the problems are permanent or transient. If the problem is widespread or the errors move about then focus on identifying the cause first (check hardware etc..). This is important as there is no point recovering a system if the underlying hardware is faulty. 2) Replace or move away from any faulty or suspect hardware. 3) Determine which database objects are affected. 4) Choose the most appropriate database recovery / data salvage option. For all steps above it is sensible to collect evidence and document exactly what actions are being taken. The 'Evidence>>' tags in this article list the information which should be collected to assist with identifying the root cause of the problem. Corruption due to NOLOGGING or UNRECOVERABLE If a NOLOGGING (or UNRECOVERABLE) operation is performed on an object and the datafile containing that object is subsequently recovered then the data blocks affected by the NOLOGGING operation are marked as corrupt and will signal an ORA-1578 error when accessed. In Oracle8i an ORA-26040 is also signalled ("ORA-26040: Data block was loaded using the NOLOGGING option" ) which makes the cause fairly obvious, but earlier releases have no additional error message. If a block is corrupt due to recovery through a NOLOGGING operation then you can use this article from Section 3 "Which Objects are Affected ?" onwards but note that: (a) Recovery cannot retrieve the NOLOGGING data (b) No data is salvagable from inside the block

(1) Determine the Extent of the Corruption Problem

Whenever a corruption error occurs note down the FULL error message/s and look in the instance's alert log and trace files for any associated errors. It is important to do this first to assess whether this is a single block corruption, an error due to an UNRECOVERABLE operation or a more severe issue. It is a good idea to scan affected files (and any important files) with DBVERIFY to check for other corruptions in order to determine the extent of the problem. For details of using DBVERIFY see Note:35512.1 Once you have determined a list of corrupt file/block combinations then the steps below can be used to help determine what action can be taken. Evidence>> - Record the original error in full, along with details of the application which encountered the error. - Save an extract from the alert log from a few hours before the FIRST recorded problem up to the current point in time. - Save any tracefiles mentioned in the alert log. - Record any recent OS problems you have encountered. - Note if you are using any special features - Eg: ASYNC IO, fast write disk options etc.. - Record your current BACKUP position (Dates, Type etc...) - Note if your database is in ARCHIVELOG mode or not Eg: Issue "ARCHIVE LOG LIST" in SQL*Plus (or Server Manager)

(2) Replace or Move Away from Suspect Hardware

The vast majority of corruption problems are caused by faulty hardware. If there is a hardware fault or a suspect component then it is sensible to either repair the problem, or make disk space available on a separate disk sub-system prior to proceeding with a recovery option. You can move datafiles about using the following steps: 1. Make sure the file to be relocated is either OFFLINE or the instance is in the MOUNT state (not open) 2. Physically restore (or copy) the datafile to its new location eg: /newlocation/myfile.dbf 3. Tell Oracle the new location of the file. eg: ALTER DATABASE RENAME FILE '/oldlocation/myfile.dbf' TO '/newlocation/myfile.dbf'; (Note that you cannot RENAME a TEMPFILE - TEMPFILEs should be dropped and recreated at the new location) 4. Online the relevant file / tablespace (if database is open) IMPORTANT: If there are multiple errors (which are NOT due to NOLOGGING) OR You have OS level errors against the affected file OR The errors are transient and keep moving about then there is little point proceeding until the underlying problem has been addressed or space is available on alternative disks. Get your hardware vendor to check the system over and contact Oracle Support with details of all errors. Please note: Whilst a failed hardware check is a good indication that there is a hardware issue, a successful hardware check should not be taken as proof that there is no hardware related issue - it is very common for hardware tests to report success when there really is some underlying fault. If using any special IO options such as direct IO , async IO or similar it may be worth disabling them in order to eliminate such options as a potential source of problems.

(3) Which Objects are Affected ?

It is best to determine which objects are affected BEFORE making any decisions about how to recover - this is because the corruption/s may be on object/s which can easily be re-created. Eg: For a corruption on a 5 row lookup table it may be far quicker to drop and recreate the table than to perform a recovery. For each corruption collect the information in the following table. The steps to do this are explained below.
  • Information to Record for each Corruption
    Original
    Error
    Absolute
    File#
    &AFN
    Relative
    File#
    &RFN
    Block#

    &BL
    TablespaceSegment
    Type
    Segment
    Owner.Name
    Related
    Objects
    Recovery
    Options


















  The notes below will help you fill in this table for each corruption.

 "Original Error"
  This is the error as initially reported. 
  Eg: ORA-1578 / ORA-1110 , ORA-600 with all arguments etc..
 
 "Absolute File#", "Relative File#" and "Block#"
  The File# and Block# should have been given to you either by the
  error, by Oracle Support, or by the steps in an error article which
  directed you to this article. 

  In Oracle8/8i/9i/10g:  
  The absolute and relative file numbers are often the
               same but can differ (especially if the database has 
        been migrated from Oracle7). It is important to get 
        the correct numbers for &AFN and &RFN
               or you may end up salvaging the wrong object !!

        An ORA-1578 reports the RELATIVE file number, with the
               ABSOLUTE file number given in the accompanying ORA-1110
               error. For ORA-600 errors you should be told an absolute
        file number.

        The following query will show the absolute and relative
        file numbers for datafiles in the database:

                SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
    FROM dba_data_files;

  In Oracle8i/9i/10g: 
        In addition to the notes above about Oracle8, Oracle8i onwards
        can have TEMPFILES.  The following query will show the 
        absolute and relative file numbers for tempfiles in the 
        database:

  SELECT tablespace_name, file_id+value "AFN", relative_fno "RFN"
    FROM dba_temp_files, v$parameter
   WHERE name='db_files';

  In Oracle7:  Use the same file number for both the "Absolute File#"
               and the "Relative File#"


 
 "Segment Type", "Owner", "Name" and "Tablespace"
  The following query will tell you the object TYPE , OWNER and NAME of
  a segment given the absolute file number "&AFN" and block number "&BL" of the
  corrupt block - the database must be open in order to use this query:

        SELECT tablespace_name, segment_type, owner, segment_name
          FROM dba_extents
         WHERE file_id = &AFN
           and &BL between block_id AND block_id + blocks - 1
         ;

  If the block is in a TEMPFILE the above query will return no data.
  For TEMPFILES the "Segment Type" will be "TEMPORARY".

  If the above query does not return rows, it can also be that the corrupted block is a segment header
  in a Locally Managed Tablespace (LMT).  When the corrupted block is a segment 
  header block in a LMT,  the above query produces a corruption message in the alert.log 
  but the query does not not fail.  In that case use this query:

       SELECT owner, segment_name, segment_type, partition_name 
         FROM dba_segments
        WHERE header_file = &AFN
          and header_block = &BL
        ;
   
   Reference Note 819533.1

 
 "Related Objects" and Possible "Recovery Options" by SEGMENT_TYPE:
  The related objects and recovery options which can be used depend on the
  SEGMENT_TYPE. The additional queries and possible recovery options are 
  listed below for each of the most common segment types. 

 CACHE  
 CLUSTER  
 INDEX PARTITION  INDEX   
 LOBINDEX   LOBSEGMENT
 ROLLBACK          
        TABLE PARTITION  TABLE
 TEMPORARY  
 TYPE2 UNDO  
   Some other Segment Type
 "no rows" from the query

  CACHE
 - If the segment type is CACHE recheck you have entered the SQL 
          and parameters correctly.
   If you get the same result contact Oracle support with all 
   information you have. 

 Options:
      The database is likely to require recovery.

 {Continue}  {Back to Segment List}

  CLUSTER 
 - If the segment is a CLUSTER determine which tables it contains.
   Eg: 
  SELECT owner, table_name 
    FROM dba_tables 
   WHERE owner='&OWNER'
     AND cluster_name='&SEGMENT_NAME' 
  ;

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary clusters possible options include:
  Recovery
     OR  Salvage data from all tables in the cluster
     THEN Recreate the cluster and all its tables
  
   As the cluster may contain a number of tables, it is best to
   collect information for each table in the cluster before making a
          decision. 

 {Collect TABLE information}  {Back to Segment List}

  INDEX PARTITION
  - If the segment is an INDEX PARTITION note the NAME and OWNER 
   and then determine which partition is affected thus:

  SELECT partition_name 
           FROM dba_extents
           WHERE file_id = &AFN
               AND &BL BETWEEN block_id AND block_id + blocks - 1
  ;

   then continue below as if the segment was an INDEX segment.

 Options:
   Index partitions can be rebuilt using:
     ALTER INDEX xxx REBUILD PARTITION ppp;    
   (take care with the REBUILD option as described in
     "Recreating Indexes" below)

  
  INDEX 
 - If the segment is an INDEX then if the OWNER is "SYS" contact 
   Oracle support with all details.

          For a non-dictionary INDEX or INDEX PARTITIONs find out which table 
   the INDEX is on:
   Eg: 
     SELECT table_owner, table_name
    FROM dba_indexes 
   WHERE owner='&OWNER'
         AND index_name='&SEGMENT_NAME'
  ;

   and determine if the index supports a CONSTRAINT:

   Eg:  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE owner='&TABLE_OWNER' 
     AND constraint_name='&INDEX_NAME'
  ;

   Possible values for CONSTRAINT_TYPE are:

  P The index supports a primary key constraint.
  U The index supports a unique constraint.


   If the INDEX supports a PRIMARY KEY constraint (type "P") then
   check if the primary key is referenced by any foreign key constraints:
   Eg: 
  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE r_owner='&TABLE_OWNER' 
     AND r_constraint_name='&INDEX_NAME'
  ;

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary indexes possible options include:
  Recovery
     OR  Recreate the index (with any associated constraint
    disables/enables)
  (take care with the REBUILD option as described in
   "Recreating Indexes" below)

 {Continue}  {Back to Segment List}

  ROLLBACK
 - If the segment is a ROLLBACK segment contact Oracle support as
   rollback segment corruptions require special handling. 

 Options:
   The database is likely to require recovery.


 {Continue}  {Back to Segment List}

  TYPE2 UNDO
 - TYPE2 UNDO is a system managed undo segment which is a special
   form of rollback segment. Corruptions in these segments require
   special handling.

 Options:
   The database is likely to require recovery.


 {Continue}  {Back to Segment List}

  TABLE PARTITION 
  - If the segment is a TABLE PARTITION note the NAME and OWNER 
   and then determine which partition is affected thus:

  SELECT partition_name 
           FROM dba_extents
           WHERE file_id = &AFN
               AND &BL BETWEEN block_id AND block_id + blocks - 1
  ;

   then continue below as if the segment was a TABLE segment.

 Options:
   If all corruptions are in the same partition then one option
   at this point is to EXCHANGE the corrupt partition with an
   empty TABLE - this can allow the application to continue (without
   access to the data in the corrupt partition) whilst any good 
   data can then be extracted from the table.

   For other options see the TABLE options below.
 

  TABLE 
 - If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

          For a non-dictionary TABLE or TABLE PARTITIONs find out which 
   INDEXES exist on the TABLE:
   Eg: 
     SELECT owner, index_name, index_type
    FROM dba_indexes 
   WHERE table_owner='&OWNER'
         AND table_name='&SEGMENT_NAME'
  ;

   and determine if there is any PRIMARY key on the table:

   Eg:  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE owner='&OWNER' 
     AND table_name='&SEGMENT_NAME'
     AND constraint_type='P'
  ;

   If there is a primary key then check if this is referenced by any 
   foreign key constraints:
   Eg: 
  SELECT owner, constraint_name, constraint_type, table_name 
    FROM dba_constraints
   WHERE r_owner='&OWNER' 
     AND r_constraint_name='&CONSTRAINT_NAME'
  ;

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (or partition)
     THEN Recreate the table (or partition)
     OR  Leave the corruption in place
  (eg: Use DBMS_REPAIR to mark the problem blocks to be skipped)

 {Continue}  {Back to Segment List}

  IOT (Index Organized Table)
         The corruption in IOT table should be handled in the same way as in a heap or partitioned table.
         The only exception is if the PK is corrupted.
         PK of an IOT table is the table itself and can't be dropped and recreated.

        Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (or partition)
     THEN Recreate the table (or partition)
     OR  Leave the corruption in place
  (DBMS_REPAIR cannot be used with IOTs)

{Continue}  {Back to Segment List}

  LOBINDEX 
        - Find out which table the LOB belongs to:

   SELECT table_name, column_name 
    FROM dba_lobs 
   WHERE owner='&OWNER'
     AND index_name='&SEGMENT_NAME';

 - If the table is owned by "SYS" then contact Oracle support with all 
   details.  The database is likely to require recovery.

 - It is not possible to rebuild LOB indexes and so you have to
   treat the problem as a corruption on the LOB column of the
   affected table.

   Get index and constraint information for the table which has
   the corrupt LOB index using the SQL in the TABLE
   section, then return here.

 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (and its LOB column/s)
     THEN Recreate the table 
   It is not generally sensible just to leave the corruption in 
   place unless the table is unlikely to have any further DML on 
   the problem column.


 {Continue}  {Back to Segment List}
 

  LOBSEGMENT 

        - Find out which table the LOB belongs to:
   Eg: 
   SELECT table_name, column_name 
    FROM dba_lobs 
   WHERE owner='&OWNER'
     AND segment_name='&SEGMENT_NAME';

 - If the table is owned by "SYS" then contact Oracle support with all 
   details.  The database is likely to require recovery.

 - For non-dictionary tables ...

   Get index and constraint information for the table which has
   the corrupt LOB data using the SQL in the TABLE
   section, then return here to find details of the exact rows 
   affected.

   Finding the exact row which references the corrupt LOB block
   can be a challenge as the errors reported do not show any
   detail about which table row owns the lob entry which is corrupt.

   Typically one can refer to application logs or any SQL_TRACE
   or 10046 trace of a session hitting the error (if available) or
   see if having event "1578 trace name errorstack level 3" 
   set in the session helps identify the current SQL/binds/row.
   eg:
  ALTER SYSTEM SET EVENTS '1578 trace name errorstack level 3';
  
  Then wait for the error to be hit by the application
  and find the trace file. 

   If there are no clues then you can construct a PLSQL block
   to scan the problem table row by row extracting the LOB
   column data which loops until it hits an error. Such a technique
   may take a while but it should be possible to get a primary key
   or rowid of any row which references a corrupt LOB block.

   eg: 
  set serverout on
  exec dbms_output.enable(100000);
  declare
   error_1578 exception;
   pragma exception_init(error_1578,-1578);
   n number;
   cnt number:=0;
   badcnt number:=0;
  begin
    for cursor_lob in
          (select rowid r, &LOB_COLUMN_NAME L from &OWNER..&TABLE_NAME)
    loop
      begin
        n:=dbms_lob.instr(cursor_lob.L,hextoraw('AA25889911'),1,999999) ;
      exception
       when error_1578 then
         dbms_output.put_line('Got ORA-1578 reading LOB at '||cursor_lob.R);
         badcnt:=badcnt+1;
      end;
      cnt:=cnt+1;
    end loop;
    dbms_output.put_line('Scanned '||cnt||' rows - saw '||badcnt||' errors');
  end;
  /


     It is possible to have a corrupt LOB block which is only 
   present as an old version (for consistent read) and which has
   not yet been re-used in which case all table rows will be
   accessible but it may not be possible to insert / update
   the LOB columns once that block is reclaimed for reuse.


 Options:
   If the OWNER is "SYS" then contact Oracle support with all details.
      The database is likely to require recovery.

   For non dictionary tables possible options include:
  Recovery
     OR  Salvage data from the table (and its LOB column/s)
     THEN Recreate the table 
     OR  Leave the corruption in place 
    (It is not possible to use DBMS_REPAIR on LOB segments)


 {Continue}  {Back to Segment List}

  TEMPORARY 
 - If the segment type is TEMPORARY then the corruption does not
   affect a permanent object. Check if the tablespace where the
   problem occurred is being used as a TEMPORARY tablespace thus:

  SELECT count(*) FROM dba_users
   WHERE temporary_tablespace='&TABLESPACE_NAME'
  ;

 Options:
   If this is a TEMPORARY_TABLESPACE then it may be possible
   to create a NEW temporary tablespace and switch all users
   to that tablespace then DROP the problem tablespace.

   If this is not a temporary tablespace then the block should
   not be read again and should get re-formatted next time the
   block is used - the error should not repeat PROVIDED any 
     underlying cause has been cured.

   No restore is normally required, although if the disk is
   suspect and the tablespace contains useful data then a 
   database recovery of the affected file/s may be wise.


 {Continue}  {Back to Segment List}

  Some other SEGMENT_TYPE
 - If the segment type returned is not covered above then contact
   Oracle support for advice with all information collected so far.

 {Continue}  {Back to Segment List}

  "no rows returned"
 - If there appears to be no extent containing the corrupt block
   then first double check the figures used in the query. If you 
   are sure the file and block are correct and do not appear as
   belonging to an object in DBA_EXTENTS then:

  - Double check if the file involved is a TEMPFILE.
    Note that TEMPFILE file numbers depend on the init.ora
    parameter DB_FILES so any changes to this parameter
    change the absolute file number reported in errors.

         - DBA_EXTENTS does not include blocks which are used
    for local space management in locally managed tablespaces.

  - If the database you are now querying is from a different
    point in time to the datafile with the error then the
    problem object may have been dropped and so queries against
    DBA_EXTENTS may show no rows.

  - If the error you are investigating was reported by DBVERIFY
    then DBV checks all blocks regardless of whether they 
    belong to an object or not. This it is possible for a
      corrupt block to exist in the datafile but in a block
    not in use by any object.

 Options:
   An error on an UNUSED Oracle block can be ignored as Oracle will
   create a new block image should the block need to be used so any
   existing problem on the block will never get read.

   If you suspect that the block may be a space management
   block then you can use DBMS_SPACE_ADMIN to help check
   this by running:
   
     exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('&TABLESPACE_NAME');

   This should write inconsistencies to the trace file but
   if it encounters a fatally corrupt block it will report an
   error like:
     ORA-03216: Tablespace/Segment Verification cannot proceed
        
   An error on a bitmap space management block can often be corrected
   by running:

    exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('&TABLESPACE_NAME');

 {Continue}  {Back to Segment List}

  Evidence>>  
   - For each corrupt block it is also a good idea to collect 
   the following physical evidence if there is a need to try 
   and identify the actual cause of the corruption:

   i)  An operating system HEX dump of the bad block and the block 
       either side of it.
       On UNIX:  
         dd if=&FILENAME bs=&TS_BLOCK_SIZE skip=&BL-1 count=3 of=BL.dd
         ^^^^^^^^      ^^^^^^^^^^^^^^         ^^^
       Eg: For BL=1224:
         dd if=ts11.dbf bs=4k skip=1223 count=3 of=1223_1225.dd 

       On VMS:    
  DUMP/BLOCKS=(start:XXXX,end:YYYY)/out=dump.out &FILENAME

  Where XXXX=Operating system block number (in 512 byte chunks)
  To calculate this multiply the block number reported by
  '&TS_BLOCK_SIZE/512'. 

   ii) If you are in ARCHIVELOG mode make a safe copy of the archived 
       log files around the time of the error, and preferably for a few 
       hours before the error was reported. Also secure any backup/s
       of the problem datafile from before the errors as the before
       image PLUS redo can help point towards a cause.
       (DBV can often be used to check if the problem exists in a
        backup copy of a file). The ideal scenario is to have a 
       datafile backup image which does not have any corruption
       and all the redo from that point in time up to and just past
       the time when the corruption is first reported.

   iii) Obtain an Oracle dump of the problem block/s:

  ALTER SYSTEM DUMP DATAFILE '&FILENAME' 
    BLOCK &BL
  ;

       (The output will go to a tracefile in the USER_DUMP_DEST).

 {Continue}  {Back to Segment List}

(4) Choosing a Recovery Option

The best recovery option now depends on the objects affected. The notes in Section (3) above should have highlighted the main options available for each affected object. The actual recovery method chosen may include a mix or one or more methods thus: Is any Recovery Required ? If the error is in a TEMPORARY tablespace, or is in a block which is no longer part of any database object then no action is required, although it may be wise to relocate the problem tablespace to a different storage device. See Warnings. Is Complete Recovery an option ? In order for complete recovery to be an option the following must be true: - The database is in ARCHIVELOG mode (The "ARCHIVE LOG LIST" command shows Archivelog Mode) - You have a good backup of affected files. Note that in some cases, the corruption may have been present, but undetected, for a long period of time. If the most recent datafile backup still contains the corruption, you can try an earlier backup as long as you have all the necessary ARCHIVELOGS. (You can often use the DBV START= / END= options to check if specific block/s in a restored copy of a backup file are corrupt) - All ARCHIVELOGS are available from the time of the backup to the current point in time - The current online log/s are available and intact - The errors are NOT due to recovery through a NOLOGGING operation When the above criteria are satisfied then complete recovery is usually the preferred option *BUT NOTE* (a) If the rollback of a transaction has seen a corrupt block on an object other than the rollback segment itself then UNDO may have been discarded. In this case you may need to rebuild indexes / check data integrity AFTER the recovery completes. (b) If the files to be recovered contain data from NOLOGGING operations performed since the last backup then those blocks will be marked corrupt if datafile or database recovery is used. In some cases this can put you in a worse scenario than the current position. If database recovery has already been performed and the corruption is still there then either all of your backups contain the corruption, the underlying fault is still present or the problem is replaying through redo. In these cases you will need to choose some other recovery option. See "(4A) Complete Recovery" for complete recovery steps. Can the object be Dropped or Re-created without needing to extract any data from the object itself ? It may be possible to lose the object, or to recreate it from a script / recent export. Once an object is dropped then blocks in that object are marked as "free" and will be re-formatted when the block gets allocated to a new object. It is advisable to RENAME rather than DROP a table unless you are absolutely sure that you do not need any data in it. In the case of a table partition then only the affected partition needs to be dropped. eg: ALTER TABLE ... DROP PARTITION ... If the corruption affects the partition segment header, or the file containing the partition header is offline, then DROP PARTITION may fail. In this case it may still be possible to drop the partition by first exchanging it with a table of the same definition. eg: ALTER TABLE .. EXCHANGE PARTITION .. WITH TABLE ..; The most common object which can be re-created is an index. Always address TABLE corruptions before INDEX problems on a table. See "(4B) Recreating Indexes" for more details. For any segment, a quick way to extract the DDL for an object, when you have the absolute file number and block number of the corrupt block, is: set long 64000 select dbms_metadata.get_ddl(segment_type, segment_name, owner) FROM dba_extents WHERE file_id=&AFN AND &BL BETWEEN block_id AND block_id + blocks -1; Is it required to salvage data before recreating the object ? If the problem is on a critical application table which is regularly updated then it may be required to salvage as much data from the table as possible, then recreate the table. See "(4C) Salvaging Data from Tables" for more details. Is it acceptable to leave the corruption in place for the moment? In some cases the best immediate option may be to leave the corruption in place and isolate it from application access. See "(4D) Leaving the Corruption In Place" for more details. Last Options Are any of the following possible ? Recovery to an old point-in-time (via point in time recovery) of either the database or tablespace point in time recovery OR Restore of a COLD backup from before the corruption OR Use of an existing export file See "(4E) Last Options" for more details.

(4A) Complete Recovery

If the database is in ARCHIVELOG mode and you have a good backup of the affected files then recovery is usually the preferred option. This is not GUARANTEED to clear a problem, but is effective for the majority of corruption issues. If recovery re-introduces the problem then return to the list of options above and choose another method. If you are using Oracle9i (or higher) then it may be possible to perform block level recovery using the RMAN BLOCKRECOVER command. If using an earlier Oracle release then you can either perform datafile recovery (which can be done while the rest of the database is still up and running), or database recovery (which requires the database to be taken down) . If you are using Oracle 11g (or higher) then it may be possible to use Data Recovery Advisor (see Note 1317849.1) Block Level Recovery ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ As of Oracle9i RMAN allows individual blocks to be recovered whilst the rest of the database (including other blocks in the datafile) are available for normal access. Note that block level recovery can only be used to recover a block fully to the current point in time. It is not necessary to be using RMAN for backups to be able to use this option for recovery of individual blocks. eg: Consider that you have an ORA-1578 on file #6 block #30 which is likely due to a media corruption problem and there is a good cold backup image of that file which has been restored to '.../RESTORE/filename.dbf'. Provided all archivelogs exist (in the default location) then you can use RMAN to perform a block level recovery using a command sequence like: rman nocatalog connect target catalog datafilecopy '.../RESTORE/filename.dbf'; run {blockrecover datafile 6 block 30;} This will use the registered datafile backup image and any required archivelogs to perform block recovery of just the one problem block to current point in time. Please see the documentation for full details of the RMAN BLOCKRECOVER command and limitations. See note 144911.1 for an example. Datafile Recovery ~~~~~~~~~~~~~~~~~~ Datafile recovery of a file involves the following steps. If there are several files repeat the steps for each file or see "Database Recovery" below. These steps can be used if the database is either OPEN or MOUNTED. OFFLINE the affected data file eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE; Copy it to a safe location (in case the backup is bad) Restore the latest backup of the file onto a GOOD disk Check the restored file for obvious corruptions with DBVERIFY For details of using DBVERIFY see Note:35512.1 Assuming the restored file is OK, then RENAME the datafile to the NEW location (if different from the old location) eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name'; Recover the datafile eg: RECOVER DATAFILE 'name_of_file'; Online the file/s eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE; {Continue} Database Recovery ~~~~~~~~~~~~~~~~~ Database recovery generally involves the following steps: Shutdown (Immediate or Abort) Copy the current copy of all files to be recovered to a safe location Restore the backup files to a GOOD disk location DO NOT RESTORE THE CONTROL FILES or ONLINE REDO LOG FILES Check restored files with DBVERIFY For details of using DBVERIFY see Note:35512.1 Startup MOUNT Rename any relocated files eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name'; Ensure all required files are online eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE; Recover the database eg: RECOVER DATABASE Open the database eg: ALTER DATABASE OPEN; After a Complete Recovery ~~~~~~~~~~~~~~~~~~~~~~~~~~ Once a complete recovery has been performed it is advisable to check the database before allowing it to be used: - Run "ANALYZE <table_name> VALIDATE STRUCTURE CASCADE" against each problem object to check for table/index mis-matches. If there has been any UNDO discarded this may show a mismatch requiring indexes to be re-created. - Check the logical integrity of data in the table at the application level.

(4B) Recreating Indexes

If the corrupt object is a user INDEX you can simply drop and re-create it PROVIDED the underlying table is not also corrupt. If the underlying table is also corrupt it is advisable to sort out the TABLE before recreating any indexes. If the information collected shows that the index has dependent FOREIGN KEY constraints then you will need to do something like this: - ALTER TABLE <child_table> DISABLE CONSTRAINT <fk_constraint>; for each foreign key - Rebuild the primary key using ALTER TABLE <table> DISABLE CONSTRAINT <pk_constraint>; DROP INDEX <index_name>; CREATE INDEX <index_name> .. with appropriate storage clause ALTER TABLE <table> ENABLE CONSTRAINT <pk_constraint>; - Enable the foreign key constraints ALTER TABLE <child_table> ENABLE CONSTRAINT <fk_constraint>; For an index partition you can: ALTER INDEX ... REBUILD PARTITION ...; Notes: (1) It is important not to REBUILD a non-partitioned corrupt index using an "ALTER INDEX .. REBUILD" command as this will usually try to build the new index from the existing index segment, which contains a corrupt block. "ALTER INDEX ... REBUILD ONLINE" and "ALTER INDEX ... REBUILD PARTITION ..." do not build the new index from the old index segment and so can be used. (2) Create INDEX can use the data from an existing index if the new index is a sub-set of the columns in the existing index. Hence if you have 2 corrupt indexes drop them BOTH before re-creating them. (3) Be sure to use the correct storage details when recreating indexes.

(4C) Salvaging Data from Tables

If the corrupt object is a TABLE or CLUSTER or LOBSEGMENT then it must be understood that the data within the corrupt block is lost. Some of the data may be salvageable from a HEX dump of the block, or from columns covered by indexes. Important: As it may be required to salvage data in the corrupt block from the indexes it is a good idea NOT to drop any existing index until any required data has been extracted. There are many ways to get data out of a table which contains a corrupt block. Choose the most appropriate method as detailed below. The aim of these methods is to extract as much data as possible from the table blocks which can be accessed. It is usually a good idea to RENAME the corrupt table so that the new object can be created with the correct name. Eg: RENAME <emp> TO <emp_corrupt>; Methods of extracting data from a corrupt table AROUND a corrupt block (1) From Oracle 7.2 onwards, including Oracle 8.0, 8.1, and 9i, it is possible to SKIP over corrupt blocks in a table. This is by far the simplest option to extract table data and is discussed in: Extracting data using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS or Event 10231 Note:33405.1 If the corruption is in an IOT overflow segment then the same method should be followed, but using event 10233 together with a full index scan. Note that this method can only be used if the block "wrapper" is marked corrupt. Eg: If the block reports ORA-1578. If the problem is an ORA-600 or other error which does not report and ORA-1578 error then it is often possible to use DBMS_REPAIR to mark the problem blocks in a table as "soft corrupt" such that they will then signal ORA-1578 when accessed which then allows you to use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS. Note: Any blocks which are marked corrupt by the "FIX_CORRUPT_BLOCKS" procedure will also be marked corrupt following any restore / recover operation through the time of the FIX_CORRUPT_BLOCKS. Full details of using DBMS_REPAIR for this can be found in the documentation but in summary the steps are: - Use DBMS_REPAIR.ADMIN_TABLES to create the admin tables - Use DBMS_REPAIR.CHECK_OBJECT to find problem blocks - Get any good data out of problem blocks before corrupting them. - Use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark the found problem blocks as corrupt so that they will then signal ORA-1578 - If required use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip corrupt blocks on the table. (2) From Oracle 7.1 onwards you can use a ROWID range scan. The syntax for this is a little tricky but it is possible to select around a corrupt block using a ROWID hint. As the format of ROWIDs changed between Oracle7 and Oracle8 there are 2 articles which discuss this: Using ROWID Range Scans to extract data in Oracle8 and higher Note:61685.1 Using ROWID Range Scans to extract data in Oracle7 Note:34371.1 (3) If there is a primary key you can select table data via this index. It may also be possible to select some of data via any other index. This can be slow and time consuming and is only normally needed for Oracle 7.0 releases. This method is described in Note:34371.1 (which also describes the ROWID range scans) (4) There are various salvage programs / PLSQL scripts which can be used to salvage data from a table. These can take longer to set up and use than the above methods but can often cope with various kinds of corruption besides an ORA-1578. As these methods typically require much hand-holding from support then some of these articles may not be visible to customers. These require Pro*C to be available and an understanding of how to build Pro*C executables: SALVAGE.PC for Oracle7 Note:2077307.6 These requires manual interaction: SALVAGE.SQL for Oracle7/8 Note:2064553.4 Methods of extracting data from a table with a corrupt LOBSEGMENT block It is not possible to used DBMS_REPAIR on LOB segments. If the corrupt LOB block is NOT referenced by any row in the table then it should be possible to CREATE TABLE as SELECT (CTAS) or export / drop / import the table as is. If the corrupt LOB block is referenced by a row then it should be possible to select or export with a WHERE predicate that excludes the problem row/s. WARNING: It is possible to update the LOB column value of a problem row to NULL which will then clear ORA-1578 on SELECT operations *BUT* the corrupt block will then be waiting to be reclaimed and will eventually signal an ORA-1578 on attempts to get a new LOB for INSERT or UPDATE operations on any row which can be a worse situation than having a corruption on a known row. Hence you should only really set the LOB column to NULL if you intend to immediately recreate the table. Extracting data from the corrupt block itself As the corrupt block itself is "corrupt" then any data extracted from the block should be treated as suspect. The main methods of getting the rows from the corrupt block itself are: - For TABLE blocks Oracle Support can use a tool which attempts to interpret the block contents. - Use any existing indexes on the table to extract data for columns covered by the index where the ROWID falls inside the corrupt block. This is described towards the end of the ROWID range scan articles mentioned above: For Oracle8/8i see Note:61685.1 For Oracle7 see Note:34371.1 - It may be possible to use LogMiner on the redo stream to find the original inserts/updates which loaded the data to the problem block. The main factor here is WHEN the data was actually put in the block. eg; row 2 may have been inserted yesterday but row 1 may have been inserted 5 years ago.

(4D) Leaving A Corruption In Place

It is possible to leave a corruption in place and just accept the errors reported, or prevent access to the problem rows at an application level. eg: If the problem block / row is in a child table then it may be possible at application level to prevent access via the parent row/s such that the child rows are never accessed. (Be wary of cascade type constraints though) This may not help with reports and other jobs which access data in bulk so it may also be desirable to use the DBMS_REPAIR options shown in 4C above to prevent the block/s erroring when accessed. Marking a corruption like this and leaving it around may give a short term solution allowing full data salvage and/or recovery to be attempted at scheduled outage, or allowing time to check other recovery options on a second (clone) database. Note though that marking a block corrupt with DBMS_REPAIR.FIX_CORRUPT_BLOCKS will cause the marked block/s to also be corrupt after recovery through the time that FIX_CORRUPT_BLOCKS was executed. Leaving a corruption may be sensible for data which rapidly ages and is subsequently purged (eg: In a date partitioned table where older partitions are dropped at some point). Leaving Corruptions in LOB segments At application level it can be possible to leave a corrupt LOB column in place until such time as the table can be rebuilt. One way to ensure you do not hit the "WARNING" scenario above is to ensure that the table is only ever accessed via a view which includes a WHERE predicate to prevent the problem row/s from being seen. eg: Consider table MYTAB( a number primary key, b clob ) has one or more rows pointing at corrupt LOB data. ALTER TABLE MYTAB ADD ( BAD VARCHAR2(1) ); CREATE VIEW MYVIEW AS SELECT a,b FROM MYTAB WHERE BAD is null; Set BAD='Y' for any problem row/s If you only access MYTAB via MYVIEW and the row will never be visible and so cannot be updated keeping the corrupt entry isolated until it can be dealt with. Clearly this example is more of a design time solution but some applications may already have similar mechanisms and may only access data via a view (or via an RLS policy) giving some option/s to hide the problem row/s. Warnings when Leaving a Corruption in Place Whilst it is possible to leave a corruption in place it should be noted that the corrupt blocks will still show up in runs of DBVERIFY, in RMAN backup warnings / errors etc.. It is important to make a careful record of any corruption you expect to see from these tools, particularly any blocks you expect to skip with RMAN (eg: having MAX_CORRUPT set) and be sure to remove any "acceptance" of the errors once the corruptions have been cleared. eg: Consider that a corrupt block has been handled by leaving the corruption in place and avoiding the problem row/s at application level. RMAN may be configured to allow the corruptions during backup. The table is then recreated at a later date during some table reorganisation. If RMAN is not updated to reflect that no errors should now be expected then RMAN may ignore some other corruption which occurs at a later time. It is also important to note that leaving corrupt blocks around in table segments can lead to mismatched results from queries eg: different results can occur for tables with SKIP_CORRUPT set depending on whether an index scan or table access occurs. Other reports may just error . Note that leaving a corruption in place but marking the block with DBMS_REPAIR.FIX_CORRUPT_BLOCKS writes redo to corrupt the block which may limit subsequent recovery options.

(4E) Last Options

If you have a standby setup (physical or logical) then check that first. Whatever sort of block the problem occurred on, one possible option is to recover the database, or problem tablespace, to a point in time BEFORE the corruption appeared. The difficulty with this option is that it is not always possible to know when the problem first appeared. DBVERIFY can be often be used to check a restored file for corruptions. For details of using DBVERIFY see Note:35512.1 . In particular the START= / END= DBV options can be used to give a quick first test of whether the problem block itself is bad on a restored backup image. This section outlines some final options available for recovering. If you have come here then one or more of the following have happened: - You have lost a "vital" datafile (or have a corruption on it) and have no good backup of the problem file/s (without the corruption) - Are either not in ARCHIVELOG mode OR do not have all archivelogs since the file was first created - Complete recovery keeps reintroducing the problem Last chance: Please note if you have lost all copies of a datafile but DO still have the ARCHIVE logs from when the file was first created it is still possible to recover the file. Eg: ALTER DATABASE CREATE DATAFILE '....' [as '...'] ; RECOVER DATAFILE '....' ALTER DATABASE DATAFILE '....' ONLINE; If you are in this scenario try to recover the datafile using these steps before proceeding below. If you have reached this line there are no options left to recover to the current point in time. It is advisable to shutdown the instance and take a BACKUP of the current database NOW in order to provide a fall-back position if the chosen course of action fails. (Eg: if you find your backup is bad). Some outline options available are: Revert to an old COLD backup - eg: If in NOARCHIVELOG mode Set up a clone database from a COLD backup - and extract (export) the problem table/s or transport the problem tablespace Point in time recovery to an older point in time that is consistent - requires a good backup and any necessary archive logs - ALL files have to be restored and the whole DB rolled forward to a suitable point in time. - It may be possible to do the point in time recovery in a clone database and then transport the problem tablespace to the problem database, or export / import the problem table from the clone to the problem database . Tablespace point in time recovery - It may be possible to perform a point in time recovery of the affected tablespace only. There are many notes describing tablespace point in time recovery such as Note:223543.1. Rebuild of DB from some logical export / copy - Requires there to already be a good logical backup of the database - NB: You have to RE-CREATE the database for this option. - As with other options the rebuild could be in a clone database just to get a good image of the problem table/s. If you have a good backup then rolling forwards with DB_BLOCK_CHECKING=TRUE can help find the first point in time where something started to go wrong. It is not generally necessary to take the problem database down while investigating the recovery options. eg: You can restore the system tablespace and problem tablespace datafiles only to a totally different location and/or machine as a different instance to investigate how far you can roll forwards etc.. As of Oracle9i you can also use "Trial Recovery" options to save having to keep restoring a backup while looking into your options.



Script To Run DBV On All Datafiles Of the Database

 
This script will simplify the task of running DBV on the all datafiles of a database.
When user experiences ORA-01578 / ORA-08103 or any other kind of corrupt messages this utility can be used to scan the datafiles at OS level.