oracle的unload,Oracle 业务数据unload恢复过程

最新推荐文章于 2021-04-09 03:44:01 发布

芬格尔mkq

最新推荐文章于 2021-04-09 03:44:01 发布

阅读量1.1k

点赞数

文章标签： oracle的unload

本文详细介绍了如何在Oracle数据库遇到极端情况导致无法打开或数据丢失时，通过业务数据卸载来恢复数据。首先创建了一个名为HSQL.test1的表并插入数据，然后使用Oracle的xdul工具进行数据卸载，生成了unload文件。接着模拟数据丢失，通过卸载文件和sqlloader恢复了数据，成功恢复了HSQL.test1表的内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

### 1. Oracle 业务数据unload介绍

```sql

Oracle Database在某些极端情况，造成数据库不能打开或数据删除后没有备份，面临业务数据丢失的风险，

此时可考虑使用业务数据unload方式,最大限度地恢复业务数据,操作步骤如下所示，

```

### 2. 业务数据创建

```sql

create table hsql.test1(t1 char(10),t2 char(10)) tablespace test1;

insert into hsql.test1 values('1','a');

insert into hsql.test1 values('2','aa');

insert into hsql.test1 values('3','aaa');

insert into hsql.test1 values('4','aaaa');

insert into hsql.test1 values('5','aaaaa');

insert into hsql.test1 values('6','aaaaaa');

insert into hsql.test1 values('7','aaaaaaa');

insert into hsql.test1 values('8','aaaaaaaa');

insert into hsql.test1 values('9','aaaaaaaaa');

insert into hsql.test1 values('10','aaaaaaaaaa');

commit;

select * from hsql.test1;

execute dbms_stats.gather_table_stats(ownname => 'HSQL',tabname => 'TEST1');

SQL> select * from hsql.test1;

T1 T2

---------- ----------

1 a

2 aa

3 aaa

4 aaaa

5 aaaaa

6 aaaaaa

7 aaaaaaa

8 aaaaaaaa

9 aaaaaaaaa

10 aaaaaaaaaa

10 rows selected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

```

### 3. 业务数据unload

```sql

[oracle@sourcedb xdul]$ ./xdul

xdul: Data Unload for Oracle version 1.1.1

Wechat: xidoublestr

Email: 634025070@qq.com

loading default config.......

load config file 'config.txt' successful

loading default control file ......

ts#fnrfnbsizeblocksfilename

---- ---- ---- ----- -------- --------------------------------------------

011819297280/oradata/epmsn/system01.dbf

122819287040/oradata/epmsn/sysaux01.dbf

23381928960/oradata/epmsn/undotbs01.dbf

4448192640/oradata/epmsn/users01.dbf

655819212800/oradata/epmsn/test1.dbf

load control file 'control.txt' successful

XDUL>list user;

useridusername

----------------

0SYS

1PUBLIC

2CONNECT

3RESOURCE

4DBA

5SYSTEM

6SELECT_CATALOG_ROLE

7EXECUTE_CATALOG_ROLE

8DELETE_CATALOG_ROLE

9OUTLN

10EXP_FULL_DATABASE

11IMP_FULL_DATABASE

12LOGSTDBY_ADMINISTRATOR

13DBFS_ROLE

14DIP

15AQ_ADMINISTRATOR_ROLE

16AQ_USER_ROLE

17DATAPUMP_EXP_FULL_DATABASE

18DATAPUMP_IMP_FULL_DATABASE

19ADM_PARALLEL_EXECUTE_TASK

20GATHER_SYSTEM_STATISTICS

21ORACLE_OCM

22RECOVERY_CATALOG_OWNER

23SCHEDULER_ADMIN

24HS_ADMIN_SELECT_ROLE

25HS_ADMIN_EXECUTE_ROLE

26HS_ADMIN_ROLE

27GLOBAL_AQ_USER_ROLE

28OEM_ADVISOR

29OEM_MONITOR

30DBSNMP

31APPQOSSYS

32WMSYS

33WM_ADMIN_ROLE

34JAVAUSERPRIV

35JAVAIDPRIV

36JAVASYSPRIV

37JAVADEBUGPRIV

38EJBCLIENT

39JMXSERVER

40JAVA_ADMIN

41JAVA_DEPLOY

42EXFSYS

43CTXSYS

44CTXAPP

45XDB

46ANONYMOUS

47XDBADMIN

48XDB_SET_INVOKER

49AUTHENTICATEDUSER

50XDB_WEBSERVICES

51XDB_WEBSERVICES_WITH_PUBLIC

52XDB_WEBSERVICES_OVER_HTTP

53ORDSYS

54ORDDATA

55ORDPLUGINS

56SI_INFORMTN_SCHEMA

2147483638XS$NULL

57MDSYS

58ORDADMIN

59OLAP_XS_ADMIN

60OLAPSYS

61OLAP_DBA

62CWM_USER

63OLAP_USER

64MDDATA

65SPATIAL_WFS_ADMIN

66SPATIAL_WFS_ADMIN_USR

67WFS_USR_ROLE

68SPATIAL_CSW_ADMIN

69SPATIAL_CSW_ADMIN_USR

70CSW_USR_ROLE

71SYSMAN

72MGMT_USER

73MGMT_VIEW

74FLOWS_FILES

75APEX_PUBLIC_USER

76APEX_ADMINISTRATOR_ROLE

77APEX_030200

78OWBSYS

79OWBSYS_AUDIT

80OWB$CLIENT

81OWB_DESIGNCENTER_VIEW

82OWB_USER

83SCOTT

84HSQL

85_NEXT_USER

XDUL>set user;

input username: HSQL

cur_user: HSQL,cur_userid:84

XDUL>XDUL>

XDUL>list table;

OBJ#,DATAOBJ#,OWNER#,NAME,SUBNAME,ROWCNT,BLKCNT

------------------------------------------------------------

88130,88130,84,TEST1,,0,0

XDUL>unload table;

input OBJ#: 88130

Unloading table: HSQL.TEST1,object ID: 88130

Unloading segment,storage(Obj#=88130 DataObj#=88130 TS#=6 File#=5 Block#=130 Cluster=0)

10 rows unloaded

XDUL>exit

[oracle@sourcedb dump]$ ls -ltr

total 12

-rw-r--r-- 1 oracle oinstall 220 Apr 2 03:17 HSQL_TEST1.txt

-rw-r--r-- 1 oracle oinstall 64 Apr 2 03:17 HSQL_TEST1.sql

-rw-r--r-- 1 oracle oinstall 222 Apr 2 03:17 HSQL_TEST1.ctl

[oracle@sourcedb dump]$

```

### 4. 业务数据恢复测试

```sql

[oracle@sourcedb dump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 2 03:20:17 2020

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1202556928 bytes

Fixed Size 2252704 bytes

Variable Size 385876064 bytes

Database Buffers 805306368 bytes

Redo Buffers 9121792 bytes

Database mounted.

Database opened.

SQL> drop table hsql.test1;

Table dropped.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@sourcedb dump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 2 03:20:44 2020

Connected to:

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> select * from hsql.test1;

select * from hsql.test1

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> @HSQL_TEST1.sql

Table created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@sourcedb dump]$ ls

HSQL_TEST1.ctl HSQL_TEST1.sql HSQL_TEST1.txt

[oracle@sourcedb dump]$ sqlldr hsql/hsql control=HSQL_TEST1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Apr 2 03:21:29 2020

Commit point reached - logical record count 10

[oracle@sourcedb dump]$

[oracle@sourcedb dump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 2 03:21:34 2020

Connected to:

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> select * from hsql.test1;

T1 T2

---------- ----------

1 a

2 aa

3 aaa

4 aaaa

5 aaaaa

6 aaaaaa

7 aaaaaaa

8 aaaaaaaa

9 aaaaaaaaa

10 aaaaaaaaaa

10 rows selected.

SQL>

```