What are control files and how we can back them up

Control files [Oracle]
       
The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:

    * Database information (RESETLOGS SCN and their time stamp)
    * Archive log history
    * Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
    * Redo threads (current online redo log)
    * Database's creation date
    * database name
    * current archive log mode
    * Log records (sequence numbers, SCN range in each log)
    * RMAN catalog
    * Database block corruption information
    * Database ID, which is unique to each DB

The location of the control files is specified through the control_files init param.
 

Backing up Controlfiles
When a controlfile (multiplexed or not) becomes unavailable (because for example of a media failure, the database cannot continue to operate.Because control files keep track of datafiles and the control file should be backed up whenever a change is made to the database (alter database).

The command to back the control file up is: alter database backup controlfile to 'filename' e.g

alter database backup controlfile to trace as '/home/oracle/controlfile.txt';.
 

Recovering database when controlfile is lost

 
If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated (with create controlfile whose syntax is a bit awkyard.
 

e.g.

create controlfile database name-of-database

In order to recover from a lost spfile, the DBID is required.

Size of control files
The size of the control files is governed by the following parameters:

    * maxlogfiles
    * maxlogmembers
    * maxloghistory
    * maxinstances
    * control_file_record_keep_time

Sections

 
The controlfile contains the following sections:

    * ARCHIVED LOG (reusable)
    * BACKUP CORRUPTION (reusable)
    * BACKUP DATAFILE (reusable)
    * BACKUP PIECE (reusable)
    * BACKUP REDOLOG (reusable)
    * BACKUP SET (reusable)
    * BACKUP SPFILE
    * CKPT PROGRESS
    * COPY CORRUPTION (reusable)
    * DATABASE
    * DATAFILE
    * DATAFILE COPY (reusable)
    * DATAFILE HISTORY
    * DATABASE INCARNATION
    * DELETED OBJECT (reusable)
    * FILENAME
    * FLASHBACK LOG
    * INSTANCE SPACE RESERVATION
    * LOG HISTORY (reusable)
    * MTTR
    * OFFLINE RANGE (reusable)
    * RECOVERY DESTINATION
    * REMOVABLE RECOVERY FILES
    * RMAN STATUS
    * RMAN CONFIGURATION
    * REDO THREAD
    * REDO LOG
    * TABLESPACE
    * TEMPORARY FILENAME
    * THREAD INSTANCE NAME MAPPING
    * PROXY COPY

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.
These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.
Misc
SQL*Loader's behaviour is specified with the SQL*Loader control file. Although they sound similar, they have nothing in common.
ASM simplifies the optimal layout of controlfiles.

How to resize or re-create online redo log files in oracle

In oracle 10g by default there are three groups having one online redo log file of 50 MB in each group. It is recommended to re-size them before going to use oracle 10g in production environment. In this article I will explain how to resize redo log files from 50 MB to 250 MB. Please follow the steps give below.

Step 1 SQL> select a.group#, a.member, b.bytes/1024/1024 mb from v$logfile a, v$log b where a.group# = b.group#;

GROUP#     MEMBER                                                       MB
3                   /u01/app/oracle/oradata/ORCL/redo03.log        50
2                  /u01/app/oracle/oradata/ORCL/redo02.log         50
1                 /u01/app/oracle/oradata/ORCL/redo01.log          50

This query will show current group with redo log members and their size.

Step 2 Make the last redo log CURRENT one

To find which group is current at this moment use following query

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                    CURRENT
2                    INACTIVE
3                    INACTIVE
Now as you can see that the first group is marked as current but we need to make group 3 as current. So force group 3 to become current one by switching log file. To switch log file use following query.

SQL> alter system switch logfile;

GROUP#         STATUS
---------- ----------------
1                     INACTIVE
2                    CURRENT
3                    INACTIVE

SQL> alter system switch logfile;

GROUP#        STATUS
---------- ----------------
1                     INACTIVE
2                     INACTIVE
3                     CURRENT

Step 3 Drop the first online redo log

After making the last online redo log file the CURRENT one, drop the first online redo log:

SQL> alter database drop logfile group 1;

Database altered.

Note:

As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group.However, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)

ORA-00312: online log 1 thread 1: ''

Easy problem to resolve. Simply perform a checkpoint on the database:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Step 4 You need to re-create dropped online redo log group with different size. Use the following query to achieve this.

SQL> alter database add logfile group 1 (
2 '/u01/app/oracle/oradata/ORCL/redo01.log',
3 '/u01/app/oracle/oradata/ORCL/redo01.log') size 250m reuse;

Database altered.

Step 5 Force another log switch

After re-creating the online redo log group, force a log switch. The online redo log group just created should become the "CURRENT" group:

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                     UNUSED
2                     INACTIVE
3                     CURRENT

SQL> alter system switch logfile;

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                      CURRENT
2                      INACTIVE
3                      ACTIVE

Step 6 # Loop back to Step 3 until all logs are rebuilt

After re-creating an online redo log group, continue to re-create (or resize) all online redo log groups until all of them are rebuilt.

I hope this article helps you in recreating and resizing the redo log files.