如何获得创建控制文件的脚本并重建控制文件

如何获得创建控制文件的脚本并重建控制文件

控制文件对于数据库来说是非常重要的数据结构，在进行数据恢复时通常是必不可少的.
Oracle提供两种方式备份控制文件:
1.生成可以重建控制文件的脚本
2.备份二进制的控制文件
我们看一下如何获得可以重建控制文件的脚本.
Oracle提供如下命令:

alter database backup controlfile to trace;

实际操作:

[oracle@standby tools]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Oct 16 08:56:13 2004

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

控制文件对于数据库来说是非常重要的数据结构，在进行数据恢复时通常是必不可少的. oracle提供两种方式备份控制文件: 1.生成可以重建控制文件的脚本 2.备份二进制的控制文件 我们看一下如何获得可以重建控制文件的脚本. oracle提供如下命令: alter database backup controlfile to trace; 实际操作: [oracle@standby tools]$ sqlplus "/ as sysdba" sql*plus: release 9.2.0.4.0 - production on sat oct 16 08:56:13 2004 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 trace文件内容: [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 release: 2.4.21-4.el version: #1 fri oct 3 18:13:58 edt 2003 machine: i686 instance name: primary redo thread 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 archival related # parameters and can be 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 first set opens # the database with the noresetlogs option and should be used only if # the current versions of all online logs are available. the second # set opens the database with the resetlogs option and should be used # if online logs are unavailable. # the appropriate set of statements can be copied from the trace into # a script file, edited as necessary, and executed when there is a # need to re-create the control file. # # set #1. noresetlogs case # # the following commands will create a new control file and use it # to open the database. # data used by the recovery manager will be lost. additional logs may # be 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 performance maxlogfiles 5 maxlogmembers 3 maxdatafiles 100 maxinstances 1 maxloghistory 226 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 any of the 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 be 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 655360 maxsize 32767m; # end of tempfile additions. # # set #2. resetlogs case # # the following commands will create a new control file and use it # to open the database. # the contents of online logs will be lost and all backups will # 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 100 maxinstances 1 maxloghistory 226 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 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 '/opt/oracle/oradata/primary/temp01.dbf' size 41943040 reuse autoextend on next 655360 maxsize 32767m; # end of tempfile additions. # 编辑这个trace文件,我们就可以获得创建控制文件的脚本. 根据数据库不同状况，你可以选择是使用resetlogs/noresetlogs来重建控制文件. 我们获得以下脚本: [oracle@standby tools]$ cat createctlf.sql startup nomount create controlfile reuse database "primary" noresetlogs archivelog -- set standby to maximize performance maxlogfiles 5 maxlogmembers 3 maxdatafiles 100 maxinstances 1 maxloghistory 226 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 ; 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 655360 maxsize 32767m; 运行这个脚本即可重建控制文件: [oracle@standby tools]$ sqlplus "/ as sysdba" sql*plus: release 9.2.0.4.0 - production on sat oct 16 09:20:24 2004 copyright (c) 1982, 2002, oracle corporation. all rights reserved. connected to an 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 bytes database buffers 25165824 bytes redo buffers 667648 bytes sql> create controlfile reuse database "primary" noresetlogs archivelog 2 -- set standby to maximize performance 3 maxlogfiles 5 4 maxlogmembers 3 5 maxdatafiles 100 6 maxinstances 1 7 maxloghistory 226 8 logfile 9 group 1 '/opt/oracle/oradata/primary/redo01.log' size 10m, 10 group 2 '/opt/oracle/oradata/primary/redo02.log' size 10m, 11 group 3 '/opt/oracle/oradata/primary/redo03.log' size 10m 12 -- standby logfile 13 datafile 14 '/opt/oracle/oradata/primary/system01.dbf', 15 '/opt/oracle/oradata/primary/undotbs01.dbf', 16 '/opt/oracle/oradata/primary/users01.dbf' 17 character set zhs16gbk 18 ; control file created. sql> recover database ora-00283: recovery session canceled due to errors ora-00264: no recovery required sql> alter 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 autoextend on next 655360 maxsize 32767m; tablespace altered. sql> 以上给出生成创建控制文件脚本并重建控制文件的方法，但是具体恢复中遇到的问题可能需要具体对待. 这种方法通常是在没有控制文件(二进制文件)备份的情况下所采用的，如果存在备份应该使用备份的控制文件尝试恢复.