12c expdp ORA-31623 -又遇到BUG

一、环境描述
12.1.0.2 RAC

二、详细过程

昨天做了一个12c RAC环境的expdp备份操作，结果出现报错，查看了官网竟然是BUG。

1.报错信息

UDE-31623: operation generated oracle error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3905
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5203
ORA-06512: at line 1

2.新参数logtime使用导致报错

Parameter LOGTIME is being used.

This parameter specifies that messages displayed during export/import operations be timestamped. You can use the timestamps to figure out the elapsed time between different phases of a Data Pump operation. Such infORMation can be helpful in diagnosing performance problems and estimating the timing of future similar operations.

The parameter as such is not essential for performing an export/import.

3.原因

Data Pump export/import with LOGTIME parameter crashes if the environment variable NLS_DATE_FORMAT is set. In some cases the errors are seen if both NLS_DATE_FORMAT and NLS_LANG are set.

The problem is addressed in
Bug 18920652 - DATAPUMP WITH LOGTIME CRASHES WHEN NLS_LANG IS SET AT O/S LEVEL 
closed as a duplicate of
Bug 17714887 - ORA-31623 ON IMPDP WITH DBLINK 

Please note that Bug 17714887 has been superseded by unpublished Bug 21094393

Unpublished Bug 21094393 is fixed in 12.2.

4.解决的方法

To solve the issue, use any of below alternatives:

Apply interim patch 21094393, if available for your platform and Oracle version.

To check for conflicting patches, please use the MOS Patch Planner Tool
Please refer to
Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?

If no patch exists for your version, please contact Oracle Support for a backport request.

- OR -

As possible workarounds:

Do not use the LOGTIME parameter, as it is not essential to the export/import functionality as such.

- OR -

Unset the NLS_DATE_FORMAT and NLS_LANG environment variables

三、附上测试过的一个备份脚本

#!/bin/sh
#filename:expdpbackup.sh
#authored by roidba
#date:2017-12-14
#$sh /home/oracle/backup.sh >/dev/null 2>&1 &
#crontab -e
#CREATE DIRECTORY dump_backup_dir as '/home/oracle';
#grant read,write on directory dump_backup_dir to system;
#col owner for a10
#col directory_name for a20
#col directory_path for a50
#set lines 150
#select * from dba_directories;
###variables###
DMP_FILE=orcl_$(date +%Y%m%d_%H%M%S).dmp
LOG_FILE=orcl_$(date +%Y%m%d_%H%M%S).log
BACKUP_DIR=DUMP_BACKUP_DIR
###main command ###
export LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_SID=orcl1
expdp system/oracle schemas=roidba,scott DIRECTORY=$BACKUP_DIR DUMPFILE=$DMP_FILE logfile=$LOG_FILE FILESIZE=20G compression=all parallel=3 CLUSTER=N;