Monthly Archives: November 2013

How To Clear Cache – R12

Login to Oracle Applications as sysadmin

Navigate to Functional Administrator responsibility

Core Services
Caching Framework
Global Configuration
Clear All cache

clear-middle-tier-cache1

cache2

When above warning message is shown,click “Yes” button

Thanks !!

TRACE IN ORACLE DATABASE

Current session trace:

NORMAL TRACE:

alter session set tracefile_identifer=’XXX’;   — trace identifier
alter session set sql_trace = true;

FULL LEVEL TARCE with WAIT and BIND :

alter session set tracefile_identifer=’XXX’;   — trace identifier
alter session set events = ‘10046 trace name context forever, level 12’;

Other running session trace:

NORMAL TRACE:

execute dbms_system.set_sql_trace_in_session ($sid,$serial,true);

FULL LEVEL TARCE with WAIT and BIND :

execute dbms_system.set_ev($sid,$serial,10046,12,’’);

TRACE FILE LOCATION:

<DIAGNOSTIC_DEST>/diag/rdbms/<SID>/trace

TKPROF :
tkprof utility can translate the trace file into a more human readable format

tkprof <tracefile> <outfile> explain=username/password sort ='(prsela,exeela,fchela)’

explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
prsela  elapsed time parsing
exeela  elapsed time executing
fchela  elapsed time fetching

RMAN : Perform Block Recovery

We can perform BLOCK RECOVERY with or without RMAN BACKUP, Here is the demonstration of block media recovery with HOT BACKUP and RMAN BACKUP as well.

1)  Create tablespace, user and table in same schema.
2)  Take the backup of datafile.
a) HOT BACKUP.
b) RMAN BACKUP.
3)  Corrupt the data in datafile.
4)  Connect with RMAN and try to use BLOCKRECOVER command.
a) Perform Block Recovery with HOTBACKUP.
b) Perform Block Recovery with RMAN BACKUP.

Here you go…

Create tablespace, user and table in same schema:

$sql> create tablespace TS1 datafile ‘/d01/oradata/ts1.dbf’ size 100m;
$Create user U1 identified by U1 default tablespace TS1;
$sql> grant connect,resource to U1;
$sql> conn U1/U1
$sql> create table TEST_CORRUPT (no number);
$sql> insert into TEST_CORRUPT values(1);
$sql> coomit;
$sql> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b WHERE a.header_file=b.file# AND a.segment_name=’TEST_CORRUPT’;
SEGMENT_NAME     TABLESPACE_NAME    NAME
—————                      —————                           ————–
TEST_CORRUPT         TS1                                     /d01/oradata/ts1.dbf

HOTBACKUP of datafile:

$sql> ALTER TABLESPACE TS1 BEGIN BACKUP;
[oracle@localhost]$cp /d01/oradata/ts1.dbf /d01/oradata/ts1_bkp.dbf
$sql> ALTER TABLESPACE TS1 END BACKUP;

RMAN BACKUP of datafile 4:

[oracle@localhost]$rman target /
RMAN> backup datafile 4;

Corrupt the data in datafile with dd command:

Make sure that dd command given below is just for learning purposes and should only be used on testing systems

$sql>select segment_name , header_file , header_block  from dba_segments where segment_name = ‘TEST_CORRUPT’
and owner = ‘U1’;
SEGMENT_NAME         HEADER_FILE   HEADER_BLOCK
—————————- ———–                ————
TEST_CORRUPT                      4                         16

[oracle@localhost]$cd /d01/oradata
[oracle@localhost]$dd of=ts1.dbf bs=8192 conv=notrunc seek=17 << EOF
> Bingo! Corrupted.
> EOF
0+1 records in
0+1 records out
18 bytes (18 B) copied, 0.000684 seconds, 27.0 kB/s

This command successfully executed block 17 in the data file “/d01/oradata/ts1.dbf” is corrupt

Check the data block curroption:

$sql> sqlplus / as sysdba
$sql> alter system flush buffer_cache;
$sql> conn u1/u1
$sql> select count(*) from TEST_CORRUPT;
select count(*) from test_corrupt
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 17)
ORA-01110: data file 4: ‘/d01/oradata/ts1.dbf’

Please perform below any one of the method, Here I am showing two methods with or without RMAN Backup.

Performing Block Recovery without having RMAN BACKUP:

As we have HOTBACKUP for datafile ts1.dbf, Catalog the “hot backup” to the RMAN repository

[oracle@localhost]$rman target /
RMAN> CATALOG DATAFILECOPY ‘/d01/oradata/ts1_bkp.dbf’;
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 17;

Performing Block Recovery with RMAN BACKUP:

As we have datafile 4 RMAN BACKUP, Perform recovey…

[oracle@localhost]$rman target /
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 17;

BLOCK MEDIA RECOVERY Complete we should now get the block 17 recovered back:

[oracle@localhost]$ sqlplus U1/U1
$sql> select count(*) from TEST_CORRUPT;
COUNT(*)
———-
1
$SQL> exit

HAPPY LEARNING !!

Enable/Disable Maintenanace Mode from SQLPLUS Session (Without adadmin)

We can enable or disable maintenance mode without using adutility (adadmin), Perform the same from SQLPLUS session.

Check the current status of Maintenance Mode:

select fnd_profile.value(‘APPS_MAINTENANCE_MODE’) from dual;

MAINT     =  MAINTENANCE MODE is ENABLED
NORMAL =  MAINTENANCE MODE is DISABLED

ENABLE maintenance mode:
sqlplus -s apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

DISABLE maintenance mode:
sqlplus -s apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

The system has not been taken off maintenance mode completely.Please contact your system adminstration.

The system has not been taken off maintenance mode completely.Please contact your system adminstration.

Maintenance_mode

Cause: After patch application, Maintenance mode is not disabled.

Solution :

1) login to applmgr user

2) run adadmin

3) use option 5 to disable maintenance mode

AD Administration Main Menu
————————————————–
1.    Generate Applications Files menu
2.    Maintain Applications Files menu
3.    Compile/Reload Applications Database Entities menu
4.    Maintain Applications Database Entities menu
5.    Change Maintenance Mode
6.    Exit AD Administration

Enter your choice [6] : 5
Please select an option:
1.    Enable Maintenance Mode
2.    Disable Maintenance Mode
3.    Return to Main Menu

Enter your choice [3] : 2

sqlplus -s &un_apps/***** @/apps/vision/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql DISABLE
Successfully disabled Maintenance Mode.

Concurrent Manager Issue : FND_DCP.REQUEST_SESSION_LOCK / DBMS_LOCK.Request

Error in Concurrent manager logfile :
=========================

Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1 from the call to DBMS_LOCK.Request.
Possible DBMS_LOCK.Request resultCall to establish_icm failed
The Internal Concurrent Manager has encountered an error.

Resolve the issue in two ways:

Solution 1 (Bounce database):
1. Stop all the services and make sure that all the concurrent managers completely down.
2. Stop and restart the database
3. Restart all the services and concurrent managers

Solution 2 (Without database bounce):

1. Stop the concurrent managers cleanly.
2. Check for the database locks and kill the locks related to CM.
3. Run cmlean.sql with apps user.
4. Start the concurrent managers.

Check for CM Locks:

SQL> SELECT v$access.sid, v$session.serial#
FROM v$session,v$access
WHERE v$access.sid = v$session.sid and v$access.object = ‘FND_CP_FNDSM’
GROUP BY v$access.sid, v$session.serial#;

SID          SERIAL#
———- ———-
2000         1000

sql> alter system kill session ‘2000,1000’;

DBMS_LOCK.Request

FNDFS Error- unable to view Concurrent program LOG and OUT files

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_APPLTOP_ . There may be a network configuration problem, or the TNS listener on node FNDFS_APPLTOP_ may not be running.Please contact your system administrator.

Problem : In R12, After running a Concurrent request, Unable to view LOG and OUT files of the Concurrent request and returns above error message.

Cause : The RRA is Not Enabled in the Profile Option.

Solution :
In the Profile Option –>system –> RRA ->RRA: Enabled is set as No in Site Level. Make it to Yes.
Save and Logout and Retest the Issue.