R12/11i 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
[oracle@r0469 11.1.0]$ lsnrctl status VIS
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 21-SEP-2012 16:18:39
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=r0469.oracle.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias VIS
Version TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Start Date 19-SEP-2012 12:21:31
Uptime 2 days 3 hr. 57 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/VIS/db/tech_st/11.1.0/network/admin/VIS_r0469/listener.ora
Listener Log File /oracle/VIS/db/tech_st/11.1.0/log/diag/tnslsnr/r0469/vis/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=r0469.oracle.com)(PORT=1521)))
Services Summary...
Service "VIS" has 2 instance(s).
Instance "VIS", status UNKNOWN, has 1 handler(s) for this service...
Instance "VIS", status READY, has 1 handler(s) for this service...
Service "VIS_XPT" has 1 instance(s).
Instance "VIS", status READY, has 1 handler(s) for this service...
The command completed successfully

File System Space Utilization
$ df -h
Operating System – eBusiness DB and Concurrent Processes
[oracle@r0469 11.1.0]$ ps -ef |grep pmon|grep -v grep

oracle 22862 1 0 Sep19 ? 00:00:06 ora_pmon_VIS

[oracle@r0469 11.1.0]$ ps -ef |grep FNDLIBR|grep -v grep

oracle 15978 15973 0 Sep20 pts/0 00:00:21 FNDLIBR
oracle 16153 16024 0 Sep20 ? 00:00:01 FNDLIBR
oracle 16154 16024 0 Sep20 ? 00:00:01 FNDLIBR
oracle 16159 16024 0 Sep20 ? 00:00:01 FNDLIBR
oracle 16164 16024 0 Sep20 ? 00:00:00 FNDLIBR
eBusiness DB Instance
SQL> select instance_name,host_name,To_CHAR(startup_time,'DD-MON-YYY') startedat from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STARTEDAT
-------------------
VIS
ebs602.oracle.com
03-APR-009

SQL> select instance_name,host_name,To_CHAR(startup_time,'DD-MON-YYY HH:MM:SS') startedat from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STARTEDAT
----------------------------
VIS
ebs602.oracle.com
03-APR-009 10:04:31

SQL> select instance_name,host_name,To_CHAR(startup_time,'DD-MON-YYY HH:MM:SS AM') startedat from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STARTEDAT
-------------------------------
VIS
ebs602.oracle.com
03-APR-009 10:04:31 PM



Invalid Objects
SQL> select owner,object_name,object_type from dba_objects where status='INVALID';



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;
INST_ID COUNT(*)
---------- ----------
1 100

Active Users Detail
SQL> select username,command,status,server,osuser,machine,terminal,program,module,action from v$session where status='INACTIVE';

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

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 184 314572800 1 NO INACTIVE

1.0132E+13 19-SEP-12

2 1 185 314572800 1 NO CURRENT

1.0132E+13 21-SEP-12

3 1 183 314572800 1 NO INACTIVE

1.0132E+13 17-SEP-12

Archiving Errors
SQL> select error from v$archive_dest;

ERROR
-----------------------------------------------------------------
10 rows selected.

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';
no rows selected

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