Oracle11G的資料庫資料導入導出（由11g上導出導入10g資料庫等）

常用指令

EXPDP USERID=’scott/[email protected] as sysdba’ schemas=scott directory=DATA_PUMP_DIR dumpfile=dp.dmp logfile=dp.log version=10.2.0.1.0

create tablespace houqin_tablespace datafile ‘D:\dzj\orcl\TEST01111.DBF’ size 100M autoextend on next 5M maxsize unlimited extent management local uniform size 1M;

create temporary tablespace test11_temp tempfile ‘D:\dzj\orcl\TEST01111_temp.dbf’ size 100m autoextend on next 3m maxsize 2048m extent management local;

create user HOUS identified by HOUS default tablespace test11 temporary tablespace test11_temp;

IMPDP USERID=’scott11/[email protected] as sysdba’ schemas=scott directory=DATA_PUMP_DIR dumpfile=dp.dmp logfile=lg.log version=10.2.0.1.0

IMPDP USERID=’HOUQIN/[email protected] as sysdba’ schemas=HOUQIN directory=DATA_PUMP_DIR dumpfile=dp.dmp logfile=lg.log version=10.2.0.1.0

–檢視表的數量

SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER =’ES2012’;

IMPDP USERID=’ES2012/[email protected] as sysdba’ schemas=ES2012 directory=DATA_PUMP_DIR dumpfile=HAIYANG10G.dmp logfile=dp.log version=10.2.0.1.0

由于11g上導出導入10g資料庫存相容問題，是以要使用expdp和impdp指令：

1. 在11g上使用EXPDP 導出到directory DATA_PUMP_DIR 指定版本号是10g

select * from dba_directories ; 檢視 DATA_PUMP_DIR 本地所在位置

例如：CMD内執行： EXPDP USERID=’scott/[email protected] as sysdba’ schemas=scott directory=DATA_PUMP_DIR dumpfile=dp.dmp logfile=dp.log version=10.2.0.1.0

1. 從 DATA_PUMP_DIR 目錄 拷貝 dmp 檔案 到11g 資料庫 DATA_PUMP_DIR 目錄下
2. 建立一個 與導出時 相同的使用者，使用者名密碼相同 賦予DBA權限

例如：CMD内執行：

IMPDP USERID=’scott/[email protected] as sysdba’ schemas=scott directory=DATA_PUMP_DIR dumpfile=dp.dmp logfile=lg.log version=10.2.0.1.0

如果報錯 表空間* 不存在

需要建立表空間 表空間位置 搜尋 .DBF 可以放到一起

PL/SQL執行：

create tablespace houqin_tablespace datafile ‘D:\dzj\orcl\TEST01111.DBF’ size 100M autoextend on next 5M maxsize unlimited extent management local uniform size 1M;

1. 再次CMD執行導入 ，導入結束 提示 導入成功，無錯誤無警告 才算真正完成導入

參考1：

Oracle11g資料庫導入Oracle10g資料庫操作筆記

11g備份，導入10g的時候會抛錯，直接阻止導入。

但是有時候還必須得把11g的資料庫導入到10g，我今天就遇到了這種情況。

一開始隻是把11g中的表全部備份，成功導入10g。但是缺少視圖、存儲過程等。一個個sql建立累死人，方法太笨。請教了一下資深DBA，重新備份，重新導入，成功。

現在把我導入操作的全過程記錄下來，希望可以友善更多的人!

一、在11g伺服器上，使用expdp指令備份資料

EXPDP USERID=’SYS/[email protected] as sysdba’ schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0

其中，紅色文字部分是根據需要改寫的地方。例如我的sys密碼是cuc2009，資料庫sid是cuc，要到出的使用者名是sybj，要導入到10.2.0.1.0版本的Oracle資料庫中去。aa.dmp和aa.log将會在11g的dpdump目錄中生成，例如我的11g裝在了E盤下面，于是aa.dmp将會在E:\app\Administrator\admin\cuc\dpdump目錄下被生成。

二、在10g伺服器上，使用impdp指令恢複資料

準備工作：1.建庫2.建表空間3.建使用者并授權4.将aa.dmp拷貝到10g的dpdump目錄下

IMPDP USERID=‘SYS/[email protected] as sysdba’ schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log version=10.2.0.1.0

其中紅色部分是根據需要改寫的地方。例如我的sys密碼是cuc2009，資料庫sid是cucf，要導入使用者名為sybj，要導入到10.2.0.1.0版本的Oracle資料庫中去。aa.log将會在10g的dpdump目錄中生成。

參考2：

expdp和impdp

使用expdp

1。data pump包括下面幾個部件：

The command-line clients, expdp and impdp

The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)

The DBMS_METADATA PL/SQL package (also known as the Metadata API)

2。expdp, impdp和原先的exp,imp不相容，也就是用exp導出的檔案用impdp是無法導入的。

3。data pump不支援XML schemas

4。使用前必須要先建立目錄：

SQL> CREATE OR REPLACE DIRECTORY dpump_dir as ‘/oracle/oradata/ORCL10/pumpdata’;

查詢目錄：dba_directories

Oracle資料泵為了提高性能，采用直接路徑的方式，通過資料庫建立的DIRECTORY将資料導入。

這造成了資料泵和IMP一個主要的差別。如果在用戶端進行IMP導入，dmp檔案是放在用戶端的。但是如果通過資料泵的方式導入，資料泵檔案總是放在資料庫伺服器端。

5。給導入導出的使用者賦予目錄的讀寫權限：

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO scott;

6。datapump是伺服器端的JOB，是以可以在執行datapump以後，通過EXIT_CLIENT退出用戶端。通過DBA_DATAPUMP_JOBS視圖可以檢查datapump作業的情況，也可以利用ATTACH重新連接配接上還在進行的JOB。每個datapump可以通過JOB_NAME參數指定名稱，如果不指定，那麼會有預設的名稱，比如上貼中的例子，名稱就是SYS_EXPORT_TABLE_01，通過V$SESSION_LONGOPS也可以檢視長時間運作的datapump job的具體内容。

7.以下參數影響data pump的性能

disk_asynch_io = true

db_block_checking = false

db_block_checksum = false

8.以下參數設定越高，來允許最大的并行度

processes

sessions

parallel_max_servers

1. 以下參數應該被設定大點

shared_pool_size

undo_tablespace

實驗以及例子：

==================================================================

建立目錄，dumpdir是自己命名的名稱

SQL> create directory dumpdir as ‘e:datadump’;

删除目錄

SQL> drop directory dumpdir ;

SQL> grant read,write on directory dumpdir to scott;

//導出一個schema:scott

C:>expdp scott/tiger dumpfile=scott.dmp directory=dumpdir schemas=scott

SQL> create user lihui identified by lihui;

SQL> grant create session,resource to lihui;

SQL> grant read,write on directory dumpdir to lihui;

SQL> grant create database link, create synonym, create view to lihui;

SQL> grant imp_full_database to scott;

//導入到lihui這個schema下

C:>impdp scott/tiger directory=dumpdir dumpfile=scott.dmp logfile=scott.log remap_schema=scott:lihui

–參數remap_schema将scott下的内容導入導李輝這個schema下

提高impdp導入的的速度，與并行操作有關:

SQL> show parameter cpu

NAME TYPE VALUE

cpu_count integer 2

parallel_threads_per_cpu integer 2

通過parallel參數導出使用一個以上的線程來顯著的加速作業.每個線程建立一個單獨的轉儲檔案,

是以dumpfile應當擁有和并行度一樣多的項目.可以使用通配符命名檔案名,而不用顯式的輸入各個檔案名,e.g

expdp lihui/lihui tables=(emp,dept) directory=dumpdir dumpfile=test_%U.dmp parallel=2 job_name=test

–%U是通配符,參數parallel并行度

資料庫監控:

監控作業的主要視圖:

dba_datapump_sessions,在執行任務的時候監控前台程序的會話.

dba_datapump_jobs,監控在作業上有多少個工作程序(degree列)在工作.

也可以通過alert日志檔案檢視到程序的資訊.

處理特定對象:

e.g

隻導出存儲過程,而不導出其它對象:

expdp scott/tiger directory=dumpdir dumpfile=pros.dmp include=PROCEDURE

隻導出一些特定的對象(比如函數FUNC1和過程PROC1)

expdp scott/tiger directory=dumpdir dumpfile=pro_fun.dmp include=PROCEDURE:”=’PROC1’”,FUNCTION:’=’FUNC1’”

全庫導出：

expdp system/sys directory=dumpdir dumpfile=full.dmp full=y job_name=expdpfull logfile=full.log//全庫導出

//使用Ctrl+c 可以退出互動模式，但是expdp操作不會停止，因為expdp是資料庫内部定義的任務已經與用戶端無關。退出後可以檢視其導出狀态

Export> status

可以使用stop_job指令真正停止該操作。

expdp導出的時候取的是用戶端的時間，而導入完成取的是伺服器端的時間，應該算是個bug。

問題：

如果發現執行在語句正确，但是導出失敗，報錯誤：

C:>expdp scott/[email protected] dumpfile=scott.dp directory=dumpdir schemas=scott l

ogfile=scott.log

Export: Release 10.2.0.3.0 - Production on 星期四, 18 12月, 2008 14:05:46

Copyright (c) 2003, 2005, Oracle. All rights reserved.

連接配接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

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

ORA-39002: 操作無效

ORA-39070: 無法打開日志檔案。

ORA-29283: 檔案操作無效

ORA-06512: 在”SYS.UTL_FILE”, line 475

ORA-29283: 檔案操作無效

則說明建立的目錄有問題，在這裡dumpdir的目錄在實際實體空間不存在。應該檢視select * from dba_directories;檢視一下dumpdir對應的目錄是否存在。