topic Re: oracle 9i in Operating System - HP-UX

oracle 9i

Pieter_5 — Wed, 06 Aug 2003 14:09:22 GMT

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?

Re: oracle 9i

Steven E. Protter — Wed, 06 Aug 2003 14:19:15 GMT

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

Re: oracle 9i

Jean-Luc Oudart — Wed, 06 Aug 2003 14:28:41 GMT

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

Re: oracle 9i

Pieter_5 — Wed, 06 Aug 2003 14:31:54 GMT

What I would like to know is if there just one controlefiles for every database?

Re: oracle 9i

Jean-Luc Oudart — Wed, 06 Aug 2003 14:40:18 GMT

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

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

JL

Re: oracle 9i

Jean-Luc Oudart — Wed, 06 Aug 2003 14:45:35 GMT

You can check this question :
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:496646066771

Rgds,
JL

Re: oracle 9i

Volker Borowski — Wed, 06 Aug 2003 14:59:56 GMT

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

Re: oracle 9i

twang — Wed, 06 Aug 2003 16:00:03 GMT

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.

Re: oracle 9i

Tim Sanko — Wed, 06 Aug 2003 16:37:06 GMT

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

Re: oracle 9i

Yogeeraj_1 — Wed, 06 Aug 2003 16:58:24 GMT

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

Re: oracle 9i

T G Manikandan — Thu, 07 Aug 2003 02:28:37 GMT

/*
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.
*/