Wednesday, May 9, 2012

Patch & their Sessions Monitor And All

Patching
========

select patch_level from fnd_product_installations where patch_level like '%AD%';
select bug_number,creation_date from ad_bugs where bug_number ='&a';
select PATCH_RUN_BUG_ID,BUG_ID,LAST_UPDATE_DATE,ORIG_BUG_NUMBER from AD_PATCH_RUN_BUGS where BUG_ID='&a';

select apd.driver_file_name, apd.DRIVER_TYPE_C_FLAG "C", apd.DRIVER_TYPE_D_FLAG "D", apd.DRIVER_TYPE_G_FLAG "G" ,
       apdl.language from ad_patch_drivers apd, ad_patch_driver_langs apdl where apd.patch_driver_id=apdl.patch_driver_id and
       apd.driver_file_name like '%4638432%';

select decode(status,'P', 'processed', 'U','unprocessed'),status ,count(*)from ad_parallel_update_units where update_id in
       (select update_id  from  ad_parallel_updates where script_name like '%GM%'  ) group by status;      

Blocks and Waits
===================
select session_id from dba_locks where blocking_others='Blocking';
select sid,event,seconds_in_wait from v$session_Wait where sid in (select sid from v$session where username='SYS');
select sid,decode(block,0,'NO','YES') BLOCKER,decode(request,0,'NO','YES') WAITER from v$lock where request > 0 or block > 0 order by block desc;
select * from v$lock where block>0;
select session_id,object_name, status, locked_mode from v$locked_object l,dba_objects o where l.object_id=o.object_id and object_name = 'CM_CMPT_DTL';
      
Sessions
=========
select s.sid,s.serial#,status,s.username,s.osuser from v$session s, v$process p where s.paddr=p.addr and p.SPID in (7062,25489,28583) order by status;
select 'alter system kill session '''|| sid||','||serial#||''';' from gv$session where status='INACTIVE'
select a.sid, a.serial#,b.sql_text from v$session a, v$sqlarea b where a.sql_address=b.address;
select SOFAR,TOTALWORK from v$session_longops where sid='&SID';

Active sessions from a particular machine for a particular database user
======================================
select sid,SERIAL#,server,MACHINE,TERMINAL from v$session where username= 'SCOTT';
ALTER SYSTEM KILL SESSION '1879,17' IMMEDIATE;
SELECT sid,serial# FROM v$session WHERE audsid = userenv('1131');
SELECT userenv('1051') FROM dual;

Patch restart process in case of a hang
----------------------------------------------------------------------
create table fnd_install_processes_bkp as select * from fnd_install_processes;
create table ad_deferred_jobs_bkp as select * from ad_deferred_jobs;
select WORKER_ID,CONTROL_CODE,STATUS from fnd_install_processes;
update fnd_install_processes set STATUS='F' where WORKER_ID>0 and STATUS='R';


ad_task_timing
------------

SELECT * FROM ad_task_timing WHERE start_time > SYSDATE - 10;

SELECT t.*,
to_char(t.start_time,'dd/mm/yy hh24:mi:yy'),
to_char(t.restart_time,'dd/mm/yy hh24:mi:yy'),
to_char(t.end_time,'dd/mm/yy hh24:mi:yy')
FROM ad_task_timing t
WHERE t.start_time > SYSDATE - 10;


select decode(a.patch_name,'merged',d.bug_number,a.patch_name) Patch_Number
      ,b.patch_driver_id,b.last_update_date,
      nvl(b.patch_abstract,c.patch_abstract)
from ad_applied_patches a,
     ad_patch_drivers b,
     ad_comprising_patches c,
     ad_bugs d
where a.creation_date > (sysdate-120)
and a.applied_patch_id=b.applied_patch_id
and b.patch_driver_id = c.patch_driver_id (+)
and c.bug_id = d.bug_id (+)
order by 3 desc;

Object Backups

View backup
==========
Select dbms_metadata.get_ddl(‘’,’’,’OWNER’) from dual;

set long 100000
spool MTL_SYSTEM_ITEMS_VL.lst
select text from dba_views where VIEW_NAME='MTL_SYSTEM_ITEMS_VL';
spool off

Package Body Backup
===================
set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool Mwac_Warranty_sn_Pk.bkp   
select text from dba_source where name='MWAC_WARRANTY_SN_PK'; and type='';
spool off
exit

Spool in excel format
==========================

SET LINESIZE 4000
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 999
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SPOOL XazyerS.xls
select NAME,VALUE from gv$parameter;

spool off
exit

DB Queries

General
========
sele¢t to-¢Har(sffisdate, 'DD mON, YYYY HH24:MI:SS') From DUAL
select HOME_URL from icx_parameters;
select release_name from fnd_product_groups;
select nls_language,language_code,installed_flag from apps.fnd_languages where installed_flag in ('I','B');        
select NODE_NAME,SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN,STATUS from apps.fnd_nodes;
select name,open_mode,created,user from v$database;
select count(*) from dba_objects where status='INVALID';
select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;
select * FROM v$active_instances;
select DBID,NAME from gv$database;
Select text from dba_errors where name =’' and owner =’’;
Select referenced_owner, referenced_name,referenced_type from dba_dependencies where name=’’ and type =‘’ and owner =’’;
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
select to_char(current_timestamp,'MM/DD/YYYY HH24:MI:SS TZD'),to_char(systimestamp, 'MM/DD/YYYY HH24:MI:SS TZD') from dual;

Alter and Compile
==================
@?/rdbms/admin/utlrp

alter package FUN_VENDOR_PVT_PKG COMPILE SPECIFICATION;
alter package FUN_VENDOR_PVT_PKG compile body;
alter procedure compile;
alter view view_name compile;
alter materialized view view_name compile;

Alter session set events ‘10046 trace name context forever,level 12’;

Disable/Enable Direct Forms Launch

 Disable:
java oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION OFF DBC=host_.dbc

Enable:
To activate basic server security, from the command line, enter:

java oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION ON DBC= host_.dbc

Db links -- 11gR2

 No need to drop and re-create the DB link -- Cool


11gR2-- The ALTER DATABASE LINK statement is used to update database links with the
current passwords of connection and authentication users.

ALTER PUBLIC DATABASE LINK public_link
  CONNECT TO scott IDENTIFIED BY scott_new_password;