CONTROL FILES

1. ContainsDatabase Name and Database Unique Identifier (DBID)locations of:
2. Feature: Maintains database Integrity by using: 
1. SCN (Sequence Numbers)
   
   b.      Timestamps

                                                         i.      Online Redo Log Files

                                                       ii.      Datafiles

                                                      iii.      Archive Log Files (i.e if the database is in Archive Log Mode)

c.       Time Stamp of Database Database Creation

d.      Details of RMAN backup (i.e. if RMAN utility is being used for backing up)



2.      Feature: Maintains database Integrity by using:

a.       SCN (Sequence Numbers)

b.      Timestamps

3.      Purpose of Control File:

a.       Control File is required to open the database as it contains the structure of the database (i.e data files, control files).

b.      Tracks the structural changes to the database (such as adding/removing/modifying a datafile).

c.       Contains metadata which is required for Instance Recovery when the database is not open.

4.      One Control File per database

5.      Multiple copies maintained by multiplexing the Control File

6.      Multiple copies are synchronized automatically

7.      Minimum number of copies: 1

8.      Maximum number of copies: 8

9.      Damage to any control file will terminate the Instance.

10.  DATA DICTIONARY VIEWS OF CONTROL FILE:



V$DATABASE
 Displays database information from control file

V$CONTROLFILE
 Location and Status of each Control File in the database

V4CONTROLFILE_RECORD SECTION
 

CONTROL_FILES PARAMETER
 SQL> show parameter control_files

Lists all the control files being specified in




a.       v$database:  Displays database information from Control File

b.      v$controlfile





11.  Creating control file:The create control file statement is used in the create database statement during the database creating itself. The create control file statement is as follows

CONTROL_FILES = (/u01/app/oracle/product/control01.ctl,                 /u02/app/oracle/product/control02.ctl,                  /u03/app/oracle/product/control03.ctl)


Recommendation: Control files if created on separate mount points as indicated above (i.e /u01, u02, u03) will ease the recovery if one of the mount points hang.



12.  Control File notes all checkpoint information from online Redo Log Files

13.  During recovery, the recovery process first reads the SCN number from the control file. The recovery process then starts by writing all information from online Redo log files from that SCN to the datafiles.

 

What if a CONTROL FILE IS LOST/DAMAGED

Changing Database Parameter Files

It so happened once when one of the filesystems of the UNIX box hung. The db_recover_file_dest was pointing to that location and the database went into hung state.

So we decided to comment out the parameter.

REDO LOG FILES

• Stores a chronological order of every change vector applied to the database
• If a datafile is damaged/destroyed, then it is restored from the last backup + the the change vectors till the datafiles lost
• 2 Types of Redo Log Files
• ONLINE REDO LOG FILES
• ARCHIVE LOG FILES
  
• Online Redo Log consists of groups of Online Redo Log Files
• Minimum 2 groups of Online Redo Log Files required
• Minimum 2 groups are required as when one group is being archived, the other group can be actively accepting the change vectors
• More number of groups of Redo Log Files are maintained for better performance
• More number of members per group are maintained for better security

STORAGE STRUCTURE

DATABASE STORAGE STRUCTURE:

The database is just a set of files present on disk. These files are created when we create the Database using the 'CREATE DATABASE' statement.

When looking at server level, we may find the files as .log .trc .trm .dbf .ctl. Oracle uses the logical structure to organize the files internally.

The data dictionary maintains the relationship between the physical and logical structure

I) Physical Database Structure:

CONTROL FILES

ORACLE DATBASE ARCHITECTURE

Many materials and websites offer a different to the same Oracle Architecture. Therefore one is left confused after reading several sources. Therefore I have made an effort to pool in all the info into an understandable form. Lets see what lies beneath the giant Oracle.



Oracle database consists of 2 types:

1) Database

2) Instance



1) Database:

The database is the physical files on disk. This consists of 2 parts:

I) Physical Database Structure

II) Logical Database Structure





I) Physical Database Structure:



The Physical Database structure conists of:

i) Datafiles

ii) Control Files

iii) Redo Log Files

iv) Other files like Server Parameter files, Alert Log Files, Trace Files



II) Logical Database Structure:



The Logical Database Structure consists of:

i) Data Blocks

ii) Extents

iii) Segments

iv) Tablespaces



2) Instance:

Instance consists of:



I) Memory Structures

II) Background Processes (or just 'Processes')

ORACLE DATBASE ARCHITECTURE

Many materials and websites offer a different to the same Oracle Architecture. Therefore one is left confused after reading several sources. Therefore I have made an effort to pool in all the info into an understandable form. Lets see what lies beneath the giant Oracle.

Oracle database consists of 2 types:
1) Database
2) Instance

1) Database:
The database is the physical files on disk. This consists of 2 parts:
I) Physical Database Structure
II) Logical Database Structure


I) Physical Database Structure:

The Physical Database structure conists of:
i) Datafiles: contains all database data which is stored logically in the form of tables and indexes
ii) Control Files: contains metadata about the physical structure of the database and details about the database and the database files
iii) Redo Log Files: records all changes made to data
iv) Other files like Server Parameter files, Alert Log Files, Trace Files



II) Logical Database Structure:

The Logical Database Structure consists of:
i) Data Blocks: lowest level of storage structure. Each data block contains a specific number of bytes on  disk
ii) Extents: contains contiguous data blocks and is used to store specific information.
iii) Segments: is a set of extents. It is used for storing database objects.
iv) Tablespaces: contains logical segments. Physically it contais atleast one datafile. Data is stored logic in a tablespace but physically in  datafiles.

2) Instance:
Instance consists of:

I) Memory Structures
II) Background Processes (or just 'Processes')