转 Trace a specific ORA- error 和 impdp报错 ORA-39029 和 /ORA-04036: PGA memory use 和 ORA-39082
#感谢Vincent Fenoll,
Trace a specific ORA- error
How to trace a specific ORA- error that is raised and can be reproduced?
We want to find the complete Oracle stack to understand the origin of this error.
For example, I want to understand why datapump expdp/impdp raises:
ORA-01422: exact fetch returns more than requested number of rows
This time, the alert file is not very helpful.
To determine the root cause, event 1422 can be set as follows:
connect /as sysdba alter system set events '1422 trace name ERRORSTACK level 3';
[…reproduce the issue…]
The event can be turned off again using:
connect /as sysdba
alter system set events '1422 trace name ERRORSTACK off';
In the event 1422 trace file, below sql query was causing error.
*** ACTION NAME:(Select sys_context into variable) 2017-01-27 14:15:24.257
*** MODULE NAME:(Trigger DDL_AUDIT) 2017-01-27 14:15:24.257
*** SERVICE NAME:(SYS$USERS) 2017-01-27 14:15:24.257
*** SESSION ID:(3265.22843) 2017-01-27 14:15:24.257
*** 2017-01-27 14:15:24.257
ksedmp: internal or fatal error
ORA-01422: exact fetch returns more than requested number of rows
Current SQL statement for this session:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’)
Of course, it’s easier to locate the target module if you have already instrumented your code with DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION;
In this case, the problem was due to SYS_CONTEXT(‘USERENV’,’SESSIONID’) returning 2 rows causing the ORA-01422 and subsequent datapump failure.
The workaround of this specific problem is to disable the Trigger DDL_AUDIT.
A solution is to modify the cursor or the statement in the trigger to retreive just one row, for example:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’) and rownum=1;
Another solution is to trap error with an excveption clause.
Author: Vincent Fenoll, Oracle DBA Montreal
Trace a specific ORA- error
How to trace a specific ORA- error that is raised and can be reproduced?
We want to find the complete Oracle stack to understand the origin of this error.
For example, I want to understand why datapump expdp/impdp raises:
ORA-01422: exact fetch returns more than requested number of rows
This time, the alert file is not very helpful.
To determine the root cause, event 1422 can be set as follows:
connect /as sysdba alter system set events '1422 trace name ERRORSTACK level 3';
[…reproduce the issue…]
The event can be turned off again using:
connect /as sysdba
alter system set events '1422 trace name ERRORSTACK off';
In the event 1422 trace file, below sql query was causing error.
*** ACTION NAME:(Select sys_context into variable) 2017-01-27 14:15:24.257
*** MODULE NAME:(Trigger DDL_AUDIT) 2017-01-27 14:15:24.257
*** SERVICE NAME:(SYS$USERS) 2017-01-27 14:15:24.257
*** SESSION ID:(3265.22843) 2017-01-27 14:15:24.257
*** 2017-01-27 14:15:24.257
ksedmp: internal or fatal error
ORA-01422: exact fetch returns more than requested number of rows
Current SQL statement for this session:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’)
Of course, it’s easier to locate the target module if you have already instrumented your code with DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION;
In this case, the problem was due to SYS_CONTEXT(‘USERENV’,’SESSIONID’) returning 2 rows causing the ORA-01422 and subsequent datapump failure.
The workaround of this specific problem is to disable the Trigger DDL_AUDIT.
A solution is to modify the cursor or the statement in the trigger to retreive just one row, for example:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’) and rownum=1;
Another solution is to trap error with an excveption clause.
Author: Vincent Fenoll, Oracle DBA Montreal
#######sample 1 oracle 19c 进行并行导入 impdp时候，报错，报错如下：ORA-39014: ORA-39029
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-01403: no data found
ORA-01403: no data found
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := 'ACCNO'; c(2) := 'ACCATTRIBUTE'; c(3) := 'COINTYPE'; DBMS_METADATA.GET_STAT_INDNAME('REBOPR','ACCINFO',c,3,i_o,i_n); EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,370652,2993,370652,1,1,200576,2,370652,NV,NV,TO_DATE('2022-03-17 22:10:37',df),NV; DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; END;
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 《-另外这个报错是在处理最后一个统计信息导入报错
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 5 with process name "DW00" prematurely terminated
Job "DBMGR"."SYS_IMPORT_FULL_01" stopped due to fatal error at Wed Apr 6 13:42:17 2022 elapsed 0 02:59:50
经过检查发现是这个问题：
REDISCOVERY INFORMATION:
If using Data Pump to import a package body in parallel causes a performance problem,
worker crash, or job termination due to underlying deadlock or hang, then this issue may be
the cause. Errors such as the following may be seen:
Bug 30662417 - ORA-39029 / ORA-1775 / ORA-32701 / ORA-4020 / ORA-60 / ORA-54 Errors and Worker Process Termination Possible During Data Pump Import (Doc ID 30662417.8) To BottomTo Bottom
Bug 30662417 ORA-39029 / ORA-1775 / ORA-32701 / ORA-4020 / ORA-60 / ORA-54 Errors and Worker Process Termination Possible During Data Pump Import
Versions confirmed as being affected
19.14.0/19.4.0
Interim patches may be available for earlier versions - click here to check.
Symptoms:
Related To:
Deadlock
Error May Occur
Hang (Process Hang)
ORA-39029 / ORA-1771 / ORA-32701 / ORA-4020 / ORA-4021 / ORA-60 / ORA-54
Datapump Import utility
Description
Parallel import can cause a Data Pump worker crash, performance issue, or job termination
due to deadlocks or hang when failing to obtain a lock and then going into wait mode. This
has now been fixed.
REDISCOVERY INFORMATION:
If using Data Pump to import a package body in parallel causes a performance problem,
worker crash, or job termination due to underlying deadlock or hang, then this issue may be
the cause. Errors such as the following may be seen:
ORA-39029: worker with process name prematurely terminated
ORA-01771: looping chain of synonyms
ORA-32701: Possible hangs up to hang detected
ORA-04020: deadlock detected while trying to lock object
ORA-04021: timeout occurred while waiting to lock object
ORA-00060: deadlock detected while waiting for resource
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Workaround
None
下一步检查方向
1.清理已经导入用户，然后重新执行导入：再删除用户的时候，可能会出现锁，需要查杀旧的进程，具体按照这篇文档
https://www.cnblogs.com/feiyun8616/p/7121391.html
如果还是没有进展，
2. 另外这个报错是在处理最后一个统计信息导入报错，可以考虑跳过这一步
，impdp时加上EXCLUDE=STATISTICS，导入后手动收集统计信息。
3.可以考虑进行安装补丁30662417
参考文档https://www.gxlcms.com/mysql-315177.html
####sample 2 ORA-39082: Object type string created with compilation warnings
March 5th, 2011 
rishi
0|
ORA-39082:
|
Object type string created with compilation warnings
|
|
Cause:
|
The object in the SQL statement following this error was created with compilation errors. If this error occurred for a view, it is possible that the base table of the view was missing.
|
|
Action:
|
This is a warning. The object may have to be recompiled before being used.
|
转载于:https://blog.51cto.com/cwind/1010089
#####sample 3
##### sample 3 ORA-06512: /ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
UDI-00028: operation generated ORACLE error 28
ORA-00028: your session has been killed
ORA-06512: at "SYS.KUPV$FT_INT", line 1820
ORA-06512: at "SYS.KUPC$QUE_INT", line 635
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-06512: at "SYS.DBMS_AQ", line 1163
ORA-06512: at "SYS.KUPC$QUE_INT", line 602
ORA-06512: at "SYS.KUPC$QUE_INT", line 1801
ORA-06512: at line 1
UDI-03114: operation generated ORACLE error 3114
ORA-03114: not connected to ORACLE
分析：
SQL> SQL> SQL> show parameter pga_ag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 3000M
pga_aggregate_target big integer 512M
解决办法：
To increase the value, there are two ways to do this
Set the value of pga_aggregate_limit to 0 and the parameter will not have any impact. Run below sql by logging in to sqlplus as agile database user:
alter system set pga_aggregate_limit=0 scope=both;
Another option is to set this value to a much higher value if there is enough physical memory in the server. Run below sql by logging in to sqlplus as agile database user:
alter system set pga_aggregate_limit=10072M scope=both;
Note: 3072M is just an example. Work with the DBA to check the appropriate value in the setup.
