Database RAC DataGaurd and APPS

How to rename SQL Tuning Set ( STS )

2012-01-29T00:16:00.020-05:00

Rename SQL Tuning Set‎( STS )

We can use SQL Performance Analyzer (SPA) to evaluate impact on SQLs of 11g R2 database upgrade from 10.2 .So created some demo , example ( with full code ). EM was very good option , but still preferred sql,pl-sql, command / api.

We all know to, use SPA for 10.2 to 11.2 database upgrade , we need one intermeiate 11g DB.

So we can run trial remotely.So we collected SQL Tuning Set (STS ) ( using demo code ) from Multiple databases and import on intermediate 11g DB. Problem started when unpacking STS, collected from different production / Test environment having same name because all

used demo code :).

So started looking for solution , is there any way to rename STS !!!! and end up with following code

-- Create new STS and copy from exiting one
DECLARE
l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
-- Create STS
DBMS_SQLTUNE.create_sqlset(sqlset_name  => 'STS_DBNAME_CMUL',description  => 'new STS SQL tuning set from XYZ DB');
OPEN l_cursor FOR
SELECT VALUE(r)
FROM   TABLE (DBMS_SQLTUNE.select_sqlset (
                'STS_RAG_CMUL'        -- old sqlset_name which need to rename
                                         )         
              ) r;
DBMS_SQLTUNE.load_sqlset (
sqlset_name     => 'STS_DBNAME_CMUL',
populate_cursor => l_cursor);
-- Drop STS
dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
close l_cursor;
END;
/

Sangam’10: All-India Oracle Users Group (AIOUG) Annual Conference

2010-09-05T11:23:00.024-05:00

Sangam’10: All-India Oracle Users Group (AIOUG) Annual Conference

The All India Oracl Users Group (AIOUG) presented Sangam 2010, a two-day(3-4-Sep-2010) Oracle Users Conference held in Hyderabad. Sangam 2010 hosted multiple sessions by international and Indian experts like Jonathan Lewis , Rittman , Iggy Fernandez etc.

Jonathan Lewis & Virag Sharma

The event was very good and opportunity to learn from experts. Sangam 2010 started with Murali welcome note followed by Presentation, by Roland Slee, Vice-President, Database Product Management, Oracle Asia Pacific & Japan.

First technical session was from Jonathan Lewis on "Writing Optimal SQL". This One day Presentation was divided in to two , 1/2 day sessions. Conference room packed for his sessions, Jonathan Lewis Presentation was main attraction for Sangam 2010. After Lunch , there were 2 Conference room for different sessions and audience can choose session as per there interest.

Sponsors "OSI Consulting" presentation on "Cross Platform migration challenges and time reduction techniques" was pretty good then expected. After "OSI Consulting" presentation, RAC SIG meeting held. In this meeting Satyendra Kumar , explained things about RAC SIG. In the end of "RAC SIG" meeting Satyendra looking leader for various city , for Delhi/NCR, I proposed Aman Sharma’s name and latter learned that Aman recently become Oracle ACE.

Session “Tips and Best Practices for DBA’s” from Francisco , was in soo much demand that Conference room 2 overflowed and finally session held in Conference room 1.

Rittman and Vivek took one session in Sangam 2010 and unfortunately , Not able to attend any of them.In the end of Sangam 2010 Oracle Users Conference, Jonathan Lewis took 1 Hr question and Answer session, that was pretty good.

Certain things in this world

2010-04-13T09:03:00.000-05:00

"In this world nothing can be said to be certain but death and taxes."
--Benjamin Franklin

Module name for logon trigger in 11g R2 AWR report

2009-09-28T00:53:00.008-05:00

Today while working on Production performance tuning, We came across one difference in awr report of 11g R1 and 11R2

In oracle 11g R1 AWR report logon trigger modelue name usually come like
sqlplus / perl / Mid Tier@server_name_from_it_login

But in 11g R2 AWR report logon trigger modelue name usually come like oraagent.bin@DB_SERVER_WHERE_IT_RUN

New change looks more logical because DB server running logon trigger code ,
not the user.

# Excerpt from 11g R1 AWR report
# In below given example app678 is server name from where
# user logged in to database using sqlplus

Module: sqlplus@app678utl (TNS V1-V3)
UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1

#Excerpt from 11g R2 AWR report
#In below given example apps001 is database server

Module: oraagent.bin@apps001 (TNS V1-V3)
UPDATE scott.MY_AUDIT SET LAST_LOGIN = SYSDATE WHERE XYZ_USERNAME = :B1

Oracle Database 11g Release 2 New Features : Edition based redefination

2009-09-04T08:33:00.008-05:00

Oracle Database 11g Release 2 New Features : Edition based redefination
Virag Sharma

Every release has some Major changes , which we usually says New Features.Some of these features dominate the version, For example 11g R1 has SPA , DB Replay Active standby etc. Same this Oracle Release ( Oracle Database 11g Release 2 ) has some New features for which this release will be known in feature. These features are "Edition based redefination"

Most likely these features designed to give big support to APPS upgrade ( ie Oracle E-Business suite upgrade). When you upgrade APPS database , it need lots of down time , hope, using these new features APPS upgrade will take less time in future.

This feature will allow application upgrade( AS DBA , i would prefer to say Online Database object upgrade) with Minimum down time or may be zero down time. I consider this feature as one step toward ZERO DOWN time for application upgrade.

In 10g statistics collected on table published immediatly, That usually cause lots of performance issue. In oracle 11g r1 there is feature, for collecting stats on tables and publishing stats , as per need to avoid performance issue due to stats collection.

Taking similar feature to next step , 11g R2 has feature "REDEFINITION" , which upgrades objects , but not published immediately, also database can have multiple Editions of objects definition. Of-course there are, some limitation

Check Default Edition

SQL>
1 SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
2* Where PROPERTY_NAME = 'DEFAULT_EDITION'
SQL> /

PROPERTY_VALUE
--------------------------------------------------------------------------------
ORA$BASE

Changing Edition at session or Database level

SQL> ALTER SESSION SET EDITION=ora$base;

Session altered.

SQL> ALTER DATABASE DEFAULT EDITION =ora$base;

Database altered.

Grant create or drop edition to user

SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to virag;

Grant succeeded.

Enable Edition on schema / User

SQL> ALTER USER virag ENABLE EDITIONS force;

Of-course there are, some limitation......

Will add more ......soon , for How to...

Reference

19 Edition-Based Redefinition

More Post on Oracle RDBMS Database 11g R2 ( Release 2 )

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name
11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Oracle Database 11g Release 2 New Features : Edition based redefination

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name

2009-09-02T19:32:00.010-05:00

Oracle 11g R2 (11.2 ) New Features : SCAN - Single Client Access Name
Virag Sharma

Once you decided to add/remove node from RAC database/cluster. We need to change tns
entry. Oracle 11g R2 introduced new concept called Single Client Access Name (SCAN).
Which eliminate the need to change tnsnetry when nodes are added to or removed from
the Cluster.

RAC Instances register to SCAN listeners as remote listeners. SCAN is fully qulified name.
Oracle recommends to assign 3 address to SCAN , which create three SCAN listeners.

$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node apps001
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node apps002
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node apps002

Running following command on Node 2 (apps002)

$ ps -aef |grep -i SCAN

oracle 9380 1 0 Aug13 ? 00:01:09 /d01/apps/oracle_crs/11.2/bin/tnslsnr LISTENER_SCAN2 -inherit

oracle 9380 1 0 Aug13 ? 00:01:09 /d01/apps/oracle_crs/11.2/bin/tnslsnr LISTENER_SCAN3 -inherit

oracle 9993 7114 0 09:57 pts/3 00:00:00 grep -i crs

From above output, it is clear that SCAN listener is running from CRS_HOME

$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

$ srvctl config scan
SCAN name: apps-scan, Network: 1/192.168.182.0/255.255.255.0/
SCAN VIP name: scan1, IP: /apps-scan.us.oracle.com/192.168.182.109
SCAN VIP name: scan2, IP: /apps-scan.us.oracle.com/192.168.182.110
SCAN VIP name: scan3, IP: /apps-scan.us.oracle.com/192.168.182.108

tns entry can use single address ( SCAN Name ) in tnsentry , instead os using entry for all Node

tns entry configured to use VIP addresses for Database will work without any issue. using
SCANs is not Medatory ( May be to support backward compatibility )

#
# TNS ENTRY with SCAN
#

test.world =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST==apps-scan.world)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=R1211.world))
)

#
# TNS Entry without SCAN ( Old way)
#

test.world =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=apps001-vip.world)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=apps002-vip.world)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=R1211.world)))
)
)

Clients Can connect to a particular instance of the database using SCAN. Entry will looks like

test.world =
(description=
(address=(protocol=tcp)(host=apps-scan.world)(port=1521))
(connect_data=
(service_name=R1211.world)
(instance_name=apps1cl1)))

tns entry configured to use VIP addresses for Database will work without any issue. using

SCANs is not Medatory ( May be to support backward compatibility )

More Post on Oracle RDBMS Database 11g R2 ( Release 2 )

11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT

2009-09-02T11:08:00.031-05:00

11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Virag Sharma

In earlier version there is no standard way to change AUDIT tables tablespace.
IN Oracle 11g R2 ( Also included in 11.1.0.7 and 10.2.0.5 ( Need to check)) , you can change audit table (SYS.AUD$ and SYS.FGA_LOG$) tablespace using DBMS_AUDIT_MGMT

Not ONLY you can change audits table tablespace , now you can periodically deleting the audit trail records using CLEAN_AUDIT_TRAIL (new in 11.2 ) Procedure.

So Now it is official , that you can change AUD$ table tablespace and purge :-) .

In Below given example , am trying to change tablespace for AUD$

Checking current tablespace from AUD$

SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$';

TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSTEM

Changing Tablespace from SYSTEM to SYSAUX for AUD$

SQL>

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');
END;
/

PL/SQL procedure successfully completed.

Checking changed Tablespace

SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$';

TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSAUX

AUDIT_TRAIL_TYPE: Refers to the database audit trail type. Enter one of the following values:

DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.

AUDIT_TRAIL_LOCATION_VALUE: Specifies the NEW destination tablespace.

Not ONLY you can change audits table tablespace , now you can periodically deleting the audit trail records/xml/.aud files etc using CLEAN_AUDIT_TRAIL Procedure.

STEPS for Purging AUDIT TRAIL

# Check initialization

BEGIN
IF
NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
dbms_output.put_line('CLEANUP NOT INITIALIZED' );
ELSE
dbms_output.put_line('CLEANUP INITIALIZED' );
END IF;
END;

# Set initialization

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 6 );
END;

# Set Last Audit Time stamp

SQL> desc DBA_AUDIT_MGMT_LAST_ARCH_TS
Name Null? Type
----------------------------------------- -------- ----------------------------
AUDIT_TRAIL VARCHAR2(20)
RAC_INSTANCE NOT NULL NUMBER
LAST_ARCHIVE_TS TIMESTAMP(6) WITH TIME ZONE

SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

# Check is Last Audit Time stamp set or not

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
LAST_ARCHIVE_TIME => sysdate -30 <------ want to delete aud file older then 30 days
RAC_INSTANCE_NUMBER => 1 );
END;

# For non RAC don't use "RAC_INSTANCE_NUMBER =>"
# If RAC system having 4 node then run above command 4 time
# with RAC_INSTANCE_NUMBER 1 , 2, 3 ,4

# Manual Purge

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
USE_LAST_ARCH_TIMESTAMP => TRUE);
END;

#
# If USE_LAST_ARCH_TIMESTAMP is False , it purge all audit trail
#

# Here we used DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL =>
# All audit trail types. This includes the standard database audit trail
# (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail,
# and XML audit trail. More details are given below
#AUDIT_TRAIL_ALL => All audit trail types. This includes the standard database audit trail (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail, and XML audit trail.
#AUDIT_TRAIL_AUD_STD => Standard database audit records in the SYS.AUD$ table
#AUDIT_TRAIL_DB_STD => Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records
#AUDIT_TRAIL_FGA_STD => Standard database fine-grained auditing (FGA) records in the SYS.FGA_LOG$ table
#AUDIT_TRAIL_FILES => Both operating system (OS) and XML audit trails
#AUDIT_TRAIL_OS => Operating system audit trail. This refers to the audit records stored in operating system files.
#AUDIT_TRAIL_XML => XML audit trail. This refers to the audit records stored in XML files.

Refrence

DBMS_AUDIT_MGMT ( Oracle Documentation 11.2 )

More Post on Oracle RDBMS Database 11g R2 ( Release 2 )

From Oracle to a Successful Vineyard

2009-08-16T11:33:00.008-05:00

Last Month , I was there in Nasik ( One Small Town near Mumabi ). We roam around the city and visited many places in town. Our Driver suggested to visit Sual Vineyard. I am not very found of wine ,

But had very good time with friends in Napa Valley, CA, US, So thought let me experience Indian Vineyard.In Vineyard , we learned that owner of Sula Vineyard Mr. Rajeev is graduated in Economics and Industrial Engineering from Stanford and was working with Oracle in the Silicon Valley. Latter he left

Job and started Vineyard in India , today Sula is one of the well know Wine band in India. Story look interesting , so thought, to share with all(Links are given below ).

From Oracle to a Successful Vineyard: Rajeev Samant of Sula Wines

http://www.delhiwineclub.com/Interview/interview_with_rajeev2.asp
http://www.pagalguy.com/plugins/p2_news/printarticle.php?p2_articleid=912

NEW option in ADRCI purge acommand - UTSCDMP

2008-12-31T12:40:00.005-05:00

NEW option in ADRCI purge acommand - UTSCDMP
There is new option in ADRCI for purge command - UTSCDMP

adrci> help purge

Usage: PURGE [[-i ]
[-age [-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]]]:

Purpose: Purge the diagnostic data in the current ADR home. If no
option is specified, the default purging policy will be used.

Options:
[-i id1 id1 id2]: Users can input a single incident ID, or a range of incidents to purge.
[-age ]: Users can specify the purging policy either to all the diagnostic data or the specified type. The data older than ago will be purged

[-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]:

Users can specify what type of data to be purged.
Examples:

purge
purge -i 123 456
purge -age 60 -type incident

There where some issue that directories cdmp_* in repositories (=$diagnostic_dest/Diag/rdbms/database_name/instance_name/bdump ) are not purging automatically. When you run following command, it remove cdmp_* directories, which is older the 3600 Minutes

adrci> purge -age 3600 -type UTSCDMP

AIOUG - TechNight in Bangalore

2008-10-06T23:27:00.004-05:00

The All India Oracle User Group with a 4 lakh -strong developer base and a community of seven-lakh users in India is organising a second meeting in Bangalore on October 22 to share experiences and provide insights into shape of things to come. It is the second largest technology network of professionals for Oracle after the US. Soon these TechNights will be held in other parts of India, such as Pune, Mumbai and Delhi.

Like several successful Oracle user groups around the world, AIOUG is also a not for profit organization formed by Oracle users for Oracle users. Let’s join hands across the country to make AIOUG a global lead user group.

AIOUG first TechNight held at Hyderabad, Please click here to see TechNight pictures

TechNight All India Oracle User Group (AIOUG)

2008-07-19T11:48:00.009-05:00

AIOUG started his first ever Technical session in India at Hyderabad… On … Friday, July 18th 2008, between 5:00 PM to 8:00 PM. Session started with quick introduction about AIOUG, there mission goal by Murali Vallath. Latter Phani Arega took technical session on "Efficient SQL Programming - Some Tricks and Tips" and Vivek Sharma on " Real Time Performance Tuning".

Both the technical session was quite good and they shared lots of real life examples in there presentation. Here is some photos from AIOUG TechNight

Murali Vallath

Phani Arega

Vivek Sharma

Satyendra Kumar & PS Janakiram

DBMS_STATS Enhancements in Oracle 11g Database

2008-04-30T07:14:00.015-05:00

DBMS_STATS Enhancements in Oracle 11g Database
Virag Sharma virag123@gmail.com

We know in 10g and 11g there is automatic job that collect stats of database based on certain preferences settings , lets have a look what are these preferences and what are there default values and how DBMS_STATS is different in 11g from 10g

Changing Preferences for Statistics

Preference name	Default Values
CASCADE	AUTO
DEGREE	AUTO
ESTIMATE_PERCENT	AUTO
METHOD_OPT	FOR ALL COLUMN SIZE AUTO
NO_INVALIDATE	AUTO
GRANULARITY	AUTO
PUBLISH ( New in 11g)	TRUE
INCREMENTAL new in 11g	FALSE
STALE_PERCENT (New in 11g)	10

#
# In Oracle 10g
#

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 6 19:04:57 2008

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL>

SQL> SELECT dbms_stats.get_param('method_opt') FROM dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> SELECT dbms_stats.get_param('GRANULARITY') FROM dual;

DBMS_STATS.GET_PARAM('GRANULARITY')
--------------------------------------------------------------------------------
AUTO

SQL> SELECT dbms_stats.get_param('NO_INVALIDATE') FROM dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL> SELECT dbms_stats.get_param('DEGREE') FROM dual;

DBMS_STATS.GET_PARAM('DEGREE')
--------------------------------------------------------------------------------
NULL

SQL> SELECT dbms_stats.get_param('CASCADE') FROM dual;

DBMS_STATS.GET_PARAM('CASCADE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
---------------------------------------------------------------------
100

SQL> exec dbms_stats.RESET_PARAM_DEFAULTS();

PL/SQL procedure successfully completed.

SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
---------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

GET_PARAM , RESET_GLOBAL_PREFS_DEFAULTS and SET_PARAM are obsolete in Oracle 11g.

In place of above procedures need to use following procedures GET_PREFS , RESET_GLOBAL_PREF_DEFAULTS and SET_GLOBAL_PREFS

SQL> Select dbms_stats.GET_PREFS('CASCADE') from dual;

DBMS_STATS.GET_PREFS('CASCADE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

SQL> Select dbms_stats.GET_PREFS('DEGREE') from dual;

DBMS_STATS.GET_PREFS('DEGREE')
--------------------------------------------------------------------------------
NULL

SQL> Select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual;

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> Select dbms_stats.GET_PREFS('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> Select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual;

DBMS_STATS.GET_PREFS('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL> Select dbms_stats.GET_PREFS('GRANULARITY') from dual;

DBMS_STATS.GET_PREFS('GRANULARITY')
--------------------------------------------------------------------------------
AUTO

SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual;

DBMS_STATS.GET_PREFS('PUBLISH')
--------------------------------------------------------------------------------
TRUE

SQL> Select dbms_stats.GET_PREFS('INCREMENTAL') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL')
--------------------------------------------------------------------------------
FALSE

SQL> Select dbms_stats.GET_PREFS('STALE_PERCENT') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT')
--------------------------------------------------------------------------------
10

In Oracle 11g you can set preference not only on database level but on global level , schema level , table level as well. It really give more control to DBA for example on one table you want histograms should collected always or want to set METHOD_OPT => ‘FOR ALL COLUMNS SIZE 254’ on some tables

Procedure for setting preference on global , database , schema and table level are given below

1. SET_GLOBAL_PREFS

2. SET_DATABASE_PREFS

3. SET_SCHEMA_PREFS

4. SET_TABLE_PREFS

Preference set on global apply for new objects or object which no preference available

Database level preference will be applied on all objects in the database excluding the tables owned by Oracle. These tables can included by passing TRUE for the add_sys parameter of procedure.

If you set preference value to NULL , it will set to Oracle default value

#
#Test case
#

SQL> create table test1(abc number);

Table created.

SQL> create table test2(abc number);

Table created.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1')
--------------------------------------------------------------------------------
10

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
10

SQL> execute dbms_stats.set_table_prefs('SYS', 'TEST2', 'STALE_PERCENT', '35');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '20');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1')
--------------------------------------------------------------------------------
20

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1')
--------------------------------------------------------------------------------
20

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> exec dbms_stats.set_database_prefs('STALE_PERCENT','30');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER')
from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER')
--------------------------------------------------------------------------------
30

SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2')
--------------------------------------------------------------------------------
35

SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER')
from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER')
--------------------------------------------------------------------------------
15

Published and Pending Statistics

Statistics published by default when system/user/job collect stats. In Oracle 11g we have option to put newly collect stats in pending state and published latter once satisfied by performance testing based on new statistics.

In 10g we have face performance issue when collected stats on OLTP (24 X 7 ) database, probably because stats of one index/ table published before other. So collect stats in pending stat , test it and once satisfied with testing published it.

SQL> col name form a40
SQL> col VALUE form a6
SQL> col ISSES_MODIFIABL VALUE form a6
SQL> col ISSES_MODIFIABL form a6
SQL> col ISSYS_MODIFIABLE form a12
SQL> select name,value,isses_modifiable,issys_modifiable
from v$parameter
where name='optimizer_use_pending_statistics' ;

NAME VALUE ISSES_MODIFIABL ISSYS_MODIFI
---------------------------------------- ------ --------------- ------------
optimizer_use_pending_statistics FALSE TRUE IMMEDIATE

#
# Test Case
#

# By default Publish is set TRUE
#

SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual
PUBLISH
---------
TRUE

SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish
from dual;

PUBLISH
------------
TRUE

#
# Setting Publish false for TEST1 table
#

SQL> exec dbms_stats.set_table_prefs('VIRSHARM', 'TEST1', 'PUBLISH', 'false');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish
from dual;

PUBLISH
-------------
FALSE

SQL> execute dbms_stats.gather_table_stats('VIRSHARM', 'TEST1');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed from user_col_pending_stats
where table_name ='TEST1'

TABLE_NAME LAST_ANALYZED
---------- ---------------
TEST1 30-APR-08

SQL> select table_name, last_analyzed from user_ind_pending_stats
where table_name ='TEST1';

no rows selected

SQL> select table_name, last_analyzed from user_col_pending_stats
where table_name ='TEST1';

TABLE_NAME LAST_ANALYZED
---------- ---------------
TEST1 30-APR-08

#
# Following views will have no record for table TEST1
# because stats not published
#

SQL> select table_name, last_analyzed from user_tables
where table_name = 'TEST1';

TABLE_NAME LAST_ANALYZED
------------------------------ -------------------
TEST1

SQL> select index_name, last_analyzed from user_indexes
where table_name ='TEST1';

no rows selected

SQL> select column_name, last_analyzed from user_tab_columns
where table_name='TEST1' ;

COLUMN_NAME LAST_ANALYZED
------------ ---------------
ABC

#
# Testing
#

SQL> ALTER SESSION SET optimizer_use_pending_statistics =TRUE;

Session altered.

#
# OR export / import STATS
#

SQL> exec dbms_stats.create_stat_table('VIRSHARM','STATS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_pending_stats(tabname=>'TEST1',stattab=>'STATS');

PL/SQL procedure successfully completed.

#
# Use expdp /impdp ot exp/imp to import stats in TEST/QA database
#

SQL> exec

dbms_stats.import_table_stats(ownname=>'VIRSHARM',tabname=>'TEST1',
stattab=>'STATS');

select column_name, last_analyzed from user_tab_columns
wheretable_name='TEST1';

#
# Published stats
# Publish the Stats to Data Dictionary for Optimizer Usage
#

SQL> exec dbms_stats.publish_pending_stats(tabname=>'TEST1');

PL/SQL procedure successfully completed.

If Stats published and performance become worse , then you can restore old version of stats. Oracle will manage the historical statistics repository, purging the statistics on a regular basis, by default every 31 days

By default Oracle keep stats for 31 day after that it purge. Retention can be increased useing following

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( 60 );

PL/SQL procedure successfully completed.

11g RAC Database hanged or appears to hanged

2008-04-24T06:07:00.010-05:00

11g RAC Database hanged or appears to hanged

Around lunch time 1:00 PM database hanged. When we tried to login as sysdba
our sqlplus session also hanged. we not able to login on either of node of RAC.
Thought to kill all the remote connection , so we will able to login

ps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9

When your database hanged and you not able to login, we have used following procedure
to create hang analysis dump

sqlplus /nolog

SQL> set _prelim on
SQL> connect / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all hanganalyze 3
Cycle 1: (1/1965)--(1/1839)
Hang Analysis in /u04/oraout/abcap/11gdiag/diag/rdbms/abcap/abc2ap/trace/abc2ap_diag_9269.trc

We also generate ASH report for that(=hang) duration.
Here is what , hang analysis show

#
# Session id is in RED colour for further analysis
#

Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle) Chain 1 Signature Hash: 0x75bdd0c

===============================================================================
Cycles:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (abcap.abc1ap)
os id: 27244
process id: 343, oracle@abc232
session id: 1839
session serial #: 12
}
is waiting for 'row cache lock' with wait info:
{
p1: 'cache id'=0x7
p2: 'mode'=0x0
p3: 'request'=0x5
time in wait: 0.526566 secs
heur. time in wait: 18.534318 secs
timeout after: 2.473434 secs
wait id: 49
blocking: 2 sessions
current sql:
short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kziasfc()+235<-kpolnb()+5279<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock'
wait id: 48 p1: 'cache id'=0x7
time waited: 3.001908 secs p2: 'mode'=0x0
p3: 'request'=0x5
2. event: 'row cache lock'
wait id: 47 p1: 'cache id'=0x7
time waited: 3.001615 secs p2: 'mode'=0x0
wait id: 47 p1: 'cache id'=0x7
time waited: 3.001615 secs p2: 'mode'=0x0
p3: 'request'=0x5
3. event: 'row cache lock'
wait id: 46 p1: 'cache id'=0x7
time waited: 3.000437 secs p2: 'mode'=0x0
p3: 'request'=0x5
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (abcap.abc1ap)
os id: 27941
process id: 125, oracle@abc232
session id: 1965
session serial #: 476
}
which is waiting for 'row cache lock' with wait info:
{
p1: 'cache id'=0x7
p2: 'mode'=0x0
p3: 'request'=0x3
time in wait: 0.462402 secs
heur. time in wait: 18.536906 secs
timeout after: 2.537598 secs
wait id: 30
blocking: 2 sessions
current sql:
short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kkdlgui()+186<-kziavdb.()+2023<-kziaia.()+220<-kpolnb()+580<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock' wait id: 29 p1: 'cache id'=0x7 time waited: 3.001776 secs p2: 'mode'=0x0 p3: 'request'=0x3 2. event: 'row cache lock' wait id: 28 p1: 'cache id'=0x7 time waited: 3.001578 secs p2: 'mode'=0x0 p3: 'request'=0x3 3. event: 'row cache lock' wait id: 27 p1: 'cache id'=0x7

When checked above session ( as marked in RED in above windows ) in ASH table.
We found both sessions are belong to “sys” user.

Lets have a look on ASH report, one which we created, when database was hanged

Top User Events

Event	Event Class	% Event	Avg Active Sessions
resmgr:cpu quantum	Scheduler	80.66	55.53
row cache lock	Concurrency	7.37	5.08
db file sequential read	User I/O	3.55	2.45
null event	Other	2.66	1.83
CPU + Wait for CPU	CPU	1.90	1.31

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
resmgr:cpu quantum	80.75	"1","0","0"	60.85	location
		"2","0","0"	17.81
		"3","0","0"	2.09
row cache lock	7.38	"10","0","3"	7.31	cache id	mode	request
db file sequential read	3.58	"59","23006","1"	0.20	file#	block#	blocks

So ASH report is showing different story , that mean database was not hanged , it appeared to hanged due to event “resmgr:cpu quantum”

As per

Oracle® Database Reference
11g Release 1 (11.1)
Part Number B28320-01

resmgr: cpu quantum

The session is waiting to be allocated a quantum of cpu. This event occurs when the resource manager is enabled and is throttling CPU consumption. To reduce the occurrence of this wait event, increase the CPU allocation for the sessions's current consumer group.

Wait Time: The time the session waited to acquire a CPU quantum

We have not enabled any resource plan , how it come into picture,In Oracle
11g all predefine maintenance windows use DEFAULT_MAINTENANCE_PLAN resource plan and Automatedmaintenance tasks run under its subplan ORA$AUTOTASK_SUB_PLAN supportEmptyParas]-->which has 25% resource allocation.

Possible Work around

Disable resource plan

Reactive performance management By Craig Shallahamer

2008-02-17T01:00:00.007-05:00

Last week Oracle University arrange Seminar by Performance Management guru Craig Shallahamer in India. Craig brings his unique experiences to many as a keynote speaker, a sought after teacher, a researcher and publisher for improving Oracle performance management, and also the architect of Horizone, OraPub's service level management solution.

His way of teaching is very very good , also contents of seminar is different and very effective. He focused on accurate performance diagnosis of even the most complex Oracle systems and how oracle interact with OS and application’s. I have been reading Craig’s papers/articles for long time , but never got full/big picture. After attending seminar his papers/articles look easy to understand.

Focus point of seminar was how to pin point issue area and different method to resolve it. He also cover some area of Mutex, IMU ( In memory Undo ) and performance forecasting etc. The way he teach latch Internals, anybody can understand it, even small kids J, he Create an honest and understandable story explaining the problem, the solutions, and how to best implement your solutions in a complex and highly available Oracle environment.

Second day of training , we had little tough time , as next to our conference hall in Oberoi Hotel, New Delhi , Shahrukh Khan( One of India Famous actor) was coming to inugrate/launch "Kya Aap Paanchvi Paas Se Tez Hain" new serial on Star Plus channel. But Craig enjoy music and continue with training

What CPU Jan 2008 Patch will not tell you

2008-02-05T10:55:00.001-05:00

What CPU Jan 2008 Patch will not tell you

File system space :- opatch suppose to check files system space as a part of Running prerequisite checks... and start apply patch when enough space is there.

Patching component oracle.rdbms, 11.1.0.6.0...

Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzsr.o"
Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzu.o"

The following actions have failed:

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzsr.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzu.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Do you want to proceed? [yn]

Well I came out with N option. When we started opatch again , it gave error some thing like spacecheck fail.

So make sure you have enough space on file system.

In case of RAC database, CPU patch readme.txt will not tell you that one need to set cluster_database = false , before starting database in upgrade mode(“startup upgrade”). For compiling views and objects.

So make sure, in RAC environment you have set cluster_database = false before starting database in upgrade mode

Secrets of Happiness

2008-02-03T11:26:00.001-05:00

Secrets of Happiness

I was traveling from my from Agra (= City of Taj Mahal, 30 Miles away from my home town ) to Hyderabad. My Train AP Express was late so thought to pick some book. As usual I picked some my favorites books/magazines like Reader Digest , Champak ( Famous Kids book in India ). While purchasing books/magazines, saw book with title “Secrets of Happiness - Tanushree Podder”. Title looks very odd to me because , I feel how one can define happiness. Well , Just picked the book and browse some page , it look good. It is not different from other book like “Mega Living- Robin Sharma” , “Who will Cry when you die Robin Sharma”. English look typical Indian English. Some of the stories we already heard in our childhood from Grand Mother , Mom , aunty etc. But it is really nice to re-visit those stories. Writer presentation look good and that make book more interesting. I started reading book from Agra and keep reading book till Gwalior, Everybody in train want to sleep , since I was reading book light was on and everybody eye brow in train getting tight. Finally switched off light , but finished book before reaching Hyderabad. I feel book is worth to read , that’s why thought to write about this book.

Check book excerpt [click Here]

Summary

What you put in life , you get back
No situation is good / bad / ugly , it is our believe that colored our perception about situation and we feel accordingly(good / bad / ugly) about situation. So change our believe , thought things will improve/change, otherwise same thought/believe same result
Keep It Simple and Straight ( KISS) .....................................

Apart from this there are two more book, that really worth to read

Monk Who Sold His Ferrari – Robin Sharma

Follow Your Heart - Andrew Matthews

I read above book frequently , and feel , If I would have got these book 6 Year Back ……. :-)

Small change make difference

2008-01-27T09:41:00.003-05:00

Small change make difference.

How small , small things make difference , here is one live example
One of my friend want to learn 11g, so she downloaded 11g and started installing on

Linux Box and created Database manually. Next time when she logged in, she did not know where she installed 11g , since she created database manually , so there was no entry in “oratab”

I remember command “pwdx” on Unix Solaris , which give current working directory of processes

$ uname -a

SunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-Enterprise

MYSUN:oracle> (10.1.0.4) /usr/proc/bin

$ ps -aef grep pmon

oracle 2424 1 0 Jan 18 ? 13:39 ora_pmon_test
oracle 8337 13002 0 05:31:47 pts/7 0:00 grep pmon

mysun:oracle> (10.1.0.4) /usr/proc/bin

$ pwdx 2424
2424: /u01/app/oracle/product/10.1.0.4/dbs

But on Linux(RHEL 4 ) there is no command like “pwdx” . In linux you can check current working directory of processes ( ORACLE_HOME/dbs i.e lock file location ) in /proc/

[root@apps001 proc]# ps -aef grep pmon

oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
root 23180 13728 0 23:19 pts/2 00:00:00 grep pmon

[root@apps001 proc]# ls -l /proc/8268

total 0
dr-xr-xr-x 2 oracle oinstall 0 Jan 27 23:20 attr
-r-------- 1 oracle oinstall 0 Jan 27 23:20 auxv
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:19 cmdline
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 cwd -> /u01/app/oracle/11.1.0/dbs
-r-------- 1 oracle oinstall 0 Jan 27 23:20 environ
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 exe -> /u01/app/oracle/11.1.0/bin/oracle
dr-x------ 2 oracle oinstall 0 Jan 27 23:20 fd
-rw-r--r-- 1 oracle oinstall 0 Jan 27 23:20 loginuid
-r-------- 1 oracle oinstall 0 Jan 27 23:20 maps
-rw------- 1 oracle oinstall 0 Jan 27 23:20 mem
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 mounts
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 root -> /
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:18 stat
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 statm
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:16 status
dr-xr-xr-x 3 oracle oinstall 0 Jan 27 23:20 task
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 wchan

[oracle@apps001 oracle]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ ls
hc_orcl11g.dat initdw.ora init.ora lkORCL11G orapworcl11g spfileorcl11g.ora

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

I asked her to create one small shell script to source oracle 11g environment variable ,
she wrote following script

ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

"11g.env" 7L, 136C written

[oracle@apps001 ~]$chmod 755 11g.env
[oracle@apps001 ~]$ . ./11g.env

She called me after some time and said , after sourcing 11g environment variable , when she try to connect oracle 11g as sysdba , it says “connect to ideal instance”

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 27 23:45:42 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.

Ctl-d

[oracle@apps001 ~]$ ps -aef grep pmon
oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
oracle 24791 24446 0 23:46 pts/4 00:00:00 grep pmon

[oracle@apps001 8268]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

# It means database is up (may be nomount , mount or open mode )
#

When ran “fuser” on lock file oracle BG processes are connected to lock file, It means database is up (may be nomount , mount or open mode )

Checked alert.log , to make sure things are fine. It shows database is OPEN

[oracle@apps001 oracle]$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on Sun Jan 27 23:52:16 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set editor vi
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/tnslsnr/apps001/listener
2: diag/rdbms/orcl2/orcl2
3: diag/rdbms/stdorcl2/stdorcl2
4: diag/rdbms/orcl11g/orcl11g
Q: to quit

Please select option:4

space available in the underlying filesystem or ASM diskgroup.
2008-01-27 21:22:07.366000 +05:30
Completed: ALTER DATABASE OPEN
2008-01-27 21:22:26.499000 +05:30
Starting background process CJQ0
CJQ0 started with pid=26, OS id=8394
2008-01-27 21:23:24.231000 +05:3
Thread 1 advanced to log sequence 3
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl11g/redo03.log
2008-01-27 21:29:03.616000 +05:30

Finally decided to check shell script , which source 11g environment variable. There suppose to be no issue at script because we able to run “adrci” and able to see alert.log

[oracle@apps001 ~]$vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written

#
# I made little change in script i.e. removed last “/” from ORACLE_HOME
#

vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written

After I removed last “/” from ORACLE_HOME , she able to connect to database J

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sys

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 28 00:00:54 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

So small change make difference.

Oracle 11g Security Enhancements Part – 1

2008-01-17T11:02:00.005-05:00

Oracle 11g Security Enhancements Part – 1

New parameters have been added to the Oracle Database 11g to enhance the default security of the database.

SEC_RETURN_SERVER_RELEASE
SEC_PROTOCOL_ERROR_FURTHER_ACTION
SEC_PROTOCOL_ERROR_TRACE_ACTION
SEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTS
SEC_DISABLE_OLDER_ORACLE_RPCS*

These parameters are system wide and static.

Release of server information restriction

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to FALSE.

SQL> show parameter SEC_RETURN_SERVER_RELEASE_BANNER

NAME TYPE VALUE

----------------------------- -------------------- ---------------------
sec_return_server_release_banner boolean FALSE

When set to true the full banner is displayed. When the value is set to FALSE, a limited generic banner is displayed.

Protect against denial of Service (DoS) attacks

The two parameters shown specify the actions to be taken when the database receives bad packets from a client. The assumption is that the bad packets are from a possible malicious client. The SEC_PROTOCOL_ERROR_FURTHER_ACTION parameter specifies what action is to be taken with the client connection: Continue, drop the connection, or delay accepting requests.

# Continue connection after 10 bad packet

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue

#Delay 4 Sec before sever accept next connection

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,4

# Drop connection after 10 bad packet

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10

The other parameter SEC_PROTOCOL_ERROR_TRACE_ACTION specifies a monitoring action: NONE, TRACE(Default) , LOG, or ALERT.

# DEFAULT Creates the trace files, but it is useful for debugging purposes,

SEC_PROTOCOL_ERROR_TRACE_ACTION = Trace

# Writes a short, one-line error message to the server trace file and alert log.

SEC_PROTOCOL_ERROR_TRACE_ACTION = Alert

# Writes a short, one-line message to the server trace file.

SEC_PROTOCOL_ERROR_TRACE_ACTION = Log

# Configures the server to ignore the bad packets and does not generate any trace files or

SEC_PROTOCOL_ERROR_TRACE_ACTION = None

SQL> show parameter SEC_PROTOCOL_ERROR_FURTHER_ACTION

NAME TYPE VALUE
------------------------------- ------------------- ------------------------
sec_protocol_error_further_action string CONTINUE

SQL> show parameter SEC_PROTOCOL_ERROR_TRACE_ACTION

NAME TYPE VALUE
---------------------- ------------------------- -----------------------
sec_protocol_error_trace_action string TRACE

Protect against intruder

If profile is not enabled then intruder can try unlimited number of authenticated requests with different user names and passwords in an attempt to gain access to the database.

A new initialization parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS that has a default setting of 10 causes a connection to be automatically dropped after the specified number of attempts. This parameter is enforced even when the password profile is not enabled.

SQL> show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS

NAME TYPE VALUE
-------------------------- --------------------------------------------------
sec_max_failed_login_attempts integer 10

The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS initialization parameter also restrict failed logins, but the difference is that these two parameters only apply to valid user accounts.

Recently come across new parameter SEC_DISABLE_OLDER_ORACLE_RPCS on Database Error Messages 11g Release 1

>
ORA-03139: This OCI call has been disabled by the DBA
Cause: The SEC_DISABLE_OLDER_ORACLE_RPCS initialization parameter was enabled.
Action: Contact the Database Administrator

But when searched oracle documentation not found any detail about it , also not found any detail about it on Database

SQL> show parameter SEC_DISABLE_OLDER_ORACLE_RPCS

SQL>

Oracle 11g NF Database Replay

2007-12-27T23:10:00.000-05:00

Oracle 11g New Feature Database Replay

“Simulating production load is not possible” , you might have heard these word.

In one project, where last 2 year management want to migrate from UNIX system to Linux system ( RAC ) , but they still testing because they are not sure where this Linux Boxes where bale to handle load or not. They have put lot of efforts and time in load testing and functional testing etc, but still not le gain confidence.

After using these feature of 11g , they will gain confidence and will able to migrate to Linux with full confidence and will know how there system will behave after migration/upgrade.

As per datasheet given on OTN

Database Replay workload capture of external clients is performed at the database server level. Therefore, Database Replay can be used to assess the impact of any system changes below the database tier level such as below:

Database upgrades, patches, parameter, schema changes, etc.
Configuration changes such as conversion from a single instance to RAC etc.
Storage, network, interconnect changes
Operating system, hardware migrations, patches, upgrades, parameter changes

DB replay does this by capturing a workload on the production system with negligible performance overhead( My observation is 2-5% more CPU usage ) and replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results; new contentions points or performance regressions. Extensive analysis and reporting ( AWR , ADDM report and DB replay report) is provided to help identify any potential problems, such as new errors encountered and performance divergences. The ability to accurately capture the production workload results in significant cost and timesaving since it completely eliminates the need to develop simulation workloads or scripts. As a result, realistic testing of even complex applications using load simulation tools/scripts that previously took several months now can be accomplished at most in a few days with Database Replay and with minimal effort. Thus using Database Replay, businesses can incur much lower costs and yet have a high degree of confidence in the overall success of the system change and significantly reduce production deployment

Steps for Database Replay

Workload Capture

Database are tracked and stored in binary files, called capture files, on the file system. These files contain all relevant information about the call needed for replay such as SQL text, bind values, wall clock time, SCN, etc.

1) Backup production Database #

2) Add/remove filter ( if any you want )
By default, all user sessions are recorded during workload capture. You can use workload filters to specify which user sessions to include in or exclude from the workload. Inclusion filters enable you to specify user sessions that will be captured in the workload. This is useful if you want to capture only a subset of the database workload.
For example , we don't want to capture load for SCOTT user

BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => 'user_scott',
fattribute => 'USER',
fvalue => 'SCOTT');
END;

Here filter name is "user_scott" ( user define name)

3) Create directory make sure enough space is there

CREATE OR REPLACE DIRECTORY db_replay_dir
AS '/u04/oraout/test/db-replay-capture';

Remember in case on Oracle RAC directory must be on shared disk otherwise , you will get following error

SQL> l
1 BEGIN
2 DBMS_WORKLOAD_CAPTURE.start_capture (name =>'capture_testing',dir => 'DB
3 END;
4*

SQL> /
BEGIN
*
ERROR at line 1:
ORA-15505: cannot start workload capture because instance 2 encountered errors
while accessing directory "/u04/oraout/test/db-replay-capture"
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799
ORA-06512: at line 2

4) Capture workload

BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (
name => capture_testing',dir=>'DB_REPLAY_DIR',
duration => NULL );
END;

Duration => NULL mean , it will capture load till we stop with below mentioned manual SQL command. Duration is optional input to specify the duration (in seconds) , default is NULL

5) Finish capture

BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;

# Take backup of production before Load capture, so we can restore database on test environment and will run replay on same SCN level of database to minimize data divergence

Note as per Oracle datasheet

The workload that has been captured on Oracle Database release 10.2.0.4 and higher can also be replayed on Oracle Database 11g release.So , I think , It simply mean NEW patch set 10.2.0.4 will support capture processes. Is it mean Current patch set (10.2.0.3) not support load capture ??????

2. Workload Processing

Once the workload has been captured, the information in the capture files has to be processed preferably on the test system because it is very resource intensive job. This processing transforms the captured data and creates all necessary metadata needed for replaying the workload.

exec DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_DIR');

Workload Replay

1) Restore database backup taken step one to test system and start Database

2) Initialize

BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (
replay_name => 'TEST_REPLAY',
replay_dir => 'DB_REPLAY_DIR');
END;

3) Prepare

exec DBMS_WORKLOAD_REPLAY.prepare_replay(synchronization => TRUE)

4) Start clients

$ wrc mode=calibrate replaydir=/u03/oradata/test/db-replay-capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:41 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Report for Workload in: /u03/oradata/test/db-replay-capture
-----------------------

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).

Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 7

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Wait for the replay to start (00:31:52)

5) Start Replay

BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/

$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Wait for the replay to start (00:31:52)
Replay started (00:33:32)
Replay finished (00:42:52)

Analysis and Reporting

Generate AWR , ADDM and DB reply report and compare with data gathered on production for same timeperiod when load was captured on Production database. For Database Replay Report run following command

SQL> COLUMN name FORMAT A20
SQL> SELECT id, name FROM dba_workload_replays;

ID NAME
---------- --------------------
1 TEST_REPLAY

DECLARE
v_report CLOB;
BEGIN
v_report := DBMS_WORKLOAD_replay.report(
replay_id => 1,
format=>DBMS_WORKLOAD_CAPTURE.TYPE_HTML
);
dbms_output.put_line(l_report);
END;
/

For sample report [ Click Here]

Reference

Chapter 22 Database Replay

Oracle 11g Database Replay

2007-12-27T02:32:00.000-05:00

Oracle 11g Database Replay

If your database currently running on 10g R2 , and want upgrade database to 11g then you can take advantage of Database Replay , As per Datasheet given on OTN workload capture on 10.2.0.4 can run/replay on 11g.

So , it simply mean , before you going to upgrade from 10g R2 to 11g , you can take advantage of database Replay feature i.e. capture work load on Production 10g R2 database , then copy workload to test system , upgrade test system to 11g , run workload captured on production and check how your system performing. This make life easier , isn't it ?

Check following links

Real Application Testing DataSheet [ Click Here ]

Oracle 11g new features - Database Replay [ Click Here ]

Oracle 11g New Features DBMS_ADDM for RAC

2007-09-20T08:37:00.004-05:00

Oracle 11g New Features DBMS_ADDM for RAC

Virag Sharma virag123@gmail.com

In “OCP Oracle Database 10g Exam Guide“ chapter 3 “Automatic Database Management” there is question

5. To retrieve the ADDM reports using SQL, what do you need to do?

A. Run the addmrpt.sql SQL script
B. Use the DBA_ADDM view
C. Use the DBA_ADVISOR view
D. Use the DBMS_ADDM package

Answer “A” is correct and D is wrong because there is no PL/SQL package named DBMS_ADDM.
But in 11g , it is not true , i.e. Package DBMS_ADDM is there in 11g. In case of RAC script addmrpt.sql
run give report for single instance, not report of all instance in RAC. But using DBMS_ADDM , we can generate report for all instance of RAC.

Different mode of DBMS_ADDM

Database Mode Instance Mode
Partial Mode

Database Mode of DBMS_ADDM

In database mode DBMS_ADDM , analyze all instance in RAC

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'DB_MODE_TEST_RAG';
:start_snap_id := 100 ;
:end_snap_id := 200 ;
DBMS_ADDM.ANALYZE_DB(:tname, :start_snap_id, :end_snap_id);

END;

Instance Mode of DBMS_ADDM
In Instance mode DBMS_ADDM , analyze one particular instance

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
VAR INST_NUM number;
BEGIN
:tname := 'INST_MODE_TEST_RAG';
:start_snap_id := 100 ;
:end_snap_id := 200 ;
:INST_NUM := 2;
DBMS_ADDM.ANALYZE_INST(:tname,:start_snap_id,:end_snap_id, :INST_NUM );
END;
/

Partial mode of DBMS_ADDM

In partial mode DBMS_ADDM analyze subset of instances. for example we want to analyze instance 2 and 4 out of four node RAC

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'PART_MODE_TEST_RAG';
:start_snap_id :=100;
:end_snap_id := 200;
DBMS_ADDM.ANALYZE_PARTIAL(:tname,'2,4', :start_snap_id, :end_snap_id);

END;

Displaying an ADDM Report

  SET LONG 1000000 PAGESIZE 0;

   SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

  or

 SELECT DBMS_ADDM.GET_REPORT('DB_MODE_TEST_RAG') FROM DUAL;

Oracle 11g Database New Features: Data Guard Enhancements

2007-09-12T07:59:00.000-05:00

Oracle 11g Database New Features: Data Guard Enhancements
Virag Sharma virag123@gmail.com

Why We need Data Guard ?? for

Data protection
Data Availability

11g Increase ROI from standby systems and enhance manageability, New feature like Active Data Guard , Snapshot standby make better ROI.

Here is some data guard category and there enhancement

1) Data Protection

Advanced Compression
Lost-write protection
Fast-Start Failover

2) Increase ROI

Active Data Guard
Snapshot Standby

3) High Availability

Faster Redo Apply
Faster failover & switchover
Automatic Failover using ASYNC

4) Manageability

Mixed Windows/Linux

Active Data Guard

Oracle Active Data Guard 11g – a new Database Option
Enables read-only access to a physical standby database while Redo Apply is active
It is now possible to query a physical standby database while Redo Apply is active.This new capability increases your return on investment in Data Guard technology because a physical standby database can now be used to offload queries from the primary database in addition to providing data protection.
( We can do same with logical standby , what is diffrent here , well redo apply method is faster in physical stand by ie redo apply methode is diffrent in physical standby)

Stop log apply

Alter database recover managed standby database cancel;

Open database for read-only access

alter database open

Once database open start redo apply

alter database recover managed standby database
using current logfile disconnect from session;

Snapshot Standby

This enhancement is good example of ROI. This feature allow us to better utilization of standby. This feature Truly leverages DR hardware for multiple purposes.For example convert physical database to snapshot standby database do testing/ application patching etc. After testing convert back snapshot database back to physical standby.

-- Convert physical database to snapshot

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

--
-- Do testing on database
--

-- Convert snapshot Database to physical database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

This Similar to storage snapshot , but it useage same space , not additional space is needed ( I know this possibly can achieve in 10g R2 too , using flashback feature , But for sure it is not easy as it is in 11g )

Redo compression

To transport redo data in compressed form to destination use COMPRESSION attribute.
By default compression is disabled.

Alter system set log_archive_dest_1 = 'SERVICE=standby1 compression=ENABLE';
select dest_name , compression from v$archive_dest;

SQL>
DEST_NAME COMPRES
--------------------------------- -------
LOG_ARCHIVE_DEST_1 DISABLE
LOG_ARCHIVE_DEST_2 ENABLE
LOG_ARCHIVE_DEST_3 DISABLE
LOG_ARCHIVE_DEST_4 DISABLE
LOG_ARCHIVE_DEST_5 DISABLE
LOG_ARCHIVE_DEST_6 DISABLE
LOG_ARCHIVE_DEST_7 DISABLE
LOG_ARCHIVE_DEST_8 DISABLE
LOG_ARCHIVE_DEST_9 DISABLE
LOG_ARCHIVE_DEST_10 DISABLE

10 rows selected.

Data Protection

New Initialization parameter db_lost_write_protect Introduced. This parameter Compare versions of blocks on the standby with that in the incoming redo stream
Version discrepancy implies lost write on either primary or standby database

db_lost_write_protect

Managibilty Enhancement

SYS user and password files no longer required for redo transmission authentication. Non-SYS user can be specified through the parameter, This user must have the SYSOPER privileges, Requires password for this user to be the same at primary and all standbys
Upon SYSDBA / SYSOPER changes, password file must be copied from the primary to all physical standby databases

redo_transport_user

Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration (- Since 11g Linux installer only available, so not able to test this feature)

Fast-Start Failover

Immediate automatic failover for user-configurable health conditions

DGMGRL> show FAST_START FAILOVER
DGMGRL> ENABLE FAST_START FAILOVER [CONDITION ];

Condition examples:

Datafile Offline
Corrupted Controlfile
Corrupted Dictionary
Inaccessible Logfile
Stuck Archiver
Any explicit ORA-xyz error

Application can also request for failover using package DBMS_DG

DBMS_DG.INITIATE_FS_FAILOVER

Some Good Links / Blogs / site to know more about New Oracle Database 11g features

2007-09-08T21:56:00.000-05:00

Some Good Links / Blogs / site to know more about New Oracle Database 11g features
Virag Sharma virag123@gmail.com

I have rated 5 *(star) for some sites , which I like Most

( these links collected from google alert )

You can have a look on following forum as well. Good thing about this forum is that , it update time to time , with latest 11g Links. Thanks to TongucY

http://forums.oracle.com/forums/thread.jspa?threadID=542281&tstart=90

- Vi

Oracle 11g New Features SQL plan management (SPM)

2007-09-07T07:54:00.004-05:00

Oracle 11g New Features SQL plan management (SPM)

Virag Sharma virag123@gmail.com
[Click Here For PDF document, more readable doc]

You might have noticed that execution plan changed in CBO , specially in following case

Database Upgrade
Database / Schema Stats collection
Change in environment ( LinkUnix to Linux Migration )
Change in data

Case: You want to upgrade database from 10g to 11g and this change can cause regressions in SQL performance,and fixing them manually can be difficult and time consuming. Sql tuning can be used but, this is a reactive mechanism and cannot guarantee stable performance when drastic changes happen to the system. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become a high-load statement due to a plan change, but this cannot be resolved by SQL tuning until after the plan change occurs.

Solution: Oracle 11g new features SQL plan management (SPM) records and evaluates the execution plans of SQL Statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

Note:- Before upgrading database to 11g (11.1.0.6) , i have collect SQL Tuning set (STS) for 7 Day using "capture_cursor_cache_sqlset". By assuming that in 7 day all SQL stas will collected in STS. After that upgraded database to 11g ( from 10g 10.2.0.3) , i have used this STS to compare execution plan from 11g. Please check "SQL Performance Analyzer Part - 2"
for steps to transfer STS one database to other database

1) Capturing SQL Plan Baselines

· Automatic Plan Capture ( 11g Only )

· Manual Plan Capture ( 10g and 11g )

2) Make Changes Upgrade Database / collect stats / Migrate Database to Linux

3) Upload SQL Plan Baseline

4) Enable the use of SQL plan baselines

5) Evolving SQL Plan Baselines

1) Capturing SQL Plan Baselines in oracle 10g before upgrading to 11g

a) Automatic Plan Capture(11g only)

When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES set true , then database automatically creates and maintains the plan history for SQL statements using information provided by the optimizer.

In init.ora file

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true

SQL>Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true;

Note : - Above automatic plan capture only work on 11g , if you want to capture plan in 10g , then check section “Manual Plan Capture”

b) Manual Plan Capture ( 10g and 11g )

Following script will capture sql plan in SQL tuning set for 7 Day ( this script will run for 7 day , you can change time according to your need )

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

DECLARE
sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';
BEGIN

dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul,
604800,
1,
'MERGE',
dbms_sqltune.MODE_ACCUMULATE_STATS
);

END;

capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

time_limit : 604800 ( The total amount of time, in seconds, to execute , 7 day = 7 * 24 * 60 * 60 = 604800 )
repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
capture_option During capture, either insert new statements, update existing ones, or both.

'INSERT'
'UPDATE',
'MERGE'

capture_mode capture Option (UPDATE and MERGE capture options).

MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions
seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE and LOAD_SQLSET procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL statements or the LOAD_SQLSET procedure, which accesses the data source only once.

2) Make Changes e.g. Upgrade Database / collect stats / Migrate Database to Linux Collect stats

exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

exec dbms_stats.set_param('method_opt','FOR ALL COLUMNS SIZE 254');

exec dbms_stats.Gather_Database_Stats;

3) Upload SQL Plan Baseline

If you have collected stats manually in SQL tuning sets ( STS) , then you need to
upload baseline from STS

-- Upload plan manually using dbms_spm
--

variable pls number;

exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name=>'STS_RAG_CMUL',-
basic_filter=>'parsing_schema_name like ''APPS'' and plan_hash_value!=0', -
fixed=>'NO',commit_rows=>1000);

4) Enable the use of SQL plan baselines

To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES
initialization parameter to TRUE. By default, this parameter is set to TRUE.

--In init.ora file
--

OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE

OR

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES= true;

5) Evolving SQL Plan Baselines

I have captured 10g plans in STS and after upgrading database to 11g uploaded those plans ,using dbms_spm.load_plans_from_sqlset. After uploading plans manually , I have set

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.

--Check not accepted Plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed
from dba_sql_plan_baselines
Where ACCEPTED='NO' and
PARSING_SCHEMA_NAME like 'APPS';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
--------------------- ------------------------- ------- --------- -------
SYS_SQL_9295397103ae5ebe SYS_SQL_PLAN_74720f16b0fcefa7 YES NO NO
SYS_SQL_9295397103ad3eba SYS_SQL_PLAN_03ad3ebaa086802f YES NO NO
SYS_SQL_434ef30d9da6a29b SYS_SQL_PLAN_9da6a29b1f6e321d YES NO NO
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES NO NO
.......................................... ....................................

40 rows selected.

-- Displaying SQL Plan Baselines
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_13836d6b3da62bbb
SQL text: ( I have removed SQL text and changed table name in below execution plan)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbb91266099
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2877140902
-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

-----------------------------------------
Id Operation Name
-----------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 TABLE ACCESS FULL COMP
-----------------------------------------

42 rows selected.

SQL>

Evolving Plans with pls/sql function DBMS_SPM.EVOLVE_SQL_PLAN_BASELIN

--Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
--

SQL> DECLARE
report clob;
BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_13836d6b3da62bbb');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =SYS_SQL_13836d6b3da62bbb
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES

Plan: SYS_SQL_PLAN_3da62bbbe5990995
-----------------------------------
Plan was verified: Time used .06 seconds.
Failed performance criterion: Compound improvement ratio <= .3.

Baseline Plan Test Plan Improv. Ratio

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

Execution Status: COMPLETE COMPLETE
Rows Processed: 1460 1460
Elapsed Time(ms): 10 10 1
CPU Time(ms): 9 9 1
Buffer Gets: 84 286 .29
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------

Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

PL/SQL procedure successfully completed.

SQL>

Just for demo purpose , lets accept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995')

-- Acccept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995') and Fixed it
--

variable cnt number;
exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

PL/SQL procedure successfully completed.
SQL>

-- Same pl/sql function used to fix plans
-- Optimizer always picked FIXED plans

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'FIXED', attribute_value => 'YES');

PL/SQL procedure successfully completed.

SQL>

- - Check status of plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines
2 Where FIXED='YES' and PARSING_SCHEMA_NAME like '';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
----------------------------- ---------------------------- -------------- --------
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES YES YES

SQL>

>

-- Displaying SQL Plan Baselines after changes
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

Plan hash value: 2877140902

-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

Reference

SQL Plan Management Chapter 15

Oracle 11g New feaures Case Sensitive Password

2007-08-31T06:15:00.010-05:00

Oracle 11g New feaures : Case Sensitive Password

Virag Sharma virag123@gmail.com

After upgrading production database to 11g, I thought to try some other features of Oracle database 11g on test database. So tried to start configuring standby database. Created password files with manager password.

But we noticed that, archive file is not shipping to physical standby side and getting authentication error again and again. Finally I have shutdown my PC and went to watch News. While watching movie idea came into mind, this error might be because of 11g new feature case sensitive password. Well it is, and I created password file with following command and after that thing work fine.

orapwd file=orclpwd password=manager ignorecase=y

Password case sensitive by default, new init.ora parameter sec_case_sensitive_logon is introduce in 11g to switch on/off

#
# Init.ora parameter
#

sec_case_sensitive_logon = (TRUE FALSE)

#
# On system level you can switch off
#

alter system set sec_case_sensitive_logon = false

#
#Find users who have case sensitive or case insensitive
#passwords
#

SQL> COL USERNAME form a19
SQL> SELECT USERNAME,PASSWORD_VERSIONS
FROM DBA_USERS where rownum <>

USERNAME PASSWORD
---------- --------
ABCXYZ12 10G 11G
ADKULABC 10G 11G
ORACLE12 10G 11G
VIRAGSHA 10G

Since password are case sensitive, so DB link created from pre-11G DB to 11G DB might not work. When you create database link in pre-11G DB by default password saved in upper case and when you use database link you will get error "ORA-01017: invalid username/password; logon denied"

When creating database link, use following work around to force case sensitive password

#
# Use quote " with password like "tiger"
#

create database link abc connect to scott identified
by "tiger" using 'ORCL2';

Reference

Oracle Database Security Guide11g Release 1 (11.1)

SQL Performance Analyzer (SPA) Part - 2

2007-08-22T10:14:00.016-05:00

SQL Performance Analyzer (SPA) Part - 2
Virag Sharma virag123@gmail.com

[Click Here for PDF format]

Case: - You want to adjust some parameters in production and you want to check how over all application going to run i.e. is SQL performance improved or it degrade ?

Solution: - Oracle 11g nw features SQL Performance Analyzer (SPA) analyze the SQL performance impact of any type of system changes. SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each statement.

SPA report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, DBAs can remedy any negative outcome before their end users are affected and can validate, with significant time and cost savings, that the system change to the production environment will result in net improvemengt

The steps of the SQL Performance Analyzer workflow are as follows:

Capture SQLs on Production Database
Move SQLs to Test Database
Create a SQL Performance Analyzer task on the test system
Generate and store the query execution statistics before the change.
Make changes (Database upgrade to 11g , parameter changes etc)
Generate and store the query execution statistics after the change.
Compare Performance
Generate Report

1) Capture SQL ( Steps on production)

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');

exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

DECLARE
sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';

BEGIN

dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul,750000,1,
'MERGE',dbms_sqltune.MODE_ACCUMULATE_STATS);

END;
/

time_limit : 750000 ( The total amount of time, in seconds, to execute )
repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
capture_option During capture, either insert new statements, update existing ones, or both.

'INSERT'
'UPDATE',
'MERGE'

capture_mode capture Option (UPDATE and MERGE capture options).

MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions seen is greater than that stored in the SQL tuning set

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full
system workload by repeatedly polling the cursor cache over a specified interval.
This function is a lot more efficient than repeatedly using the SELECT_CURSOR_
CACHE and LOAD_SQLSET procedures to capture the cursor cache over an
extended period of time. This function effectively captures the entire workload, as
SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL
statements—or the LOAD_SQLSET procedure, which accesses the data source only
once.

2) Move SQLs to test system

Steps on production database

Create staging table for STS and pack STS data into staging table and import the staging table

a) Create a staging table

exec dbms_sqltune.create_stgtab_sqlset('SQLSET_TAB');

b) Pack the sqlset in a staging table

exec dbms_sqltune.pack_stgtab_sqlset('STS_RAG_CMUL','APPS','SQLSET_TAB','APPS');

c) Export the staging table

exp apps/apps file=sqlset_tab.dmp tables=sqlset_tab

Steps on test database

Import staging table and unpack staged data in STS

d) Import the staging table in the target database

imp apps/apps file=sqlset_tab.dmp full=y

e) unpack the staging table into sqlset

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');

exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

exec dbms_sqltune.UNPACK_STGTAB_SQLSET(
SQLSET_NAME=>'%',
SQLSET_OWNER=>'APPS',
REPLACE=>true,
STAGING_TABLE_NAME=>'SQLSET_TAB',
STAGING_SCHEMA_OWNER=>'APPS'
);

3) Create a SQL Performance Analyzer task on the test system

Create a SQL Performance Analyzer task on the test system using the
SQL Tuning Set as its input source.

var l_task_id char(30)
begin
:l_task_id:=dbms_sqlpa.create_analysis_task(sqlset_name =>
'STS_RAG_CMUL',task_name => 'MY_REPLAY_TASK');
end;
/

Since we processing whole system load, So TIME_LIMIT
need to increase

exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK',
'LOCAL_TIME_LIMIT',60);

exec dbms_sqltune.set_tuning_task_parameter('MY_REPLAY_TASK',
'TIME_LIMIT',200000000000);

The time_limit parameter specifies the global time limit to process all SQL
statements in a SQL Tuning Set beforetiming out.

The local_time_limit parameter specifies the time limit to process each SQL
statement in a SQL Tuning Set before timing out.

4)Generate and store the query execution statistics before the change

Use SQL Performance Analyzer to build the pre-change performance data by executing the
SQL statements stored in the SQL Tuning Set.

Begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'TEST EXECUTE',execution_name => 'MY_REPLAY_EXECUTE_BEFORE');
end;
/

Check status of Analysis task

SQL> col TASK_NAME format a30

SQL> col EXECUTION_NAME for a30

SQL> l
select execution_name, status, execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='MY_REPLAY_TASK'
order by execution_end

SQL> /

EXECUTION_NAME STATUS EXECUTION_END

-------------------- --------------------------- ---------------
MY_REPLAY_EXECUTE_BEFORE EXECUTING

--Check time

SQL> select TASK_ID,TASK_NAME,TASK_NAME , STATUS
FROM USER_ADVISOR_TASKS
WHERE task_name = 'MY_REPLAY_TASK';

TASK_ID TASK_NAME TASK_NAME STATUS
---------- -------------------- -------------------- --------------
244563 MY_REPLAY_TASK MY_REPLAY_TASK EXECUTING

SQL> SELECT sofar, totalwork
FROM V$ADVISOR_PROGRESS
where TASK_ID=244563;

SOFAR TOTALWORK
---------- ----------
142 100630

5) Make database Changes

alter system set "_b_tree_bitmap_plans" = false;

--- More application change
--- Stats collection- etc

6) Generate and store the query execution statistics after the change.

Use SQL Performance Analyzer to build the post-change performance data
by re-executing the SQL statements in the SQL Tuning Set on the post-change
test system.

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'TEST EXECUTE',
execution_name => 'MY_REPLAY_EXECUTE_after');

end;
/

7) Compare execution plans

Use SQL Performance Analyzer to compare and analyze
the pre-change and post-change versions of performance data

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MY_REPLAY_TASK',execution_type => 'COMPARE PERFORMANCE',
execution_name => 'MY_COMPARE_EXECUTION');

end;
/

8) Compare the execution changes due to your database changes

Generate a report to identify the SQL statements in the
SQL workload that have improved, remained unchanged, or
regressed after the system change. Review the report and
interpret the results.

--Displaying the Results of a SQL Tuning Task

set serveroutput on size 999999
set long 999999

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK')from dual;

-- [ Click Here for Sample Report of above command ]OR

var rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK', -
'text', 'typical', 'summary');

SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130

PRINT :rep

--Note above commands will take few Min.

exec dbms_sqlpa.DROP_ANALYSIS_TASK('MY_REPLAY_TASK');

Some more article on 11g

1. Oracle 11g TOP feature for DBA

2.11g Automatic Diagnostic Repository (ADR)

3. Sql Performance Analyzer (SPA) Part 1

4. Sql Performance Analyzer (SPA) Part 2

TOTAL RECALL Oracle 11g New Features

2007-08-14T05:19:00.001-05:00

TOTAL RECALL, Oracle 11g New Feature
Virag Sharma virag123@gmail.com

Dont get confused with Arnold Picture and Movie call "Total Recall"
We going to discuss different total recall i.e. Oracle database 11g new features :- Total Recall

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

With Total Recall’s Flashback Data Archive technology, managing historical data should no longer be a painful task. It provides an extremely secure, efficient, easy to use and application transparent solution for managing historical data as well as a centralized, secure query-able historical data store that makes the most efficient use of all your resources – be it CPU, Storage or administrator time, and it enables and reduces your cost of compliance.

1) Creating a Flashback Data Archive
2) Specifying the Default Flashback Data Archive
3) Enabling and Disabling Flashback Data Archive
4) DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive
5) Viewing Flashback Data Archive Data

CREATE FLASHBACK ARCHIVE DEFAULT flatest TABLESPACE RAG_tbs1
QUOTA 10G RETENTION 1 YEAR;

ALTER TABLE emp FLASHBACK ARCHIVE flatest;

-When history data from emp exceeds the age of 1 years,
-it will purged

ALTER TABLE emp NO FLASHBACK ARCHIVE;

- Using Flashback Data Archive to Access Historical Data
- Emp Salary as on 31-DEC-2006 close of business Hr.

SELECT emp_id, sal FROM emp AS OF
TIMESTAMP TO_TIMESTAMP ('2006-12-31 17:00:00', 'YYYY-MM-DD HH24:MI:SS')

Coming soon ...all details about Total Recall

Restriction
Following DDL statements on a table enabled for Flashback
Data Archive causes error ORA-55610:

ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or subpartition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
DROP TABLE statement
RENAME TABLE statement
TRUNCATE TABLE statement

Refrence

Using Flashback Technology

Oracle SQL REPLAY & Sql Performance Analyzer(SPA) Part 1

2007-08-12T09:53:00.002-05:00

Oracle SQL REPLAY Part 1
Virag Sharma virag123@gmail.com

You can use Sql Performance Analyzer (SPA) to analyze the SQL performance impact of any type of system changes.

Implementation of tuning recommendations ( like parameter changes )
Schema changes (eg Application Patch )
Statistics gathering
Database upgrades
OS/hardware changes

SQL Performance Analyzer allows for the comparison of SQL performance statistics before and after changes and provide comparison report.

Steps for SQL REPLAY

Collect Sqls
Create the SQL Replay Task with SQL Tuning Set (STS)
Collect SQL Performance Before Changes
Make Database Change ( eg collect stats etc)
Collect SQL Performance After Changes
Comparing SQL Performance Before and After Change
Display the Results of a SQL Replay Task

Steps in details

1. Collect SQL

To create an SQL Tuning Set (STS)

exec dbms_sqltune.create_sqlset ('MYSQLSET');

PL/SQL procedure successfully completed.

Collect sqls in the SQL Tuning Set(STS) with all queries from the cursor cache.
You can call the procedure multiple times to add new SQL statements or replace
attributes of existing statements.

DECLARE

cur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN cur FOR
SELECT VALUE(p)
FROM TABLE (dbms_sqltune.select_cursor_cache) p;

dbms_sqltune.load_sqlset(
sqlset_name => 'MYSQLSET',
populate_cursor => cur);
END;

Collect sql mentioned in AWR as well , You populate the tuning set with 'ACCUMULATE' as yourupdate_option because it is assumed the cursors currently in the cache had aged out since the snapshot was taken.

DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN

OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(1,100) P;

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MYSQLSET',
populate_cursor => cur,
load_option => 'MERGE',
update_option => 'ACCUMULATE');

END;

Note 1 is start snap id and 100 is END snap ID

Create the SQL Replay Task with SQL Tuning Set (STS)

Create the SQL Replay Task with SQL Tuning Set(STS) named MYSQLSET

var l_task_id char(30)
begin
:l_task_id:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'MYSQLSET',
task_name => 'MY_REPLAY_TASK');
end;

Collect SQL Performance data Before Changes

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'MY_REPLAY_TASK',
execution_type => 'TEST EXECUTE',
execution_name => 'MY_REPLAY_EXECUTE_BEFORE');
end;
/

Make Database Change ( eg collect stats etc)

Make application , database changes

alter system set "_b_tree_bitmap_plans" = false;

alter system set optimizer_index_cost_adj=15

Collect SQL Performance After Changes

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'MY_REPLAY_TASK',
execution_type => 'TEST EXECUTE',
execution_name => 'MY_REPLAY_EXECUTE_after');
end;
/

Comparing SQL Performance Before and After Change

Analyze the Performance (Improvement or regressions)

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'MY_REPLAY_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'MY_COMPARE_EXECUTION');
end;

Display the Results of a SQL Replay Task

-- Checking the Status of a SQL Tuning Task

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'MY_REPLAY_TASK'

-- Checking the Progress of the SQL Tuning Advisor

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE task_name = 'MY_REPLAY_TASK'

--Displaying the Results of a SQL Tuning Task

set serveroutput on size 999999

set long 999999

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK') from dual;

--Note above command will take few Min.

VAR rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_REPLAY_TASK', -
'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep

- Sample Output of report

Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 92.33%

REP
------------------------------------------------
Improvement Impact : 92.33%
Regression Impact : 0%

SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 3813 0
Improved 2 0
Unchanged 3775 0
with Errors 36 0

In this article Sql Performance Analyzer Part 1,we just covered Sql Performance Analyzer (SPA) basic functionality , In part 2 I will cover more expects and case study of SPA

Some more article on 11g

SQL Tuning Information Views

· Advisor views,

DBA_ADVISOR_TASK
DBA_ADVISOR_EXECUTIONS,
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_RATIONALE views

· SQL tuning views,

DBA_SQLTUNE_STATISTICS
DBA_SQLTUNE_BINDS, and
DBA_SQLTUNE_PLANS views

· SQL Tuning Set views,

DBA_SQLSET,
DBA_SQLSET_BINDS,
DBA_SQLSET_STATEMENTS,
DBA_SQLSET_REFERENCES views.

· Captured execution plans for statements in SQL Tuning Sets

DBA_SQLSET_PLANS
USER_SQLSET_PLANS

· SQL tuning view,

V$SQL,
V$SQLAREA,
V$SQLSTATS,
V$SQL_BINDS views

· SQL Profile information is displayed in the DBA_SQL_PROFILES view.
The TYPE parameter shows if the SQL profile was created manually by the SQL Tuning Advisor (if TYPE = MANUAL) or automatically by automatic SQL tuning (if TYPE = AUTO).

· Advisor execution progress information is displayed in the V$ADVISOR_PROGRESS view.

References

Automatic SQL Tuning

Automatic Diagnostic Repository (ADR)

2007-08-12T00:17:00.000-05:00

Automatic Diagnostic Repository (ADR)

Virag Sharma virag123@gmail.com

In Oracle 11g database New dimension fault diagnosability infrastructure added to Oracle self managing concept to reduce time for resolving problem/bug/SR and round trip between end-user and Oracle Support. Trace file, dump file , core file ADR, ADRCI etc are components of fault diagnosability infrastructure.

In early version of oracle, When ever critical error such as code bug , data corruption occur end user need to collect diagnostic data such as process dump , data structure dump etc. In oracle 11g database end user need to worry about what kind of dump need to collect , need not to search trace file which need to send to Oracle Support Oracle 11g Database proactively collect ,tagged and store diagnostic data in file based repository known as ADR. 11g used file based repository because even your database is down, you have diagnostic data to send to oracle support.

Key Components for Fault Diagnosability Infrastructure

ADR
ADRCI ( ADR Command line utility )
Alert log
Trace ,Dumps etc.

ADR (Automatic Diagnostic Repository)

ADR is file based repository for diagn

ostic data like trace file,process dump,data structure dump etc.
In oracle 11g trace. alert

not saved in *_DUMP_DEST directory even you set those parameters in init.ora.11g ignore *_DUMP_DEST and store data in new format , directory structure is given below

Diag/product_type/database_name/instance_name

ADR_base/diag/rdbms/orcl2/orcl2

Click on figure for clear view

Note : ADR_HOME is user define variable , I have define this variable make life easier

ADR root where ADR directory structure start.11g New initialize parameter DIAGNOSTIC_DEST decide location of ADR root,

By default location of DIAGNOSTIC_DEST is $ORACLE_HOME/log,
if ORACLE_BASE is set in environment then DIAGNOSTIC_DEST is set to $ORACLE_BASE

In 11g alert file is saved in 2 location, one is in alert directory ( in XML format) and old style alert file in trace directory. Within ADR base, there can be many ADR homes, where each ADR home is the root directory for all diagnostic data for a particular instance. The location of an ADR home for a database is shown on the above graphic.

Note :- I have created on environment variable ADR_HOME= . I am using same in all my this document

SQL> show parameter diag
NAME TYPE VALUE
------------------------ ---------------------- ------------------------------
diagnostic_dest string /u01/app/oracle

Data	Old location	ADR location
Core Dump	CORE_DUMP_DEST	$ADR_HOME/cdump
Alert log data	BACKGROUND_DUMP_DEST	$ADR_HOME/trace $ADR_HOME/alert (XML)
Background process trace	BACKGROUND_DUMP_DEST	$ADR_HOME/trace
User process trace	USER_DUMP_DEST	$ADR_HOME/trace


SQL> desc v$diag_info
Name                                       Null?    Type
----------------------------------------- --------  ----------------------------
INST_ID                                             NUMBER
NAME                                                VARCHAR2(64)
VALUE                                               VARCHAR2(512)

SQL> select * from v$diag_info;

INST_ID NAME VALUE
---------- ------------------------- ---------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/orcl2/orcl2
1 Diag Trace /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm
1 Default TraceFile /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_14385.trc
1 Active Problem Count 0
1 Active Incident Count 0

Retention policy

There is retention policy for ADR that allow to specify how long to keep the data
ADR incidents are controlled by two different policies:

The incident metadata retention policy ( default is 1 year )
The incident files and dumps retention policy ( Default is one month)

We can change retention policy using “adrci” MMON purge data automatically on expired ADR data.

adrci> show control

ADR Home = /u01/app/oracle *************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

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

3667832353 720 8760 2008-07-02 13:24:01.088681 -07:00 2008-07-22 00:20:04.113441 -07:00 1 2 0 1 2008-07-02 13:24:01.088681 -07:00

1 rows fetched

adrci>

Change Retention

adrci> set control (SHORTP_POLICY = 360 )
adrci> set control (LONGP_POLICY = 4380 )

Automatic Diagnostic Repository (ADRCI)

Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool. This tool
allow user to interact with ADR ,check alert log, check health monitor(HM) status , create report
on HM, Package incident and problem information into a zip file for send to Oracle Support. etc.
No username/password need to log in to ADRCI, ADRCI interact with file system and ADR data
is secured only by operating system permissions on the ADR directories.
[Click Here for Oracle Documenation on adrci]

[oracle@apps001 ~] $ adrci
adrci> help

HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL

There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list

adrci>

adrci> SHOW INCIDENT
ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:
********************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
9817 ORA 600 [kcidr_reeval_3] 2008-05-14 18:41:03.609077
+05:30
1 incident info records fetched

Checking alert log with ADRCI

One can see alert log content with the help of ADRCI

adrci>>show alert

If just want to tail content of alert log

$adrci
adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )

Content of xml alert log ( log.xml )

type='UNKNOWN' level='16' host_id='apps001'
host_addr='192.20.182.113' module='' pid='19541'>
Incremental checkpoint up to RBA [0x10e.98cbe.0], current log tail at RBA [0x10e.98cea.0]

Since alert log saved as XML format ( log.xml ) , you can query xml file as well

Below is example to check all “ORA-“ in alert log

adrci>SHOW ALERT -P "MESSAGE_TEXT LIKE '%ORA-%'"

You can spool output for ADRCI using spool command same as we use in sqlplus

Problem and Incident

Problem

AD introduce new concept of problem and incident, problem is critical error in database and in ADR problem is identified by problem key. Problem key is consist of oracle error number, error parameter value etc
for example ORA600kci

Incident

Incident is single occurrence of problem , each incident is identified by unique number called incident id ,
which is unique in ADR home, all incident data stored in ADR. Each incident has a problem key and is
mapped to a single problem. When error occurred backup ground process make entry in alert.log and
collect data about incident (like process dump, data structure dump etc)

If similar incident happen more frequently , oracle will not collect data for all incident
By default only five dumps per hour for a given problem are allowed for single given problem and
this call flood control in 11g , some time you see "flood control" messages in alert.log / log.xml.
Incident can be created as manual as well, if needed.

adrci>>SHOW INCIDENT

ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
9817 ORA 600 [kcidr_reeval_3] 2008-05-14 18:41:03.609077 +05:30
1 incident info records fetched

IPS ( Incident package service )

DBA need not search trace , dump etc related particular error, to sent it to oracle support. In ADR
diagnostic data are tagged with incident id and IPS identified trace and dump for particular incident
and allow end user to create package from ADR to send to Oracle Support. Using IPS end user can
add some more file to package if needed.

How to create package

Create logical package
Add files to package
Create zip file to send to oracle support

Create logical package

Check incidents for which you want to create package

adrci>SHOW INCIDENT

ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
9817 ORA 600 [kcidr_reeval_3] 2008-08-14 18:41:03.609077
+05:30
1 incident info records fetched

We can use IPS CREATE PACKAGE command to create a logical package for above incident

adrci>ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical

Add files to package

Adding diagnostic information for a particular (9817) incident

adrci>ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical
adrci>ips add incident 9817 package 4
Added incident 9817 to package 4

You can add additional files if needed, But file should be in ADR, below in example we adding alert log to package.

adrci> ips add file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4
Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
package 4 4

Create zip file to send to oracle support

adrci> ips generate package 4 in /tmp
Generated package 4 in file /tmp/ORA600kci_20080814184516_COM_1.zip, mode
complete
adrci>

[root@apps001 tmp]#ls –l /tmp/*.zip

-rw-r--r-- 1 oracle oinstall 584410 May 14 18:50 ORA600kci_20080814184516_COM_1.zip

[root@apps001 tmp]# unzip -l ORA600kci_20080814184516_COM_1.zip
Archive: ORA600kci_20080814184516_COM_1.zip
Length Date Time Name
-------- ---- ---- ----
763210 05-14-08 18:41
diag/rdbms/orcl2/orcl2/incident/incdir_9817/orcl2_ora_5967_i9817.trm
2302506 05-14-08 18:41
diag/rdbms/orcl2/orcl2/incident/incdir_9817/orcl2_ora_5967_i9817.trc
186887 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log
491982 05-14-08 18:41 diag/rdbms/orcl2/orcl2/alert/log.xml
1122 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_diag_5931.trc
189 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_diag_5931.trm
1342 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_ora_5967.trc
773 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/orcl2_ora_5967.trm
831 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_CONFIGURATION.dmp
338 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE.dmp
193 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE_INCIDENT.dmp
1094 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE_FILE.dmp
234 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_PACKAGE_HISTORY.dmp
6004 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_FILE_METADATA.dmp
214 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/IPS_FILE_COPY_LOG.dmp
1273 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_DEF.dmp
1813 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_PARAMETER_DEF.dmp
204 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION.dmp
198 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_ACTION_PARAMETER.dmp
353 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_INCIDENT_TYPE.dmp
163 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/DDE_USER_INCIDENT_ACTION_MAP.dmp
614 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/INCIDENT.dmp
357 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/INCCKEY.dmp
202 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/INCIDENT_FILE.dmp
406 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/PROBLEM.dmp
710 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/HM_RUN.dmp
843 05-14-08 18:49 diag/rdbms/orcl2/orcl2/hm/HMREPORT_HM_RUN_21.hm
708 05-14-08 18:49 diag/rdbms/orcl2/orcl2/hm/HMREPORT_HM_RUN_41.hm
207 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/export/EM_USER_ACTIVITY.dmp
62624 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/config.xml 489 05-14-07 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/metadata.xml
9508 05-14-08 18:49
diag/rdbms/orcl2/orcl2/incpkg/pkg_4/seq_1/manifest_4_1.xml
0 05-05-08 04:00 diag/rdbms/orcl2/orcl2/alert/
0 05-05-08 04:00 diag/rdbms/orcl2/orcl2/cdump/
0 05-14-08 18:49 diag/rdbms/orcl2/orcl2/hm/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/incident/
0 05-14-08 18:45 diag/rdbms/orcl2/orcl2/incpkg/
0 05-13-08 22:51 diag/rdbms/orcl2/orcl2/ir/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/lck/
0 05-05-08 04:00 diag/rdbms/orcl2/orcl2/metadata/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/stage/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/sweep/
0 05-14-08 18:41 diag/rdbms/orcl2/orcl2/trace/
489 05-14-08 18:49 metadata.xml
-------- -------
3838080 44 files
[root@apps001 tmp]#

Log a SR and upload this zip file to Oracle Support for diagnose and resolution.

IPS in Summary

$ adrci
adrci> help ips
adrci> show incident
( For example above command show incident No 9817 for ORA-600 [XYZ] )

adrci> ips create package incident 9817 <= ( it will give package No.)
adrci> ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical

adrci> ips add incident 9817 package 4
Added incident 9817 to package 4

adrci>

adrci>>ips add file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4

Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
package 4

adrci>>ips generate package 4 in /tmp
Generated package 4 in file /tmp/ORA600kci_20080814184516_COM_1.zip, mode
complete
adrci>>

Health Monitor (HM)

Health Monitor run diagnostic checks on various components of the database. Health Monitor checks
examine various components of the database, including files, memory, transaction integrity, metadata,
and process usage. In order to collect more data after critical error (incident) , oracle invoke health
monitoring implicitly. If need end-user can also run health monitoring procedure manually

Health Monitor checks run in two ways:

Reactive: The fault diagnosability infrastructure can invoke Health Monitor checks automatically
in response to critical errors.
Manual: DBA can manually run Health Monitor health checks Manually

Kinds of health monitoring

Please look at the V$HM_CHECK view , it will list all Health monitoring checks

SQL> desc v$hm_check
Name Null? Type
--------- -------- -------
ID NUMBER
NAME VARCHAR2(64)
CLSID NUMBER
CLS_NAME VARCHAR2(15)
FLAGS NUMBER
INTERNAL_CHECK VARCHAR2(1)
OFFLINE_CAPABLE VARCHAR2(1)
DESCRIPTION VARCHAR2(64)

SQL> select name from v$hm_check ;

NAME
-------------------------
HM Test Check
Database Cross Check
Data Block Check
Redo Check
Logical Block Check
Table Check
Table-Index Cross Check
Table Row Check
Table-Index Row Mismatch
Transaction Check
Undo Segment Check
All Control Files Check
CF Member Check
All Datafiles Check
Single Datafile Check
Log Group Check
Log Group Member Check
Archived Log Check
Redo Revalidation Check
IO Revalidation Check
Block IO Revalidation Check
Txn Revalidation Check
Failure Simulation Check
Database Dictionary Check

25 rows selected.

High Lighted HM , we going to run Manually in next step

Health monitoring report

The checker generates a report of its execution in XML and stores the reports in ADR.
You can view these reports using either V$HM_RUN, DBMS_HM, ADRCI, or Enterprise Manager.

Run Health Check Manually

SQL> exec dbms_HM.RUN_CHECK('Database Dictionary Check');

PL/SQL procedure successfully completed.

$adrci
adrci>SHOW HM_RUN

ADR Home = /u01/app/oracle/diag/rdbms/orcl2/orcl2:

*************************************************************************

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

RUN_ID 1
RUN_NAME HM_RUN_1
CHECK_NAME Database Cross Check
NAME_ID 2
MODE 2
START_TIME 2008-08-05 04:01:56.783059 +05:30
RESUME_TIME
END_TIME 2008-08-08 04:02:04.007178 +05:30
MODIFIED_TIME 2008-08-08 04:02:04.007178 +05:30
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE

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

RUN_ID 21

RUN_NAME HM_RUN_21

CHECK_NAME Database Dictionary Check
NAME_ID 24
MODE 0
START_TIME 2008-08-13 23:09:43.831573 +05:30
RESUME_TIME
END_TIME 2008-08-13 23:09:47.713191 +05:30
MODIFIED_TIME 2008-08-14 00:03:01.470031 +05:30
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm/HMREPORT_HM_RUN_21.hm

2 rows fetched

Create HM Report

adrci>>CREATE REPORT HM_RUN HM_RUN_21

You can create and view Health Monitor checker reports using the ADRCI utility. Make sure that Oracle environment variables are set properly, The ADRCI utility starts and displays its prompt as shown above.
You then enter the SHOW HM_RUN command to list all the checker runs registered in the ADR repository. Locate the checker run for which you want to create a report and note the checker run name using the corresponding RUN_NAME field. you can generate the report using the CREATE REPORT HM_RUN command. You view the report using the SHOW REPORT HM_RUN command or by running dbms_hm.get_run_report on sql prompt

View HM generated reports on SQL prompt

SQL> set long 1000000
SQL> select dbms_hm.get_run_report('HM_RUN_21') from dual;

DBMS_HM.GET_RUN_REPORT('HM_RUN_21')

--------------------------------------------------------------------------------
................

View HM generated reports on adrci prompt

adrci>>show report hm_run HM_RUN_21

View HM generated reports on OS level ( In ADR repository )

$cd $ADR_HOME/hm
$ pwd
/u01/app/oracle/diag/rdbms/orcl2/orcl2/hm

$ ls

HMREPORT_HM_RUN_21.hm

$ more HMREPORT_HM_RUN_21.hm

Conclusion

That was just a quick glance at some of the new features in Oracle Database 11g Release 1. Now I'm off to read the documentation in full so I can take advantage of all the new stuff.

Oracle Database 11g Release 1 (11.1) Installation ( CRS and Database Software )

2007-08-12T00:10:00.000-05:00

Oracle Database 11g Release 1 (11.1) Installation ( CRS and Database Software )

Virag Sharma virag123@gmail.com

By default Oracle Enterprise Installation(OEL) installation not include following packages, so install it before starting Oracle Database & CRS 11g installation

The following packages (or later versions) must be installed on top of default OEL default installation

elfutils-libelf-devel-0.97

unixODBC-2.2.11

unixODBC-devel-2.2.11

As per Oracle Documentation
Packages for Asianux 2, Oracle Enterprise Linux 4.0, and Red Hat Enterprise Linux 4.0
The following packages (or later versions) must be installed: binutils-2.15.92.0.2-18
compat-libstdc++-33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c++-3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc++-3.4.5-2
libstdc++-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11

Download Oracle Database 11g Release 1 (11.1.0.6.0) (1.7 GB)
Oracle Database 11g Release 1 Documentation Library
Oracle® Database Concepts 11g Release 1 (11.1)
Oracle® Database Upgrade Guide 11g Release 1 (11.1)
Oracle® Database New Features Guide 11g Release 1 (11.1)
“What’s New in 11g” search on documentation
Oracle Database 11g: The Top New Features for DBAs

ADR (Automatic Diagnostic Repository)
Database Replay
Partitioning to Perfection
Schema Management
Installing 11g Release 1 on Xubuntu 7.04 by Howard Rogers

Oracle 11g IPS ( Incident Packaging Service)

2007-07-29T12:02:00.000-05:00

Oracle 11g new Features , IPS ( Incident Packaging Service)

Virag Sharma virag123@gmail.com

To make oracle support better , oracle 11g introduce new feature / Service known IPS ( Incident Packaging Service ) . Basically it is a part/extention of Oracle 11g new feature ADR ( Automatic Diagnostic repository)[Click Here for details ]. ADR is new concept in oracle 11g and it is just a file based repository of diagnostic data. ADR gives lot fexibility to maintain / handle diagnostic data.

When critical errors(i.e Problem) are detected, they automatically create an “incident” ( one occurrence of problem is Incident i.e relation between problem and incident is one to many).
Information(trace ,dump ) related to the incident is automatically captured in file based repository known as ADR ( Automatic Diagnostic repository), all the incident related files are taged with Incident Number and certain health checks are run automatically. This information can be packaged to be sent to Oracle support

Incident Packaging Service (IPS) wraps up all information about an incidentand allows you to send the whole package to Oracle Support.

Here is steps to collect and send data to support using IPS

$ adrci
adrci> help ips
adrci> show incident
( For example above command show incident No 9817 for ORA-600 [XYZ] )

adrci> ips create package incident 9817 <= ( it will give package No.)
adrci> ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical

adrci> ips add incident 9817 package 4
Added incident 9817 to package 4

adrci>

adrci>>ips add file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4

Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
package 4

adrci>>ips generate package 4 in /tmp
Generated package 4 in file /tmp/ORA600kci_20070514184516_COM_1.zip, mode
complete
adrci>>

Send above created file to Support

TAG = > Oracle 11g New Features , Oracle 11g tips and tricks, Oracle Database 11g New Features for DBA, Oracle Database 11g New Features

How to check alert.log in Oracle 11g

2007-07-27T08:46:00.002-05:00

How to check alert.log in Oracle 11g

Virag Sharma virag123@gmail.com

Oracle 11g introduce new tool/utility called ADRCI known as ADR command line tool.
This tool allow user to interact with ADR ,check alert log, check health monitor(HM) status ,

Package incident and problem information into a zip file for send to Oracle Support. Etc.

No username/password need to log in to ADRCI, ADRCI interact with file system and ADR data is secured only by operating system permissions on the ADR directories.

$adrci
adrci> set editor vi
adrci> show alert ( it will open alert in vi editor )
adrci> show alert -tail ( Similar to Unix tail command )
adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )
adrci> show alert -tail -f ( Similar to Unix command tail -f )

adrci> show alert -tail 100 -f ( Similar to tail -100f )

Too list all the "ORA-" error run following command

$adrci
adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"

In 11g alert file is saved in 2 location, one is in alert directory ( in XML format log.xml ) and
old style alert file in trace directory. Both these directory reside inside ADR HOME. ADR (Automatic Diagnostic Repository ) is a file system based directory structure outside of the database, So you can have access of diagnostic data even database is down.

New initialization parameter DIAGNOSTIC_DEST decide where to store ADR data ( i.e diagnostic data). In oracle 11g parameter background_dump_dest, core_dump_dest, user_dump_dest is replaced by DIAGNOSTIC_DEST. Even you set background_dump_dest, core_dump_dest, user_dump_dest it not going to used i.e. 11g ignore these parameters.

SQL> show parameter diagnostic_dest

NAME TYPE VALUE
--------------- --------------------------- -------------------------

diagnostic_dest string /u01/app/oracle

More Oracle Database 11g New Features

TAG = > Oracle 11g New Features , Oracle 11g tips and tricks , Oracle Database 11g New Features for DBA , Oracle Database 11g New Features

Oracle 11g and ACL ( access control list )

2007-07-14T07:00:00.001-05:00

Oracle 11g and ACL ( access control list )

Virag Sharma virag123@gmail.com

If your application code ( package , procedure , function ) uses utl_tcp , utl_smtp etc and you upgraded database to oracle 11g , then that code will not work even you gave execute permission on utl_tcp etc to user

You need execute addition steps and add user to ACL ( access control list )

How to check XML DB installed or not

.

SQL> select COMP_NAME, status from dba_registry;

COMP_NAME STATUS
-------------------------------------------- --------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Real Application Clusters VALID
Oracle XML Database VALID

...

How to install XML DB
If above query not show XML DB component then run following sql to install XML DB

sqlplus "/ as sysdba"
SQL>@?/rdbms/admin/catqm.sql

How to add user to ACL
Following example show , how add scott user to ACL

SQL> exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('MY.xml',
'Comments ..', 'SCOTT', TRUE, 'connect');


SQL>exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('MY.xml','*.abc.com');
SQL> Commit;

Note :- Don't forget to commit

As per Oracle 11g Upgrade manual

Configure Fine-Grained Access to External Network Services

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE

 acl_path  VARCHAR2(4000);

BEGIN

 SELECT acl INTO acl_path FROM dba_network_acls
  WHERE host = 'host_name' AND lower_port 
IS NULL AND upper_port IS NULL;

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,
                            'user_name','connect') IS NULL THEN

   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
                              'user_name', TRUE, 'connect');

END IF;

EXCEPTION

 WHEN no_data_found THEN

   DBMS_

NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml',

     'ACL description', 'user_name', TRUE, 'connect');

   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');

END;

COMMIT;

UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR PL/SQL Packages

A new security measure is introduced in this release for the following network-related
PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR.
The invoker of those packages needs additional privileges to connect to an external
host or to resolve the name or the IP address of a host. The packages check the
invoker for the necessary privileges only when the calls are made at runtime and
raises an exception if the invoker lacks the privileges. This new security measure is
implemented by XML DB's access control list (ACL) mechanism and, therefore,
requires XML DB to be installed in order to use those packages.

Each external host that a database user wants to connect to or to resolve the name
or IP address for from the database is restricted by an access control list (ACL).
To grant a user the privileges for the host, the database administrator should create
an ACL, add the privileges to the ACL for the user, assign the ACL to the host
and commit the changes using the DBMS_NETWORK_ACL_ADMIN PL/SQL package.
For example, to give the users SCOTT and ADAMS the permission to connect
to www.oracle.com via HTTP (namely to connect to TCP/IP port 80), the
database administrator should execute the following:

SQL> REM Creates a new ACL and adds SCOTT the privilege

SQL> REM to the ACL to make TCP connections

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('acl_for_oracle.xml', -

> 'ACL for www.oracle.com', 'SCOTT', TRUE, 'connect')

SQL> REM Adds ADAMS the privilege to the ACL to make TCP connections also

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('acl_for_oracle.xml', -

> 'ADAMS', TRUE, 'connect')

SQL> REM Assigns the new ACL to www.oracle.com for TCP/IP port 80 (HTTP)

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('acl_for_oracle.xml', -

> 'www.oracle.com', 80)

SQL> REM Commits to make the ACL take effect

SQL> COMMIT

The invoker of the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL packages needs the 'connect' privilege to make TCP/IP, HTTP, or SMTP connections to the external host. The invoker of the UTL_INADDR package needs the 'resolve' privilege to resolve the name or the IP address of the external host. Note that those privileges are not granted through the GRANT SQL statement but through the DBMS_NETWORK_ACL_ADMIN package.

The current ACL assignment to external hosts and the privileges currently defined
in the ACLs are shown through the system catalog views DBA_NETWORK_ACLS
and DBA_NETWORK_ACL_PRIVILEGES.

The invokers of other database components that use those PL/SQL packages to
perform network operations from the database, which are XML DB's
HttpUriType, Oracle Multimedia (formerly interMedia), and Spatial, are
subject to the same network permission check and need the same privileges.

TAG=> Oracle 11g New Features, Oracle 11g tips and tricks, Oracle Database 11g New Features for DBA, Oracle Database 11g New Features

Oracle 11g new features

2007-07-12T20:46:00.000-05:00

Oracle 11g New Features

Virag Sharma virag123@gmail.com

As all other DBA , me too interested in Oracle 11g new feature to started reading/writing some good features of Oracle 11g.

For details about these Oracle 11g New features have a look on Site [ Click here ]

If you want to see summary about Oracle database Have a look on following link

Oracle Database 11g New Feature Summary [ Click Here ]

Above link also contains lots of other Best Site on 11g links and documents.

TAG => Oracle 11g New Features, Oracle 11g tips and tricks, Oracle Database 11g New Features for DBA, Oracle Database 11g New Features

Oracle Database 11g Launch party

2007-07-12T09:21:00.000-05:00

Oracle Database 11g Launch party

Yesterday there was Oracle Database 11g launch party , and after launch part otn.oracle.com published New Feature of Oracle Database 11g

http://www.oracle.com/technology/products/database/oracle11g/pdf/database-11g-new-features-whitepaper.pdf

Also otn.oracle.com published lots of Oracle Database 11g Focus Area Whitepapers

http://www.oracle.com/technology/products/database/oracle11g/index.html

As a all other DBA , I am intersted in on following Oracle 11g Database new features

Check/Click here to see following New Feature of Oracle Database 11g

1) Automatic Diagnostic Repository

2) Database Replay

3) Automatic Memory Tuning

4) Case sensitive password

5) Virtual columns and indexes

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management and SQL Plan Baselines

10) SQL Access Advisor & Partition Advisor

11) SQL Query Repair Advisor

12) SQL Replay Advisor

13) DBMS_STATS Enhancements

14) The Result Cache

Database 10g Express Edition

2007-06-29T11:36:00.000-05:00

Database 10g Express Edition

Click on topic to read in detail about Oracle XE

Database RAC DataGaurd and APPS

How to rename SQL Tuning Set ( STS )

Sangam’10: All-India Oracle Users Group (AIOUG) Annual Conference

Certain things in this world

Module name for logon trigger in 11g R2 AWR report

Oracle Database 11g Release 2 New Features : Edition based redefination

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name

11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT

From Oracle to a Successful Vineyard

NEW option in ADRCI purge acommand - UTSCDMP

AIOUG - TechNight in Bangalore

TechNight All India Oracle User Group (AIOUG)

DBMS_STATS Enhancements in Oracle 11g Database

Preference name

Default Values

Published and Pending Statistics

11g RAC Database hanged or appears to hanged

Top User Events

Top Event P1/P2/P3 Values

Reactive performance management By Craig Shallahamer

What CPU Jan 2008 Patch will not tell you

Secrets of Happiness

Small change make difference

Oracle 11g Security Enhancements Part – 1

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,4

Oracle 11g NF Database Replay

Steps for Database Replay

Workload Capture

Note as per Oracle datasheet

2. Workload Processing

Workload Replay

Analysis and Reporting

Reference

Oracle 11g Database Replay

Oracle 11g New Features DBMS_ADDM for RAC

Displaying an ADDM Report

Oracle 11g Database New Features: Data Guard Enhancements

Some Good Links / Blogs / site to know more about New Oracle Database 11g features

Oracle 11g New Features SQL plan management (SPM)

Just for demo purpose , lets accept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995')

Reference

Oracle 11g New feaures Case Sensitive Password

SQL Performance Analyzer (SPA) Part - 2

TOTAL RECALL Oracle 11g New Features

Oracle SQL REPLAY & Sql Performance Analyzer(SPA) Part 1

Steps for SQL REPLAY

1. Collect SQL

-- Checking the Status of a SQL Tuning Task

-- Checking the Progress of the SQL Tuning Advisor

--Displaying the Results of a SQL Tuning Task

In this article Sql Performance Analyzer Part 1,we just covered Sql Performance Analyzer (SPA) basic functionality , In part 2 I will cover more expects and case study of SPA

SQL Tuning Information Views

References

Automatic Diagnostic Repository (ADR)

Key Components for Fault Diagnosability Infrastructure

ADR (Automatic Diagnostic Repository)

Note : ADR_HOME is user define variable , I have define this variable make life easier

Note :- I have created on environment variable ADR_HOME= . I am using same in all my this document

Retention policy

Change Retention

Automatic Diagnostic Repository (ADRCI)

Checking alert log with ADRCI

Problem and Incident

Problem

Incident

IPS ( Incident package service )

Create logical package

Add files to package

Create zip file to send to oracle support

IPS in Summary

Health Monitor (HM)

Kinds of health monitoring

Health monitoring report

Run Health Check Manually

RUN_NAME HM_RUN_21

Create HM Report

View HM generated reports on SQL prompt

View HM generated reports on adrci prompt

View HM generated reports on OS level ( In ADR repository )