Oracle 12.2.0.1 expdp pdb hang

我来答

like052

2024-10-12

oracle

Oracle 12.2.0.1 expdp pdb hang

备份其中某一个PDB的元数据，hang住，备份命令如下：

[oracle@cdb trace]$ expdp system/sssss@12xxxxx:1521/testdb directory=dump_dir dumpfile=test41.dmp tables=test.aaaa content=metadata_only

备份单独表或者备份schema均hang住。

Export: Release 12.2.0.1.0 - Production on Thu Dec 28 14:24:52 2023

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@12.3.10.15:1521/iomtestdb directory=dump_dir dumpfile=test41.dmp tables=iomtest.IMS_MAINTAIN content=metadata_only

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-39374: Statistics failed to export. Failing error is

ORA-01732: data manipulation operation not legal on this view

/*不加该参数content=metadata_only，报错相同，增加exclude=statistics，只是没有报错，依然无法正常导出，10046跟踪了几秒和awr一小时间隔看了一下，一直再跑，如下SQL：

SELECT A.NAME FROM ATTRCOL$ A WHERE A.OBJ#=:B2 AND A.INTCOL#=:B1

该SQL大概一秒执行万次以上。但改表数据量在2000以下。

SQL ID: 938wuu4npuj57 Plan Hash: 3549160210

SELECT A.NAME

FROM

ATTRCOL$ A WHERE A.OBJ#=:B2 AND A.INTCOL#=:B1

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0

Execute 366493 33.84 33.65 0 0 0 0

Fetch 366493 1.11 1.18 0 736206 0 3220

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 732986 34.95 34.83 0 736206 0 3220

如果不备份元数据，仅备份data_only，则一切正常，多个PDB中，仅该PDB无法备份元数据。

我来答

添加附件

问题补充

4条回答

默认

最新

like052

解决如下：

实际和该错误有关：ORA-01732: data manipulation operation not legal on this view

使用errorstack跟踪，跟踪具体的SQL：

alter system set events '1732 trace name errorstack forever';

报错SQL如下：

delete from (select c5

from "SYS"."IMPDP_STATS"

where statid is null

and type in ('D', 'O', 'V'))

where c5 in

(select to_char(n1) from sys.spd_scratch_tab l where n1 is not null)

;

对比了PDB的参数，修改设置如下备份元数据成功：

alter system set "_simple_view_merging"=true scope=both;

参考MOS如下（具体SQL不完全一致）：

Schema Level DataPump Export Hangs When Exporting PROCACT_SCHEMA Objects (Doc ID 1545170.1)

有用 0

打赏 0

盖国强

有已知问题和 streams_pool_size 有关，检查该参数，并尝试调大之。

有用 0

打赏 0

like052

streams的值由512M调整到4G，依然没有变化。
[oracle@cdb dump_dir]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 14 16:07:08 2024

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 80G
sga_min_size big integer 0
sga_target big integer 80G
unified_audit_sga_queue_size integer 1048576
SQL> show parameter stream

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 2G
SQL> alter system set streams_pool_size=4G scope=both;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@cdb dump_dir]$ expdp system/oracle123@xxxx:1521/xxx directory=dump_dir dumpfile=expdp_TEST.dmp logfile=expdp_TEST.log schemas=TEST CONTENT=METADATA_ONLY

Export: Release 12.2.0.1.0 - Production on Mon Oct 14 16:07:54 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03": system/********@1xxxx:1521/xxxx directory=dump_dir dumpfile=expdp_TEST.dmp logfile=expdp_TEST.log schemas=TEST CONTENT=METADATA_ONLY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39374: Statistics failed to export. Failing error is
ORA-01732: data manipulation operation not legal on this view

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM

后台执行的SQL除了上次说的，还有如下，执行的次数少。
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PARENT_OBJ.NAME ,KU$.PARENT_OBJ.OWNER_NAME ,KU$.PROPERTY ,KU$.REFPAR_LEVEL ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_HTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS)) AND (BITAND(KU$.FLAGS,536870912)=0)