Some day to day tips for Oracle Apps DBA

LOCKED SESSIONS

Locked Objects

---------------------------------------------------

select

c.owner,

c.object_name,

c.object_type,

b.sid,

b.serial#,

b.status,

b.osuser,

b.machine

from

gv$locked_object a ,

gv$session b,

dba_objects c

where

b.sid = a.session_id

and

a.object_id = c.object_id;

Get OS PID from Sid

-----------------------------------------------------

Select p.spid

From v$session s, v$process p

Where s.paddr = p.addr

And s.sid = &sid;

Now remove lock

------------------------------------------------------

alter system kill session '&sid,&serial';

select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'M01827A' and p.addr (+) = s.paddr;

alter system kill session '&sid,&serial' immediate;

or kill -9 spid

SQL QUERY RUN BY CONCURRENT REQ

select SQL_TEXT from gv$sql where SQL_ID = (select SQL_ID from gv$session where PADDR = (select ADDR from gv$process where SPID = (SELECT oracle_process_id FROM fnd_concurrent_requests WHERE request_id = '&request_id')));

select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

select SQL_TEXT from v$sql where SQL_ID=(select SQL_ID from v$session where PADDR = (select ADDR from v$process where SPID =&spid)

SQL>select a.sid,a.serial#,c.object_name

from gV$session a,

gV$locked_object b,

user_objects c

where a.sid=b.session_id

and b.object_id=c.object_id;

Output:

SID SERIAL# OBJECT_NAME

---- ------- ------------

7 36 emp

9 58 dept

2 rows selected.

Now you can release the lock:

SQL>alter system kill session '7,36';

System Altered.

SQL>alter system kill session '9,58';

System Altered.

select * from gv$session where paddr in ( select addr from gv$process where spid=9629)

select * from gv$process where spid=9629

select sid from v$session s, v$process p where p.addr=s.paddr and p.spid=9629

select oracle_process_id from fnd_concurrent_requests where request_id=4640901

select USERNAME,STATE, TERMINAL, PROGRAM from v$session where PADDR=(select ADDR from v$process where SPID ='&1');

To generate trace file:

select distinct sid from v$mystat;

SQL> oradebug setospid 11680;

Oracle pid: 164, Unix process pid: 11680, image: oracle@sgi-not-etbed03 (TNS V1-V3)

SQL> oradebug EVENT 10046 trace name context forever, level 12

Statement processed.

SQL> oradebug setname

ORA-00070: command setname is not valid

SQL> oradebug tracefile_name

/u01/db01/11.2.0.4/admin/DB011/udump/db011_ora_11680.trc

SQL> oradebug event 10046 trace name context off

Statement processed.

SQL>

explain plan for "query"

select * from table(dbms_xplan.display);

FORMS COMPLIATION AND DATAGUARD

frmcmp_batch module= userid=apps/ output_file= compile_all=special batch=yes

DataGuard

SELECT MAX(NEXT_CHANGE#)+1 UNTIL_SCN FROM V$LOG_HISTORY LH, V$DATABASE DB

WHERE LH.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND LH.RESETLOGS_TIME =

DB.RESETLOGS_TIME;

alter database recover managed standby database using current logfile disconnect;

recover managed standby database cancel;

alter database open read only;

MEMORY LOCK

Enter ipcs and check the shmid

ipcrm shm

TABLE CONTAINING CORRUPTED BLOCKS

select segment_name,segment_type,owner from sys.dba_extents where file_id=51 and (1508104) between block_id and block_id + blocks -1;

ADPATCH ON EXTERNAL NODE

adpatch options=copyportion,nodatabaseportion,generateportion,nocompiledb

CLONE DATABASE ONLY

perl clone.pl ORACLE_HOME=/oracle/stbydb/11.2.0/clone ORACLE_BASE=/oracle/stbydb ORACLE_HOME_NAME=/oracle/stbydb/11.2.0/clone|-defaultHomeName OSDBA_GROUP=oinstall OSOPER_GROUP=dba OSASM_GROUP=dba

RMAN BACKUP

run{

Allocate channel disk1 type disk format = '/san_backup/rman_backup1/REPPOC/Backup_DRUAT_08JAN10/%U' MAXPIECESIZE 2 G;

Allocate channel disk2 type disk format = '/san_backup/rman_backup1/REPPOC/Backup_DRUAT_08JAN10/%U' MAXPIECESIZE 2 G;

Allocate channel disk3 type disk format = '/san_backup/rman_backup1/REPPOC/Backup_DRUAT_08JAN10/%U' MAXPIECESIZE 2 G;

Allocate channel disk4 type disk format = '/san_backup/rman_backup1/REPPOC/Backup_DRUAT_08JAN10/%U' MAXPIECESIZE 2 G;

Allocate channel disk5 type disk format = '/san_backup/rman_backup1/REPPOC/Backup_DRUAT_08JAN10/%U' MAXPIECESIZE 2 G;

set limit channel disk1 kbytes=2000000;

set limit channel disk2 kbytes=2000000;

set limit channel disk3 kbytes=2000000;

set limit channel disk4 kbytes=2000000;

set limit channel disk5 kbytes=2000000;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/san_backup/rman_backup1/REPPOC/%F';

set command id to 'bkrs';

backup full tag REPPOC_20JUL10_1900 (database include current controlfile);

release channel disk1;

release channel disk2;

release channel disk3;

release channel disk4;

release channel disk5;

}

run {

allocate channel disk1 type disk format='/db_data/backup_fr_dr/DR_dbf_%U_%t' ;

allocate channel disk2 type disk format='/db_data/backup_fr_dr/DR_dbf_%U_%t' ;

allocate channel disk3 type disk format='/db_data/backup_fr_dr/DR_dbf_%U_%t' ;

allocate channel disk4 type disk format='/db_data/backup_fr_dr/DR_dbf_%U_%t' ;

set limit channel disk1 kbytes=2000000;

set limit channel disk2 kbytes=2000000;

set limit channel disk3 kbytes=2000000;

set limit channel disk4 kbytes=2000000;

set command id to 'bkrs';

backup full tag '11g_DR_200710' database plus archivelog;

release channel disk1;

release channel disk2;

release channel disk3;

release channel disk4;

}

CONCURRENT MANAGER ISSUES

/shared/R12/DB01/apps/apps_st/appl/ad/12.0.0/bin/adrelink.sh force=y "fnd FNDLIBR"

/shared/R12/DB01/apps/tech_st/10.1.2/network/lib

make -f ins_net_server.mk

make -f ins_net_client.mk

FREE RAW DISKS

dd if=/dev/zero of=/dev/raw/raw1 bs=512 count=1

PATCHING USING DEFAULTS FILE

Here Patch number is - 7715864

adadmin set maintenance mode

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaultsfile \

logfile=adadmin.log \

menu_option=ENABLE_MAINT_MODE

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaultsfile \

logfile=adpatch_u${PATCH_NUMBER}.log \

patchtop=/work/patches/DB28/${PATCH_NUMBER} \

driver=u${PATCH_NUMBER}.drv \

workers=16

adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaultsfile \

logfile=adadmin.log \

menu_option=DISABLE_MAINT_MODE

DISABLE DYNAMIC TRIGGERS IN ORACLE EBIZ

You can disable the trigger from the application as well as from the back end.

1) Please do it in the Test Instance first.
Application :
1) Go to HRMS Responsibility.
2) Navigate to Other Definitions -> Dynamic Triggers.
3) Query the Trigger 'HR_PAY_IF_ELE_ENT_BRD' with the Application as 'Payroll' and select all
the Trigger Actions.
4) Now disable the Enabled Check box.
5) Save the action.

From the Backend:
1) Go to Apps Schema.
$ sqlplus apps/apps
SQL> alter trigger HR_PAY_IF_ELE_ENT_BRD disable;
SQL> exit;
2) Check the Status of the Trigger(Enabled/Disabled).
select * from all_triggers where trigger_name like 'HR_PAY_IF_ELE_ENT_BRD';

LIBRARY CACHE LOCK

select SADDR,SID,PADDR,USERNAME from gv$session where upper(event) like '%LIBRARY%';

select saddr from v$session where sid in (select sid from v$session_wait where event like 'library cache lock');

BLOCKER SESSION

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM GV$SESSION WHERE SADDR in (SELECT KGLLKSES FROM X$KGLLK LOCK_A WHERE KGLLKREQ = 0 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B WHERE KGLLKSES = 'saddr_from_v$session above' /* BLOCKED SESSION */ AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL AND KGLLKREQ > 0));

Please follow the following steps to change the HTML Agent for wf Mailer when necessary

1. Change the profile option WF: Workflow Mailer Framework Web Agent and change the node information

2. Change the HTML Agent in wf mailer edit -> advanced

3. Find the tablespace where indexes on corrid for queue tables are created

4. select distinct tablespace_name
from dba_indexes,dba_queues
where index_name like 'WF%N1'
and table_name=queue_table
and name like 'WF%';

5. Rebuild mailer's queue

6. sqlplus apps/ and run

7. $FND_TOP/patch/115/sql/wfntfqup

8. Recreate Index on corrid column :

9. sqlplus applsys/

10. CREATE INDEX WF_NOTIFICATION_OUT_N1
ON WF_NOTIFICATION_OUT(CORRID)
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
TABLESPACE &tbs; where &tbs is the output from step 3

11. Restart the wf Agent Listeners and mailers

12. If the mailers do not start giving error like this :

The Service Component Container is not running:

1. Sitemap > Generic Services > Status Overview > View All > Generic Service Component Container > select Workflow Agent Listener Service > Start

In the same page select Workflow Mailer Service > Start

Now the components should start according with the start mode that you have set:

- Automatic
- On-Demand
- Manual

Create / Repair Inventory for RDBMS_HOME

cd $ORACLE_HOME/oui/bin

./runInstaller -silent -detachHome -invPtrLoc ./oraInst.loc

ORACLE_HOME="" ORACLE_HOME_NAME=""

./runInstaller -silent -ignoreSysPrereqs -attachHome -noClusterEnabled ORACLE_HOME="/u01/db01/11.2.0" ORACLE_HOME_NAME="DB01_Home" CLUSTER_NODES="xxxxxx" LOCAL_NODE="xxxxx"

Compile jsp files

perl -x /shared/R12/DB01/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/ojspCompile.pl --compile –quiet

EXPORT IMPORT STATISTICS

exec dbms_stats.create_stat_table ( ownname => user , stattab => 'temp_stat' ) ;

begin

DBMS_STATS.EXPORT_TABLE_STATS (

ownname => 'HR',

tabname => 'PER_PERSON_TYPE_USAGES_F',

stattab => 'stat_table',

statid => 'imp1',

cascade => true);

end;

begin

DBMS_STATS.IMPORT_TABLE_STATS (

ownname => 'HR',

tabname => 'PER_PERSON_TYPE_USAGES_F',

stattab => 'stat_table',

statid => 'imp1',

cascade => true);

end;

begin

DBMS_STATS.EXPORT_TABLE_STATS (

ownname => 'HR',

tabname => 'PER_PERFORMANCE_REVIEWS',

stattab => 'stat_table',

statid => 'imp2',

cascade => true);

end;

begin

DBMS_STATS.IMPORT_TABLE_STATS (

ownname => 'HR',

tabname => 'PER_PERFORMANCE_REVIEWS',

stattab => 'stat_table',

statid => 'imp2',

cascade => true);

end;

exec dbms_monitor.session_trace_enable(2176,52212, waits=> TRUE, binds=> TRUE);

Oracle Applications DBA

Contact Details

About Me