sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle非归档模式恢复删除数据的3种方法详细步骤方法

时间:2016-11-27 19:55   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库数据文件丢失,需要如何最大程度抢救数据(数据文件丢失-非归档)。介绍三种方法恢复数据。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 如何最大程度抢救数据(数据文件丢失-非归档)

今天一朋友问到一个比较有意思的问题:如果一个表空间,其中一个数据文件丢失,在无备份和非归档的情况下,如何最大程度的把表空间里面的数据给抢救出来?

我当时直接的回答是,使用exp进行逻辑导出,当时对方直接说exp是不行的,真是这样吗?

这篇文章就的目的就是为了解答这个问题,首先准备下测试环境:
SQL > SELECT file_id,file_name,bytes/1024/1024,tablespace_name,AUTOEXTENSIBLE
  2  FROM dba_data_files
  3  ORDER BY 1;

FILE_ID FILE_NAME                                  BYTES/1024/1024 TABLESPACE_NAME   AUT
------- ------------------------------------------ --------------- ----------------- ---
1      /home/ora10g/oradata/roger/system01.dbf                450 SYSTEM            YES
2      /home/ora10g/oradata/roger/undotbs01.dbf               925 UNDOTBS1          YES
3      /home/ora10g/oradata/roger/sysaux01.dbf                260 SYSAUX            YES
4      /home/ora10g/oradata/roger/users01.dbf                   5 USERS             YES
5      /home/ora10g/oradata/roger/roger01.dbf                  10 ROGER             NO
6      /home/ora10g/oradata/roger/roger02.dbf                  10 ROGER             NO
7      /home/ora10g/oradata/roger/roger03.dbf                  10 ROGER             NO

7 ROWS selected.

SQL > CREATE USER roger IDENTIFIED BY roger DEFAULT tablespace roger;
USER created.

SQL > GRANT CONNECT,resource,dba TO roger;
GRANT succeeded.

SQL > conn roger/roger
Connected.

SQL > CREATE TABLE killdb1
  2    AS SELECT * FROM sys.dba_objects;

TABLE created.

SQL > CREATE TABLE killdb2
  2    AS SELECT * FROM killdb1;

TABLE created.

SQL > BEGIN
  2    FOR i IN 1..100 loop
  3      INSERT /*+ append */INTO killdb2 SELECT * FROM killdb2;
  4      commit;
  5    END loop;
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-01653: unable TO extend TABLE ROGER.KILLDB2 BY 128 IN tablespace ROGER
ORA-06512: at line 3

SQL > analyze TABLE killdb1 compute statistics;
TABLE analyzed.

SQL > analyze TABLE killdb2 compute statistics;
TABLE analyzed.

SQL > conn /AS sysdba
Connected.

SQL > SELECT DISTINCT dbms_rowid.rowid_relative_fno(rowid) file#
  2    FROM roger.killdb1
  3   UNION ALL
  4  SELECT DISTINCT dbms_rowid.rowid_relative_fno(rowid) file#
  5    FROM roger.killdb2;

FILE#
----------
6
5
7
6
5
7

6 ROWS selected.

SQL > shutdown immediate;

DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL > startup mount
ORA-00000: normal, successful completion

SQL > conn /AS sysdba
Connected TO an idle instance.

SQL > startup mount

ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE              79693032 bytes
DATABASE Buffers           83886080 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.

SQL > ALTER DATABASE noarchivelog;
DATABASE altered.

SQL > ALTER DATABASE OPEN;
DATABASE altered.

SQL > !rm /home/ora10g/oradata/roger/roger03.dbf

SQL > shutdown immediate;

DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL > startup
ORA-00000: normal, successful completion

SQL > conn /AS sysdba
Connected TO an idle instance.

SQL > startup

ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE              79693032 bytes
DATABASE Buffers           83886080 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
ORA-01157: cannot identify/LOCK DATA file 7 - see DBWR trace file
ORA-01110: DATA file 7: '/home/ora10g/oradata/roger/roger03.dbf'

SQL > SELECT STATUS FROM v$instance;

STATUS
------------
MOUNTED

SQL > ALTER DATABASE datafile 7 offline DROP;
DATABASE altered.

SQL > ALTER DATABASE OPEN;
DATABASE altered.

SQL > SELECT COUNT(*) FROM roger.killdb1;
SELECT COUNT(*) FROM roger.killdb1
*
ERROR at line 1:
ORA-00376: file 7 cannot be READ at this TIME
ORA-01110: DATA file 7: '/home/ora10g/oradata/roger/roger03.dbf'

情景我们已经模拟出来来,现在问题是我们要如何去最大限度的抢救killdb1,killdb2里面的数据(虽然会丢失部分数据)。
'###### 第一种方式 ######'

[ora10g@killdb ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[ora10g@killdb ~]$ exp roger/roger file=killdb1.dmp tables=killdb1 grants=n indexes=n

Export: Release 10.2.0.5.0 - Production on Wed Nov 2 20:52:53 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                        KILLDB1
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/home/ora10g/oradata/roger/roger03.dbf'
Export terminated successfully with warnings.

[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y

Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:55:43 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition elease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing 'ROGER's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "KILLDB1" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "
""SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER,"
" "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTA"
"MP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED"
"" VARCHAR2(1), "SECONDARY" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 M"
"AXTRANS 255 STORAGE(INITIAL 6291456 NEXT 1048576 FREELISTS 1 FREELIST GROUP"
"S 1 BUFFER_POOL DEFAULT) TABLESPACE "ROGER" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace ROGER
Import terminated successfully with warnings.
++++++ roger表空间不足,我需要扩容一下 ++++++

SQL > ALTER DATABASE datafile 5 resize 20m;
DATABASE altered.

[ora10g@killdb ~]$ imp system/oracle file=killdb1.dmp fromuser=roger touser=system indexes=n grants=n IGNORE=y

Import: Release 10.2.0.5.0 - Production on Wed Nov 2 20:56:32 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ROGER, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing 'ROGER's objects into SYSTEM
. . importing table                      "KILLDB1"       9500 rows imported
Import terminated successfully without warnings.

SQL > conn /AS sysdba
Connected.

SQL > SELECT COUNT(*) FROM system.killdb1;

COUNT(*)
----------
9500

SQL > SELECT * FROM SYSSEGOBJ
  2   WHERE obj# IN ( SELECT object_id
  3                     FROM dba_objects
  4                    WHERE object_name='KILLDB1' AND owner='ROGER'
  5                 );

OBJ#        FILE#     BLOCK#   TYPE      PCTFREE$   PCTUSED$
----------  -----     ------ ------      --------   --------
52059          5         11  TABLE           10         40

SQL > SELECT file#,BLOCK#,BLOCKS,EXTENTS
  2    FROM seg$
  3   WHERE file#=5 AND block#=11;

FILE#     BLOCK#     BLOCKS    EXTENTS
---------- ---------- ---------- ----------
5         11        768         21

SQL > col owner FOR a10
SQL > SELECT a.owner,
2         a.segment_name,
3         a.initial_extent,
4         b.file_id,
5         a.extents,
6         b.extent_id,
7         b.blocks,
8         a.HEADER_FILE,
9         a.HEADER_BLOCK
10    FROM dba_segments a, dba_extents b
11   WHERE a.owner = b.owner
12     AND a.segment_name = b.segment_name
13     AND a.owner = 'ROGER'
14     AND b.segment_name = 'KILLDB1'
15   ORDER BY 6;

OWNER      SEGMENT_NAME    INITIAL_EXTENT    FILE_ID    EXTENTS  EXTENT_ID     BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER      KILLDB1                  65536          5         21          0          8           5           11
ROGER      KILLDB1                  65536          5         21          1          8           5           11
ROGER      KILLDB1                  65536          5         21          2          8           5           11
ROGER      KILLDB1                  65536          5         21          3          8           5           11
ROGER      KILLDB1                  65536          5         21          4          8           5           11
ROGER      KILLDB1                  65536          5         21          5          8           5           11
ROGER      KILLDB1                  65536          5         21          6          8           5           11
ROGER      KILLDB1                  65536          5         21          7          8           5           11
ROGER      KILLDB1                  65536          5         21          8          8           5           11
ROGER      KILLDB1                  65536          5         21          9          8           5           11
ROGER      KILLDB1                  65536          5         21         10          8           5           11
ROGER      KILLDB1                  65536          5         21         11          8           5           11
ROGER      KILLDB1                  65536          5         21         12          8           5           11
ROGER      KILLDB1                  65536          5         21         13          8           5           11
ROGER      KILLDB1                  65536          5         21         14          8           5           11
ROGER      KILLDB1                  65536          5         21         15          8           5           11
ROGER      KILLDB1                  65536          7         21         16        128           5           11
ROGER      KILLDB1                  65536          6         21         17        128           5           11
ROGER      KILLDB1                  65536          5         21         18        128           5           11
ROGER      KILLDB1                  65536          7         21         19        128           5           11
ROGER      KILLDB1                  65536          6         21         20        128           5           11

21 ROWS selected.

SQL > SELECT owner,
2         TABLE_NAME,
3         NUM_ROWS,
4         BLOCKS,
5         EMPTY_BLOCKS,
6         AVG_SPACE,
7         AVG_ROW_LEN
8    FROM DBA_TAB_STATISTICS
9   WHERE TABLE_NAME = 'KILLDB1'
10     AND owner = 'ROGER';

OWNER      TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
---------- ------------------------------ ---------- ---------- ------------ ---------- -----------
ROGER      KILLDB1                             50094        708           60        861          97

SQL > SELECT 16*8*(50094/708) FROM dual;

16*8*(50094/708)
----------------
9056.54237
通过上面的查询,我们可以看出,exp在抽取数据的时候,是根据extent来的,换句话说在exp导出表killdb1时,只是抽取了前面0~15个extent,虽然extent id 17,18,20是完好的。

也就是说使用exp进行导出的话,实际上是丢失了这3个extent部分的数据的。

'###### 第2种方法:基于 rowid 进行数据抢救 ######'

++++++ 表 killdb1 的block分布情况如下:++++++

SQL > col owner FOR a10
SQL > SELECT a.owner,
2         a.segment_name,
3         a.initial_extent,
4         b.file_id,
5         a.extents,
6         b.extent_id,
7         b.blocks,
8         a.HEADER_FILE,
9         a.HEADER_BLOCK
10    FROM dba_segments a, dba_extents b
11   WHERE a.owner = b.owner
12     AND a.segment_name = b.segment_name
13     AND a.owner = 'ROGER'
14     AND b.segment_name = 'KILLDB1'
15   ORDER BY 6;

OWNER      SEGMENT_NAME    INITIAL_EXTENT    FILE_ID    EXTENTS  EXTENT_ID     BLOCKS HEADER_FILE HEADER_BLOCK
---------- --------------- -------------- ---------- ---------- ---------- ---------- ----------- ------------
ROGER      KILLDB1                  65536          5         21          0          8           5           11
ROGER      KILLDB1                  65536          5         21          1          8           5           11
ROGER      KILLDB1                  65536          5         21          2          8           5           11
ROGER      KILLDB1                  65536          5         21          3          8           5           11
ROGER      KILLDB1                  65536          5         21          4          8           5           11
ROGER      KILLDB1                  65536          5         21          5          8           5           11
ROGER      KILLDB1                  65536          5         21          6          8           5           11
ROGER      KILLDB1                  65536          5         21          7          8           5           11
ROGER      KILLDB1            Oracleoracleplus.net      65536          5         21          8          8           5           11
ROGER      KILLDB1                  65536          5         21          9          8           5           11
ROGER      KILLDB1                  65536          5         21         10          8           5           11
ROGER      KILLDB1                  65536          5         21         11          8           5           11
ROGER      KILLDB1                  65536          5         21         12          8           5           11
ROGER      KILLDB1                  65536          5         21         13          8           5           11
ROGER      KILLDB1                  65536          5         21         14          8           5           11
ROGER      KILLDB1                  65536          5         21         15          8           5           11
ROGER      KILLDB1                  65536          7         21         16        128           5           11
ROGER      KILLDB1                  65536          6         21         17        128           5           11
ROGER      KILLDB1                  65536          5         21         18        128           5           11
ROGER      KILLDB1                  65536          7         21         19        128           5           11
ROGER      KILLDB1                  65536          6         21         20        128           5           11

21 ROWS selected.

SELECT  139+128    FROM dual; = 267
SELECT  139+2*128  FROM dual; = 395
SELECT  139+3*128  FROM dual; = 523
SELECT  139+4*128  FROM dual; = 651
SELECT  139+5*128  FROM dual; = 779

SQL > SELECT object_id
  2    FROM dba_objects
  3   WHERE object_name='KILLDB1' AND owner='ROGER';

OBJECT_ID
----------
52059

SQL > SELECT 139+128 FROM dual;

139+128
----------
267

SQL > SELECT 139+3*128 FROM dual;

139+3*128
----------
523

SQL > SELECT 139+4*128 FROM dual;

139+4*128
----------
651

SQL > SELECT 139+5*128 FROM dual;

139+5*128
----------
779

SQL > SET LINES 200
SQL > SELECT dbms_rowid.rowid_create(1,52059,5,139,0) FROM dual;

DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAACLAAA

SQL > SELECT dbms_rowid.rowid_create(1,52059,5,267,0) FROM dual;

DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAELAAA

SQL > SELECT dbms_rowid.rowid_create(1,52059,5,395,0) FROM dual;

DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAGLAAA

SQL > SELECT dbms_rowid.rowid_create(1,52059,5,523,0) FROM dual;

DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAILAAA

SQL > SELECT dbms_rowid.rowid_create(1,52059,5,651,0) FROM dual;

DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAKLAAA

SQL > SELECT dbms_rowid.rowid_create(1,52059,5,779,0) FROM dual;

DBMS_ROWID.ROWID_C
------------------
AAAMtbAAFAAAAMLAAA

SQL > CREATE TABLE t1
  2    AS SELECT * FROM roger.killdb1 WHERE rowid < 'AAAMtbAAFAAAACLAAA';

TABLE created.

SQL > SELECT COUNT(*) FROM t1;

COUNT(*)
----------
9500      ====== 这个就是我们使用EXP所能抽取的9500条数据 ======

SQL > INSERT INTO t1
2    SELECT *
3      FROM (SELECT *
4              FROM roger.killdb1
5             WHERE rowid < 'AAAMtbAAFAAAAELAAA'
6            UNION ALL
7            SELECT *
8              FROM roger.killdb1
9             WHERE rowid < 'AAAMtbAAFAAAAGLAAA'
10               AND rowid >='AAAMtbAAFAAAAELAAA'
11            UNION ALL
12            SELECT *
13              FROM roger.killdb1
14             WHERE rowid < 'AAAMtbAAFAAAAKLAAA'
15               AND rowid >= 'AAAMtbAAFAAAAGLAAA'
16            UNION ALL
17            SELECT *
18              FROM roger.killdb1
19             WHERE rowid < 'AAAMtbAAFAAAAMLAAA'
20               AND rowid >= 'AAAMtbAAFAAAAKLAAA');

18347 ROWS created.

SQL > commit;

Commit complete.

SQL > SELECT COUNT(*) FROM t1;

COUNT(*)
----------
27847

++++++ 我们可以看到,这样可以抢救出27847条数据,远大于9500条。++++++

'###### 第三种方式:使用ODU进行数据抽取 ######'

[ora10g@killdb DATA]$ ls -ltr

total 1036
-rw-r--r--  1 ora10g oinstall 1036288 Nov  3 01:07 ROGER_KILLDB1.txt
-rw-r--r--  1 ora10g oinstall     416 Nov  3 01:07 ROGER_KILLDB1.sql
-rw-r--r--  1 ora10g oinstall     618 Nov  3 01:07 ROGER_KILLDB1.ctl

SQL > @ /home/ora10g/odu/odu/DATA/ROGER_KILLDB1.SQL

TABLE created.

SQL > !
[ora10g@killdb ~]$ cd /home/ora10g/odu/odu/DATA
[ora10g@killdb DATA]$ sqlldr system/oracle control=ROGER_KILLDB1.ctl

SQL*Loader: Release 10.2.0.5.0 - Production ON Thu Nov 3 00:50:33 2011

Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.

Commit point reached - logical record COUNT 6492
Commit point reached - logical record COUNT 9478
Commit point reached - logical record COUNT 9479

[ora10g@killdb DATA]$ exit
exit

SQL > SELECT COUNT(*) FROM system.killdb1;

COUNT(*)
----------
9479
可以发现,对于低版本的ODU,似乎还存在一些问题,高版本的ODU应该是ok的,我这里没测试。

目前我暂时所能想到的就这几种方式,如果谁还有更好的方法,请告诉我,谢谢!

总的来说,我个人认为第2种方式比较好,当然使用新版的ODU也比较省事,直接scan一下,然后unload,imp就完了。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle非归档模式恢复删除数据的3种方法详细步骤方法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1335.html

Oracle研究中心

关键词:

使用Oracle ODU恢复删除的数据文件

使用EXP恢复删除的数据库

使用rowid恢复删除的数据库

如何最大程度抢救数据 数据文件丢失-非归档