Control File:

  - It's a small control file -It's a heart of the database

  - We can maintain maximum 8 copies but recommended 2 copies

  - Depends upon 5 files  sizes the control file size is allocated those files are

     *MAX LOGFILE=5

     *MAX LOGMEMBER= 5

     *MAX LOGHISTORY=1

     *MAX DATAFILES=100

     *MAX INSTANCE=1

  - It holds the information of

      *DB name and Identifier

      *Database creation time

      *Tablespace names

      *Name & location of datafiles

      *Backup Information

      *Redolog Archive Information

  - To get the information control file

      I) V$controlfile This command is used to view the location of control file

      desc V$controlfile
      COLUMN                  DATATYPE         DESC
      STATUS 	                VARCHAR2(7) 	INVALID if the name cannot be determined, which should not occur. Null if the name can be determined.
      NAME 	                VARCHAR2(513) 	Name of the control file
      IS_RECOVERY_DEST_FILE 	VARCHAR2(3) 	Indicates whether the file was created in the flash recovery area (YES) or not (NO)

      II) V$controlfile_record_section displays information about the control file record sections.

     Desc V$CONTROLFILE_RECORD_SECTION

    TYPE 	VARCHAR2(28) 	Identifies the type of record section:

                          DATABASE

                          CKPT PROGRESS

                          REDO THREAD

                          REDO LOG

                          DATAFILE

                          FILENAME

                          TABLESPACE

                          TEMPORARY FILENAME

                          RMAN CONFIGURATION

                          LOG HISTORY

                          OFFLINE RANGE

                          ARCHIVED LOG

                          BACKUP SET

                          BACKUP PIECE

                          BACKUP DATAFILE

                          BACKUP REDOLOG

                          DATAFILE COPY

                          BACKUP CORRUPTION

                          COPY CORRUPTION

                          DELETED OBJECT

                          PROXY COPY

                          BACKUP SPFILE

                          DATABASE INCARNATION

                          FLASHBACK LOG

                          RECOVERY DESTINATION

                          INSTANCE SPACE RESERVATION

                          REMOVABLE RECOVERY FILES

                          RMAN STATUS

                          THREAD INSTANCE NAME MAPPING

                          MTTR

                          DATAFILE HISTORY

 RECORD_SIZE 	NUMBER 	Record size in bytes
RECORDS_TOTAL 	NUMBER 	Number of records allocated for the section
RECORDS_USED 	NUMBER 	Number of records used in the section
FIRST_INDEX 	NUMBER 	Index (position) of the first record
LAST_INDEX 	NUMBER 	Index of the last record
LAST_RECID 	NUMBER 	Record ID of the last record

  To view information stored  in control file

    SQL>select distinct type from V$controlfile_record_section;

     TYPE
    ----------------------------
     FILENAME
     TABLESPACE
     RMAN CONFIGURATION
     BACKUP CORRUPTION
     PROXY COPY
     FLASHBACK LOG
     ........

 Display the contents in control file

     SQL>select  value from V$diag_info where name='Diag Trace';

    in oracle 11g and above  the trace  files are written in $ADR_HOME/trace but in oracle 10g and below its was written into USER_DUMP_TEST

Viewing Control File Names and Locations

    if our database is in nomount or mount or open  stage we can view location and name of  the control files location

    SQL> show parameter control_files

    we can view by quering also but it ll work  on mount or open stage only

    SQL>select name from v$controlfile;

 Adding Control files in spfile

    if our database is open first we ensure it will run on spfile or pfile  so we would issue the command

s-1

    SQL>show parameter spfile

      NAME           TYPE       VALUE
     --------- ----------- ------------------------------
      spfile        string      /u01/app/oracle/product/11.1.0.3/db_1/dbs/spfileORCL.ora

    i found my db is running on spfile

s-2 Determine the CONTROL_FILES parameter’s current value

    SQL> show parameter control_files
      NAME                    TYPE      VALUE
     ----------------- ----------- ------------------------------
      control_files          string       /u01/app/oracle/product/11.2.0.3/oradata/orcl/control01.ctl

s-3 Adding control in spfile  

    SQL> alter system set control_files='/u01/app/oracle/product/11.2.0.3/oradata/oracl/control01.dbf','/u02/backupdile/control02.ctl' 
scope=spfile;

s-4 after  adding control files shut the database  

     SQL>shut immediate;

s-5 copy the control file as we mentioned location in spfile

     SQL>host
     oracle@oracle11g]cp /u01/app/oracle/product/11.2.0.3/oradata/orcl/control01.ctl
     /u02/backupfile/control02.ctl

s-6 start our database

    SQL>startup;

s-7 check controlfile name and locations

    SQL> show parameter control_files

     NAME               TYPE        VALUE
    --------------- ----------- ------------------------------
    control_files       string    /u01/app/oracle/product/oradata/orcl/control01.ctl
                             ,/u02/dbfile/control02.ctl

  Moving controlfile

 s-1 Determine the CONTROL_FILES parameter’s current value

    SQL> show parameter control_files
       NAME                    TYPE      VALUE
     -----------------     ----------- ------------------------------
       control_files          string       /u01/app/oracle/product/11.2.0.3/oradata/orcl/control01.ctl

 s-2 To move control file from  one location into  another location

    SQL>Alter  system set control_files='/u02/backupfile/control02.ctl' scope=spfile;

 s-3 shut the database

    SQL>shut immediate;

      move the control file location

    SQL> host
    oracle@oracle11g~]mv /u01/app/oracle/product/11.2.0.3/oradata/orcl/contol01.ctl /u02/backupfile/control02.ctl

  s-4 start the database

    SQL>startup;

check control file parameter

    SQL> show parameter control_files
      NAME                    TYPE      VALUE
     ----------------- ----------- ------------------------------
      control_files          string      /u02/backupfile/control02.ctl

 Removing the control files

   a media failure with a storage device that contains one of your multiplexed control files: ORA-00205: error in identifying control file, check alert log for more info  

  s-1 identify  error in alert log file ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/bakupfiles/control02.ctl'  

  SQL> show parameter control_files

    NAME               TYPE        VALUE
    --------------- ----------- ------------------------------
    control_files       string    /u01/app/oracle/product/oradata/orcl/control01.ctl
                             ,/u02/dbfile/control02.ctl

  s-2 remove that control file which has been showed in alert log  

  SQL>host
  oracle@oracle11g~]rm -rf /u02/backupfiles/control02.ctl

  s-3

  SQL>alter system set control_files='/u01/app/oracle/product/oradata/oracl/control01.ctl' scope=spfile;

s-4 shut the database

  SQL>shut immediate;

s-5 start the database

  SQL>startup;