学习目标

懂得如何处理数据库备份时的错误

业务场景

数据库需要备份，碰到错误需要解决

常见错误

逻辑目录不存在ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name DATAPUMP1 is invalid
操作系统目录不存在或权限不对
没有remap tablespace
导出文件损坏
数据库是RAC群集，导出导入时却没加cluster=n参数
11.2.0.1导入创建索引时失败
导出时碰ORA-04031 unable to allocate xx bytes of shared memory
导出时出现VT表导出错误
expdp组件损坏
ORA-39064文件写入错误
ORA-01658 无法为表空间xx创建 initial区
数据库备份时报ORA-09070 文件不存在
备份时连接不上数据库
备份或恢复时日志文件不全
备份到目标库时，报表长度不够
ORA-31693,ORA-02351,ORA-01555 snapshot too old: rollback segment number xx with name "_syssmuxxxxxxx" too small
ORA-31664: unable to construct unique job name when defalted
报ora-31626 作业不存在 ORA-39086无法检索作业信息
如何清除 DBA_DATAPUMP_JOBS 视图中的异常数据泵作业?

1.逻辑目录不存在ORA-39002: invalid operation ORA-39070: Unable to open the log file.

[oracle@mytest ~]$ expdp system/123456 schemas=easuser directory=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp logfile=easuser_exp.log exclude=table:\"like \'VT%\'\",statistics parallel=2 cluster=n

Export: Release 12.2.0.1.0 - Production on Thu Mar 25 01:07:40 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name DATAPUMP1 is invalid

原因：

directory=DATAPUMP1

这个目录名没有创建，

需要先创建这个oracle逻辑名字，可以通过以下语句查询是否存在逻辑目录名和目录绝对路径，这两个都要存在，并且权限要求oracle有读和写权限

set linesize 200

column DIRECTORY_NAME format a25

column DIRECTORY_PATH format a60

SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH

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

XMLDIR /u01/app/oracle/12/db1/rdbms/xml

XSDDIR /u01/app/oracle/12/db1/rdbms/xml/schema

ORA_DBMS_FCP_LOGDIR /u01/app/oracle/12/db1/cfgtoollogs

ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/12/db1/rdbms/admin

OPATCH_INST_DIR /u01/app/oracle/12/db1/OPatch

ORACLE_OCM_CONFIG_DIR /u01/app/oracle/12/db1/ccr/state

DATA_PUMP_DIR /u01/app/oracle/12/admin/mytest/dpdump/

ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/12/db1/ccr/state

OPATCH_SCRIPT_DIR /u01/app/oracle/12/db1/QOpatch

OPATCH_LOG_DIR /u01/app/oracle/12/db1/rdbms/log

ORACLE_BASE /u01/app/oracle/12

不但DIRECTORY_NAME 要有，DIRECTORY_PATH这两个都要存在。

如果没有，要创建

先创建操作系统目录并赋权限：

mkdir -p /u01/dbbackup

chown -R oracle:oinstall /u01/dbbackup/

再创建数据泵目录：

create or replace directory DATAPUMP1 as '/u01/dbbackup/';

2. 操作系统目录不存在或权限不对

[oracle@mytest ~]$ expdp system/123456 schemas=easuser directory=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp logfile=easuser_exp.log exclude=table:\"like \'VT%\'\",statistics parallel=2 cluster=n

Export: Release 12.2.0.1.0 - Production on Thu Mar 25 01:40:11 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-29283: invalid file operation

修改方法如同上面第一点，要创建目录，并确保路径存在，目录拥有权限

mkdir -p /u01/dbbackup

chown -R oracle:oinstall /u01/dbbackup/

再创建数据泵目录：

create or replace directory DATAPUMP1 as '/u01/dbbackup/';

3. ORA-39083 , ORA-00959表空间不存在

没有创建表空间或没有remap tablespace

[oracle@node1 dbbackup]$ impdp easuser_new/123456 DIRECTORY=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp REMAP_SCHEMA=easuser:easuser_new parallel=2 LOGFILE=testusernew_imp.log cluster=n

Import: Release 11.2.0.4.0 - Production on Tue Jul 13 12:23:21 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "EASUSER_NEW"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "EASUSER_NEW"."SYS_IMPORT_FULL_01": easuser_new/******** DIRECTORY=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp REMAP_SCHEMA=easuser:easuser_new parallel=2 LOGFILE=testusernew_imp.log cluster=n
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"EASUSER_NEW" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"EASUSER_NEW"."TB1" failed to create with error:
ORA-00959: tablespace 'EAS_D_TESTUSER_STANDARD' does not exist
Failing sql is:
CREATE TABLE "EASUSER_NEW"."TB1" ("A" NUMBER(*,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EAS_D_TESTUSER_STAND
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "EASUSER_NEW"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Tue Jul 13 12:23:24 2021 elapsed 0 00:00:02

首先要有对应的表空间，把旧的名字映射成新的表空间名

create user easuser_new identified by 123456 default tablespace EAS_D_easusernew_STANDARD temporary tablespace temp;

[oracle@node1 dbbackup]$ impdp easuser_new/123456 DIRECTORY=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp REMAP_SCHEMA=easuser:easuser_new remap_tablespace=EAS_D_TESTUSER_STANDARD:EAS_D_easusernew_STANDARD parallel=2 LOGFILE=testusernew_imp.log cluster=n

Import: Release 11.2.0.4.0 - Production on Tue Jul 13 12:25:14 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "EASUSER_NEW"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "EASUSER_NEW"."SYS_IMPORT_FULL_01": easuser_new/******** DIRECTORY=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp REMAP_SCHEMA=easuser:easuser_new remap_tablespace=EAS_D_TESTUSER_STANDARD:EAS_D_easusernew_STANDARD parallel=2 LOGFILE=testusernew_imp.log cluster=n
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"EASUSER_NEW" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "EASUSER_NEW"."TB1" 5.015 KB 1 rows
Job "EASUSER_NEW"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Jul 13 12:25:17 2021 elapsed 0 00:00:02

4. 导出文件损坏

如果导入时发生类似如下错误，表示导出文件不完整或导出文件被破坏了

ditional information: 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9715

----- PL/SQL Call Stack -----
object      line object
handle    number name
0x30adc80c     21979 package body SYS.KUPW$WORKER
0x30adc80c      9742 package body SYS.KUPW$WORKER
0x30adc80c      4287 package body SYS.KUPW$WORKER
0x30adc80c     10450 package body SYS.KUPW$WORKER
0x30adc80c      1824 package body SYS.KUPW$WORKER
0x2db594d8         2 anonymous block

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_METADATA [SELECT process_order, flags, xml_clob, NVL(dump_fileid, :1), NVL(dump_position, :2), dump_length, dump_allocation, NVL(value_n, 0), grantor, object_row, object_schema, object_long_name, partition_name, subpartition_name, processing_status, processing_state, base_object_type, base_object_schema, base_object_name, base_process_order, property, size_estimate, in_progress, original_object_schema, original_object_name, creation_level, object_int_oid FROM "EASUSER_NEW"."SYS_IMPORT_FULL_02" WHERE process_order between :3 AND :4 AND duplicate = 0 AND processing_state NOT IN (:5, :6, :7) ORDER BY process_order]
ORA-19505: failed to identify file "/u01/dbbackup/easuser_expdp1.dmp"
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9715

----- PL/SQL Call Stack -----
object      line object
handle    number name
0x30adc80c     21979 package body SYS.KUPW$WORKER
0x30adc80c      9742 package body SYS.KUPW$WORKER
0x30adc80c      4287 package body SYS.KUPW$WORKER
0x30adc80c     10450 package body SYS.KUPW$WORKER
0x30adc80c      1824 package body SYS.KUPW$WORKER
0x2db594d8         2 anonymous block

Job "EASUSER_NEW"."SYS_IMPORT_FULL_02" stopped due to fatal error at Tue Jul 13 13:28:39 2021 elapsed 0 00:00:02

出现这种情况，需要重新导出。

5. 数据库是RAC群集，导出导入时却没加cluster=n参数

解决办法是在RAC群集中做expdp/impdp时，要加cluster=n参数

如：

impdp easuser_new/123456 DIRECTORY=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp REMAP_SCHEMA=easuser:easuser_new remap_tablespace=EAS_D_TESTUSER_STANDARD:EAS_D_easusernew_STANDARD parallel=2 LOGFILE=testusernew_imp.log cluster=n

6. 11.2.0.1导入创建索引时失败

Failing sql is:

CREATE INDEX "CABLEEX"."IDX_CMALL_PRODUCT_VOL" ON "CABLEEX"."T_CMALL_PRODUCT" (UPPER("VOLTAGE")) PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING NOCOMPRESS LOGGING TABLESPACE "TBS_CABLEEX" PARALLEL 1
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14102: only one LOGGING or NOLOGGING clause may be specified

这是因为使用了11.2.0.1这个版本的缘故，该版本存在导入时创建索引时，语句中同时存在NOLOGGING和LOGGING这两个参数。

解决办法：

A. 安装11.2.0.4或更高版本

B.或者把创建索引的语句取出来，

CREATE INDEX "CABLEEX"."IDX_CMALL_PRODUCT_VOL" ON "CABLEEX"."T_CMALL_PRODUCT" (UPPER("VOLTAGE")) PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING NOCOMPRESS LOGGING TABLESPACE "TBS_CABLEEX" PARALLEL 1，删除nologging或logging的其中一个词，再使用修改好的创建索引语句，手工执行脚本以创建索引。

7. 导出时碰ORA-04031 unable to allocate xx bytes of shared memory

该错误是由于共享池无法分配内存，可能是共享池过小，也可能是共享池碎片率过高，无可用内存分配导致，需要增加共享池，或设置alter system set cursor_sharing=force scope=both sid='*'; 并重启数据库。

8. 导出时出现VT表导出错误

exclude是要排除的导出内容， EAS中VT表垃圾表，是不应该、也不需要导出的，要排除掉

解决办法：

增加参数 exclude=table:\"like \'VT%\'\"

9. expdp组件损坏

expdp组件损坏会导致虽然操作无误，但无论怎么导都导不进去的情况

解决办法：

EXPDP/IMPDP是非常高效的数据库逻辑备份与恢复工具，突然有一天如果这两个工具损坏了，无法使用，该如何是好呢？
在最极端的情况下我们也可以通过重建的方法来解决问题。
具体的恢复步骤如下，该过程已在Oracle 11gR2验证，供参考。

1.以SYS身份登录到数据库
secooler@secDB /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 14 22:52:02 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sys@11gR2>

2.使用Catdph.sql脚本重建数据泵的TYPES和VIEWS
sys@11gR2> @$ORACLE_HOME/rdbms/admin/catdph.sql

3.如果安装了XDB，需要执行脚本catmetx.sql
sys@11gR2> @$ORACLE_HOME/rdbms/admin/catmetx.sql

4.使用prvtdtde.plb脚本重建tde_library包
sys@11gR2> @$ORACLE_HOME/rdbms/admin/prvtdtde.plb

5.使用Catdpb.sql脚本重建DataPump包
sys@11gR2> @$ORACLE_HOME/rdbms/admin/catdpb.sql

6.使用Dbmspump.sql脚本重建DataPump对象
sys@11gR2> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

7.如果有无效的数据库对象，重新编译之
sys@11gR2> @$ORACLE_HOME/rdbms/admin/utlrp.sql

通过如上几个步骤，我们便完成了EXPDP/IMPDP工具的重建的工作，如果在重建的过程中没有出现其他问题，可以说我们成功挽救了EXPDP/IMPDP，生命得以延续~~
BTW：如果是Oracle 10gR1版本，重建仅需要执行两个脚本：catdp.sql和dbmspump.sql。

如果上面方法还不行，请考虑rman来做备份恢复。

10. ORA-39064文件写入错误

[oracle@node1 dbbackup]$ expdp system/123456 schemas=easuser directory=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp logfile=easuser_exp.log exclude=table:\"like \'VT%\'\",statistics parallel=2 cluster=n

Export: Release 11.2.0.4.0 - Production on Tue Jul 13 13:51:54 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
解决办法：
请检查目录权是否有写和读权限，或磁盘是否满了，或磁盘是否有损坏

11. ORA-01658 无法为表空间xx创建 initial区

表空间满了，需要加大表空间

执行查询表空间命令

select max1.tablespace_name, Total_GB,GB_used ,GB_free,trunc(pct) "UsedPercent%",trunc(maxbytes) as " MaxGBytes" ,pct_warn ,round(100*GB_used/maxbytes,2) as "growupPercent",
(case   when ((GB_used/maxbytes)>0.9) then 'Almost Full'      else 'Will Auto Increase' end ) AutoExpandStatus
from (select T.TABLESPACE_NAME as tablespace_name,
sum(decode(d.MAXBYTES,0,d.bytes,d.maxbytes)/1024/1024/1024) as maxbytes
from dba_tablespaces t, dba_data_files d where t.TABLESPACE_NAME=d.TABLESPACE_NAME group by T.TABLESPACE_NAME) max1,
(select tbs.tablespace_name,
        ROUND((tot.bytes/1024/1024/1024),1) Total_GB,
        ROUND(trunc(tot.bytes/1024/1024/1024-sum(nvl(fre.bytes,0))/1024/1024/1024),1) GB_used,
        ROUND(trunc(sum(nvl(fre.bytes,0))/1024/1024/1024),1) GB_free,
        (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
        decode(
            greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
            90, '', '*'
        ) pct_warn
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024/1024/1024, tot.bytes) avail1
where avail1.tablespace_name=max1.tablespace_name;
如果查询结果出现 ”Almost Full“ 字样，说明表空间快要满了，需要为表空间增加新的数据库文件；

如果没出现，说明表空间未满。

12. 数据库恢复时报ORA-09070 文件不存在

[oracle@node1 dbbackup]$ impdp easuser_new/123456 DIRECTORY=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dm REMAP_SCHEMA=easuser:easuser_new remap_tablespace=EAS_D_TESTUSER_STANDARD:EAS_D_easusernew_STANDARD parallel=2 LOGFILE=testusernew_imp.log cluster=n

Import: Release 11.2.0.4.0 - Production on Tue Jul 13 14:12:05 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/dbbackup/easuser_expdp2.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

解决方法：

请确定目录是否存大，文件名是否正确，Linux和Unix下，是严格区分大小写的。

13. 备份时连接不上数据库

impdp easuser_new/123456@dblinkname DIRECTORY=DATAPUMP1 dumpfile=easuser_expdp1.dmp,easuser_expdp2.dmp REMAP_SCHEMA=easuser:easuser_new remap_tablespace=EAS_D_TESTUSER_STANDARD:EAS_D_easusernew_STANDARD parallel=2 LOGFILE=testusernew_imp.log cluster=n

如果是连接到本地数据库，又不是PDB数据库，请不要加上@dblinkname这个连接字符串，这样只会降低效率，增加出错的可能。

14. 备份或恢复时日志文件不全

解决办法：

设置环境变量，客户端字集变量要和数据库的一致，避免日志不完整。

EAS大多数情况下是有的UTF8，如果数据库字符集不是UTF8，要改成实际值

Linux下设置环境变量:

export NLS_LANG=american_america.AL32UTF8
export

Windows下设置环境变量:

set NLS_LANG=american_america.AL32UTF8
set

如果字符集不是utf8，按实际情况设置

　有很多种方法可以查出oracle server端的字符集，比较直观的查询方法是以下这种:

SQL> select * from nls_database_parameters where parameter LIKE '%CHARACTERSET%';

15. 备份到目标库时，报表长度不够

这是由于导出的数据库字符集和导入的数据库的字符集不同导致。

需要保证源和目的的字符集一致，避免乱码和表数据被截断，出错。

查出oracle server端的字符集：

select * from nls_database_parameters where parameter LIKE '%CHARACTERSET%';

16. ORA-31693,ORA-02351,ORA-01555 snapshot too old: rollback segment number xx with name "_syssmuxxxxxxx" too small

如果表有LOB字段，可能是LOB字段损坏，需要置空损坏的行。

可参考：

https://vip.kingdee.com/article/127083366482312448

17. ORA-31664: unable to construct unique job name when defalted

ORA-31634: job already exists,ORA-31664: unable to construct unique job name when defaulted (DATAPUMP fails)

Cause
If the entries in DBA_DATAPUMP_JOBS table become equals to 99 for any particular schema then “ORA-31634 : job already exists”

SELECT owner_name,job_name,operation,state FROM DBA_DATAPUMP_JOBS;
Solution
select owner,table_name from dba_tables where table_name like '%SYS%EXPORT%';

SQL> select owner,table_name from dba_tables where table_name like '%SYS%EXPORT%';

OWNER TABLE_NAME
------------------------------ ------------------------------
xxx SYS_EXPORT_SCHEMA_46
xxx SYS_EXPORT_SCHEMA_45
xxx SYS_EXPORT_SCHEMA_44
xxx SYS_EXPORT_SCHEMA_43
xxx SYS_EXPORT_SCHEMA_42
xxx SYS_EXPORT_SCHEMA_41
xxx SYS_EXPORT_SCHEMA_40
xxx SYS_EXPORT_SCHEMA_39
xxx SYS_EXPORT_SCHEMA_38
xxx SYS_EXPORT_SCHEMA_37
xxx SYS_EXPORT_SCHEMA_36
...
99 rows selected.

SQL>

删除 SYSTEM.SYS_EXPORT_FULL_01 直到 99 tables然后重新备份

SQL> drop table xxx.SYS_EXPORT_SCHEMA_01;
Table dropped.
SQL> drop table xxx.SYS_EXPORT_SCHEMA_02;
Table dropped.
SQL> drop table xxx.SYS_EXPORT_SCHEMA_03;
Table dropped.
...............
.................
SQL> drop table xxx.SYS_EXPORT_SCHEMA_99;

19. 如何清除 DBA_DATAPUMP_JOBS 视图中的异常数据泵作业?

解决方案

用于这个例子中的作业:

- 导出作业 SCOTT.EXPDP_20051121 是一个正在运行的 schema 级别的导出作业

- 导出作业 SCOTT.SYS_EXPORT_TABLE_01 是一个表级别的异常导出作业

- 导出作业 SCOTT.SYS_EXPORT_TABLE_02 是一个表级别的停止导出作业

- 导出作业 SYSTEM.SYS_EXPORT_FULL_01 是一个被暂停的全库导出作业

第1步. 用 SQL*PLUS 判断在数据库中有哪些数据泵作业

%sqlplus /nolog

CONNECT / as sysdba

SET lines 200

COL owner_name FORMAT a10;

COL job_name FORMAT a20

COL state FORMAT a12

COL operation LIKE state

COL job_mode LIKE state

-- 查找数据泵作业:

SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%'

ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED

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

SCOTT EXPDP_20051121 EXPORT SCHEMA EXECUTING 1

SCOTT SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0

SCOTT SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0

SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

第2步. 确保在 dba_datapump_jobs 中列出的作业不是活动的数据泵作业: 状态应该是'NOT RUNNING'。

第3步. 同作业属主确认视图 dba_datapump_jobs 中状态为'NOT RUNNING' 的作业不是被暂停，而失败的作业。(例如，SYSTEM 用户的全库导出作业不是一个失败的作业，而是一个被故意暂停的作业)

第4步. 通过 SQL*Plus 找到相关的 master 表：

-- 查找数据泵的 master 表：

SELECT o.status, o.object_id, o.object_type, 'DROP TABLE '||o.owner||'.'||object_name "OWNER.OBJECT"||';'

FROM dba_objects o,dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name

AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

上面语句可能有问题，用这个：

SELECT 'DROP TABLE '||o.owner||'.'||object_name || ';' FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT

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

VALID 85283 TABLE SCOTT.EXPDP_20051121

VALID 85215 TABLE SCOTT.SYS_EXPORT_TABLE_02

VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01

第5步. 对于过去被终止的和根本不会再启动的作业，删除它的 master 表，例如，

DROP TABLE scott.sys_export_table_02;

-- 对于启用了 recycle bin 的系统，需要额外运行：

purge dba_recyclebin;

第6步. 重新运行第1步和第4步对 dba_datapump_jobs 和 dba_objects 的查询。如果 dba_datapump_jobs 里仍然有作业列出，并且这些作业根本没有 master 表，我们就可以以作业属主的身份清除它们。例如，

CONNECT scott/tiger

SET serveroutput on

SET lines 100

DECLARE

h1 NUMBER;

BEGIN

h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SCOTT');

DBMS_DATAPUMP.STOP_JOB (h1);

END;

/

注意：调用 STOP_JOB 过程以后，可能会花一点时间去清除作业，我们可以查询 user_datapump_jobs 检查作业是否已经被清除掉：

SELECT * FROM user_datapump_jobs;

第7步. 确认作业已经被清除

CONNECT / as sysdba

SET lines 200

COL owner_name FORMAT a10;

COL job_name FORMAT a20

COL state FORMAT a12

COL operation LIKE state

COL job_mode LIKE state

-- 查找数据泵作业:

SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%'

ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED

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

SCOTT EXPDP_20051121 EXPORT SCHEMA EXECUTING 1

SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

-- 查找数据泵的 master 表:

SELECT o.status, o.object_id, o.object_type,

o.owner||'.'||object_name "OWNER.OBJECT"

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name

AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT

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

VALID 85283 TABLE SCOTT.EXPDP_20051121

VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01

摘要:

1. 异常数据泵作业不会影响新的数据泵作业. dba_datapump_jobs 是基于 gv$datapump_job, obj$, com$, and user$ 的一个视图。这个视图显示仍在运行的数据泵作业，或者作业的 master 表仍然保留在数据库中，或者不正常结束的作业(异常作业)。如果一个新的数据泵作业启动，会创建一条新的记录，与旧的数据泵作业无关。

2. 当用系统自动生成的作业名启动一个新的数据泵作业时，我们会检查 dba_datapump_job 中现有的名称以保持惟一性。当然，启动这个作业的用户下需要有足够的空间来创建一个新的 master 表。

3. 数据泵作业与用 DBMS_JOBS 包定义的作业不同， DBMS_JOBS 创建的作业使用它自己的进程。数据泵作业使用一个 master 进程和一些 worker 进程。如果一个数据泵作业被暂停，数据泵作业会一直存在在数据库中（status: NOT RUNNING），这时，master 和 worker 进程会被停止，或者不再存在。客户端之后可以再次挂载到这个作业，并且继续作业的执行(START_JOB)。

4. 如果活动的数据泵作业相关联的 master 表被删除，可能会导致不一致.

4.a. 如果是一个导出作业, 不太可能引起不一致，因为删除 master 表只会导致数据泵的 mater 和 worker 进程中止。这种情况类似于客户端发起的一个意外中止。

4.b. 如果这个作业是一个导入作业，那么情况就有所不同。删除掉 master 表会导致数据泵的 worker 和 mater 进程中断。这有可能会引起不完整的导入。例如，没有导入表的所有数据，或表，索引，视图等的导入不完整，这种情况类似于意外中断导入的客户端。

删除 master 表本身不会引起任何数据字典的不一致。如果您在作业完成后还保留 master 表（使用非公开的参数：KEEP_MASTER=Y），以后再删除 master

表的操作不会造成任何不一致。

学习处理Oracle备份过程中常见的报错

恭喜您！