Oracle DB Admin Advance

1. Listener Adm.
2. How to check session/process utilization.
3. Information about upgrades, downgrades, and critical patch updates that have been performed on the database
4. Resize tablespace script
5. Optimize Oracle UNDO Parameters

1. Listener Adm.

Static Listener Registration

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = XE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = XE)
)
)

Invoke the database to register it sefl back to the listener

alter system register;
http://www.dba-oracle.com/oracle_news/2005_4_28_use_alter_system_register_command.htm

2. How to check session/process utilization.

 set colsep “|”
 set linesize 32767
 set pagesize 10000
 COLUMN RESOURCE_NAME        FORMAT A25
 COLUMN CURRENT_UTILIZATION  FORMAT 99999
 COLUMN MAX_UTILIZATION      FORMAT 99999
 COLUMN INITIAL_ALLOCATION   FORMAT A25
 COLUMN LIMIT_VALUE          FORMAT A25
 SELECT * FROM v$resource_limit;

 set colsep “|”
 set linesize 32767
 set pagesize 10000
 COLUMN RESOURCE_NAME        FORMAT A25
 COLUMN CURRENT_UTILIZATION  FORMAT 99999
 COLUMN MAX_UTILIZATION      FORMAT 99999
 COLUMN INITIAL_ALLOCATION   FORMAT A25
 COLUMN LIMIT_VALUE          FORMAT A25
 SELECT * FROM gv$resource_limit
 WHERE RESOURCE_NAME IN ('processes','sessions','transactions')
 ORDER BY RESOURCE_NAME;

3. Information about upgrades, downgrades, and critical patch updates that have been performed on the database

set colsep “|”
set linesize 32767
set pagesize 10000
COLUMN ACTION_TIME  FORMAT A25
COLUMN ACTION       FORMAT A25
COLUMN NAMESPACE    FORMAT A25
COLUMN VERSION      FORMAT A25
COLUMN ID           FORMAT 999999
COLUMN COMMENTS     FORMAT A25
SELECT * FROM DBA_REGISTRY_HISTORY;

CTION_TIME              “|”ACTION                   “|”NAMESPACE                “|”VERSION                  “|”     ID“|”BUNDLE_SERIES             “|”COMMENTS
-------------------------“|”-------------------------“|”-------------------------“|”-------------------------“|”-------“|”------------------------------“|”-------------------------
16-JUL-13 07.20.17.333069“|”APPLY                    “|”SERVER                   “|”11.2.0.3                 “|”      0“|”PSU      “|”Patchset 11.2.0.2.0
 PM                      “|”                         “|”                         “|”                         “|”       “|”         “|”

4. Resize tablespace script

SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*  8192 )/1024/1024 ) || 'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS* 8192 /1024/1024)- CEIL((NVL(HWM,1)*  8192 )/1024/1024 ) > 0;

5. Optimize Oracle UNDO Parameters

 Actual Undo Size

    SELECT SUM(a.bytes) "UNDO_SIZE"
      FROM v$datafile a,
           v$tablespace b,
           dba_tablespaces c
     WHERE c.contents = 'UNDO'
       AND c.status = 'ONLINE'
       AND b.name = c.tablespace_name
       AND a.ts# = b.ts#;

     UNDO_SIZE
    ----------
      209715200

Undo Blocks per Second

    SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
          "UNDO_BLOCK_PER_SEC"
      FROM v$undostat;

    UNDO_BLOCK_PER_SEC
    ------------------
            3.12166667

DB Block Size

    SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
     FROM v$parameter
    WHERE name = 'db_block_size';

    DB_BLOCK_SIZE [Byte]
    --------------------
                    4096

Optimal Undo Retention

    209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]

    Using Inline Views, you can do all in one query!

    SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
           SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
           ROUND((d.undo_size / (to_number(f.value) *
           g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
      FROM (
           SELECT SUM(a.bytes) undo_size
              FROM v$datafile a,
                   v$tablespace b,
                   dba_tablespaces c
             WHERE c.contents = 'UNDO'
               AND c.status = 'ONLINE'
               AND b.name = c.tablespace_name
               AND a.ts# = b.ts#
           ) d,
           v$parameter e,
           v$parameter f,
           (
           SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
                  undo_block_per_sec
             FROM v$undostat
           ) g
    WHERE e.name = 'undo_retention'
      AND f.name = 'db_block_size'
    /

    ACTUAL UNDO SIZE [MByte]
    ------------------------
    200

    UNDO RETENTION [Sec]
    --------------------
    10800

    OPTIMAL UNDO RETENTION [Sec]
    ----------------------------
    16401

http://www.akadia.com/services/ora_optimize_undo.html