Oracle Apps R12 Log File Locations

Oracle Apps R12 Log File Locations

Here is the list of Log file location in Oracle Applications for Startup/Shutdown, Cloning, Patching, DB & Apps Listener and various components in Apps R12

A. Startup/Shutdown Log files for Application Tier in R12

Instance Top is new TOP added in R12

Startup/Shutdown error message text files like adapcctl.txt, adcmctl.txt

$INST_TOP/logs/appl/admin/log

Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)

$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)

$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp]

$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J~, oa*, opmn.log)

$INST_TOP/logs/ora/10.1.2/network/ (listener log)

$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)

B. Log files related to cloning in R12

Preclone log files in source instance

Database Tier  $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)

Application Tier -

$INST_TOP/apps/$CONTEXT_NAME/admin/log/(StageAppsTier_MMDDHHMM.log)

Clone log files in target instance

Database Tier  $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log

Apps Tier  $INST_TOP/admin/log/ApplyAppsTier_.log

If your clone on DB Tier fails while running txkConfigDBOcm.pl (Check metalink note  415020.1)

During clone step on DB Tier it prompts for Target System base directory for source homes and during this you have to give like /base_install_dir like ../../r12 and not oracle home like ../../r12/db/tech_st_10.2.0

C. Patching related log files in R12

i) Application Tier adpatch log  $APPL_TOP/admin/$SID/log/

ii) Developer (Developer/Forms & Reports 10.1.2) Patch  $ORACLE_HOME/.patch_storage

iii) Web Server (Apache) patch  $IAS_ORACLE_HOME/.patch_storage

iv) Database Tier opatch log  $ORACLE_HOME/.patch_storage

D. Autoconfig related log files in R12

i) Database Tier Autoconfig log :

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/adconfig.log

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/NetServiceHandler.log

ii) Application Tier Autoconfig log : $INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

Autoconfig context file location in R12 $INST_TOP/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml

E. R12 Installation Logs

Database Tier Installation

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTechStack_.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ohclone.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/make_.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/installdbf.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_.log RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDatabase_.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log

RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log

Application Tier Installation

$INST_TOP/logs/.log

$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppsTechStack.log

$INST_TOP/logs/ora/10.1.2/install/make_.log

$INST_TOP/logs/ora/10.1.3/install/make_.log

$INST_TOP/admin/log/ApplyAppsTechStack.log

$INST_TOP/admin/log/ohclone.log

$APPL_TOP/admin/$CONTEXT_NAME/log/installAppl.log

$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppltop_.log

$APPL_TOP/admin/$CONTEXT_NAME/log//adconfig.log

$APPL_TOP/admin/$CONTEXT_NAME/log//NetServiceHandler.log

Inventory Registration

$Global Inventory/logs/cloneActions.log

$Global Inventory/logs/oraInstall.log

$Global Inventory/logs/silentInstall.log

F. Other log files in R12

1) Database Tier

1.1) Relink Log files

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log

1.2) Alert Log Files

$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

1.3) Network Logs

$ORACLE_HOME/network/admin/$SID.log

1.4) OUI Logs

OUI Inventory Logs :

$ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs

2) Application Tier

$ORACLE_HOME/j2ee/DevSuite/log

$ORACLE_HOME/opmn/logs

$ORACLE_HOME/network/logs

Tech Stack Patch 10.1.3 (Web/HTTP Server)

$IAS_ORACLE_HOME/j2ee/forms/logs

$IAS_ORACLE_HOME/j2ee/oafm/logs

$IAS_ORACLE_HOME/j2ee/oacore/logs

$IAS_ORACLE_HOME/opmn/logs

$IAS_ORACLE_HOME/network/log

$INST_TOP/logs/ora/10.1.2

$INST_TOP/logs/ora/10.1.3

$INST_TOP/logs/appl/conc/log

$INST_TOP/logs/appl/admin/log

UNIX & AIX

1. Kill all the O.S process of a particular user | kill -9

Want to kill all the O.S process that are running under particular user.

UNIX$ ps -aef  | grep -i shaik | awk '{print $2}' | sed 's/^/kill -9 /' > 1.txt
UNIX$./1.txt

2. Copy file from source to destination when size > 0 bytes

#!/bin/ksh
wdate=`date '+%Y-%m-%d'`
wdate1=`date '+%m%d%y'`
logdate=`date '+%m%d%y%H%M%S'`
source=/your source/$wdate
destination=/yourdestination
log_dir=/loglocations/log
log=$log_dir/logfilename_$wdate1.log
while true
do
  if [ -d "$source" ]
  then
echo $source
echo $source/*.csv
      if [ ! -s "$source/*.csv"  ]
      then
         echo " File exists- copying the File to destination @$logdate" >> $log
         cp  $source/*.csv $destination/logfilename_$wdate1.csv
         echo " copied the file..exiting now  $logdate" >>$log
         exit 0
      else
   echo "sleepping ..."
          sleep 10
      fi
  fi
echo $wdate
done

3. find files within directories

find . -type f -exec grep -il "hello" {} \;

or if you know the file type

find . -type f -name "*.ext" -exec grep -il "hello" {} \

4. find number of cpu

On Solaris
psrinfo -v
or
/usr/platform/sun4u/sbin/prtdiag -v

5. use truss to find the process execution

$ truss -aefo logfile
$ truss -rall -wall -p

make sure the logfile is stored where enough size is available.

6. UNIX prstat vs top

While digging for problems in UNIX prstat comes handy in looking at the some of the statistics of the server.

prstat - report active process statistics

example:

Now
statistics are sorted by cpu high -low
prstat -s cpu
or -u for a particular user
prstat -u oracle -s cpu

or top 5 cpu events
prstat -s cpu -n 5

or
prstat -T (total)

Other options available to prstat are:

  -a    Report information about processes and users

-T Report information about processes and tasks.

  -u    Report only processes whose effective user  ID

  -v    Report  verbose  process  usage

  -s key            Sort output lines (that is, processes, lwps, or users)            by  key  in descending order. Only one key can be used            as an argument.             There are five possible key values:             cpu   Sort by process CPU usage. This is the default.             pri   Sort by process priority.             rss   Sort by resident set size.             size  Sort by size of process image.
-n ntop[,nbottom] (comes with ntop or nbottom)

7. Unix memory leak issues.

If several crashes occur that have an associated large core file, then this is a good indication of a potential memory leak.


At any stage if you want to generate the core dump of an active process:

On Solaris:
-0 option is to manually give the path and filename for the core dump
gcore -o /obishared/obiqb/Lognode2/core.19199 19199
gcore: /obishared/obiqb/Lognode2/core.19199.19199 dumped




Start collecting memory consumption statistics.
Solaris

The prstat command can be used to gather basic performance data to help identify if there are any processes consuming a large amount

of memory. For example, the data below is sorted by the SIZE column. SIZE is the total virtual memory size of the process:

prstat -s size


AIX

The ps command can be used to show basic memory usage per process. For example, the data below is sorted by the VSZ column, the total

virtual memory size of the process in KB:

ps -efo "vsz,pid,user,cpu,thcount,comm" | sort –n


HP-UX

The top command can be used to show basic memory usage per process. For example, the SIZE column is the total virtual memory size of

the process in KB:

top

Linux

The ps command can be used to show basic memory usage per process. For example, the data below shows the memory being used by all the

siebmtshmw proceses in KB:


ps axo user,pid,vsz,thcount,cmd | grep siebmtshmw






If you are experiencing some of the symptoms described above or if you suspect a memory leak, it is particularly important that

performance data is captured so the memory leak can be confirmed. It is important to gather at least the following pieces of

information:


Process ID (PID)
Size of the process
The executing command
The timestamp information showing exactly when the data was captured.


One method of capturing this data is to use a shell script. The following are examples of shell scripts that can be used for the

various OS types:

Solaris

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -eo pid,vsz,rss,pcpu,args | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done

AIX

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -efo "pid,vsz,user,cpu,thcount,comm" | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done

HP-UX

while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps -elf | grep $pid >> ps.log.$pid
done
sleep 30
done


Linux


while true
do
for pid in `cat pids`
do var=`date|cut -d' ' -f4`
echo "$var :\c" >> ps.log.$pid
ps axo pid,vsz,user,%cpu,thcount,cmd | grep $pid |grep -v grep >> ps.log.$pid
done
sleep 30
done


above are snippets from MOS.
References:

Note 477520.1 "How To Troubleshoot Siebel Server Component Crashes on UNIX". It is however possible for a process to crash as a result of a memory leak.
Note 477004.1 "How Can Users Prevent Core Files from Being Overwritten on UNIX Platforms?"

Backup & Recovery

1. RMAN switch database to copy

RMAN image copy .. switching database to image copy backup.


>r sql
sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 23 15:09:10 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size                  2041368 bytes
Variable Size            1258297832 bytes
Database Buffers          553648128 bytes
Redo Buffers               14729216 bytes
Database mounted.


labrman01(MYDB)  /ora_backup/backups/MYDB
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 23 15:10:13 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN> connect target /

connected to target database: MYDB (DBID=2563884143, not open)

RMAN> connect catalog rcat10g/rcat10g@rcat;

connected to recovery catalog database

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp"
datafile 2 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm"
datafile 3 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr"
datafile 4 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib"
datafile 5 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij"
datafile 6 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm"
datafile 8 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm"
datafile 9 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib"
datafile 10 switched to datafile copy "/ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq"
starting full resync of recovery catalog
full resync complete


RMAN>  run{
2> set until time "to_date('08/23/2010 15:00:00','mm/dd/yyyy hh24:mi:ss')";
3> recover database;
4> }

executing command: SET until clause

Starting recover at 23-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1092 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-UNDOTBS1_FNO-2_ullm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v7lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSTEM_FNO-1_unlm2jhp
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v9lm2n0r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_BI_FNO-8_umlm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v8lm2n02_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-SYSAUX_FNO-3_uolm2jhr
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_valm2n1b_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_APPS_FNO-9_uplm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vblm2n1c_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-TOOLS_FNO-5_urlm2jij
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vdlm2n1s_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-MYDB_DATA_FNO-6_uklm2jgm
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_v6lm2n01_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-USERS_FNO-4_uqlm2jib
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_vclm2n1r_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00010: /ora_backup/backups/MYDB/MYDB_image_data_D-MYDB_I-2563884143_TS-HYP_HAL_DATA_FNO-10_uslm2jiq
channel ORA_DISK_1: reading from backup piece /ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/ora_backup/backups/MYDB/MYDB_image_velm2n23_1_1 tag=TAG20100823T145351
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc
archive log thread 1 sequence 7 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000007_0727545786.arc
archive log thread 1 sequence 8 is already on disk as file /adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000008_0727545786.arc
archive log filename=/adm02/u8001/MYDB/arch/arch_HYPQ_0001_0000000006_0727545786.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-AUG-10


RMAN> run{
2> sql 'alter database open resetlogs';
3> }

sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
761     774     MYDB     2563884143       PARENT  1          29-OCT-07
761     762     MYDB     2563884143       PARENT  10513166137703 21-JUL-10
761     4636    MYDB     2563884143       PARENT  10513166276407 20-AUG-10
761     5187    MYDB     2563884143       PARENT  10513166280927 20-AUG-10
761     6336    MYDB     2563884143       PARENT  10513166285247 20-AUG-10
761     6786    MYDB     2563884143       CURRENT 10513166407407 23-AUG-10
761     1681    MYDB     2563884143       ORPHAN  10513167403561 20-AUG-10

2.ORA-16038: log sequence# cannot be archived

After the 3 node - 2node RAC Apps database clone one of our DBA forgot to disable the unused thread#3.

i.e Our Prod was 3-node RAC hence three threads for redologs where as our QA system is two node RAC and the 3rd thread should be disabled ( No third node to capture the logs for thread#3) if not during the RMAN backup you will get the below errors.



Starting backup at 31-AUG-12
ORACLE error from target database:
ORA-16038: log 3 sequence# 171 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+DBNAME_FRA_01/DBNAME/onlinelog/group_3.4609.790630963'
ORA-00312: online log 3 thread 1: '+DBNAME_FRA_01/DBNAME/onlinelog/group_3.4547.790630981'

Fix:

Login into the DB:

SQL>  select inst_id,open_mode from gv$database;

   INST_ID OPEN_MODE
---------- --------------------
         1 READ WRITE
         2 READ WRITE


SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          2 UNUSED
         5          2 UNUSED
         6          2 UNUSED
         7          3 UNUSED
         8          3 UNUSED
         9          3 UNUSED
        10          3 UNUSED

10 rows selected.

SQL> alter database disable thread 3;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 9;

Database altered.

SQL> alter database drop logfile group 10;

Database altered.

SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          2 UNUSED
         5          2 UNUSED
         6          2 UNUSED

6 rows selected.

Now restart the RMAN archivelog backup:


Starting backup at 31-AUG-12
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=790830466
input archived log thread=1 sequence=2 RECID=2 STAMP=790851323
input archived log thread=1 sequence=3 RECID=3 STAMP=790893321

3. RMAN-06183: datafile or datafile copy (file number ) larger than MAXSETSIZE

One of our database backups were failing, when looked at the backup log.

RMAN>
connected to target database: MYDB (DBID=1218259347)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
allocated channel: dsk1
channel dsk1: SID=1136 instance=MYDB1 device type=DISK

allocated channel: dsk2
channel dsk2: SID=1419 instance=MYDB1 device type=DISK

Starting backup at 29-NOV-11
released channel: dsk1
released channel: dsk2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/29/2011 16:00:45
RMAN-06183: datafile or datafile copy +DATA_01/MYDB/datafile/xxusa_data.307.757873839 (file number 40) larger than MAXSETSIZE

RMAN>


From the above error message it is evident that MAXSETSIZE is set at the RMAN level.

So to verify this:

unixhost(catalogdb) /export/home/oracle/sshaik
>rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 1 11:46:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target rmanbackup/@diskqb1

connected to target database: diskQB (DBID=1218259347)

RMAN> connect catalog rcat_oth/@catalogdb

connected to recovery catalog database

RMAN> show all;

RMAN configuration parameters for database with db_unique_name diskQB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
----
CONFIGURE MAXSETSIZE TO 30720 M;

Here we go so we have the max file size limit of 30g where as my file size is around 32 g. So to fiz this...

RMAN> CONFIGURE MAXSETSIZE clear
2> ;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 30720 M;
RMAN configuration parameters are successfully reset to default value
starting full resync of recovery catalog
full resync complete

RMAN> show all;

RMAN configuration parameters for database with db_unique_name diskQB are:

==
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
=

4. Unregister database from Recovery Catalog

In Recovery Catalog Database:
If the target database already deleted/dropped/decommissioned or not available
Then in 9i/10g
SQL> alter session set current_schema=RCAT10G_OTH;

Session altered.

SQL> execute dbms_rcvcat.unregisterdatabase(1,129982387);

PL/SQL procedure successfully completed.

If Target database is available and UP, Then in 10g

rman>connect target id/pwd@dbname
rman>connect catalog id/pwd@catalogdb

RMAN> UNREGISTER DATABASE ;

set linesize 1000
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

5. Picked up the above from google:

Size of the redo log file:

SQL> SELECT distinct(to_char((bytes*0.000001),'9990.999')) size_mb
FROM v$log;
2
SIZE_MB
---------
1073.742


size & number of redologs by day:

SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024/1024/1024) TOTAL_SIZE_GB,
to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size
order by 1 desc
/

6.

delete force noprompt archivelog all completed before 'sysdate-7';

If you want to delete archive logs older than few hours:

Verify the time like how many hours older than you want to delete.

SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate-1/11 from dual;


SYSDATE-1/11
-------------------
09/22/2011 08:33:05

SQL> SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
09/22/2011 10:44:15

SQL>

So sysdate-1/11 will delete the files older than 8:30 am.

RMAN>connect target /
delete noprompt archivelog until time '(sysdate-1/11)' backed up 1 times to device type sbt_tape;

or

RMAN> delete force noprompt obsolete;
RMAN> delete force noprompt expired;
cheers.