oracle控制文件(controlfile)丢失恢复方法列举

时间:2022-09-08 14:12:39

oracle控制文件记录着数据文件、日志文件的位置及SCN等信息,十分重要。若丢失数据库无法打开,影响较大,所以官方见采用多路复用的方式进行冗余备份。

控制文件丢失有以下几种情况:

1、有rman备份

对数据库进行过全库备份或者配置CONTROLFILE AUTOBACKUP ON(每次在rman中执行backup时都会备份控制文件),此时控制文件丢失可以使用备份文件进行恢复

RMAN> list backup of controlfile;


备份集列表
===================

BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
1312 Full 9.89M DISK 00:00:02 23-8月 -13
BP 关键字: 1389 状态: AVAILABLE 已压缩: NO 标记: TAG20130823T103349
段名:/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2013_08_23/o1_mf_s_823619925_91fljgwo_.bkp
包括的控制文件: Ckp SCN: 383068075 Ckp 时间: 16-8月 -13


以上是自动备份结果信息


[oracle@redhat4 ~]$ rm /u01/app/oracle/oradata/JIAGULUN/controlfile/o1_mf_7p5b2xty_.ctl
[oracle@redhat4 ~]$ rm /u01/app/oracle/flash_recovery_area/JIAGULUN/controlfile/o1_mf_7p5b2y0c_.ctl


删除控制文件,模拟控制文件丢失


SQL> shutdown immediate
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 734003200 bytes
Fixed Size 2023688 bytes
Variable Size 201330424 bytes
Database Buffers 528482304 bytes
Redo Buffers 2166784 bytes
ORA-00205: error in identifying control file, check alert log for more info

 
ALTER DATABASE   MOUNT
Sat Aug 24 11:05:52 2013
ORA-00202: ????: ''/u01/app/oracle/oradata/JIAGULUN/controlfile/o1_mf_7p5b2xty_.ctl''
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 24 11:05:52 2013
ORA-205 signalled during: ALTER DATABASE MOUNT...
Sat Aug 24 11:07:19 2013
alter database mount
Sat Aug 24 11:07:19 2013
ORA-00202: ????: ''/u01/app/oracle/oradata/JIAGULUN/controlfile/o1_mf_7p5b2xty_.ctl''
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sat Aug 24 11:07:19 2013


在alert日志里看到提示找不到控制文件


RMAN> restore controlfile from autobackup;

启动 restore 于 24-8月 -13
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=155 devtype=DISK

恢复区域目标: /u01/app/oracle/flash_recovery_area
用于搜索的数据库名 (或数据库的唯一名称): ORCL
通道 ORA_DISK_1: 在恢复区域中找到自动备份
通道 ORA_DISK_1: 已找到的自动备份: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2013_08_23/o1_mf_s_823619925_91fljgwo_.bkp
通道 ORA_DISK_1: 从自动备份复原控制文件已完成
输出文件名=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_91j9gw3b_.ctl
输出文件名=/u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_91j9gwfz_.ctl
完成 restore 于 24-8月 -13

2、使用controlfile的trace文件进行恢复

SQL> alter database backup controlfile to trace;

Database altered.

在$oracle/base/udump/找到trace文件打开,得到创建控制文件的脚本

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 '/data/orcl/redo01.log' SIZE 50M,
GROUP 2 '/data/orcl/redo02.log' SIZE 50M,
GROUP 3 '/data/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data/orcl/system01.dbf',
'/data/orcl/undotbs01.dbf',
'/data/orcl/sysaux01.dbf',
'/data/orcl/users01.dbf',
'/data/orcl/TRSWCMNEW.dbf',
'/data/orcl/test.dbf',
'/data/orcl/TEST2.dbf',
'/data/orcl/TEST3.dbf',
'/data/orcl/test0823.dbf'
CHARACTER SET ZHS16GBK
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 8 DAYS');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ENCRYPTION FOR DATABASE','OFF');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- 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 '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_09_18/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_09_18/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_09_18/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_09_18/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_09_18/o1_mf_1_1_%u_.arc';
-- 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 '/data/orcl/temp02.dbf'
SIZE 209715200 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

将数据库启动到nomount状态

查询controlfile位置

SQL> select value from v$parameter where name='control_files';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_91j9gw3b_.ctl, /u01/app/oracle/fl
ash_recovery_area/ORCL/controlfile/o1_mf_91j9gwfz_.ctl

注意:要保证在硬盘这两个文件存在,否则报错,可以touch一下生成这两个文件。


执行生成控制文件脚本

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 '/data/orcl/redo01.log' SIZE 50M,
GROUP 2 '/data/orcl/redo02.log' SIZE 50M,
GROUP 3 '/data/orcl/redo03.log' SIZE 50M
DATAFILE
'/data/orcl/system01.dbf',
2 3 4 5 6 7 8 9 10 11 12 13 '/data/orcl/undotbs01.dbf',
'/data/orcl/sysaux01.dbf',
'/data/orcl/users01.dbf',
'/data/orcl/TRSWCMNEW.dbf',
'/data/orcl/test.dbf',
'/data/orcl/TEST2.dbf',
'/data/orcl/TEST3.dbf',
'/data/orcl/test0823.dbf'
CHARACTER SET ZHS16GBK;
14 15 16 17 18 19 20 21
Control file created.

提示成功

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 11
Current log sequence 13
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM ARCHIVE LOG ALL
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving


SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/orcl/temp02.dbf' SIZE 209715200 REUSE AUTOEXTEND OFF;

Tablespace altered.

至此控制文件恢复成功