单个dbf故障的恢复
归档模式下普通users表空间某一个数据文件的删除
备注：这里不管是表空间的第几个文件，也不管表空间是否就这一个dbf，对于这种情况dbf的删除的处理方式都是一样的。
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/dong/user02.dbf' size 10M autoextend on;
SQL> create table t1203(id int) tablespace users;
Table created.
SQL> declare
2 i number;
3 begin
4 for i in 1..9999 loop
5 insert into t1203 values(i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from t1203;
COUNT(*)
----------
9999
SQL> select segment_name,file_id from dba_extents where segment_name='T1203';
SEGMENT_NAME FILE_ID
--------------------------------------------------------------------------------- ----------
T1203 4
T1203 4
T1203 4
SQL> insert into t1203 select * from t1203;
9999 rows created.
SQL> /
19998 rows created.
SQL> /
39996 rows created.
SQL> /
79992 rows created.
SQL> select segment_name,file_id from dba_extents where segment_name='T1203';
SEGMENT_NAME FILE_ID
--------------------------------------------------------------------------------- ----------
T1203 4
T1203 4
T1203 4
T1203 4
T1203 4
T1203 4
T1203 4
T1203 4
T1203 4
T1203 4
T1203 5
SEGMENT_NAME FILE_ID
--------------------------------------------------------------------------------- ----------
T1203 5
T1203 5
T1203 5
T1203 5
T1203 5
T1203 5
17 rows selected.
SQL> commit;
Commit complete.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/dong/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/dong/users01.dbf ONLINE
5 /u01/app/oracle/oradata/dong/user02.dbf ONLINE
SQL> !rm -rf /u01/app/oracle/oradata/dong/user02.dbf或者执行alter database datafile '/u01/app/oracle/oradata/dong/user02.dbf' offline drop;
备注：如果执行offline drop的话，那查看dbf的状态会发现马上就是recover了，但如果是rm的话，就需要我们执行rm的话，dbf的状态不会马上改变，需要有检查点触发或者shutdown再启动的时候才会看到状态为recover。
SQL> select count(*) from t1203;
COUNT(*)
----------
159984
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2092498944 bytes
Fixed Size 1337604 bytes
Variable Size 268437244 bytes
Database Buffers 1811939328 bytes
Redo Buffers 10784768 bytes
Database mounted.
Database opened.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/dong/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/dong/users01.dbf ONLINE
5 /u01/app/oracle/oradata/dong/user02.dbf RECOVER
SQL> select count(*) from t1203;
select count(*) from t1203
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/dong/user02.dbf'
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database create datafile '/u01/app/oracle/oradata/dong/user02.dbf';
Database altered.
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> select count(*) from t1203;
COUNT(*)
----------
159984
非归档下offline drop数据文件的恢复
SQL> create tablespace mm datafile '/u01/app/oracle/oradata/dong/mm01.dbf' size 10M autoextend on;
Tablespace created.
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1415653
2 1415653
3 1415653
4 1415653
5 1416158
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1415653
2 1415653
3 1415653
4 1415653
5 1416158
SQL> alter system checkpoint;
System altered.
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1416431
2 1416431
3 1416431
4 1416431
5 1416431
SQL> create table tmm(id int) tablespace mm;
Table created.
SQL> insert into tmm select object_id from t1;
71892 rows created.
SQL> commit;
Commit complete.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/dong/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/dong/users01.dbf ONLINE
5 /u01/app/oracle/oradata/dong/mm01.dbf ONLINE
SQL> alter database datafile 5 offline drop;
Database altered.
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1416431
2 1416431
3 1416431
4 1416431
5 1416431
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/dong/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/dong/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/dong/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/dong/users01.dbf ONLINE
5 /u01/app/oracle/oradata/dong/mm01.dbf RECOVER
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL>
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/dong/mm01.dbf'
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2092498944 bytes
Fixed Size 1337604 bytes
Variable Size 268437244 bytes
Database Buffers 1811939328 bytes
Redo Buffers 10784768 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> select file#,CHECKPOINT_CHANGE# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1417012
2 1417012
3 1417012
4 1417012
5 1416431
SQL> alter database open resetlogs;
Database altered.
SQL> col name for a35
SQL> col value for a10
SQL> col describ for a80
SQL> set linesize 200
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%_allow_resetlogs_corruption%';
NAME VALUE DESCRIB
----------------------------------- ---------- --------------------------------------------------------------------------------
_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24500180/viewspace-1062039/ ，如需转载，请注明出处，否则将追究法律责任。
