Subject: ORACLE7: UNDERSTANDING ORACLE7 CONTROLFILE STRUCTURE Creation Date: 10-JAN-2000 PURPOSE ------- To document the structure and information contained in a Oracle7 controlfile tracefile. SCOPE & APPLICATION ------------------- For Oracle support analysts requiring further understanding of an Oracle7 controlfile dump. RELATED DOCUMENTS ----------------- [NOTE:28984.1] EVENT CONTROLF - Obtaining & Interpreting ControlFile Dumps To get a dump of the controlfile, you can use the CONTROLF event. This event can be set using: SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL 1'; This level will dump only the controlfile's file header. SQL> ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME CONTROLF LEVEL 10'; Using CONTROLF with level 10 you will get trace that shows the entire structure of the controlfile with the relative contents. This createS a tracefile in the directory defined by the parameter user_dump_dest. An Oracle7 controlfile contains the following sections: 1. FILE HEADER INFORMATION - This section contains: a. Software version - contains the hexadecimal representation of the release where the controlfile was been created. Please note that the contents of this field doesn't change after Oracle upgrade. b. Compatibility - that is the lowest Oracle version where is compatible this controlfile format c. Db Id - is a number calculated with an hash algorithm between "dbname" and "database creation timestamp" d. Dbname - Database name e. Control file sequence - is the number of this controlfile's updates f. File size - is the number of the O.S. block needed to store the controlfile. Please note that this number does not include the file header block g. Block size - is the O.S. block size h. File number - is a relative number that Oracle uses to count the files in trace files i. File type - is a number that Oracle uses to identify the file type in use. See [NOTE:28984.1] for further details. 2. DATABASE ENTRY - This section describe the entire database and contains: a. Offset - is the offset that indicates the begin point of this section in the controlfile b. Size - is the size of this section expressed in bytes and fields that describe the entire database: a. Incomplete recovery - this is composed by various fields that are updated after an incomplete recovery. If count is a non zero value the SCN field show the SCN number used by the database after an RESETLOGS option. With fields Oracle prevents DBA from doing recovery with oldest archived redo log. b. Data files - is the total number of the datafiles that this database uses (it includes both online and offline datafiles). c. Online files - is the number of the online datafiles. d. Enabled threads - is the number of the enabled redo threads If this number is more that 1 it means that you are running with parallel server option. e. Open threads - is the number of opened and used threads. f. Max log members - is the maximum number of redolog file for each redolog group. This is specified with MAXLOGMEMBERS at "create database" time g. Max data members - this field is the same as "max log members" as this feature (datafile's mirror) isn't yet implemented h. Archive log information - FORCE SCN is the SCN used to force the redo. Each redo that has the SCN below the Force Scn, will be archived. 3. REDO THREAD ENTRIES - This section show how many redo threads you can use with this database and how many redo threads you are using on this database. a. max - maximum number of threads available. b. hi - number of threads enabled. Please note that stored in this section is informations about the offset and size of the portion. After this generic information, there is a specific subsection for each thread: a. status - this is the status for the thread. See [NOTE:28984.1] for further details. b. log - is the number of the redo log group managed by this thread. c. current - is the log file group that LGWR is using at a specific time. d. enabled - is the SCN and timestamp where this thread has been enabled e. opened - is the timestamp and the instance db name that has opened this thread. f. Checkpointed - is the SCN and the timestamp of the checkpoint for this thread. 4. LOG FILES ENTRIES - This section has information about the redo log files, their status and their availability. The fields offset and size determine the position and the size of this section, and two other fields to define how many redo log your database's managing: a. max - that is the maximum number of redo log that you can use in this database b. hi - that is number of redo log used at this time For each logfile you have the following information: a. thread - the number of the thread that manages this logfile b. size - The number of O.S. blocks of this redo log c. sequence - is the sequence number for this redo log d. nab - this is the Next Available Block that LGWR will use to write the informations. If you're analizing the current logfile, this field should be set to infinity -> 0xffffffff e. low SCN - is the lowest SCN contained in the current log file. f. next SCN - is the highest SCN contained in this log file. If this logfile is the current logfile, this field will be set to infinity -> 0xffffffff Please note that "low SCN" and "next SCN" are the low and high SCN for this logfile. g. previous SCN: If the lowest SCN contained in the previous log file. 5. DATA FILE ENTRIES - Oracle uses this section to get the information when the users 'select * from V$DATAFILE'; view. As with previous sections, the offset and the size of this section are defined along with: a. max - the maximum number of datafiles in this database b. hi - the number of datafiles used at this time. After this you get information for each datafile as follows: a. status - Oracle uses this field to understand if the datafile is a system or non-system datafile or if it's online or offline b. checkpoint counter - is the number of the checkpoints made on this file. This counter is incremented even when this datafile is in hot backup. c. scn - is the last SCN for the last checkpoint made on this datafile. d. stop scn - This field is written during a shutdown normal or immediate, and means that the datafile does not need media recovery. When the database is opened the value is set to infinity. e. Creation checkpoint - with this field Oracle keeps track of the creation SCN with relative timestamp. f. Offline scn - this is the SCN recorded at the ALTER TABLESPACE .... OFFLINE time. For system datafiles this field will be always "0" because you cannot offline system tablespace g. Online checkpoint - When you put a tablespace in backup mode, will be done a checkpoint for each datafile of this tablespace and time and SCN will be recorded in this filed. 6. LOG FILES HISTORY ENTRIES - This is the last section in the ORACLE7 controlfiles. In this section, you WILL find the last switch of the log file. For each switch you can get the "low SCN" and the "next SCN" for the entries contained in the log file. This section does not contain all the history but only the "Earliest log history" and the "Latest log history". To get all the log history you can use the event "LOGHIST".