Oracle is a strong database administration platform, which has the capacity to prove essential DB solutions to business leaders and pave the way to operational success.
However, for an effective business leader, it is essential to know the changing concepts in terms of database management and also how to derive actionable insights from the critical resource of data.
In this article, we are trying to discuss some crucial aspects of Oracle database administration, which will help the business dealers to manage their database better and use it for better operational decision making.
The concept of a control file:
All Oracle databases will have a control file. It is a primary binary file which defines the database structure. A standard control file includes the details of the database as:
- Name of the database
- The names as well as locations of the associated data files and the log files
- Timestamp about the creation of the database
- The latest log sequence number
- The checkpoint DB information
A control file should be available for the writing to be done by Oracle DB server when a database is open. With a control file available, database mounting and recovery of data are also impossible.
An Oracle DB control file is created at the very moment of the creation of a new database. At least a copy of this file is created by default during the time of DB creation. On some operating systems, multiple copies may also be created by default. You may also create two copies of more of the control at the time of database creation.
It is also possible to create control files later after creating a database if you lose the control files anyway and want to change the settings in control files.
Providing a control file name:
You can specify the file name for control files using the initialization parameter of CONTROL_FILES in the initialization parameter file. During startup, this instance will recognize and open all listed files, and the instance will write to it and maintain all the listed control files during the time of database operation.
If you don’t specify any files for the CONTROL_FILES on creating new databases, the database will create a control file by its own and assigns a default filename to it. This name could be operating-system specific.
On the other hand, if you use the Automatic Storage Management option, you may place the filenames of ASM in the DB_CREATE_FILE_DEST and the DB_RECOVERY_FILE_DEST parameters. Automatic Storage Management will then create control files automatically at the appropriate destinations.
Multiplex control files
As we have seen, each Oracle DB may have two control files at a minimum, which get stored on different disks. Even if a control file gets damaged with a disk failure, the instance associated with it must shut down.
Once the drive is repaired, then the control file which is damaged can be instantly restored using the copy of it on another disk. In this case, no separate media discovery is needed. Oracle also recommends having a minimum two control files to be located on separate disks, the procedure of which can be found at RemoteDBA.com.
One handy way to create multiplex control files is to create a control file copy on every drive which stores members of the log groups if redo log gets multiplexed. With storage of control files at these various locations, one can ultimately minimize the risk of all the control files and redo groups getting lost during a single disk failure.
Backing up the control files
Along with keeping multiplex control files, it is also essential to properly back up the control files. This is important initially as well as each time when the physical structure of your database is changed. The structural changes to be considered include:
- ADD, DROP, or RENAME data files
- ADD or DROP tablespace, or ALTER the read/write status of the tablespace.
- ADD, DROP redo log files or log file groups.
The major determinants of the size of a control file are values set for the parameters in the CREATE DB statement as:
- MAXLOGFILES
- MAXDATAFILES
- MAXLOGMEMBERS
- MAXINSTANCES
- MAXLOGHISTORY
Size of the control file will be increased with the increase in values of these parameters.
Initial control file creation:
An initial control file is created of any Oracle Database while you run CREATE DATABASE statement. As seen above, the name of control files is specified by the parameters of CONTROL_FILES during initialization parameter file during database creation. Filenames in the CONTROL_FILES are OS specific. Here is an example of the initialization parameter of CONTROL_FILES:
If another file with the same name exists while creating a database, then you have to specify it using the clause ‘CONTROLFILE REUSE’ along with CREATE DATABASE to avoid an error instance.
If the size of the existing control file is different from the size of the new one created, then it’s not possible to apply the REUSE clause. It is noted that the control file size changes between various Oracle DB release.
It may also change when the number of files in the control file may change in different instances. The configuration parameters in the CREATE DATABASE may also change the file size.
Creating a control file copy:
You can make a copy of a control file for multiplexing by copying the existing control file to another location and then adding that file name to control file listing. In the same way, you can also rename a control file by simply copying the concerned file to another location with a new name.
However, to ensure that the control files don’t change while copying or renaming always shut down the databases before the process. Let’s see the step by step process of a multiplexed copying of a control file.
- Initiate database shut down
- Copy the control file to another location by using the OS commands.
- Edit CONTROL_FILES parameters at parameter file and add a new file name or change the existing filename.
- Now restart the database.
In some cases, it may also become necessary to create new control files if all existing control files in the given database got irreparably damaged and you haven’t kept a backup for control files. We will discuss this process too in the forthcoming articles of the database business leadership series.
- Guide to Choosing the Best Spy Phone App in 2023 - April 20, 2023
- Playable Ads: Get All the Juice Out of Your Advertising Efforts - April 6, 2023
- 10 Most Notorious Black Hat Hackers in History - March 27, 2023