怎么进行oracle控制文件的恢复

共计 4754 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章将为大家详细讲解有关怎么进行 oracle 控制文件的恢复，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

1 重建控制文件

SYS@hyyk
alter database backup controlfile to /home/oracle/controlfile01.ctl

2. 删除当前使用的控制文件

SYS@hyyk
ho rm -rf /u01/app/oracle/oradata/hyyk/control01.ctl

3. 从启数据库

SYS@hyyk
startup force

ORACLE instance started.

Total System Global Area  776646656 bytes

Fixed Size    2217384 bytes

Variable Size    578816600 bytes

Database Buffers    188743680 bytes

Redo Buffers    6868992 bytes

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

4. 拷贝备份的控住文件

SYS@hyyk ho
cp /home/oracle/controlfile01.ctl /u01/app/oracle/oradata/hyyk/control01.ctl

SYS@hyyk
alter database mount;

Database altered.

SYS@hyyk
alter database open;

alter database open

*

ERROR at line 1:

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

SYS@hyyk
alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be
consistent

ORA-01110: data file 1: /u01/app/oracle/oradata/hyyk/system01.dbf

SYS@hyyk
recover database using backup controlfile;

ORA-00279: change 1324612 generated at
11/03/2017 11:03:43 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/archive/arch_1_943793507_23.log

ORA-00280: change 1324612 for thread 1 is
in sequence #23

这里查看 /u01/app/oracle/archive/arch_1_943793507_23.log 是否存在

如果不存在说明没有归档，

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

/u01/app/oracle/oradata/hyyk/redo02.log

Log applied.

Media recovery complete.

// 以 RESETLOGS 的方式启动数据库

SYS@hyyk
alter database open resetlogs;

Database altered.

// 及时进行全库备份

实验 2 修改数据库结构后丢所有的控制文件

实验思路：1. 备份当前的控制文件

2. 建立一个表空间和表

 
3. 删除当前的控制文件

  4. 从启数据库

  5. 发现控制文件和数据文件不一致

备份控制文件

SYS@hyyk
alter database backup controlfile to /home/oracle/control01.ctl

创建表空间和表

SYS@hyyk
create tablespace t2 datafile /u01/app/oracle/oradata/hyyk/t2.dbf size 10m;

SYS@hyyk
create table b(id number) tablespace t2;

SYS@hyyk
insert into b values(2);

SYS@hyyk
commit;

删除当前的控制文件

SYS@hyyk
ho cp /home/oracle/control01.ctl /u01/app/oracle/oradata/hyyk/control01.ctl

SYS@hyyk
ho cp /home/oracle/control01.ctl /u01/app/oracle/oradata/hyyk/control02.ctl

将数据库 mount 阶段

SYS@hyyk
alter database mount;

将数据库打开

SYS@hyyk
alter database open;

alter database open

*

ERROR at line 1:

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

SYS@hyyk
alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:
/u01/app/oracle/oradata/hyyk/system01.dbf

查看当前正在使用的日志

SYS@hyyk   select * from v$log;

 
GROUP#  THREAD#  SEQUENCE# 
BYTES  BLOCKSIZE  MEMBERS ARC

———- ———- ———- ———-
———- ———- —

STATUS  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
NEXT_TIME

—————- ————- ———
———— ———

 
1  1  1 
52428800  512  1 YES

INACTIVE  1347611 05-NOV-17  1354710 05-NOV-17

  3  1  3 
52428800  512  1 NO

CURRENT 
1358125 05-NOV-17  2.8147E+14

 
2  1  2 
52428800  512  1 YES

INACTIVE  1354710 05-NOV-17  1358125 05-NOV-17

恢复当前的数据库

SYS@hyyk
recover database using backup controlfile;

ORA-00279: change 1358125 generated at 11/05/2017 12:37:33
needed for thread 1

ORA-00289: suggestion :
/u01/app/oracle/archive/arch_1_959256089_3.log

ORA-00280: change 1358125 for thread 1 is in sequence #3

// 这里查看是否存在归档日志，如果存在下面选择 auto，如果不存在就输入当前的 rodo log

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

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

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media
recovery

ORA-01110: data file 7: /u01/app/oracle/oradata/hyyk/t2.dbf

ORA-01112: media recovery not started

SYS@hyyk
select name from v$datafile;

NAME

——————————————————————————–

/u01/app/oracle/oradata/hyyk/system01.dbf

/u01/app/oracle/oradata/hyyk/sysaux01.dbf

/u01/app/oracle/oradata/hyyk/undotbs01.dbf

/u01/app/oracle/oradata/hyyk/users01.dbf

/u01/app/oracle/oradata/hyyk/example01.dbf

/u01/app/oracle/oradata/hyyk/t1.dbf

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007

数据文件改名

SYS@hyyk
alter database create datafile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007
as

 
2 
/u01/app/oracle/oradata/hyyk/t2.dbf

9. 恢复数据库

SYS@hyyk
recover database using backup controlfile;

ORA-00279: change 1364363 generated at
11/05/2017 13:51:22 needed for thread 1

ORA-00289: suggestion :
/u01/app/oracle/archive/arch_1_959256089_3.log

ORA-00280: change 1364363 for thread 1 is
in sequence #3

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

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

Log applied.

Media recovery complete.

SYS@hyyk
alter database open resetlogs;

Database altered.

查看数据文件

SYS@hyyk
select name from v$datafile;

NAME

——————————————————————————–

/u01/app/oracle/oradata/hyyk/system01.dbf

/u01/app/oracle/oradata/hyyk/sysaux01.dbf

/u01/app/oracle/oradata/hyyk/undotbs01.dbf

/u01/app/oracle/oradata/hyyk/users01.dbf

/u01/app/oracle/oradata/hyyk/example01.dbf

/u01/app/oracle/oradata/hyyk/t1.dbf

/u01/app/oracle/oradata/hyyk/t2.dbf

7 rows selected.

SYS@hyyk select * from b;

 

  ID

———-

关于怎么进行 oracle 控制文件的恢复就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。