Shulou(Shulou.com)06/01 Report--

I. Description of environment

12.1.0.2 RAC

II. Detailed process

Yesterday, I did an expdp backup operation of 12c RAC environment, and the result was an error. I checked the official website and it turned out to be BUG.

1. an error message

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

2. The new parameter logtime causes errors to be reported

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. reason

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 inBug 18920652 - DATAPUMP WITH LOGTIME CRASHES WHEN NLS_LANG IS SET AT O/S LEVEL closed as a duplicate ofBug 17714887 - ORA-31623 ON IMPDP WITH DBLINK Please note that Bug 17714887 has been superseded by unpublished Bug 21094393Unpublished Bug 21094393 is fixed in 12.2.

4. the solution

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 ToolPlease refer toNote 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

III. Attach a tested backup script

#!/ 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).dmpLOG_FILE=orcl_$(date +%Y%m%d_%H%M%S).logBACKUP_DIR=DUMP_BACKUP_DIR###main command ###export LANG=AMERICAN_AMERICA.AL32UTF8export ORACLE_SID=orcl1expdp system/oracle schemas=roidba,scott DIRECTORY=$BACKUP_DIR DUMPFILE=$DMP_FILE logfile=$LOG_FILE FILESIZE=20G compression=all parallel=3 CLUSTER=N;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.