Sujeet APPS DBA: DBA Scripts

-- INSTANCE RELATED QURIES --

select instance_name,instance_number,status from v$instance;

select banner from v$version;

desc fnd_product_groups

select release_name,last_update_date from fnd_product_groups;

select * from v$parameter

select name from v$parameter where name like '%out%'

-- PATCHING RELATED --

select * from dba_objects;

select count(*) from dba_objects where status ='INVALID';

select * from dba_objects where status='INVALID'

select count(*) from dba_objects where owner = 'APPS' and status ='INVALID';

select * from dba_objects where status='INVALID';

select bug_number,last_update_date from ad_bugs where bug_number='13374062'

SQL> select bug_number,last_update_date from ad_bugs where bug_number='13006289';

BUG_NUMBER LAST_UPDATE_DATE

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

13006289 05-MAR-14

select patch_name, last_update_date from ad_applied_patches where patch_name='13374062'

select release_name,last_update_date,creation_date from fnd_product_groups

select bug_number,last_update_date from ad_bugs where trunc(LAST_UPDATE_DATE) >= '13-JUN-2011'

select PATCH_NAME,last_update_date from ad_applied_patches where trunc(LAST_UPDATE_DATE) >= '08-JUN-2011'

-- USER ACCOUNT RELATED --

select * from dba_users where username='APPS';

select username,account_status from dba_users where username='RAC_ACCNT';

select user_id,user_name,session_number from fnd_user where user_name=''

select username,password,account_status,lock_date from dba_users where username='APPS'

-- TABLE SPACES and GRANTS Related --

select * from dba_tablespaces

select * from dba_temp_files

select tablespace_name from dba_tablespaces where tablespace_name='READONLY';

select username,DEFAULT_TABLESPACE from dba_users where username='ROAPPS'

revoke select on HR.AME_ACTION_TYPES_TL from ROAPPS

-- DATABASE LOCKS --

select s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

no rows selected

desc dba_locks

SQL> desc dba_locks

Name Null? Type

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

SESSION_ID NUMBER

LOCK_TYPE VARCHAR2(26)

MODE_HELD VARCHAR2(40)

MODE_REQUESTED VARCHAR2(40)

LOCK_ID1 VARCHAR2(40)

LOCK_ID2 VARCHAR2(40)

LAST_CONVERT NUMBER

BLOCKING_OTHERS VARCHAR2(40)

desc v$locked_objects ;

desc dba_blockers

select holding_session from dba_blockers

select session_id,lock_type from dba_locks

select * from dba_ddl_locks

select * from v$lock where block<>0;

select * from v$session where sid =820;

select * from V$sqltext where hash_value='3533547372' order by piece

--For checking what SQL, CM program is executing--

select * from fnd_concurrent_requests where request_id='639447';

--we need to take oracle_process_id from above , then SPID is oracle process id from the above query

select * from v$process where spid='15229';

--Take paddr value from the above query--

select * from v$session where paddr='00000000DC48C2D0';

--Take SQL_HASH_VALUE from the above query--

select * from V$sqltext where hash_value='2556871103' order by piece;

-- COMPILE APPS SCHEMA --

EXEC DBMS_UTILITY.compile_schema(schema => 'APPS');

-- APP Users Responsibilities --

Select fu.user_id

,fu.user_name

,frt.RESPONSIBILITY_NAME

,fur.start_date Start_date

,fur.end_date End_Date

From fnd_user fu

,apps.FND_USER_RESP_GROUPS_ALL fur

,FND_RESPONSIBILITY_TL frt

Where fu.user_name= 'VIKRAM_MEHTA'

and fu.user_id=fur.user_id

and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID

-- GATHER SCHEMA STATISTICS on TABLES --

exec dbms_stats.gather_table_stats(SYS, 'GL_JE_LINES', cascade => TRUE);

exec sys.dbms_stats.gather_table_stats(GL, 'GL.GL_JE_LINES', cascade => TRUE);

exec sys.dbms_stats.gather_table_stats('GL','GL_JE_LINES',ESTIMATE_PERCENT=>100,CASCADE=>True);

analyze table gl.gl_je_lines estimate statistics sample 100 percent for table for all indexed columns for all indexes

analyze table gl.gl_balances estimate statistics sample 100 percent for table for all indexed columns for all indexes

select table_name,column_name,num_distinct,last_analyzed,num_buckets from user_tab_cols where table_name like ('%GL_JE_LINES%');

select table_name,index_name,num_rows,last_analyzed from user_indexes where table_name like ('%GL_JE_LINES%');

-- TEMPLATES --

select * from xdo_lobs where LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'XXWINCEADR%'

select * from xdo_lobs where 1=1-- LOB_TYPE like 'TEMPLATE_SOURCE'

and LOB_CODE like 'PO_STANDARD_PO%'

-- OTHERS --

select * from dba_ddl_locks

where name like 'XXWIN_HCM%'

select * from v$lock

select * from v$session_wait

select * from fnd_concurrent_requests

select request_id, request_date,logfile_name from fnd_concurrent_requests where request_id='744042'

select * from fnd_nodes

--To see employee name for the persons column (To add employee to the user)--

select * from per_all_people_f where FULL_NAME like '%vic%'

select * from apps.fnd_lookup_values where lookup_type='XXWIN_SFTP_DETAILS'

select * from v$recover_file

---- To SEE what parameters assigned for CUSTOM CONCURRENT PROGRAMS----

select * from FND_DESCR_FLEX_COL_USAGE_VL

where END_USER_COLUMN_NAME = 'p_debug_flag'

select * from fnd_concurrent_programs where concurrent_program_id=55455

select * from xxwin_datafile_validation_tab where data_file_name like 'UKJNL%'

----Table Space Query -----

SELECT

d.status status

, d.tablespace_name name

, d.contents type

, d.extent_management extent_mgt

, d.segment_space_management segment_mgt

, NVL(a.bytes, 0) ts_size

, NVL(a.bytes - NVL(f.bytes, 0), 0) used

-- , NVL(f.bytes, 0) free

, NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used

FROM

sys.dba_tablespaces d

, ( select tablespace_name, sum(bytes) bytes

from dba_data_files

group by tablespace_name

) a

, ( select tablespace_name, sum(bytes) bytes

from dba_free_space

group by tablespace_name

) f

WHERE

d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = f.tablespace_name(+)

AND NOT (

d.extent_management like 'LOCAL'

AND

d.contents like 'TEMPORARY'

)

and NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)>70

UNION ALL

SELECT

d.status status

, d.tablespace_name name

, d.contents type

, d.extent_management extent_mgt

, d.segment_space_management segment_mgt

, NVL(a.bytes, 0) ts_size

, NVL(t.bytes, 0) used

, NVL(t.bytes / a.bytes * 100, 0) pct_used

FROM

sys.dba_tablespaces d

, ( select tablespace_name, sum(bytes) bytes

from dba_temp_files

group by tablespace_name

) a

, ( select tablespace_name, sum(bytes_cached) bytes

from v$temp_extent_pool

group by tablespace_name

) t

WHERE

d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management like 'LOCAL'

AND d.contents like 'TEMPORARY'

AND NVL(t.bytes / a.bytes * 100, 0)>70

select * from dba_temp_files

Checks to be performed while Concurrent program is running.

1) You need to first get the oracle session id by running the below sql with appropriate request_id.

select * from fnd_concurrent_requests where request_id='656309'

2) Oracle session id will be the input to the below query to find out the sql_id and SID.

select * from gv$session where AUDSID='2659367'

3) To find out the latest sql running , you need to input sql_id from the above query into the below query.

select * from gv$sqltext where sql_id='3sxmjmxtz9mt8' order by piece

4) Once the records in the table XXWIN_HCM_EMP_MASTER_STG_TAB is populated completely ( total records should be 196601) it will start writing into the file. You can get this confirmed by running the query in step 3 and the output should be below which means it writing to the file.

SELECT * FROM XXWIN_HCM_EMP_MASTER_STG_TAB WHERE EXTRACT_ID = :B

AND REQUEST_ID=:B1

5) To check the number of records processed, you need to run the below query.

select count(*) from xxwin.XXWIN_HCM_EMP_MASTER_STG_TAB where status not like 'P'

6) Keep an eye on the file created in the interface_home outgoing directory. The file size should be gradually increasing.

-rw-r--r-- 1 ortwncti dbtwncti 112573157 Nov 24 16:23 INTHCM1_20111124121142_00001.dat

select count(*) from PO_LINE_LOCATIONS_ALL

select count(*) from po_headers_interface

select count(*) from po_lines_interface

select count(*) from po_distributions_interface

select username,default_tablespace,account_status from dba_users

where default_tablespace='SYSAUX';

select * from DBA_HIST_WR_CONTROL;

select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

Select * from v$sysaux_occupants;

This is to find the work flow inbound and outbound service names and mailer user names.

select c.component_id, c.component_name, c.component_status,

p.parameter_id, p.parameter_name, v.parameter_value value

from fnd_svc_comp_param_vals_v v,

fnd_svc_comp_params_b p,

fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER', 'ACCOUNT', 'REPLYTO')

order by c.COMPONENT_ID;

To see the Login url from OS level.

grep -i login $CONTEXT_FILE

Important Quearies to get the SID from PID:

select * from v$session, v$process

where addr=paddr and

v$process.spid in ('1178','1129')

To Query users:

select user_name,creation_date

from fnd_user

where end_date is null

and user_name like '%\_%' escape '\'

select trunc(start_time) "Date", count( distinct user_id) "Number of users logged in",

count( distinct user_id)*100/2503 "% of total users"

from fnd_logins

where user_id not in (1162,0)

group by trunc(start_time)

Blocking Sessions Query

check for blocking sessions...

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

To check lock on PO table’s and AP table’s

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action from v$locked_object,dba_objects,v$session

where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id

and SESSION_ID=sid;

To Check that the program has to be run with which responsibility.

SELECT responsibility_name
FROM fnd_responsibility_tl a,
       fnd_responsibility c,
       fnd_request_group_units d,
       fnd_concurrent_programs_tl b
WHERE     a.responsibility_id = c.responsibility_id
       AND c.request_group_id = d.request_group_id
       AND b.concurrent_program_id = d.request_unit_id
       AND UPPER (b.USER_CONCURRENT_PROGRAM_NAME) =
              UPPER ('Qtel CI TO IRB Interface Program');

Revoke Exceptional Privileges from a DB User.

SELECT 'revoke insert,update,delete '||owner||'.'||object_name||' from xxwin;' FROM dba_objects WHERE object_type IN ('TABLE') and owner in ('APPS');

To See responsibilities of a User:

Select fu.user_id

,fu.user_name

,frt.RESPONSIBILITY_NAME

From fnd_user fu

,FND_USER_RESP_GROUPS_ALL fur

,FND_RESPONSIBILITY_TL frt

Where fu.user_id= 1218

and fu.user_id=fur.user_id

and fur.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID

To Find out the DB patch list:

$ cd $ORACLE_HOME/OPatch

$ optach lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc >> dbpatchlist.txt

$ vi patch.list

To find out size of the Database:

select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

Lock on Table:

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id

TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:

set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';

Lock on sessions:

select owner,OBJECT_NAME,LOCKED_MODE,SESSION_ID,client_identifier,program,module,action from v$locked_object,dba_objects,v$session
where dba_objects.owner='AP' and v$locked_object.object_id=dba_objects.object_id
and SESSION_ID=sid;

Find out SID from OS PID:

select * from v$session where paddr in (select addr from v$process where spid =31665)

To Check for the SYSTEM process Associted with Concurrent Request

select distinct s.inst_id,S.TYPE,substr(''''||S.SID||','||S.SERIAL#||'''',1,15) SI_SE#, p.spid,s.process client_process,
s.MACHINE, s.TERMINAL,P.PID, G.NAME,P.PROGRAM,s.module,S.USERNAME,q.module qmodule ,s.command,s.osuser,
q.hash_value, substr(q.sql_text,1,30) SQL_TEXT,w.EVENT,W.WAIT_TIME,w.SECONDS_IN_WAIT,
--,w.p1,w.p2,W.STATE,
s.logon_time,t.start_time,
t.used_ublk, t.log_io,t.phy_io,t.cr_change,q.EXECUTIONS,
q.PLAN_HASH_VALUE, q.CHILD_NUMBER,
.LOGON_TIME,s.last_call_et, s.status
9 from gV$SESSION S, gV$SQL Q, gV$PROCESS P, gV$BGPROCESS G, gv$session_wait w,gV$TRANSACTION T
where S.SQL_ADDRESS = Q.ADDRESS (+) and S.SQL_HASH_VALUE = Q.HASH_VALUE (+)
and S.PADDR = P.ADDR
and S.PADDR = G.PADDR (+)
and s.sid=w.sid (+)
AND S.SADDR = T.SES_ADDR (+)
and s.process in ('&client_process_list')

To Check Business Events Exist or not:

select guid from wf_events where name = 'xops.oracle.apps.gmd.rerollandcutbacks'

select *

from WF_BPEL_QTAB

where q_name = 'xops.oracle.apps.gmd.rerollandcutbacks'

To find out SPID:

SELECT s.sid, p.spid, s.osuser, s.program FROM v$process p,

v$session s WHERE p.addr = s.paddr

alter system kill session ’134,47747′ immediate;

SQL> select username,sid,serial#,terminal from v$session;

WFUPLOAD and WFDOWNLOAD

WFLOAD apps/X4wNG4rg 0 Y DOWNLOAD XXWIN_POWFRQAG.wft XXWINPOR

WFLOAD apps/X4wNG4rg 0 Y UPLOAD XXWIN_POWFRQAG.wft XXWINPOR

WFLOAD apps/X4wNG4rg 0 Y FORCE XXWIN_POWFRQAG.wft

select * from fnd_application_tl

Solution For the REP 3000, Report Cache Errors.

1- Shutdown the Concurrent Manager.
2- Manually delete all the temporary files under the /Base/logs/ora/10.1.2/reports/cache
3- Startup up the Concurrent Manager.

To Fix this Issue permanently.

Intermittent Oracle Reports REP-0069: REP-57054: Error (Doc ID 1237834.1).

note 1237834.1 and 1322704.1 mentioned in the support SR and come up the below action plan.

Action Plan
========
1. Stop the MT services
2. Review the patch readme and apply the patch 11669923, 11776182
3. change the cachesize parameter value from 50 to 0 within the file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf also add the <property name="noVoidedOutputError" value="yes"/>
<property name="cacheSize" value="50"/>
to
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
4. Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to $FND_TOP/admin/template/custom (ensure the file is not there in the custom directory before copy the file.
5. Add <property name="noVoidedOutputError" value="yes"/> to the $FND_TOP/admin/template/custom/rwbuilder_conf_1012.tmp and also change the cachesize value to 0.

in this file under the <cache class="oracle.reports.cache.RWCache"> section
ie:
<cache class="oracle.reports.cache.RWCache">
<property name="cacheSize" value="0"/>
<property name="noVoidedOutputError" value="yes"/>
<property name="cacheDir"
value="%s_logs_dir%/ora/10.1.2/reports/cache"/>


</cache>

6. Start the MT services

Huge Pages on the Server:

The host is up and huge page looks good now:

[root@auohswnct08 ~]# cat /proc/meminfo |grep -i huge
HugePages_Total: 8100
HugePages_Free: 6825
HugePages_Rsvd: 6750
Hugepagesize: 2048 kB
[root@auohswnct08 ~]# sysctl -a |grep -i huge
vm.hugetlb_shm_group = 0
vm.nr_hugepages = 8100

The host is hung in console after starting using xm create :

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

[root@auod09m2al141r23 27118_auohswnct08]# xm create -c vm.cfg
Using config file "./vm.cfg".

Started domain 27118_auohswnct08 (id=13)
[root@auod09m2al141r23 27118_auohswnct08]# xm console 27118_auohswnct08

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

We tried booting from the snapshot of Mar 24 - which is the only available snapshot in this DOM0 , but did not help.

Tried starting the host with the huge page entry commented out as the host was rebooted after the huge page changes, but no luck.

Checking further with Maint SA

Remove the hold of concurrent requests from back end:

SQL> @CMGR_del_hold

SQL> update apps.fnd_concurrent_requests

2 set hold_flag = 'N'

3 WHERE

4 nvl(hold_flag,'N') = 'Y' and phase_code = 'P' and

5 request_id not in (SELECT request_id FROM apps.tmp_program_on_hold) ;

Workflow Quries:

SELECT COUNT (*), machine, process

FROM gv$session

WHERE program LIKE 'JDBC%'

GROUP BY machine, process

ORDER BY 2 ASC

Workflow processed Query

select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE;

imap services are fine
==============

spawn telnet auohswnct09.oracleoutsourcing.com 143
Trying 141.146.174.10...
Connected to auohswnct09.oracleoutsourcing.com (141.146.174.10).
Escape character is '^]'.
* OK Dovecot ready.
1 login wfpwncti wfpwncti
1 OK Logged in.
1 select INBOX
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft NonJunk \*)] Flags permitted.
* 1 EXISTS
* 0 RECENT
* OK [UNSEEN 1] First unseen.
* OK [UIDVALIDITY 1300614072] UIDs valid
* OK [UIDNEXT 172901] Predicted next UID
1 OK [READ-WRITE] Select completed.
1 select PROCESS
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 1229 EXISTS
* 1229 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 1376] Predicted next UID

1 OK [READ-WRITE] Select completed.
1 select DISCARD
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 214 EXISTS
* 214 RECENT
* OK [UIDVALIDITY 1340822807] UIDs valid
* OK [UIDNEXT 407] Predicted next UID
1 OK [READ-WRITE] Select completed.
IMAP Validation Success

The messages are processed successfully
==========
SQL> select MSG_STATE, count(*) from applsys.aq$wf_notification_out group by MSG_STATE;

MSG_STATE COUNT(*)
---------------- ----------
PROCESSED 7650

No messages are stuck in out bound queue
=================
SQL> select count(*) from applsys.wf_notification_out where state=0;

COUNT(*)
----------
0

Workflow Quries for JDBC Connections:

select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1800
and status = 'ACTIVE'
order by last_call_et asc

select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1800
and s.status = 'ACTIVE'

To Find out the Report Builder Version through command line:

Findings
========
$ rwrun help=yes | grep Release
REP-0503: You did not specify the name of a report.
Report Builder: Release 10.1.2.3.0 - Production on Fri Nov 2 02:20:56 2012
$ pwd
/pwncti/product/1012/OPatch
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

OPatch succeeded.
$ opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 11669923

Sujeet APPS DBA

DBA Scripts

No comments:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database