当前位置：首页 > article >正文

ORACLE 19.8版本数据库环境EXPDP导数据的报错处理

article 2025/3/25 20:49:04

近期用户在做EXPDP导出时，报错异常termination终止;EXPDP本身是简单的功能并且这个环境也是经常做导出的，到底是什么原因导致了这个问题呢？

导出脚本报错：

分析导出日志，当时系统资源充足但是进程启动失败，直觉是遇到了BUG；

在MOS上检查，可以发现这样一个文档：Data Pump Export Fails to Start, Raising 'ORA-31648: Timeout before master process DM00 finished initialization'Error (Doc ID 2677216.1)，各项报错基本匹配；

原因是SELECT COUNT(*) FROM V$SQL_MONITOR; 里面的SQL数量很多，实际查看确实这样，但是RAC环境因为业务分布情况，导致一个节点多一个少.

后续提议在少的节点上做了导出，可以正常完成导出。

文档里面提供的解决办法：

报错现象：

APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
Data Pump export job fails to start, raising errors similar to the following:
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TABLE_02 for user OPER01
ORA-06512: at "SYS.KUPV$FT", line 1121
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1726
ORA-39062: error creating master process DM00
ORA-31648: Timeout before master process DM00 finished initialization.
ORA-06512: at "SYS.KUPP$PROC", line 57
ORA-06512: at "SYS.KUPP$PROC", line 275
ORA-06512: at "SYS.KUPV$FT", line 1668
ORA-06512: at "SYS.KUPV$FT", line 1082
CHANGES

原因：

Trace of the startup indicates Data Pump is waiting on the following query:
SELECT COUNT(1) FROM SYS.V$SQL_MONITOR WHERE SID = :B1 AND STATUS IN ('QUEUED', 'EXECUTING')
This was taking more than two minutes to come back with results as part of the Data Pump master process initialization.
If the Data Pump master process takes more than two minutes to initialize, it will abort.
Found the view for V$SQL_MONITOR contained over 80,000 rows.
Due to the number of rows, the problem query was taking too long to return results and the master process initialization was aborted

解决办法：

If a count of rows in V$SQL_MONITOR returns a high number, and the customer / end user does not make use of SQL
monitoring, then it can be safely disabled.
To disable sql monitoring:
alter system set "_sqlmon_threshold"=0 scope=both sid='*';
To re-enable sql monitoring:
alter system set "_sqlmon_threshold"=5 scope=both sid='*';
A database restart would then be required to clear out the rows from V$SQL_MONITOR.
Alternatively, gathering fixed object statistics when there are a large number of rows in V$SQL_MONITOR may help. Rows
accumulate in V$SQL_MONITOR over time, best to gather stats when there are many rows in this view.
To gather the statistics:
$ sqlplus / as sysdba
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();
NOTE1: This same problem may exist for Data Pump import as well, but this is not verified.
NOTE2: The following SQL Monitor bugs may also help, but they were not tested in this case:
Bug 28789533 - Slow V$SQL_Monitor /SQL_Monitor (Doc ID 28789533.8)
Bug 28204104 - Slow Output from V$SQL_Monitor (Doc ID 28204104.8)