awrextr.sql 导出失败

Ling.QIu

Using the dump file prefix: awrdat_7426_7605
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /oracle/Ling
|   awrdat_7426_7605.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /oracle/Ling
|   awrdat_7426_7605.log
|
Exception encountered in AWR_EXTRACT
ORA-31626: 作业不存在
ORA-31637: 无法创建作业 SYS_EXPORT_TABLE_02 (用户 SYS)
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512:
在 "SYS.KUPV$FT_INT", line 672
ORA-31635: 无法建立作业资源同步


begin
*
第 1 行出现错误:
ORA-31623: 作业没有通过指定的句柄连接到此会话
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: 在 "SYS.DBMS_DATAPUMP", line 938
ORA-06512: 在 "SYS.DBMS_DATAPUMP", line 4946
ORA-06512: 在 "SYS.DBMS_SWRF_INTERNAL", line 691
ORA-06512: 在 "SYS.DBMS_SWRF_INTERNAL", line 1007
ORA-06512: 在 line 4

Ling.QIu

数据库版本是10.2.0.5
exp 与数据泵都能正常应用

Ling.QIu

问题搞定了  STREAMS_POOL_SIZE设置为0的问题...让你们手工建库....
DataPump export (expdp) does not start:

$ expdp system/****** directory=test dumpfile=system.dmp logfile=abcd.log full=y

Export: Release 11.2.0.1.0 - Production on Thu Mar 25 12:11:31 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1


Solution
For this DataPump error, you will need to configure the database with some Streams Pool.

Perform the following query to find out how much STREAMS_POOL_SIZE has been allocated

select * from v$sgainfo;
...
Streams Pool Size                               0 Yes

Data Pump now uses Advanced Queuing (AQ) internally, so it can handle job stops/starts/re-starts.  However, AQ using the Streams Pool, where the database currently has the STREAMS_POOL_SIZE set to ZERO.

Manually set the STREAMS_POOL_SIZE, re-start the database and re-attempt the Data Pump Export.

Alternately, turn on Automatic Shared Memory management (ASMM) by setting SGA_TARGET or MEMORY_TARGET.

Maclean Liu(刘相兵

DataPump工具Expdp/Impdp需要从PGA和SGA的Shared Pool、Streams Pool和Large Pool分配必要的内存。为了避免Expdp/Impdp出现ORA-04031/ORA-04030错误，我们有必要在自动管理模式下设置合理的pga_aggregate_target和sga_target(抑或者memory_target)内存初始化参数，如果使用手动的SGA管理的话，那么有必要保证shared_pool_size的设置适宜，对于Streams Pool和Large Pool一般设置为150MB大小。

详见  http://www.oracledatabase12g.com ... 04031-ora-4030.html

Ling.QIu

Liu 的终极解决方案
http://www.oracledatabase12g.com ... 04031-ora-4030.html