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

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

最近做oracle 數據遷移，需要將空表一同導出，經過搜索，找到了expdb 于是有了此文。

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

在使用expdp impdp之前，需要先建立目錄對象，并賦予用戶權限。這是因為expdp impdp只能通過DIRECTORY對象關系，將數據存入系統目錄。

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

expdp 導出

1. 創建DIRECTORY對象和OS PATH映射，并賦予權限

   >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為數據庫對象的集合,一個用戶一般對應一個schema,該用戶的schema名等于用戶名，并作為該用戶缺省schema。參考http://blog.csdn.net/kimsoft/article/details/4627520

DIRECTORY             供轉儲文件和日志文件使用的目錄對象。
DUMPFILE              目標轉儲文件 (expdat.dmp) 的列表

關鍵字               說明 (默認)  參考http://blog.csdn.net/engledb/article/details/8979910

------------------------------------------------------------------------------

ATTACH                連接到現有作業, 例如 ATTACH [=作業名]。
COMPRESSION           減小有效的轉儲文件內容的大小
                      關鍵字值為: (METADATA_ONLY) 和 NONE。
CONTENT               指定要卸載的數據, 其中有效關鍵字為:
                      (ALL), DATA_ONLY 和 METADATA_ONLY。
DIRECTORY             供轉儲文件和日志文件使用的目錄對象。
DUMPFILE              目標轉儲文件 (expdat.dmp) 的列表,
                      例如 DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
ENCRYPTION_PASSWORD   用于創建加密列數據的口令關鍵字。
ESTIMATE              計算作業估計值, 其中有效關鍵字為:
                      (BLOCKS) 和 STATISTICS。
ESTIMATE_ONLY         在不執行導出的情況下計算作業估計值。
EXCLUDE               排除特定的對象類型, 例如 EXCLUDE=TABLE:EMP。
FILESIZE              以字節為單位指定每個轉儲文件的大小。
FLASHBACK_SCN         用于將會話快照設置回以前狀態的 SCN。
FLASHBACK_TIME        用于獲取最接近指定時間的 SCN 的時間。
FULL                  導出整個數據庫 (N)。
HELP                  顯示幫助消息 (N)。
INCLUDE               包括特定的對象類型, 例如 INCLUDE=TABLE_DATA。
JOB_NAME              要創建的導出作業的名稱。
LOGFILE               日志文件名 (export.log)。
NETWORK_LINK          鏈接到源系統的遠程數據庫的名稱。
NOLOGFILE             不寫入日志文件 (N)。
PARALLEL              更改當前作業的活動 worker 的數目。
PARFILE               指定參數文件。
QUERY                 用于導出表的子集的謂詞子句。
SAMPLE                要導出的數據的百分比;
SCHEMAS               要導出的方案的列表 (登錄方案)。
STATUS                在默認值 (0) 將顯示可用時的新狀態的情況下,
                      要監視的頻率 (以秒計) 作業狀態。
TABLES                標識要導出的表的列表 - 只有一個方案。
TABLESPACES           標識要導出的表空間的列表。
TRANSPORT_FULL_CHECK  驗證所有表的存儲段 (N)。
TRANSPORT_TABLESPACES 要從中卸載元數據的表空間的列表。
VERSION               要導出的對象的版本, 其中有效關鍵字為:
                      (COMPATIBLE), LATEST 或任何有效的數據庫版本。

impdp 導入

將expdb 導出的備份文件上傳到新庫主機

    1.將數據導入新庫之前，需要新庫上創建用戶并賦予相關權限

    創建用戶user_name

        $sqlplus sys/ as sysdba;

        >CREATE USER user_name IDENTIFIED BY password;

    2.賦予user_name用戶登錄和創建表權限    

        >GRANT create table,create session TO user_name;

    3.分配USERS表配額

    sys用戶權限：

        >GRANT UNLIMITED TABLESPACE TO user_name;

    4.創建DIRECTORY對象和OS PATH映射，并賦予權限

        >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)導整個數據庫
    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)導入數據庫
    impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5)追加數據
    impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system

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

2. 如果為1，則為STREAMS_POOL_SIZE 分配50-100mb 內存
   

查看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:

用戶沒又創建表的權限，給用戶creaate table的權限即可

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

導入數據時報類似如下錯誤

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

因為表已經存在導致，導入過程中由于table_exists_action參數的默認選項是skip，從而跳過存在表的表數據的導入

A:

這時我們想導入這些數據，可以加參數 table_exists_action，指定想要的選項。
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

對應 添加、替換、[跳過]和截斷。

這里選擇truncate，即如果表存在，那么處理方式是truncate此表后導入文件中包含的數據

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

導入用戶下所有的內容，可使用TABLE_EXISTS_ACTION=REPLACE

修改用戶密碼

ALTER USER user_name IDENTIFIED BY new_password;

刪除用戶

DROP USER user_name cascade