Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to deal with expdp task anomalies. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Environment: AIX 6.1 + Oracle 10.2.0.4

Phenomenon: in the XTTS migration test phase, when you encounter several expdp export tasks, no information is returned, and there is no output corresponding to the log. Check 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.

You can see that the STATE of all expdp export tasks stays in the DEFINING state.

1. Niudao small test to clear the abnormal

First force to kill all expdp tasks executed in the background:

Ps-ef | grep expdp | grep-v grep | awk'{print $2}'| xargs kill-9

Then try to delete these tables (which should actually be deleted in not running)

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

But this is useless, the query results remain the same.

Even if you try to stop the database with normal shutdown immediate, it will not succeed. The alarm log shows that there is an active call:

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

It is found that the id of these processes corresponds to the process of ora_dm:

$ps-ef | grep ora_dm oracle 4456536 10 17:00:09-0:00 ora_dm01_xxxxdb oracle 4522064 10 16:50:57-0:00 ora_dm00_xxxxdb oracle 7340140 10 14:06:07-0:00 ora_dm03_xxxxdb oracle 8126596 10 14:35:03-0:00 ora_dm05_xxxxdb oracle 10027180 10 13:55:08-0:00 ora_dm02_xxxxdb oracle 6291888 10 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 associated with the expdp task, forcing them to be killed:

Ps-ef | grep ora_dm | grep-v grep | awk'{print $2}'| xargs kill-9

After that, the database was closed successfully:

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

After starting the database, the query again found that it had been cleaned 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 there is an exception in the data pump task, but the task does not exit, you need to kill such processes at the same time (after killing, the state will change to NOT RUNNING). It is not necessary to close the library, just to demonstrate that the blocked scene is closed normally at this time. This also explains why it is necessary to ensure that you can only clean up in the NOT RUNNING state.

two。 Trace back to the source and trace the MOS

The above steps only clean up the abnormal data pump task, but do not solve the problem. Performing the backup task again in the background will still cause the failure:

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 15:30:10 pts/2 0:00 grep ora_dm oracle 3997964 1 15:30:05-0:00 ora_dm00_xxxxdb$

At this point, the query dba_datapump_jobs,state is still in the defining status:

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

All other export tasks are the same, so I won't repeat them.

To facilitate testing, write a simple single table expdp export, and the phenomenon is the same.

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

Based on the failure phenomenon, search the MOS with the following keywords: expdp state DEFINING, match to the document:

DataPump Export/Import Hangs With "DEFINING" Status When Using A Directory On NFS Filesystem (document ID 2262196.1)

As it happens, this test is on the NFS file system, and MOS recommends moving to the local file system for export.

Kill all the expdp processes this time:

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 at this time:

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.

Clean up the master table for NOT RUNNING:

Select 'drop table' | | OWNER_NAME | |'. | | JOB_NAME | | 'purge;' from dba_datapump_jobs where STATE='NOT RUNNING';-- execution result is used for execution. If you check the result again, it is empty: SQL > select * from dba_datapump_jobs;no rows selected.

Move the export task to the local file system as recommended by MOS:

The AIX source side exports the XTTS source data to the source side / hxbak/xtts_exp directory, and then copy 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'

At this point, 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 additional 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 export files to / xtts/dmp/ for subsequent import on the target side of xtts testing:

$pwd/hxbak/xtts_exp$ cp-rp * / xtts/dmp/ the above is shared by the editor on how to handle expdp task exceptions. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.