文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_XX"

2024-04-02 19:55

关注

ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_XX"

EXPDP in Oracle 12c (12.1.0.2) fails with below error. During performing Data Pump Export backup in a 2 node Oracle 12c RAC database, Data Pump job terminates with below error.


=============================================================


Export: Release 12.1.0.2.0 - Production on Mon Jan 23 10:16:15 2017    Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,    OLAP, Advanced Analytics and Real Application Testing options    ORA-31626: job does not exist    ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_05"    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95    ORA-06512: at "SYS.KUPV$FT", line 1048    ORA-06502: PL/SQL: numeric or value error: character string buffer too small


=============================================================

Note : - Master table for Data Pump job was not being created hence it was terminating the Data pump job at its initial startup. In above error we can see ORA-06502 that comes most of the time if we have not set streams_pool_size parameter value to enough one but in my case it was sized enough set as below to perform Data Pump Operations.


NAME                 TYPE               VALUE    ------------------------------------ -------------------------------- ------------------------------    streams_pool_size          big integer           128M

Further we decided to clear any data pump orphaned job left in the database from earlier executions.


-- locate Data Pump master tables:    SQL> SELECT o.status, o.object_id, o.object_type,        o.owner||'.'||object_name "OWNER.OBJECT"      FROM dba_objects o, dba_datapump_jobs j      WHERE o.owner=j.owner_name AND o.object_name=j.job_name       AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;     STATUS  OBJECT_ID OBJECT_TYPE       OWNER.OBJECT    ------- ---------- ----------------------- ----------------------------------------    VALID   1434366 TABLE          SYS.SYS_EXPORT_FULL_01    VALID   1434139 TABLE          SYS.SYS_EXPORT_SCHEMA_01    VALID   1434144 TABLE          SYS.SYS_EXPORT_SCHEMA_02    VALID   1434149 TABLE          SYS.SYS_EXPORT_SCHEMA_03    VALID   1434160 TABLE          SYS.SYS_EXPORT_SCHEMA_04    VALID   1434377 TABLE          SYS.SYS_EXPORT_SCHEMA_05    VALID   1432335 TABLE          SYSTEM.SYS_EXPORT_FULL_01    VALID   1434155 TABLE          SYSTEM.SYS_EXPORT_FULL_02    VALID   1434339 TABLE          SYSTEM.SYS_EXPORT_FULL_03    VALID   1434344 TABLE          SYSTEM.SYS_EXPORT_FULL_04    VALID   1434349 TABLE          SYSTEM.SYS_EXPORT_FULL_05    VALID   1434354 TABLE          SYSTEM.SYS_EXPORT_FULL_06    VALID   1434360 TABLE          SYSTEM.SYS_EXPORT_FULL_07    VALID   1434372 TABLE          SYSTEM.SYS_EXPORT_FULL_08    VALID   1434392 TABLE          SYSTEM.SYS_EXPORT_FULL_09    VALID   1434408 TABLE          SYSTEM.SYS_EXPORT_FULL_10    VALID   1434983 TABLE          SYSTEM.SYS_EXPORT_FULL_11    -- Below we cleared all Orphaned Data Pump Jobs.    SQL> drop table SYS.SYS_EXPORT_FULL_01;    Table dropped.    SQL> drop table SYS.SYS_EXPORT_SCHEMA_01;    Table dropped.    SQL> drop table SYS.SYS_EXPORT_SCHEMA_02;    Table dropped.    SQL> drop table SYS.SYS_EXPORT_SCHEMA_03;    Table dropped.    SQL> drop table SYS.SYS_EXPORT_SCHEMA_04;    Table dropped.    SQL> drop table SYS.SYS_EXPORT_SCHEMA_05;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_01;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_02;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_03;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_04;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_05;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_06;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_07;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_08;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_09;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_10;    Table dropped.    SQL> drop table SYSTEM.SYS_EXPORT_FULL_11;    Table dropped.

-- Now no orphaned data pump job left in the system.


 SQL> SELECT * FROM user_datapump_jobs;    no rows selected

  SQL> SELECT owner_name, job_name, rtrim(operation) "OPERATION",        rtrim(job_mode) "JOB_MODE", state, attached_sessions     FROM dba_datapump_jobs     WHERE job_name NOT LIKE 'BIN$%'     ORDER BY 1,2;     2  3  4  5    no rows selected

-- As, we are clean at this step so tried to run expdp job again.


 Export: Release 12.1.0.2.0 - Production on Wed Jan 18 19:33:40 2017     Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.     Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production     With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,     OLAP, Advanced Analytics and Real Application Testing options     ORA-31626: job does not exist     ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_05"     ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95     ORA-06512: at "SYS.KUPV$FT", line 1048     ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Terrible, it failed again with same error. We then decided to re-load Data Pump packages even our Catalog status was in VALID state in database registry.


 -- Decided to re-load data pump packages    1.Catproc.sql

  SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

 2.To recompile invalid objects, if any    SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Note  :- you need to follow proper steps to run catproc.sql script in Oracle RAC database.
=============================================================Tried to fire Data Pump Export job again but failed with same error……  L è  Tried to trace the data pump session to diagnose it in depth. You should try this step earlier than reloading data pump packages. We set below event tracing and fired Data Pump job again and it generated trace file for that point in time. Make sure you turn off the event after you are done with tracing. Set event 6502 to trap ORA-6502 and dump a stack trace 


SQL> alter system set events '6502 trace name errorstack level 3';     SQL> alter system set events 'sql_trace {process : pname = dw , pname =     dm} level=12';



After reviewing the trace file generated, we could see, there was a TRIGGER( MONITORING_DDL) which was   causing our export job to fail at every attempt.


 SQL> select owner, object_name, object_type, status from dba_objects where lower(object_name) like '%monitoring_ddl';    OWNER      OBJECT_NAME          OBJECT_TYPE       STATUS    --------------- ------------------------------ ----------------------- -------    SYSTEM     MONITORING_DDL       TRIGGER         VALID    SYSTEM     MONITORING_DDL       TABLE          VALID    3 rows selected.



-- We Disabled the Trigger..............

We decided to disable to trigger as it was preventing DDLs operations to be performed other than SYS and SYSTEM users.


SQL> alter trigger system.MONITORING_DDL disable;    Trigger altered.


Finally, tried to run Data Pump Export Job again and it went fine.


Export: Release 12.1.0.2.0 - Production on Mon Jan 23 12:37:53 2017    Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,    OLAP, Advanced Analytics and Real Application Testing options    FLASHBACK automatically enabled to preserve database integrity.    Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=EXPORT_DIR dumpfile=expdp_rac1_2017-01-23.dmp logfile=expdp_rac1_2017-01-23.log full=y metrics=y    Startup took 8 seconds    Estimate in progress using BLOCKS method...    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA       Estimated 18894 TABLE_DATA objects in 357 seconds    Total estimation using BLOCKS method: 120.9 GB    Processing object type DATABASE_EXPORT/TABLESPACE       Completed 175 TABLESPACE objects in 18 seconds



FROM http://rajkumar-dba.blogspot.com/2017/01/ora-31633-unable-to-create-master-table.html


阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯