ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_XX"
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14659796/viewspace-2648736/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Unable to create git index lockGitIndex
- create table of mysql databaseMySqlDatabase
- create a partition table using a exsit table
- Oracle Create Table as SelectOracle
- ftp_rawlist: Unable to create temporary file.FTP
- Fatal error in launcher: Unable to create process using '"'Error
- create table if not exists Waiting for table metadata lockAI
- Unable to create index.lock File exists錯誤Index
- MySQL5.6 create table原理分析MySql
- sqlserver不能直接create table as select ......SQLServer
- create table進階學習(一)
- create table進階學習(三)
- create table進階學習(四)
- Unable To Create Database Using ASM ORA-15055DatabaseASM
- 利用mysql slave 修復master MyISAM tableMySqlAST
- MySQL的create table as 與 like區別MySql
- VBA從Excel中生成Oracle create tableExcelOracle
- create table進階學習系列(八)
- create table進階學習系列(五)
- create table進階學習系列(七)
- create table進階學習系列(九)
- CREATE TABLE AS SELECT(CAST)(二)-ConceptAST
- iptables報錯!iptables-restore unable to initialize table 'filterRESTFilter
- ORA-09925: Unable to create audit trail file彙總AI
- use azure data studio to create external table for oracleOracle
- create table之storage選項initial和其它
- T-SQL的CREATE TABLE語法(下)SQL
- T-SQL的CREATE TABLE語法(上)SQL
- create table進階學習(二)_全域性臨時表_global temporary table
- Oracle案例01——ORA-09925: Unable to create audit trail fileOracleAI
- 解決ORA-27125: unable to create shared memory segment
- 救命啊java.lang.OutOfMemoryError: unable to create new native threadJavaErrorthread
- git拉取程式碼報錯filename too long unable to create fileGit
- oracle 19c 無法create table解決Oracle
- How to Quickly Create a Copy of a Table using Transact-SQLUISQL
- dual表與create table語句的關係
- CREATE TABLE AS SELECT(CAST)(一)-ORA-1652AST
- NOARCHIVE模式CREATE TABLE不記錄日誌呀Hive模式