Recover your Control Files in Oracle 19c from a trace backup

If because of some catastrophic disk failure you lose all the control files for your database, you can still recover them as long as you have been faithful about backing up the control file to trace after each structural change. The trace file will have the necessary commands to create a new control file, restart, and recover your database.

You can recover your control files in Oracle 19c from a trace backup if all control files are lost due to catastrophic disk failure.
Here's the detailed step-by-step procedure:
Step 1: Locate the Trace Backup File
- Whenever you execute the command:

  ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
  

Oracle generates a readable text script containing SQL commands needed for control file recovery.
- This file is typically stored in:

  $ORACLE_BASE/diag/rdbms///trace
  

- It typically has a name like: `ora_{pid}.trc`.
Step 2: Identify the Correct Trace File
- Navigate to the trace directory:

cd $ORACLE_BASE/diag/rdbms///trace

- Search for recent backup trace files:

grep -l "CREATE CONTROLFILE" *.trc | sort -t_ -k3,3nr | head

- Open and verify the identified trace file. You should see a section starting with:

CREATE CONTROLFILE REUSE DATABASE ...

Step 3: Edit the Trace File
- From the trace file, you'll see something similar to this:

CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG
-- OR --
CREATE CONTROLFILE REUSE DATABASE "DBNAME" RESETLOGS ARCHIVELOG

• Decide between:
  • RESETLOGS: If the database requires recovery from a backup or if redo logs are also lost or corrupted.
  • NORESETLOGS: If redo logs are intact and in sync.
• If all control files are lost and the database is inconsistent, typically you will use:
  

  CREATE CONTROLFILE REUSE DATABASE "DBNAME" RESETLOGS ARCHIVELOG
  

Step 3: Shut Down the Database
- Shut down the database completely (if still running):

SHUTDOWN ABORT;

Step 4: Prepare the Database For the following list of 2 elements, put the elements in a HTML unordered list and the child elements of the unordered list into another unordered list.

• Replace or restore necessary database files (data files, redo logs, archived logs) to their original or backup locations.
• Ensure the files mentioned in the control file creation script exist and match the paths listed in the trace file.

Step 5: Start the Instance in NOMOUNT Mode

STARTUP NOMOUNT;

Step 5: Recreate the Control File from Trace
- Execute the control file creation script (copied from trace file):

-- Example
CREATE CONTROLFILE REUSE DATABASE "DBNAME" RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/DBNAME/redo01.log' SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/DBNAME/redo02.log',
  GROUP 3 '/u01/app/oracle/oradata/DBNAME/redo03.log'
DATAFILE
  '/u01/app/oracle/oradata/DBNAME/system01.dbf',
  '/u01/app/oracle/oradata/DBNAME/sysaux01.dbf',
  '/u01/app/oracle/oradata/DBNAME/undotbs01.dbf',
  '/u01/app/oracle/oradata/DBNAME/users01.dbf'
CHARACTER SET AL32UTF8;

- Run this script as SYSDBA in SQL*Plus or SQL Developer.
Step 6: Recover the Database
If RESETLOGS was specified, perform recovery:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

- When prompted for archived logs, provide paths or type `CANCEL` if none available or recovery is complete.
Step 7: Open Database
- Open the database with RESETLOGS option (mandatory if RESETLOGS was used):

ALTER DATABASE OPEN RESETLOGS;

- If NORESETLOGS was used and logs are intact:

ALTER DATABASE OPEN;

Step 7: Backup Immediately After Recovery
After recovering and opening the database with RESETLOGS, take a full backup immediately because the database now operates with a new incarnation:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
-- Perform full RMAN or user-managed backup now.

Recommendations for Best Practices:
- Regularly execute:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

- Schedule periodic RMAN backup of the control file:

BACKUP CURRENT CONTROLFILE FORMAT '/backup/controlfile_%U.bkp';

- Store backup files off-site or in cloud storage.
Conclusion

• Yes, losing all control files is severe but recoverable using the control file backup trace method.
• It's essential to consistently generate control file backups after structural changes to protect against catastrophic scenarios.

This practice ensures your Oracle 19c database can quickly recover, maintain integrity, and minimize downtime.

1. Make sure that the database is completely shut down.
2. Back up all the database files.
3. Edit the trace file, and remove all the messages preceding the STARTUP NOMOUNT command. Save the resulting file. You may want to change the extension from .trc to .sql.
4. Start Server Manager and connect as the INTERNAL user.
5. User Server Manager's @ command to execute the trace file.

When you execute the trace file, the first thing that happens is that the STARTUP NOMOUNT command starts up the Oracle instance. Next, a CREATE CONTROLFILE command is executed. The CREATE CONTROLFILE command is what actually re-creates your control files. The remaining commands in the trace file initiate recovery and then open the database.

The legacy code below is for Oracle 8i.

$SQL>

Oracle Server Manager Release 3.1.5.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8i Release 8.1.5.0.0 - Production

With the Partitioning and Java options

PL/SQL Release 8.1.5.0.0 - Production

SQL> connect internal

Connected.

SVRMGR > @ora00241.sql

ORACLE instance started.

Total System Global Area                         34451404 bytes

Fixed Size                                          65484 bytes

Variable Size                                    17469440 bytes

Database Buffers                                 16384000 bytes

Redo Buffers                                       532480 bytes

Statement processed.

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

Statement processed.

SVRMGR >

There are other reasons to re-create a control file besides recovering from a disk failure. If you have moved database files to new locations, re-creating the control file is one way to tell Oracle about that. You can also re-create the control file to make it larger. For example, you may want to increase the MAXDATAFILES or MAXLOGFILES limits. Re-creating the control file allows you to do that.
In the next lesson, you will learn how to check current database parameter settings.

Click the Quiz link below to answer a few questions about re-creating control files.
Recreating Control Files - Quiz