sql_id can be found in the following views
v$sql, V$sqlstat, or dba_hist_sqlstat
SET LINESIZE 100
COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
In 11g
SET LINESIZE 100 COLUMN value FORMAT A60 SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
tkprof
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table “” not found /]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
SPID is the operating system Process identifier (os pid)
PID is the Oracle Process identifier (ora pid)
--------------------------------------------------------------------------------------------------------------------
If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:
column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
----------------------------------------------------------------------------------------------------------------------
Note:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
Note:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
Note:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
Note:1102801.1 How to Get 10046 Trace for Parallel Query
Note:242374.1 Tracing PX session with a 10046 event or sql_trace
Note:258418.1 Getting 10046 Trace for Export and Import
/*
Multiple line comments
Can go between these delimiters
*/
SET TERM OFF -- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON -- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON -- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF -- HEADING = OFF will hide column headings
SET FEEDBACK OFF -- FEEDBACK = ON will count rows returned
SET PAUSE OFF -- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0 -- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80 -- LINESIZE = width of page (80 is typical)
SET VERIFY OFF -- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
This is located in a common library
#!/usr/bin/ksh
# Purpose: Search the alert log of $ORACLE_SID for error
# messages.
# If any error messages are found, send notification
# (see Notes below).
#
# Prereqs: $ORACLE_SID environment variable must be set or
# passed as a positional parameter. Also, $ORATAB
# environment variable must be set.
#
# Notes: If this script is invoked from a terminal session,
# display the results to the screen (STDOUT); otherwise,
# send a mail message to the specified mail accounts.
#
# Exit Status: 99 = Required input, $ORACLE_SID, not supplied.
# 90 = Invalid Oracle sid.
# 95 = init.ora file not found for input sid.
# 97 = Alert log file does not exist.
#
# Author: Michael Culp
# Date: May 2002
#===============================================================
# R E V I S I O N H I S T O R Y
#===============================================================
# Who:
# Date:
# Comments:
################################################################
function error_notification
{
if [ "$TERMINAL" = "Y" ]; then
echo "$ERROR_MSG"
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
echo "$ERROR_MSG" | mail $(sendvar)
done
fi
}
export SEND_MAIL_TO='mike.culp@gmail.com'
export SEND_MAIL_TO2='mike.culp@gmail.com'
SERVER=$(uname -n)
DATE=$(date +%c%t)
ERROR_MSG_FILE=/home/oracle/alertlog_message
# check to see if command is being executed interactively
/usr/bin/tty -s
STATUS=$?
if [ $STATUS = 0 ]
then
clear
TERMINAL=Y
else
TERMINAL=N
fi
# Test for required inputs.
# Positional parameters override env variables.
if [ ${1:-0} = 0 ]; then
if [ ${ORACLE_SID:-0} = 0 ]; then
ERROR_MSG=`echo "ORACLE_SID must be supplied as input to $0"`
error_notification
exit 99
fi
else
export ORACLE_SID=$1
fi
# Call the validate_sid function to verify ORACLE_SID is valid.
# The function also sets the ORACLE_HOME and ORACLE_BASE variables.
validate_sid
if [ "$VALID_SID" != "Y" ]; then
ERROR_MSG=`echo "ORACLE_SID ($ORACLE_SID) is invalid. Script is $0"`
error_notification
exit 90
fi
INIT_PATH="$ORACLE_HOME"/dbs
INIT_FILE="$INIT_PATH"/init"$ORACLE_SID".ora
if [ ! -s "$INIT_FILE" ]; then
ERROR_MSG=`echo "$INIT_FILE does not exist. Script is $0"`
error_notification
exit 95
fi
LOG_PATH="/"`awk -F= '/background_dump_dest/ { print $2 }' "$INIT_FILE" |tr -s " "|cut -f2-30 -d/`
ALERTLOG="$LOG_PATH"/alert_"$ORACLE_SID".log
# Create a list of valid Oracle error message prefixes by parsing
# the oerr script in $ORACLE_HOME/bin
/home/oracle/bin/awk_message_prefixes > /tmp/ora_prefixes
# Archiver error message prefix is not included in the oerr
# script. Add an entry for this into the "ora_prefixes" file.
echo '^ARC[0-9]: Error' >> /tmp/ora_prefixes
# Search the alert.log for any occurrences of an Oracle error
# message prefix.
grep -bsf /tmp/ora_prefixes $ALERTLOG > $ERROR_MSG_FILE
status=$?
if [ $status -eq 1 ]; then # no matches found
if [ $TERMINAL = 'Y' ]; then
echo "Amazingly enough, there are NO errors in $ALERTLOG"
fi
elif [ $status -gt 1 ]; then
ERROR_MSG=`echo "$ALERTLOG does not exist."`
error_notification
exit 97
fi
# If there are any messages in the message file, send
# appropriate notifications.
if [ -s $ERROR_MSG_FILE ]; then
echo "\nMessages from server $SERVER on $DATE" >> $ERROR_MSG_FILE
if [ $TERMINAL = 'Y' ]; then
cat $ERROR_MSG_FILE
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
mail $(sendvar) < $ERROR_MSG_FILE
done
fi
fi
if [ -f $ERROR_MSG_FILE ]; then
rm $ERROR_MSG_FILE
fi
if [ -f /tmp/ora_prefixes ]; then
rm -f /tmp/ora_prefixes
fi
exit
Clone DB OracleBase article
This article is a test
When doing any type of upgrade/patch I'm often asked about backing up the existing binaries and how to save the customer from minimal downtime. One of the ways this can be achieved is by simply making another oracle home tar copied from the existing home. This now gives several options that allow flexibity in many ways.
# tar -cvf /tmp/u01.tar /u01
First parameter is the location of the tar file, second paramater is the directory where the oracle home is located
# gzip /tmp/u01.tar
You can then gzip it to make it smaller/easier to move it
Once moved to the destination server
# gunzip /tmp/u01.tar.gz
# cd /
# tar -xvf /tmp/u01.tar
Run the root configutation scripts, generated as part of the original installation, on the destination server as the "root" user.
/u01/app/oraInventory/orainstRoot.sh /u01/app/oracle/product/11.2.0/db_1/root.sh
If you have not prepared the ".bash_profile" as part of the destination server setup, copy the file from the source to the destination server.
# scp /home/oracle/.bash_profile oracle@192.168.2.136:/home/oracle/.bash_profile
Edit the ".bash_profile" file on the destination server, giving it the correct value for the ORACLE_HOSTNAME environment variable.
Amend any hostname or IP references in the "listener.ora" and "tnsnames.ora" files in the "$ORACLE_HOME/network/admin" directory.
Edit the "/etc/oratab" making sure all instances are referenced correctly. In my case I need to add the following entry.
DB11G:/u01/app/oracle/product/11.2.0/db_1:Y
Tested a table on Exadata
I felt most folks looking for full-table scans were not getting the full picture and created my own script to pick up the operations I felt went into this category.
#!/bin/ksh
sqlplus -s "/ as sysdba" <<EOF
spool fts_MRC.log
set linesize 200
set pagesize 66
col operation format a13
col object_name format a32
col object_owner format a10
col options format a15
col executions format 999,999,999
select
a.sql_id,a.object_owner,a.object_name, rtrim(a.operation) operation,
a.options, sum(b.executions) executions, c.bytes, (sum(b.executions)*c.bytes)/(1024*1024) fts_meg
from
v\$sql_Plan a, v\$sqlarea b, dba_segments c
where (a.object_owner=c.owner and a.object_name=c.segment_name) and
a.address=b.address and
a.operation IN ('TABLE ACCESS','INDEX','HASH JOIN') and
nvl(a.options,'NULL') in ('FULL','FULL SCAN','FAST FULL SCAN','SKIP SCAN','SAMPLE FAST FULL SCAN','OUTER','NULL') and
a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS') and
b.executions>1
group by a.sql_id,a.object_owner, a.object_name, operation, options, c.bytes
order by 8, a.object_owner,a.object_name,operation, options, executions desc;
spool off
EOF
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
This is a query to show what is in cache currently
select decode(pd.bp_id,1,'KEEP',
2,'RECYCLE',
3,'DEFAULT',
4,'2K SUBCACHE',
5,'4K SUBCACHE',
6,'8K SUBCACHE',
7,'16K SUBCACHE',
8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,
bh.blocks
from x\$kcbwds ds,
x\$kcbwbpd pd,
(select /*+ use_hash(x) */ set_ds,
o.name object_name,
count(*) BLOCKS
from obj\$ o,
x\$bh x
where o.dataobj# = x.obj and
x.state !=0 and
o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid and
ds.set_id <= pd.bp_hi_sid and
pd.bp_size != 0 and
ds.addr=bh.set_ds;
Advanced features of Oracle Net include failover and load balancing. These features are actually interrelated in as much as you usually don’t have one without the other. While they are mostly used in a RAC environment, they can be set up in a single instance environment as well.
FAILOVER:
In the context of Oracle Net, failover refers to the mechanism of switching over to an alternate "database" or resource when connection to the primary resource gets terminated or unavailable during a connection request. Connection failure may be broadly categorized as:
The first category of connection failure can be dealt with comparatively easily. If your attempt to connect to an instance fails, you can make the connection attempt again, but this time to a backup instance. As long as you have backup instances configured, you can continue trying to connect, until a successful connection is established. This process is technically termed as Connect Time Failover.
The second category of connection failure is of the type that happens after a successful connection has already been established, and subsequently the connection gets terminated.
In such cases, the application normally has to handle all the details of reconnecting to a backup instance, re-establishing the session environment, and resubmitting any work lost, because of the break in the connection. The technical name for this type of failover mechanism is transparent application failover or TAF for short.
Ok, now let’s have a look at the mechanism of setting up these features. Let’s start with Connect Time Failover. Although Connect Time Failover is a RAC environment mechanism, you can still use it with non-RAC environments, where you have a mechanism like standby database in place. Connect Time Failover can be achieved by the simple expedient of configuring a Net Service Name through which clients may connect to the standby database, whenever they cannot access the primary database.
This can even be achieved with a single net service name provided you configure
a) Multiple listener addresses within a description
b) Multiple descriptions within a description list
The difference between the two is that while the former will use the same connect data for all the listener addresses, that latter may have separate connect data for each configured listener address. Below, I provide an example of both types of configuration:
Multiple Listener addresses within a description:
MCULP.TEST = (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=TEST_DB) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=MY_DB) (PORT=1521)) (FAILOVER= TRUE) ) (CONNECT_DATA= (SERVICE_NAME= mculp) ) )
An important issue to be aware of at this point is that Connect Time Failover only works for you, if you are using dynamic registration. This means that this feature won’t work if you have something like this configured in your listener.ora:
SID_LIST_MYLISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sales.us.acme.com)
(ORACLE_HOME=/s01/app/oracle/ora/prod)
(SID_NAME=sghosh)
)
)
Any reference to Global_dbname and you can forget about Connect Time Failover. By default, failover is enabled when you specify multiple addresses. However, you can disable failover by specifying (FAILOVER=false). When failover is disabled, Oracle Net will attempt to connect using the first address, and if that attempt fails, no further attempts will be made and Oracle Net will generate an error.
Multiple descriptions within a description list:
MCULP.TEST= (DESCRIPTION_LIST= (FAILOVER=true) (LOAD_BALANCE=false) (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=TEST_DB) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=mculp)) ) (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=MY_DB) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME= test_mculp)) ) )
Notice that in the description list above, I have put (FAILOVER=true) and (LOAD_BALANCE=false). There is no real need to put (FAILOVER=true), as it is the default behavior, however, (LOAD_BALANCE=false) does not represent default behavior, and I have to set it to false to stop client load balancing which is enabled by default, if I am using multiple description lists. When client load balancing is enabled, Oracle Net will randomly choose descriptions from the description list to make a client connection.
Now, let’s look at how TAF works. Unlike connect time failover, which is invoked before the connection is made, TAF comes into play after the connection is made (and then, broken). If the connection is lost while the application is running, Oracle Net will transparently reconnect the application to another instance accessing the same database. The failover is done gracefully and uses failover aware APIs built into OCI.
TAF supports two types of failover: SESSION and SELECT. A SESSION failover connection is not over ambitious. It just fails over to a backup instance. All work in progress at that point are irrevocably lost. SELECT failover is more intricate in as much as it enables some type of read only application to failover without losing the work in progress. If a SELECT statement was in progress at the time of the termination of the connection, then as the connection is reestablished to a backup instance, Oracle Net re-executes the SELECT statement and positions the cursor in a manner that the client can seamlessly continue fetching the rows. But that’s about all that TAF has to offer. It doesn’t have any mechanism to recover DML statements that were in progress, when the failure occurred, or even for SELECT statements, you lose global temporary tables, package states and session settings.
TAF supports two failover methods: BASIC and PRECONNECT. In BASIC method, you connect to the backup instance when the primary connection fails. In the PRECONNECT method, you connect to the backup instance at the same time you connect to the primary instance. This has the obvious benefit of having a backup connection available all of the time, thus reducing the time of ‘failover’. But the downside is that you have to pay the extra ‘cost’ in terms of resources spent, of having a backup connection open all the while.
TAF is configured by adding a FAILOVER_MODE parameter to the CONNECT_DATA parameter for a net service name. Since you cannot configure TAF using the Net Manager, you have to use either OEM or manually edit tnsnames.ora. If you are going to use TAF, then you have to have a backup instance in place. That means you have to configure two net service names—one to connect to the primary instance and the other to connect to the backup instance. The example below shows a TAF configuration where connections to TEST will failover to TEST_BKUP.
TEST=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (HOST=NEW_HOST) (PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=saibal.ghosh)
(FAILOVER_MODE= (TYPE=SELECT) (METHOD=BASIC) (BACKUP=TEST_BKUP))
)
)
TEST_BKUP =
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (HOST=BKUP_HOST) (PORT=1526))
(CONNECT_DATA=
(SERVICE_NAME= sbkup.ghosh)
)
)
The definition of TEST contains a FAILOVER_MODE entry that specifies the name of the net service name to which a connection is to be made, should the TEST connection happen to fail. In this particular example, I have ‘failed over’ to an entirely different database, not to another instance accessing the same database. I did this to show that TAF can work either way, but if you are failing over to a different database, then you should keep the two databases in sync by using a mechanism like standby database.
Normally, TAF makes only a single attempt to connect to the backup instance. However, if you specify the RETRIES and DELAY parameters, you can force TAF to make multiple connection attempts to the backup instance. The following example shows TAF configured for 10 retries, each at an interval of fifteen seconds.
PROD_BKUP=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (HOST=ss-bkup) (PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=saibal6)
(FAILOVER_MODE=
(TYPE=SELECT) (METHOD= BASIC)(BACKUP=saibal6_bkup) (RETRIES= 10)
(DELAY= 15))
)
)
RETRIES and DELAY parameters may be gainfully employed where you are using a standby database to failover to and such a database may take a few moments to be brought up.
If we want to have the entire failover mechanism configured, so that we can take advantage of both connect time failover, as well as transparent application failover, we can put something like the following in place:
PROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP) (HOST=ss01-main) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP) (HOST=ss02-main) (PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=saibal6)
(FAILOVER_MODE=
(TYPE=SELECT)(METHOD=BASIC)(BACKUP=PROD_BKUP) )
)
PROD_BKUP=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (HOST=ss02-main) (PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=saibal6)
)
)
Now, let’s analyze the scenario above. The failover set up is from ss01-main to ss02-main. But a problem will occur where the initial connection is made to ss-02-main because of connect time failover; then we are already connected to the backup mode. If that fails, we have had it, there’s nowhere else to go. And what’s more, if you are thinking of using a description list, with several descriptions, that won’t work either. TAF settings are picked up from the first connect_data entry encountered, so other descriptions in the description list become useless.
LOAD BALANCING
Load balancing may be defined as distributing a job or piece of work over multiple resources. RAC is an ideal environment for distributing load amongst multiple instances accessing the same physical database. Other environments may also suitably configured to invoke load balancing, and in the following few paragraphs I show how load balancing can be set up.
CLIENT LOAD BALANCING: You can configure load balancing either at the client end or at the server end. Client load balancing is configured at the net service name level, and it is done simply by providing multiple addresses in an address list, or multiple descriptions in a description list. When load balancing is enabled, Oracle Net chooses an address to connect to in a random order rather than sequential order. This has the effect of clients connecting through
addresses which are picked up at random and no one address is overloaded. But significantly, there is no guarantee that just because clients are being connected through different addresses picked at random, there is an even distribution of workload at the server end. To do that, you will need to configure load balancing at the server end which is discussed below.
CONNECTION LOAD BALANCING: This feature improves connection performance by allowing the listener distribute new connections to different dispatchers and instances. The listener is in a position to do so because due to dynamic registration, the current load of instances and dispatchers are available with the listener. This allows the listener to balance the load across dispatchers and instances while connecting client connection requests.
Connection load balancing may be done in:
Load balancing is done in the following order: In case of dedicated server configuration it is:
In case of shared server, a listener selects a dispatcher in the following order:
Connection load balancing may also be combined with client load balancing to leverage load balancing activity. While the listener will load-balance connections to dispatchers/instances, client load balancing will distribute load of handling new connections over more than one listener. Add failover at the client end and you will be setting up a robust system, which will insulate the clients from potential connection failures, while at the same time keeping an eye on performance.
This piece of code sets files equal to 3 different files, then goes through a for / next loop to determine if they exist
#!/bin/ksh
files="/etc/passwd /etc/group /etc/hosts"
for f in $files; do
if [ ! -f $f ]
then
echo "$f file missing!"
fi
done
#!/bin/ksh for car in $(cat car.txt) do print "Current Car : $car" done
#!/bin/ksh for f in $(ls /tmp/*) do print "Full file path in /tmp dir : $f" done