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

標籤：oracle 11g 備份空表

之前做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/[email protected] schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;
2)並行進程parallel
    expdp scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)按表名導
    expdp scott/[email protected] TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)按查詢條件導
    expdp scott/[email protected] 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 TABLE_EXISTS_ACTION

報錯

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:

引用官方

ChangesIn the first situation AQ_TM_PROCESSES=0For the second situation AQ_TM_PROCESSES should not be 0.CauseFor 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 resolvedFor the second situation, there is likely a lack of memory for the streams_pool_size.SolutionFor 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));

修改使用者密碼

ALTER USER user_name IDENTIFIED BY new_password;

刪除使用者

DROP USER user_name cascade

本文出自 “morrowind” 部落格，請務必保留此出處http://morrowind.blog.51cto.com/1181631/1786008

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