### 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 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. 业务数据恢复测试 ```sql [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> ```