Health Check on Database 11G

Health Check on Database 

To check for the control files and to have at least two copies of the controlfile

connect as sysdba
SQL> select status, name from v$controlfile;

At least two redo log groups are required

SQL> select * from v$logfile;

To check archive configuration

SQL> archive log list

To determine if a datafile and thus, a tablespace, has AUTOEXTEND capabilities:

SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name from dba_data_files where autoextensible = 'YES';

To verify location of datafiles

SQL> select * from v$dbfile;

To check user objects that should not be created in the system tablespace

SQL> select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');

To check which components are occupying space:

 SQL> select space_usage_kbytes, occupant_name, occupant_desc
from v$sysaux_occupants
order by 1 desc;

To verify which tablespace is Locally Managed or Dictionary Managed:

SQL> select tablespace_name, extent_management
from dba_tablespaces;

To view tablespaces

SQL> select tablespace_name, contents
from dba_tablespaces;

To query lists of all users that have a permanent tablespace specified as their default temporary tablespace.

SQL> select u.username, t.tablespace_name
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';

To show the SYSTEM tablespace as default temporary tablespace. This value can be altered as well to prevent fragmentation in the SYSTEM tablespace.

SQL> alter user SYSTEM temporary tablespace TEMP

To get the size of the temporary tablespace:

SQL> select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;

To get the "high water mark" of that temporary tablespace (= max used at one time):

SQL> select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;

 To get  current usage:

SQL> select ss.tablespace_name, 
sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;

To query list of all objects that have allocated more extents than a specified minimum. Change the <--minext--> value by an actual number, in general objects allocating more then 100 a 200 extents can be recreated with larger extent sizes:

SQL> select owner, segment_type, segment_name, tablespace_name,
count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks)
from dba_extents
where owner NOT IN ('SYS','SYSTEM')
group by owner, segment_type, segment_name, tablespace_name
having count(*) > <--minext-->>
order by segment_type, segment_name;

To query all segments that are unable to allocate their next extent :

select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);

Query to show if there are not enough rollback segments online or if the rollback segments are too small.

SQL> select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
s.wraps, s.status
from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id
order by 1;

The WAITS indicates which rollback segment headers had waits for them. Typically you would want to reduce such contention by adding rollback segments. 

If SHRINKS is non zero then the OPTIMAL parameter is set for that particular rollback segment, or a DBA explicitly issued a shrink on the rollback segment.
The number of shrinks indicates the number of times a rollback segment shrinked because a transaction has extended it beyond the OPTIMAL size. If this value is too high then the value of the OPTIMAL size should be increased as well as the overall size of the rollback segment (the value of minextents can be increased or the extent size itself, this depends mostly on the indications of the WRAPS column).

The WRAPS column indicate the number of times the rollback segment wrapped to another extent to serve the transaction. If this number is significant then you need to increase the extent size of the rollback segment.

Oracle 11g

Automatic Memory Management (AMM) is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

Oracle 12c

You can monitor SGA memory usage on pluggable databases using queries against database views.

11g and above: Alert File

SQL> show parameter diagnostic_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
diagnostic_dest string /oracle/admin/L111

Pre-11g: 

show parameter background_dump_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
background_dump_dest string D:\Oradata\Admin\PROD\Trace\BDump

Make sure the disk space can handle the maximum size specified, if not then this value should be changed.

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
---------------------------------- ------- ---------------------
max_dump_file_size integer 10240

Audit files

By default, every connection as SYS or SYSDBA is logged in an operating system file. 
The location is controlled through the parameter 'audit_file_dest'. If this parameter is not set then the location defaults to $ORACLE_HOME/rdbms/audit. Overtime this directory may contain a lot of auditing information and can take up a significant amount of space.

Advanced Health Checking

The previous sections have been outlining the basic items to check to prevent common database caveats. In this section you will find references to several MOS articles explaining how a more in depth analyses and monitoring can be achieved. These article mainly focus on Data Dictionary Integrity and DataBase structure verification.

Oracle Pre-11g:

Note 456468.1 - Identify Data Dictionary Inconsistency 
NOTE 136697.1 - "hcheck8i.sql" script to check for known problems in racle8i,Oracle9i, and Oracle10g


Oracle 11g

Note 466920.1: 11g New Feature Health monitor 

REFERENCES

NOTE:249664.1 - Pfile vs SPfile
NOTE:105120.1 - Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
NOTE:115424.1 - How to Rename or Move Datafiles and Logfiles
NOTE:122555.1 - Determine How Much Disk Space is Needed for the Archive Files
NOTE:136697.1 - "hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g
NOTE:147356.1 - How to Move Tables from One Tablespace to Another.
NOTE:112011.1 - ALERT: RESIZE or AUTOEXTEND can "Over-size" Datafiles and Corrupt the Dictionary
NOTE:262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
NOTE:262472.1 - 10g: BIGFILE Type Tablespaces Versus SMALLFILE Type
NOTE:311615.1 - Oracle 10G new feature - Automatic Undo Retention Tuning
NOTE:329984.1 - Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
NOTE:443746.1 - Automatic Memory Management (AMM) on 11g
NOTE:456468.1 - Identify Data Dictionary Inconsistency


NOTE:62005.1 - Creating, Optimizing, and Understanding Rollback Segments
NOTE:69739.1 - How to Turn Archiving ON and OFF in Oracle RDBMS
NOTE:564989.1 - How To Truncate a Background Trace File Without Bouncing the Database
NOTE:93771.1 - Introduction to Locally-Managed Tablespaces
NOTE:1012431.6 - Overview of Database Fragmentation in Oracle 7
NOTE:1020182.6 - Script to Detect Tablespace Fragmentation
NOTE:102995.1 - Maintenance of Online Redo Log Groups and Members

Additional References:
Note: 122669.1 How to Perform a Health Check on the Database 

Note: 1417774.1 FAQ: SQL Health Check (SQLHC) Frequently Asked Questions

Note: 1366133.1 SQL Tuning Health-Check Script (SQLHC)

Note: 868955.1 Get Proactive - Oracle Health Checks - Installation, troubleshooting, catalog and more.

R12: Find Service Transaction Managers Status

select *
  from (select q.user_concurrent_queue_name service_name,
               a.application_name srvc_app_name,
               a.application_short_name srvc_app_short_name,
               q.concurrent_queue_name service_short_name,
               decode( ( select count(*)
          from apps.fnd_concurrent_processes fcp1
         where fcp1.concurrent_queue_id = q.concurrent_queue_id
           and fcp1.queue_application_id = q.application_id
           and ( fcp1.process_status_code in ('C','M')
               or (fcp1.process_status_code in ('A', 'D', 'T')
               and exists (select 1
                             from gv$session
                            where fcp1.session_id = audsid )))
        )/*actual_processes */, 0,
       decode(q.max_processes, 0,'NOT_STARTED', 'DOWN'),
              q.max_processes, 'UP', 'WARNING' ) service_status,
              q.max_processes target_processes,
        (select count(*)
           from apps.fnd_concurrent_processes fcp2
          where fcp2.concurrent_queue_id = q.concurrent_queue_id
            and fcp2.queue_application_id = q.application_id
            and ( fcp2.process_status_code in ('C','M')
             /* Connecting or Migrating */
             or ( fcp2.process_status_code in ('A', 'D', 'T')
            and exists (select 1 from gv$session
                         where fcp2.session_id = audsid)))) actual_processes,
                        '' message, s.service_handle srvc_handle
                        from apps.fnd_concurrent_queues_vl q,                            
                                 apps.fnd_application_vl a,
                                apps.fnd_cp_services s
                       where q.application_id = a.application_id
                         and s.service_id = q.manager_type
                      UNION
                   /* Need to cover the case where a manager has no rows in
                      FND_CONCURRENT_PROCESSES. Outer joins won't cut it. */
                     select q.user_concurrent_queue_name service_name,
                            a.application_name srvc_app_name,
                            a.application_short_name srvc_app_short_name,
                            q.concurrent_queue_name srvc_short_name,
                            decode( q.max_processes, 0, 'NOT_STARTED', 'DOWN') service_status,
                            q.max_processes target_processes,
                            0 actual_processes,
                            '' message, s.service_handle srvc_handle
                      from apps.fnd_concurrent_queues_vl q, apps.fnd_application_vl a,
                           apps.fnd_cp_services s
                     where q.application_id = a.application_id
                       and s.service_id = q.manager_type
                       and not exists (select 1 from apps.fnd_concurrent_processes p
                                        where process_status_code in ('C','M','A','D','T')
                                          and q.concurrent_queue_id = p.concurrent_queue_id
                                          and q.application_id = p.queue_application_id) )
                   where service_name= 'MRP Manager'   --'Inventory Manager'
               order by service_status;

R12 EBS Locked Objects and Long Running Jobs

TO LIST ALL LOCKED OBJECTS:

SELECT b.session_id AS sid,
               NVL(b.oracle_username, '(oracle)') AS username,
               a.owner AS object_owner,
               a.object_name,
               Decode(b.locked_mode, 0, 'None',
                                                      1, 'Null (NULL)',
                                                      2, 'Row-S (SS)',
                                                      3, 'Row-X (SX)',
                                                      4, 'Share (S)',
                                                      5, 'S/Row-X (SSX)',
                                                      6, 'Exclusive (X)', b.locked_mode) locked_mode,
               b.os_user_name
FROM   dba_objects a,
               v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

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

SELECT a.type,
               Substr(a.owner,1,30) owner,
               a.sid,
               Substr(a.object,1,30) object
FROM   v$access a
WHERE a.owner NOT IN ('SYS','PUBLIC')
ORDER BY 1,2,3,4;

-------------------------------------
TO LIST LOCKED OBJECTS FOR RAC:

SELECT b.inst_id,
               b.session_id AS sid,
               NVL(b.oracle_username, '(oracle)') AS username,
               a.owner AS object_owner,
               a.object_name,
               Decode(b.locked_mode, 0, 'None',
                                                      1, 'Null (NULL)',
                                                      2, 'Row-S (SS)',
                                                      3, 'Row-X (SX)',
                                                      4, 'Share (S)',
                                                      5, 'S/Row-X (SSX)',
                                                      6, 'Exclusive (X)',
                                                           b.locked_mode) locked_mode,
               b.os_user_name
FROM   dba_objects a,
               gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

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

R12 Long Running Concurrent Jobs More Than 5 Mins

SELECT DISTINCT fcpt.USER_CONCURRENT_PROGRAM_NAME,
       (fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 process_time,
       fcr.request_id,
       fcr.parent_request_id,
       fcr.request_date,
       fcr.actual_start_date,
       fcr.actual_completion_date,
       (fcr.actual_completion_date-fcr.request_date)*24*60*60 end_to_end,
       (fcr.actual_start_date-fcr.request_date)*24*60*60 lag_time,
       fu.user_name,
       fcr.phase_code,
       fcr.status_code,
       fcr.argument_text,
       fcr.priority
 FROM apps.fnd_concurrent_requests fcr,
      apps.fnd_concurrent_programs fcp,
      apps.FND_CONCURRENT_PROGRAMS_TL fcpt,
      apps.fnd_user fu
WHERE fcr.concurrent_program_id=fcp.concurrent_program_id AND
      fcp.concurrent_program_id=fcpt.concurrent_program_id AND
      fcr.requested_by=fu.user_id AND
      status_code='C' AND
      (fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 > 300
ORDER BY Process_time desc;

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

R12 EBS USER LOCKING TABLE

SELECT objects.owner,

       objects.object_name,

       objects.object_type,

       user1.user_name locking_fnd_user_name,

       login.start_time locking_fnd_user_login_time,

       vs.module,

       vs.machine,

       vs.osuser,

       vlocked.oracle_username,

       vs.sid,

       vp.pid,

       vp.spid os_process,

       vs.serial#,

       vs.status,

       vs.saddr,

       vs.audsid,

       vs.process

  FROM fnd_logins login,

       fnd_user user1,

       v$locked_object vlocked,

       v$process vp,

       v$session vs,

       dba_objects objects

 WHERE     vs.sid = vlocked.session_id

       AND vlocked.object_id = objects.object_id

       AND vs.paddr = vp.addr

       AND vp.spid = login.process_spid(+)

       AND vp.pid = login.pid(+)

       AND login.user_id = user1.user_id(+)

       AND objects.object_name LIKE '%' || upper('PO_HEADERS_ALL') || '%'

       AND NVL (vs.status, 'XX') != 'KILLED';

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

R12 EBS To Check Non-Blocking Objects

SELECT oracle_username || ' (' || s.osuser || ')' username,
         s.sid || ',' || s.serial# sess_id,
         owner || '.' || object_name object,
         object_type,
         DECODE (l.block,  0, 'Not Blocking',  1, 'Blocking',  2, 'Global')
            status,
         DECODE (v.locked_mode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lmode))
            mode_held
    FROM v$locked_object v,
         dba_objects d,
         v$lock l,
         v$session s
   WHERE     v.object_id = d.object_id
         AND v.object_id = l.id1
         AND v.session_id = s.sid
        -- AND l.block <> 0
ORDER BY oracle_username, session_id;

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

R12 GET SID, SERIAL#, SPID OF A RUNNING CONCURRENT REQUEST

SELECT fcr.request_id,
               vs.sid,
               vs.serial# ,
               vp.SPID
 FROM  apps.fnd_concurrent_requests   fcr,
              apps.fnd_concurrent_processes fcp,
              v$process                                    vp,
              v$session                                     vs
 WHERE fcr.controlling_manager = fcp.concurrent_process_id
      AND vp.pid                              = fcp.oracle_process_id
      AND fcp.session_id                 = vs.audsid
      AND fcr.request_id                  = &Request_ID
      AND fcr.phase_code                = 'R';
-------------------------------------------

ORACLE APPS RECORD LOCKING 
FRM-40501: COULD NOT RESERVE RECORD [2 TRIES]

One of your end users attempts to modify a record, and the user is prompted with a dialog box asking “Could not reserve record [2 tries]. Keep trying?” The user answers “yes” and after being prompted a few more times the user gives up. Ultimately the user gets a “FRM-40501: ORACLE error: unable to reserve record for update or delete.” When this happens it’s nice to have some scripts ready to go to quickly diagnose what is causing the contention, so appropriate action can be taken. The forms don’t wait to the obtain the lock for very long, so it’s a little more difficult to determine the blocking lock. This first script provides a listing of the possible locks and some relevant E-Business Suite information for digging further.

Query 1:
SELECT vs.audsid audsid,

       locks.sid sid,

       vs.serial# serial#,

       vs.username oracle_user,

       vs.osuser os_user,

       vs.program program,

       vs.module module,

       vs.action action,

       vs.process process,

       DECODE (locks.lmode,  1, NULL,  2, 'Row Share',

3, 'Row Exclusive',

4, 'Share',

5, 'Share Row Exclusive',

6, 'Exclusive', 'None') lock_mode_held,

decode(locks.request,

1, NULL,

2, 'Row Share',

3, 'Row Exclusive',

4, 'Share',

5, 'Share Row Exclusive',

6, 'Exclusive', 'None') lock_mode_requested,

decode(locks.type,

'MR', 'Media Recovery',

'RT', 'Redo Thread',

'UN', 'User Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL User Lock',

'DX', 'Distributed Xaction',

'CF', 'Control File',

'IS', 'Instance State',

'FS', 'File Set',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalidation',

'LS', 'Log Start or Log Switch',

'RW', 'Row Wait',

'SQ', 'Sequence Number',

'TE', 'Extend Table',

'TT', 'Temp Table',

locks.type) lock_type,

objs.owner object_owner,

objs.object_name object_name,

objs.object_type object_type,

round( locks.ctime/60, 2 ) lock_time_in_minutes

from v$session vs,

v$lock locks,

dba_objects objs,

dba_tables tbls

where locks.id1 = objs.object_id

and vs.sid = locks.sid

and objs.owner = tbls.owner

and objs.object_name = tbls.table_name

and objs.owner != 'SYS'

and locks.type = 'TM'

order by lock_time_in_minutes;

To determine the table(s) the form is trying to lock, use the Help->Record History menu option; this provides the base table or view for the form block.

Look through the result set from Query 1 for an object_name (typically a table or view) in the same vicinity as your table or view. If you have a form block based on a view, it may be helpful to look up the tables behind the view. If the action starts with an ‘FRM:%’, then another forms session has the lock.

One thing to note: You’ll often see the same user blocking themselves. This could be a training issue, or it could be due to a previous forms session that crashed, but the f60webmx process did not die. If this is the case, you can kill the application server OS process (based on the process value in Query 1).

Query 2 provides further details for results in Query 1 that are forms sessions – simply plop in the AUDSID from Query 1.

Query 2:

SELECT F.AUDSID,

       S.SID,

       S.SERIAL#,

       L.USER_ID,

       L.TERMINAL_ID,

       L.LOGIN_NAME,

       R.RESP_APPL_ID,

       R.RESPONSIBILITY_ID,

       F.FORM_ID,

       F.FORM_APPL_ID,

       L.PID,

       L.PROCESS_SPID,

       NVL (F.START_TIME, NVL (R.START_TIME, L.START_TIME)) TIME,

       USR.USER_NAME,

       a.application_name,

       RSP.RESPONSIBILITY_NAME,

       FRM.USER_FORM_NAME,

       s.program,

       s.action,

       s.module,

       s.state,

       s.event,

       s.wait_class,

       s.seconds_in_wait

  FROM FND_RESPONSIBILITY_TL RSP,

       FND_FORM_TL FRM,

       FND_USER USR,

       FND_LOGINS L,

       FND_LOGIN_RESPONSIBILITIES R,

       FND_LOGIN_RESP_FORMS F,

       GV$SESSION S,

       fnd_application_tl A

 WHERE     F.AUDSID = &ENTER_FORM_AUDSID

       AND R.LOGIN_ID = F.LOGIN_ID

       AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID

       AND L.LOGIN_ID = R.LOGIN_ID

       AND L.END_TIME IS NULL

       AND R.END_TIME IS NULL

       AND F.END_TIME IS NULL

       AND L.USER_ID = USR.USER_ID

       AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID

       AND R.RESP_APPL_ID = RSP.APPLICATION_ID

       AND RSP.LANGUAGE =  'US'

       AND RSP.application_id = a.application_id

       AND a.language =  'US'

       AND F.FORM_ID = FRM.FORM_ID

       AND F.FORM_APPL_ID = FRM.APPLICATION_ID

       AND FRM.LANGUAGE =  'US'

       AND F.AUDSID = S.AUDSID;

If a concurrent program holds the lock, Query 3 provides a bit more information. Here we can see the user, concurrent program, how long it’s been running and log/output files.

Query 3:

SELECT fcr.request_id,

       fcr.requested_by,

       fu.user_name,

       fcr.program_application_id,

       fcr.concurrent_program_id,

       fcr.actual_start_date,

       fat.application_name,

       fcp.concurrent_program_name,

       fcpt.user_concurrent_program_name,

       fcr.description,

       fcr.logfile_node_name,

       fcr.outfile_name,

       fcr.logfile_name,

       fcr.completion_text,

       fcr.parent_request_id,

       vs.process,

       vs.state,

       vs.event,

       vs.wait_class,

       vs.seconds_in_wait

  FROM v$session vs,

       fnd_concurrent_requests fcr,

       fnd_application_tl fat,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_user fu

 WHERE     vs.audsid = &ENTER_CONC_PROCESS_AUDSID

       AND vs.process = fcr.os_process_id

       AND fcr.actual_completion_date IS NULL

       AND fcr.program_application_id = fat.application_id

       AND fcr.program_application_id = fcp.application_id

       AND fcr.concurrent_program_id = fcp.concurrent_program_id

       AND fcr.program_application_id = fcpt.application_id

       AND fcr.concurrent_program_id = fcpt.concurrent_program_id

       AND fcr.requested_by = fu.user_id;

This should be enough information to chase down the offender (someone out for coffee and not save that latest change first?) or possibly even point to a process that needs attention.