Backing up control file

To protect yourself from the complete loss of your database control file further, Oracle enables you to back up a control file while the database is running. Usually you do this by telling Oracle to generate the necessary commands to re-create the control file in the event that all multiplexed copies are lost. The following SQL command backs up a control file by generating the commands necessary to re-create it:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

You can issue this command from SQL*Plus. You need to have the ALTER DATABASE system privilege to issue it. As a result of executing this command, Oracle will generate a CREATE CONTROLFILE statement and place it in a trace file. The trace file will be in the directory pointed to by the user_dump_dest parameter.

The following documentation reflects modern database practices, updated syntax, and file path conventions aligned with Oracle's current recommendations.
Purpose: The following SQL statements are used to rebuild the control file, perform media recovery if necessary, and open the Oracle Database. This script assumes all online redo logs are available and intact.
🔒 Caution: If any datafiles have been restored or the database was not shut down cleanly, recovery will be required before opening the database.
🛠️ Control File Rebuild Script

-- Start the instance without mounting the database
STARTUP NOMOUNT;

-- Recreate the control file using existing datafile and logfile metadata
CREATE CONTROLFILE REUSE DATABASE "COIN" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 64
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 1
    MAXLOGHISTORY 800
LOGFILE
    GROUP 1 ('/u01/oradata/COIN/redo01.log') SIZE 200M,
    GROUP 2 ('/u01/oradata/COIN/redo02.log') SIZE 200M,
    GROUP 3 ('/u01/oradata/COIN/redo03.log') SIZE 200M
DATAFILE
    '/u01/oradata/COIN/system01.dbf',
    '/u01/oradata/COIN/sysaux01.dbf',
    '/u01/oradata/COIN/undotbs01.dbf',
    '/u01/oradata/COIN/users01.dbf'
CHARACTER SET AL32UTF8;

🔄 Perform Media Recovery (If Needed)

-- Apply archived redo logs to bring datafiles up to date
RECOVER DATABASE;

If recovery is not required (e.g., clean shutdown, no missing datafiles), this step may be skipped.
📤 Archive Logs (Optional but Recommended)

-- Archive all online redo logs before opening
ALTER SYSTEM ARCHIVE LOG ALL;

-- Open the database using the newly created control file
ALTER DATABASE OPEN;

📌 Additional Consideration: Snapshot Control File Location (Optional)
In environments using Recovery Manager (RMAN), you may optionally specify the location of the snapshot control file:

-- Configure snapshot control file location for RMAN use
EXECUTE DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('/u01/app/oracle/backup/snapcf_coin.f');

📘 Summary
This script is appropriate for controlled recovery scenarios where:

• All datafiles are available
• The database is not part of an Oracle RAC or Data Guard configuration
• The DBA has sufficient privileges (e.g., SYSDBA)

For environments using Oracle Managed Files (OMF)^[1] or multitenant architecture, filenames and file paths should be updated accordingly.

Oracle 23c still generates trace files, but the directory structure and file management system has evolved significantly.
🔁 Key Differences in Oracle 23c

Aspect	Oracle 8i–10g (udump)	Oracle 11g–23c (ADR-based)
Location	Manually defined via user_dump_dest	Automatically managed by ADR (diag tree)
Path Example	E:\Oracle\ADMIN\coin\udump\	$ORACLE_BASE/diag/rdbms/dbname/instance/trace/
Configuration Parameter	user_dump_dest	DIAG_ADR_ENABLED = ON (default)
File Types	.trc (only)	.trc, .trm, .xml (structured metadata/logs)
Naming Format	ora<nnnnn>.trc	db_name_ora_<PID>.trc or db_name_mmdd_pid.trc
Access Method	Manual file browsing	adrci CLI or Oracle Enterprise Manager
Trace Trigger	SQL trace, ALTER SESSION	Same, plus automatic diagnostic capture via Health Monitor, Incident Packaging Service (IPS)

To find a control file creation trace or any trace file:

-- Navigate to ADR
cd $ORACLE_BASE/diag/rdbms/yourdb/yourdb/trace/

-- Or use ADRCI to list recent files
adrci> show tracefile
adrci> show alert
adrci> show incident

🧾 Summary

• The .trc extension still exists in Oracle 23c, but the file path, naming convention, and management are now handled by the Automatic Diagnostic Repository (ADR).
• The legacy udump directory is no longer used in modern versions.
• Diagnostic workflows now benefit from structured logging, incident reporting, and integration with Enterprise Manager or adrci.

To find the file containing the generated command, you need to look at the time stamp on all the files in the user_dump_dest directory. Find the file (or files) created at about the time that you issued the ALTER DATABASE command and look in those files. One of them should contain the CREATE CONTROLFILE command. The following simulation walks you through the process of backing up a control file to trace:

1. This is the SQL*Plus screen. You have already connected to the database as the SYSTEM user. Now issue the alter database backup controlfile to trace; command. Be sure to press the ENTER key
2. SQL prompt
3. alter database backup controlfile to trace
4. Syntax error. Type alter database backup controlfile to trace; then press Enter

1. Connect as SYSDBA:
   

   sqlplus / as sysdba
   

2. Issue Command:
   

   ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
   

3. Determine Trace File Path:
   

   SHOW PARAMETER diagnostic_dest;
   

4. Use adrci to locate the latest file:
   

   adrci> show tracefile
   adrci> show trace -t
   

5. Open Trace File:
   Navigate to the trace directory and open the most recent .trc file containing CREATE CONTROLFILE.

Here is a "Bash command-line script" for "Linux-based Oracle 23c environments" that automates the following:

• Executes ALTER DATABASE BACKUP CONTROLFILE TO TRACE
• Uses adrci to locate the most recent trace file
• Filters and displays only the part containing the CREATE CONTROLFILE statement

📜 `backup_controlfile_trace.sh`

#!/bin/bash

# Ensure you're running as the oracle user or with environment variables set
export ORACLE_SID=yourdb
export ORACLE_HOME=/u01/app/oracle/product/23c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Timestamp for comparison
echo "Backing up control file to trace..."
sqlplus -s / as sysdba << EOF
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
EXIT;
EOF

# Use ADRCI to find the most recent trace file
echo "Locating the most recent trace file..."
LATEST_TRACE=$(adrci exec="SHOW TRACEFILE" | grep '\.trc' | tail -1)

# Extract full path of the trace file
TRACE_DIR=$(adrci exec="SHOW BASE" | grep -v "ADR base is" | awk '{print $1}')
TRACE_PATH="$TRACE_DIR/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/$LATEST_TRACE"

echo "Found trace file: $TRACE_PATH"
echo "Extracting CREATE CONTROLFILE statement..."

# Extract and display only the relevant part
awk '/CREATE CONTROLFILE/,/;/' "$TRACE_PATH"

🔧 Setup Instructions

1. Replace yourdb with your actual ORACLE_SID.
2. Ensure the script is saved as backup_controlfile_trace.sh.
3. Run the script as the Oracle software owner:

chmod +x backup_controlfile_trace.sh
./backup_controlfile_trace.sh

🧾 Output Example
The script will print:

• Confirmation of the backup
• The name of the latest trace file
• The full CREATE CONTROLFILE SQL block extracted from the trace

Click the Exercise link below to practice backing up your control file to trace.
Backing Control File - Exercise
In the next lesson, you will learn how to create a new control file from scratch.

[1] Oracle Managed Files (OMF): Oracle Managed Files (OMF) in Oracle Database 23c simplifies database administration by allowing the database to automatically manage the naming and location of database files. When using OMF, you specify directories for file creation, and Oracle handles the creation, naming, and deletion of files like datafiles, redo logs, and control files. This reduces administrative overhead and the potential for human errors associated with manual file management.