DBA Arena

List of tasks to be done as part of the Health Check

 

1. Check database and application availability
2. Check status of cluster services
3. Monitor space availability in all tablespaces
4. Check for errors in Database Alert log files and take appropriate action
5. Check Concurrent Managers
6. Check /tmp for space constraints
7. Check invalid objects and compile if required
8. ASM Grid Space Check
9. Database and Application Mount points check
10. Verify Backups
11. Verify Gather Schema Statistics and Purge Concurrent Request and/or Manager Data Concurrent Requests are scheduled and are completing successfully

 

1. Check database and application availability

SQL> select name,open_mode from v$database;

 

NAME      OPEN_MODE

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

PROD   READ WRITE

 

SQL> select instance_name,status from v$instance;

 

INSTANCE_NAME    STATUS

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

prod1         OPEN

 

SQL> select instance_name,status from v$instance;

 

INSTANCE_NAME    STATUS

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

prod2         OPEN

 

[applprod@ebsapp01 ~]$ adopmnctl.sh status

You are running adopmnctl.sh version 120.6.12010000.5

Checking status of OPMN managed processes...

 

Processes in Instance: PROD_ebsapp01.ebsapp01.hmc.org.qa

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status

---------------------------------+--------------------+---------+---------

OC4JGroup:default_group          | OC4J:oafm          |   22993 | Alive

OC4JGroup:default_group          | OC4J:forms         |   22744 | Alive

OC4JGroup:default_group          | OC4J:oacore        |     666 | Alive

OC4JGroup:default_group          | OC4J:oacore        |     665 | Alive

HTTP_Server                              | HTTP_Server        |     521 | Alive

 

 

adopmnctl.sh: exiting with status 0

 

 

2. Check status of cluster services

Go to $GRID_HOME/bin

[oragrid@ebsdb01 bin]$ ./crsctl stat res –t

 

[oragrid@ebsdb01 bin]$ ./crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

3. Monitor space availability in all tablespaces

Use the below query

select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace

as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2)

as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t,

(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs

where t.tablespace=fs.tablespace order by "% Free";

 

TABLESPACE	Totalspace(MB)	Used Space(MB)	Freespace(MB)	% Used	% Free
APPS_UNDOTS1	32,768	28,829	3,939	88	12
SYSAUX	10,240	8,135	2,105	79	21
APPS_TS_INTERFACE	15,360	11,876	3,484	77	23
APPS_TS_TX_DATA	529,984	378,323	151,661	71	29
APPS_TS_TX_IDX	463,104	326,945	136,159	71	29
DBA_AUDIT_BKP	76,800	45,594	31,206	59	41
APPS_TS_MEDIA	157,696	91,863	65,833	58	42
APPS_TS_SUMMARY	30,720	17,506	13,214	57	43
APPS_BACKUP	15,360	8,750	6,610	57	43
APPS_TS_ARCHIVE	18,432	10,215	8,217	55	45
APPS_TS_SEED	6,144	3,305	2,839	54	46
APPS_UNDOTS2	32,768	16,185	16,583	49	51
APPS_TS_QUEUES	61,440	30,333	31,107	49	51
DBA_AUDIT	15,360	5,434	9,926	35	65
APPS_TS_NOLOGGING	500	151	349	30	70
SYSTEM	56,320	10,129	46,191	18	82
DISCOVERER	5,120	703	4,417	14	86
ODM	100	11	89	11	89
OWAPUB	10	1	9	10	90
PORTAL	100	2	98	2	98
OLAP	100	1	99	1	99
XXHYP	1,024	9	1,015	1	99
CTXD	300	1	299	0	100
APPS_TS_TOOLS	512	1	511	0.2	99.8

 

 

 

3. Check for errors in Database Alert log files and take appropriate action

Check Alter log using vi editor by searching /ORA-

Or Use below query

 

SQL> select TO_CHAR(ORIGINATING_TIMESTAMP,'MM-DD-YYYY  HH:MM') ,rownum,message_text FROM   sys.x$dbgalertext where originating_timestamp > (sysdate - 1)

and message_text like 'ORA-%' order by originating_timestamp;  2    3

 

no rows selected

 

4. Check Concurrent Managers

Use below Query or Check from Application frontend using navigation as

System Administrator > Concurrent > Manager > Administer

select T.USER_CONCURRENT_QUEUE_NAME,B.RUNNING_PROCESSES,B.MAX_PROCESSES

from apps.FND_CONCURRENT_QUEUES_TL T, apps.FND_CONCURRENT_QUEUES B

where B.APPLICATION_ID = T.APPLICATION_ID

and B.CONCURRENT_QUEUE_ID = T.CONCURRENT_QUEUE_ID

and T.LANGUAGE = userenv('LANG');

 

USER_CONCURRENT_QUEUE_NAME	RUNNING_PROCESSES	MAX_PROCESSES
Conflict Resolution Manager	1	1
Internal Manager	1	1
Scheduler/Prereleaser Manager	1	1
Transaction Manager ( Internal use only )	0	0
Standard Manager	15	15
PO Document Approval Manager	1	1
Receiving Transaction Manager	10	10
PA Streamline Manager	1	1
Workflow Summary Mailer	0	0
Inventory Manager	20	20
INV Remote Procedure Manager	1	1
MRP Manager	0	0
CRP Inquiry Manager	0	0
Workflow Mailer	0	0
Workflow Agent Listener Service	1	1
Workflow Mailer Service	1	1
Transportation Manager	0	0
WMS Task Archiving Manager	0	0
Oracle Provisioning Manager	0	0
Debug Service	0	0
C AQCART Service	0	0
Session History Cleanup	1	1
UWQ Worklist Items Release for Crashed session	1	1
SFM Controller Service	0	0
SFM Order Queue Service	0	0
SFM Work Item Queue Service	0	0
SFM Fulfillment Actions Queue Service	0	0
SFM Fulfillment Element Ready Queue Service	0	0
SFM Event Manager Queue Service	0	0
SFM Inbound Messages Queue Service	0	0
SFM Timer Queue Service	0	0
SFM Application Monitoring Service	0	0
SFM SM Interface Test Service	0	0
Contracts Core Concurrent Manager	0	0
Collections Manager	0	0
OAM Metrics Collection Manager	1	1
Workflow Document Web Services Service	1	1
Marketing Data Mining Manager	0	0
Output Post Processor	5	5
FastFormula Transaction Manager	1	1
Email Center Download Processor - Normal Mode	0	0
Email Center Download Processor - Migration Mode	0	0
Service Manager: EBSAPP01	1	1
Internal Monitor: EBSAPP01	0	0
OAM Generic Collection Service:EBSAPP01	1	1
Service Manager: EBSAPP02	1	1
Internal Monitor: EBSAPP02	0	0
OAM Generic Collection Service:EBSAPP02	1	1
Standard Manager_EBSAPP02	15	15
Output Post Processor_EBSAPP02	5	5
CUSTOM_MANAGER	10	10
CUSTOM MANAGER_EBSAPP02	10	10

Submit an Active Users request by navigating to System Administrator > Concurrent > Manager > Administer
Once the request completes check the request log and out files.

 

 

5. Check /tmp for space constraints on both Database and Application tier

 

6. Check invalid objects and compile if required

select object_name,owner, status from all_objects where status like 'INVALID';

Compile if required using

SQL> @?/rdbms/admin/utlrp.sql

 

7. ASM Grid Space Check

Use below query or check using lsdg from asm commandline

select NAME,STATE,TOTAL_MB,USABLE_FILE_MB,FREE_MB from v$asm_diskgroup;

NAME	STATE	TOTAL_MB	USABLE_FILE_MB	FREE_MB
DATA	CONNECTED	124363776	33509152	98109248
DBFS_DG	MOUNTED	1192960	446692	1191624
RECO	CONNECTED	31090944	1361400	4021488

 

 

 

8. Database and Application Mount points check

Use df-h and crosscheck

[oracle@ebsdb01 ~]$ df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VGExaDb-LVDbSys1

                       99G   38G   57G  40% /

/dev/sda1             124M   94M   25M  80% /boot

/dev/mapper/VGExaDb-LVDbOra1

                      297G  172G  110G  62% /u01

tmpfs                  81G  277M   81G   1% /dev/shm

192.168.11.70:/export/ebsdbbackup/bkpchnl1

                      3.0T  2.2T  914G  71% /ebsdbbkp

 

[applprod@ebsapp01 ~]$ df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/vg_os-lv_root

                       82G  4.5G   73G   6% /

/dev/mapper/vd_data-lv_bkp

                      582G  295G  258G  54% /backup

/dev/mapper/vd_data-lv_store

                      462G  199M  438G   1% /store

/dev/mapper/vd_data-lv_mt

                      485G   87G  373G  19% /u01pa

/dev/cciss/c0d0p1     190M   21M  160M  12% /boot

tmpfs                  32G     0   32G   0% /dev/shm

 

9. Verify Backups

Cross verify the backup logs for Disk and Tapes.

 

10. Verify Gather Schema Statistics and Purge Concurrent Request and/or Manager Data Concurrent Requests are scheduled and are completing successfully.