根据进程找回oracle控制文件,oracle控制文件(controlfile)丢失恢复方法列举

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. 至此控制文件恢复成功

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值