Apps DBA Daily tasks!!

eBusiness 11i/R12 – Daily Checks (for Apps. DBA) – Admin Node

Being Apps. DBA, one should check the subsequent on Daily Basis in order to make sure that everything is working fine. This post is relating to ADMIN NODE.

Listener Status
$ lsnrctl status PROD(Listner Name)

File System Space Utilization
$ df -h

$df -sg

SQL> !df -sg /u99
Filesystem GB blocks Free* %Used   Iused   %Iused Mounted on
/dev/u99      599.85   490.19     19%    4108       1%             /u99

SQL>

 

Operating System – eBusiness DB and Concurrent Processes
$ ps -ef |grep pmon|grep -v grep
$ ps -ef |grep FNDLIBR|grep -v grep

eBusiness DB Instance
SQL> SELECT instance_name,host_name,TO_CHAR(startup_time,’DD-MON-YYY HH:MM:SS AM’) startedat FROM v$instance;

Invalid Objects
SQL> select owner,object_name,object_type from dba_objects where status’VALID’;

Inactive Users
SQL> select username,command,status,server,osuser,machine,terminal,program,module,action from v$session where status’INACTIVE’;

Active Sessions
SQL> select inst_id,count(*) from gv$session group by inst_id;

Active Users Detail
SQL> select username,command,status,osuser,process,machine,terminal,program,module,action from v$session where status’ACTIVE’;

Check inactive form sessions also for server load:

select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,
to_char( b.logon_time, ‘dd-MON-yyyy hh24:mi:ss’ ) logon_time,
trunc( sysdate-b.logon_time ) “Dy”,
trunc( mod( (sysdate-b.logon_time)*24, 24 ) ) “Hr”,
trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) ) “Mi”,
trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) “Sec”
from gV$access a,gv$session b, gv$process c
where a.sid=b.sid
and b.paddr=c.addr
and b.status=’INACTIVE’
and (b.action like ‘%FRM%’ or b.action like ‘%frm%’ or b.program like ‘%TOAD%’ or b.program like ‘%toad%’ or b.program like
‘SQL%’ or b.program like ‘%sql%’ or b.program like ‘%FRM%’
or b.program like ‘%frm%’ or b.action like ‘SQL%’ or b.action like ‘sql%’ or b.action like ‘TOAD%’ or b.action like ‘toad%’)
and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1)

You can use this according your use:

 

— +—————————————————————————-+
— | Venkat.K.Raju |
— | ursvenkat@gmail.com |
— | ursvenkat.wordpress.com |
— |—————————————————————————-|
— | Copyright (c) 2004-2015 Venkat.K.Raju. All rights reserved. |
— |—————————————————————————-|
— | DATABASE : Oracle |
— | FILE : Form_Sessions_Morethan_3_Hours.sql |
— | CLASS : Form_Sessions_Morethan_3_Hours |
— | PURPOSE : Query all Form_Sessions_Morethan_3_Hours in the databases. |
— |This query will privde the script to run againest the database |
— | |
— | NOTE : As with any code, ensure to test this script in a development |
— | environment before attempting to run it in production. |
— +—————————————————————————-+

SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
SET TERMOUT OFF;
COLUMN current_time NEW_VALUE current_time NOPRINT;
select to_char(sysdate,’DD:MON:YYYY HH24:MI:SS’) current_time from dual;
SET TERMOUT ON;

PROMPT
PROMPT +————————————————————————+
PROMPT | Report : Form Sessions More than 3 Hours and excluding %ANALYTICS% |
PROMPT | Instance : &current_instance |
PROMPT | Time : &current_time |
PROMPT | By Venkat.K.Raju |
PROMPT | The Blow count is from both &current_instance and Instance 2 |
PROMPT +————————————————————————+

select count(*) “Form Session more than 3hr ” from gv$session where PROGRAM like ‘frm%’ and last_call_et > 10800 and ACTION not like (‘%ANALYTICS%’) ;
PROMPT
PROMPT +————————————————————————+
PROMPT | Report : Form Sessions More than 3 Hours and excluding %ANALYTICS% |
PROMPT +————————————————————————+
set line 200
set pagesize 2000
col MACHINE for a24
col CLIENT_IDENTIFIER for a20
col LAST_CALL_ET for a10

select CLIENT_IDENTIFIER,SID,PROCESS,STATUS,MACHINE,to_char(sysdate,’dd:mon:yyyy hh24:mi:ss’)LOGON_TIME,
trunc (last_call_et/3600,2)||’ hr’ LAST_CALL_ET,ACTION,MODULE from gv$session where PROGRAM like ‘frm%’ and last_call_et > 10800 and ACTION not like (‘%ANALYTICS%’) order by LAST_CALL_ET;
PROMPT
PROMPT +————————————————————————+
PROMPT | Copy and Paste the below in both Node1 and Node2 as SYSDBA user |
PROMPT +————————————————————————+

set line 200
SET HEADING off
set pagesize 2000
select ‘alter system kill session ”’ ||sid|| ‘,’ || serial#|| ”’ immediate;’ from gv$session where PROGRAM like ‘frm%’ and last_call_et > 10800 and ACTION not like (‘%ANALYTICS%’);
~

Data Files Status:
SQL> select name,status from v$datafile;

Log Files Status
SQL> select * from v$log;

Archiving Errors
SQL> select error from v$archive_dest;

Tablespace Free Space
SQL> select tablespace_name,round (sum(bytes)/1024/1024) Free_Space_MB from dba_free_space group by (TABLESPACE_NAME);

Invalid Indexes
SQL> select index_name,table_name from dba_indexes where status =’INVALID’ and owner=’APPS’;

eBusiness 11i/R12 – Applications Listener Status
$ sh $COMMON_TOP/admin/scripts/PROD_oracle2/adalnctl.sh status

Checking on the status of Report Server
$ sh $ORACLE_HOME/admin/scripts/PROD_oracle2/adrepctl.sh status

Database Connectivity via Apps. user without Connect String
$ sqlplus -S -M “HTML ON TABLE ‘BORDER=”2″‘” “apps/****” @/oracle/appsprod/oui/venkat/appschk.sql > /oracle/appsprod/oui/venkat/output.html

Database Growth:

select to_char(creation_time, ‘RRRR Month’) “Month”, sum(bytes)/1024/1024/1024 “Growth in GB” from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, ‘RRRR Month’);

Advertisements
  1. December 29, 2009 at 9:55 am

    Hi,

    Nice posting. Thanks a lot for sharing.

    Like

    • sohail
      June 17, 2012 at 3:00 am

      Hi venkat,

      Good keep it up.

      From,
      Sohail.

      Like

      • ursvenkat
        June 29, 2012 at 2:13 pm

        Thank you buddy.. Let me know if any thing need to post.

        Like

  2. Muhammad shahid Hussain
    November 18, 2012 at 8:13 am

    Thank u so much for providing such types of information for us…..!

    Like

  3. sindhu Nunna
    February 18, 2013 at 8:11 am

    hi Venkat,
    Could you please explian,While checking concurrent process we can use ps -ef|grep FNDLIBR.You mentioned it as ps -ef |grep FNDLIBR|grep -v grep

    What is the difference between above two commands.Thanks in advance

    Thanks,
    Sindhu Nunna

    Like

    • ursvenkat
      February 18, 2013 at 9:29 am

      Hi Sindhu,

      First one thing..

      1. If you have multiple applications/Databases installed in one server how you will check for specific instance Database process?

      Here ps -ef |grep FNDLIBR|grep -v grep will bring the right output. Let me know if you required more details.

      Best Regards
      Venkat
      ursvenkat@gmail.com

      Like

  4. Anwar Jamil
    July 26, 2013 at 3:45 am

    Hi, daily great job and Thanks for sharing.

    Like

    • ursvenkat
      July 26, 2013 at 6:45 am

      Thanks Anwar.

      Like

  5. ursvenkat
    August 10, 2013 at 7:27 am

    Hi Guys, i am adding more details, let me know if you need any specific ..

    Regards
    Venkat

    Like

  6. Mohd Yaqub
    August 13, 2013 at 11:31 am

    Good brother

    Like

  7. Amol Nalawade
    March 11, 2014 at 1:07 pm

    hi,

    Thanks for info..

    please give me more info about App DBA

    Like

    • ursvenkat
      March 24, 2014 at 2:36 pm

      Hi Amol,

      Thank you for writing let me know what you are looking for. I’m glad to help

      Best Regards
      Venkat

      Like

  8. May 22, 2014 at 8:47 am

    Hello venkat,

    Nice post on Oracle Apps DBA check list, I appreciate if you post how cloning process will be done on realtime, they take any downtime or perform hot clone, please provide the complete steps for application cloning and post cloning steps in detail

    Regards
    Mohd Qadar

    Like

    • ursvenkat
      May 22, 2014 at 1:26 pm

      Hi Qadar,

      There is no concept of hot clone as i know… if you see in version 12.2.3 there is Online patching with ADOP utility. coming to post clone its differ on client specific. Please let me know its cleared or send me a mail so i can give more details.

      Best Regards
      Venkat

      Like

  9. Santosh
    November 18, 2014 at 5:50 am

    Hi Venkat…I’m Having 5 years exp. as Oracle DBA just switched my skill to Oracle Apps DBA
    Wondering Wht will be the Daily routine task for Oracle Apps DBA..
    Thanks after reading your Post I found Answer for question ..

    Regards
    Santosh

    Like

    • ursvenkat
      November 18, 2014 at 12:48 pm

      Thankyou

      Like

  10. Raghu
    July 7, 2015 at 7:07 pm

    thank u its really helps a lot to understand apps dba routine tasks
    thanks for infor

    Like

    • ursvenkat
      July 7, 2015 at 9:49 pm

      Thank you Raghu.

      Like

  11. Raghu
    July 10, 2015 at 5:17 pm

    can u post apps dba day to day Queries

    Like

    • ursvenkat
      July 10, 2015 at 5:23 pm

      I will do

      Like

  12. Raghu
    May 7, 2016 at 8:07 am

    hi sir can u please post APPS R12 filesystem,and patching methodology

    Like

  13. Raghu
    May 8, 2016 at 10:27 am

    12.2.4 file system and differences between 12.1.3 and 12.2.4

    Like

  14. Raghu
    May 8, 2016 at 10:28 am

    pls post above mentioned

    Like

  15. Venkata
    August 14, 2017 at 11:24 am

    Hi Venkat,
    Can you please share the detail troubleshooting steps Long running requests.

    Thanks in Advance.

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: