Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle 9i

Pieter_5
Advisor

oracle 9i

I am installing a oracle 9i db. I have a question about the control.file. A saw an installation in which several controlefiles existed for one database. Are these controlefiles identical and meant as duplicates? What is good practise for these files?
10 REPLIES
Steven E. Protter
Exalted Contributor

Re: oracle 9i

Good practices for control files is to have serveral copies so the database can survive the loss of one or more of them.

We keep three on different physical disks.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Jean-Luc Oudart
Honored Contributor

Re: oracle 9i

Eventhough you may have mirroring for your physical storage (or OS mirroring) it is advised to have a copy of your control file in case it is deleted from the command line ...

Rgds,
JL
fiat lux
Pieter_5
Advisor

Re: oracle 9i

What I would like to know is if there just one controlefiles for every database?
Jean-Luc Oudart
Honored Contributor

Re: oracle 9i

You should have more than one control file per Oracle instance.

you can find the information in the init.ora configuration file.

JL
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: oracle 9i

Volker Borowski
Honored Contributor

Re: oracle 9i

Hello,

these files are very important.
You must have at least one per database.
You should have them mirrored as already suggested to three diffrent physical disks.

You should save a backup copy beside your backup. Watch it! If the database is up and running, you cannot backup the actual file. You need to execute
"ALTER DATABASE BACKUP CONTROLFILE TO '/somewhere_safe/backup_controlfile';"
and save this file along with your backup. Might be worth to save the usertrace-destination after a
"ALTER DATABASE BACKUP CONTROLFILE TO TRACE;"
as well.

The binary version of this file is the only way to sync the datafiles to their creation SCN. This is important in case you loose a datafile before it has been backed up for the first time.

Take good care of them !
Volker
twang
Honored Contributor

Re: oracle 9i

Multiplexing control file means maintaining serveral identical control files.
The control file is a critical resource and Oracle requires that you have at least 2. It reads only the primary and writes to all copies to keep them in snyc. Should Oracle become unable to write to the multiplexed copy Oracle has to hang to keep the files in sync. The purpose of multiplexing the control file is not to provide alternate access in the event of a failure but to prevent loss of the control file information, which is critical to recovery operations.

For Oracle to be able to mark a control file as inaccessible and to continue you would have to have at least 3 of them so that a hot backup was always available.

Oracle recommends putting the second copy of the control file with the second copy of the online redo logs works under the theory that one disk is used and this provides a complete backup set for media recovery if the primary set is lost due to media failure. The control file is not updated that frequenty except for checkpoint information since you would not expect that many datafile extentions or additions to take place on an ongoing basis so IO wise having the files together makes sense. It also prevented Oracle OFA from having to specify an additional disk just to hold the second control file.

Tim Sanko
Trusted Contributor

Re: oracle 9i

We have three for production and each test instance.

No control file is on the same drive as another control file.

Even with mirroring and BCVs we like to keep them in sync. {if you have problems with a recovery match all three control files from a previous backup and roll your archive logs forward. (We keep 100 GB) two days worth of archive logs.

Tim
Yogeeraj_1
Honored Contributor

Re: oracle 9i

hi,

to add to above replies:

By storing multiple control files for a single database on different disks, you can safeguard against a single point of failure with respect to control files. If a single disk that contained a control file crashes, then the current instance fails when Oracle attempts to access the damaged control file. However, when other copies of the current control file are available on different disks, an instance can be restarted easily without the need for database recovery.

read: http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96524/c04space.htm

Hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
T G Manikandan
Honored Contributor

Re: oracle 9i

/*
Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using an intact copy of the control file and the instance can be restarted; no media recovery is required.

Behavior of Multiplexed Control Files
The following list describes the behavior of multiplexed control files:

Two or more filenames are listed for the initialization parameter CONTROL_FILES in the database's parameter file.

The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle Server during database operation.

If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

The only disadvantage of having multiple control files is that all operations that update the control files (such as adding a datafile or checkpointing the database) can take slightly longer. However, this difference is usually insignificant (especially for operating systems that can perform multiple, concurrent writes) and does not justify using only a single control file.
*/