Lesson 1

Oracle uses a control file to keep track of the current state of a database. You, the database administrator (DBA), use initialization parameters to control many aspects of how a database operates. This module gives you the information that you need to manage both the control file and the initialization parameters. By the end of this module, you will know how to:

1. Explain the importance of multiplexing the control files
2. Add new control files to a database
3. Move an existing control file
4. Remove a control file from a database
5. Back up your control file
6. View current database parameter settings
7. Change parameter settings while the database is open

The first part of this module concentrates on the skills that you need to manage the control file. The last part of the module covers database parameter settings.

In Oracle Database 23c, the control file is a critical component that maintains metadata about the database's physical structure, such as the locations of data files, redo log files, and other key information. The database administrator (DBA) uses initialization parameters to manage aspects of the control file. Below are the primary initialization parameters used to manage the control file in Oracle 23c, based on available documentation and standard Oracle practices:

• CONTROL_FILES
  • Purpose: Specifies the names and locations of the control files for the database. Oracle writes to all control files listed in this parameter, ensuring redundancy.
  • Usage: This is the primary parameter for control file management. It defines one or more control file paths, separated by commas. Oracle recommends having at least two control files stored on separate physical disk drives to prevent data loss in case of disk failure.
  • Example:
    

    CONTROL_FILES = ('/u01/oracle/oradata/control01.ctl', '/u02/oracle/oradata/control02.ctl')
    

  • Behavior:
    • If not specified, Oracle creates a control file in a default operating system-dependent location, which may not be Oracle Managed Files (OMF) unless OMF-related parameters are set.
    • If files listed in CONTROL_FILES exist during database creation, the CONTROLFILE REUSE clause must be included in the CREATE DATABASE statement to overwrite them.
    • The first file listed is the primary control file read during database operation, but all listed files are maintained and written to.

DB_CREATE_ONLINE_LOG_DEST_n (where n is 1 to 5)

• Purpose: Specifies default locations for creating control files and redo log files when using Oracle Managed Files (OMF). This parameter can override other file destination parameters for control files.
• Usage: When set, it defines up to five directories or Oracle Automatic Storage Management (ASM) disk groups for multiplexing control files. If CONTROL_FILES is not set, Oracle creates OMF control files in these locations.
• Example:

  DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata'
  DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata'
  

• Behavior:
  • Takes precedence over DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST for control file creation.
  • Enhances fault tolerance by allowing multiplexing of control files across multiple locations.
  • If no CONTROL_FILES parameter is set and this parameter is defined, Oracle creates OMF control files in the specified directories.

DB_CREATE_FILE_DEST

• Purpose: Specifies the default directory for creating database files, including control files, when using Oracle Managed Files, if DB_CREATE_ONLINE_LOG_DEST_n is not set.
• Usage: If CONTROL_FILES and DB_CREATE_ONLINE_LOG_DEST_n are not specified, Oracle creates an OMF control file in the directory defined by this parameter.
• Example:

  	DB_CREATE_FILE_DEST = '/u01/oracle/oradata'
  	

• Behavior:
  • Used as a fallback for control file creation if DB_CREATE_ONLINE_LOG_DEST_n is not defined.
  • Works in conjunction with DB_RECOVERY_FILE_DEST to create one control file in each location if both are set and CONTROL_FILES is unset.

DB_RECOVERY_FILE_DEST

• Purpose: Specifies the default location for the Fast Recovery Area, which can store control files, redo log files, and backups.
• Usage: If CONTROL_FILES and DB_CREATE_ONLINE_LOG_DEST_n are not set, Oracle may create an OMF control file in the Fast Recovery Area defined by this parameter.
• Example:

  DB_RECOVERY_FILE_DEST = '/u01/oracle/fast_recovery_area'
  DB_RECOVERY_FILE_DEST_SIZE = 20G
  

• Used as a secondary fallback (after DB_CREATE_ONLINE_LOG_DEST_n and DB_CREATE_FILE_DEST) for control file creation in OMF scenarios.
• Ensures control files are stored in a location optimized for recovery operations.

DB_FILES

• Purpose: Specifies the maximum number of data files that can be opened by the database, indirectly affecting the control file size.
• Usage: The control file maintains metadata about data files, and DB_FILES limits the number of data files the database can manage. If a new file exceeds the MAXDATAFILES limit (set during CREATE DATABASE), the control file expands automatically up to the DB_FILES limit.
• Example:

  	DB_FILES = 200
  	

• Behavior:
  • Impacts the control file’s data file section size, as it determines how many files the control file can track.
  • Should be set higher than the anticipated number of data files to avoid manual intervention for control file expansion.

---

• Oracle Managed Files (OMF): Enabling OMF through DB_CREATE_ONLINE_LOG_DEST_n, DB_CREATE_FILE_DEST, or DB_RECOVERY_FILE_DEST simplifies control file management by automating file creation and naming. If OMF is enabled and CONTROL_FILES is not set, Oracle creates control files in the specified OMF destinations. If a server parameter file (SPFILE) is used, Oracle automatically updates the CONTROL_FILES parameter in the SPFILE. Otherwise, the DBA must manually add it to the text initialization parameter file (PFILE).,
• Control File Size: The size of the control file is influenced by parameters like MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES set during database creation. These parameters determine the metadata stored in the control file and should be set appropriately to avoid frequent resizing.,
• Modifying Control Files: The CONTROL_FILES parameter is not dynamically modifiable (Modifiable: No). Changes require updating the SPFILE (e.g., ALTER SYSTEM SET CONTROL_FILES = ... SCOPE=SPFILE) and restarting the database.
• Best Practices:
  • Always specify multiple control files in CONTROL_FILES and store them on separate disks to ensure redundancy.
  • Use OMF parameters (DB_CREATE_ONLINE_LOG_DEST_n) for simplified management and multiplexing.
  • Regularly back up control files, as they are critical for database recovery.
• Oracle 23c Specifics: While the parameters listed above are consistent with Oracle’s control file management in recent versions (e.g., 19c, 21c), Oracle 23c documentation does not introduce new control file-specific initialization parameters. The behavior remains aligned with prior releases, with potential enhancements in OMF integration or ASM support. Always consult the Oracle 23c Database Reference for any version-specific changes.

Sources:

• Oracle Documentation on Initialization Parameters and Control Files:
• Oracle Database Reference for parameter descriptions

In Oracle Database 19c, multiplexing control files is a critical best practice for database reliability and fault tolerance. It involves maintaining multiple identical copies of control files to protect against corruption or loss.
Here is a detailed explanation:
What is a Control File?
A control file is a small binary file that maintains critical metadata about the database, including:

• Database name and identifier (DBID)
• Data file names and locations
• Online redo log file locations
• Checkpoint information
• Archive log history
• Information about backup and recovery

If a control file is damaged or lost, the database cannot function normally, resulting in immediate downtime or data loss until recovery occurs.

Multiplexing control files addresses these potential risks:
How to Multiplex Control Files in Oracle 19c
Step-by-Step Procedure:
1. Check existing control files:

-- List current control file locations
SELECT name FROM v$controlfile;

Output example:

+--------------------------------------------+
| NAME                                       |
+--------------------------------------------+
| /u01/app/oracle/oradata/ORCLCDB/control01.ctl |
| /u01/app/oracle/oradata/ORCLCDB/control02.ctl |
+--------------------------------------------+

2. Add a new control file copy:
- Shutdown the database gracefully:

SHUTDOWN IMMEDIATE;

- Copy an existing control file to a new location (done at OS level):

cp /u01/app/oracle/oradata/ORCLCDB/control01.ctl /u02/app/oracle/oradata/ORCLCDB/control03.ctl

- Update the initialization parameter (`CONTROL_FILES`) to include the new control file in your `init.ora` or `spfile`:

ALTER SYSTEM SET CONTROL_FILES =
 '/u01/app/oracle/oradata/ORCLCDB/control01.ctl',
 '/u01/app/oracle/oradata/ORCLCDB/control02.ctl',
 '/u02/app/oracle/oradata/ORCLCDB/control03.ctl'
SCOPE=SPFILE;

- Restart the database to apply the change:

SHUTDOWN IMMEDIATE;
STARTUP;

Performance and Reliability Benefits:

• High Availability:
  • Prevents database downtime due to control file corruption or hardware failure.
• Fault Tolerance:
  • Oracle automatically updates all multiplexed control files simultaneously, providing redundancy without performance penalties.
• Easy Recovery:
  • Faster recovery from failures without the need to restore control files from backups, minimizing downtime.

Recommendations for Multiplexing in Oracle 19c:

• At least two or preferably three control files.
• Place copies on different physical disks or storage systems to further mitigate hardware failure risks.
• Regularly verify the status of control files through dynamic views like v$controlfile.

Checking Control File Multiplexing Status:
To confirm successful multiplexing:

SELECT name FROM v$controlfile;

This will show all active control files.
Conclusion Multiplexing control files is a best practice in Oracle 19c databases, greatly reducing the risk of downtime, data loss, and recovery complexity. This ensures that your database remains highly reliable, robust, and easier to manage.
In the next lesson, you will learn what it means to multiplex a control file and why that is important.