使用data dump 恢復資料庫

1）先對資料庫進行邏輯匯出整個庫

Export: Release 11.1.0.6.0 - Production on 星期日, 23 5月, 2010 21:41:45
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=dump dumpfile=database.dmp full=y
正在使用 BLOCKS 方法進行估計...
處理物件型別 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 131.0 MB
處理物件型別 DATABASE_EXPORT/TABLESPACE
處理物件型別 DATABASE_EXPORT/PROFILE
處理物件型別 DATABASE_EXPORT/SYS_USER/USER
處理物件型別 DATABASE_EXPORT/SCHEMA/USER
處理物件型別 DATABASE_EXPORT/ROLE
處理物件型別 DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
處理物件型別 DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
處理物件型別 DATABASE_EXPORT/SCHEMA/ROLE_GRANT
處理物件型別 DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
處理物件型別 DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
處理物件型別 DATABASE_EXPORT/RESOURCE_COST
處理物件型別 DATABASE_EXPORT/TRUSTED_DB_LINK
處理物件型別 DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
處理物件型別 DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
處理物件型別 DATABASE_EXPORT/DIRECTORY/DIRECTORY
處理物件型別 DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
處理物件型別 DATABASE_EXPORT/DIRECTORY/GRANT/CROSS_SCHEMA/OBJECT_GRANT
省略了一些。。。
處理物件型別 DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
處理物件型別 DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
處理物件型別 DATABASE_EXPORT/SCHEMA/VIEW/VIEW
處理物件型別 DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
處理物件型別 DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
處理物件型別 DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
處理物件型別 DATABASE_EXPORT/SCHEMA/EVENT/TRIGGER
處理物件型別 DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
處理物件型別 DATABASE_EXPORT/SCHEMA/JOB
處理物件型別 DATABASE_EXPORT/SCHEMA/DIMENSION
處理物件型別 DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
處理物件型別 DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
處理物件型別 DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
處理物件型別 DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
處理物件型別 DATABASE_EXPORT/AUDIT
. . 匯出了 "SH"."CUSTOMERS"                            9.853 MB   55500 行
. . 匯出了 "PM"."ONLINE_MEDIA"                         7.854 MB       9 行
. . 匯出了 "FLOWS_030000"."WWV_FLOW_PAGE_PLUGS"        4.606 MB    6938 行
. . 匯出了 "SYSMAN"."MGMT_MESSAGES"                    3.406 MB   19070 行
. . 匯出了 "FLOWS_030000"."WWV_FLOW_DICTIONARY$"       2.909 MB   70601 省略了一些。。。
. . 匯出了 "OLAPSYS"."CWM2$MRALL_DESCRIPTORS"          7.085 KB      41 行
. . 匯出了 "SCOTT"."DEPT"                              5.937 KB       4 行
. . 匯出了 "SCOTT"."EMP"                               8.570 KB      14 行
. . 匯出了 "SCOTT"."SALGRADE"                          5.867 KB       5 行
. . 匯出了 "SCOTT"."T2"                                21.47 KB       4 行
. . 匯出了 "SCOTT"."T3"                                5.445 KB       4 行
. . 匯出了 "SH"."CAL_MONTH_SALES_MV"                   6.312 KB      48 行

. . 匯出
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_FULL_01"
******************************************************************************
SYSTEM.SYS_EXPORT_FULL_01 的轉儲檔案集為:
  F:\DUMP\DATABASE.DMP
作業 "SYSTEM"."SYS_EXPORT_FULL_01" 已於 21:47:37 成功完成
2）模擬誤操作 刪除scott使用者和表空間user

SQL> conn system/yang as sysdba
已連線。
SQL> drop user scott cascade;
使用者已刪除。
SQL> drop tablespace test including contents and datafiles;
表空間已刪除。
SQL> select name from v$datafile;
NAME                                                                           
--------------------------------------------------------------------------------
F:\APP\YANG\ORADATA\ORACL\SYSTEM01.DBF                                         
F:\APP\YANG\ORADATA\ORACL\SYSAUX01.DBF                                         
F:\APP\YANG\ORADATA\ORACL\UNDOTBS01.DBF                                        
F:\APP\YANG\ORADATA\ORACL\USERS01.DBF                                          
F:\APP\YANG\ORADATA\ORACL\EXAMPLE01.DBF                                        
已選擇6行。

3）使用匯出的資料文檔案匯入整個庫

impdp system/yangdirectory=dump dumpfile=database.dmp full=y ignore=y

Import: Release 11.1.0.6.0 - Production on 星期日, 23 5月, 2010 22:12:1
Copyright (c) 2003, 2007, Oracle.  All rights reserv
連線到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功載入/解除安裝了主表 "SYSTEM"."SYS_IMPORT_FULL_01"
啟動 "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump dumpfile=database.dmp full=y
處理物件型別 DATABASE_EXPORT/TABLESPACE
. . 匯入了 "SCOTT"."DEPT"                              5.937 KB       4 行
. . 匯入了 "SCOTT"."EMP"                               8.570 KB      14 行
. . 匯入了 "SCOTT"."SALGRADE"                          5.867 KB       5 行
. . 匯入了 "SCOTT"."T2"                                21.47 KB       4 行
. . 匯入了 "SCOTT"."T3"                                5.445 KB       4 行
. . 匯入了 "SCOTT"."BONUS"                                 0 KB       0 行
省略了一些。。。

4）驗證資料庫的恢復是否成功

SQL> select name from v$datafile;

NAME                                                                           
--------------------------------------------------------------------------------
F:\APP\YANG\ORADATA\ORACL\SYSTEM01.DBF                                         
F:\APP\YANG\ORADATA\ORACL\SYSAUX01.DBF                                         
F:\APP\YANG\ORADATA\ORACL\UNDOTBS01.DBF                                        
F:\APP\YANG\ORADATA\ORACL\USERS01.DBF                                          
F:\APP\YANG\ORADATA\ORACL\EXAMPLE01.DBF                                        
F:\APP\YANG\ORADATA\ORACL\TEST.DBF                                             

已選擇6行。

SQL> conn scott/yang
已連線。

---成功

小結  ：

第一次做邏輯匯入匯出，有很多步驟不太合理 比如說考慮到匯入匯出速度的話，應該加上buffer 引數，包括這次的實驗和以前的實驗都是比較簡單的，沒有考慮到生產環境上遇到的問題。比如說 源端與目標端的資料庫版本，字符集，還有索引，約束，外部表 ，long 欄位，lob等等，以後再做此類實驗的時候，會考慮。。。