Record an expdp task exception handling case

Environment:AIX 6.1 + Oracle 10.2.0.4
Symptom:In the xtts migration test phase, when you run several expdp export tasks, the system does not return any information, and the corresponding log does not have any output. view the task status:

SQL> set lines 300col OWNER_NAME for a10col OPERATION for a15col JOB_MODE for a20col STATE for a15select * from dba_datapump_jobs; OWNER_NAME JOB_NAME                       OPERATION       JOB_MODE             STATE               DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS---------- ------------------------------ --------------- -------------------- --------------- ---------- ----------------- -----------------SYS        SYS_EXPORT_TRANSPORTABLE_01    EXPORT          TRANSPORTABLE        DEFINING                 1                 0                 1SYS        SYS_EXPORT_TRANSPORTABLE_02    EXPORT          TRANSPORTABLE        DEFINING                 1                 1                 2SYS        SYS_EXPORT_TRANSPORTABLE_03    EXPORT          TRANSPORTABLE        DEFINING                 1                 1                 2SYS        SYS_EXPORT_SCHEMA_01           EXPORT          SCHEMA               DEFINING                 1                 1                 2SYS        SYS_EXPORT_TRANSPORTABLE_04    EXPORT          TRANSPORTABLE        DEFINING                 1                 1                 2SYS        SYS_EXPORT_SCHEMA_02           EXPORT          SCHEMA               DEFINING                 1                 1                 26 rows selected.

We can see that the state of all the expdp export tasks stays in the defining State.

• 1. An error occurred while trying to clear the problem.
• 2. Tracing and tracing MOS

1. An error occurred while trying to clear the problem.

First, force all expdp tasks executed in the background to be killed:

ps -ef|grep expdp|grep -v grep|awk ‘{print $2}‘|xargs kill -9

Then try to delete these tables (in fact, they should be deleted in the not running state)

select ‘drop table ‘||OWNER_NAME||‘.‘||JOB_NAME||‘ purge;‘ from dba_datapump_jobs where STATE=‘NOT RUNNING‘;drop table sys.SYS_EXPORT_TRANSPORTABLE_01 purge;..

However, the query results remain unchanged.
Even if you try to shut down the database normally, immediate will not be able to succeed. The alarm log shows that there are active calls:

Thu Nov  1 15:14:24 2018Active call for process 4522064 user ‘oracle‘ program ‘[email protected] (DM00)‘Active call for process 4456536 user ‘oracle‘ program ‘[email protected] (DM01)‘Active call for process 10027180 user ‘oracle‘ program ‘[email protected] (DM02)‘Active call for process 7340140 user ‘oracle‘ program ‘[email protected] (DM03)‘Active call for process 6291888 user ‘oracle‘ program ‘[email protected] (DM04)‘Active call for process 8126596 user ‘oracle‘ program ‘[email protected] (DM05)‘SHUTDOWN: waiting for active calls to complete.

It is found that the IDs of these processes correspond to the ora_dm process:

$ ps -ef|grep ora_dm  oracle  4456536        1   0 17:00:09      -  0:00 ora_dm01_xxxxdb  oracle  4522064        1   0 16:50:57      -  0:00 ora_dm00_xxxxdb  oracle  7340140        1   0 14:06:07      -  0:00 ora_dm03_xxxxdb  oracle  8126596        1   0 14:35:03      -  0:00 ora_dm05_xxxxdb  oracle 10027180        1   0 13:55:08      -  0:00 ora_dm02_xxxxdb  oracle  6291888        1   0 14:31:17      -  0:00 ora_dm04_xxxxdb  oracle  7340432  8388786   0 15:22:59  pts/4  0:00 grep ora_dm

In fact, this is the process related to the expdp task and forces these processes to be killed:

ps -ef|grep ora_dm|grep -v grep|awk ‘{print $2}‘|xargs kill -9

Then the database is closed successfully:

Thu Nov  1 15:24:37 2018All dispatchers and shared servers shutdownThu Nov  1 15:24:37 2018ALTER DATABASE CLOSE NORMAL

After the database is started, check again and find that the database has been cleared successfully:

SQL> set lines 300col OWNER_NAME for a10col OPERATION for a15col JOB_MODE for a20col STATE for a15select * from dba_datapump_jobs; no rows selected

Summary:The Data Pump task is related to the ora_dm process. If an exception occurs in the Data Pump task but the task does not exit, kill the process at the same time (the State Changes to not running after the task is killed ). It is not necessary to close the database. It only demonstrates the scenario where the database is normally shut down and blocked. This also shows why it should be cleared in the not running state.

2. Tracing and tracing MOS

The above steps only cleared the abnormal data pump task, but did not solve the problem. If you run the backup task in the background again, the failure will still be repeated:
Nohup sh expdp_xtts.sh &

$ ps -ef|grep expdp  oracle  6684914  8061208   0 15:30:07  pts/2  0:00 grep expdp  oracle  7143482  8061208   0 15:30:03  pts/2  0:00 sh expdp_xtts.sh  oracle  6685096  7143482   0 15:30:03  pts/2  0:00 expdp ‘/ as sysdba‘ parfile=expdp_xtts.par$ ps -ef|grep ora_dm  oracle  7602308  8061208   0 15:30:10  pts/2  0:00 grep ora_dm  oracle  3997964        1   1 15:30:05      -  0:00 ora_dm00_xxxxdb$ 

When dba_datapump_jobs is queried, the State is still defining:

OWNER_NAME JOB_NAME                       OPERATION       JOB_MODE                       STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS---------- ------------------------------ --------------- ------------------------------ ------------------------------ ---------- ----------------- -----------------SYS        SYS_EXPORT_TRANSPORTABLE_01    EXPORT          TRANSPORTABLE                  DEFINING                                1                 1                 2

Other export tasks are the same.
To facilitate the test, write a simple single table expdp for export.

expdp \‘/ as sysdba\‘ directory=XTTS tables=query.test dumpfile=query_test.dmp logfile=query_test.log

Search for expdp state defining in MOS using the following keywords based on the fault symptom and match the document:

• DataPump export/import hangs with "defining" status when using a directory on NFS filesystem (Document ID 2262196.1)

This test is performed on the NFS file system. We recommend that you move the mos File System to the local file system for export.

This time, all expdp processes are killed:

ps -ef|grep ora_dm|grep -v grep|awk ‘{print $2}‘|xargs kill -9ps -ef|grep expdp|grep -v grep|awk ‘{print $2}‘|xargs kill -9

Query dba_datapump_jobs:

OWNER_NAME JOB_NAME                       OPERATION       JOB_MODE                       STATE               DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS---------- ------------------------------ --------------- ------------------------------ --------------- ---------- ----------------- -----------------SYS        SYS_EXPORT_TABLE_04            EXPORT          TABLE                          NOT RUNNING              0                 0                 0SYS        SYS_EXPORT_SCHEMA_01           EXPORT          SCHEMA                         NOT RUNNING              0                 0                 0SYS        SYS_EXPORT_TABLE_02            EXPORT          TABLE                          NOT RUNNING              0                 0                 0SYS        SYS_EXPORT_TABLE_05            EXPORT          TABLE                          NOT RUNNING              0                 0                 0SYS        SYS_EXPORT_TABLE_03            EXPORT          TABLE                          NOT RUNNING              0                 0                 0SYS        SYS_EXPORT_TABLE_01            EXPORT          TABLE                          NOT RUNNING              0                 0                 0SYS        SYS_EXPORT_TRANSPORTABLE_01    EXPORT          TRANSPORTABLE                  NOT RUNNING              0                 0                 07 rows selected.

Clear the not running master table:

Select 'drop table' | owner_name | '. '| job_name | 'purge;' from dba_datapump_jobs where State = 'not running'; -- the execution result is used for execution. Check the result again as null: SQL> select * From dba_datapump_jobs; no rows selected

Follow the mos suggestions to move the export task to the local file system:
Export the xtts source data from the AIX source to the/hxbak/xtts_exp directory on the source, and then copy the data to NFS shared storage/xtts/DMP:

mkdir /hxbak/xtts_expchown oracle:dba /hxbak/xtts_expls -ld /hxbak/xtts_expselect * from dba_directories;create or replace directory XTTS as ‘/hxbak/xtts_exp‘;

In this case, the test expdp task can run normally:

$ expdp \‘/ as sysdba\‘ directory=XTTS tables=query.test dumpfile=query_test.dmp logfile=query_test.logExport: Release 10.2.0.4.0 - 64bit Production on Thursday, 01 November, 2018 16:03:21Copyright (c) 2003, 2007, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_TABLE_01":  ‘/******** AS SYSDBA‘ directory=XTTS tables=query.test dumpfile=query_test.dmp logfile=query_test.log Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 8 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "QUERY"."TEST"                              6.743 MB   72593 rowsMaster table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_TABLE_01 is:  /hxbak/xtts_exp/query_test.dmpJob "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 16:03:57SQL> select * from dba_datapump_jobs;OWNER_NAME JOB_NAME                       OPERATION       JOB_MODE                       STATE               DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS---------- ------------------------------ --------------- ------------------------------ --------------- ---------- ----------------- -----------------SYS        SYS_EXPORT_TABLE_01            EXPORT          TABLE                          EXECUTING                1                 1                 3

Export other metadata again:

#expdp_xtts.sh (about 5min)nohup sh expdp_xtts.sh &#expdp_xtts_other.sh(about 5min)nohup sh expdp_xtts_other.sh &#expdp_tmp_tablenohup sh expdp_tmp_table01.sh &nohup sh expdp_tmp_table02.sh &nohup sh expdp_tmp_table03.sh &nohup sh expdp_tmp_table04.sh &

Finally, move these exported files to/xtts/DMP/for subsequent import on the xtts test target end:

$ pwd/hxbak/xtts_exp$ cp -rp * /xtts/dmp/ 

When importing data to the target end, you only need to have the permission to read these files. The actual test is OK.

Summary:Tested in your Linux environment, you can directly expdp to the NFS file system. There is a difference in Aix. The MOs recommendation is only a workaround, but it can also meet the requirements, after all, the metadata export file is not large.

Record an expdp task exception handling case