记录一次Oracle导入数据库失败的解决办法,最终报错:UDI-04045、ORA-04045、ORA-01775

费了很大的工夫,终于解决了。做个记录。


 

********************************************************************************
创建完成用户及表空间后,导入数据库,出现如下问题:
********************************************************************************

[oracle@ufdb165 bin]$ ./impdp cwy_init0914/cwy_123456789@ufgovdb1 directory=DATA_PUMP_DIR dumpfile=cwy_init_0902.dmp REMAP_SCHEMA=cwy_init:cwy_init0914 transform=segment_attributes:n logfile= imp_cwy_init_0914.log

Import: Release 11.2.0.4.0 - Production on Wed Sep 14 17:11:38 2022

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_FULL_01 for user CWY_INIT0914
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-39080: failed to create queues "KUPC$C_1_20220914171138" and "KUPC$S_1_20220914171138" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1534
ORA-00604: error occurred at recursive SQL level 2
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

******************************************************************************************
解决办法:参考https://blog.csdn.net/u011199063/article/details/54138871/
******************************************************************************************
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql

******************************************************************************************
再次执行导入数据库,出现如下新问题:
******************************************************************************************

[oracle@ufdb165 bin]$ ./impdp cwy_init0914/cwy_123456789@ufgovdb1 directory=DATA_PUMP_DIR dumpfile=cwy_init_0902.dmp REMAP_SCHEMA=cwy_init:cwy_init0914 transform=segment_attributes:n logfile= imp_cwy_init_0914.log

Import: Release 11.2.0.4.0 - Production on Wed Sep 14 19:20:27 2022

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

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

UDI-04045: operation generated ORACLE error 4045
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_DATAPUMP
ORA-01775: looping chain of synonyms

************************************************************************************************
尝试度娘提供的各种办法,仍未解决

如下:

https://blog.csdn.net/totorobig/article/details/111058342
https://blog.51cto.com/williamyang/1397110

************************************************************************************************

无脑中。。。

************************************************************************************************************************************************
整理整个报错过程,注意到第一次的报错:ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
*************************************************************************************************************************************************

undo表空间不足导致报错:ORA-30036

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

  的确是undo表空间不足,已经达到32G,需要增加一个表空间文件

alter tablespace UNDOTBS1 add datafile '/u01/app/oracle/oradata/ufgovdb1/UNDOTBS1_01.dbf' SIZE 5000m AUTOEXTEND ON NEXT 1000m MAXSIZE UNLIMITED;

 ************************************************************************************************************************************************
再次执行导入数据库操作,仍然报错
*************************************************************************************************************************************************

[oracle@ufdb165 bin]$ ./impdp cwy_init0914/cwy_123456789@ufgovdb1 directory=DATA_PUMP_DIR dumpfile=cwy_init_0902.dmp REMAP_SCHEMA=cwy_init:cwy_init0914 transform=segment_attributes:n logfile= imp_cwy_init_0914.log

Import: Release 11.2.0.4.0 - Production on Wed Sep 14 19:20:27 2022

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

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

UDI-04045: operation generated ORACLE error 4045
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_DATAPUMP
ORA-01775: looping chain of synonyms

 ************************************************************************************************************************************************
哈哈,不放弃。再次执行如下操作:
*************************************************************************************************************************************************

SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql

 ************************************************************************************************************************************************
看脚本执行记录,感觉有戏。等执行完成脚本,重新导入数据库
*************************************************************************************************************************************************

[oracle@ufdb165 bin]$ ./impdp cwy_inim/cwy_123456789@ufgovdb1 directory=DATA_PUMP_DIR dumpfile=cwy_init_0902.dmp REMAP_SCHEMA=cwy_init:cwy_inim transform=segment_attributes:n logfile= imp_cwy_init_0914.log

Import: Release 11.2.0.4.0 - Production on Wed Sep 14 20:26:30 2022

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "CWY_INIM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "CWY_INIM"."SYS_IMPORT_FULL_01": cwy_inim/********@ufgovdb1 directory=DATA_PUMP_DIR dumpfile=cwy_init_0902.dmp REMAP_SCHEMA=cwy_init:cwy_inim transform=segment_attributes:n logfile=
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CWY_INIM" already exists
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/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CWY_INIM"."ACT_HI_VARINST" 7.416 GB 40209055 rows
. . imported "CWY_INIM"."ACT_PROC_RU_VARIABLE" 4.310 GB 186555 rows
. . imported "CWY_INIM"."SYS_OPERATIONLOG" 1.033 GB 3733404 rows
. . imported "CWY_INIM"."GL_VOU_DETAIL_ASS_HIS" 2.169 GB 5427501 rows
. . imported "CWY_INIM"."GL_VOU_DETAIL_HIS" 1.812 GB 7344598 rows
. . imported "CWY_INIM"."GL_PREBAL" 481.0 MB 1022850 rows
 ************************************************************************************************************************************************
哈哈,完美解决。正常导入数据库
*************************************************************************************************************************************************

 ************************************************************************************************************************************************
问题根源:undo表空间不足导致。
*************************************************************************************************************************************************

 

posted @ 2022-09-14 20:51  查拉图斯特拉面条  阅读(828)  评论(3编辑  收藏  举报