Oracle文件损坏及恢复的过程-mysql教程

一、基本情况 OS：RHEL 3 CPU：4个Intel(R) Xeon(TM) MP CPU 2.70GHz Mem：8G Swap：16G Disk：120G Or

一、基本情况

OS：RHEL 3

CPU：4个Intel(R) Xeon(TM) MP CPU 2.70GHz

Mem：8G

Swap：16G

Disk：120G

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0

二、备份方式

数据库以archive模式运行，RMAN多级增量备份。策略如下：

设置控制文件自动备份。

每三个月做一个数据库的全备份（包括所有得数据库和只读表空间）,并备份归档日志。

每一个月做一次零级备份（不包含只读表空间），并备份归档日志。

每周做一次一级备份，并备份归档日志。

每天做一次二级备份，并备份归档日志。

三、 恢复案例

所有恢复的前提：已经做过数据库全备份（包括归档日志），控制文件和spfile自动备份。

1． 损坏一个数据文件

（1）故障模拟

删除数据文件：rm /u02/oradata/dbnms/users01.dbf

关闭数据库：shutdown immediate;

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

强行关闭：sutdown abort;

启动数据库：startup;

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'

（2）恢复步骤

rman target sys@dbnms catalog rmanuser@cata

run{

allocate channel c1 type disk;

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

sql 'alter database open';

release channel c1;

}

sqlplus sys as sysdba

select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

dbnms OPEN

恢复成功

如果：数据空间没有备份，，又要强行启动数据库

Sql> shutdown immediate;

Sql>startup mount;

Sql>alter database datafile '/oradata/test.dbf'' offline drop;

Sql>alter database open;

2． 损坏全部数据文件

（1）故障模拟

删除数据文件：rm /u02/oradata/dbnms/*.dbf

强行关闭：sutdown abort;

启动数据库：startup;

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u02/oradata/dbnms/system01.dbf'

（2）恢复步骤

rman target sys@dbnms catalog rmanuser@cata

run{

allocate channel c1 type disk;

restore database;

recover database;

sql 'alter database open';

release channel c1;

}

sqlplus sys as sysdba

select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

dbnms OPEN

恢复临时文件：

alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;

alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50M autoextend on next 5M maxsize unlimited;

恢复成功

3． 损坏非当前联机日志成员

（1）故障模拟

删除日志文件：rm /u02/oradata/dbnms/redo01.log

关闭数据库：shutdown immediate;

启动数据库：startup;

select * from v$logfile;--可以考虑从v$log视图中去查找

GROUP# STATUS TYPE MEMBER IS_

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

3 ONLINE /u02/oradata/dbnms/redo03.log NO

2 STALE ONLINE /u02/oradata/dbnms/redo02.log NO

1 INVALID ONLINE /u02/oradata/dbnms/redo01.log NO

1 STALE ONLINE /u02/oradata/dbnms/redo11.log NO

1 STALE ONLINE /u02/oradata/dbnms/redo21.log NO

2 STALE ONLINE /u02/oradata/dbnms/redo12.log NO

3 ONLINE /u02/oradata/dbnms/redo13.log NO

2 STALE ONLINE /u02/oradata/dbnms/redo22.log NO

3 ONLINE /u02/oradata/dbnms/redo23.log NO

4 ONLINE /u02/oradata/dbnms/redo31.log NO

4 ONLINE /u02/oradata/dbnms/redo32.log NO

GROUP# STATUS TYPE MEMBER IS_

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

4 ONLINE /u02/oradata/dbnms/redo33.log NO

（2）恢复步骤

alter database drop logfile member '/u02/oradata/dbnms/redo01.log';

alter database add logfile member '/u02/oradata/dbnms/redo01.log' to group 1;

恢复成功