Automated Database Maintenance Tasks

Oracle 11g includes three automated database maintenance tasks:

1.   Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects.

2.   Automatic Segment Advisor - Identifies segments that could be reorganized to save.

3.   Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL.

You, as a DBA, should control the following:

·         Adjust the duration and start time of the maintence window appropriately

·         Control the restore plan that allocates to the automated maintenance tasks

·         Enable / disable individual tasks appropriate to your environment. 

Enterprise Manager is the preferred way to control these tasks, but you can always use the DBMS_AUTO_TASK_ADMIN package like the example below..

exec dbms_auto_task_admin.disable(  'auto optimizer stats collection', null, :window);

exec dbms_auto_task_admin.disable('auto space advisor', null, :window);

exec dbms_auto_task_admin.enable(  'auto optimizer stats collection', null, :window);

exec dbms_auto_task_admin.enable(  'auto space advisor', null, :window);

BEGIN
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
END;

col client_name format a32, mean_job_duration format a32

set linesize 96

Select client_name, mean_job_duration, resource_percentage
FROM DBA_AUTOTASK_CLIENT

To change a task execution’s duration:

The value of the TIME_LIMIT parameter determines the total time allowed for a task execution.  By default its value is 60 minutes.

select parameter_value

from   dba_advisor_parameters

where  task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name = 'TIME_LIMIT';

PARAMETER_VALUE

-------------------------------------------------------------------------

3600

To change it to 30 mintues:

exec dbms_sqltune.set_tuning_task_parameter(  'SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 1800);

SQL> select parameter_value

  2  from   dba_advisor_parameters

  3  where  task_name = 'SYS_AUTO_SQL_TUNING_TASK' and

  4         parameter_name = 'TIME_LIMIT';

PARAMETER_VALUE

---------------------------------------------------------------------

1800

SQL> exec dbms_sqltune.execute_tuning_task('SYS_AUTO_SQL_TUNING_TASK');

PL/SQL procedure successfully completed.

SCAN - Sample EZConnect and Thin JDBC Connect Strings

The main benefit of using SCAN is that you don't need to change connect string when adding or removing nodes in the 11gR2 Oracle cluster.  Below are few examples of how to connect to a database using SCAN.

sqlplus anguyen/an123@//lab-scan:1521/TOOLS_RAC

si01.an.com> oracle: sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 22 10:40:24 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter user-name: anguyen/an123@//lab-scan:1521/TOOLS_RAC

sqlplus anguyen@\"lab-scan:1521/TOOLS_RAC\"


JDBC thin URL

jdbc:oracle:thin:@mydr-scan:1521/TOOLS_RAC

Performance Reference

Dynamic memory

select substr(COMPONENT, 0, 10) COMP, CURRENT_SIZE CS, USER_SPECIFIED_SIZE US from v$memory_dynamic_components where CURRENT_SIZE!=0;

select substr(COMPONENT, 0, 10), FINAL_SIZE, OPER_TYPE, OPER_MODE, status from v$memory_resize_ops order by START_TIME;

set echo off

Look up table, index, and colum statistics

select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len

from user_tables

where table_name = '&1';

select index_name, last_analyzed ANALYZE_TIME, num_rows,

       leaf_blocks, distinct_keys

from user_indexes

where table_name = '&1'

order by index_name;

select column_name, last_analyzed ANALYZE_TIME, num_distinct,

       num_nulls, density

from user_tab_columns

where table_name = '&1'

order by column_name;

set echo on

Explain Plan

--

explain plan for select * from mytab where my_sid_id > 0;

select plan_table_output from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

Select * from table(dbms_xplan.display());

Show pending statistics

select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len

from user_tab_pending_stats

where table_name = '&1' and partition_name is null;

select index_name, last_analyzed "analyze time", num_rows,

       leaf_blocks, distinct_keys

from user_ind_pending_stats

where table_name = '&1' and partition_name is null

order by index_name;

select column_name, last_analyzed "analyze time", num_distinct,

       num_nulls, density

from user_col_pending_stats

where table_name = '&1' and partition_name is null

order by column_name;

col table_name format a10

col extension format a12

Stat extensions:

select * from user_stat_extensions;

-- Create extention on the 2 columns

select dbms_stats.create_extended_stats(null,’mytab’,’(col1,col2)’) from dual;

select * from user_stat_extensions;

-- Collect stats

begin

  dbms_stats.gather_table_stats(null, 'mytab',

    method_opt => 'for all columns size 1 for columns (col1,col2) size 254');

end;

/

Flashback and Restore Point

Starting 11gR2, you can enable / disable flashback while the database is open.  You can monitor flashback database using the SOFAR and TOTALWORK columns of v$SESSION_LONGOPS

SQL> select status from gv$instance;

STATUS

------------

OPEN

OPEN

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

SQL> create restore point Golden_DB guarantee flashback database;

Restore point created.

RMAN> list restore point all;

using target database control file instead of recovery catalog

SCN              RSP Time  Type       Time      Name

---------------- --------- ---------- --------- ----

596770000000                          25-SEP-10 RESTORE2

5974114096614              GUARANTEED 21-JUL-10 GOLDEN_DB

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

RESTORE POINT ONLY

Generate a range of AWR reports

The below procedure was originally written by someone else to grab a range of AWR reports in text format and send them to a directory.  You just need to submit the snap_id, inst_id, inst_name, and the directory where the files are sppoled to.  I found it useful and hope you like it too.

create or replace PROCEDURE CreateAwrReports (end_snap number,begin_snap number,dbid_num number,inst_num number,inst_name varchar2, directory varchar2 )

as

     v_Instance_number v$instance.instance_number%TYPE;

     v_Instance_name v$instance.instance_name%TYPE;

     v_instname v$instance.instance_name%TYPE;

     v_dbid V$database.dbid%TYPE;

     v_instnum v$instance.instance_number%TYPE;

     v_file UTL_FILE.file_type;

BEGIN

     SELECT instance_number, instance_name

     into v_Instance_number,v_Instance_name

     FROM   gv$instance

     ORDER BY 1;

     v_dbid := dbid_num;

     v_instnum := inst_num;

     v_instname := inst_name;

     EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR AS '''||directory||'''');

   FOR i IN begin_snap..end_snap-1 LOOP

         BEGIN

             --Creating and Naming the file:

             v_file := UTL_FILE.fopen('TEMP_DIR', 'awr_' || v_instname ||'_'|| v_instnum  || '_' || i || '_' || (i+1) || '.txt', 'w', 32767);

             FOR c_AWRReport IN (

                 SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT( v_dbid, v_instnum ,  i, i+1))

                        ) LOOP

                         --Writing the AWR HTML report content to the file:

                         UTL_FILE.PUT_LINE(v_file, c_AWRReport.output);

                        END LOOP;

             --Closing the file:

             UTL_FILE.fclose(v_file);

         END;

    END LOOP;

CRS-2675

crsctl stop cluster -all

CRS-2675: Stop of 'ora.DATA.dg' on 'si01' failed

Checking the ASM alert log

NOTE: [emcrsp.bin@si01.an.com (TNS V1-V3) 20111] opening OCR file

NOTE: [emcrsp.bin@si01.an.com (TNS V1-V3) 20115] opening OCR file

NOTE: [emcrsp.bin@si01.an.com (TNS V1-V3) 20119] opening OCR file

Some notes about this issue:

1. This Bug is fixed in 11.2.0.3 and above.  If patch 9897335 is unavailable, the warning can be   ignored as it posts no harm.

2. You can't connect as sysasm and shut it down

SQL> connect / as sysasm

Connected.

SQL> shutdown immediate;

ORA-15097: cannot SHUTDOWN ASM instance with connected client (process 10871)

SQL> set linesize 132

SQL> select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAME                                                    DB_NAME  STATUS

------------ ---------------------------------------------------------------- -------- ------------

SOFTWARE_VERSION                                             COMPATIBLE_VERSION

------------------------------------------------------------ ---------------------------------------

           1 +ASM1                                                            +ASM     CONNECTED

11.2.0.2.0                                                   11.2.0.2.0

           3 +ASM1                                                            asmacfs   CONNECTED

11.2.0.2.0                                                   11.2.0.2.0

NEVER To shutdown ASM or kill -9.  CRS need to be shutdown: crsctl stop crs

How to do backup and Restore of Oracle statistics

Optimizer statistics backup and restore:

1)            Create stat table from source database.

EXEC DBMS_STATS.create_stat_table('SYS',' STATS_070509');

2)            Export stats to stats table created in step#1 

EXEC DBMS_STATS.export_schema_stats('SYS','STATS_070509',NULL,'DBASCHEMA');

This table can be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

                EXEC DBMS_STATS.import_schema_stats('SYS','STATS_070509',NULL,'DBASCHEMA');

You can run into issue with the statistics for hidden columns.  When we create INDEXES on column expressions (hidden columns like decode (column1,…)) ,  Oracle create system generated name like sys_100036 for each column expression.

Example :

SQL> select index_name,column_expression from dba_ind_expressions  where table_owner like ‘MY_APP’ and table_name like 'AN_FILE'  and index_name IN ('AN_FILE_N1','AN_FILE_N4');

INDEX_NAME                                    COLUMN_EXPRESSION

--------------------------------------------------------------------------------

AN_FILE_N1                                       UPPER("FILE_EXTERNAL_ID")

AN_FILE_N4                                       TRUNC("ADJ_FINALIZE_DTS")

SQL> select index_name,column_name from dba_ind_columns where table_owner like ‘MY_APP’and table_name like 'AN_FILE'  and indeX_name IN ('AN_FILE_N1','AN_FILE_N4');

INDEX_NAME                                    COLUMN_NAME

---------------------------------------------------------------------

AN_FILE_N1                                       SYS_NC00037$

AN_FILE_N4                                       SYS_NC00038$

As the hidden column names are system-generated as shown above, oracle will give them different names in different databases based on whatever random number is available at the time of index creation on hidden column in that database.  When we import the exported statistics as explained in step#3 above  and  if oracle does not find same name for hidden column of the index in destination database  as it is mentioned in the exported statistics of index or in the source database ,then import_stats command silently skips the import of histogram information or statistics information on the hidden column of the index. This will cause difference in statistics and histogram information for that column or index.

Example :

SQL> select table_name,column_name,histogram from dba_tab_cols

  where owner like ‘MY_APP’   and (hidden_column='YES' or virtual_column= 'YES') order by table_name,column_name  ;

Production database:

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM

AN_TITLE_FEE                       SYS_NC00045$                     FREQUENCY

QA database:

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM

AN_TITLE_FEE                       SYS_NC00051$                   FREQUENCY

From above-mentioned outputs , it seems that  production has hidden column name 'SYS_NC00045$  and Load test has a name called “SYS_NC00051$”  for same hidden column.

SQL> select index_name,column_name from dba_ind_columns where table_owner like ‘MY_APP’and table_name like 'AN_TITLE_FEE'  and column_name  like ‘'SYS_NC00045$ ‘

INDEX_NAME                                    COLUMN_NAME

AN_TITLE_FEE_N4                           SYS_NC00045$

AN_TITLE_FEE_N9                           SYS_NC00045$

SQL> select index_name,column_name from dba_ind_columns where table_owner like ‘MY_APP’and table_name like 'AN_TITLE_FEE'  and column_name  like ‘'SYS_NC00051$ ‘

INDEX_NAME                                    COLUMN_NAME

AN_TITLE_FEE_N9                           SYS_NC00051$

AN_TITLE_FEE_N4                           SYS_NC00051$

Now you can update the stats table to correct the hidden column name

SQL> update MY_APP.stats_070509 set c4='SYS_NC00051$' where c1 like 'AN_TITLE_FEE' and c4 like 'SYS_NC00045$';

SQL> commit;

Then import the statistics in QA environment  or any destination environment

EXEC DBMS_STATS.import_schema_stats('SYS','STATS_070509',NULL,'DBASCHEMA');

How to create a database using dbca silent method

The content of dbca silent file will look like this..                            

cat dbca_silient

dbca -silent                             \

       -createDatabase                   \

       -templateName General_Purpose.dbc \

       -gdbName RACDBLAB                   \

       -sid RACDBLAB \

       -SysPassword Yourpasswd \

       -SystemPassword Yourpasswd \

       -emConfiguration NONE             \

       -redoLogFileSize 100 \

       -recoveryAreaDestination FRA_DG\

       -storageType ASM                  \

         -asmSysPassword Yourpasswd \

         -diskGroupName DATA_DG\

       -characterSet AL32UTF8         \

       -nationalCharacterSet AL16UTF16 \

       -totalMemory 2400 \

       -databaseType MULTIPURPOSE \

       -nodelistsi01.an.com

Then run it ksh dbca_silent

Copying database files

1% complete

3% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

96% complete

100% complete