Subscribe: Database RAC DataGaurd and APPS
Added By: Feedage Forager Feedage Grade A rated
Language: English
audit trail  audit  database  dbms stats  dbms  expressed  necessarily  new features  new  oracle  scan  sql  stats  trail  views 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Database RAC DataGaurd and APPS

Database RAC DataGaurd and APPS

Blog create to share my Oracle DBA experience with Oracle User community to make Oracle usage easy and enjoyable.

Updated: 2018-01-15T11:40:41.409-05:00


When error say nothing about real issue


We want restore backup from source. So we took backup from source and copied it to /dba/share/MYDB on target. My collouge sent me email saying  restore failed with following error

rman target /

Recovery Manager: Release - Production on Wed Sep 18 20:47:38 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    8551575552 bytes

Fixed Size                     2245480 bytes
Variable Size                503319704 bytes
Database Buffers            8036286464 bytes
Redo Buffers                   9723904 bytes

RMAN> set DBID=2387922???;

executing command: SET DBID

RMAN> restore controlfile from '/dba/share/MYDB/c-2387922???-20130910-00.ctl';

Starting restore at 18-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=391 device type=DISK

channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/18/2013 20:48:04
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

We searched MOS and forums. Most of the search , pointing it is old backup ( more then 7 day ),  some doc say backup corrupted etc etc. Instead going for whole story that how reached to conclusion, let we tell you the finding.

We found the mount option for /dba/share/MYDB ( where we copied backup from source ) not correct
...that's why getting error. We changed it to

           rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp,noacl 0 0

After this change we able to restore control file and rest of the backup successfully

How to rename SQL Tuning Set ( STS )


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
l_cursor DBMS_SQLTUNE.sqlset_cursor;
-- Create STS
DBMS_SQLTUNE.create_sqlset(sqlset_name => 'STS_DBNAME_CMUL',description => 'new STS SQL tuning set from XYZ DB');
OPEN l_cursor FOR
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
close l_cursor;

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


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



"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


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)

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

Module: oraagent.bin@apps001 (TNS V1-V3)

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


Oracle Database 11g Release 2 New Features : Edition based redefinationVirag SharmaEvery 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 limitationCheck Default Edition SQL>1 SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES2* 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...Reference19 Edition-Based RedefinitionMore Post on Oracle RDBMS Database 11g R2 ( Release 2 ) Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMTOracle Database 11g Release 2 New Features : Edition based redefinationThe views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine[...]

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


Oracle 11g R2 (11.2 ) New Features : SCAN - Single Client Access NameVirag SharmaOnce you decided to add/remove node from RAC database/cluster. We need to change tnsentry. 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 fromthe 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_listenerSCAN Listener LISTENER_SCAN1 is enabledSCAN listener LISTENER_SCAN1 is running on node apps001SCAN Listener LISTENER_SCAN2 is enabledSCAN listener LISTENER_SCAN2 is running on node apps002SCAN Listener LISTENER_SCAN3 is enabledSCAN listener LISTENER_SCAN3 is running on node apps002Running 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 -inheritoracle 9380 1 0 Aug13 ? 00:01:09 /d01/apps/oracle_crs/11.2/bin/tnslsnr LISTENER_SCAN3 -inheritoracle 9993 7114 0 09:57 pts/3 00:00:00 grep -i crsFrom above output, it is clear that SCAN listener is running from CRS_HOME $ srvctl config scan_listenerSCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521$ srvctl config scanSCAN name: apps-scan, Network: 1/ VIP name: scan1, IP: / VIP name: scan2, IP: / VIP name: scan3, IP: / entry can use single address ( SCAN Name ) in tnsentry , instead os using entry for all Nodetns entry configured to use VIP addresses for Database will work without any issue. usingSCANs is not Medatory ( May be to support backward compatibility ) ## TNS ENTRY with =(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)( TNS Entry without SCAN ( Old way) =(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)( Can connect to a particular instance of the database using SCAN. Entry will looks like =(description=(address=(protocol=tcp)( entry configured to use VIP addresses for Database will work without any issue. usingSCANs is not Medatory ( May be to support backward compatibility )More Post on Oracle RDBMS Database 11g R2 ( Release 2 ) Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMTOracle Database 11g Release 2 New Features : Edition based redefinationThe views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine[...]

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


11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMTVirag Sharma In earlier version there is no standard way to change AUDIT tables tablespace.IN Oracle 11g R2 ( Also included in and ( Need to check)) , you can change audit table (SYS.AUD$ and SYS.FGA_LOG$) tablespace using DBMS_AUDIT_MGMTNot 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--------------------------------------------------------------------------------SYSTEMChanging Tablespace from SYSTEM to SYSAUX for AUD$SQL>BEGINDBMS_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 TablespaceSQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$';TABLESPACE_NAME--------------------------------------------------------------------------------SYSAUXAUDIT_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 initializationBEGINIFNOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)THENdbms_output.put_line('CLEANUP NOT INITIALIZED' );ELSEdbms_output.put_line('CLEANUP INITIALIZED' );END IF;END;# Set initializationBEGINDBMS_AUDIT_MGMT.INIT_CLEANUP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,DEFAULT_CLEANUP_INTERVAL => 6 );END;# Set Last Audit Time stampSQL> desc DBA_AUDIT_MGMT_LAST_ARCH_TSName Null? Type----------------------------------------- -------- ----------------------------AUDIT_TRAIL VARCHAR2(20)RAC_INSTANCE NOT NULL NUMBERLAST_ARCHIVE_TS TIMESTAMP(6) WITH TIME ZONESQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;# Check is Last Audit Time stamp set or not BEGINDBMS_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 PurgeBEGINDBMS_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 =>[...]

From Oracle to a Successful Vineyard


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

NEW option in ADRCI purge acommand - UTSCDMP


NEW option in ADRCI purge acommand - UTSCDMPThere is new option in ADRCI for purge command - UTSCDMP adrci> help purgeUsage: PURGE [[-i ][-age [-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]]]:Purpose: Purge the diagnostic data in the current ADR home. If nooption 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:purgepurge -i 123 456purge -age 60 -type incidentThere 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 The views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mineOther ADR/ADRCI related posts Automatic Diagnostic Repository (ADR)Oracle 11g new Features , IPS ( Incident Packaging Service)How to check alert.log in Oracle 11gThe views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine[...]

AIOUG - TechNight in Bangalore


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)


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

(image) Phani Arega


DBMS_STATS Enhancements in Oracle 11g Database


DBMS_STATS Enhancements in Oracle 11g DatabaseVirag Sharma virag123@gmail.comWe 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 - Production on Thu Mar 6 19:04:57 2008SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual;DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')--------------------------------------------------------------------------------DBMS_STATS.AUTO_SAMPLE_SIZESQL>SQL> SELECT dbms_stats.get_param('method_opt') FROM dual;DBMS_STATS.GET_PARAM('METHOD_OPT')--------------------------------------------------------------------------------FOR ALL COLUMNS SIZE AUTOSQL> SELECT dbms_stats.get_param('GRANULARITY') FROM dual;DBMS_STATS.GET_PARAM('GRANULARITY')--------------------------------------------------------------------------------AUTOSQL> SELECT dbms_stats.get_param('NO_INVALIDATE') FROM dual;DBMS_STATS.GET_PARAM('NO_INVALIDATE')--------------------------------------------------------------------------------DBMS_STATS.AUTO_INVALIDATESQL> SELECT dbms_stats.get_param('DEGREE') FROM dual;DBMS_STATS.GET_PARAM('DEGREE')--------------------------------------------------------------------------------NULLSQL> SELECT dbms_stats.get_param('CASCADE') FROM dual;DBMS_STATS.GET_PARAM('CASCADE')--------------------------------------------------------------------------------DBMS_STATS.AUTO_CASCADESQL> 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')---------------------------------------------------------------------100SQL> 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_CASCADESQL> Select dbms_stats.GET_PREFS('DEGREE') from dual;DBMS_STATS.GET_PREFS('DEGREE')--------------------------------------------------------------------------------NULLSQL> Select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual;DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')--------------------------------------------------------------------------------DBMS_STATS.AUTO_SAMPLE_SIZESQL> Select dbms_stats.GET_PREFS('METHOD_OPT') from dual;DBMS_STATS.GET_PREFS('METHOD_OPT')--------------------------------------------------------------------------------FOR ALL COLUMNS SIZE AUTOSQL> Select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual;DBMS_STATS.GET_PREFS('NO_INVALIDATE')--------------------------------------------------------------------------------DBMS_STATS.AUTO_INV[...]

11g RAC Database hanged or appears to hanged


11g RAC Database hanged or appears to hangedAround lunch time 1:00 PM database hanged. When we tried to login as sysdbaour 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 loginps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9When your database hanged and you not able to login, we have used following procedureto create hang analysis dumpsqlplus /nolog SQL> set _prelim onSQL> connect / as sysdbaSQL> oradebug setmypidStatement processed.SQL> oradebug unlimitStatement processed.SQL> oradebug -g all hanganalyze 3SQL> oradebug -g all hanganalyze 3Cycle 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'[...]

Reactive performance management By Craig Shallahamer



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 Oracl(image) e 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


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,

Updating archive file "/u01/app/oracle/product/" with "lib/libserver11.a/kzsr.o"
Updating archive file "/u01/app/oracle/product/" 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/ ''

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

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


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]

  1. What you put in life , you get back
  2. 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
  3. 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


Small change make difference.How small , small things make difference , here is one live exampleOne 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 -aSunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-EnterpriseMYSUN:oracle> ( /usr/proc/bin$ ps -aef grep pmonoracle 2424 1 0 Jan 18 ? 13:39 ora_pmon_testoracle 8337 13002 0 05:31:47 pts/7 0:00 grep pmonmysun:oracle> ( /usr/proc/bin$ pwdx 24242424: /u01/app/oracle/product/[root@apps001 proc]# ps -aef grep pmonoracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XEoracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11groot 23180 13728 0 23:19 pts/2 00:00:00 grep pmon[root@apps001 proc]# ls -l /proc/8268total 0dr-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 cmdlinelrwxrwxrwx 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 environlrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 exe -> /u01/app/oracle/11.1.0/bin/oracledr-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 mountslrwxrwxrwx 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 statusdr-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_HOME=/u01/app/oracle/11.1.0/export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATHexport PATHORACLE_SID=orcl11gexport ORACLE_SID~"11g.env" 7L, 136C written[oracle@apps001 ~]$chmod 755 11g.env[oracle@apps001 ~]$ . ./11g.envShe 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 - Production on Sun Jan 27 23:45:42 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to an idle instance.Ctl-d[oracle@apps001 ~]$ ps -aef grep pmonoracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XEoracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11goracle 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 25372lkORCL11G: 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]$ adrciADRCI: Release - Beta on Sun Jan 27 23:52:16 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.ADR base = "/u01/app/oracle"adrci> set editor viadrci> sho[...]

Oracle 11g Security Enhancements Part – 1


Oracle 11g Security Enhancements Part – 1New parameters have been added to the Oracle Database 11g to enhance the default security of the database.SEC_RETURN_SERVER_RELEASESEC_PROTOCOL_ERROR_FURTHER_ACTIONSEC_PROTOCOL_ERROR_TRACE_ACTIONSEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTSSEC_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_BANNERNAME 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) attacksThe 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 packetSEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue#Delay 4 Sec before sever accept next connectionSEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,4 # Drop connection after 10 bad packetSEC_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 orSEC_PROTOCOL_ERROR_TRACE_ACTION = None>SQL> show parameter SEC_PROTOCOL_ERROR_FURTHER_ACTIONNAME TYPE VALUE------------------------------- ------------------- ------------------------sec_protocol_error_further_action string CONTINUESQL> show parameter SEC_PROTOCOL_ERROR_TRACE_ACTIONNAME TYPE VALUE---------------------- ------------------------- -----------------------sec_protocol_error_trace_action string TRACEProtect against intruderIf 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_ATTEMPTSNAME 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 disab[...]

Oracle 11g NF Database Replay


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 OTNDatabase 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 changesOperating 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 BEGINDBMS_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_dirAS '/u04/oraout/test/db-replay-capture'; Remember in case on Oracl[...]

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 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

Oracle 11g New Features DBMS_ADDM for RAC


Oracle 11g New Features DBMS_ADDM for RACVirag Sharma virag123@gmail.comIn “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 scriptB. Use the DBA_ADDM viewC. Use the DBA_ADVISOR viewD. Use the DBMS_ADDM packageAnswer “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.sqlrun 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_ADDMIn Instance mode DBMS_ADDM , analyze one particular instanceVAR 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; The views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine[...]

Oracle 11g Database New Features: Data Guard Enhancements


Oracle 11g Database New Features: Data Guard EnhancementsVirag Sharma virag123@gmail.comWhy We need Data Guard ?? forData 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 enhancement1) Data Protection Advanced Compression Lost-write protection Fast-Start Failover2) Increase ROI Active Data Guard Snapshot Standby3) High Availability Faster Redo Apply Faster failover & switchover Automatic Failover using ASYNC4) Manageability Mixed Windows/LinuxActive Data Guard Oracle Active Data Guard 11g – a new Database OptionEnables read-only access to a physical standby database while Redo Apply is activeIt 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 DISABLELOG_ARCHIVE_DEST_2 ENABLELOG_ARCHIVE_DEST_3 DISABLELOG_ARCHIVE_DEST_4 DISABLELOG_ARCHIVE_DEST_5 DISABLELOG_ARCHIVE_DEST_6 DISABLELOG_ARCHIVE_DEST_7 DISABLELOG_ARCHIVE_DEST_8 DISABLELOG_ARCHIVE_DEST_9 DISABLELOG_ARCHIVE_DEST_10 DISABLE10 rows selected.Data ProtectionNew Initialization parameter db_lost_write_protect Introduced. This parameter Compare versions of blocks on the standby with that in the incoming redo streamVersion discrepancy implies lost write on either primary or standby database db_lost_write_protectManagibilty Enhancement SYS user and pass[...]

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


Some Good Links / Blogs / site to know more about New Oracle Database 11g featuresVirag Sharma virag123@gmail.comI have rated 5 *(star) for some sites , which I like Most( these links collected from google alert ) Oracle Database Online Documentation 11g Release 1 (11.1) *****ORACLE-BASE - Articles on Oracle 11g new features *****Oracle Database 11g on OTNOracle Database 11g: The Top Features for DBAs and DevelopersPythian Group Blog » Tuning Pack 11g : Real-Time SQL MonitoringPSOUG - New in 11gR1*****Robert G. Freeman’s Blog: 11g Security New Feature… A short one…Oracle 11g new Features Summary by BurlesonMy top 10 Oracle 11g New Features Part 3 - 5 nice optimizer statisticsOracle 11g Top New Features for DBA Virag SharmaOracle Magazine: PL/SQL Practices: On the PL/SQL Function Result CacheChanges in Oracle 11.1 - Julian Dyke ***** Blogging about 11g - Part 7 - Function Result CacheOracle 11g New Features SQL plan management (SPM)Oracle 11g New feaures : Case Sensitive PasswordSQL Performance Analyzer (SPA) Part - 2Oracle 11g ADR Automatic Diagnostic RepositoryNew Parameters in 11g (Part 2) | Dizwell InformaticsOracle 11g DRCP: Database Resident Connection Pooling - second attemptOracle 11g internals part 1: Automatic Memory ManagementMy top 10 Oracle 11g New Features Part 4 - the SQL Query Result Cache Oracle 11g Tips by Burleson ConsultingYou 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 ViThe views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine[...]

Oracle 11g New Features SQL plan management (SPM)


Oracle 11g New Features SQL plan management (SPM)Virag Sharma[Click Here For PDF document, more readable doc]You might have noticed that execution plan changed in CBO , specially in following case Database UpgradeDatabase / Schema Stats collectionChange in environment ( LinkUnix to Linux Migration )Change in dataCase: 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 ( , 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 , 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 database1) 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 baselines5) Evolving SQL Plan Baselines1) 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 fileOPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true OR 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');DECLAREsts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';BEGINdbms_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[...]