Monitoring Control Files
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
- Oracle 7.3 and beyond 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 and beyond, the V$CONTROLFILE_RECORD view
becomes the V$CONTROLFILE_RECORD_SECTION view.
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
ttitle 'Control File Status'
SPOOL con_file.lis
SELECT
name,
status
FROM
v$controlfile
See Code Depot;
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TTITLE OFF
CLEAR COLUMNS
Here is a sample listing:
Con
Con
File
File
Location
Status
------------------------------------------------------------ ------
/var/oracle/OraHome2/oradata/diogenes1/control01.ctl
/var/oracle/OraHome2/oradata/diogenes1/control02.ctl
/var/oracle/OraHome2/oradata/diogenes1/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.
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
ttitle 'Control File Records'
SPOOL 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
Here is a sample listing:
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
FILEN 56
100
2
0
0
2
RMAN
CONFIGURA 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
CORRUPTI 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 host of other information
about the database. The Records Used column indicates how many of a
particular type have been assigned for the database.
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|