之前做oracle 備份用的都是exp，但exp在11g上存在一個問題，就是無法導出空表。
成都創(chuàng)新互聯(lián)公司是一家專注于成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)與策劃設(shè)計,紅花崗網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)10年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:紅花崗等地區(qū)。紅花崗做網(wǎng)站價格咨詢:18982081108
最近做oracle 數(shù)據(jù)遷移，需要將空表一同導出，經(jīng)過搜索，找到了expdb 于是有了此文。
此文僅作記錄其中問題，以及我個人對expdp 的理解
在使用expdp impdp之前，需要先建立目錄對象，并賦予用戶權(quán)限。這是因為expdp impdp只能通過DIRECTORY對象關(guān)系，將數(shù)據(jù)存入系統(tǒng)目錄。
注:紅色部分要替換成實際值
expdp 導出
創(chuàng)建DIRECTORY對象和OS PATH映射，并賦予權(quán)限
>connect /as sysdba;
>CREATE OR REPLACE DIRECTORY directory_name AS 'directory_ospath';
>GRANT read,write ON DIRECTORY directory_name TO user_name;
2.查詢DIRECTORY
>select * from dba_directories;
>select * from all_directories;
3.expdp 導出
>expdp user_name/user_passwd schemas=user_name dumpfile=expdp.dmp directory=directory_name
schema為數(shù)據(jù)庫對象的集合,一個用戶一般對應(yīng)一個schema,該用戶的schema名等于用戶名，并作為該用戶缺省schema。參考http://blog.csdn.net/kimsoft/article/details/4627520
DIRECTORY 供轉(zhuǎn)儲文件和日志文件使用的目錄對象。
DUMPFILE 目標轉(zhuǎn)儲文件 (expdat.dmp) 的列表
關(guān)鍵字 說明 (默認) 參考http://blog.csdn.net/engledb/article/details/8979910
------------------------------------------------------------------------------
ATTACH 連接到現(xiàn)有作業(yè), 例如 ATTACH [=作業(yè)名]。
COMPRESSION 減小有效的轉(zhuǎn)儲文件內(nèi)容的大小
關(guān)鍵字值為: (METADATA_ONLY) 和 NONE。
CONTENT 指定要卸載的數(shù)據(jù), 其中有效關(guān)鍵字為:
(ALL), DATA_ONLY 和 METADATA_ONLY。
DIRECTORY 供轉(zhuǎn)儲文件和日志文件使用的目錄對象。
DUMPFILE 目標轉(zhuǎn)儲文件 (expdat.dmp) 的列表,
例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
ENCRYPTION_PASSWORD 用于創(chuàng)建加密列數(shù)據(jù)的口令關(guān)鍵字。
ESTIMATE 計算作業(yè)估計值, 其中有效關(guān)鍵字為:
(BLOCKS) 和 STATISTICS。
ESTIMATE_ONLY 在不執(zhí)行導出的情況下計算作業(yè)估計值。
EXCLUDE 排除特定的對象類型, 例如 EXCLUDE=TABLE:EMP。
FILESIZE 以字節(jié)為單位指定每個轉(zhuǎn)儲文件的大小。
FLASHBACK_SCN 用于將會話快照設(shè)置回以前狀態(tài)的 SCN。
FLASHBACK_TIME 用于獲取最接近指定時間的 SCN 的時間。
FULL 導出整個數(shù)據(jù)庫 (N)。
HELP 顯示幫助消息 (N)。
INCLUDE 包括特定的對象類型, 例如 INCLUDE=TABLE_DATA。
JOB_NAME 要創(chuàng)建的導出作業(yè)的名稱。
LOGFILE 日志文件名 (export.log)。
NETWORK_LINK 鏈接到源系統(tǒng)的遠程數(shù)據(jù)庫的名稱。
NOLOGFILE 不寫入日志文件 (N)。
PARALLEL 更改當前作業(yè)的活動 worker 的數(shù)目。
PARFILE 指定參數(shù)文件。
QUERY 用于導出表的子集的謂詞子句。
SAMPLE 要導出的數(shù)據(jù)的百分比;
SCHEMAS 要導出的方案的列表 (登錄方案)。
STATUS 在默認值 (0) 將顯示可用時的新狀態(tài)的情況下,
要監(jiān)視的頻率 (以秒計) 作業(yè)狀態(tài)。
TABLES 標識要導出的表的列表 - 只有一個方案。
TABLESPACES 標識要導出的表空間的列表。
TRANSPORT_FULL_CHECK 驗證所有表的存儲段 (N)。
TRANSPORT_TABLESPACES 要從中卸載元數(shù)據(jù)的表空間的列表。
VERSION 要導出的對象的版本, 其中有效關(guān)鍵字為:
(COMPATIBLE), LATEST 或任何有效的數(shù)據(jù)庫版本。
impdp 導入
將expdb 導出的備份文件上傳到新庫主機
1.將數(shù)據(jù)導入新庫之前，需要新庫上創(chuàng)建用戶并賦予相關(guān)權(quán)限
創(chuàng)建用戶user_name
$sqlplus sys/ as sysdba;
>CREATE USER user_name IDENTIFIED BY password;
2.賦予user_name用戶登錄和創(chuàng)建表權(quán)限
>GRANT create table,create session TO user_name;
3.分配USERS表配額
sys用戶權(quán)限：
>GRANT UNLIMITED TABLESPACE TO user_name;
4.創(chuàng)建DIRECTORY對象和OS PATH映射，并賦予權(quán)限
>CREATE OR REPLACE DIRECTORY directory_name AS 'directory_ospath';
>GRANT read,write ON DIRECTORY directory_name TO user_name;
5.導入
>impdp user_name/password directory=directory_name dumpfile=backup_name.dmp schemas=user_name
EXPDP用法舉例：
1)按用戶導
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)并行進程parallel
expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名導
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查詢條件導
expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
5)按表空間導
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)導整個數(shù)據(jù)庫
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
IMPDP用法舉例：
1)導到指定用戶下
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2)改變表的owner
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
3)導入表空間
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
4)導入數(shù)據(jù)庫
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5)追加數(shù)據(jù)
impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system
6)將原scott用戶的數(shù)據(jù)導入到現(xiàn)在的scott2用戶,將原users表空間的對象重定向到users2表空間。
impdp system DIRECTORY=backup SCHEMAS=scott REMAP_SCHEMA=scott:scott2
REMAP_TABLESPACE=users:users2 TABLE_EXISTS_ACTION=replace DUMPFILE=scott_all%U.dmp LOGFILE=impdp_scott_all.log PARALLEL=2
TABLE_EXISTS_ACTION關(guān)鍵字 說明 (默認)
------------------------------------------------------------------------------
ATTACH 連接到現(xiàn)有作業(yè), 例如 ATTACH [=作業(yè)名]。
CONTENT 指定要加載的數(shù)據(jù), 其中有效關(guān)鍵字為:
(ALL), DATA_ONLY 和 METADATA_ONLY。
DIRECTORY 供轉(zhuǎn)儲文件, 日志文件和 sql 文件使用的目錄對象。
DUMPFILE 要從 (expdat.dmp) 中導入的轉(zhuǎn)儲文件的列表,
例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
ENCRYPTION_PASSWORD 用于訪問加密列數(shù)據(jù)的口令關(guān)鍵字。
此參數(shù)對網(wǎng)絡(luò)導入作業(yè)無效。
ESTIMATE 計算作業(yè)估計值, 其中有效關(guān)鍵字為:
(BLOCKS) 和 STATISTICS。
EXCLUDE 排除特定的對象類型, 例如 EXCLUDE=TABLE:EMP。
FLASHBACK_SCN 用于將會話快照設(shè)置回以前狀態(tài)的 SCN。
FLASHBACK_TIME 用于獲取最接近指定時間的 SCN 的時間。
FULL 從源導入全部對象 (Y)。
HELP 顯示幫助消息 (N)。
INCLUDE 包括特定的對象類型, 例如 INCLUDE=TABLE_DATA。
JOB_NAME 要創(chuàng)建的導入作業(yè)的名稱。
LOGFILE 日志文件名 (import.log)。
NETWORK_LINK 鏈接到源系統(tǒng)的遠程數(shù)據(jù)庫的名稱。
NOLOGFILE 不寫入日志文件。
PARALLEL 更改當前作業(yè)的活動 worker 的數(shù)目。
PARFILE 指定參數(shù)文件。
QUERY 用于導入表的子集的謂詞子句。
REMAP_DATAFILE 在所有 DDL 語句中重新定義數(shù)據(jù)文件引用。
REMAP_SCHEMA 將一個方案中的對象加載到另一個方案。
REMAP_TABLESPACE 將表空間對象重新映射到另一個表空間。
REUSE_DATAFILES 如果表空間已存在, 則將其初始化 (N)。
SCHEMAS 要導入的方案的列表。
SKIP_UNUSABLE_INDEXES 跳過設(shè)置為無用索引狀態(tài)的索引。
SQLFILE 將所有的 SQL DDL 寫入指定的文件。
STATUS 在默認值 (0) 將顯示可用時的新狀態(tài)的情況下,
要監(jiān)視的頻率 (以秒計) 作業(yè)狀態(tài)。
STREAMS_CONFIGURATION 啟用流元數(shù)據(jù)的加載
TABLE_EXISTS_ACTION 導入對象已存在時執(zhí)行的操作。
有效關(guān)鍵字: (SKIP), APPEND, REPLACE 和 TRUNCATE。
TABLES 標識要導入的表的列表。
TABLESPACES 標識要導入的表空間的列表。
TRANSFORM 要應(yīng)用于適用對象的元數(shù)據(jù)轉(zhuǎn)換。
有效的轉(zhuǎn)換關(guān)鍵字: SEGMENT_ATTRIBUTES, STORAGE
OID 和 PCTSPACE。
TRANSPORT_DATAFILES 按可傳輸模式導入的數(shù)據(jù)文件的列表。
TRANSPORT_FULL_CHECK 驗證所有表的存儲段 (N)。
TRANSPORT_TABLESPACES 要從中加載元數(shù)據(jù)的表空間的列表。
僅在 NETWORK_LINK 模式導入操作中有效。
VERSION 要導出的對象的版本, 其中有效關(guān)鍵字為:
(COMPATIBLE), LATEST 或任何有效的數(shù)據(jù)庫版本。
僅對 NETWORK_LINK 和 SQLFILE 有效。
報錯
Q:
ORA-31626: job does not exist
ORA-31687: error creating worker process with worker id 1
ORA-31687: error creating worker process with worker id 1
ORA-31688: Worker process failed during startup.
A:
引用官方
Changes In the first situation AQ_TM_PROCESSES=0 For the second situation AQ_TM_PROCESSES should not be 0. Cause For the first situation, AQ_TM_PROCESSES init.ora parameter was set to zero (AQ_TM_PROCESSES=0) Once removed this parameter from the init.ora file, and bounced the database the problem was resolved For the second situation, there is likely a lack of memory for the streams_pool_size. Solution For the first situation: o Remove AQ_TM_PROCESSES init.ora parameter (AQ_TM_PROCESSES=0) from the init.ora. For the second situation: o Allocate between 50-100MB for the STREAMS_POOL_SIZE in order for datapump to function since it is dependent on streams processing.
簡而言之，查詢aq_tm_processes值
如果為0，則從 init.ora 中將此值刪除，然后重啟oracle服務(wù)
如果為1，則為STREAMS_POOL_SIZE 分配50-100mb 內(nèi)存
查看aq_tm_processes值
SQL>show parameter process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
修改STREAMS_POOL_SIZE
SQL>alter system set streams_pool_size=50m scope=spfile;
Q:
SQL> Connected to an idle instance.
SQL> ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
SQL> Disconnected
A:
cp /oracle/app/oracle/admin/orcl/pfile/init.ora.4262015194529 /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora
當遇到無法啟動時，一定要看啟動日志的報錯信息！
/oracle/app/oracle/product/11.2.0/dbhome_1/startup.log
Q:
ORA-31626: job does not exist
ORA-31633: unable to create master table "SERVER.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01031: insufficient privileges
A:
用戶沒又創(chuàng)建表的權(quán)限，給用戶creaate table的權(quán)限即可
>GRANT CREATE TABLE TO user_name;
Q:
ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-4
ORA-01950: no privileges on tablespace 'USERS'
A:
USERS 表空間不足，給用戶給USERS 表空間即可
>GRANT UNLIMITED TABLESPACE TO user_name;
或者:
>alter user youruse quota 100m on users;
user_name用戶：
> CREATE TABLE test(A varchar2(100));
Q:
導入數(shù)據(jù)時報類似如下錯誤
Job ""SYSTEM"."SYS_IMPORT_SCHEMA_01"" completed with 116 error(s)
因為表已經(jīng)存在導致，導入過程中由于table_exists_action參數(shù)的默認選項是skip，從而跳過存在表的表數(shù)據(jù)的導入
A:
這時我們想導入這些數(shù)據(jù)，可以加參數(shù) table_exists_action，指定想要的選項。
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
對應(yīng) 添加、替換、[跳過]和截斷。
這里選擇truncate，即如果表存在，那么處理方式是truncate此表后導入文件中包含的數(shù)據(jù)
注意：如果這里選用append選項，那么如果原表有數(shù)據(jù)，且沒有合理的約束條件，則可能導致數(shù)據(jù)的重復導入，所以，生產(chǎn)環(huán)境實際導入過程中一定要弄清楚數(shù)據(jù)的實際情況才能準確決定如何選用此參數(shù)的選項
導入用戶下所有的內(nèi)容，可使用TABLE_EXISTS_ACTION=REPLACE
修改用戶密碼
ALTER USER user_name IDENTIFIED BY new_password;
刪除用戶
DROP USER user_name cascade
重慶分公司
重慶分公司
