PRM-DUL 可以直接从Oracle .DBF数据文件中抽取出表的数据。
Oracle DBA神器：PRM灾难恢复工具，Schema级别数据恢复。PRM For Oracle Database – schema级别oracle数据库数据恢复特性 ，PRM即ParnassusData Recovery Manager是企业级别Oracle数据库灾难恢复工具。PRM可以在无备份的情况下恢复被truncated掉的表，也可以恢复无法打开的Oracle数据库(Alter Database Open失败)中的数据。
不需要特别的操作，exp会导出还能读取的数据，自动跳过无法导出的段区，而且导出的dmp文件也可以正常导入。
测试步骤如下：
SQL>
SQL> drop user test cascade;
User dropped.
SQL> drop tablespace test;
Tablespace dropped.
SQL>
SQL> create tablespace test
2 datafile 'E:\Oracle\oradata\ORCL\test01.dbf' size 5m reuse,
3 'E:\Oracle\oradata\ORCL\test02.dbf' size 5m reuse,
4 'E:\Oracle\oradata\ORCL\test03.dbf' size 5m reuse;
Tablespace created.
SQL> create user test identified by test default tablespace test;
User created.
SQL> grant connect, resource, dba to test;
Grant succeeded.
SQL>
SQL> select file_name,file_id from dba_data_files where tablespace_name = 'TEST';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID
----------
E:\ORACLE\ORADATA\ORCL\TEST01.DBF
7
E:\ORACLE\ORADATA\ORCL\TEST02.DBF
8
E:\ORACLE\ORADATA\ORCL\TEST03.DBF
9
SQL>
SQL> connect test/test
Connected.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create table t3 as select * from dba_objects;
Table created.
SQL> create table t4 as select * from dba_objects;
Table created.
SQL> create table t5 as select * from dba_objects;
Table created.
SQL> create table t6 as select * from dba_objects;
Table created.
SQL>
SQL> col FILE_NAME format a50
SQL> col FILE_ID format 999
SQL> col OWNER format a10
SQL> col SEGMENT_NAME format a15
SQL>
SQL> SELECT OWNER, SEGMENT_NAME, FILE_ID
2 FROM DBA_EXTENTS
3 WHERE OWNER = 'TEST'
4 GROUP BY OWNER, SEGMENT_NAME, FILE_ID
5 ORDER BY OWNER, SEGMENT_NAME, FILE_ID;
OWNER SEGMENT_NAME FILE_ID
---------- --------------- -------
TEST T1 7
TEST T1 8
TEST T2 7
TEST T2 8
TEST T3 7
TEST T3 8
TEST T4 7
TEST T4 8
TEST T5 9
TEST T6 9
10 rows selected.
SQL>
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host del E:\Oracle\oradata\ORCL\test03.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 247465888 bytes
Database Buffers 58720256 bytes
Redo Buffers 6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: 'E:\ORACLE\ORADATA\ORCL\TEST03.DBF'
SQL> alter database create datafile 9 as 'E:\Oracle\oradata\ORCL\test03.dbf';
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: 'E:\ORACLE\ORADATA\ORCL\TEST03.DBF'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> quit
SQL>
数据库打开后做数据导出
E:\>exp test/test file=test.dmp
Export: Release 11.2.0.1.0 - Production on Sat Oct 3 19:16:38 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table T1 13449 rows exported
. . exporting table T2 13450 rows exported
. . exporting table T3 13451 rows exported
. . exporting table T4 13452 rows exported
. . exporting table T5
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 9, block # 131)
ORA-01110: data file 9: 'E:\ORACLE\ORADATA\ORCL\TEST03.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
. . exporting table T6
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 9, block # 387)
ORA-01110: data file 9: 'E:\ORACLE\ORADATA\ORCL\TEST03.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
这里把旧表删掉
E:\>sqlplus test/test
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 3 19:17:10 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> drop table t3;
Table dropped.
SQL> drop table t4;
Table dropped.
SQL> drop table t5;
Table dropped.
SQL> drop table t6;
Table dropped.
SQL> purge recyclebin;
Recyclebin purged.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
测试导入的过程
E:\>imp test/test file=test.dmp fromuser=test touser=test
Import: Release 11.2.0.1.0 - Production on Sat Oct 3 19:17:57 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . importing table "T1" 13449 rows imported
. . importing table "T2" 13450 rows imported
. . importing table "T3" 13451 rows imported
. . importing table "T4" 13452 rows imported
. . importing table "T5" 0 rows imported
. . importing table "T6" 0 rows imported
Import terminated successfully without warnings.
E:\>
这里T5和T6都在datafile9中，如果实际情况数据表部分数据不在datafile9中，也会导出。
内容全部来源于网络收集，如有侵权，请联系网站删除：QQ：24596024