Wednesday, May 6, 2009

Oracle Upgrade DB to 10g in oracle apps 11.5.10.2


10g (10.2.0.3) Database Upgrade

Note: upgrade from 9.2.0.7 to 10.2.0
OS: Sun 5. 10
Node: Multi node


10g Upgrade Information
Software Requirements
Software
Patches
Documents
Prerequisite
Upgrade
Before the Database Installation
Database Installation:
Database Upgrade
Post Upgrade Step (Conditional)
HR EEO Patch
Appendix
Initialization parameters set for 10G database (TAG) on 11i
Before 10g upgrade
After 10g Upgrade

10g Upgrade Information

Server: oadevdb03

SID: TAG

Port: (1547)

Location: /u26/oracle/oraTAG/TAGdb/10.2.0

Oracle DB version: 10.2.0.3.0

Time taken: 5 hours (approx)


Software Requirements

Software

� Oracle Database 10g (10.2.0.1) DVD.

Patches

� 4653225 (11.5.10 INTEROP PATCH FOR 10GR2)
� 5337014 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)
� 6880880 (OPatch 10.2)
� 5892355 (ASSERT IN KOPEPIC THROWING 22635 INTERNAL ERROR NEEDS TO BE REMOVED)
� 5871314 (PICKLER DOES ADD NULL VALUE IN IMAGE FOR KOTT* WHEN THE POINTER IS NULL)
� 5753621 (ONE-OFF REQUEST ON TOP OF 11.5.9 CU2 FOR BUG 5525015 Product Receivables)
� 5511810 (ETHNIC CODE AND JOB CATEGORY CHANGES FOR 2007)

Documents

� Metalink Note# 362203.1 (Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0))
� Oracle Database Upgrade Guide 10g Release 2
� Metalink Note# 353150.1 (OPatch Failing Validation Phase When Archiving Really Did Not Fail)
� Metalink Note# 216205.1 (Database Initialization Parameters for Oracle Applications Release 11i)
� Metalink Note# 438049.1 (How To Find RDBMS patchsets on Metalink)
� Metalink Note# 165195.1 (Using AutoConfig to Manage System Configurations with Oracle Applications 11i)



Prerequisite

� Cold Backup of entire instance.
Unix system parameter
Create magic ticket for following
Add set noexec_user_stack=1 in the /etc/system file (will be done by sysadmin)
Then reboot the system.
Perform this step a week before in Production.


Upgrade

We are following section 1 of Note # 362203.1.
Before the Database Installation

1. Verify software version (15 minutes)
� Apply patch 4653225 interoperability patch for 10gR2.
� Download patch 4653225 for both Us, Japanese Language.
� Make sure only database and listener are running.
� Count number of invalids objects
� Enable maintenance mode using adadmin utility.
� Perform following under dbnode (oadevdb03)
Unzip p4653225_11i_SOLARIS.zip
cd 4653225
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
Unzip p4653225_11i_SOLARIS.zip
cd 4653225
use adpatch utility to apply patch
Perform following under dbnode (oadevdb03)
Unzip p4653225_11i_ja.zip
cd 4653225_JA
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
Unzip p4653225_11i_ja.zip
cd 4653225_JA
use adpatch utility to apply patch
Disable maintenance mode using adadmin utility.
Shutdown Database and listener.

2. Migrate to Oracle Portal 10g
� Skip as we are not using the Oracle Portal.

3. Deregister the current database server
� Skip as we are not planning to change database port, host, SID

4. Update application tier context file with new database listener port number
� Skip

5. Export OLAP analytical workspaces
� Skip

Database Installation:

6. Prepare to create the 10.2.0 Oracle home (10 minutes)
� Please follow Chapter 2 of 10g Upgrade guide also.
� Create following directory
cd /u26/oracle/oraTAG/TAGdb
mkdir 10.2.0
Change orainventory location in oadevdb03
cd /var/opt/oracle
vi oraInst.loc
Add
inventory_loc=/u26/oracle/oraTAG/TAGdb/10.2.0/oraInventory
and comment all other locations
Change .profile of oracle user (oraTAG)
Add
export ORACLE_HOME=/u26/oracle/oraTAG/TAGdb/10.2.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl/5.8.3
export PERL5LIB
ORA_NLS10=$ORACLE_HOME/nls/data/9idata
export ORA_NLS10
export ORACLE_SID=TAG
Comment
export DBDIR=/u26/oracle/oraTAG/TAGdb/9.2.0
PATH=/u26/oracle/oraTAG/TAGora/iAS/Apache/perl/bin:/usr/j2sdk1.4.2_06/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/usr/ucb:/etc/vx/bin:/usr/ccs/bin:/etc:/usr/lib:/usr/openwin/bin:/home/oraTAG/edge:.
export PATH
PERL5LIB=/u26/oracle/oraTAG/TAGora/iAS/Apache/perl/lib/5.00503:/u26/oracle/oraTAG/TAGora/iAS/Apache/perl/lib/site_perl/5.005
Export PERL5LIB
. /u26/oracle/oraTAG/TAGappl/APPSORA.env

7. Install the base 10.2.0 software
� Install the base 10.2.0 software. (20 minutes)
� export DISPLAY=10.x.x.x:0.0
� verify DISPLAY setting by invoking the xclock command
� cd /u26/oracle/oraTAG/soft
� ./runInstaller
� Need root access to run root.sh (especially in Production)


8. Install Oracle Database 10g Products from the 10g Companion CD
� Skip. Not Required.

9. Perform 10.2.0.3 patch set pre-installation tasks
� Download Patchset 3 (5337014)
� Unzip patch under /u26/oracle/oraTAG/soft/patchset

10. Perform 10.2.0.3 patch set installation tasks (15 minutes)
� Install the patchset
� export DISPLAY=10.x.x.x:0.0
� verify DISPLAY setting by invoking the xclock command
� cd /u26/oracle/oraTAG/soft/patchset/Disk1
� ./runInstaller
� Need root access to run root.sh (especially in Production)

11. Create nls/data/9idata directory (5 minutes)
� cd $ORACLE_HOME (10g Oracle Home)
� check perl location ( it should be /usr/bin/perl)
� cd nls/data/old
� perl cr9idata.sql(pl)
� It will create $ORACLE_HOME/nls/data/9idata directory
� After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.
20th � 27th November 2008Take cold bacup of entry system.

Cd $ORACLE_HOME/OPatch
./opatch apply /u26/oracle/oraTAG/soft/patchset/5871314

opatch lsinventory
% cd 5892355
% opatch rollback -id 5892355

1) echo $ORACLE_HOME(10.2.0.3)
2) check the oracle_home in .profile
3)/var/opt/oracle below the files
oratab and oraInst.loc
4) $ORACLE_HOME/jdk/bin/java -fullversion
java full version "1.4.2_08-b03"
5) check perl version and location
/usr/bin/perl
6) setenv OBJECT_MODE 32_64 before applying opatch
Ensure environment variable OBJECT_MODE is set to 32_64
# % setenv OBJECT_MODE 32_64 or $ export OBJECT_MODE=32_64

7) Please get the confom the Opatch under $ORACLE_HOME
8) read care full README.txt
9) incase of failures.



12. Apply additional 10.2.0.3.0 RDBMS patches (20 minutes-> opatch_apply.sh)
� Download all patches (5892355,5871314,5755471,5752399 and 6880880)
� Unzip the patch 6880880 under $ORACLE_HOME for latest OPATCH version.
� Unzip patch 5892355
� cd 5892355
� opatch apply
� Verify the patch application. (./opatch lsinventory)
� Download in the /u26/oracle/oraTAG/TAGdb/10.2.0
� Copy the old version Opatch folder.
� Please ignore following error message for all the Patches
The following warnings have occurred during OPatch execution:
1) OUI-67124:Verification of 'ar' actions failed. This is a known issue for this platform. Please check the note 353150.1 on Metalink for the process to manually verify the files.
OPatch Session completed with warnings.
� Unzip patch 5871314
� cd 5871314
� opatch apply
� Unzip patch 5755471
� cd 5755471
� opatch apply
� Apply patch 5752399 to avoid the problem in step 15
� Unzip patch 5752399
� cd 5752399
� opatch apply OPATCH.SKIP_VERIFY=true
6880880
Step1 cd /u26/oracle/oraTAG/TAGdb/10.2.0/Opatch

(Folder copy)
cp-Rp/u26/oracle/oraTAG/TAGdb/10.2.0/OPatch /u26/oracle/oraTAG/TAGdb/10.2.0/OPatch_old

(File copy)
cpp6880880_102000_SOLARIS64.zip /u26/oracle/oraTAG/TAGdb/10.2.0/p6880880_102000_SOLARIS64.zip
Database Upgrade

13. Shutdown Applications server processes and database listener
� On each application tier server node, shut down all server processes or services. Also shutdown database and listener.
� Already done.

14. Prepare to upgrade
� Done.
28 Nov 2008 18:30 Hours (Take cold bacup of entry system.)

15. Upgrade the database instance. (2 hours)
� Please follow Chapter 3 and 4 of 10g Upgrade guide.
� Run the Pre-Upgrade Information Tool
� Start separate session with old 9i oracle home setting and start database
� Copy the utlu102i.sql file from the ORACLE_HOME/rdbms/admin directory of the new Oracle Database 10g release to old 9i oracle home.
� Login to sqlplus with sys user (10 minutes)
� spool 10gupg.log
� @ utlu102i.sql
� spool off;
� copy the 2 file utlu102i.sql and utltzuv2.sql (/tmp/utlu102i.sql , utltzuv2.sql)
� In Production, copy orapwPROD from old_oracle_home to new_oracle_home.
� Modify the initTAG.ora in new Oracle home with following changes

**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648
WARNING: --> "large_pool_size" needs to be increased to at least 8388608
WARNING: --> "session_max_open_files" needs to be increased to at least 20

**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"


Issues Requiring Further Analysis Prior to Upgrading

� Deprecated CONNECT Role
skip
Database Links With Passwords
TIMESTAMP WITH TIMEZONE Datatype
No effect
Release 8.1.7 National Character Set
skip
Optimizer Statistics
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(�SYS�); (20 minutes)
Shutdown database.
Copy the configuration file from old_home to new_home
Ifile
cp-Rp/u26/oracle/oraTAG/TAGdb/9.2.0/admin /u26/oracle/oraTAG/TAGdb/10.2.0/admin
Please verify following variables are set(New oracle1og Home)
ORACLE_SID
ORACLE_HOME
ORA_NLS10
LD_LIBRARY_PATH
cd $ORACLE_HOME/rdbms/admin(New Oracle Home)
connect to sqlplus as sysdba (10 minutes)
startup upgrade
CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf' SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;(/u26/oracle/oraTAG/TAGdata/sysaux01.dbf)
Default $ORACLE_HOME/dbs/sysaux01.dbf Move to above the location.
Gather the invalid objects( Count and object name)
spool upgrade.log 29th nov 2008 (15.30 � 16.50)Hours
@/u26/oracle/oraTAG/TAGdb/10.2.0/rdbms/admin/catupgrd.sql
(1 hour)
spool off;
Next shutdown immediate, restart for normal operation, and then
Run utlrp.sql to recompile any invalid application objects.
@utlu102s.sql TEXT
@/u26/oracle/oraTAG/TAGdb/10.2.0/rdbms/admin/utlrp.sql
At time of process you can the status of invalid objects.
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
(to compile the invalids) (30 minutes) 29th nov 2008 16.52 hours
It does not compile all invalids. Recompile later thru adadmin.(43 Invalid)
Gather the invalid objects( Count and object name)

Select substr(owner,1,12) owner,substr(object_name,1,30) object,
substr(object_type,1,30) type, status from dba_objects where status <> 'VALID';

Note: before upgrade (225) after upgrade 42000 invalid objects

16. Modify Initialization parameters
� Follow Note # 216205.1 to change Database initialization parameter in Oracle Application 11i.
� Please refer the Appendix
16A shutdown
Strtaup pfile= /u26/oracle/oraTAG/TAGdb/10.2.0/dbs/initTAG.ora
Spfile create spfile from pfile;


17. Additional database configuration
� Done

18. Perform 10.2.0.3.0 patch set post installation tasks
� Skip.

19. Install Data Mining and OLAP
� Skip.

20. Natively compile PL/SQL code
� Skip.

After the Database Upgrade

21. Fix Korean Lexers
� Skip as we didn�t find the script drkorean.sql under @$ORACLE_HOME/ctx/sample/script

22. Apply RDBMS patches for Daily Business Intelligence
� Skip.

23. Import OLAP analytical workspaces
� Skip.

24. Start the new database listener (5 minutes)
� cp tnsnames.ora and listener.ora from OLD_ORACLE_HOME/network/admin/TAG to ORACLE_HOME/network/admin
cp -Rp /u26/oracle/oraTAG/TAGdb/9.2.0/network/admin /u26/oracle/oraTAG/TAGdb/10.2.0/network/admin
� edit listener.ora and make following changes
change the location of Oracle home and Log file
� lsnrctl start TAG
Export TNS_ADMIN=/u26/oracle/oraTAG/TAGdb/10.2.0/network/admin/TAG_oadevdb03
25. Run adgrants.sql (2 minutes)
� Copy $APPL_TOP/admin/adgrants.sql to ORACLE_HOME
� sqlplus "/ as sysdba" @adgrants.sql APPLSYS

26. Grant create procedure privilege on CTXSYS (2 minutes)
� Copy $AD_TOP/patch/115/sql/adctxprv.sql to ORACLE_HOME
� sqlplus apps/ @adctxprv.sql CTXSYS

27. Implement and run AutoConfig (30 minutes) -- > 19 Nov 2008 20:20 Hours
� Follow Section 8 from Note# 165195.1
� Copy AutoConfig to the RDBMS ORACLE_HOME
� cp-Rp/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil /u26/oracle/oraTAG/TAGdb/10.2.0/appsutil
On the Application Tier (OADEVAPP03):
Log in to the APPL_TOP environment
Create appsutil.zip file
perl /bin/admkappsutil.pl
This will create appsutil.zip in $APPL_TOP/admin/out

On the Database Tier:
FTP the appsutil.zip file to the ORACLE_HOME
cd ORACLE_HOME
unzip �o appsutil.zip

cd $ORACLE_HOME
ls *.env
vi TAG_oadevdb03.env ( change the oracle_home path and unset APPL_TOP)
Generate your Database Context File (1st Dec 2008 1.pm IST)
cd ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=APPS
Enter Hostname of Database server: oadevdb03
Enter Port of Database server: 1551(1547)
Enter SID of Database server: TAG
Enter the value for Display Variable: oadevdb03:1.0
The context file has been created at:
/u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/TAG_oadevdb03.xml
Generate and Apply AutoConfig Configuration files
cd $ORACLE_HOME/appsutil/bin
adconfig.sh contextfile=/u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/TAG_oadevdb03.xml
run autoconfig.sh in appstier (both dbnode and appsnode)
DB node :
� /u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/scripts/TAG_oadevdb03 >./adautocfg.sh
� /u26/oracle/oraTAG/TAGcomn/admin/scripts/TAG_oadevdb03 >./adautocfg.sh
� Apps Node :
� /u26/oracle/oraTAG/TAGcomn/admin/scripts/TAG_oadevdb03 >./adautocfg.sh


28. Gather statistics for SYS schema (10 minutes) 1st Dec 2008 5.30pm
� Copy $APPL_TOP/admin/adstats.sql to ORACLE_HOME
� sqlplus "/ as sysdba"
� shutdown immediate;
� startup restrict;
� @adstats.sql;
� shutdown normal;
� startup;
� exit;

29. Re-create custom database links
� Not required.

PLATFORM=SunOS; export PLATFORM (NOTE:293441.1)

30. Re-create grants and synonyms(10 minutes) 3rd Dec 2008 8.30pm
Adadmin
Recreate grants and synonyms option
Choose option 4 from Main menu then choose option 2.
Compile APPS schema.
Choose option 3 from Main menu then choose option 1.
Make sure the number of invalids objects are 14.
New invalid objects
PER_STC_BUS (Package Body)
DEBUG_CARTX (Synonym)

31. Apply Oracle Receivables patch(5 minutes)
� Enable maintenance mode using adadmin utility.
Perform following under dbnode (oadevdb03)
Unzip p5753621_11i_SOLARIS.zip
cd 5753621
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
Unzip p5753621_11i_SOLARIS.zip
cd 5753621
use adpatch utility to apply patch
Disable maintenance mode using adadmin utility.
/u26/oracle/oraTAG/soft/patchset/5753621
/u26/oracle/oraTAG/soft/patchset/5753621/u5753621.drv
Post Upgrade Step (Conditional)

� Please test the adstpall.sh in oadevdb03. (10 minutes)
� Check if the entire concurrent managers are shutting down.
� If not please run the cmclean.sql script (134007.1)
� Make sure all the FNDLIBR are stopped.
� Login to sqlplus as apps user
o @cmclean.sql
o Commit;
� Test the adstpall.sh again
� Check the number of invalids after the 10g upgrade.

HR EEO Patch
� Apply HR EEO patch(30 minutes)
� Enable maintenance mode using adadmin utility.
� Gather invalid object again
� Perform following under dbnode (oadevdb03)
o Unzip p5511810_11i_SOLARIS.zip
o cd 5511810
o use adpatch utility to apply patch
� Perform following under appsnode (oadevapp03)
o Unzip p5511810_11i_SOLARIS.zip
o cd 5511810
o use adpatch utility to apply patch
� Perform following under dbnode (oadevdb03)
o Unzip p5511810_11i_ja.zip
o cd 5511810_JA
o use adpatch utility to apply patch
� Perform following under appsnode (oadevapp03)
o Unzip p5511810_11i_ja.zip
o cd 5511810_JA
o use adpatch utility to apply patch
� Disable maintenance mode using adadmin utility.
� Gather invalid object again
� Take Cold Backup of system.


Appendix

Initialization parameters set for 10G database (TAG) on 11i

Before 10g upgrade

db_name = TAG
control_files = /u26/oracle/oraTAG/TAGdata/cntrl01.dbf,/u26/
oracle/oraTAG/TAGdata/cntrl02.dbf,/u26/oracle/oraTAG/TAGdata/cntrl03.dbf

db_block_size = 8192
compatible = 9.2.0
_system_trig_enabled = true

O7_DICTIONARY_ACCESSIBILITY = TRUE

#row_locking = always

nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary # Required 11i setting
nls_comp = binary # Required 11i setting
nls_length_semantics = BYTE # Required 11i setting


#audit_trail = true # if you want auditing

#max_enabled_roles = 100 # Some modules depend on this feature.
user_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/udump
background_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/bdump
core_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/cdump
max_dump_file_size = 20480 # trace file size
timed_statistics = true

processes = 1200 # Max. no. of users x 2
sessions = 400 # 2 X processes
db_files = 500 # Max. no. of database files
dml_locks = 10000
cursor_sharing = EXACT # Required 11i settting
open_cursors = 600 # Consumes process memory, unless using MTS.
session_cached_cursors = 200
#enqueue_resources = 32000 # Max. no of concurrent database locks
.
db_cache_size = 163577856
db_block_checking = FALSE
db_block_checksum = TRUE

log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE


undo_management=AUTO

undo_retention=1800

#undo_suppress_errors=FALSE

undo_tablespace=APPS_UNDOTS1

workarea_size_policy=AUTO

pga_aggregate_target=1G

olap_page_pool_size = 4194304
shared_pool_size = 300000000
shared_pool_reserved_size = 30000000
_shared_pool_reserved_min_alloc = 4100

java_pool_size = 52428800

#utl_file_dir = /usr/tmp,/u26/oracle/oraTAG/TAGcomn/temp,/u26/oracle/oraTAG/s
it1db/9.2.0/appsutil/outbound/SIT1_oadevdb03,/usr/tmp
utl_file_dir= /home/bicepsftp/files,/u26/oracle/oraTAG/sit1comn/temp,/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil/outbound/SIT1_oadevdb03,/usr/tmp
aq_tm_processes = 1
job_queue_processes = 2

# log_archive_start = true # if you want automatic archiving

parallel_max_servers = 8
parallel_min_servers = 0

optimizer_features_enable = 9.2.0

db_file_multiblock_read_count = 8


#optimizer_max_permutations = 2000 # Required for 11i setting

_like_with_bind_as_equality = TRUE

_sort_elimination_cost_ratio=5

_fast_full_scan_enabled = FALSE

query_rewrite_enabled = true

_sqlexec_progression_cost=2147483647

_index_join_enabled = FALSE

_b_tree_bitmap_plans = FALSE

streams_pool_size=50331648
large_pool_size=8388608
session_max_open_files=20

After 10g Upgrade

db_name = TAG
control_files= /u26/oracle/oraTAG/TAGdata/cntrl01.dbf,/u26/oracle/oraTAG/TAGdata/cntrl02.dbf,/u26/oracle/oraTAG/TAGdata/cntrl03.dbf

db_block_size = 8192

compatible = 10.2.0

_system_trig_enabled = true

O7_DICTIONARY_ACCESSIBILITY = TRUE

nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary # Required 11i setting
nls_comp = binary # Required 11i setting
nls_length_semantics = BYTE # Required 11i setting

user_dump_dest = /u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/udump
background_dump_dest=/u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/bdump
core_dump_dest = /u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/cdump

max_dump_file_size = 20480 # trace file size

timed_statistics = true

_trace_files_public = TRUE

processes = 1200 # Max. no. of users x 2
sessions = 400 # 2 X processes
db_files = 500 # Max. no. of database files
dml_locks = 10000
cursor_sharing = EXACT # Required 11i settting
open_cursors = 600 # Consumes process memory, unless using MTS.
session_cached_cursors = 200

sga_target= 1G
db_block_checking = FALSE
db_block_checksum = TRUE

log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE

undo_management=AUTO

undo_tablespace=APPS_UNDOTS1

workarea_size_policy=AUTO

pga_aggregate_target=1G

olap_page_pool_size = 4194304

shared_pool_size = 300000000
shared_pool_reserved_size = 30000000

utl_file_dir = /home/bicepsftp/files,/u26/oracle/oraTAG/TAGcomn/temp,/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil/outbound/TAG_oadevdb03,/usr/tmp

aq_tm_processes = 1
job_queue_processes = 2

parallel_max_servers = 8
parallel_min_servers = 0

db_file_multiblock_read_count = 8

_like_with_bind_as_equality = TRUE

_sort_elimination_cost_ratio=5

_fast_full_scan_enabled = FALSE

_sqlexec_progression_cost=2147483647

_b_tree_bitmap_plans = FALSE

streams_pool_size=50331648
session_max_open_files=20


-----------------------------------------------------------------------------------------------------------------------
issues
PLATFORM=SunOS; export PLATFORM (add in the .profile in DBTier of DB node)
export TNS_ADMIN=/u26/oracle/oraTAG/TAGdb/10.2.0/network/admin/TAG_oadevdb03
unset NLS_LANG (in DB env file)

Happy Learning !



Understanding the SCN(System Change Number) in Oracle

Understanding the SCN

In order to understand how Oracle performs recovery, it’s first necessary to understand Oracle’s SCN in terms of the various places where it can be stored and how it’s used for instance and media recovery.

The SCN is an internal number maintained by the database management system (DBMS) to log changes made to a database. The SCN increases over time as changes are made to the database by Structured Query Language (SQL). By understanding how the SCN is used, you can understand how Oracle recovery works. Oracle9i enables you to examine the current SCN using the following SQL:

SQL> select dbms_flashback.get_system_change_number from dual;

Whenever an application commits a transaction, the log writer process (LGWR) writes records from the redo log buffers in the System Global Area (SGA) to the online redo logs on disk. LGWR also writes the transaction’s SCN to the online redo log file. The success of this atomic write event determines whether your transaction succeeds, and it requires a synchronous (wait-until-completed) write to disk.


Note: The need for a synchronous write upon commit is one of the reasons why the online redo log can become a bottleneck for applications and why you should commit as infrequently as is practical. In general, Oracle writes asynchronously to the database datafiles for performance reasons, but commits require a synchronous write because they must be guaranteed at the time they occur.

SCN and Checkpoints:

A checkpoint occurs when all modified database buffers in the Oracle SGA are written out to datafiles by the database writer (DBWn) process. The checkpoint process (CKPT) updates all datafiles and control files with the SCN at the time of the checkpoint and signals DBWn to write out the blocks. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during recovery. Checkpoints occur automatically as follows:

  • Whenever a redo log switch takes place
  • Whenever the time set by the LOG_CHECKPOINT_TIMEOUT initialization parameter is reached
  • Whenever the amount of redo written reaches the number of bytes associated with the LOG_CHECKPOINT_INTERVAL

Typically, LOG_CHECKPOINT_INTERVAL is chosen so that checkpoints only occur on log switches. Oracle stores the SCN associated with the checkpoint in four places: three of them in the control file and one in the datafile header for each datafile.

The System Checkpoint SCN:

After a checkpoint completes, Oracle stores the system checkpoint SCN in the control file. You can access the checkpoint SCN using the following SQL:

SQL> select checkpoint_change# from v$database;


CHECKPOINT_CHANGE#
--------------------
292767

The Datafile Checkpoint SCN:

After a checkpoint completes, Oracle stores the SCN individually in the control file for each datafile. The following SQL shows the datafile checkpoint SCN for a single datafile in the control file:

SQL> select name,checkpoint_change# from v$datafile where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 292767

The Start SCN:

Oracle stores the checkpoint SCN value in the header of each datafile. This is referred to as the start SCN because it is used at instance startup time to check if recovery is required. The following SQL shows the checkpoint SCN in the datafile header for a single datafile:

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 292767

The Stop SCN:

The stop SCN is held in the control file for each datafile. The following SQL shows the stop SCN for a single datafile when the database is open for normal use:

SQL> select name,last_change# from v$datafile where name like '%users01%';

NAME LAST_CHANGE#
----------------------------------- ------------
/u02/oradata/OMFD1/users01.dbf

During normal database operation, the stop SCN is NULL for all datafiles that are online in read-write mode. SCN Values while the Database Is Up Following a checkpoint while the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match. The stop SCN for each datafile in the control file is NULL. SCN after a Clean Shutdown After a clean database shutdown resulting from a SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL of the database, followed by STARTUP MOUNT, the previous queries on v$database and v$datafile return the following:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293184

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%user%';

NAME CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------------------- -------------------- --------------
/u02/oradata/OMFD1/users01.dbf 293184 293184


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 293184

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.

SCN after an Instance Crash

The previous example showed the behavior of the SCN after a clean shutdown. To demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:
create table x(x number) tablespace users;

insert into x values(100);

If the instance is crashed by using SHUTDOWN ABORT, the previous queries on v$database and v$datafile return the following after the database is started up in mount mode:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293185

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%users01%';

NAME CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------------------- -------------------- --------------
/u02/oradata/OMFD1/users01.dbf 293185


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 293185

In this case, the stop SCN is not set, which is indicated by the NULL value in the LAST_CHANGE# column. This information enables Oracle, at the time of the next startup, to determine that the instance crashed because the checkpoint on shutdown was not performed. If it had been performed, the LAST_CHANGE# and CHECKPOINT_CHANGE# values would match for each datafile as they did during a clean shutdown. If an instance crashes at shutdown, then instance crash recovery is required the next time the instance starts up.

Recovery from an Instance Crash

Upon the next instance startup that takes place after SHUTDOWN ABORT or a DBMS crash, the Oracle DBMS detects that the stop SCN for datafiles is not set in the control file during startup. Oracle then performs crash recovery. During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back. In our example, the following transaction was active but not committed at the time of the SHUTDOWN ABORT, so it needs to be rolled back:

SQL> insert into x values(100);

After instance startup, the X table exists, but remains empty. Instance recovery happens automatically at database startup without database administrator (DBA) intervention. It may take a while because of the need to apply large amounts of outstanding redo changes to data blocks for transactions that completed and those that didn’t complete and require roll back.

Recovery from a Media Failure

Up until this point, the checkpoint start SCN in the datafile header has always matched the datafile checkpoint SCN number held in the control file. This is reasonable because during a checkpoint, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are both updated, along with the system checkpoint SCN. The following SQL shows the start SCN from the datafile header and datafile checkpoint SCN from the control file for the same file:

SQL> select 'controlfile' "SCN location",name,checkpoint_change# from v$datafile where name like '%users01%'
union
select 'file header',name,checkpoint_change# from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
-------------- ----------------------------------- --------------------
controlfile /u02/oradata/OMFD1/users01.dbf 293188
file header /u02/oradata/OMFD1/users01.dbf 293188

Unlike the v$datafile view, there is no stop SCN column in the v$datafile_header view because v$datafile_header is not used at instance startup time to indicate that an instance crash occurred. However, the v$datafile_header does provide the Oracle DBMS with the information it requires to perform media recovery. At instance startup, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are checked for equality. If they don’t match, it is a signal that media recovery is
required.

For example, media recovery is required if a media failure has occurred and the original datafile has been replaced with a backup copy. In this case, the start SCN in the backup copy is less than the checkpoint SCN value in the control file, and Oracle requests archived redo logs—generated at the time of previous log switches—in order to reapply the changes required to bring the datafile up to the current point in time.

In order to recover the database from a media failure, you must run the database in ARCHIVELOG mode to ensure that all database changes from the online redo logs are stored permanently in archived redo log files. In order to enable ARCHIVELOG mode, you must run the command ALTERDATABASE ARCHIVELOG when the database is in a mounted state.

You can identify files that need recovery after you have replaced a datafile with an older version by starting the instance in mount mode and running the following SQL:

SQL> select file#,change# from v$recover_file;

FILE# CHANGE#
---------- ----------
4 313401

In this example, file 4 is the datafile in the USERS tablespace. By reexecuting the previous SQL to display the datafile checkpoint SCN in the control file and the start SCN in the datafile header, you can see that the start SCN is older due to the restore of the backup datafile that has taken place:

SQL> select 'controlfile' "SCN location",name,checkpoint_change#
from v$datafile where name like '%users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
-------------- ----------------------------------- --------------------
controlfile /u02/oradata/OMFD1/users01.dbf 313551
file header /u02/oradata/OMFD1/users01.dbf 313401
If you were to attempt to open the database, you would receive errors like the following:
ORA-01113: file 4 needs media recovery
ORA-01110: datafile 4: '/u02/oradata/OMFD1/users01.dbf'
You can recover the database by issuing RECOVER DATABASE from SQL*Plus while the database is in a mounted state. If the changes needed to recover the database to the point in time before the crash are in an archived redo log, then you will be prompted to accept the suggested name:
ORA-00279: change 313401 generated at 11/10/2001 18:50:23 needed for thread
ORA-00289: suggestion : /u02/oradata/OMFD1/arch/T0001S0000000072.ARC
ORA-00280: change 313401 for thread 1 is in sequence #72

Specify log: {=suggested | filename | AUTO | CANCEL}

If you respond to the prompt using AUTO, Oracle applies any archived redo logs it needs, followed by any necessary changes in the online redo logs, to bring the database right up to the last committed transaction before the media failure that caused the requirement for the restore.

So far, we’ve considered recovery scenarios where the goal is to recover the database to the most recent transaction. This is known as complete recovery. The RECOVER DATABASE command has several other options that enable you to recover from a backup to a point in time before the most recent transaction by rolling forward and then stopping the application of the redo log changes at a specified point. This is known as incomplete recovery. You can specify a time or an SCN as the recovery point. For example,

recover database until time '2001-11-10:18:52:00';
recover database until change 313459;

Before you perform incomplete recovery, it’s recommended that you restore a complete database backup first. After incomplete recovery, you must open the mounted database with ALTER DATABASE OPEN RESETLOGS. This creates a new incarnation of the database and clears the contents of the existing redo logs to make sure they can’t be applied.

Recovery from a Media Failure Using a Backup Control File

In the previous example, we had access to a current control file at the time of the media failure. This means that none of the start SCN values in the datafile headers exceeded the system checkpoint SCN number in the control file. To recap, the system checkpoint number is given by the following:

SQL> select checkpoint_change# from v$database;

You might be wondering why Oracle needs to maintain the last system checkpoint value in the control file as well as checkpoint SCNs in the control file for each datafile (as used in the previous example). There are two reasons for this. The first is that you might have read-only tablespaces in your database. In this case, the database checkpoint SCN increases, and the checkpoint SCN for the datafiles in the read-only tablespace remains frozen in the control file.

The following SQL report output shows a database with a read-write tablespace (USERS) and read-only tablespace (TEST). The start SCN in the file header and the checkpoint SCN in the control file for TEST are less than the system checkpoint value. Once a tablespace is read only, checkpoints have no effect on the files in it. The other read-write tablespace has checkpoint values that match the system checkpoint:

SCN location NAME CHECKPOINT_CHANGE#
-------------------- ---------------------------------- ----------------
controlfile SYSTEM checkpoint 355390
file header /u02/oradata/OD2/users01.dbf 355390
file in controlfile /u02/oradata/OD2/users01.dbf 355390
file header /u02/oradata/OD2/test01.dbf 355383
file in controlfile /u02/oradata/OD2/test01.dbf 355383

The second reason for the maintenance of multiple checkpoint SCNs in the control file is that you might not have a current control file available at recovery time. In this case, you need to restore an earlier control file before you can perform a recovery. The system checkpoint in the control file may indicate an earlier change than the start SCN in the datafile headers.

The following SQL shows an example where the system checkpoint SCN and datafile checkpoint SCN indicate an earlier change than the start SCN in the datafile header:

SQL> select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where name like 'users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
------------------- ------------------------------ ------------------
controlfile SYSTEM checkpoint 333765
file header /u02/oradata/OD2/users01.dbf 355253
file in controlfile /u02/oradata/OD2/users01.dbf 333765

If try you to recover a database in the usual way in this situation, Oracle detects that the control file is older than some of the datafiles, as indicated by the checkpoint SCN values in the datafile headers, and reports the following message:

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

If you want to proceed with recovery in this situation, you need to indicate to Oracle that a noncurrent control file—possibly containing mismatches in the SCN values identified by the previous error messages—is about to be specified for recovery by using the following command:
recover database using BACKUP CONTROLFILE;


Happy Learning !

Monday, April 6, 2009

Automatic Workload Repository


Learn to use the new feature that collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information

When you have a database performance problem, what is the first thing you do to address it? One common approach is to see if a pattern exists: Answering questions such as "Is the same problem recurrent?", "Does it occur during a specific time period?", and "Is there a link between two problems?" will almost always lead to a better diagnosis.

As a DBA you probably have invested in a third-party or homegrown tool to collect elaborate statistics during database operation and derive performance metrics from them. In a crisis, you access those metrics for comparisons to the present. Replaying these past events can shed light on current problems, so continuously capturing relevant statistics becomes important for performance analysis.

For some time, Oracle's solution in this area has been its built-in tool, Statspack. While it can prove invaluable in certain cases, it often lacks the robustness required by performance troubleshooting exercises. Oracle Database 10g offers a significant improvement: the Automatic Workload Repository (AWR). The AWR installs along with the database and captures not only statistics, but the derived metrics as well.

A Quick Test Drive

AWR capability is best explained quickly by the report it produces from collected statistics and metrics, by running the script awrrpt.sql in the $ORACLE_HOME/rdbms/admin directory. This script, in its look and feel, resembles Statspack; it shows all the AWR snapshots available and asks for two specific ones as interval boundaries. It produces two types of output: text format, similar to that of the Statspack report but from the AWR repository, and the default HTML format, complete with hyperlinks to sections and subsections, providing quite a user-friendly report. Run the script and take a look at the report now to get an idea about capabilities of the AWR.

Implementation

Now let's explore how AWR is designed and structured. Basically, AWR is an Oracle built-in tool that collects performance related statistics and derives performance metrics from them to track a potential problem. Unlike Statspack, snapshots are collected automatically every hour by a new background process called MMON and its slave processes. To save space, the collected data is automatically purged after 7 days. Both the snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:

select snap_interval, retention
from dba_hist_wr_control;

SNAP_INTERVAL RETENTION
------------------- -------------------
+00000 01:00:00.0 +00007 00:00:00.0

This SQL shows that the snapshots are taken every hour and the collections are retained 7 seven days. To change the settings--say, for snapshot intervals of 20 minutes and a retention period of two days--you would issue the following. The parameters are specified in minutes.

begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2*24*60
);
end;

AWR uses several tables to store the collected statistics, all stored under the SYS schema in the new special tablespace named SYSAUX, and named in the format WRM$_* and WRH$_*. The former type stores metadata information such as the database being examined and the snapshots taken, and the latter type holds the actual collected statistics. (As you might have guessed, H stands for "historical" and M stands for "metadata.") There are several views with the prefix DBA_HIST_ built upon these tables, which can be used to write your own performance diagnosis tool. The names of the views directly relate to the table; for example, the view DBA_HIST_SYSMETRIC_SUMMARY is built upon the table WRH$_SYSMETRIC_SUMMARY.

The AWR history tables capture a lot more information than Statspack, including tablespace usage, filesystem usage, even operating system statistics. A complete list of these tables can be seen from the data dictionary through:

select view_name from user_views where view_name like 'DBA\_HIST\_%' escape '\';

The view DBA_HIST_METRIC_NAME defines the important metrics the AWR collects, the groups to which they belong, and the unit in which they are collected. For example, here is one record (in vertical format):

DBID : 4133493568
GROUP_ID : 2
GROUP_NAME : System Metrics Long Duration
METRIC_ID : 2075
METRIC_NAME : CPU Usage Per Sec
METRIC_UNIT : CentiSeconds Per Second

It shows that a metric "CPU Usage Per Sec" is measured in units of "CentiSeconds Per Second" and belongs to a metric group "System Metrics Long Duration." This record can be joined with other tables such as DBA_HIST_SYSMETRIC_SUMMARY to get the activity, as in:

select begin_time, intsize, num_interval, minval, maxval, average, standard_deviation sd
from dba_hist_sysmetric_summary where metric_id = 2075;

BEGIN INTSIZE NUM_INTERVAL MINVAL MAXVAL AVERAGE SD
----- ---------- ------------ ------- ------- -------- ----------
11:39 179916 30 0 33 3 9.81553548
11:09 180023 30 21 35 28 5.91543912

... and so on ...

Here we see how the CPU time was consumed in centi-seconds. The standard deviation adds to our analysis by helping ascertain whether the average figure reflects the actual workload. In the first records, the average is 3 centi-seconds in CPU per second elapsed, but the standard deviation is 9.81, meaning the average of 3 is not reflective of the workload. In the second example, the value 28, with a standard deviation of 5.9, is more representative. This type of information trends help understanding the effects of several environmental parameters on performance metrics.

Using the Statistics

So far we have seen what AWR collects; now let's see what it does with the data.

Most performance problems do not exist in isolation, but rather leave tell-tale signs that will lead to the eventual root cause of the problem. Let's use a typical tuning exercise: You notice that the system is slow and decide to look into the waits. Your examination reveals that the "buffer busy wait" is very high. What could be the problem? There are several possibilities: there could be a monotonically increasing index, a table so packed that a single block is asked to be loaded to memory very quickly, or some other factors. In any case, first you want identify the segment in question. If it's an index segment, you could decide to rebuild it; change it to a reverse key index; or convert it to a hash-partitioned index introduced in Oracle Database 10g. If it's a table, you could consider changing storage parameters to make it less dense or move it over to a tablespace with automatic segment space management.

Your plan of attack is generally methodical and usually based your knowledge of various events and your experience in dealing with them. Now imagine if the same thing were done by an engine - an engine that captures metrics and deduces possible plans based on pre-determined logic. Wouldn't your job be easier?

That engine, now available in Oracle Database 10g, is known as Automatic Database Diagnostic Monitor (ADDM). To arrive at a decision, ADDM uses the data collected by AWR. In the above discussion, ADDM can see that the buffer busy waits are occurring, pull the appropriate data to see the segments on which it occurs, evaluate its nature and composition, and finally offer solutions to the DBA. After each snapshot collection by AWR, the ADDM is invoked to examine the metrics and generate recommendations. So, in effect you have a full-time robotic DBA analyzing the data and generating recommendations proactively, freeing you to attend to more strategic issues.

To see the ADDM recommendations and the AWR repository data, use the new Enterprise Manager 10g console on the page named DB Home. To see the AWR reports, you can navigate to them from Administration, then Workload Repository, and then Snapshots. We'll examine ADDM in greater detail in a future installment.

You can also specify alerts to be generated based on certain conditions. These alerts, known as Server Generated Alerts, are pushed to an Advanced Queue, from where they can be consumed by any client listening to it. One such client is Enterprise Manager 10g, where the alerts are displayed prominently.

Time Model

When you have a performance problem, what comes to mind first to reduce the response time? Obviously, you want to eliminate (or reduce) the root cause of the factor that adds to the time. How do you know where the time was spent--not waiting, but actually doing the work?

Oracle Database 10g introduces time models for identifying the time spent in various places. The overall system time spent is recorded in the view V$SYS_TIME_MODEL. Here is the query and its output.

STAT_NAME VALUE
------------------------------------- --------------
DB time 58211645
DB CPU 54500000
background cpu time 254490000
sequence load elapsed time 0
parse time elapsed 1867816
hard parse elapsed time 1758922
sql execute elapsed time 57632352
connection management call elapsed time 288819
failed parse elapsed time 50794
hard parse (sharing criteria) elapsed time 220345
hard parse (bind mismatch) elapsed time 5040
PL/SQL execution elapsed time 197792
inbound PL/SQL rpc elapsed time 0
PL/SQL compilation elapsed time 593992
Java execution elapsed time 0
bind/define call elapsed time 0

Note the statistic named DB Time, which represents the time spent in the database since the instance startup. Run the sample workload and select the statistic value from the view again. The difference should represent the time spent in the database for that workload. After another round of tuning, perform the same analysis and that difference will show the change in DB Time after the tuning, which can be compared to first change to examine the effect of the tuning exercise on the database time.

In addition to the database time, the V$SYS_TIME_MODEL view shows a whole lot of other statistics, such as time spent in different types of parsing and even PL/SQL compilation.

This view shows the overall system times as well; however, you may be interested in a more granular view: the session level times. The timing stats are captured at the session level as well, as shown in the view V$SESS_TIME_MODEL, where all the stats of the current connected sessions, both active and inactive, are visible. The additional column SID specifies the SID of the sessions for which the stats are shown.

In previous releases, this type of analysis was impossible to get and the user was forced to guess or derive from a variety of sources. In Oracle Database 10g, getting this information is a snap.

Active Session History

The view V$SESSION in Oracle Database 10g has been improved; the most valuable improvement of them all is the inclusion of wait events and their duration, eliminating the need to see the view V$SESSION_WAIT. However, since this view merely reflects the values in real time, some of the important information is lost when it is viewed later. For instance, if you select from this view to check if any session is waiting for any non-idle event, and if so, the event in question, you may not find anything because the wait must have been over by the time you select it.

Enter the new feature Active Session History (ASH), which, like AWR, stores the session performance statistics in a buffer for analysis later. However, unlike AWR, the storage is not persistent in a table but in memory, and is shown in the view V$ACTIVE_SESSION_HISTORY. The data is polled every second and only the active sessions are polled. As time progresses, the old entries are removed to accommodate new ones in a circular buffer and shown in the view. To find out how many sessions waited for some event, you would use

select session_id||','||session_serial# SID, n.name, wait_time, time_waited
from v$active_session_history a, v$event_name n
where n.event# = a.event#

This command tells you the name of the event and how much time was spent in waiting. If you want to drill down to a specific wait event, additional columns of ASH help you with that as well. For instance, if one of the events the sessions waited on is buffer busy wait, proper diagnosis must identify the segments on which the wait event occurred. You get that from the ASH view column CURRENT_OBJ#, which can then be joined with DBA_OBJECTS to get the segments in question.

ASH also records parallel query server sessions, useful to diagnose the parallel query wait events. If the record is for a parallel query slave process, the SID of the coordinator server session is identified by QC_SESSION_ID column. The column SQL_ID records the ID of the SQL statement that produced the wait event, which can be joined with the V$SQL view to get the offending SQL statement. To facilitate the identification of the clients in a shared user environment like a web application, the CLIENT_ID column is also shown, which can be set by DBMS_SESSION.SET_IDENTIFIER.

Since ASH information is so valuable, wouldn't it be nice if it were stored in a persistent manner similar to AWR? Fortunately, it is; the information is flushed to the disk by the MMON slave to the AWR table, visible through the view DBA_HIST_ACTIVE_SESS_HISTORY.

Manual Collection

Snapshots are collected automatically by default, but you can also collect them on demand. All AWR functionality has been implemented in the package DBMS_WORKLOAD_REPOSITORY. To take a snapshot, simply issue:

execute dbms_workload_repository.create_snapshot

It immediately takes a snapshot, recorded in the table WRM$_SNAPSHOT. The metrics collected are for the TYPICAL level. If you want to collect more detailed statistics, you can set the parameter FLUSH_LEVEL to ALL in the above procedure. The stats are deleted automatically but can also be deleted manually by calling the procedure drop_snapshot_range().

Baseline

A typical performance tuning exercise starts with a capturing a baseline set of metrics, making changes, and then taking another baseline set. These two sets can be compared to examine the effect of the changes made. In AWR, the same kind of analogy can be implemented for existing snapshots taken. Suppose a particularly resource intensive process named apply_interest ran between 1:00 and 3:00PM, corresponding to snapshot IDs 56 through 59. We could define a baseline named apply_interest_1 for these snapshots:

exec dbms_workload_repository.create_baseline (56,59,'apply_interest_1')

This action marks the snapshots 56 through 59 as part of a baseline named above. Checking for existing baselines:

select * from dba_hist_baseline;

DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- -------------------- ------------- -----------
4133493568 1 apply_interest_1 56 59

After a few tuning steps, we can create another baseline--called, say apply_interest_2--and compare the metrics for only those snapshots related to these two baselines. Isolating snapshots to only a few sets like this helps in studying the effects of tuning on performance metrics. You can drop the baselines after the analysis using the procedure drop_baseline(); the snapshots will be preserved. Also, when the purge routine kicks in to delete the old snapshots, the ones related to baselines are not purged, allowing for further analysis.

Conclusion

This installment was intended to be merely an introduction to the very rudimentary aspects of the AWR. For a more complete coverage, see Oracle Database 10g documentation. Furthermore, an excellent treatise on AWR and ADDM can be found in the technical whitepaper The Self-Managing Database: Automatic Performance Diagnosis. In Week 18, you will learn more about ADDM and using it to solve real-life problems.

Happy Learning !