Oracle的控制文件的恢复与重建

当数据丢失一个控制文件
模拟丢失控制文件
[oracle@localhost orcl]$ pwd

/u01/app/oracle/oradata/orcl

[oracle@localhost orcl]$ ls

control01.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf

example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

[oracle@localhost orcl]$ rm -rf control01.ctl
重启后报错，查看错误日志

 SQL> startup force

ORACLE instance started.

Total System Global Area  780824576 bytes

Fixed Size            2217424 bytes

Variable Size          557845040 bytes

Database Buffers      218103808 bytes

Redo Buffers            2658304 bytes

ORA-00205: error in identifying control file, check alert log for more info

[oracle@localhost trace]$ pwd

/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@localhost trace]$ vi alert_orcl.log

Mon Dec 05 21:25:31 2016

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Mon Dec 05 21:25:32 2016

Checker run found 1 new persistent data failures

ORA-205 signalled during: ALTER DATABASE   MOUNT...

可以看到control01.ctl不见了

解决方法很简单，先把数据库关闭，然后把另一个控制文件改名复制到该目录下即可

SQL> shutdown abort

ORACLE instance shut down.

[oracle@localhost orcl]$ pwd

/u01/app/oracle/flash_recovery_area/orcl

[oracle@localhost orcl]$ ls

control02.ctl

[oracle@localhost orcl]$ cp control02.ctl /u01/app/oracle/oradata/orcl/control01.ctl

SQL> startup

ORACLE instance started.

Total System Global Area  780824576 bytes

Fixed Size            2217424 bytes

Variable Size          557845040 bytes

Database Buffers      218103808 bytes

Redo Buffers            2658304 bytes

Database mounted.

Database opened.

要是所有的控制文件都丢失了呢？

得分两种情况

第一种，有二进制 备份的情况下

模拟丢失，数据库中所有的控制文件删除

[oracle@localhost orcl]$ rm control02.ctl

[oracle@localhost orcl]$ rm /u01/app/oracle/oradata/orcl/control01.ctl

SQL> startup force

ORA-00205: error in identifying control file, check alert log for more info

[oracle@localhost trace]$ vi alert_orcl.log

RA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE   MOUNT...

Mon Dec 05 21:38:38 2016

Checker run found 1 new persistent data failures

开始进行恢复

oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 5 21:49:22 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> set dbid=1449044261;

executing command: SET DBID

RMAN> startup nomount;

Oracle instance started

Total System Global Area     780824576 bytes

Fixed Size                     2217424 bytes

Variable Size                557845040 bytes

Database Buffers             218103808 bytes

Redo Buffers                   2658304 bytes

RMAN> restore controlfile from autobackup ;

RMAN> recover database;

RMAN> alter database open resetlogs;

database opened

数据库已经恢复完成，查看下演化了新的incarnation

RMAN> list incarnation ;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORCL     1449044261       PARENT  1          15-AUG-09

2       2       ORCL     1449044261       PARENT  945184     30-AUG-16

3       3       ORCL     1449044261       CURRENT 1094224    05-DEC-16

所有必须要重新对数据库进行备份

RMAN> backup as compressed backupset database plus archivelog;

第二种，没有二进制备份的情况下

只能对控制文件进行 重建了

SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';

Database altered.

跟上面一样模拟错误

[oracle@localhost orcl]$ rm control02.ctl

[oracle@localhost orcl]$ rm /u01/app/oracle/oradata/orcl/control01.ctl

SQL> startup force

ORA-00205: error in identifying control file, check alert log for more info

从/home/oracle/control.sql中截取下面语句到/home/oracle/control01.sql

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/orcl/system01.dbf',

  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',

  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',

  '/u01/app/oracle/oradata/orcl/users01.dbf',

  '/u01/app/oracle/oradata/orcl/example01.dbf'

CHARACTER SET AL32UTF8

;

SQL> set sqlblanklines on

SQL> @/home/oracle/control01.sql

Control file created.

SQL> set sqlblanklines on

SQL> @/home/oracle/control01.sql

Control file created.

SQL> RECOVER DATABASE;

Media recovery complete.

SQL> ALTER DATABASE OPEN;

Database altered.

自此，控制文件重建成功，数据库恢复完成

就算你没有对控制文件进行文本备份，也可以到官方文档中查询创建语句
但是你要清楚数据库的一些构造，如日志文件，数据文件的名字和位置，数据库实例名，字符集等等。
就能够自己写创建语句了。
ps：最后，控制文件重建是一件非常危险的事情，不到万不得已，不要轻易尝试。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31386161/viewspace-2129861/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31386161/viewspace-2129861/