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

Thanks
Srini