Monitoring Database Initialization Parameters

Oracle Database Tips by Donald Burleson

The database initialization parameters are critical parts of the database. I have seen many installations where administrators were very diligent about database backups but neglected to document the settings of their initialization parameters. At some installations DBAs?or should I say DBBSs (database babysitters)?didn't even know the location of the init<SID>.ora file.

The documentation of the parameters has been covered in earlier sections; with the advent of Oracle9i, the v$parameter file provides the source for discovering the value and status of any documented initialization file parameter in effect for the current session (previously it was for the instance.) Oracle9i also added the V$PARAMETER2, V$SYSTEM_PARAMETER, and V$SYSTEM_PARAMETER2 views. The V$SYSTEM_PARAMETERS view shows the instancewide parameter values. The V$PARAMETER2 and V$SYSTEM_PARAMETER2 views show the same parameters as their nonnumbered counterparts, except that any multivalues string (such as CONTROL_FILE or ROLLBACK_SEGMENT) will have an individual listing for each value of the string, differentiated by an ORDINAL column to tell you the order that the substring occurred within the master string. A simple script to generate a nearly ready-for-prime-time init<SID>.ora file is listed in Source 11.34. Of course, with Oracle9i, you can use the CREATE PFILE command if you have a current SPFILE to generate a parameter file listing. An example of output from this script is shown in Listing 11.29.

SOURCE 11.34 Script to re-create the init<SID>.ora file.

REM
REM NAME        : init_ora_rct.sql
REM FUNCTION    : Re-create the instance init.ora file
REM USE         : GENERAL
REM Limitations : None
REM History: MRA 11/7/95 Initial creation
REM          MRA 10/14/01 Updated for Oracle9i
REM
SET NEWPAGE 0 VERIFY OFF
SET ECHO OFF FEEDBACK OFF TERMOUT OFF PAGES 300 LINES 80 HEADING OFF
COLUMN name FORMAT a80 WORD_WRAPPED
COLUMN dbname NEW_VALUE db NOPRINT
SELECT name dbname FROM v$database;
DEFINE OUTPUT = 'rep_out\&db\init.ora'
SPOOL &OUTPUT
SELECT '# Init.ora file FROM v$system_parameter' name FROM dual
UNION
SELECT '# generated on:'||sysdate name FROM dual
UNION
SELECT '# script by MRA 10/14/01 TUSC' name FROM dual
UNION
SELECT '#' name FROM dual
UNION
SELECT name||' = '||value name FROM v$system_parameter
WHERE value IS NOT NULL and Isdefault='FALSE';
SPOOL OFF
CLEAR COLUMNS
SET NEWPAGE 0 VERIFY OFF
SET TERMOUT ON PAGES 22 LINES 80 HEADING ON
SET TERMOUT ON
UNDEF OUTPUT
PAUSE Press Enter to continue

LISTING 11.29     Example of output from the INIT.ORA re-creation script.

#
# Init.ora file FROM v$system_parameter
# generated on:14-OCT-01
# script by MRA 10/14/01 TUSC
background_dump_dest = /var/oracle/OraHome2/admin/galinux1/bdump
compatible = 9.0.0
control_files = /var/oracle/OraHome2/oradata/galinux1/control01.ctl,
/var/oracle/OraHome2/oradata/galinux1/control02.ctl,
/var/oracle/OraHome2/oradata/galinux1/control03.ctl
core_dump_dest = /var/oracle/OraHome2/admin/galinux1/cdump
db_block_size = 8192
db_cache_size = 67108864
db_domain = tuscgalinux
db_name = galinux1
dispatchers = (PROTOCOL=TCP)(SER=MODOSE),
(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer),
(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
fast_start_mttr_target = 300
instance_name = galinux1
java_pool_size = 117440512
large_pool_size = 1048576
open_cursors = 300
processes = 150
remote_login_passwordfile = EXCLUSIVE
resource_manager_plan = SYSTEM_PLAN
shared_pool_size = 117440512
sort_area_size = 524288
timed_statistics = TRUE
undo_management = AUTO
undo_tablespace = UNDOTBS
user_dump_dest = /var/oracle/OraHome2/admin/galinux1/udump

Notice that the WHERE clause in the query for the init.ora re-creation script restricts the return values to only those that have been changed from their default settings (isdefault='FALSE'). If you do not restrict the return from the query, you get all hundred-plus parameters.

Monitoring Undocumented Initialization Parameters

There are also undocumented initialization parameters that require monitoring. Undocumented parameters are those that are (a) undergoing testing or (b) were too good to get rid of completely. Unfortunately, the undocumented values are a wee bit more difficult to access. For Oracle7.2 and earlier, the following script would return the values:

Rem undoc7.sql
Rem MRA from posting on compuserve orauser forum
COLUMN parameter FORMAT a40
COLUMN value      FORMAT a30
COLUMN ksppidf                  HEADING 'Is|Default'
SET FEEDBACK OFF VERIFY OFF PAGES 55
START title80 'Undocumented Init.ora Parameters'
SPOOL rep_out/&db/undoc
SELECT
     ksppinm "Parameter",
       ksppivl "Value",
       ksppidf
FROM
     x$ksppi
WHERE
     ksppinm like '/_%' escape '/'
/
SPOOL OFF
TTITLE OFF

But because Oracle monkeyed with the structure of the k and x$ tables, frequently you have to use the following script for Oracle7.3 and later:

REM undoc.sql
REM Script for getting undocumented init.ora
REM parameters from a 7.3 or greater instance
REM MRA - Revealnet 4/23/97
REM MRA - 10/14/01 verified against Oracle9i
REM
COLUMN parameter           FORMAT a37
COLUMN description         FORMAT a30 WORD_WRAPPED
COLUMN "Session VALUE"     FORMAT a10
COLUMN "Instance VALUE"    FORMAT a10
SET LINES 100 PAGES 0
SPOOL undoc.lis
SELECT
     a.ksppinm "Parameter",
     a.ksppdesc "Description",
     b.ksppstvl "Session Value",
     c.ksppstvl "Instance Value"
FROM
     x$ksppi a,
     x$ksppcv b,
     x$ksppsv c
WHERE
     a.indx = b.indx
     AND a.indx = c.indx
     AND a.ksppinm LIKE '/_%' escape '/'
/
SPOOL OFF

Both these scripts must be run from the SYS user.

Note: These parameters are listed in Chapter 2, if you would like to review them. Also, be aware that the preceding scripts have been tested up to Oracle9.0.1, so they aren't guaranteed beyond that.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

��