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

RMAN restore from a particular Days TAPE

Hi,

We got a scenario where we were required to restore database from a particular tape. Client asked us to find out the tape which should be used for that restore. We faced lots of issues in getting those tapes in place.

1) We were trying point in time recovery till 1 PM for restoring(time was not a constrain)
2)Restore command was not able to find the required backup and as it was 10g it failed after trying to access all the backups taken before that time.
3)Customer complained that he did not find any RMAN request was not coming to TAPE device.
4)We wasted about 8 hours.

Below are the couple of things we learnt during this activity.

1) RMAN always tries to restore from the backup taken prior to the time until which we try to restore

For Example:

suppose Until time is set till 13:00:00
Daily backup happens at 5 PM
And we are required to restore say till 29:07:07

In this scenario rman will restore from backup taken on 28th and apply all the archives generated after the backup till 29:07:07 13:00:00.
So the we must mount the tape of 28th .

Client wanted us to restore from tape containing the 29th backup. In this case you cannot use until time before 5:30Pm (Backup completion time)

Also one more thing we need to keep in mind is RMAN Always restores from the prevoius backup, it first tries to restore from 28th backup , if it is not there it sarches for 27th backup (and apply all the archive log till 29) so on..

The above feature is only present in 10G and later releases.

This is how we need to find out the backup and time for till which we can restore so that we remain within the available options.

Assuming that we have a CATALOG. Use the below select statements and extract the time from the catalog.

select to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS') from RC_BACKUP_DATAFILE where trunc(completion_time) >= trunc(sysdate-9);

.
. TO_CHAR(COMPLETION_
-------------------
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:47:50
06/29/2007 16:45:09
06/29/2007 16:28:10
06/29/2007 15:18:42
06/29/2007 14:29:03
06/29/2007 14:19:37
06/29/2007 14:45:31
06/29/2007 14:48:36
06/29/2007 15:02:08

TO_CHAR(COMPLETION_
-------------------
06/29/2007 15:04:45
06/29/2007 15:17:04
06/29/2007 15:19:37
06/29/2007 15:21:37
06/29/2007 15:20:57
06/29/2007 15:20:19
06/29/2007 15:20:51
06/29/2007 15:21:20
06/29/2007 15:21:27
06/29/2007 15:21:35
06/29/2007 15:21:28


This Confirms the backup completion time. We can select any time after 15:21:28 which will confirm that the rman will restore from 29th backup.

Now till what time to recover , Since it is a Tape , if we give the restore time as 15:21:28 ,Then Rman will restore from the proper backup but DB open will fail saying some DBf's need more recovery. In such case you need to restore all the archive logs and apply them (or recover using RMAN) which will take plenty of time.

Its better we give a proper time for restore.

Use the below query.
select DB_NAME,to_char(COMPLETION_TIME,'MM/DD/YYYY HH24:MI:SS'),THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from rc_backup_redolog where trunc(completion_time) >= trunc(sysdate-9) and DB_NAME='PROD' order by 3,4,2;

.
.
PROD 06/29/2007 17:05:02 1 22239 2445236995 2445476389

DB_NAME TO_CHAR(COMPLETION_ THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
-------- ------------------- ---------- ---------- ------------- ------------
PROD 06/29/2007 17:05:02 1 22240 2445476389 2445739485
PROD 06/29/2007 17:05:02 1 22241 2445739485 2446147163
PROD 06/29/2007 17:05:02 1 22242 2446147163 2446554642
PROD 06/29/2007 17:05:02 1 22243 2446554642 2446899303
PROD 06/29/2007 17:05:02 1 22244 2446899303 2446936172
PROD 06/29/2007 17:05:02 1 22245 2446936172 2447040692
PROD 06/29/2007 17:05:02 1 22246 2447040692 2447308086
PROD 06/29/2007 17:05:02 1 22247 2447308086 2447610092
PROD 06/29/2007 17:05:02 1 22248 2447610092 2447611059
PROD 06/30/2007 16:56:09 1 22249 2447611059 2447876345
PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725


This is the archive log which is in the TAPE "PROD 06/30/2007 16:56:09 1 22250 2447876345 2448128725"

Now we can use the time "06/30/2007 16:56:09 " as the candidate for until time.
But good practice is to use until scn 2448128725 which is very accurate OR time when archive with sequence 22250 was created.

Now if we use "06/30/2007 16:56:09 " as until time then RMAN thinks that it must recover till "06/30/2007 16:56:09 " but this time is actually the archivelog backup completion time.
This archive must be generated before '06/30/2007 16:56:09 ".

The RMAN recovery will fail saying that it could not find archivelog 22251 .

In the above case do the below steps.

on the DB where rman restore failed

Connect as RMAN

rman target /

recover database until cancel

.
.

Just enter Cancel.

then open the database using restelogs (if required use "alter database open resetlogs using backup controlfile"


The above scenario will not occur if we use the until SCN or "Archive log ceration time"

Also in 10G as told earlier the RMAN will will fail over to previous backup if it does not find the exact backup.
eg :

it must restore from 29Th backup. If that tape is not mounted then it will try to restore from 28th backup . If 28th backup is available then it will restore the DBF's but recover will fail . It will now search for archive logs generated on 28'th after backup till "'06/30/2007 16:56:09" .
so some archives are in 28th tape and rest in 29th tape.

To avoid this make sure the RMAN restores from proper backup set.
We will use the above time for restore. Also list backup of database gives the information about the backup set which should be used for restore

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
136394 Incr 0 191G SBT_TAPE 03:18:06 29-JUN-07
BP Key: 136397 Status: AVAILABLE Compressed: NO Tag: TAG20070629T133039
Handle: bk_u6lilgak0_s2261_p1_t626535040 Media:
List of Datafiles in backup set 136394
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/system01.dbf
2 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/undotbs01.dbf
3 0 Incr 2447390136 29-JUN-07 /u02/oradata/PROD/sysaux01.dbf

Rman must restore from bk_u6lilgak0_s2261_p1_t626535040 which is the first backupset created on 29th . This is present in the restore logfile. Make sure that RMAN does not fail over to previous days backup if so our untilTime should be the Culprit Or TAPE is not the correct one ot it May not be mounted properly.

Thanks,
Sandarsh.