三. 发现表被删除, 开始着手解决 1. 该表所在表空间离线 ( 确保删除表所在位置不会被重写 ) SQL> alter tablespace raw_odu offline; Tablespace altered. 2. 通过 logmnr, 找出被删除的数据 data _object _id 1

Size: px

Start display at page:

Download "三. 发现表被删除, 开始着手解决 1. 该表所在表空间离线 ( 确保删除表所在位置不会被重写 ) SQL> alter tablespace raw_odu offline; Tablespace altered. 2. 通过 logmnr, 找出被删除的数据 data _object _id 1"

即伴须
5 years ago
Views:

1 使用 odu 恢复被 drop 表过程一. 数据库版本 SQL> select * from v$version; BANNER Oracle9i Enterprise Edition Release Production PL/SQL Release Production CORE Production TNS for Linux: Version Production NLSRTL Version Production 二. 模拟环境 1. 在裸设备空间下创建实验表 ( 想验证下裸设备下 odu 的工作情况 ) SQL> create tablespace raw_odu datafile '/dev/raw/raw1' size 10m autoextend on next 10m maxsize 4800M; Tablespace created. SQL> create table t_xifenfei tablespace raw_odu 2 as 3 select * from dba_objects; Table created. 2. 写入数据文件 SQL> alter system checkpoint; System altered. 3. 查询表中记录数 ( 便于恢复后核对 ) SQL> select count(*) from chf.t_xifenfei; COUNT(*) 删除表 SQL> drop table chf.t_xifenfei; Table dropped.

2 三. 发现表被删除, 开始着手解决 1. 该表所在表空间离线 ( 确保删除表所在位置不会被重写 ) SQL> alter tablespace raw_odu offline; Tablespace altered. 2. 通过 logmnr, 找出被删除的数据 data _object _id 1) 安装 logmnr Package created. Grant succeeded. Procedure created. No errors. Grant succeeded. Package created. 2) 设置 utl_file_dir 目录, 该步骤可能需要重启 SQL> alter system set utl_file_dir='/tmp' scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes

3 Redo Buffers bytes Database mounted. Database opened. 3) 加载日志文件开始挖日志, 找出相关数据 ( 添加你删除表时候的归档日志或者 redo 日志 ) SQL> execute dbms_logmnr_d.build('testtrace.ora', '/tmp',options =>dbms_logmnr_d.store_in_flat_file); SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename=>'/tmp/recover/redo01.log'); SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename=>'/tmp/recover/redo02.log'); SQL> execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename=>'/tmp/recover/redo03.log'); SQL> execute dbms_logmnr.start_logmnr(dictfilename =>'/tmp/testtrace.ora'); SQL> create table logmnr1 2 nologging 3 as select * from 4 V$LOGMNR_CONTENTS; Table created. SQL> exec dbms_logmnr.end_logmnr 4) 查询 data _object _id SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from logmnr1 2 where sql_redo like '%t_xifenfei%' and operation='ddl';

4 TO_CHAR(TIMESTAMP,' SQL_REDO :22:46 create table t_xifenfei tablespace raw_odu as select * from dba_objects; :37:55 drop table chf.t_xifenfei; SQL> select sql_redo from logmnr1 where 2 TIMESTAMP=to_date(' :37:55','yyyy-mm-dd hh24:mi:ss') and operation='delete'; SQL_REDO delete from "SYS"."OBJ$" where "OBJ#" = ' ' and "DATAOBJ#" = ' ' and "OWNER#" = '70' and "NAME" = 'T_XIFENFEI' and "NAMESPACE" = '1' and "SUBNAME" I S NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('12-FEB-12', 'DD-MON-RR') and "MT IME" = TO_DATE('12-FEB-12', 'DD-MON-RR') and "STIME" = TO_DATE('12-FEB-12', 'DD- MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and ROWI D = 'AAAAASAAgAAAA+HAAB'; 四. 开始使用 odu 找回被删除表 0.odu 版本 Oracle Data Unloader:Release 配置 control.txt 文件 SQL> col name for a30 SQL> select ts#,file#,rfile#,name FROM V$DATAFILE; TS# FILE# RFILE# NAME

5 0 1 1 /tmp/recover/system /tmp/recover/undotbs01.dbf /tmp/recover/user01.dbf /dev/raw/raw /tmp/recover/system 启动 odu 遇到问题 read data error from file '/dev/raw/raw1'.error message:invalid argument 需要使用设备名称, 而不能直接使用裸设备名 can not open file /dev/sdb1!error message:permission denied 该设备使用 oracle 用户不能直接访问, 需要授权或者直接使用 root 运行 odu 3.odu 找回数据 1) 扫描被删除表所在表空间 ODU> scan extent tablespace 14 scan extent start: :54:06 scanning extent... scanning extent finished. scan extent completed: :54:07 2) 尝试恢复数据 ODU> unload object sample Unloading Object,object ID: , Cluster: 0 output data is in file : 'data/odu_ txt' Sample result: object id: tablespace no: 14 sampled 1059 rows column count: 13 column 1 type: VARCHAR2 column 2 type: VARCHAR2 column 3 type: RAW column 4 type: NUMBER column 5 type: NUMBER column 6 type: VARCHAR2 column 7 type: DATE column 8 type: DATE column 9 type: VARCHAR2 column 10 type: VARCHAR2 column 11 type: VARCHAR2 column 12 type: VARCHAR2 column 13 type: VARCHAR2

6 COMMAND: unload object tablespace 14 column VARCHAR2 VARCHAR2 RAW NUMBER NUMBER VARCHAR2 DATE DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 3) 正式恢复数据 ODU> unload object tablespace 14 column VARCHAR2 VARCHAR2 RAW NUMBER NUMBER VARCHAR2 DATE DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 Unloading Object,object ID: , Cluster: rows unloaded 五. 查看找回来数据 [root@localhost data]# pwd /tmp/odu/data [root@localhost data]# ll total rw-r--r-- 1 root root 580 Feb 12 10:09 ODU_ ctl -rw-r--r-- 1 root root 370 Feb 12 10:09 ODU_ sql -rw-r--r-- 1 root root Feb 12 10:09 ODU_ txt -rw-r--r-- 1 root root 1141 Feb 12 09:54 sample.txt 六. 导入数据 Table created. [oracle@localhost data]$ sqlldr "'/ as sysdba'" control=/tmp/odu/data/odu_ ctl SQL*Loader: Release Production on Sun Feb 12 10:12: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Commit point reached - logical record count 226 Commit point reached - logical record count 452 Commit point reached - logical record count 678 Commit point reached - logical record count Commit point reached - logical record count 10848

7 Commit point reached - logical record count SQL> select count(*) from ODU_ ; COUNT(*) 至此找回被 drop 表完成, 至于怎么把这种临时表中的数据, 转为业务数据, 那就不是我的工作了

Oracle 4

Oracle 4 01 04 Oracle 07 Oracle Oracle Instance Oracle Instance Oracle Instance Oracle Database Oracle Database Instance Parameter File Pfile Instance Instance Instance Instance Oracle Instance System