How to get the script to create the control file and rebuild the control file

The control file is a very important data structure for the database and is usually necessary for data recovery.

Oracle provides two ways to back up control files:

1. Generate scripts that can reconstruct control files
2. Backup binary control files

Let's take a look at how to get the script to rebuild the control file.

Oracle provides the following command:

ALTER DATABASE backup Controlfile to trace;

actual operation:

[Oracle@standby tools]$ Sqlplus "/As SYSDBA" sql*plus:release 9.2.0.4.0-production on Sat Oct 08:56:13 >copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: oracle9i Enterprise Edition release 9.2.0.4.0-production with the partitioning option Jserver release 9.2.0.4.0-production sql> ALTER DATABASE backup Controlfile to trace; Database altered. sql> @gettrcname trace_file_name ----------------------------------------------------------------- --------------- /OPT/ORACLE/ADMIN/PRIMARY/UDUMP/PRIMARY_ORA_2135.TRC

Race file Contents:

[Oracle@standby tools]$ more/opt/oracle/admin/primary/udump/primary_ora_2135.trc/opt/oracle/admin/primary/ UDUMP/PRIMARY_ORA_2135.TRC oracle9i Enterprise Edition release 9.2.0.4.0-production with the partitioning option Jserver Release 9.2.0.4.0-production oracle_home =/opt/oracle/product/9.2.0 System name:linux Node name:standby Rele Ase:2.4.21-4.el Version: #1 Fri Oct 3 18:13:58 EDT 2003 machine:i686 Instance name:primary-Redo th Read mounted by this instance:1 Oracle process number:12 Unix process pid:2135, Image:oracle@standby (TNS v1-v3) * * * Session ID: (11.6) 2004-10-16 09:00:03.830 * * 2004-10-16 09:00:03.830 # The following are current System-scope REDO Log Ar Chival related # Parameters and can is included in the database initialization file. # # log_archive_dest= ' # log_archive_duplex_dest= ' # # # # # # # # # # # # # log_archive_format=%t_%s.dbf # remote_archive_enable=true # LOG_ Archive_start=true # log_archive_max_processes=2 # Standby_file_managEment=manual # standby_archive_dest=?/dbs/arch # fal_client= ' # fal_server= ' # log_archive_dest_1= ' LOCATION=/opt/ Oracle/oradata/primary/archive ' # log_archive_dest_1= ' OPTIONAL reopen=300 nodelay ' # log_archive_dest_1= ' ARCH Noaffirm SYNC ' # log_archive_dest_1= ' REGISTER noalternate nodependency ' log_archive_dest_1= ' nomax_failure NOQUOTA_ SIZE noquota_used ' # log_archive_dest_state_1=enable # Below are two sets of SQL statements, each of which creates a new # control file and uses it to open the database. The "The" opens # The database with the Noresetlogs option and should is used only if # The current versions of ' All ' on Line logs are available. The second # set opens the database with the Resetlogs option and should is used # if online logs are. # The appropriate set of statements can is copied from the ' trace into # a script file, edited as necessary, and executed W Hen there is a # need to re-create of the control file. # #The Set #1. Noresetlogs Case # The following commands'll create a new control file and use it # to open the database. # Data used by the recovery manager would be lost. Additional logs may # is required for media recovery of offline data files. Use this # only if the current version of all online logs are available. STARTUP nomount CREATE controlfile reuse DATABASE "PRIMARY" noresetlogs archivelog--SET STANDBY to maximize Performanc E maxlogfiles 5 maxlogmembers 3 maxdatafiles maxinstances 1 maxloghistory LOGFILE GROUP 1 '/ Opt/oracle/oradata/primary/redo01.log ' size 10M, Group 2 '/opt/oracle/oradata/primary/redo02.log ' size 10M, Group 3 '/opt/oracle/oradata/primary/redo03.log ' SIZE 10M--STANDBY LOGFILE datafile '/opt/oracle/oradata/primary/ System01.dbf ', '/opt/oracle/oradata/primary/undotbs01.dbf ', '/opt/oracle/oradata/primary/users01.dbf ' CHARACTER SET ZHS16GBK; # Recovery is required if ' datafiles are restored backups, # or if the last shutdown was nOT normal or immediate. RECOVER DATABASE # All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG all; # Database can now is opened normally. ALTER DATABASE OPEN; # Commands to add tempfiles to temporary tablespaces. # Online Tempfiles have complete space information. # Other tempfiles may require adjustment. ALTER tablespace TEMP ADD tempfile '/opt/oracle/oradata/primary/temp01.dbf ' SIZE 41943040 reuse autoextend on NEXT 6 55360 MAXSIZE 32767M; # End of Tempfile additions. # #The Set #2. Resetlogs Case # The following commands'll create a new control file and use it # to open the database. # The contents of online logs'll be lost and all backups'll # be invalidated. Use this only if online logs are damaged. STARTUP nomount CREATE controlfile reuse DATABASE "PRIMARY" resetlogs archivelog--SET STANDBY to maximize performance Maxlogfiles 5 maxlogmembers 3 maxdatafiles maxinstances 1 maxloghistory LOGFILE GROUP 1 '/op T/oracle/oradata/primary/redo01.log ' size 10M, Group 2 '/opt/oracle/oradata/primary/redo02.log ' size 10M, Group 3 '/ Opt/oracle/oradata/primary/redo03.log ' SIZE 10M--STANDBY LOGFILE datafile '/opt/oracle/oradata/primary/system01.dbf ', '/opt/oracle/oradata/primary/undotbs01.dbf ', '/opt/oracle/oradata/primary/users01.dbf ' CHARACTER SET ZHS16GBK; # Recovery is required if ' datafiles are restored backups, # or if the last shutdown am not normal or immediate . RECOVER database USING BACKUP controlfile # DatabaseCan 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 '/opt/oracle/oradata/primary/temp01.dbf ' SIZE 41943040 reuse autoextend on NEXT 6 55360 MAXSIZE 32767M; # End of Tempfile additions. #

By editing the trace file, we get the script to create the control file.

Depending on the database situation, you can choose to use Resetlogs/noresetlogs to reconstruct the control file.

we have the following script:

[Oracle@standby tools]$ cat createctlf.sql STARTUP nomount CREATE controlfile reuse DATABASE "PRIMARY" Noresetlogs ARC Hivelog maxlogfiles 5 maxlogmembers 3 maxdatafiles maxinstances 1 maxloghistory LOGFILE P 1 '/opt/oracle/oradata/primary/redo01.log ' size 10M, GROUP 2 '/opt/oracle/oradata/primary/redo02.log ' size 10M, GR OUP 3 '/opt/oracle/oradata/primary/redo03.log ' SIZE 10M datafile '/opt/oracle/oradata/primary/system01.dbf ', '/opt/or Acle/oradata/primary/undotbs01.dbf ', '/opt/oracle/oradata/primary/users01.dbf ' CHARACTER SET ZHS16GBK; RECOVER DATABASE ALTER SYSTEM ARCHIVE LOG all; ALTER DATABASE OPEN; ALTER tablespace TEMP ADD tempfile '/opt/oracle/oradata/primary/temp01.dbf ' SIZE 41943040 reuse autoextend on NEXT 6 55360 MAXSIZE 32767M;

Run this script to rebuild the control file:

[Oracle@standby tools]$ Sqlplus "/As SYSDBA" Sql*plus:release 9.2.0.4.0-production on Sat Oct 09:20:24-Copy Right (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to a idle instance. Sql> set echo on sql> @createctlf sql> STARTUP nomount ORACLE instance started. Total System Global area 135337420 bytes Fixed size 452044 bytes Variable Size 109051904 by TEs Database buffers 25165824 bytes Redo buffers 667648 bytes sql> CREATE controlfile Reuse D Atabase "PRIMARY" Noresetlogs archivelog 2--SET STANDBY to maximize performance 3 Maxlogfiles 5 4 MAX 3 5 maxdatafiles 6 maxinstances 1 7 maxloghistory logmembers 8 LOGFILE 9 GROUP 1 '/op T/oracle/oradata/primary/redo01.log ' Size 10M, GROUP 2 '/opt/oracle/oradata/primary/redo02.log ' size 10M, 11 GROUP 3 '/opt/oracle/oradata/primary/redo03.log ' SIZE 10M-STANDBY LogfilE datafile '/opt/oracle/oradata/primary/system01.dbf ', '/opt/oracle/oradata/primary/undotbs01.dbf ', 16 '/opt/oracle/oradata/primary/users01.dbf ' CHARACTER SET ZHS16GBK 18; Control file created. Sql> RECOVER DATABASE ora-00283:recovery session canceled due to errors Ora-00264:no recovery required sql> R SYSTEM ARCHIVE LOG all; System altered. sql> ALTER DATABASE OPEN; Database altered. sql> ALTER tablespace TEMP ADD tempfile '/opt/oracle/oradata/primary/temp01.dbf ' 2 SIZE 41943040 reuse End on NEXT 655360 MAXSIZE 32767M; Tablespace altered. Sql>

The above gives a way to generate scripts to create control files and reconstruct control files, but the problems encountered in a specific recovery may require specific treatment.
This method is typically used in the absence of a control file (binary) backup, if there is a backup should use the backup of the control file to try to recover.

Note: When editing text, you should distinguish between full recovery and incomplete recovery.