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

« 关于PUSH_SUBQ提示的说明 | Blog首页 | 发现一个有趣的网站-www.breakthechain.org »

控制文件对于数据库来说是非常重要的数据结构，在进行数据恢复时通常是必不可少的.

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>

以上给出生成创建控制文件脚本并重建控制文件的方法，但是具体恢复中遇到的问题可能需要具体对待.
这种方法通常是在没有控制文件(二进制文件)备份的情况下所采用的，如果存在备份应该使用备份的控制文件尝试恢复.

历史上的今天...
>> 2019-10-16文章:

2019，国产数据库元年开启新纪元

>> 2010-10-16文章:

《Oracle DBA手记 2》即将出版

>> 2009-10-16文章:

Oracle OpenWorld 2009 第五日 - 与奥巴马擦肩

Oracle OpenWorld 2009 第四日 - I'll be back

>> 2008-10-16文章:

史上最强黑屏行动-微软20日黑屏反盗版验证

>> 2007-10-16文章:

Baby、恩墨-我的儿子

>> 2006-10-16文章:

推荐Veritas VCS系列模拟器软件

>> 2005-10-16文章:

Statspack ORA-00001 错误的解决

By eygle on 2004-10-16 15:03 | Comments (4) | HowTo | 68 |

4 Comments

xiaoyang | September 13, 2007 11:23 AM

您好，Eygle：
我做一个表空间的恢复测试如下：
1、Rman备份全库。
2、Drop一个表空间后。
3、用备份的Controlfile进行全库的Restore，结果正确。
4、再次Drop表空间后，想单独恢复这个表空间，发现无法进行，提示如下：
RMAN> restore datafile 5;

Starting restore at 13-SEP-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/13/2007 11:14:52
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 5

继续恢复实验中，请指正，谢谢！

eygle | September 13, 2007 11:29 AM

如果你需要恢复一个误drop的数据文件，只能执行不完全恢复。

如果是OS上rm掉的，则没有问题。

Nik | October 28, 2009 2:19 AM

重新生成控制文件一定要手工编辑trace文件吗？

suncsb | October 21, 2010 2:25 PM

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/primary/temp01.dbf'
2 SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

为什么使用新的控制文件之后，oracle认不到原来的临时表空间的信息呢？其他的表空间都可以认到，就是临时表空间认不到，非得执行一下上面的语句呢？临时表空间和其他表空间区别在哪呢？oracle是出于哪一点才这样设计的？