15
Oct

Health Check of Oracle E-Business Suite Database

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

 

 

  1. 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

  1. 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

 

 

 

  1. 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

 

  1. 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.

 

 

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

 

  1. 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

 

  1. 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

 

 

 

  1. 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

 

  1. Verify Backups

Cross verify the backup logs for Disk and Tapes.

 

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

 

Back to Top