Production DBA support Scripts

To find out the locked objects

If a table used by an user A is locked by User B then A needs to wait until B unlocks it. By issuing this query, User A can find which tables in his schema are locked and which session has locked it.

select a.sid,a.serial#,c.object_name
 from V$session a,
 V$locked_object b,
 user_objects c
 where a.sid=b.session_id
 and b.object_id=c.object_id;

Output:
 SID SERIAL# OBJECT_NAME
 ---- ------- ------------
 7 36 emp
 9 58 dept

 2 rows selected.
Now you can release the lock:
 SQL>alter system kill session '7,36';
      System Altered.
 SQL>alter system kill session '9,58';
      System Altered.

To find the CPU consumption

select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command
from
v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.paddr=p.addr
and se.sql_address=q.address
order by ss.value desc,ss.sid

Script to show problem tablespaces

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)< 10
OR next.max_next_extent > free.max_free)
order by pct_used desc

Oracle space monitoring scripts (grand total table space)

select
        sum(tot.bytes/(1024*1024*1024))”Total size”,
        sum(tot.bytes/(1024*1024*1024)-sum(nvl(fre.bytes,0))/(1024*1024*1024)) Used,
        sum(sum(nvl(fre.bytes,0))/(1024*1024*1024)) Free,
        sum((1-sum(nvl(fre.bytes,0))/tot.bytes)*100) Pct
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024*1024), tot.bytes




What's holding up the system?

Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:

Select
   p.pid, s.sid, s.serial#,s.status, s.machine,s.osuser,  p.spid, t.sql_text
  From
    v$session s,
    v$sqltext t,
    v$process p
  Where
    s.sql_address = t.address and
    s.paddr = p.addr and
    s.sql_hash_value = t.hash_value and
    s.sid > 7 and
    s.audsid != userenv ('SESSIONID')
  Order By s.status,s.sid, s.osuser, s.process, t.piece ;

Script to display status of all the Concurrent Managers  
select distinct Concurrent_Process_Id CpId, PID Opid,
       Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
       P.process_status_code Status,
       To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from   Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
where  Q.Application_Id = Queue_Application_ID
  and  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
  and  Spid = Os_Process_ID
  and  Process_Status_Code not in ('K','S')
order  by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name


Get current SQL from SGA

select sql_text
from V$session s , V$sqltext t
where s.sql_address=t.address
and sid=
order by piece;

You can find the SID from V$session.

Monitoring and Tuning the Shared Pool

select
  sum(a.bytes)/(1024*1024) shared_pool_used,
  max(b.value)/(1024*1024) shared_pool_size,
  (max(b.value)/(1024*1024))-(sum(a.bytes)/(1024*1024)) shared_pool_avail,
  (sum(a.bytes)/max(b.value))*100 shared_pool_pct
   from v$sgastat a, v$parameter b
where a.name in (
'reserved stopper',            
'table definiti',                
'dictionary cache',          
'library cache',            
'sql area',
'PL/SQL DIANA',
'SEQ S.O.') and
b.name='shared_pool_size';


What SQL is running and who is running it?

select a.sid,a.serial#,a.username,b.sql_text
from v$session a,v$sqltext b
where a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by 1,2,b.piece;

                   ---
select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
          s.sid "Session ID",s.status "Status",
          s.username "Username", RTRIM(s.osuser) "OS User",
          b.spid "OS Process ID",s.machine "Machine Name",
          s.program  "Program",c.sql_text "SQL text"
   from v$session s, v$session_longops l,v$process b,
        (select address,sql_text from v$sqltext where piece=0) c
 where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
 group by s.sid,s.status,s.username,s.osuser,s.machine,
          s.program,b.spid, b.pid, c.sql_text order by s.status,s.sid

 TO FIND THE SORTING DETAILS
SELECT a.sid,a.value,b.name from
         V$SESSTAT a, V$STATNAME b
         WHERE a.statistic#=b.statistic#
         AND b.name LIKE 'sort%'
         ORDER BY 1;
       


Long running SQL statements

SELECT s.rows_processed, s.loads, s.executions, s.buffer_gets,
       s.disk_reads, t.sql_text,s.module, s.ACTION
 FROM v$sql /*area*/                        s,
      v$sqltext                         t
 WHERE s.address = t.address
   AND ((buffer_gets > 10000000) or
        (disk_reads > 1000000) or
        (executions > 1000000))
 ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece

V$session_longops



SELECT * FROM (select
username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops)
WHERE "% Complete" != 100

Identify an object's locks in the database
Here are two simple scripts to identify an object's locks in the database. Whenever a user complains that there's a session locked, I use these scripts to find out if there are object locks.

# To find locks objects in the database
select c.Owner,c.Object_Name,c.Object_Type,
       b.Sid,b.Serial#,b.Status,b.Osuser,b.Machine
 from v$locked_object a ,v$session b,dba_objects c
 where b.Sid = a.Session_Id
   and a.Object_Id = c.Object_Id;
To find the locks and latches
select s.sid, s.serial#,
       decode(s.process, null,
          decode(substr(p.username,1,1), '?',   upper(s.osuser), p.username),
          decode(       p.username, 'ORACUSR ', upper(s.osuser), s.process)
       ) process,
       nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username,
       decode(s.terminal, null, rtrim(p.terminal, chr(0)),
              upper(s.terminal)) terminal,
       decode(l.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||l.type) type,
       decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX',  6, 'X',
                       to_char(l.lmode) ) lmode,
       decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                         4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest,
       decode(l.type, 'MR', decode(u.name, null,
                            'DICTIONARY OBJECT', u.name||'.'||o.name),
                      'TD', u.name||'.'||o.name,
                      'TM', u.name||'.'||o.name,
                      'RW', 'FILE#='||substr(l.id1,1,3)||
                      ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                      'WL', 'REDO LOG FILE#='||l.id1,
                      'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE',
                                             'NEW BLOCK ALLOCATION'),
                      'ID1='||l.id1||' ID2='||l.id2) object
from   sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,
       sys.v_$process p
where  s.paddr  = p.addr(+)
  and  l.sid    = s.sid
  and  l.id1    = o.obj#(+)
  and  o.owner# = u.user#(+)
  and  l.type   <> 'MR'
UNION ALL                          /*** LATCH HOLDERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
       'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from   sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where  h.pid  = p.pid
  and  p.addr = s.paddr
UNION ALL                         /*** LATCH WAITERS ***/
select s.sid, s.serial#, s.process, s.username, s.terminal,
       'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from   sys.v_$session s, sys.v_$process p, sys.v_$latch l
where  latchwait is not null
  and  p.addr      = s.paddr
  and  p.latchwait = l.addr



To clear the log files:

1) move the alert log file and recreate one dummy alert log file
    using touch command
      path     $ORACLE_HOME/admin/bdump

2) move network log file and recreate one dummy file
location  $ORACLE_HOME/network/admin

3) move the Apache log files (access_log and error_log) and recreate one dummy file
Location $iAS_ORACLE_HOME/Apache/Apache/logs
  4) move the Jserv log file and create one dummy file
 Location $iAS_ORACLE_HOME/Apache/Jserv/logs


=======================================================================

Move a table from one tablespace to another

There are many ways to move a table from one tablespace to another. For example, you can create a duplicate table with dup_tab as select * from original_tab; drop the original table and rename the duplicate table as the original one.

The second option is exp table, drop it from the database and import it back. The third option (which is the one I am most interested in) is as follows.

Suppose you have a dept table in owner scott in the system tablespace and you want to move in Test tablespace.

connect as sys
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           SYSTEM

Elapsed: 00:00:00.50

You want to move DEPT table from system to say test tablespace.
SQL :> connect scott/tiger
Connected.
SQL :> alter table DEPT move tablespace TEST;

Table altered.

Elapsed: 00:00:00.71

SQL :> connect
Enter user-name: sys
Enter password:
Connected.
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           TEST


TKPROF    Command

ALTER SESSION SET sql_trace = TRUE

ALTER SYSTEM SET TIMED_STATISTICS = TRUE
   Session level
ALTER SESSION SET sql_trace = TRUE  (or) ALTER SYSTEM SET sql_trace =  
                                                                                                                             TRUE  
EXECUTE SYS.dbms_system.set_sql_trace_in_session (, , TRUE|FALSE);


TKPROF explain=user/password@service table=sys.plan_table


To find the location of Dump files
    select c.value || '/' || instance || '_ora_' ||
       ltrim(to_char(a.spid,'fm99999')) || '.trc'
  from v$process a, v$session b, v$parameter c, v$thread c
  where a.addr = b.paddr
   and b.audsid = userenv('sessionid')
   and c.name = 'user_dump_dest'


TO find the patch set level
Please follow the note 120638.1

select patch_level
     from fnd_product_installations
     where application_id = 200;
To compile the procedure
Alter  PROCEDURE  JA_IN_BULK_PO_QUOTATION_TAXES compile

To compile the form

         F60gen userid=apps/metroapps@dev module=

.fmb 

         output_file=/forms/US/

.fmx 

         module_type=form batch=no compile_all=special

Compiling DFFs
cd $JA_TOP/4239736

fdfcmp apps/metroapps@dev 0 Y R "INV" "JAF23A_2"

Move all log files to the patch log directory

Find . –name “XXXX” –type f –mtime +90 –exec rm {} \;
Find . –name “XXXX” –type f –mtime +90 –print | sargs cp –R /vasu

find . -name "*.log" | grep -v "/log/" | xargs -i mv {} $JA_TOP/$APPLLOG/4239736


cd %AU_TOP%\resource
ifcmp60 module="JAINTAX.pll" userid=apps\%1 output_file="%AU_TOP%\resource\JAINTAX.plx" module_type=library batch=yes


@REM Forms
echo "Generating forms."

cd %AU_TOP%\forms\US
ifcmp60 module="JAIN57F4.fmb" userid=apps/%1 output_file="%JA_TOP%\forms\US\JAIN57F4.fmx" batch=yes


ifcmp60 module="JAIRGMST.fmb" userid=apps/%1 output_file="%JA_TOP%\forms\US\JAIRGMST.fmx" batch=yes




How to find versions
*************************************************************
This article is being delivered in Draft form and may contain
errors.  Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************

PURPOSE
-------

The purpose of this note is to bring together methods to get version of
programs, executables, forms, reports, database objects and other files
involved in Oracle Applications v. 11.x.

SCOPE & APPLICATION
-------------------

All audience. Version of objects are often needed by support.

CONTENTS
--------
1. Oracle Applications
2. Forms
3. Reports
4. SQL or PL/SQL scripts
5. Executables
6. Other files
7. RDMBS
8. Database objects
9. Operating System

select release_name from fnd_product_groups
select organization_id org_id, name from hr_operating_units;]


Get the JDK version
 [JDK_TOP]/bin/java -version


Example run of adjkey
-------------------------------
cd $APPL_TOP/admin
$ adjkey -initialize

Regenerate Application Jar Files
----------------------------------

Run ADADMIN to Regenerate (sign) the JAR files on each middle tier


1. Launch ADADMIN (Ensure you are APPLMGR with permissions to write to adadmin.log)
2. Choose option number 2 to Maintain Files, then 10 to regenerate JAR Files making sure to select FORCE = Y which will resign every JAR file using the new digital certificate that you just copied over from your original instance.


1. ORACLE APPLICATIONS

a. from any forms you can get Oracle Applications version by this menu option :
Main Menu => Help => About Oracle Applications ...

A pop-up window displays, among other things, version of :

 Oracle Applications
 current used module
 Oracle Forms
 RDBMS
 current open form

b. you can also run the following command :
 sqlplus applsys/
  select release_name from fnd_product_groups;
  select * from fnd_product_installations;

2. FORMS

 a. if the form is displayed, see §1 above to get current open form version
 b. in case of the form doesn't appear you must :

  - retreive the form name from an other environment without the problem
    (i.d. NLS, test or production, etc.) or from WEB IV, Metalink, ARU...
  - go to /forms (/ eventually) directory, you
    should find the corresponding file with .fmx extension
  - see §6 below to get the file version

3. REPORTS

- you need first to note the report name on top of it's log file
- go to /reports(/ eventually) directory
  you should find the corresponding file with .rdf extension
- see §6 below to get the file version

4. SQL OR PL/SQL SCRIPTS

- go to /admin/sql or /patch/110/sql for last version.
  You should find the corresponding file with .sql, .pls, .pkh, or .pkb
  extension
- see §6 below to get the file version

5. EXECUTABLES

- binary or executable names have often no extension on unix systems
  or present .exe or .dll extension on MS-Windows.
- most of them are located under

/bin

- there are many ways to get the version of an executable,
  you can try the following methods :

   a. if an interface is displayed, go to the menu => Help => About ...

      e.g. Oracle Applications, internet browsers, tools (Oracle Forms,
           Oracle Reports, Enterprise Manager, SQL*Plus)
           'Help => About Plug-ins' give JInitiator version

   b. run the file without parameter

      e.g. f45gen, f60gen (for Oracle Forms)
           r25convm, rwcon60 (for Oracle Reports)
           sqlplus (for SQL*Plus)
           tnsping (TNS Ping Utility)
           jre (Java Runtime Loader)
 
   c. see properties of the file with MS-Windows Explorer
      e.g. *.exe, *.dll files
 
   d. find 'Header' string, see §6 to proceed
      e.g. ad utilities (adpatch, adrelink, etc.), fnd executables,
           binaries under /bin directories

   e. run specific command

      e.g. Appletviewer :
             java -version
           Oracle Workflow in Oracle Applications :
             sqlplus apps/ 
             @$FND_TOP/sql/wfver.sql
             or
             select TEXT from WF_RESOURCES where NAME='WF_VERSION';

   f. launch Oracle Installer
 
      Several Oracle products (like RDBMS, Tools) need orainst to be installed,
      below is the way to launch it and get version of products:

      - login with Oracle account
      - run Oracle Installer by :
         .  $ORACLE_HOME/orainst/orainst
        or under MS-Windows :
         $ORACLE_HOME\bin\orainst.exe
      - answer by default to reach 'Software Asset Manager' screen
      - right column shows you installed products and versions

      Same informations are in these files :
       $ORACLE_HOME/orainst/unix.rgs (Unix)
       $ORACLE_HOME\orainst\nt.rgs , windows.rgs (Win NT, MS-Windows)



6. OTHER FILES

- other files could be:
 
 driver files (*.drv)
 object description files (*.odf)
 data files (*.dat)
 library and object files (*.a, *.o)
 Oracle Forms libraries (*.pll, *.plx)
 Oracle Forms menu files (*.mmb, *.mmx)
 form source files (*.fmb)
 jar file (*.jar)
 java class file (*.class)
 html, xlm files (*.htm, *.xlm)

- go to the corresponding directory
- execute one of the following commands to get the version of the file
  on all platforms (beginning with Oracle Applications v. 11.x) :
     adident Header
  on Unix :
     strings -a | grep Header
  on Windows (DOS box) :
     find "Header"


7. RDMBS

 a. See §1.a to get easily the version of your Oracle Server installed

 b. You can also execute sqlplus, it displays SQL*Plus and RDBMS version

   e.g. Oracle8 Enterprise Edition Release 8.0.6.1.

 c. see $5.f if you prefer to use Oracle Installer which displays also
    version of several installed products.

8. DATABASE OBJECTS

 a. run this sql statement to get package version :

   select text from user_source where name='&package_name'
   and text like '%$Header%';

 prompt asks you the package name, in return it gives you two lines
 corresponding to specifications and body creation files

 You can also get pls version on database by running:

 select name , text
 from dba_source
 where text like '%.pls%'
 and line < 10;

 b. views

 Sometimes version information is available in view definition.
 Try the following sql statement :

   col TEXT for a40 head "TEXT"
   select VIEW_NAME, TEXT
   from USER_VIEWS
   where VIEW_NAME = '&VIEW_NAME';

 c. workflow

 Run wfver.sql (see §5.e) to get version of workflow packages and views.

9. OPERATING SYSTEM

 a. for most Unix platforms run command :
     uname -a

 b. for MS-WINDOWS 95/98/2000
   Start => Parameters => Control Panel => System

 c. for WIN/NT, execute command :
   winver

 or menu :
   Start => Programs => Admin Tools => WIN NT Diagnostic

RELATED DOCUMENTS
-----------------

Note 106767.1 How To Determine The Version Of An Applications Form In Rele


ROLL BACK SEGMENTS
dba_rollback_segs
v$transaction
v$rollname
v$undostat
alter index gl_interface_n1 coalesce;
alter index gl_interface_n1 rebuild nologging;




Monitoring Pending Requests in the Concurrent Managers
• FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES
Select *
From   Fnd_Concurrent_Requests R, Fnd_Lookups L
Where  R.Status_Code = L.Lookup_Code
  And  L.Lookup_Type = 'CP_STATUS_CODE'
  And  Phase_Code = 'C'
--  And  Actual_Completion_Date - &DaysPrior
  and meaning in ('Error','Warning')
--  and request_date = '6/13/2006'
--Group BY Meaning;







Tables that are updated when Oracle Applications Concurrent Program is started

FND_CONCURRENT_REQUESTS    This table contains a complete history of  
                           all concurrent requests.

FND_RUN_REQUESTS           When a user submits a report set, this table
                           stores information about the reports in the
                           report set and the parameter values for each
                           report.

FND_CONC_REQUEST_ARGUMENTS This table records arguments passed by the
                           concurrent manager to each program it starts
                           running.

FND_DUAL                   This table records when requests do not
                           update database tables.

FND_CONCURRENT_PROCESSES   This table records information about Oracle
                           Applications and operating system processes.

FND_CONC_STAT_LIST         This table collects runtime performance
                           statistics for concurrent requests.

FND_CONC_STAT_SUMMARY      This table contains the concurrent program
                           performance statistics generated by the
                           Purge.


The lookup for the  output would be:
PHASE CODE:
Value  Meaning
  I     Inactive
  P     Pending
  R     Running
  C     Completed

STATUS CODE:
Value  Meaning
  U     Disabled
  W     Paused
  X     Terminated
  Z     Waiting
  M     No Manager
  Q     Standby
  R     Normal
  S     Suspended
  T     Terminating
  D     Cancelled
  E     Error
  F     Scheduled
  G     Warning
  H     On Hold
  I     Normal
  A     Waiting
  B     Resuming
  C     Normal
For long running requests.
Log onto SQLPLUS as user APPS.  Enter the following command:
update fnd_concurrent_requests set phase_code=‘C’, status_code=’D’  where request_id=reqid;
commit;


su  - applprod
cd  $FND_TOP
cd sql


afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.

afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled.
afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.

Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.
When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.

However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';
conc_stat.sql
set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999    heading "Request ID"
     col exec_time format 999999999 heading "Exec Time|(Minutes)"
    col start_date format a10       heading "Start Date"
     col conc_prog format a20       heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
spool long_running_cr.lst
SELECT
   fcr.request_id request_id,
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
   fcr.actual_start_date start_date,
   fcp.concurrent_program_name conc_prog,
   fcpt.user_concurrent_program_name user_conc_prog
FROM
  fnd_concurrent_programs fcp,
  fnd_concurrent_programs_tl fcpt,
  fnd_concurrent_requests fcr
WHERE
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
   fcr.concurrent_program_id = fcp.concurrent_program_id
and
   fcr.program_application_id = fcp.application_id
and
   fcr.concurrent_program_id = fcpt.concurrent_program_id
and
   fcr.program_application_id = fcpt.application_id
and
   fcpt.language = USERENV('Lang')
ORDER BY
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
         
spool off
Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.
Enter          value for min: 60

Locations of Major Configuration Information in EPM 11.1.2.1

Applies to: Hyperion Essbase Administration Services - Version 11.1.2.1.000 and later Hyperion Financial Management - Version 11.1.2.1.000 and later Information in this document applies to any platform. Access to Hyperion Registry configuration information is usually undertaken via epmsys_registry.bat|.sh or via Metadata in Hyperion Shared Services. Purpose This article points to the few remaining configuration files that are still used in Enterprise Performance Management 11.1.2.1.This is presumably not a complete list, however. It is useful to relate findings in logs to configuration settings elsewhere in the products. It is also very helpful to increase the logging levels against specific servlets or servers by adjustments to the matching logging.xml files Troubleshooting Steps Oracle Hyperion Enterprise Performance Management 11.1.2.1 No. Filename Location Description 1. reg.properties /Oracle/Middleware/user_projects/epmsystem1/config/foundation/11.1.2.0/ jdbc.url, username accessing database repository 2. registry.xml \Oracle\Middleware Provides precise version of WebLogic application server. 3. .product.properties \Oracle\Middleware\wlserver_10.3 Hidden file contains: WLS_JAVA_HOME, MW_HOME, EPM_ORACLE_HOME, WLS_PRODUCT_VERSION, JAVA_MEM_ARGS 4. epmsys_registry.sh|.bat /Oracle/Middleware/user_projects/epmsystem1/bin This may be used to display or modify Hyperion registry settings. Run on its own it generates an HTML dump of the Hyperion registry in /Middleware/user_projects/epmsystem1/diagnostics/reports/registry.html 5. startconfigtool.bat startconfigtool-manual.bat \Oracle\Middleware\EPMSystem11R1\common\config\11.1.2.0 Configuration Utility 6. Essbase.properties datasources.xml, AnalyticProviderServices.properties, BPMS_bpms1_Server.properties. CalcMgr.properties, EisServer.properties, EpmaDataSync.properties, EpmaWebReports.properties, EssbaseAdminServices.properties, FinancialReporting.properties, FoundationServices.properties, ocm.properties, OHS.properties, Planning.properties, RaFrameworkAgent.properties, RaFramework.properties, RMI.properties \Oracle\Middleware\user_projects\epmsystem1\aps\bin \Oracle\Middleware\user_projects\epmsystem1\config\starter Essbase configuration file; system.session.timeout, smartview properties 7. BpmServer.properties \Oracle\Middleware\user_projects\domains\EPMSystem\servers\FoundationServices0 or RaFramework0\tmp\servers\Foundation or RaFramework\ {etc} Temporary files indicating localization settings. 8. RMService8.properties \Oracle\Middleware\EPMSystem11R1\products\biplus\common\config CHECK_SERVICE_STARTUP for startup dependencies. 9. BPMA_Server_Config.xml C:\Oracle\Middleware\EPMSystem11R1\products\Foundation\BPMA\AppServer\DimensionServer\ServerEngine\bin DimensionServerPort 10. web.config C:\Oracle\Middleware\EPMSystem11R1\products\Foundation\BPMA\AppServer\DimensionServer\WebService DimensionServerPort 11. ADM.properties \Oracle\Middleware\EPMSystem11R1\common\ADM\11.1.2.0\lib ADM_RMI_PORT=8299 port number; MAX_PROPERTY_VALUE_LENGTH=4; LOAD_MDX_METADATA. 12. httpd.conf \Oracle\Middleware\user_projects\epmsystem1\httpConfig\ohs\config\OHS\ohs_component Oracle HTTP Server configuration: Aliases, ODL logging settings, timeout, Listen {port}, LoadModule, et cetera 13. mod_wl_ohs.conf, ssl.conf \Oracle\Middleware\user_projects\epmsystem1\httpConfig\ohs\config\OHS\ohs_component LocationMatch, WLIOTimeoutSecs, Idempotent, WeblogicCluster, port; Listen, ProxyPreserveHost 14. logging.xml \Oracle\Middleware\user_projects\epmsystem1\config (\FoundationServices or ReportingAnalysis\Converter or ReportingAnalysis\MigrationUtility or \SDK or \syncCSSId or \validation) \Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin \oracle\Middleware\user_projects\epmsystem1\BPMS\bpms1\bin Oracle Diagnostic Logging is configured in \Oracle\Middleware\user_projects\epmsystem1\config\*\logging.xml files for servers 15. logging.xml \Oracle\Middleware\user_projects\domains\EPMSyem\config\fmwconfig\servers\WebAnalysis0 or \Oracle\Middleware\user_projects\domains\EPMSystem\config\fmwconfig\servers\(AdminServer or AdminServer\jboss or AdminServer\was\AnalyticProviderServices0 or EssbaseAdminServices0, FinancialReporting0, FMWebServices0, FoundationServices0 or Planning0 or RaFramework0 or WebAnalysis0) Oracle Diagnostic Logging Configuration files for servlets 16. web.config \Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Web\HFMOfficeProvider or HFMLCMService or HFMServices or HFMApplicationService appSettings, diagnostics 17. oraInst.loc Windows: C:\Program Files\Oracle\Inventory\logs. Unix: oraInst.loc file is generally in the /etc folder  Central Inventory location is specified in theoraInst.loc 18. upgrade.properties /Oracle/Middleware/EPMSystem11R1/upgrades/raframework/upgrade.properties   On Microsoft Windows, configuration information is kept in Windows Registry. HKEY_LOCAL_MACHINE\SOFTWARE\*Oracle Corporation, Hyperion Java Service or Hyperion Solutions, HKEY_LOCAL_MACHINE\SYSTEM\ControlSet003\Services The former CMC functionality shifted to the Hyperion Shared Services User Interface.

Preparing a Microsoft Windows Server for Installation of EPM 11.1.2.x

Hyperion Essbase - Version 11.1.2.1.000 to 11.1.2.2.000 [Release 11.1]
Hyperion BI+ - Version 11.1.2.0.00 to 11.1.2.2.000 [Release 11.1]
Hyperion Planning - Version 11.1.2.0.00 to 11.1.2.2.000 [Release 11.1]
Microsoft Windows x64 (64-bit) - Version: 2008 R2
The compression/decompression utility should be capable of handling long file paths (a free utility which fulfills this requirement may be downloaded from http://www.7-zip.org)

Database clients (in a preferred 64-bit environment) should be installed in the order of 32-bit then 64-bit. Products which require 32-bit clients include Interactive Reporting and Financial Data Quality Management.

Goal

Many applications can be installed in EPM 11.1.2.0 and EPM 11.1.2.1 environments. These require careful preparation to prevent installation and configuration failures. This article makes some best practice recommendations that will allow a new installation to be robust. The EPM 11.1.2.0 release did not offer upgrade or migration features, so many customers are expected to move up to and take advantage of the new features of EPM 11.1.2.1. In most cases customers who have production environments containing earlier versions of the Hyperion System 9 or Oracle EPM 11 families will need to and want to move to higher specification environments to meet the certification requirements of the environment of this set of products. 

Fix

(1) GREENFIELD/CLEAN INSTALL
It is safest to do a clean install. An install on top of a pre-existing Hyperion System 9.2.1, 9.3.3, or 11.1.1.3 installation would lead to the loss of prior data and configuration files and lead to a new environment. That is supported, but ensure all files and repository data are preserved if the EPM 11.1.2.x install is unsuccessful and one needed to revert to the prior install.

(2) MICROSOFT WINDOWS VERSION
Earlier versions of Hyperion System 9 and Oracle Hyperion EPM 11.1.1.x were not certified against currently supported operating systems and databases so it is unlikely that they were installed in environments certified to work with EPM 11.1.2.1.

Since many of the applications of EPM 11.1.2.1 are 64-bit savvy and could benefit from the optimization features of Microsoft Windows 2008 R2 64-bit...that would be recommended.

(3) COMPRESSION/DECOMPRESSION

As mentioned above, ensure that your compression/decompression utility will handle long file path names (greater than the 260 character Microsoft Windows path limitation). 

http://msdn.microsoft.com/en-us/library/aa365247%28VS.85%29.aspx#maxpath

(4) TURN OFF MICROSOFT'S USER ACCOUNT CONTROL

UAC (Microsoft User Account Control) is a feature of Microsoft Vista and Windows 2008.



When the hyperlink is clicked, the next window should NOT have a check box checked.



The screen layout for Microsoft Windows 2008 R2 is:

(5) ENSURE MEMORY AND CPU NUMBERS ARE ADEQUATE

If all Hyperion products that can be accessed via Oracle EPM Foundation 11.1.2.x were installed and activated it could easily require around 14 gigabytes or more of RAM. This pretty much eliminates consideration of a 32-bit install (where each machine can access at best 4 gigabytes of memory...and no less than 1 gigabyte of that would be for the operating system). The processing load for many separate Java virtual machines would also make it practical to have four or more CPUs. The memory and CPU requirements might be reduced somewhat if fewer applications or JVMs were run, but it would not be advisable to run even a test and development install with fewer than two CPUs and 8 gigabytes of RAM as there are around two dozen processes to support. Technical support is not equipped to estimate the actual production capacity of an environment...there are too many variables involved. It would be best to ramp up the system with actual processes and loads and project from that.

(6) PREPARING DATABASE CLIENTS

The following applications require the installation of a full Oracle database client on the machines where they will be installed.

Performance Management Architect Dimension server
Financial Management application server
FDM Application Server and any machine that has FDM Workbench
Strategic Finance

If an Oracle database is used, a full database client with Oracle Call Interface (Oracle 11.1.0.6 or later) must be installed. Ensure a Net Service Name/tnsnames.ora is configured for remote databases.

(7) PREPARING DATABASE REPOSITORIES

A number of different relational database repositories must be prepared if their matching applications will be configured, preferably with different schema owners. Ideally the database will be on a separate stand alone server behind a firewall.

• CalcManager
• DisclosureManagement
• EnterprisePerformanceManagementArchitect
• ERPIntegrator
• Essbase
• FDM
• FinancialClose
• FinancialManagement
• PerformanceScorecard
• PlanningApplication
• PlanningSystem
• ProfitabilityAndCostManagement
• ReportingAndAnalysis
• SharedServices

ORACLE DATABASE REPOSITORY:
Server versions supported include: Oracle 10.2.0.4+, 11.1.0.7+, or 11.2.0.1+
HFM, HSS, and/or EPMA instances require at least 1GB of RAM and Automatic Memory Management.
The database encoding should preferably be AL32UTF8 (UTF8 is fallback option) and NLS_NUMERIC_CHARACTERS should be in order ',.' (confirm by running: SELECT * FROM NLS_DATABASE_PARAMETERS;)
Each EPM user must have the RESOURCE role and the CREATE SESSION and
CREATE VIEW privileges.

Note that Oracle recommends a separate Oracle database instance and other specific adjustments to work with FDM.

Oracle Data Provider (ODP) for .NET 2.0 (from the Oracle Data Access Component (ODAC) package) is required and must be installed by a user with Windows administrator rights for the following products: FDM or Performance Management Architect Dimension Server.

MICROSOFT SQL SERVER DATABASE REPOSITORY
Microsoft SQL Server 2008 R2 is certified.
Run the following two commands against each database used by EPM:
alter database set READ_COMMITTED_SNAPSHOT ON
alter database set ALLOW_SNAPSHOT_ISOLATION ON

IBM DB2 DATABASE REPOSITORY
IBM DB2 9.7 FP3a+ is certified.

(8) MICROSOFT INTERNET INFORMATION SERVER

For Microsoft Windows 2008 servers, ensure that IIS 7 is installed with IIS 6 compatibility features (needed for HFM). Ensure that ASP.NET has been installed as well. In Windows 2008: Start > All Programs > Administrative Tools > Server Manager (or use icon in tool-bar) > Roles Summary > click Add Roles > ...complete verifications... > On Select Server Roles screen select Web Server (IIS) > et cetera.

(9) PREPARE A DOMAIN USER FOR INSTALL

The Microsoft Windows Services control panel should have a domain user with rights to start a service as the owner of each Oracle EPM service, so that user should be determined before installation.

(10) CONFIGURE INTERNET EXPLORER 8 (if not using Firefox)

Internet Options > Security Settings tab > Custom level...
Allow script-initiated windows without size or position constraints (Enable)
Allow websites to open windows without address or status bar (Enable)

Internet Options > Security tab > Enable Protected Mode (Uncheck)

If multiple open windows are needed: http://forums.oracle.com/forums/thread.jspa?messageID=9391075

(11) INSTALL A 32-BIT GNU (7.06) or AFPL (8.5.4 or 8.51or 8.14 ) GHOSTSCRIPT OR ADOBE ACROBAT DISTILLER (6.0 or 8.0) FOR VERSIONS BEFORE 11.1.2.2.0

This is needed for Oracle Hyperion Financial Report PDF output.

Enhanced display of charts (via Adobe SVG Viewer) is only possible when using Adobe Acrobat Distiller.

These PDF generation tools are no longer required from version 11.1.2.2.0 and later.

(12) ENSURE THERE ARE NO SPACES IN THE INSTALL PATH

(13) REMOTE DIAGNOSTIC AGENT 4.28 AND LATER MAY BE USED FOR PREINSTALL CHECK OF EPM SERVER OR CLIENT: rda.cmd -T hcve (case sensitive)

RDA 4.27 supports EPM CLIENT preinstall and RDA 4.28 support EPM SERVER preinstall. CLIENT hcve (health check validation engine) is only valid on Microsoft Windows platforms. SERVER hcve is valid on all platforms certified for EPM 11.1.2.x installs.

Why does the emCCR cron job run constantly?

    Why does the emCCR cron job run constantly?


Oracle Configuration Manager - Version: 10.2.7.0 to 10.3.1
Information in this document applies to any platform.
***Checked for relevance on 16-FEB-2012***

OSS Support Tools - Version: 3.0
Goal

Explain why the emCCR cron job runs constantly.
Solution

After OCM is installed by whatever means into an ORACLE_HOME a cron job similar to the following may be present:



  0,15,30,45 * * * * JAVA_HOME=/u00/app/oracle/product/10.2.0.3/jdk /u00/app/oracle/product/10.2.0.3/ccr/bin/emCCR -silent start



The emCCR cron job is ensuring the OCM (and thus the internal OCM scheduler) is running.  When configured, the OCM process should always be running in the background so that the collection occurs every 24 hours by default.

If the emCCR cron job detects that OCM is running, it does nothing; if OCM is not running, the cron job starts it.  The job runs every 15 minutes.

How to Perform a Health Check on the Database

       How to Perform a Health Check on the Database



Applies to:

Oracle Server - Enterprise Edition - Version 7.3.4.0 and later
Information in this document applies to any platform.



Purpose



This article explains how to perform a BASIC Health Check on the database verifying
several configuration issues.  General guidelines are given on what areas to investigate
to get a better overview on how the database is working and evolving. These guidelines
will reveal common issues regarding configuration as well as problems that may occur in the future.
For a more in depth health check to check Database structure and data dictionary integrity,
please follow the appropriate links in chapter 11.
The areas investigated here are mostly based on scripts and are brought to you without
any warranty, these scripts may need to be adapted for next database releases and features.
This article will probably need to be extended to serve specific application need0s/checks.
Although some performance areas are discussed in this article, it is not the intention
of this article to give a full detailed explanation of optimizing the database performance.
Scope

1. Parameter file
2. Controlfiles
3. Redolog files
4. Archiving
5. Datafiles
  5.1 Autoextend
  5.2 Location
6. Tablespaces
  6.1 SYSTEM Tablespace
  6.2 SYSAUX Tablespace (10g Release and above)
  6.3 Locally vs Dictionary Managed Tablespaces
  6.4 Temporary Tablespace
  6.5 Tablespace Fragmentation
7. Objects
  7.1 Number of Extents
  7.2 Next extent
  7.3 Indexes
8. AUTO vs MANUAL undo
  8.1 AUTO UNDO
  8.2 MANUAL UNDO
9. Memory Management
  9.1 Pre Oracle 9i
  9.2 Oracle 9i
  9.3 Oracle 10g
  9.4 Oracle 11g
10. Logging & Tracing
  10.1 Alert File
  10.2 Max_dump_file_size
  10.3 User and core dump size parameters
  10.4 Audit files
11. Advanced Health Checking

Details

1. Parameter file

The parameter file can exists in 2 forms. First of all we have the text-based version, commonly referred to as init.ora or pfile, and a binary-based file, commonly referred to as spfile. The pfile can be adjusted using a standard Operating System editor, while the spfile needs to be managed through the instance itself.

It is important to realize that the spfile takes presedence above the pfile, meaning whenever there is an spfile available this will be automatically taken unless specified otherwise.

NOTE: Getting an RDA report after making changes to the database configuration is also a recommendation. Keeping historical RDA reports will ensure you have an overview of the database configuration as the database evolves.

Reference:
Note 249664.1 Pfile vs SPfile

2. Controlfiles

It is highly recommended to have at least two copies of the controlfile. This can be done by mirroring the controlfile, strongly recommended on different physical disks. If a controlfile is lost, due to a disk crash for example, then you can use the mirrored file to startup the database. In this way fast and easy recovery
from controlfile loss is obtained.

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

STATUS NAME
------- ---------------------------------
/u01/oradata/L102/control01.ctl
/u02/oradata/L102/control02.ctl

The location and the number of controlfiles can be controlled by the 'control_files' initialization parameter.

3. Redolog files

The Oracle server maintains online redo log files to minimize loss of data in the database. Redo log files are used in a situation such as instance failure to recover commited data that has not yet been written to the data files. Mirroring the redo log files, strongly recommended on different physical disks, makes recovery more easy in case one of the redo log files is lost due to a disk crash, user delete, etc.

connect as sysdba
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
--------- ------- ------ -----------------------------------
1 ONLINE /u01/oradata/L102/redo01_A.log
1 ONLINE /u02/oradata/L102/redo01_B.log

2 ONLINE /u01/oradata/L102/redo02_A.log
2 ONLINE /u02/oradata/L102/redo02_B.log

3 ONLINE /u01/oradata/L102/redo03_A.log
3 ONLINE /u02/oradata/L102/redo03_B.log

At least two redo log groups are required, although it is advisable to have at least three redo log groups when archiving is enabled (see the following chapter). It is common, in environments where there are intensive log switches, to see the ARCHiver background process fall behind of the LGWR background process. In this case the LGWR process needs to wait for the ARCH process to complete archiving the redo log file.

References:
Note 102995.1 Maintenance of Online Redo Log Groups and Members

4. Archiving

Archiving provides the mechanism needed to backup the changes of the database. The archive files are essential in providing the necessary information to recover the database. It is advisable to run the database in archive log mode, although you may have reasons for not doing this, for example in case of a TEST environment where you accept to loose the changes made between the current time and the last backup.
You may ignore this chapter when the database doesn't run in archive log mode.

There are several ways of checking the archive configuration, below is one of them:

connect as sysdba
SQL> archive log list

Database log mode No Archive Mode --OR-- Archive Mode
Automatic archival Disabled --OR-- Enabled
Archive destination --OR-- USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence seq. no
Current log sequence seq. no

Pre-10g, if the database is running in archive log mode but the automatic archiver process is disabled, then you were required to manually archive the redolog files.
If this is not done in time then the database is frozen and any activity is prevented.
Therefore you should enable automatic archiving when the database is running in archive log mode. This can be done by setting the 'log_archive_start' parameter to true in the parameter file.
Starting from 10g, this parameter became obsolete and is no longer required to be set explicitly. It is important that there is enough free space on the dedicated disk(s) for the archive files, otherwise the ARCHiver process can't write and a crash is inevitable.

References:
Note 69739.1 How to Turn Archiving ON and OFF
Note 122555.1 Determine how many disk space is needed for the archive files

5. Datafiles

5.1 Autoextend

The autoextend command option enables or disables the automatic extension of data files. If the given datafile is unable to allocate the space needed, it can increase the size of the datafile to make space for objects to grow.

A standard Oracle datafile can have, at most, 4194303 Oracle datablocks.
So this also implies that the maximum size is dependant on the Oracle Block size used.

DB_BLOCK_SIZE Max Mb value to use in any command
~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2048 8191 M
4096 16383 M
8192 32767 M
16384 65535 M

Starting from Oracle 10g, we have a new functionality called BIGFILE, which allows for bigger files to be created. Please also consider that every Operating System has its limits, therefore you should make sure that the maximum size of a datafile cannot be extended past the Operating System allowed limit.

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';

References:
Note 112011.1 ALERT: RESIZE or AUTOEXTEND can "Over-size" Datafiles and Corrupt the Dictionary
Note 262472.1 10g BIGFILE Type Tablespaces Versus SMALLFILE Type

5.2 Location

Verify the location of your datafiles. Overtime a database will grow and datafiles may be added to the database. Avoid placing datafiles on a 'wherever there is space' basis as this will complicate backup strategies and maintenance.

Below is an example of bad usage:

SQL> select * from v$dbfile;

FILE# NAME
--------- --------------------------------------------------
1 D:\DATABASE\SYS1D806.DBF
2 D:\DATABASE\D806\RBS1D806.DBF
3 D:\DATABASE\D806\TMP1D806.DBF
5 D:\DATABASE\D806\USR1D806.DBF
6 D:\USR2D806.DBF
7 F:\ORACLE\USR3D806.DBF

References:
Note 115424.1 How to Rename or Move Datafiles and Logfiles

6. Tablespaces

6.1 SYSTEM Tablespace

User objects should not be created in the system tablespace. Doing so can lead to unnecessary fragmentation and preventing system tables of growing. The following query returns a list of objects that are created in the system tablespace but not owned by SYS or SYSTEM.

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


6.2 SYSAUX Tablespace (10g Release and above)

The SYSAUX tablespace was automatically installed as an auxiliary tablespace to the SYSTEM tablespace when you created or upgraded the database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.

If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

The amount of data stored in this tablespace can be significant and may grow over time to unmanageble sizes if not configured properly. There are a few components that need special attention.

To check which components are occupying space:

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

Reference:
Note 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER

6.3 Locally vs Dictionary Managed Tablespaces

Locally Managed Tablespaces are available since Oracle 8i, however they became the default starting from Oracle 9i. Locally Managed Tablespaces, also referred to as LMT, have some advantage over Data Dictionary managed tablespaces.

To verify which tablespace is Locally Managed or Dictionary Managed, you can run the following query:

SQL> select tablespace_name, extent_management
from dba_tablespaces;

References:
Note 93771.1 Introduction to Locally-Managed Tablespaces
Note 105120.1Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

6.4 Temporary Tablespace

* Locally Managed Tablespaces use tempfiles to serve the temporary tablespace, whereas Dictionary Managed Tablespaces use a tablespace of the type temporary. When you are running an older version (pre Oracle 9i), then it is important to check the type of tablespace used to store the temporary segments. By default, all tablespaces are created as PERMANENT, therefore you should make sure that the tablespace dedicated for temporary segments is of the type TEMPORARY.

SQL> select tablespace_name, contents
from dba_tablespaces;

TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
USER_DATA PERMANENT
ROLLBACK_DATA PERMANENT
TEMPORARY_DATA TEMPORARY


* Make sure that the users on the database are assigned a tablespace of the type temporary. The following query lists all the 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';

Note: User SYS and SYSTEM will show the SYSTEM tablespace as there default temporary tablespace. This value can be altered as well to prevent fragmentation in the SYSTEM tablespace.

SQL> alter user SYSTEM temporary tablespace TEMP


*The space allocated in the temporary tablespace is reused. This is done for performance reasons to avoid the bottleneck of constant allocating and de-allocating of extents and segments. Therefore when looking at the free space in the temporary tablespace, this may appear as full all the time. The following are a few queries that can be used to list more meaningful information about the temporary segment usage:

This will give the size of the temporary tablespace:

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

This will give 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;

This will give 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;

6.5 Tablespace Fragmentation

Heavly fragmented tablespaces can have an impact on the performance, especially when a lot of Full Table Scans are occurring on the system. Another disadvantage of fragmentation is that you can get out-of-space errors while the total sum of all free space is much more then you had requested.

The only way to resolve fragmentation is recreate the object. As of Oracle8i you can use the 'alter table .. move' command. Prior to Oracle8i you could use export/import.

If you need to defragment your system tablespace, you must rebuild the whole database since it is NOT possible to drop the system tablespace.

References:
Note 1020182.6 - SCRIPT to detect tablespace fragmentation
Note 1012431.6 - Common causes of Fragmentation
Note 147356.1 - How to Move Tables from One Tablespace to Another.

7. Objects

7.1 Number of Extents

While the performance hit on over extended objects is not significant, the aggregate effect on many over extended objects does impact performance. The following query will list all the 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;

7.2 Next extent

It is important that segments can grow and therefore allocate their next extent when needed. If there is not enough free space in the tablespace then the next extent can not be allocated and the object will fail to grow. The following query returns all the 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);


Note that if there is a lot of fragmentation in the tablespace, then this query may give you objects that still are able to grow. The above query is based on the largest free chunk in the tablespace available. If there are a lot of  'small' free chunks after each other, then Oracle will coalesce these to serve the extent allocation.

Therefore it can be interesting to adapt the script in Note 1020182.6 'SCRIPT to detect tablespace fragmentation' to compare the next extent for each object with the 'contiguous' bytes (table space_temp) in the tablespace.

7.3 Indexes

The need to rebuild an index is very rare and often the coalescing the index is a better option. Please see the following article for a full explanation:

Reference:
Note 989093.1: Index Rebuild, the Need vs the Implications
Note 989186.1: Script to investigate a b-tree index structure

8. AUTO vs MANUAL undo

Starting from Oracle 9i we introduced a new way of managing the before-images. Previously this was achieved through the RollBack Segments or also referred to as manual undo. Automatic undo is used when the UNDO_MANAGEMENT parameter is set to AUTO. When not set or set to MANUAL then we use the 'old' rollback segment mechanism. Although both versions are still available in current release, automatic undo is preferred.

8.1 AUTO UNDO

There is little to no configuration involved to AUM (Automatic Undo Management). You basically define the amount of time the before image needs to be kept available. This is controlled through the parameter UNDO_RETENTION, defined in seconds. So a value of 900 indicates 15 minutes.

It is important to realize that this value is not honored when we are under space pressure in the undo tablespace.

Therefore the following formula can be used to calculate the optimal undo tablespace size:

Note 262066.1: How To Size UNDO Tablespace For Automatic Undo Management

Starting from Oracle 10g, you may choose to use the GUARANTEE option, to make sure the undo information does not get overwritten before the defined undo_retention time.

Note 311615.1: Oracle 10G new feature - Automatic Undo Retention Tuning

8.2 MANUAL UNDO

* Damaged rollback segments will prevent the instance to open the database. Only if names of rollback segments are known, corrective action can be taken. Therefore specify all the rollback segments in the 'rollback_segments' parameter in the init.ora

* Too small or not enough rollback segments can have serious impact on the behavior of your database. Therefore several issues must be taken into account. The following query will show you 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;

SEGMENT_NAME TABLESPACE_NAME WAITS SHRINKS WRAPS STATUS
--------------- ------------------ ----- --------- --------- --------
RB1 ROLLBACK_DATA 1 0 160 ONLINE
RB2 ROLLBACK_DATA 31 1 149 ONLINE
SYSTEM SYSTEM 0 0 0 ONLINE

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.

Reference:
Note 62005.1 Creating, Optimizing, and Understanding Rollback Segments

9. Memory Management

This chapter is very version driven. Depending on which version you are running the option available will be different. Overtime Oracle has invested a great deal of time and effort in managing the memory more efficiently and transparently for the end-user. Therefore it is advisable to use the automation features as much as possible.

9.1 Pre Oracle 9i

The different memory components (SGA & PGA) needed to be defined at the startup of the database. These values were static. So if one of the memory components was too low the database needed to be restarted to make the changes effective.
How to determine the optimal or best value for the different memory components is not covered in this note, since this would lead us too far. However a parameter that was often misused in these versions is the sort_area_size.

The 'sort_area_size' parameter in the init.ora defines the amount of memory that can be used for sorting. This value should be chosen carefully since this is part of the User Global Area (UGA) and therefore is allocated for each user individually.
If there are a lot of concurrent users performing large sort operation on the database then the system can run out of memory.

E.g.: You have a sort_area_size of 1Mb, with 200 concurrent users on the database.  Although this memory is allocated dynamically, it can allocate up to 200Mb and therefore can cause extensive swapping on the system.

9.2 Oracle 9i

Starting from Oracle 9i we introduced the parameters:

workarea_size_policy = [AUTO | MANUAL]
pga_aggregate_target =

This allows you define 1 pool for the PGA memory, which will be shared across sessions.
When you often receive ORA-4030 errors, then this can be an indication that this value is specified too low.

9.3 Oracle 10g

Automatic Shared Memory Management (ASMM) was introduced in 10g. The automatic shared memory
management feature is enabled by setting the SGA_TARGET parameter to a non-zero value.

This feature has the advantage that you can share memory resources among the different components.
Resources will be allocated and deallocated as needed by Oracle automatically.

Automatic PGA Memory management is still available through the 'workarea_size_policy' and
'pga_aggregate_target' parameters.

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


Reference:
Note 443746.1 Automatic Memory Management(AMM) on 11g

10. Logging & Tracing

10.1 Alert File

The alert log file of the database is written chronologically. Data is always appended and therefore this file can grow to an enormous size. It should be cleared or truncated on a regular basis, as a large alert file occupies unnecessary disk space and can slow down OS write performance to the file.


Pre-11g:

SQL> show parameter background_dump_dest

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

11g and above:

SQL> show parameter diagnostic_dest

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


10.2 Max_dump_file_size

Oracle Server processes generate trace files for certain errors or conflicts. These trace files are of use for further analyzing the problem. The init.ora parameter 'max_dump_file_size' limits the size of these trace files. The value of this parameter should be specified in Operating System blocks.
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


10.3 User and core dump size parameters

The parameters 'user_dump_dest' and 'core_dump_dest' can contain a lot of trace information.
It is important to clear this directory at regular times as this can take up a significant amount of space.

Note: starting from Oracle 11g, this location is controlled by the 'diagnostic_dest' parameter

Reference:
Note 564989.1 How To Truncate a Background Trace File Without Bouncing the Database

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

11. Advanced Health Checking

The previous chapters have been outlining the basic items to check to prevent common database cavehats. In this section you will find references to several 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.