How to recreate a control file in Oracle Database?

Oracle provides the "alter database backup controlfile to trace" command to copy the create database syntax into a trace file in your user dump directory.

Use this command to re-create a control file only if:

• All copies of your existing control files have been lost through media failure.
• You want to change the name of the database.
• You want to change the maximum number of redo log file groups, redo log file members, archived redo log files, datafiles, or instances that can concurrently have the database mounted and open.

SQL>  show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /mnt/ora01/app/oracle/diag/rdbms/QA/QA/trace
[TEST:QA] pwd
/mnt/ora01/app/oracle/diag/rdbms/QA/QA/trace
[TEST:QA] ls -lrt QA_ora_1858.trc
-rw-r-----. 1 oracle oinstall 10645 Jan 23 11:21 QA_ora_1858.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "QA" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/mnt/ora02/oradata/QA/redo01a.rdo',
    '/mnt/ora03/oradata/QA/redo01b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 2 (
    '/mnt/ora02/oradata/QA/redo02a.rdo',
    '/mnt/ora03/oradata/QA/redo02b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 3 (
    '/mnt/ora02/oradata/QA/redo03a.rdo',
    '/mnt/ora03/oradata/QA/redo03b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 4 (
    '/mnt/ora02/oradata/QA/redo04a.rdo',
    '/mnt/ora03/oradata/QA/redo04b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 5 (
    '/mnt/ora02/oradata/QA/redo05a.rdo',
    '/mnt/ora03/oradata/QA/redo05b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 6 (
    '/mnt/ora02/oradata/QA/redo06a.rdo',
    '/mnt/ora03/oradata/QA/redo06b.rdo'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 7 (
    '/mnt/ora02/oradata/QA/redo07a.rdo',
    '/mnt/ora03/oradata/QA/redo07b.rdo'
  ) SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/mnt/ora03/oradata/QA/system01.dbf',
  '/mnt/ora03/oradata/QA/sysaux01.dbf',
  '/mnt/ora06/oradata/QA/undotbs01.dbf',
  '/mnt/ora01/oradata/QA/users01.dbf',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_01.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_02.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_03.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_04.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_05.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_06.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_07.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_08.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_09.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_10.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_11.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_12.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_13.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_14.DBF',
  '/mnt/ora09/oradata/QA/DATA_APPDATA_15.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_01.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_02.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_03.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_04.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_05.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_06.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_07.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_08.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_09.DBF',
  '/mnt/ora08/oradata/QA/INDX_APPINDX_10.DBF'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/mnt/ora01/app/oracle/fast_recovery_area/QA/archivelog/2016_01_23/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/mnt/ora04/oradata/QA/temp01.dbf'
     SIZE 61865984  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TEMP001 ADD TEMPFILE '/mnt/ora05/oradata/QA/TEMP001.DBF'
     SIZE 10000M REUSE AUTOEXTEND ON NEXT 1048576  MAXSIZE 50000M;
-- End of tempfile additions.
--