When you install a server patch on your database server or Discoverer server, you need to check if the patch or pre-requisite patch has been installed or not. Here is how you can do that:
$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 6472361
If this command returns anything, the patch 6472361 may be installed on your server.
Below line also gives the list of installed patches. But, it does not give the patch number for the Top-level upgrade (such as 5337014 for upgrading 10.2.0.1.0 to 10.2.0.3.0. Without it, it is not easy to identify the binary file in download).
$ ./opatch lsinventory -oh $ORACLE_HOME
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
There are 2 products installed in this Oracle Home.
Interim patches (3) :
Patch 6596361 : applied on Fri Nov 23 10:24:05 MST 2007
Created on 20 Nov 2007, 02:42:09 hrs PST8PDT,M3.2.0,M11.1.0
Bugs fixed:
6596361
Patch 5556081 : applied on Mon Sep 17 14:21:59 MST 2007
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
5556081
Patch 5557962 : applied on Mon Sep 17 14:21:39 MST 2007
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
4269423, 5557962, 5528974
Rac system comprising of multiple nodes
Local node = dssdb1p
Remote node = dssdb2p
Wednesday, July 23, 2008
Monday, June 16, 2008
Script to find the trace file by Request ID
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
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
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
Subscribe to:
Comments (Atom)