Tuesday, 25 June 2013

Oracle database: Analyze AWR Report

script is here $ORACLE_HOME\RDBMS\ADMIN\

execute like below

SQL*Plus: Release - Production on Sun Jul 10 14:37:04 2011

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

SQL> CONN sys@stlbas105 as sysdba
Enter password:
SQL> @G:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\awrrpt.sql

Current Instance

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2515622958 STLBAS 1 stlbas

Specify the Report Type
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified: text

Instances in this Workload Repository schema

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2515622958 1 STLBAS stlbas TESTSERVER

Using 2515622958 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing 
specifying a number lists all completed snapshots.

Enter value for num_days:

This document shows you some basic checks to be done from AWR to identify the problem.

Older Database versions like 8i,9i have Statspack report only.

From 10g onwards, AWR is available along with Statspack.
Automatic Workload Repository (AWR) : The Automatic Workload Repository (AWR) provides information to different manageabilities components. AWR consists of two components: in-memory statistics accessible through V$ dynamic views, and AWR snapshots saved in the database that represent the persistent and historical portion.

AWR snapshots can be generated at will using the following syntax:

EXECUTE dbms_workload_repository.create_snapshot();

By default in 10g, the AWR snapshots are generated automatically on hourly basis.

If you are facing any performance problem in the database and you have license for AWR,
then AWR reports can be generated for the problem period.

If there is no proper license for AWR available then statspack report can be generated.

The AWR/Statspack report should be taken for the interval not more than 60 minutes during problem.

Please dont take AWR / Statspack report for duration of like five or six hours as that would not be reliable.

The AWR report can be taken in both html/text format.

1) The first thing to be checked in AWR report is the following:-
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 112 11-Jun-09 00:00:57 191 6.7
End Snap: 113 11-Jun-09 01:00:11 173 7.4
Elapsed: 59.23 (mins)
DB Time: 710.73 (mins)
Check the "DB Time" metric. If it is much higher than the elapsed time, then it indicates that the sessions are waiting for something.

Here in this example, the Elapsed Time is around 60 minutes while the DB Time is around 700 minutes. This means that 700 minutes of time is spent by the sessions on waiting.

2) Next thing to be looked is the following:-
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.67 In-memory Sort %: 100.00
Library Hit %: 98.60 Soft Parse %: 99.69
Execute to Parse %: 5.26 Latch Hit %: 99.31
Parse CPU to Parse Elapsd %: 12.78 %Non-Parse CPU: 99.10
As per the thumb rule, Instance Efficieny Percentages should be ideally above 90%.
3) Then comes the Shared Pool Statistics.
Shared Pool Statistics
Begin End
Memory Usage %: 85.49 80.93
% SQL with executions>1: 42.46 82.96
% Memory for SQL w/exec>1: 47.77 81.03
The memory usage statistics of shared pool is shown.
Idealy this should be lesser. If it is very high like beyond 90, this shows the contention
in the shared pool.
4) Next thing to be looked after is the Top 5 Timed Events table.

This shows the most significant waits contributing to the DB Time.
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read 4,076,086 28,532 7 66.9 User I/O
CPU time 11,214 26.3
Backup: sbtbackup 4 4,398 1,099,452 10.3 Administrative
log file sync 37,365 2,421 65 5.7 Commit
log file parallel write 37,928 1,371 36 3.2 System I/O
Here, the significant wait is the db file sequential read which contributes to 67% of DB Time.

5) Then , SQL Statistics can be checked.
SQL Statistics
SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads
SQL Statistics section would have commonly the above four sections.

Each section shows the list of SQLs based on the order of the respective metric.
For example, SQL ordered by Elapsed Time section shows the list of SQLs in the order
of the Elapsed Time. High resource consuming SQLs can be spotted out and meant for

Note: All the above four sections of SQL Statistics show the list of SQLs in descending order.
i.e, For ex: Highest elapsed time is shown as first.

6) Then comes the IO Stats section.

This shows the IO Statistics for each tablespaces in the database.
As the thumb rule, the Av Rd(ms) [Average Reads in milliseconds] should not cross beyond 30, add myself(not greater that 30)
which is considered to be IO bottleneck.
Tablespace IO Stats
ordered by IOs (Reads + Writes) desc
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
TEMP 3,316,082 933 4.91 1.00 28,840 8 0 0.00
DAT1 520,120 146 16.06 1.21 185,846 52 902 13.00
DAT3 93,411 26 42.82 2.98 13,442 4 16 23.13
DAT2 98,171 28 91.97 7.97 5,333 2 325 34.89
In the above example, the Av Rd(ms) is high in all tablespaces indicating the IO contention.

7) Then , Advisory Statistics can be checked.

This section shows the following:-
Buffer Pool Advisory
PGA Aggr Summary
PGA Aggr Target Stats
PGA Aggr Target Histogram
PGA Memory Advisory
Shared Pool Advisory
SGA Target Advisory
Streams Pool Advisory
Java Pool Advisory
It is very commonly used to check the advisories for the most important SGA structures like shared pool, buffer cache etc and PGA.

8) Then finally, init.ora Parameters is shown which shows the list of parameters set at instance level.
init.ora Parameters
All the above said sections except the DB Time can be checked from Statspack report also.

The statspack snapshots are not generated automatically as in AWR.
It has to be generated during the problem period as follows:-

Take 2 snapshots between 60 minutes interval during the problem and generate the statspack report
exec statspack.snap
wait for 60 minutes
exec statspack.snap
Please run $ORACLE_HOME/rdbms/admin/spreport.sql
and specify BEGIN and END ID's of the snapshots taken during the problem.

The above said sections are the most common checks can be performed from user level.
Further intensive checking can be done through Oracle Support.

Monday, 24 June 2013

Oracle database: database link ORA-00600: internal error code

To create database link in another schema you must execute “create database link” logged in that schema or you can use workaround noted in this post:

How to Create a Database Link in Another User’s Schema

Neil Johnson wrote nice post on that subject and I’m using his method for creating database links in another user’s schema. So check it out - link is above.

Recently I had situation when I didn't know password of the user on the local and remote database. But I had to create database link to compile some views and procedures.

In that case my method was:
(execute this steps as user with admin privileges)

-- Grant privilege to create database link to user
grant create database link to locuser;

-- Create temporary procedure to execute create database
link as another user
CREATE OR REPLACE PROCEDURE locuser.create_dblink AS
''E977335D83B8468E'' USING ''testdb''';
END create_dblink;

-- Execute procedure and create database link in another schema
exec locuser.create_dblink;

-- Drop temporary procedure
drop procedure locuser.create_dblink;

-- Revoke privilege to create database link
revoke create database link from locuser;

But when I tried to test database link I’ve received this nasty error:

 ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], [], [], [], [], [] 

There is MOS note related to this error: [ID 1309705.1]

 The error suggests that when the database link was created, the was established using the syntax IDENTIFIED BY VALUES as compared to the document syntax of IDENTIFIED BY

Use of IDENTIFIED BY VALUES is reserved for internal Oracle use only.

While earlier Oracle releases allowed the use of IDENTIFIED BY VALUES, this is not documented as being valid syntax.

From Oracle release 10gR2, database links must be created using the documented syntax.

Solution: Recreate the database link using valid syntax.

So Oracle support says that I cannot use syntax IDENTIFIED BY VALUES while creating database link.

Luckily I’ve found workaround :-)

Environment: Oracle EE on OEL 5.8

To reproduce error I’ve pulled password of another user's schema from "sys.user$" internal table logged as sys user.
Now I will try to create a database link using this password. 
-- Create database link

Database link created.

-- Test database link
SQL> select * from dual@TEST_DB;
select * from dual@TEST_DB
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err],
[18446744073709551603], [], [], [], [], [], [], [], [], [], []

-- Drop
SQL> drop database link TEST_DB;

Database link dropped.

To workaround this problem you could extract user password using “dbms_metadata.get_ddl” package to generate DDL for database link
-- Use this query to generate DDL
select dbms_metadata.get_ddl('DB_LINK',db_link,owner)||';'
from dba_db_links;

-- Create database link

Database link created.

-- Test database link
SQL> select * from dual@TEST_DB;


-- Drop
SQL> drop database link TEST_DB;

Database link dropped.

In this case, with differently hashed password, using IDENTIFIED BY VALUES is valid syntax.

Although you can create database link using this syntax have in mind that this is not documented as being valid syntax. So use valid and documented syntax when you can. 

Sunday, 23 June 2013

Oracle database: Generate AWR multiple reports

Use the below script and just modify either interval or snap_id range.  

set serveroutput on
spool master_awr_control.sql
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat
, to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s
, dba_hist_database_instance di
,gv$instance i
,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
--and s.begin_interval_time > trunc(sysdate -7) -- last last 7 days  
and  s.snap_id between 46291 and 46372 
order by di.db_name, i.instance_name, s.snap_id;
for c1 in c
if c1.begin_snap_id > 0 then
dbms_output.put_line('spool '||c1.inst_name||'_'
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||
c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
spool off;
set heading off
set pages 50000
set linesize 1500
set trimspool on
set trimout on
set term off
set verify off;
set feedback off;

Monday, 17 June 2013

Oracle dba / apps interview questions

DBA and Apps DBA interview questions

    I found these questions challenging as these questions will surely need some time  along with practical experience on the tasks and concepts of Oracle Apps,RAC.

1)Explain Database migration steps in details?(DBA interview question)
Ans: Database migration or Data migration involves a team effort of DBAs,Developers,Data Modelors/Database Designers and Applications Administrator if the other applications servers like JBOSS/tomcat is involved.When we are working in team the best way to complete this task is to prepare a plan of tasks along with time and table well in advance on an excel sheet and intimate each of the team members to communicate effectively.
From DBA's point below is an example of one such task,Check the below link for the details:

2)What are the 3 ips used in RAC configuration,explain them in brief?(DBA interview question)
Ans: 3 IPs used in RAC configuration are public,private and virtual IP.
1.Public IP address:Public IP adress is the normal IP address typically used to manage storage,system and database.
2.private IP address:Private IP address is used only for internal clustering processing (i.e Cache Fusion or as interconnect).
3.Virtual IP address:VIP is used by database applications to enable failover when one cluster node fails.

3)What is cache fusion?(DBA interview question)
Ans: Oracle RAC uses Cache Fusion to synchronize the data stored in the buffer cache of each database instance. Cache Fusion moves current data blocks (which reside in memory) between database instances, rather than having one database instance write the data blocks to disk and requiring another database instance to reread the data blocks from disk. When a data block located in the buffer cache of one instance is required by another instance, Cache Fusion transfers the data block directly between the instances using the interconnect, enabling the Oracle RAC database to access and modify data as if the data resided in a single buffer cache.

4)What is voting Disk,what is the major use of it?(DBA interview question)
Ans:Voting disk Manages cluster membership and arbitrates cluster ownership
between the nodes in case of network failures
.The voting disk is a file that
resides on shared storage.

5)What is OCR,what is the major use of it?(DBA interview question)
Ans:Oracle Cluster Registry (OCR)– Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR contains information such as which database instances run on which nodes and which services run on which databases. The OCR also stores information about processes that Oracle Clusterware controls. The OCR resides on shared storage that is accessible by all the nodes in your cluster.

6)In RAC 11g as we all know ASM can be used for storing OCR and voting disk,if that is the case how the clusterware start?(DBA interview question)
Ans: The clusterware starts and works properly because of the new feature added in Oracle11g release2 for ASM i.e ACFS(ASM cluster file system).ACFS is available in 11gR2 for LINUX and few unix platforms and Windows operating system
also have a similar solution available.

7)How you change apps password?Explain the syntax of FNDCPASS?(Apps DBA interview question)
Ans: FNDCPASS utility is used to change the password of apps/applsys and all other application products users like gl,ar.FNDCPASS utilliy is supplied with various parameters and options,details are given below along with one task for complete understanding.

Note: Backup FND_ORACLE_USERID and FND_USER tables before using FNDCPASS utility for safety purpose.

Note 1: Passwords for APPLSYS and the APPS schemas -- including the MRC schema -- must be the same.
If you change the password for one, FNDCPASS automatically changes the others

Note 2:After changing the password as the golden rule for apps dba check the log file if it gives
error than password is unchaged.Error details in log file can be as give below:

APP-FND-01496 Results From FNDCPASS Chaning The APPLSYS password
APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password

Note 3:When changing 'apps' password it is important to run autoconfig to propagate the change across the application or else we will need change the 'apps' password in following files manually which can be a tedious task.
1. $IAS_ORACLE_HOME\Apache\modplsql\cfg\wdbsvr.app file
2. $FND_TOP/resource/wfmail.cfg
3. $COMMON_TOP/admin/scripts/adcmctl.ch
4. $OA_HTML/bin/appsweb.cfg
5. $AD_TOP/admin/template/CGIcmd.dat

Q 8) What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?(Apps DBA inteview question)
Ans: If we lost the 'apps' user password we can see this password in wdbsvr.app file.
The file wdbsvr.app file is located under $IAS_ORACLE_HOME/Apache/modplsql/cfg

Based on file location I am sure you can say this is related to Apache, & looking into modplsql/cfg , I am sure you will say its related to mod_pls (mod plsql component of Apache/Oracle 11i WebServer) configuration file. This file is used by mod_plsql component of Apache to connect to database. So when you type url http://hostname:port/pls/SID , whenever Apache(11i Web Server) finds that request is for /pls/ then Apache delegates this request to mod_pls component which in turn pick this file & check if there is any DAD with name SID (in our example its VISION11I) &
Sample entry in wdbsvr.app
connect_string = VISION11I
password = apps
username = APPS
default_page = fnd_web.ping

On typing http://hostname:port/pls/VISION11I ,
it will connect to database using apps schema & will return you page fnd_web.ping ( where fnd_web is package & ping is procedure or vise versa).So story about this file doesn't stop here,this URL which I mentioned about is quite useful in troubleshooting so you can check if database connection is working fine
or not. Another thing you want to check about this file is since it stores APPS password you need to change here whenever you change apps password .

Note:In Apps 11i we have apps password without encrypted form in the below mentioned locations:

• iAS_TOP/Apache/modplsql/cfg/wdbsvr.app
• ORACLE_HOME/reports60/server/CGIcmd.dat

In R12 you won't see these files anymore because Modplsql module got removed and we won't be seeing the files with apps password.

9)Explain the process when we login with 'sysadmin' what happens at the backend?(Apps DBA interview question)
Which table is involved?

Ans: When we login using 'sysadmin' user and password there is a validation takes place,once the user gets validated than E-business suite home page is displayed.

8)How you troubleshoot apps login issue?(Apps DBA interview question)
Ans:Atul's link explain this in best way,please go through it even for troubleshooting at our work environment also.


9)Explain the major troubling which you had done in Oracle Apps?
Ans:We have to explain the recent troubeshooting which we had done recently.Whenever we are doing troubleshooting in Oracle Applications,it is very important to get familiar with the log file locations,depending
on release of Apps.In Apps 11i and R12 the log file locations have changed,so best link I found to remember this locations is given below:


10)What do we have in FND_NODES?when do we run FND_CONC_CLONE.SETUP_CLEAN ?(Apps DBA interview question)

Ans:FND_NODES have the information about node_name,node_mode, support_cp.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table to clear corrupted setup after performing
clonning of Applications.

## before cleanup

SQL>select node_name, node_mode, support_cp,
support_web, support_admin, support_forms

------------------------------ - - - - -
APPSERVER21 N Y N Y <-- bogust="" br="" host="">APPSERVER22 N Y N Y <-- bogust="" br="" host="">APPSERVER23 N Y N Y <-- bogust="" br="" host="">AUTHENTICATION O N N N N

6 rows selected.

## Cleanup
login as 'applmgr'(application filesystem owner),set the application environment and run
FND_CONC_CLONE script to remove bogust host entries.Now login with 'apps' user in Database.

SQL> sho user

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

## After cleanup

SQL> select node_name, node_mode, support_cp,
support_web, support_admin, support_forms

## Run AutoConfig
Note:Context file location
In Apps 11i:$APPL_TOP/admin/$TWO_TASK.xml
In Apps R12:$INST_TOP/appl/admin/TWO_TASK_.xml

## Host01
In Apps 11i:
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/ appspass=

In Apps R12:
$AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/TWO_TASK_.xml

## Host
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/XTPR_host02.xml appspass=

## After AutoConfig runs

select node_name, node_mode, support_cp,
support_web, support_admin, support_forms

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

11)Where do we find the details of all the ports in Oracle Apps?
Ans: Details of all the ports for various severs are present in portlist.ini file.
This file is located inside technology stack in the below mentioned path:


The portlist.ini file have the details of all the ports information of all the servers like HTTP,Java,Oracle Notification
Server,Oracle Developer suite,DATABASE LISTENER,Web Cache,Reports Services,DCM Discovery,Log Loader which can be
seen below.

-bash-3.2$ view portlist.ini

;OracleAS Components reserve the following ports at install time.
;As a post-installation step, you can reconfigure a component to use a different port.
;Those changes will not be visible in this file.

Host Name = TESTNODEebs.in.apps.com

Oracle HTTP Server port = 7778
Oracle HTTP Server Listen port = 7779
Oracle HTTP Server SSL port = 8250
Oracle HTTP Server Listen (SSL) port = 4444
Java Object Cache port = 7000
Oracle Notification Server Request port = 6003
Oracle Notification Server Local port = 6102
Oracle Notification Server Remote port = 6201
Oracle Developer Suite HTTP port = 8888
Oracle Developer Suite JMS port = 9240
Oracle Developer Suite RMI port = 23910
ASG port = 7891
Oracle HTTP Server Diagnostic port = 7200
Log Loader port = 44000
Application Server Control RMI port = 1850
DCM Discovery port = 7100
Oracle Net Listener = 1521
Oracle Management Agent Port = 1157
Application Server Control port = 1156
Web Cache HTTP Listen port = 7778
Web Cache HTTP Listen (SSL) port = 8250
Web Cache Administration port = 9400
Web Cache Invalidation port = 9401
Web Cache Statistics port = 9402
Reports Services bridge port = 14011
Reports Services discoveryService port = 14021
Reports Services SQL*Net port = 1950

If you don't know port number of your web server you can check it in file
$IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf with directive Listen & Port

12)How we check the sysadmin password which we are using to login Oracle E-business suite home page is correct?(Apps DBA interview question)
Ans:We Use Following query to check whether 'sysadmin' password that we are
using is correct or incorrect.

select fnd_web_sec.validate_login('SYSADMIN','Passord_for_sysadmin')
from dual;

If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using is wrong. We have to change sysadmin password using 'FNDCPASS' utility.

Number of Visitors