CHECKLIST FOR validating UTL_FILE_DIR value and FND_FILE value
1 . Make sure the utl_file_dir should have multiple entries in single quote seperated by coma.
Correct Entry is:

EXAMPLE:

utl_file_dir='/app/D4CRP/d4crpcomn/temp','/orahome/product/D4CRP/10.2.0/appsutil/outbound/D4CRP_ibisdev-e1-zone08','/usr/tmp'


Wrong Entry:

utl_file_dir=/app/D4CRP/d4crpcomn/temp,/orahome/product/D4CRP/10.2.0/appsutil/outbound/D4CRP_ibisdev-e1-zone08,/usr/tmp


2 . MAKE SURE:


First Value of utl_file_dir should be the value of APPLTMP and APPLPTMP

EG:
utl_file_dir='/app/D4CRP/d4crpcomn/temp','/orahome/product/D4CRP/10.2.0/appsutil/outbound/D4CRP_ibisdev-e1-zone08','/usr/tmp'
APPLTMP=/app/D4CRP/d4crpcomn/temp
APPLPTMP=/app/D4CRP/d4crpcomn/temp


3) Steps To Validate at the DB level:

login to oracle user
sqlplus “/ as sysdba”
select value from v$parameter2 where name like '%utl%';

Correnct Output Should be :

VALUE
--------------------------------------------------------------------------------
/app/D4CRP/d4crpcomn/temp
/orahome/product/D4CRP/10.2.0/appsutil/outbound/D4CRP_ibisdev-e1-zone08
/usr/tmp


Incorrect Value:

VALUE
--------------------------------------------------------------------------------
/app/D4CRP/d4crpcomn/temp, /orahome/product/D4CRP/10.2.0/appsutil/outbound/D4CRP
_ibisdev-e1-zone08, /usr/tmp
Note :Correct the utlfile entry before you move to application.

4 . Steps to validate at application level:

sqlplus “apps/#####”

exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

Note : the above procedure should complete successfully if not then verify APPLTMP,APPLPTMP and utl_file_dir as specified from the step 1.
NOTE: If only APPLPTMP and APPLTMP value is wrong then you can change it to correct value and bounce the Managers. If UTL file entry is wrong then we need to correct it and bounce the whole instance(APPLICATION+DB)

v$parameter2 is the view which holds many value for single parameter.

For example

select name from v$parameter where name like '%utl%';
Will return only one row , but same query will return more than one value when the view name is changed to v$parameter2.

select count(*) from v$parameter2 where name like '%utl%' will return the number on entries in utl_file_dir parameter(utl_file_dir='/tmp','/$COMMON_TOP/tmp'; in this case the query will return 2.

Script to check the status of running RMAN Backup and Recovery sessions

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPSWHERE OPNAME LIKE 'RMAN%'AND OPNAME NOT LIKE '%aggregate%'AND TOTALWORK != 0AND SOFAR <> TOTALWORK;

Releasing Space in ASM flash area

When we use a flash area all the dupliacte copies and backup files are stored in the FLASH AREA.

Some files that are stored here are.

1)All archieve log file.
2)Logfile member (duplicate copy)
3)Control file duplicate (not always)
etc

After you have backedup your archivelog files to tape ,we need to delete the same from the flash area.

We issue the below command.(we are not using catalog)

rman target /

run
{
configure channel ch1 device type tape;
backup archivelog all;
}

RMAN>exit;

source the ASM home

asmcmd;
>cd flash

Delete all the archivelogs which were backedup

>exit;

Now if you check the flash area size (usedmb and freemb) it will be the same even after deleting the old archivelogs. This shows that no space was released from FLASH

this is because eventhough we deleted the file inside flash the entry is still present in the controlfile.

Solution

rman target /

RMAN> crosscheck archivelog all;
.
.
.
RMAN>list expired;
RMAN>delete expired archivelog all;
RMAN> exit;

now if we check the asm views you can see the increase in free MB in ASM FLASH

crosscheck archivelog all; will validate the archivelog files in controlfile with there physical location and if not present then it will mark that archive log as expired. when you delete expired the archivelog entry is deleted from the controlfile thus releasing the space in ASM flash.

Thanks,
Sandarsh.

ORA-27211: Failed to load Media Management Library

After Upgrading DB from 10.1.0.04 to 10.2.0.3 rman restore was failing.
We were trying to restore TEST from PROD which was recently upgraded to 10.2.0.3.
=============================================
We were receiving the below error when trying to run the rman duplicate command.
________________________________________________________________________________________________________________

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch00 channel at 08/01/2007 04:47:57
RMAN-10035: exception raised in RPC:
ORA-19624: operation failed, retry possible
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 173
RMAN-10031: RPC Error: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.DEVICEALLOCATE

RMAN> **end-of-file**
=============================================
CAUSE:
RMAN was not able to recognise the TAPE device.
Solution:
When we Install a TAPE there are certain libraryfiles which must be loaded or read by RMAN to recognize the tape device.
For example in windows if an utility is not installed properly then we get the error saying unable to locate the .dll etc.
The customer was using VERITAS NET backup which on instaling creates library files which must be available to RMAN to recognose the device.
These are the three libraries
libobk.sllibobk.sl64libobk.so
Sine RMAN is a ORACLE utility searches these libraries inside ORACLE_HOME/lib or lib32 based on version(32or64)
Create the below links so that rman can recognise the Tape device before starting backup or recovery
oracle@hposi00[prod] > ls -l libobk*lrwxr-xr-x 1 root sys 34 Jul 29 21:55 libobk.sl -> /usr/openv/netbackup/bin/libobk.sllrwxr-xr-x 1 root sys 36 Jul 29 21:55 libobk.sl64 -> /usr/openv/netbackup/bin/libobk.sl64lrwxr-xr-x 1 root sys 34 Jul 29 21:56 libobk.so -> /usr/openv/netbackup/bin/libobk.sooracle@hposi00[prod] > pwd/u01/app/oracle/102/lib
----------------------------------------------------------

unable to Find archivelog XXXX

Problem: Unable to find/lock the archivelog 'XXXX"

Scenario:
Archive log we manually backedup to tape and deleted from the source.

We configured a catalog for that database and then gave the below command;

rman target / catalog rmanrep/rmanrep@RMAN4
Recovery Manager: Release 9.2.0.3.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: RMAN4 (DBID=4109780682)connected to recovery catalog database
RMAN> backup format '/u01/app/oracle/sandy/proddb/1.arc' archivelog all

Unable to identift/locg logfile '/u01/app/oracle/sandy/proddb/9.2.0/arch/REOPENPROD_1_42.arc'


This error occured as 42 was deleted before creating the catalog.

the solution for this proble is

rman target / catalog rmanrep/rmanrep@RMAN4
Recovery Manager: Release 9.2.0.3.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.connected to target database: RMAN4 (DBID=4109780682)connected to recovery catalog database

RMAN> crosscheck archivelog all;


RMAN> backup format '/u01/app/oracle/sandy/proddb/1.arc' archivelog all


allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=18 devtype=DISKchannel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinputarchive log thread=1 sequence=43 recid=3 stamp=629043047input archive log thread=1 sequence=44 recid=4 stamp=629047148input archive log thread=1 sequence=45 recid=5 stamp=629051293input archive log thread=1 sequence=46 recid=6 stamp=629055443input archive log thread=1 sequence=47 recid=7 stamp=629059581input archive log thread=1 sequence=48 recid=8 stamp=629063680input archive log thread=1 sequence=49 recid=9 stamp=629067845input archive log thread=1 sequence=50 recid=10 stamp=629071975input archive log thread=1 sequence=51 recid=11 stamp=629076089input archive log thread=1 sequence=52 recid=12 stamp=629080236input archive log thread=1 sequence=53 recid=13 stamp=629084368input archive log thread=1 sequence=54 recid=14 stamp=629088490input archive log thread=1 sequence=55 recid=15 stamp=629092638input archive log thread=1 sequence=56 recid=16 stamp=629096758input archive log thread=1 sequence=57 recid=17 stamp=629100873


The above command "crosscheck archivelog all;" will actually delete the archivelog entry from controlfile. Thus Catalog will not try backingup the archivelog which is not present(or deleted)

ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled

When you clone a Instance from a DB which is non rac but a clone of RAC

Eg:

PROD--RAC
DEV-Non Rac cloned from PROD

No You want to clone DEV to TEST.

After restoring the DB from the backup of DEV when you issue the below command

SQL>Alter database open resetlogs;

*ERROR at line 1:ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled


This is because of the RAC component which is still valid in Source.

Solution:

1) Solution:-Edit the init.ora file and put the parameter “_no_recovery_through_resetlogs”.Startup mount the instance.SQL>startup mount.SQL>alter database open resetlogs;


2)On the Source (DEV)
sqlplus / as sysdba

sql>dbms_registry.invalid("RAC");

SQL>exit;

Now take a backup of database and then clone TEST from this backup.



Thanks,
Sandarsh

When to rebuild an Index .

We will follow the below procedure to find which index to rebuild.

analyze index INDEXMANE validate structure;

Once it is complete run the below three queries

Select del_lf_rows / lf_rows from index_stats;
Select HEIGHT from index_stats;
Select LF_ROWS, LF_BLKS from index_stats;

Now an index should be rebuilt if
1) percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
2) If the ‘HEIGHT’ is greater than 4.
3) If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

For EXample

1)

INDEX_NAME: WF_ITEM_ATTRIBUTE_VALUES_PK
Size in MB:2000m
Del%(EL_LF_ROWS/LF_ROWS)%:17
HEIGHT: 4
LF_ROWS:31399384
LF_BLKS:265071
REBUILD: NO

2)

INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:0
HEIGHT: 4
LF_ROWS:57220
LF_BLKS:164476
REBUILD: YES

As LF_BLKS: is considerably higher thn LF_ROWS condition number 3.

Eg 3:

INDEX_NAME: CCIX_PLSQL_LOG_N1
Size in MB:1500
Del%(EL_LF_ROWS/LF_ROWS)%:35
HEIGHT: 4
LF_ROWS:17674676
LF_BLKS:154250
REBUILD: YES


Del%(EL_LF_ROWS/LF_ROWS)% > 30 condition 1.

Thanks,
Sandarsh