1. Oracle 业务数据unload介绍
Oracle Database在某些极端情况，造成数据库不能打开或数据删除后没有备份，面临业务数据丢失的风险，
此时可考虑使用业务数据unload方式,最大限度地恢复业务数据,操作步骤如下所示，
2. 业务数据创建
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
[oracle@sourcedb xdul]$ ./xdul
xdul: Data Unload for Oracle version 1.1.1
Copyright(c) 2020 orastar.All rights reserved.
Wechat: xidoublestr
Email: 634025070@qq.com
loading default config.......
load config file 'config.txt' successful
loading default control file ......
ts# fn rfn bsize blocks filename
---- ---- ---- ----- -------- --------------------------------------------
0 1 1 8192 97280 /oradata/epmsn/system01.dbf
1 2 2 8192 87040 /oradata/epmsn/sysaux01.dbf
2 3 3 8192 8960 /oradata/epmsn/undotbs01.dbf
4 4 4 8192 640 /oradata/epmsn/users01.dbf
6 5 5 8192 12800 /oradata/epmsn/test1.dbf
load control file 'control.txt' successful
XDUL>list user;
userid username
------ ----------
0 SYS
1 PUBLIC
2 CONNECT
3 RESOURCE
4 DBA
5 SYSTEM
6 SELECT_CATALOG_ROLE
7 EXECUTE_CATALOG_ROLE
8 DELETE_CATALOG_ROLE
9 OUTLN
10 EXP_FULL_DATABASE
11 IMP_FULL_DATABASE
12 LOGSTDBY_ADMINISTRATOR
13 DBFS_ROLE
14 DIP
15 AQ_ADMINISTRATOR_ROLE
16 AQ_USER_ROLE
17 DATAPUMP_EXP_FULL_DATABASE
18 DATAPUMP_IMP_FULL_DATABASE
19 ADM_PARALLEL_EXECUTE_TASK
20 GATHER_SYSTEM_STATISTICS
21 ORACLE_OCM
22 RECOVERY_CATALOG_OWNER
23 SCHEDULER_ADMIN
24 HS_ADMIN_SELECT_ROLE
25 HS_ADMIN_EXECUTE_ROLE
26 HS_ADMIN_ROLE
27 GLOBAL_AQ_USER_ROLE
28 OEM_ADVISOR
29 OEM_MONITOR
30 DBSNMP
31 APPQOSSYS
32 WMSYS
33 WM_ADMIN_ROLE
34 JAVAUSERPRIV
35 JAVAIDPRIV
36 JAVASYSPRIV
37 JAVADEBUGPRIV
38 EJBCLIENT
39 JMXSERVER
40 JAVA_ADMIN
41 JAVA_DEPLOY
42 EXFSYS
43 CTXSYS
44 CTXAPP
45 XDB
46 ANONYMOUS
47 XDBADMIN
48 XDB_SET_INVOKER
49 AUTHENTICATEDUSER
50 XDB_WEBSERVICES
51 XDB_WEBSERVICES_WITH_PUBLIC
52 XDB_WEBSERVICES_OVER_HTTP
53 ORDSYS
54 ORDDATA
55 ORDPLUGINS
56 SI_INFORMTN_SCHEMA
2147483638 XS$NULL
57 MDSYS
58 ORDADMIN
59 OLAP_XS_ADMIN
60 OLAPSYS
61 OLAP_DBA
62 CWM_USER
63 OLAP_USER
64 MDDATA
65 SPATIAL_WFS_ADMIN
66 SPATIAL_WFS_ADMIN_USR
67 WFS_USR_ROLE
68 SPATIAL_CSW_ADMIN
69 SPATIAL_CSW_ADMIN_USR
70 CSW_USR_ROLE
71 SYSMAN
72 MGMT_USER
73 MGMT_VIEW
74 FLOWS_FILES
75 APEX_PUBLIC_USER
76 APEX_ADMINISTRATOR_ROLE
77 APEX_030200
78 OWBSYS
79 OWBSYS_AUDIT
80 OWB$CLIENT
81 OWB_DESIGNCENTER_VIEW
82 OWB_USER
83 SCOTT
84 HSQL
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. 业务数据恢复测试
[oracle@sourcedb dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 2 03:20:17 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 10
[oracle@sourcedb dump]$
[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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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>
文章转载自数据库技术笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
