Monday, June 11, 2012

Accounting setup manager main page function is not available under GL

Under a GL Super user Responsibility, when users access the self service pages like Accounting setup Manager, they receive an error :

Function not available under this responsibility.

Fix:

Run " Compile Security" program with Option :Yes.
Bounce Apache.

Issue fixed.

Sunday, June 3, 2012

Unable to authenticate session

When trying to access Login Page of an R12 instance,We got the following error in the browser (firefox).

Unable to authenticate session

We had to close all the browser windows and open a new browser window, after which we got the login page.

In case, If it is after a patch and disabling maintenance mode, oacore bounce might help.

 

PCP/RAC --Run a program on a Specific RAC Instance

In 12.1 and later,

Other than the Target Node for a concurrent program,
We can specify the Target Instance on which a praticular program will be running.

To specify a Target Instance,
System Administrator Responsibility > Concurrent > Program > Define > Click 'Session Control' 

==>
If an instance is specified for a program and the instance is up, a manager picks up the request and runs the program on this instance.
ie. though Concurrent Manager is connected to a different instance,  the request will be run on a specific instance.

==>
If no instance is specified, then a manager picks up the request and runs the program on the instance to which the manager is already connected.

Parallel Concurrent Processing

Important points regarding PCP.
========================

1)The terms and services PCP and GSM go together.That is to say, if PCP is set up in a configuration it means that GSM is enabled.

PCP can't be activated independent of GSM.

2)ICM can run on any node , it can activate and de-activate managers on all nodes.

3)ICM should be active all the time, TO ensure this, we have Internal Monitor which monitors ICM and restarts it , in case of a failure.

4)One Internal Monitor manager should be defined for each node.

5)Internal Monitor Processes are activated and deactivated by the Internal Concurrent Manager.

6)Primary node for ICM can be defined. This is the node ICM prefers to run if node is available ( any comments?)

7)Primary and secondary node names for all other concurrent managers can be defined.
However, If no primary node is assigned, ICM assigns a valid concurrent processing server node as the target node.

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;