Question

Oracle 误删控制文件,oracle 数据库没重启的情况下,怎么恢复？

Answer 1

Linux 平台误删 oracle 数据文件的恢复方法

模拟误操作：

数据库在正常运行,人工直接rm 掉了数据文件。

--1.测试环境情况:

$ cat /etc/redhat-release
CentOS release 6.5 (Final)

select file_name from dba_data_files;
/u01/app/oracle/oradata/orcl/test.dbf

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 3月 5 15:55:14 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

开启数据库归档

archive log list;

数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 3002
当前日志序列 3004

mkdir /u01/arch

alter system set log_archive_dest_1='location=/u01/arch' scope=spfile;

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

Total System Global Area 1.0088E+10 bytes
Fixed Size 2261928 bytes
Variable Size 1644170328 bytes
Database Buffers 8422162432 bytes
Redo Buffers 19595264 bytes
数据库装载完毕。
SQL> alter database archivelog;

数据库已更改。

SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 /u01/arch
最早的联机日志序列 3002
下一个存档日志序列 3004
当前日志序列 3004
SQL> alter database open;

数据库已更改。

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ WRITE

--2.新建测试数据

select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf

SQL> create tablespace wind datafile '/u01/app/oracle/oradata/orcl/wind01.dbf' size 200m;

SQL> create user wind identified by wind01 default tablespace wind;

SQL> grant connect,resource,dba to wind;

$ sqlplus wind/wind01

create table t1
(
sid int not null primary key,
sname varchar2(10)
)
tablespace wind;

--循环导入数据
declare
maxrecords constant int:=100000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t1 values(i,'ocpyang');
end loop;
dbms_output.put_line(' 成功录入数据！ ');
commit;
end;
/

select count(*) from t1;

COUNT(*)
----------
100000

--3.模拟删除数据

SQL> col tablespace_name for a20
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
-------------------- ---------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TTSPACE ONLINE
OCPYANG ONLINE
OCPYANGINDEX ONLINE
LOBOCPYANG ONLINE
LOBOCPYANG01 ONLINE
LOBOCPYANG02 ONLINE

TABLESPACE_NAME STATUS
-------------------- ---------------------------
WIND ONLINE

rm -rf /u01/app/oracle/oradata/orcl/wind01.dbf

ls /u01/app/oracle/oradata/orcl/ | grep wind

SQL> show user;
USER 为 "WIND"
SQL> select count(*) from t1;

COUNT(*)
----------
100000

SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SID NOT NULL NUMBER(38)
SNAME VARCHAR2(10)

SQL> delete from t1 where sid>99000;

已删除1000行。

SQL> commit;

提交完成。

SQL> select count(*) from t1;

COUNT(*)
----------
99000

--4.恢复
ps -eaf|grep dbw0 |grep -v grep
oracle 1928 1 0 15:59 ? 00:00:00 ora_dbw0_orcl

SQL> col tablespace_name for a20
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
-------------------- ---------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TTSPACE ONLINE
OCPYANG ONLINE
OCPYANGINDEX ONLINE
LOBOCPYANG ONLINE
LOBOCPYANG01 ONLINE
LOBOCPYANG02 ONLINE

TABLESPACE_NAME STATUS
-------------------- ---------------------------
WIND ONLINE

cd /proc/1928/fd

ll

lr-x------ 1 oracle oinstall 64 Mar 5 16:20 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Mar 5 16:20 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 10 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkORCL
lr-x------ 1 oracle oinstall 64 Mar 5 16:20 11 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Mar 5 16:20 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 256 -> /u01/app/oracle/oradata/orcl/control01.ctl
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 257 -> /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 258 -> /u01/app/oracle/oradata/orcl/system01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 259 -> /u01/app/oracle/oradata/orcl/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 260 -> /u01/app/oracle/oradata/orcl/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 261 -> /u01/app/oracle/oradata/orcl/users01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 262 -> /u01/app/oracle/oradata/orcl/ttspace01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 263 -> /u01/app/oracle/oradata/orcl/ocpyang01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 264 -> /u01/app/oracle/oradata/orcl/ocpyangindex01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 265 -> /u01/app/oracle/oradata/orcl/lobocpyang01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 266 -> /u01/app/oracle/oradata/orcl/lobocpyang0101.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 267 -> /u01/app/oracle/oradata/orcl/lobocpyang0202.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 268 -> /u01/app/oracle/oradata/orcl/temp01.dbf
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 269 -> /u01/app/oracle/oradata/orcl/wind01.dbf (deleted) --注
lr-x------ 1 oracle oinstall 64 Mar 5 16:20 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 5 16:20 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 5 16:20 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 5 16:20 6 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Mar 5 16:20 7 -> /proc/1928/fd
lr-x------ 1 oracle oinstall 64 Mar 5 16:20 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Mar 5 16:20 9 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_orcl.dat

直接cp该句柄文件名回原位置：

$ pwd
/proc/1928/fd

cp /proc/1928/fd/269 /u01/app/oracle/oradata/orcl/wind01.dbf

NOTE:
由于数据库一直是打开使用的，数据在不断变更则SCN也会不断的变化，
cp数据文件和数据库当前的信息明显不一致，此时需要对数据文件进行recover.

--脱机数据文件
alter database datafile '/u01/app/oracle/oradata/orcl/wind01.dbf' offline;

select count(*) from t1;

第 1 行出现错误:
ORA-00376: 此时无法读取文件 11
ORA-01110: 数据文件 11: '/u01/app/oracle/oradata/orcl/wind01.dbf'

--恢复数据文件
recover datafile '/u01/app/oracle/oradata/orcl/wind01.dbf';

--联机数据文件
alter database datafile '/u01/app/oracle/oradata/orcl/wind01.dbf' online;

select count(*) from t1;

COUNT(*)
----------
99000

成功恢复.