Oracle DBA Daily Experiences

When doing a incomplete recovery (i.e. database is open with resetlogs) and when running RMAN to backup that database after incomplete recovery you will get the following error.

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog

To fix this issue by performing the following steps.

1) Connect to RMAN catalog via SQL*Plus
$ sqlplus @rmancatalog

SQL> SELECT name, DBID, RESETLOGS_TIME FROM rc_database WHERE dbid=9999999999;

NAME DBID RESETLOGS
——– ———- ———
TESTDB 9999999999 29-JUL-09

Check the latest incarnation
SQL> SELECT dbid, name, dbinc_key, resetlogs_change#, resetlogs_time FROM rc_database_incarnation WHERE dbid=9999999999 ORDER BY resetlogs_time;

SQL>SELECT db_key,DBID,name,current_incarnation FROM rc_database_incarnation WHERE dbid = 9999999999 order by 1;

DBID NAME DBINC_KEY RESETLOGS_CHANGE# TO_CHAR(RESETLOGS_TI
———- ——– ———- —————– ——————–
9999999999 TESTDB 473490 1 16-FEB-2006 08:59:37
9999999999 TESTDB 473484 565658 07-MAY-2007 10:15:58
9999999999 TESTDB 774712 52500357 29-JUL-2009 11:12:21

So then connect to the rman catalog
$ export NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’
$ rman catalog target /
RMAN> reset database to incarnation 774712; — From step 1 after running this step you should be able to run backup on the database on which incomplete recovery was done

RMAN> resync catalog;

– shows the current incarnation is the current one
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
473483 473490 TESTDB 9999999999 PARENT 1 16-FEB-2006 08:59:37
473483 473484 TESTDB 9999999999 PARENT 565658 07-MAY-2007 10:15:58
473483 774712 TESTDB 9999999999 CURRENT 52500357 29-JUL-2009 11:12:21

Here are some of the options that can be passed to runInstaller
-slient – Slient mode
-debug – To get debug information
-noclusterEnabled – Disabled so no cluster nodes specified so it doesn’t install cluster aware
To turn on debugging when running installer one can pass the following flags -J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:
– ORA-1652: unable to extend temp .. (for temp tablespace)
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK LEVEL 3′;

It will write to the alert.log
Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off one would run the following SQL:
SQL> ALTER system SET EVENTS ’1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
SQL> alter system set event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on event for.

sed ’1,10d’ file – doesn’t print the top 10 lines from the file
sed ’5d’ myfile – delete 5th line from the file
sed ’5,$d’ myfile – Delete 5 till end of file lines from myfile

I use this script to clean up all the log and trace file from each host..  You can schedule a job in OEM to go and execute this in all hosts you want.

$cat /etc/oratab

ESRTSP:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
ESRTSS:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
RESTST:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
LISTENER_10g:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
AGENT_10g:/oracle/app/EPW/agent/v10203_ee_suse10:N

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

###############################################
###############################################
##  ADD ALL INSTANCES TO THE FOR LOOK LIST   ##
###############################################
###############################################

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:'|egrep -v  ‘LISTENER|AGENT’`
do

find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

done

#!/usr/bin/ksh

##################################################################################################################
#
# This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
#
##################################################################################################################

script=`basename $0`

export ORACLE_SID=$1
dir=/oracle/EPW/${ORACLE_SID}/temp/arch

tmpf=$dir/.$script.tmp

logcount=50

function GetAppliedLogfileSequenceNumber
{
sqlplus -S /nolog <<EOF > $tmpf
connect / as sysdba
set head off
set pages 0
select max(sequence#) from v\$archived_log where applied = ‘YES’;
select resetlogs_id from v\$database_incarnation where status = ‘CURRENT’;
exit
EOF
return
}

if [ -d $dir ]
then
cd $dir
GetAppliedLogfileSequenceNumber

if [ -s $tmpf ]
then
count=`cat $tmpf | awk ‘{print $1}’ | sed -n ’1p’;`
db_incarnation=`cat $tmpf | awk ‘{print $1}’ | sed -n ’3p’;`

if [ ${#count} -ne 0 ]
then
let count=$count-$logcount

if ((count <= 0))
then
echo “$script: log count is set to (non)zero no log(s) to remove”
exit 0
fi
else
exit 0
fi
else
echo “$script: no archive log(s) to remove”
exit 0
fi

while [ -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc ]
do
rm -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
#ls ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
rcode=$?

if ((rcode != 0))
then
echo “$script: cannot remove: ${ORACLE_SID}_${count}_1_${db_incarnation}.arc”
exit 1
else
let count=$count-1
fi
done

rm -f $tmpf

else
print “$script: $dir no such file or directory”
exit 1
fi
exit 0

One of the scenario we ran into when one of the data file reported there was block corruption as RMAN reported the following message in its logs. Note: This scenario was done on 9.2.0.1 running Linux.

RMAN-03009: failure of backup command on ch1 channel at 07/02/2009 04:27:06
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/oradata/TESTDB/TEST_data_01.dbf

And the alert.log (alert_TESTDB.log) also had the following message by RMAN
Reread of blocknum=443343, file=/u01/oradata/TESTDB/TEST_data_01.dbf. found same corrupt data
***
Corrupt block relative dba: 0x0346c3cf (file 13, block 443343)
Bad check value found during backing up datafile
Data in bad block -
type: 6 format: 2 rdba: 0x0346c3cf
last change scn: 0×0000.32a8f165 seq: 0×1 flg: 0×04
consistency value in tail: 0xf1650601
check value in block header: 0xeb4f, computed block checksum: 0xec16
spare1: 0×0, spare2: 0×0, spare3: 0×0

So to double check one can ran dbverify (dbv) or RMAN to validate the datafile which confirmed data corruption and the datafile# and the block#.
$ dbv blocksize=8192 file=/u01/oradata/TESTDB/TEST_data_01.dbf > /tmp/TEST_data_01_file.log 2>&1

DBVERIFY – Verification starting : FILE = /u01/oradata/TESTDB/TEST_data_01.dbf
Page 443343 is marked corrupt
***
Corrupt block relative dba: 0x0346c3cf (file 13, block 443343)
Bad check value found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0x0346c3cf
last change scn: 0×0000.32a8f165 seq: 0×1 flg: 0×04
consistency value in tail: 0xf1650601
check value in block header: 0xeb4f, computed block checksum: 0xec16
spare1: 0×0, spare2: 0×0, spare3: 0×0
***

DBVERIFY – Verification complete

Total Pages Examined : 486400
Total Pages Processed (Data) : 473439
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12950
Total Pages Marked Corrupt : 1
Total Pages Influx : 0

And using RMAN one can validate the same thing by checking the view v$database_block_corruption after running the script below.

RMAN> connect target /

RMAN> backup validate check logical database;
…

It reported the same block as show below:

SQL> SELECT * FROM v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
13 443343 1 0 FRACTURED

After it was confirmed there was only one datafile with one block corruption using block recover we were able to recover the datafile without shutting down the database. The channel allocated was the same that was specified when backing up the data file.

connect target /
connect catalog rmancatalog/rmancatalog@catalog
run {
allocate channel…;
blockrecover datafile 13 block 443343;
release channel …;
}

Log file:
Starting blockrecover at 02-JUL-09

channel ch1: restoring block(s)
channel ch1: specifying block(s) to restore from backup set
restoring blocks of datafile 00013
channel ch1: restored block(s) from backup piece 1
piece handle=g0kirlhe_1_1 tag=TAG20090630T040048 params=NULL
channel ch1: block restore complete

starting media recovery

archive log thread 1 sequence 30644 is already on disk as file /u01/oradata/TESTDB/arch/1_30644.dbf
archive log thread 1 sequence 30645 is already on disk as file /u01/oradata/TESTDB/arch/1_30645.dbf
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=30643
channel ch1: restored backup piece 1
piece handle=hlkj27vr_1_1 tag=TAG20090701T160827 params=NULL
channel ch1: restore complete
media recovery complete
Finished blockrecover at 02-JUL-09
released channel: ch1

Recovery Manager complete.

And after the block was restored we validated the datafile through RMAN and v$database_block_corruption reported no records found.

RMAN> connect target /
RMAN> backup validate check logical datafile 13;

Starting backup at 02-JUL-09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=22 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00013 name=/u01/oradata/TESTDB/TEST_data_01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:35
Finished backup at 02-JUL-09

sys@TESTDB> select * FROM v$database_block_corruption;

no rows selected