oracle 控制文件误删除手动恢复小测试

测试系统 OLinux 5.9 oracle版本 11.2.0.4

备份控制文件

1.备份到trace文件

SQL> alter database backup controlfile to trace;

Database altered.

查看告警日志，确定备份控制文件trace的位置信息

alter database backup controlfile to trace

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_1452.trc

Completed: alter database backup controlfile to trace

oracle 10g 控制文件备份在udump目录中

2.备份控制文件为binary文件

SQL> alter database backup controlfile to ‘/oracle/control20150608_bk.ctl‘;

Database altered.

切换日志，完成全库备份（临时表空间存放的是临时数据不需要备份，online redolog在归档模式下自动备份，不需要手动备份）

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

实验一：

丢失控制文件如何恢复

手动删除所有控制文件，尝试重启数据库

[oracle@ora11g prod]$ ls

control01.ctl  control02.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@ora11g prod]$ rm -rf *.ctl

SQL> shutdown abort                    

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  534462464 bytes

Fixed Size                  2254952 bytes

Variable Size             209717144 bytes

Database Buffers          314572800 bytes

Redo Buffers                7917568 bytes

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

告警日志错误信息

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: ‘/u01/app/oracle/oradata/prod/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/prod/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...

Sat Jun 06 09:40:05 2015

Checker run found 2 new persistent data failures

恢复，将备份的binary控制文件恢复至原来控制文件位置

SQL> !

[oracle@ora11g prod]$ cp /oracle/control20150608_bk.ctl ./control01.ctl

[oracle@ora11g prod]$ cp /oracle/control20150608_bk.ctl ./control02.ctl

[oracle@ora11g prod]$ exit

当前数据库为nomount状态，尝试mount

SQL> alter database mount;

Database altered.

尝试打开数据库

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00279: change 976301 generated at 06/06/2015 08:48:22 needed for thread 1

ORA-00289: suggestion : /oracle/arch2/1_6_881656848.dbf

ORA-00280: change 976301 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 976728 generated at 06/06/2015 09:12:40 needed for thread 1

ORA-00289: suggestion : /oracle/arch2/1_7_881656848.dbf

ORA-00280: change 976728 for thread 1 is in sequence #7

ORA-00278: log file ‘/oracle/arch2/1_6_881656848.dbf‘ no longer needed for this

recovery

ORA-00279: change 976731 generated at 06/06/2015 09:12:41 needed for thread 1

ORA-00289: suggestion : /oracle/arch2/1_8_881656848.dbf

ORA-00280: change 976731 for thread 1 is in sequence #8

ORA-00278: log file ‘/oracle/arch2/1_7_881656848.dbf‘ no longer needed for this

recovery

ORA-00279: change 976734 generated at 06/06/2015 09:12:42 needed for thread 1

ORA-00289: suggestion : /oracle/arch2/1_9_881656848.dbf

ORA-00280: change 976734 for thread 1 is in sequence #9

ORA-00278: log file ‘/oracle/arch2/1_8_881656848.dbf‘ no longer needed for this

recovery

ORA-00308: cannot open archived log ‘/oracle/arch2/1_9_881656848.dbf‘

ORA-27037: unable to obtain file status

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

Additional information: 3

找不到归档日志/oracle/arch2/1_9_881656848.dbf

此归档日志可能为当前日志组日志

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

         1          1          0   52428800        512          1 YES CURRENT                       0                      0

         3          1          0   52428800        512          1 YES UNUSED                      0                      0

         2          1          0   52428800        512          1 YES UNUSED                       0                      0

当前日志组为3

SQL> select group#, member from v$logfile;

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/prod/r

           edo03.log

         2 /u01/app/oracle/oradata/prod/r

           edo02.log

         1 /u01/app/oracle/oradata/prod/r

           edo01.log

重新recover

SQL> recover database using backup controlfile;

ORA-00279: change 976301 generated at 06/06/2015 08:48:22 needed for thread 1

ORA-00289: suggestion : /oracle/arch2/1_6_881656848.dbf

ORA-00280: change 976301 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/prod/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

数据库恢复成功

实验二，关库删除控制文件，用trace备份的控制文件进行恢复

在实验一中数据库被resetlogs打开过，需要重新对控制文件进行备份

SQL> alter database backup controlfile to trace;

Database altered.

告警日志信息：

alter database backup controlfile to trace

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_10568.trc

Completed: alter database backup controlfile to trace

正常关库删除控制文件

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@ora11g ~]$ cd $ORACLE_BASE/oradata/prod/

[oracle@ora11g prod]$ ls

control01.ctl  control02.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@ora11g prod]$ rm -rf *.ctl

[oracle@ora11g prod]$ ls

redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@ora11g prod]$ 

尝试打开数据库

[oracle@ora11g prod]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 6 14:15:11 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> 

SQL> 

SQL> startup 

ORACLE instance started.

Total System Global Area  534462464 bytes

Fixed Size                  2254952 bytes

Variable Size             209717144 bytes

Database Buffers          314572800 bytes

Redo Buffers                7917568 bytes

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

告警日志报错：

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: ‘/u01/app/oracle/oradata/prod/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/prod/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...

Sat Jun 06 15:31:09 2015

Checker run found 2 new persistent data failures

查看前trace备份的控制文件

more /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_10568.trc

创建控制文件的语句如下

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

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

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

  GROUP 3 ‘/u01/app/oracle/oradata/prod/redo03.log‘  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  ‘/u01/app/oracle/oradata/prod/system01.dbf‘,

  ‘/u01/app/oracle/oradata/prod/sysaux01.dbf‘,

  ‘/u01/app/oracle/oradata/prod/undotbs01.dbf‘,

  ‘/u01/app/oracle/oradata/prod/users01.dbf‘

CHARACTER SET AL32UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

-- disk. Any one log file from each branch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE ‘/oracle/arch1/1_1_824297850.dbf‘;

-- ALTER DATABASE REGISTER LOGFILE ‘/oracle/arch1/1_1_881678511.dbf‘;

-- 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 ‘/u01/app/oracle/oradata/prod/temp01.dbf‘

     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

--

开始尝试创建控制文件

[oracle@ora11g prod]$ exit

exit

SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 ‘/u01/app/oracle/oradata/prod/redo01.log‘  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 ‘/u01/app/oracle/oradata/prod/redo02.log‘  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 ‘/u01/app/oracle/oradata/prod/redo03.log‘  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    ‘/u01/app/oracle/oradata/prod/system01.dbf‘,

 14    ‘/u01/app/oracle/oradata/prod/sysaux01.dbf‘,

 15    ‘/u01/app/oracle/oradata/prod/undotbs01.dbf‘,

 16    ‘/u01/app/oracle/oradata/prod/users01.dbf‘

 17  CHARACTER SET AL32UTF8

 18  ;

Control file created.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

ORA-00279: change 977244 generated at 06/06/2015 15:30:21 needed for thread 1

ORA-00289: suggestion : /oracle/arch1/1_6_881678511.dbf

ORA-00280: change 977244 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log ‘/oracle/arch1/1_6_881678511.dbf‘

ORA-27037: unable to obtain file status

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

Additional information: 3

ORA-00308: cannot open archived log ‘/oracle/arch1/1_6_881678511.dbf‘

ORA-27037: unable to obtain file status

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

Additional information: 3

找不到归档日志/oracle/arch1/1_6_881678511.dbf

此归档日志可能为当前日志组日志

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

         1          1          0   52428800        512          1 YES UNUSED                       0                      0

         3          1          0   52428800        512          1 YES CURRENT                      0                      0

         2          1          0   52428800        512          1 YES UNUSED                       0                      0

当前日志组为3

SQL> select group#, member from v$logfile;

    GROUP# MEMBER

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

         3 /u01/app/oracle/oradata/prod/r

           edo03.log

         2 /u01/app/oracle/oradata/prod/r

           edo02.log

         1 /u01/app/oracle/oradata/prod/r

           edo01.log

重新recover

SQL> recover database using backup controlfile;

ORA-00279: change 977244 generated at 06/06/2015 15:30:21 needed for thread 1

ORA-00289: suggestion : /oracle/arch1/1_6_881678511.dbf

ORA-00280: change 977244 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/prod/redo03.log

Log applied.

Media recovery complete.

开库

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

恢复成功

本文出自 “DBA的天空” 博客，请务必保留此出处http://kevinora.blog.51cto.com/9406404/1659757