Useful 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 = 'SCOTT' 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='SCOTT';

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='SCOTT'

-- 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='ROSCOTT'

revoke  select on  HR.AME_ACTION_TYPES_TL from ROSCOTT

-- 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;

 

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 ('SCOTT');

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

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 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;

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"/>
<!--property name="maxCacheFileNumber" value="max number of cache files"/-->
<!--property name="ignoreParameters" value="parameter names to be
ignored in constructing cache key, separated by comma ','"/-->
</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

Workflow  Quries:

SELECT COUNT (*), machine, process

    FROM gv$session

   WHERE program LIKE 'JDBC%'

GROUP BY machine, process

ORDER BY 2 ASC

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