Oracle 11gR2 and 12c etc. – Create a script that will help recreate a controlfile – using: alter database backup controlfile to trace;

The “alter database backup controlfile to trace;” will create a text based file – human readable – that contains the steps necessary to recreate an Oracle controlfile for a specific database (the database you were connected to when you issue the alter command). See Oracle’s documentation on how to use this file. It actually has comments in it that will also guide you on how to use it.

Two forms of alter database backup controlfile to trace 1st names the file for you and place the file in USER_DUMP_DEST
and the 2nd form allows you to control the location and file name or the trace file (a text script that will assist in controlfile
creation in an emergency).

Note: your alert_<SID>.log will contain a reference to the file creation with name and location. There are a few ways to look at alert log, the one I use is to look for trace file location in v$diag_info – and use it to find the trace file.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/home/oracle/scripts/control-file-trace.ctl’;

Database altered.

SQL> select * from v$diag_info;

INST_ID NAME


TRUE

1 Default Trace File
/u01/app/oracle/diag/rdbms/prodclon/prodclon/trace/prodclon_ora_3976.trc

11 rows selected.

SQL> quit

oracle [prodclon] scripts $ tail /u01/app/oracle/diag/rdbms/prodclon/prodclon/trace/alert_prodclon.log

ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/prodclon/prodclon/trace/prodclon_ora_5568.trc
Completed: ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Thu Dec 30 09:22:41 2021
ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/home/oracle/scripts/control-file-trace.ctl’
Completed: ALTER DATABASE BACKUP CONTROLFILE TO TRACE as ‘/home/oracle/scripts/control-file-trace.ctl’
oracle [prodclon] scripts $

Leave a Comment

Scroll to Top