一、环境描述
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.AL32UTF8 export ORACLE_SID=orcl1 expdp system/oracle schemas=roidba,scott DIRECTORY=$BACKUP_DIR DUMPFILE=$DMP_FILE logfile=$LOG_FILE FILESIZE