12c expdp ORA-31623 -又遇到BUG

一、環(huán)境描述
12.1.0.2 RAC

二、詳細(xì)過程

昨天做了一個(gè)12c RAC環(huán)境的expdp備份操作，結(jié)果出現(xiàn)報(bào)錯(cuò)，查看了官網(wǎng)竟然是BUG。

1.報(bào)錯(cuò)信息

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.新參數(shù)logtime使用導(dǎo)致報(bào)錯(cuò)

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

三、附上測(cè)試過的一個(gè)備份腳本

#!/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;