[TOC]

一、问题描述

​ 客户反馈，每天定时导出任务执行失败。

二、排查思路

1. 检查crontab导出任务

00 05 * * * bash /server/scripts/mes_table_backup.sh
00 20 * * * /bin/find /home/oracle/dmp/ -mtime +6|xargs rm -fr
复制

每天导出，定时删除dmp文件，保留6天。

2. 查看expdp备份脚本

[oracle@oracledb ~]# cat /server/scripts/mes_table_backup.sh 
#! /bin/bash
if [ -f ~/.bash_profile ];
then
  . ~/.bash_profile
fi
##backup table
/u01/app/oracle/oracle/product/11.2.0/db_1/bin/expdp backup_dba/backup_dba directory=DUMP_DIR dumpfile=expdp_all_$(date +%Y%m%d%H%M).dmp logfile=expdp_all_$(date +%Y%m%d%H%M).log SCHEMAS='xx','xx','xx','xx','xx' content=METADATA_ONLY cluster=n
复制

3. 查看dump_dir directory对应路径

[root@oracledb ~]# ll /home/oracle/dmp
ls: cannot access /home/oracle/dmp: No such file or directory
复制

dmp路径居然不存在，难道被人为删除了？ 经询问，无人删除，先不管，手动创建dmp目录。

[root@oracledb ~]# su - oracle
[oracle@oracledb ~]# mkdir dmp
[oracle@oracledb ~]# ll /home/oracle/dmp
total 0
复制

4. 手动执行脚本，报错

ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted
复制

5. 解决办法

-- 清理not running的job
[oracle@oracledb ~]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 31 01:32:20 2023
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-- 执行以下输出结果（生成清理dba_datapump_jobs的sql语句）
SQL> select 'drop table '||owner_name||'.'||job_name||';'  from  dba_datapump_jobs where state='NOT RUNNING';

'DROPTABLE'||OWNER_NAME||'.'||JOB_NAME||';'
--------------------------------------------------------------------------------
drop table BACKUP_DBA.SYS_EXPORT_SCHEMA_01;
drop table BACKUP_DBA.SYS_EXPORT_SCHEMA_02;
drop table BACKUP_DBA.SYS_EXPORT_SCHEMA_03;
....


SQL> drop table BACKUP_DBA.SYS_EXPORT_FULL_01;

Table dropped.
SQL> drop table BACKUP_DBA.SYS_EXPORT_FULL_02;

Table dropped.
SQL> drop table BACKUP_DBA.SYS_EXPORT_FULL_03;

Table dropped.
SQL> drop table BACKUP_DBA.SYS_EXPORT_FULL_04;

Table dropped.
SQL> drop table BACKUP_DBA.SYS_EXPORT_FULL_05;

Table dropped.

.......
复制

6. 再次执行脚本

[oracle@oracledb ~]# sh /server/scripts/mes_table_backup.sh
Export: Release 11.2.0.4.0 - Production on Fri Mar 31 03:37:58 2023
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "BACKUP_DBA"."SYS_EXPORT_SCHEMA_01":  backup_dba/******** directory=DUMP_DIR dumpfile=expdp_all_202303310337.dmp logfile=expdp_all_202303310337.log SCHEMAS=xx,xx,xx,xx,xx content=METADATA_ONLY cluster=n 
Processing object type SCHEMA_EXPORT/USER
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
....

Dump file set for BACKUP_DBA.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/dmp/expdp_all_202303271123.dmp
Job "BACKUP_DBA"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Mar 27 03:40:48 2023 elapsed 0 00:00:05
复制

结论：导出正常，且后续观察，crontab导出任务执行正常。 

[oracle@oracledb dmp]$ ll
total 755768
-rw-r----- 1 oracle asmadmin 192888832 Mar 27 11:31 expdp_all_202303271123.dmp
-rw-r--r-- 1 oracle asmadmin      4528 Mar 27 11:31 expdp_all_202303271123.log
-rw-r----- 1 oracle asmadmin 192933888 Mar 28 05:11 expdp_all_202303280503.dmp
-rw-r--r-- 1 oracle asmadmin      4528 Mar 28 05:11 expdp_all_202303280503.log
-rw-r----- 1 oracle asmadmin 193032192 Mar 29 05:11 expdp_all_202303290503.dmp
-rw-r--r-- 1 oracle asmadmin      4528 Mar 29 05:11 expdp_all_202303290503.log
-rw-r----- 1 oracle asmadmin 193085440 Mar 30 05:11 expdp_all_202303300503.dmp
-rw-r--r-- 1 oracle asmadmin      4528 Mar 30 05:11 expdp_all_202303300503.log
复制

三、思考

1. dmp文件夹怎么没有了 ？

结论：是因为expdp导出任务失败，导致原来的dmp目录保留时间超过6天后，dmp目录被crontab中定义的删除脚本删除。 

2. 为什么有那么多的not running的job ？

​ 为什么expdp产生了这么多的SYS_EXPORT_FULL_XXX表，正常情况下expdp结束后oracle会自动清除，那这里为什么没有自动删除？

-- 手动连到backup_dba用户下执行drop
SQL> conn backup_dba/backup_dba
Connected.
SQL> 
SQL> drop table backup_dba.SYS_EXPORT_FULL_05;
drop table backup_dba.SYS_EXPORT_FULL_05
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 7
复制

结论：backup_dba用户执行drop table报错，触发trigger，不允许drop table，原来这才是根因，后续调整trigger脚本，允许backup_dba用户执行drop table，expdp导出后可正常清理SYS_EXPORT_FULL_XXX表。