manage controlfiles

 a binary file  1.what's controlfiles

 

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database.The database control file is a small binary file necessary for the database to start and operate successfully. A control file is updated continuously by Oracle during database use, so it must be available for writing whenever the database is open. If for some reason the control file is not accessible, then the database cannot function properly.Each control file is associated with only one Oracle database.The control file of an Oracle Database is created at the same time as the database.no database administrator or user can edit a control file.

 

2.controlfiles  contents

 

• The database name

• The timestamp of database creation

• The names and locations of associated datafiles and redo log files

• Tablespace information

• Datafile offline ranges

• The log history

• Archived log information

• Backup set and backup piece information

• Backup datafile and redo log information

• Datafile copy information

• The current log sequence number

• Checkpoint information

 

 

3. the Size of Control Files

    The main determinants of the size of a control file are the values set for t  he MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES parameters in the CREATE DATABASE

   

    maxdatafiles

    maxlogfiles

    maxlogmembers<=5

    maxloghistory

    maxinstances

 

4. Multiplexing the control file when using SPFILE

   1.select * from v$controlfile/show parameter control;

   2.show parameter spfile  if value not null,it means that you server is now using spfile,otherwise using pfile

   3.alter system set control_files=('','','') scope=spfile

   4.shutdown immediate

   5.cp one to one   //create the additional controlfile

   6.startup

   7.show parameter control

5. Multiplexing the control file when using PFILE 

     1. show parameter spfile

     2.shutdown immeidate

     3.cp one to one

     4.edit the pfile

     5.startup

     6.show parameter spfile;

6. create controlfiles

   if not exist controlfile,modify the db_name,modify maxlogfiles maxlogmembers,maxloghistory,maxinstances,maxdatafiles,we need create new controlfiles.

   1.get informations about datafiles and logfiles

      select * from v$logfile;

      select * from v$datafile;

      the database can not be opened,because of all of controlfiles was lost.we must find the datafiles and logfiles in the enviroment(server).

   2.shutdown immediate

   3.startup nomount;

   4.create controlfile

      set database "lsh"

      norestlogs/resetlogs

      archivelog

      character set al16utf16

      datafile

             '/u01/oradata/lsh/system01.dbf' size 300m,

              '/u01/oradata/lsh/sysaux01.dbf' size 300m,

              '/u01/oradata/lsh/undotbs01.dbf size 200m

          logfile

              group 1 '/u01/oradata/lsh/redo01a.dbf','/u01/oradata/lsh/redo01b.dbf',

              group 2 '/u01/oradata/lsh/redo02a.dbf','/u01/oradata/lsh/redo02b.dbf',

              group 3 '/u01/oradata/lsh/redo03a.dbf','/u01/oradata/lsh/redo03b.dbf'

         maxlogfiles 5

         maxlogmembers 3

         maxloghistory 10

         maxinstances 1

         maxdatafiles 4;

         note:‘resetlogs’ command basically forces the entire database to re-synchronise at time zero, which is fine for getting the database open, but is not exactly useful if you ever need to restore from your prior backups (taken when the database was at a time of, say, 10894329), or if you ever expect to be able to apply redo from priot archive logs (which were also taken when the database was at time 10894329 and earlier). Basically, a ‘resetlogs’ renders your database entirely vulnerable: there are no effective backups, and no effective archives. You are therefore supposed to immediately shut the newly-recovered database down, and perform a whole, closed backup of the entire database (which is not exactly a cheap option).

   5. alter database open /alter database open resetlogs

   6. select * from v$logfile;

          select * from v$datafile;

   7.backup controlfiles

        alter database backup controlfile to trace; textfile

        alter database backup controlfile to '/u01/oradata/lsh/control.bakl'  a binary file 

        note: a binary file record the scn ,the scn is not agree with the scn of the crush database

                we issue the command:

                    1.   recover database using backup controfile 

                    2.   alter database open resetlogs 

                a text file record the location of the datafile and logfile ,so it uses those locations to read the headers of all the data files, whereupon it picks the highest SCN it comes across as the one to write into the header of the Control File it is about to create. That means the re-constructed Control File is already in synchronisation with the data files, and no forced synchronisation to time Zero is therefore required.

So, what’s the best way of backing up the Control File? Answer: multiplex your Control Files so that a recovery is never needed in the first place. But if, despite that, you need some insurance, the Trace File is definitely the better way to go.  It doesn’t render your entire database exposed to failure, it doesn’t effectively trash all prior backups and archives, it works quickly, and well.

 

 

 8.drop the controlfile

        alter system set control_file=;