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

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

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;

编辑这个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

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> .
这种方法通常是在没有控制文件(二进制文件)备份的情况下所采用的，如果存在备份应该使用备份的控制文件尝试恢复.