程序员人生 网站导航

误删 oracle 数据文件的恢复

栏目:数据库应用时间:2015-03-30 08:59:06
Linux 平台误删 oracle 数据文件的恢复方法 摹拟误操作: 数据库在正常运行,人工直接rm 掉了数据文件。 -⑴.测试环境情况: $ 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 星期4 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 下1个存档日志序列 3004 当前日志序列 3004 SQL> alter database open; 数据库已更改。 SQL> select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ READ WRITE -⑵.新建测试数据 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 -⑶.摹拟删除数据 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 -⑷.恢复 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: 由于数据库1直是打开使用的,数据在不断变更则SCN也会不断的变化, cp数据文件和数据库当前的信息明显不1致,此时需要对数据文件进行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 成功恢复. ---⑸.简单验证 ls /u01/app/oracle/oradata/orcl/ | grep wind wind01.dbf select count(*) from t1; COUNT(*) ---------- 99000 alter system switch logfile; SQL> shutdown immediate; 数据库已关闭。 已卸载数据库。 ORACLE 例程已关闭。 SQL> startup 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> insert into t1 values(9999999,'rmfiletest'); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select * from t1 where rownum=1 order by sid desc; SID SNAME ---------- ------------------------------ 9999999 rmfiletest


 

------分隔线----------------------------
------分隔线----------------------------

最新技术推荐