oracle 11g expdp 備份解決空表無法備份

之前做oracle 備份用的都是exp，但exp在11g上存在一個問題，就是無法導(dǎo)出空表。

最近做oracle 數(shù)據(jù)遷移，需要將空表一同導(dǎo)出，經(jīng)過搜索，找到了expdb 于是有了此文。

此文僅作記錄其中問題，以及我個人對expdp 的理解

在使用expdp impdp之前，需要先建立目錄對象，并賦予用戶權(quán)限。這是因為expdp impdp只能通過DIRECTORY對象關(guān)系，將數(shù)據(jù)存入系統(tǒng)目錄。

注:紅色部分要替換成實際值

expdp 導(dǎo)出

1. 創(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 導(dǎo)出

        >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              目標(biāo)轉(zhuǎn)儲文件 (expdat.dmp) 的列表

關(guān)鍵字               說明 (默認(rè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              目標(biāo)轉(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í)行導(dǎo)出的情況下計算作業(yè)估計值。
EXCLUDE               排除特定的對象類型, 例如 EXCLUDE=TABLE:EMP。
FILESIZE              以字節(jié)為單位指定每個轉(zhuǎn)儲文件的大小。
FLASHBACK_SCN         用于將會話快照設(shè)置回以前狀態(tài)的 SCN。
FLASHBACK_TIME        用于獲取最接近指定時間的 SCN 的時間。
FULL                  導(dǎo)出整個數(shù)據(jù)庫 (N)。
HELP                  顯示幫助消息 (N)。
INCLUDE               包括特定的對象類型, 例如 INCLUDE=TABLE_DATA。
JOB_NAME              要創(chuàng)建的導(dǎo)出作業(yè)的名稱。
LOGFILE               日志文件名 (export.log)。
NETWORK_LINK          鏈接到源系統(tǒng)的遠(yuǎn)程數(shù)據(jù)庫的名稱。
NOLOGFILE             不寫入日志文件 (N)。
PARALLEL              更改當(dāng)前作業(yè)的活動 worker 的數(shù)目。
PARFILE               指定參數(shù)文件。
QUERY                 用于導(dǎo)出表的子集的謂詞子句。
SAMPLE                要導(dǎo)出的數(shù)據(jù)的百分比;
SCHEMAS               要導(dǎo)出的方案的列表 (登錄方案)。
STATUS                在默認(rèn)值 (0) 將顯示可用時的新狀態(tài)的情況下,
                      要監(jiān)視的頻率 (以秒計) 作業(yè)狀態(tài)。
TABLES                標(biāo)識要導(dǎo)出的表的列表 - 只有一個方案。
TABLESPACES           標(biāo)識要導(dǎo)出的表空間的列表。
TRANSPORT_FULL_CHECK  驗證所有表的存儲段 (N)。
TRANSPORT_TABLESPACES 要從中卸載元數(shù)據(jù)的表空間的列表。
VERSION               要導(dǎo)出的對象的版本, 其中有效關(guān)鍵字為:
                      (COMPATIBLE), LATEST 或任何有效的數(shù)據(jù)庫版本。

impdp 導(dǎo)入

將expdb 導(dǎo)出的備份文件上傳到新庫主機(jī)

    1.將數(shù)據(jù)導(dǎo)入新庫之前，需要新庫上創(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.導(dǎo)入

        >impdp user_name/password directory=directory_name dumpfile=backup_name.dmp schemas=user_name

EXPDP用法舉例：

1)按用戶導(dǎo)
    expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)并行進(jìn)程parallel
    expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名導(dǎo)
    expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查詢條件導(dǎo)
    expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
5)按表空間導(dǎo)
    expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)導(dǎo)整個數(shù)據(jù)庫
    expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

IMPDP用法舉例：
   
1)導(dǎo)到指定用戶下
    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)導(dǎo)入表空間
    impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;
4)導(dǎo)入數(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ù)導(dǎo)入到現(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)鍵字               說明 (默認(rèn))
------------------------------------------------------------------------------
ATTACH                連接到現(xiàn)有作業(yè), 例如 ATTACH [=作業(yè)名]。
CONTENT               指定要加載的數(shù)據(jù), 其中有效關(guān)鍵字為:
                      (ALL), DATA_ONLY 和 METADATA_ONLY。
DIRECTORY             供轉(zhuǎn)儲文件, 日志文件和 sql 文件使用的目錄對象。
DUMPFILE              要從 (expdat.dmp) 中導(dǎo)入的轉(zhuǎn)儲文件的列表,
                      例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
ENCRYPTION_PASSWORD   用于訪問加密列數(shù)據(jù)的口令關(guān)鍵字。
                      此參數(shù)對網(wǎng)絡(luò)導(dǎo)入作業(yè)無效。
ESTIMATE              計算作業(yè)估計值, 其中有效關(guān)鍵字為:
                      (BLOCKS) 和 STATISTICS。
EXCLUDE               排除特定的對象類型, 例如 EXCLUDE=TABLE:EMP。
FLASHBACK_SCN         用于將會話快照設(shè)置回以前狀態(tài)的 SCN。
FLASHBACK_TIME        用于獲取最接近指定時間的 SCN 的時間。
FULL                  從源導(dǎo)入全部對象 (Y)。
HELP                  顯示幫助消息 (N)。
INCLUDE               包括特定的對象類型, 例如 INCLUDE=TABLE_DATA。
JOB_NAME              要創(chuàng)建的導(dǎo)入作業(yè)的名稱。
LOGFILE               日志文件名 (import.log)。
NETWORK_LINK          鏈接到源系統(tǒng)的遠(yuǎn)程數(shù)據(jù)庫的名稱。
NOLOGFILE             不寫入日志文件。
PARALLEL              更改當(dāng)前作業(yè)的活動 worker 的數(shù)目。
PARFILE               指定參數(shù)文件。
QUERY                 用于導(dǎo)入表的子集的謂詞子句。
REMAP_DATAFILE        在所有 DDL 語句中重新定義數(shù)據(jù)文件引用。
REMAP_SCHEMA          將一個方案中的對象加載到另一個方案。
REMAP_TABLESPACE      將表空間對象重新映射到另一個表空間。
REUSE_DATAFILES       如果表空間已存在, 則將其初始化 (N)。
SCHEMAS               要導(dǎo)入的方案的列表。
SKIP_UNUSABLE_INDEXES 跳過設(shè)置為無用索引狀態(tài)的索引。
SQLFILE               將所有的 SQL DDL 寫入指定的文件。
STATUS                在默認(rèn)值 (0) 將顯示可用時的新狀態(tài)的情況下,
                      要監(jiān)視的頻率 (以秒計) 作業(yè)狀態(tài)。
STREAMS_CONFIGURATION 啟用流元數(shù)據(jù)的加載
TABLE_EXISTS_ACTION   導(dǎo)入對象已存在時執(zhí)行的操作。
                      有效關(guān)鍵字: (SKIP), APPEND, REPLACE 和 TRUNCATE。
TABLES                標(biāo)識要導(dǎo)入的表的列表。
TABLESPACES           標(biāo)識要導(dǎo)入的表空間的列表。
TRANSFORM             要應(yīng)用于適用對象的元數(shù)據(jù)轉(zhuǎn)換。
                      有效的轉(zhuǎn)換關(guān)鍵字: SEGMENT_ATTRIBUTES, STORAGE
                      OID 和 PCTSPACE。
TRANSPORT_DATAFILES   按可傳輸模式導(dǎo)入的數(shù)據(jù)文件的列表。
TRANSPORT_FULL_CHECK  驗證所有表的存儲段 (N)。
TRANSPORT_TABLESPACES 要從中加載元數(shù)據(jù)的表空間的列表。
                      僅在 NETWORK_LINK 模式導(dǎo)入操作中有效。
VERSION               要導(dǎo)出的對象的版本, 其中有效關(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值

1. 如果為0，則從 init.ora 中將此值刪除，然后重啟oracle服務(wù)
   

2. 如果為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
當(dāng)遇到無法啟動時，一定要看啟動日志的報錯信息！

/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:

導(dǎo)入數(shù)據(jù)時報類似如下錯誤

Job ""SYSTEM"."SYS_IMPORT_SCHEMA_01"" completed with 116 error(s)

因為表已經(jīng)存在導(dǎo)致，導(dǎo)入過程中由于table_exists_action參數(shù)的默認(rèn)選項是skip，從而跳過存在表的表數(shù)據(jù)的導(dǎo)入

A:

這時我們想導(dǎo)入這些數(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此表后導(dǎo)入文件中包含的數(shù)據(jù)

注意：如果這里選用append選項，那么如果原表有數(shù)據(jù)，且沒有合理的約束條件，則可能導(dǎo)致數(shù)據(jù)的重復(fù)導(dǎo)入，所以，生產(chǎn)環(huán)境實際導(dǎo)入過程中一定要弄清楚數(shù)據(jù)的實際情況才能準(zhǔn)確決定如何選用此參數(shù)的選項

導(dǎo)入用戶下所有的內(nèi)容，可使用TABLE_EXISTS_ACTION=REPLACE

修改用戶密碼

ALTER USER user_name IDENTIFIED BY new_password;

刪除用戶

DROP USER user_name cascade