This script will identify the trace file on the database server from a Request ID if it generates a trace file. It also gives the session information if the session is still running. Usually if the trace option is turned on, a trace file is created by the Request.
SQL> SELECT 'Request id: '|| request_id ,
'Trace id: '|| oracle_Process_id,
'Trace Flag: '|| req.enable_trace,
'Trace Name: '|| dest.value || '/' || lower(dbnm.value) || '_ora_' || oracle_process_id || '.trc',
'Prog. Name: '|| prog.user_concurrent_program_name,
'File Name: '|| execname.execution_file_name || ' ' || execname.subroutine_name ,
'Status : '|| decode(phase_code,'R','Running') || '-' || decode(status_code,'R','Normal'),
'SID Serial: ' || ses.sid || ',' || ses.serial#,
'Module : ' || ses.module
from apps.fnd_concurrent_requests req, v$session ses,
v$process proc, v$parameter dest, v$parameter dbnm,
apps.fnd_concurrent_programs_vl prog, apps.fnd_executables execname
where req.request_id = 31414905
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id ;
Similar information can be got from two simple queries:
SQL> select value from v$parameter where name = 'user_dump_dest';
VALUE
----------------------------------------------------------------------
/path/to/udump
SQL> select oracle_process_id, enable_trace from fnd_concurrent_requests where request_id = 5589516;
ORACLE_PROCESS_ID ENABLE_TRACE
-------------------------- ------------------
11662 N
Monday, June 16, 2008
Wednesday, May 28, 2008
Two RMAN errors during restore
1. "restore database from tag=TAG20080113T210205" got errors:
ORA-19870: error reading backup piece bk_a2j634ef_1_1_643928527
ORA-19507: failed to retrieve sequential file, handle="bk_a2j634ef_1_1_643928527", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup filenot found in NetBackup catalog
Even "RMAN> list backup of database completed after 'sysdate - 16';" show the backuppiece (and the TAG) is in status "AVAILABLE ".
In this case, most likely the backup tape has been recycled. You may not have chance to restore the database from this backup set.
2. If you see below errors:
......
channel ch02: reading from backup piece pejdb4dd_1_1
ORA-19870: error reading backup piece pajdb2gd_1_1
ORA-19507: failed to retrieve sequential file, handle="pajdb2gd_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to open backup file for restore.
ORA-19870: error reading backup piece ppjddfcb_1_1
ORA-19507: failed to retrieve sequential file, handle="ppjddfcb_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file
Most likely RMAN can not locate the next tape. You need to make sure all tapes for the backup set are in the tape drive.
ORA-19870: error reading backup piece bk_a2j634ef_1_1_643928527
ORA-19507: failed to retrieve sequential file, handle="bk_a2j634ef_1_1_643928527", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file
Even "RMAN> list backup of database completed after 'sysdate - 16';" show the backuppiece (and the TAG) is in status "AVAILABLE ".
In this case, most likely the backup tape has been recycled. You may not have chance to restore the database from this backup set.
2. If you see below errors:
......
channel ch02: reading from backup piece pejdb4dd_1_1
ORA-19870: error reading backup piece pajdb2gd_1_1
ORA-19507: failed to retrieve sequential file, handle="pajdb2gd_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to open backup file for restore.
ORA-19870: error reading backup piece ppjddfcb_1_1
ORA-19507: failed to retrieve sequential file, handle="ppjddfcb_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file
Most likely RMAN can not locate the next tape. You need to make sure all tapes for the backup set are in the tape drive.
Monday, May 26, 2008
Delete stats to speed up RMAN backup
There is a way that may speed up the RMAN backing up in 10G.
1. Run the following as sysdba on the target database:
SQL>exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
2. Implement the following sql statement as the first entry in your RMAN backup script:
run {sql "alter session set optimizer_mode=RULE";
...
}
Note: the 1st statement will cleanup old stats. You may want to wait until rman finishes if it is running. You cannot do anything to improve performance of the currently running rman session.
1. Run the following as sysdba on the target database:
SQL>exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
2. Implement the following sql statement as the first entry in your RMAN backup script:
run {sql "alter session set optimizer_mode=RULE";
...
}
Note: the 1st statement will cleanup old stats. You may want to wait until rman finishes if it is running. You cannot do anything to improve performance of the currently running rman session.
Friday, May 23, 2008
.profile to setup Oracle login environment
This is to show how .profile uses to a 2nd file on Linux (ksh) to set up Oracle environment.
auohsjy02 > cat .profile
. ./profile.iascc_env
auohsjy02 > ls -al *profile*
-rw-r--r-- 1 iasjy iasjy 2651 Mar 23 14:39 profile.iascc_env
auohsjy02 > cat profile.iascc_env
trap "rm - f /tmp/oh_list.$$ /tmp/valid_oh_list.$$ /tmp/sid_list_tmp.$$ /tmp/sid_list.$$; return" 1 2 3 15
# get list of ORACLE_HOME
case `uname` in
"Linux")
oratab=/etc/oratab
;;
"HP-UX")
oratab=/etc/oratab
;;
"SunOS")
oratab=/var/opt/oracle/oratab
;;
esac
awk -F":" "/^[^#]/ {print \$2}" $oratab sortuniq > /tmp/oh_list.$$
# validate the list
"rm" -f /tmp/valid_oh_list.$$
total=0
for oh in `cat /tmp/oh_list.$$`; do
if [ -w $oh/bin/genclntsh ]; then
echo $oh >> /tmp/valid_oh_list.$$;
total=`expr $total + 1`
fi
done
"rm" -f /tmp/oh_list.$$
# show menu
if [ $total -gt 1 ]; then
echo "Available ORACLE_HOME:"
echo ""
i=1
for oh in `cat /tmp/valid_oh_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_HOME from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_HOME=`sed -n ${answer}p /tmp/valid_oh_list.$$ 2>/dev/null`
export ORACLE_HOME
"rm" -f /tmp/valid_oh_list.$$
echo "$ORACLE_HOME " grep -i "ocsmt_904" 1>/dev/null 2>/dev/null
if [ "$?" = "0" ]; then
CTSIS_IPC_PATH=/var/opt/oracle/ocs/tmp
export CTSIS_IPC_PATH
fi
. /etc/ora_cc_env
# ORACLE_SID
ORACLE_SID=
total=0
# check spfile first
sidlist=`"ls" $ORACLE_HOME/dbs/spfile?*.ora 2>/dev/null`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b7-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
else
# then init.ora
sidlist=`"ls" $ORACLE_HOME/dbs/init?*.ora 2>/dev/null grep -v initdw`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b5-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
fi
fi
sort /tmp/sid_list_tmp.$$ 2>/dev/null uniq > /tmp/sid_list.$$
total=`"wc" -l /tmp/sid_list.$$ awk '{print $1}'`
"rm" -f /tmp/sid_list_tmp.$$
# show menu
if [ "$total" -gt 1 ]; then
echo "ORACLE_SID list:"
echo ""
i=1
for oh in `cat /tmp/sid_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_SID from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_SID=`sed -n ${answer}p /tmp/sid_list.$$ 2>/dev/null`
export ORACLE_SID
"rm" -f /tmp/sid_list.$$
trap 1 2 3 15
echo "ORACLE_HOME=$ORACLE_HOME"
echo "ORACLE_SID=$ORACLE_SID"
auohsjy02 > echo $SHELL
/bin/ksh
auohsjy02 > cat .profile
. ./profile.iascc_env
auohsjy02 > ls -al *profile*
-rw-r--r-- 1 iasjy iasjy 2651 Mar 23 14:39 profile.iascc_env
auohsjy02 > cat profile.iascc_env
trap "rm - f /tmp/oh_list.$$ /tmp/valid_oh_list.$$ /tmp/sid_list_tmp.$$ /tmp/sid_list.$$; return" 1 2 3 15
# get list of ORACLE_HOME
case `uname` in
"Linux")
oratab=/etc/oratab
;;
"HP-UX")
oratab=/etc/oratab
;;
"SunOS")
oratab=/var/opt/oracle/oratab
;;
esac
awk -F":" "/^[^#]/ {print \$2}" $oratab sortuniq > /tmp/oh_list.$$
# validate the list
"rm" -f /tmp/valid_oh_list.$$
total=0
for oh in `cat /tmp/oh_list.$$`; do
if [ -w $oh/bin/genclntsh ]; then
echo $oh >> /tmp/valid_oh_list.$$;
total=`expr $total + 1`
fi
done
"rm" -f /tmp/oh_list.$$
# show menu
if [ $total -gt 1 ]; then
echo "Available ORACLE_HOME:"
echo ""
i=1
for oh in `cat /tmp/valid_oh_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_HOME from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_HOME=`sed -n ${answer}p /tmp/valid_oh_list.$$ 2>/dev/null`
export ORACLE_HOME
"rm" -f /tmp/valid_oh_list.$$
echo "$ORACLE_HOME " grep -i "ocsmt_904" 1>/dev/null 2>/dev/null
if [ "$?" = "0" ]; then
CTSIS_IPC_PATH=/var/opt/oracle/ocs/tmp
export CTSIS_IPC_PATH
fi
. /etc/ora_cc_env
# ORACLE_SID
ORACLE_SID=
total=0
# check spfile first
sidlist=`"ls" $ORACLE_HOME/dbs/spfile?*.ora 2>/dev/null`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b7-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
else
# then init.ora
sidlist=`"ls" $ORACLE_HOME/dbs/init?*.ora 2>/dev/null grep -v initdw`
if [ ! -z "$sidlist" ]; then
for sid in $sidlist; do
fname=`basename $sid`
valid_sid=`basename $fname .ora cut -b5-`
echo $valid_sid >> /tmp/sid_list_tmp.$$
done
fi
fi
sort /tmp/sid_list_tmp.$$ 2>/dev/null uniq > /tmp/sid_list.$$
total=`"wc" -l /tmp/sid_list.$$ awk '{print $1}'`
"rm" -f /tmp/sid_list_tmp.$$
# show menu
if [ "$total" -gt 1 ]; then
echo "ORACLE_SID list:"
echo ""
i=1
for oh in `cat /tmp/sid_list.$$`; do
echo "$i: $oh"
i=`expr $i + 1`
done
echo ""
answer=0
while [ "$answer" -lt 1 -o "$answer" -gt $total ];
do
echo "Please choose the ORACLE_SID from the list:"
read answer
case $answer in
[0-9]*)
;;
*)
answer=0
;;
esac
done
else
answer=1
fi
ORACLE_SID=`sed -n ${answer}p /tmp/sid_list.$$ 2>/dev/null`
export ORACLE_SID
"rm" -f /tmp/sid_list.$$
trap 1 2 3 15
echo "ORACLE_HOME=$ORACLE_HOME"
echo "ORACLE_SID=$ORACLE_SID"
auohsjy02 > echo $SHELL
/bin/ksh
Friday, May 16, 2008
Discoverer Logs
Discoverer 10g (10.1.2.48.18 in my server) provides a script to collect all logs for troubleshooting. The script called collectlogs.sh is located in $ORACLE_HOME/discoverer/util. (Note $ORACLE_HOME could be null in the Discoverer installation acconut).
I ran it by below line. It created two files: the .tar file, and loglist.txt file for listing all log files collected in the tar file.
$ pwd
/sjy/disco/product/disco_1012/discoverer/util
$ ./collectlogs.sh disco_log.tar > loglist.txt
Initializing ORB...
ORB initialized successfully.
Checking for Discoverer "ServicesStatus"...
"ServicesStatus" is running.
Checking the adminstrator set soft limit on no. of sessions ...
Current value of soft limit = 50 sessions.
Hint: Administrators can increase the soft limit on Discoverer sessions by modifying
"//ias-component[id='Discoverer']/process-type[id='SessionServer']/process-set/@maxprocs" attribute in the file /sjy/disco/product/disco_1012/opmn/conf/opmn.xml
Checking for Discoverer Preferences component...
Found Discoverer Preferences component.
Binding to Discoverer Server...
Successfully bound to Discoverer Server.
Checking that the machine has only one network card...
Yes.
cp: cannot stat `/spulti/disco/product/disco_1012/discoverer/util/diag.log': No such file or directory
cp: cannot stat `/spulti/disco/product/disco_1012/network/admin/sqlnet.ora': No such file or directory
cp: omitting directory `/spulti/disco/product/disco_1012/opmn/logs/states'
For troubleshooting purpose, three commands are useful to check the status of Discoverer processes and environment:
$ $ORACLE_HOME/dcm/bin/dcmctl getstate -v -d
Current State for Instance:sjy_disco.auohsjy02.com
Component Type Up Status In Sync Status
==============================================
1 home OC4J Up True
2 HTTP_Server HTTP_Server Up True
3 OC4J_BI_Forms OC4J Up True
$ opmnctl status
Processes in Instance: sjy_disco.auohsjy02.com
-------------------+--------------------+---------+---------
ias-component process-type pid status
-------------------+--------------------+---------+---------
DSA DSA N/A Down
LogLoader logloaderd N/A Down
dcm-daemon dcm-daemon 11231 Alive
WebCache WebCache 9828 Alive
WebCache WebCacheAdmin 9814 Alive
OC4J home 9815 Alive
OC4J OC4J_BI_Forms 9816 Alive
HTTP_Server HTTP_Server 9817 Alive
Discoverer ServicesStatus 9818 Alive
Discoverer PreferenceServer 9819 Alive
$ emctl status iasconsole
TZ set to US/Central
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://auohsjy02.com:10923/emd/console/aboutApplication
Oracle Enterprise Manager 10g Application Server Control is running.
I ran it by below line. It created two files: the .tar file, and loglist.txt file for listing all log files collected in the tar file.
$ pwd
/sjy/disco/product/disco_1012/discoverer/util
$ ./collectlogs.sh disco_log.tar > loglist.txt
Initializing ORB...
ORB initialized successfully.
Checking for Discoverer "ServicesStatus"...
"ServicesStatus" is running.
Checking the adminstrator set soft limit on no. of sessions ...
Current value of soft limit = 50 sessions.
Hint: Administrators can increase the soft limit on Discoverer sessions by modifying
"//ias-component[id='Discoverer']/process-type[id='SessionServer']/process-set/@maxprocs" attribute in the file /sjy/disco/product/disco_1012/opmn/conf/opmn.xml
Checking for Discoverer Preferences component...
Found Discoverer Preferences component.
Binding to Discoverer Server...
Successfully bound to Discoverer Server.
Checking that the machine has only one network card...
Yes.
cp: cannot stat `/spulti/disco/product/disco_1012/discoverer/util/diag.log': No such file or directory
cp: cannot stat `/spulti/disco/product/disco_1012/network/admin/sqlnet.ora': No such file or directory
cp: omitting directory `/spulti/disco/product/disco_1012/opmn/logs/states'
For troubleshooting purpose, three commands are useful to check the status of Discoverer processes and environment:
$ $ORACLE_HOME/dcm/bin/dcmctl getstate -v -d
Current State for Instance:sjy_disco.auohsjy02.com
Component Type Up Status In Sync Status
==============================================
1 home OC4J Up True
2 HTTP_Server HTTP_Server Up True
3 OC4J_BI_Forms OC4J Up True
$ opmnctl status
Processes in Instance: sjy_disco.auohsjy02.com
-------------------+--------------------+---------+---------
ias-component process-type pid status
-------------------+--------------------+---------+---------
DSA DSA N/A Down
LogLoader logloaderd N/A Down
dcm-daemon dcm-daemon 11231 Alive
WebCache WebCache 9828 Alive
WebCache WebCacheAdmin 9814 Alive
OC4J home 9815 Alive
OC4J OC4J_BI_Forms 9816 Alive
HTTP_Server HTTP_Server 9817 Alive
Discoverer ServicesStatus 9818 Alive
Discoverer PreferenceServer 9819 Alive
$ emctl status iasconsole
TZ set to US/Central
Oracle Enterprise Manager 10g Application Server Control Release 10.1.2.0.2
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://auohsjy02.com:10923/emd/console/aboutApplication
Oracle Enterprise Manager 10g Application Server Control is running.
Subscribe to:
Comments (Atom)