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 file not 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.

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.

.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

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.

Monitor Discoverer Processes (dis51ws) Not Terminated by Timeout

If user does not use "file > exit" or "file > close" to gracefully close the workbook or Discoverer, an orphaned session may be created. Some times, user may get error while trying to connect to Discoverer Viewer and Plus:

A connection error has occurred.
- Attempt 1. CORBA proto : Hard limit on number of sessions reached. Please contact your administrator or retry after some time.
Hint: An administrator can further diagnose connection problems by running the "checkdiscoverer" script under <ORACLE_HOME>/discoverer/util.

Here is a useful korn shell script:

$ cat script.ksh
# script to display dis51ws processes over 5 hours old.
# you need to be in the korn shell to run this.

echo "\nDD:HH:MM:SS PID Process"
echo "------------------------"
for i in `ps -A -o etime,pid,args grep dis51ws sed 's/ /_/g'`
do
myday=`echo $i awk '{ fred2=substr($1,1,2);print fred2}'`
myhour=`echo $i awk '{ fred2=substr($1,4,2);print fred2}'`
# the above line extracts the hour .

if test $myday = "__*"
then
if test $myhour != "__*"
then
if test $myhour -gt 5
# the last number here is the hour limit -
# you can customize this from 0 to 23.
then
process=`echo $i sed 's/_/ /g'`
echo $process
fi
fi
else # if any day at all is listed, then the proc must be over 5 hours.
process=`echo $i sed 's/_/ /g'`
echo $process
fi
done
echo "\n"

$ for i in ` ksh script.ksh awk '{ print $2}'`
> do
> kill -9 $i
> done


Note that Discoverer uses $ORACLE_HOME/discoverer/util/perf.txt to set up the timeout variable: Timeout = 1800 seconds. $ORACLE_HOME/opmn/conf/opmn.xml also has entries for time: <start timeout="600" /> <stop timeout="120" /> (not sure what it's for).

Profile Options Could Stop Concurrent Jobs

In production instance, MRP Workbench does not have data. The concurrent job "Purchasing MRP (Report)" failed with error in the log:
Request restarted at :01-MAY-2008 01:05:57
**Starts**01-MAY-2008 01:05:57
**Ends**01-MAY-2008 01:05:57
The set completed normally with outcome Error. The outcome was determined by the final stage, Load/Copy/Merge MDS (10).

The log on program "Load/Copy/Merge MDS" says that
APP-MRP-22132: Cannot get profile option MRP_DEBUG
Cause: The current routine cannot retrieve a value for the specified profile option.

Below query confirms that two profile options are blank because of human error, as it returns 0 row:

SELECT fpo.profile_option_name Profile,
fpov.profile_option_value Value,
decode(fpov.level_id,10001, 'SITE',10002, 'APPLICATION',10003, 'RESPONSIBILITY', 10004,'USER') "LEVEL",
fa.application_short_name App,
fr.responsibility_name Responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options fpo,
fnd_application fa,
apps.fnd_responsibility_vl fr,
fnd_user fu, fnd_logins fl
WHERE fpo.profile_option_id=fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.application_id(+)=fpov.level_value_application_id
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN
and fpo.profile_option_name in ('MRP_TRACE','MRP_DEBUG')
order by 1,3

After setting profile options "MRP:Debug" and "MRP:Trace" to 'No' at Site level, the MRP request set runs successfully.