### 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#	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. 業務資料恢復測試
```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]$ 
[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>

```

Oracle 業務資料unload恢復過程

相關文章