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

Source: Internet
Author: User
Tags session id sqlplus

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.