Monitoring Directories and Libraries

Directories and libraries were internal database structures new to Oracle8. Libraries are pointers to external sharable libraries of 3GL routines that can be called via the external procedures call option, also new to Oracle8. Directories, as their name implies, are pointers to external directories, where BFILE and other LOB data objects can be stored outside the database.

Monitoring Directories

Directory information is available from the DBA_DIRECTORIES view. This view has three columns. A simple report to show everything the database knows about directories is shown in Source 11.30.

SOURCE 11.30 Example of script to report on database directories.

rem NAME: dir_rep.sql
rem FUNCTION: Report on directories known by the database
rem HISTORY: MRA 6/16/97 Created for Oracle8
rem          MRA 5/16/99 Verified for Oracle8i
rem          MRA 10/14/01 Verified for Oracle9i
rem
COLUMN owner               FORMAT a10 HEADING 'Owner'
COLUMN directory_name      FORMAT a15 HEADING 'Directory'
COLUMN directory_path      FORMAT a45 HEADING 'Full Path'
SET VERIFY OFF PAGES 58 LINES 78 FEEDBACK OFF
START title80 'Database Directories Report'
SPOOL rep_out\&db\dir_rep.lis
SELECT
     owner,directory_name,directory_path
FROM
     dba_directories
ORDER BY
     owner;
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TTITLE OFF
CLEAR COLUMNS

Listing 11.25 shows an example of output from the directories report script in Source 11.30. Remember, directories aren't verified for existence until access is attempted.

LISTING 11.25 Example of output from the directories report.

Date: 10/14/01                                                  Page:   1
Time: 04:18 PM          Database Directories Report             DBAUTIL
                             galinux1 databa

Owner      Directory       Full Path
---------- --------------- ---------------------------------------------
SYS        MEDIA_DIR       /project/linux/install/d2/pse/cus/901/demo/sc
                           hema/product_media/ 

SYS        LOG_FILE_DIR    /project/linux/install/d2/pse/cus/901/admin/s
                           tp1/create/ 

SYS        DATA_FILE_DIR   /project/linux/install/d2/pse/cus/901/demo/sc
                           hema/sales_history/

SYS        SQL_DIR         /home/oracle/sql_scripts

Monitoring Libraries

Libraries are monitored through the DBA_LIBRARIES view. The DBA_LIBRARIES view contains five fields. An example of a report for monitoring libraries is shown in Source 11.31. The output from the library report script is shown in Listing 11.26.

SOURCE 11.31 Example of script to document external library specifications.

rem
rem NAME: lib_rep.sql
rem FUNCTION: Document External Library Entries in Database
rem HISTORY: MRA 6/16/97 Created
rem          MRA 10/14/01 Updated for Oracle9i
rem
COLUMN owner           FORMAT a8    HEADING 'Library|Owner'
COLUMN library_name    FORMAT a15   HEADING 'Library|Name'
COLUMN file_spec       FORMAT a30   HEADING 'File|Specification'
COLUMN dynamic         FORMAT a7    HEADING 'Dynamic'
COLUMN stauts          FORMAT a10   HEADING 'Status'
BREAK ON owner
SET FEEDBACK OFF VERIFY OFF LINES 78 PAGES 58
START title80 'Database External Libraries Report'
SPOOL rep_out\&db\lib_rep.lis
SELECT
     owner,library_name,file_spec,dynamic,status
FROM
     dba_libraries
ORDER BY
     owner;
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS

LISTING 11.26 Example of output from the library report.

Date: 10/14/01                                                  Page:   1
Time: 04:22 PM       Database External Libraries Report         DBAUTIL
                              galinux1 databa

Monitoring Control Files and Initialization Parameters

The control files have traditionally been a ?don't ask, don't tell? element of Oracle. Everyone knew they were there but weren't sure what they were for or how they could be monitored. Although initialization parameters were easy to monitor, no one did so. Now, in Oracle8, Oracle8i, and Oracle9i, monitoring both control files and initialization parameters, which are critical to database health and well-being, is much easier.

Monitoring Control Files

Oracle7 (from release 7.3 on) and Oracle8 provide the V$CONTROLFILE view to help keep track of the control files. Oracle8 provides the V$CONTROLFILE_RECORD view that is used with Recovery Manager. In Oracle9i, the V$CONTROLFILE_RECORD view becomes the V$CONTROLFILE_RECORD_SECTION view. The script in Source 11.32 can be used to monitor control file status. Its output is documented in Listing 11.27.

SOURCE 11.32 Script to monitor control file location and status.

rem
rem NAME : con_file.sql
rem FUNCTION: Document control file location and status
rem HISTORY: MRA 6/16/97 Creation
rem          MRA 10/14/01 Verified against Oracle9i
rem
COLUMN name    FORMAT a60 HEADING 'Con|File|Location' WORD_WRAPPED
COLUMN status  FORMAT a7  HEADING 'Con|File|Status'
SET LINES 78 FEEDBACK OFF VERIFY OFF
START title80 'Control File Status'
SPOOL rep_out\&db\con_file.lis
SELECT
     name,status
FROM
     v$controlfile;
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TTITLE OFF
CLEAR COLUMNS

LISTING 11.27 Example of output of control file script.

Date: 10/14/01                                              Page:   1
Time: 04:27 PM              Control File Status             DBAUTIL
                              galinux1 databa

Con                                                          Con
File                                                         File
Location                                                     Status
------------------------------------------------------------ -------
/var/oracle/OraHome2/oradata/galinux1/control01.ctl
/var/oracle/OraHome2/oradata/galinux1/control02.ctl
/var/oracle/OraHome2/oradata/galinux1/control03.ctl

Note that the Control File Status should always be blank. If it shows a status, it's an indication that the control file is corrupt. That said, because the database can't start up if the file is corrupt, this is an unlikely occurrence. You should confirm that the files are on separate disks or disk arrays.

TIP:  In previous versions of Oracle prior to 8, the control files were usually less than 1 megabyte in size. From Oracle8on, they can be tens of megabytes in size due to the extra backup material monitored. Be careful to allow for this in your file systems.

The v$controlfile_record_section gives statistics on each type of record contained in the control file. Recall that, in Chapter 2, we used this view to generate the MAX set of parameters for the CREATE DATABASE command. A script to monitor this table is shown in Source 11.33. The output from this script is shown in Listing 11.28.

SOURCE 11.33 Script to monitor the control file record sections.

rem
rem NAME: con_rec.sql
rem FUNCTION: Provide documentation of control file record stats
rem HISTORY: MRA 6/16/97 Creation
rem          MRA 10/14/01 Verified for Oracle9i
rem
COLUMN type           FORMAT a18        HEADING 'Record Type'
COLUMN record_size    FORMAT 999999     HEADING 'Record|Size'
COLUMN records_used   FORMAT 999999     HEADING 'Records|Used'
COLUMN first_index    FORMAT 9999999    HEADING 'First|Index'
COLUMN last_index     FORMAT 9999999    HEADING 'Last|Index'
COLUMN last_recid     FORMAT 999999     HEADING 'Last|Record|ID'
SET LINES 80 PAGES 58 FEEDBACK OFF VERIFY OFF
START title80 'Control File Records'
SPOOL rep_out\&db\con_rec.lis
SELECT
     type,record_size,records_total,records_used,first_index,
     last_index,last_recid
FROM
     v$controlfile_record_section;
SPOOL OFF
CLEAR COLUMNS
SET FEEDBACK ON VERIFY ON
TTITLE OFF

LISTING 11.28 Results from the control file records report.

Date: 10/14/01                                                  Page:   1
Time: 04:31 PM              Control File Records                DBAUTIL
                              galinux1 databa

                                                                      Last
                    Record               Records    First     Last  Record
Record Type           Size RECORDS_TOTAL    Used    Index    Index      ID
------------------ ------- ------------- ------- -------- -------- -------
DATABASE               192             1       1        0        0       0
CKPT PROGRESS         4084             4       0        0        0       0
REDO THREAD            104             1       1        0        0       0
REDO LOG                72            50       3        0        0       3
DATAFILE               180           100      12        0        0      19
FILENAME               524           351      17        0        0       0
TABLESPACE              68           100      13        0        0       7
TEMPORARY FILENAME      56           100       2        0        0       2
RMAN CONFIGURATION    1108            50       0        0        0       0
LOG HISTORY             36           226       8        1        8       8
OFFLINE RANGE           56           145       0        0        0       0
ARCHIVED LOG           584            13       0        0        0       0
BACKUP SET              40           204       0        0        0       0
BACKUP PIECE           736           210       0        0        0       0
BACKUP DATAFILE        116           211       0        0        0       0
BACKUP REDOLOG          76           107       0        0        0       0
DATAFILE COPY          660           210       0        0        0       0
BACKUP CORRUPTION       44           185       0        0        0       0
COPY CORRUPTION         40           204       0        0        0       0
DELETED OBJECT          20           408       0        0        0       0
PROXY COPY             852           306       0        0        0       0
RESERVED4                1          8168       0        0        0       0 

The control file records report can tell you the number of data files, redo logs, archived logs, and a plethora of other information about your database. The Records Used column indicates how many of a particular type has been assigned for your database.

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