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