"Practice" Trace Text rebuild control file

This little exercise is a quick way to rebuild a control file after all the lost control files.

1. Back up the control files to the trace

SQL>alterdatabasebackup to Trace; Database altered.

2.trace text is placed under the User_dump_dest path

Sql>Show parameterDump; NAME TYPE VALUE------------------------------------ ----------- ------------------------------background_core_dump string Partialbackground_dump_dest string/U01/App/Oracle/Diag/Rdbms/ora 11gr2/Ora11gr2/Tracecore_dump_dest String/U01/App/Oracle/Diag/Rdbms/ora 11gr2/Ora11gr2/cdumpmax_dump_file_size string Unlimitedshadow_core_dump string Partia Luser_dump_dest string/U01/App/Oracle/Diag/Rdbms/ora 11gr2/Ora11gr2/Trace

3. Open alert to view the generated trace

[[email protected]]$cd/U01/App/Oracle/Diag/Rdbms/Ora11gr2/Ora11gr2/Trace[[email protected] Trace]$ cat Alert_ora11gr2.Log BackupControlfile written toTracefile /U01/App/Oracle/Diag/Rdbms/Ora11gr2/Ora11gr2/Trace/ora11gr2_ora_3790.trccompleted:Alter Database BackupControlfile toTracefriDec  Geneva Ten: +: -  .Starting background Process SmcofriDec  Geneva Ten: +: -  .SMCO started withPid= +, OS ID=3907④ sorted by time, latest generation file ranked first[[email protected] Trace]$ ls-LT Total --rw-R-----1 oracle oinstall 50839 DEC 2 10:19 alert_ora11gr2.log-rw-R-----1 oracle oinstall 1068 DEC 2 10:19 ora11gr2_mmon_3778.trc-rw-R-----1 oracle oinstall 2 10:19 ORA11GR2_MMON_3778.TRM-rw-R-----1 oracle oinstall 18936 Dec 2 10:16 ora11gr2_ora_3790.trc-rw-R-----1 oracle oinstall 227 Dec 2 10:16 ora11gr2_ora_3790.trm

4. Copy Create Controlfile

[[email protected] Trace]$ cat Ora11gr2_ora_3790.trcCREATEControlfile ReuseDATABASE"ORA11GR2" Resetlogs noarchivelog maxlogfiles -maxlogmembers3Maxdatafiles -maxinstances8maxloghistory292LOGFILEGROUP 1 '/u01/app/oracle/oradata/ora11gr2/redo01.log'SIZE 50M BLOCKSIZE +,  GROUP 2 '/u01/app/oracle/oradata/ora11gr2/redo02.log'SIZE 50M BLOCKSIZE +,  GROUP 3 '/u01/app/oracle/oradata/ora11gr2/redo03.log'SIZE 50M BLOCKSIZE +--STANDBY LOGFILEdatafile'/u01/app/oracle/oradata/ora11gr2/system01.dbf',  '/u01/app/oracle/oradata/ora11gr2/sysaux01.dbf',  '/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf',  '/u01/app/oracle/oradata/ora11gr2/users01.dbf',  '/u01/app/oracle/oradata/ora11gr2/example01.dbf'CHARACTER SETAl32utf8;

5. Database boot to Nomount state

SQL>  startup nomountoracle instance started. Total System  Globalarea830930944  bytesfixed Size                  2257800  bytesvariable Size             536874104  bytesDatabase buffers           289406976  Bytesredo buffers                2392064 bytes

6. Rebuilding control files based on trace text

Sql> CREATEControlfile ReuseDATABASE"ORA11GR2" Resetlogs noarchivelog2Maxlogfiles -  3Maxlogmembers3  4Maxdatafiles -  5Maxinstances8  6Maxloghistory292  7LOGFILE8    GROUP 1 '/u01/app/oracle/oradata/ora11gr2/redo01.log'SIZE 50M BLOCKSIZE +,  9    GROUP 2 '/u01/app/oracle/oradata/ora11gr2/redo02.log'SIZE 50M BLOCKSIZE +, Ten    GROUP 3 '/u01/app/oracle/oradata/ora11gr2/redo03.log'SIZE 50M BLOCKSIZE +  One  --STANDBY LOGFILE  Adatafile -    '/u01/app/oracle/oradata/ora11gr2/system01.dbf',  -    '/u01/app/oracle/oradata/ora11gr2/sysaux01.dbf',  the    '/u01/app/oracle/oradata/ora11gr2/undotbs01.dbf',  -    '/u01/app/oracle/oradata/ora11gr2/users01.dbf',  -    '/u01/app/oracle/oradata/ora11gr2/example01.dbf'  -  CHARACTER SETAl32utf8 +  ; ControlfileCreated.

7. Play Open: Error, need resetlogs

Sql> Alter Database Open;Alter Database Open*ERROR at line1: ORA-01589: Must UseResetlogsorNoresetlogsoption  for Database OpenSQL> Alter Database Openresetlogs;Databasealtered. SQL> SelectStatus fromv$instance; STATUS------------OPEN

"Practice" Trace Text rebuild control file