Ora 6512 ошибка

Ernst Leber sent me an email today. He hit an error at a customer upgrading to Oracle 19.9.0 on Exadata with AutoUpgrade. ORA-1422 and ORA-6512 from SYS.DBMS_STATS in Post Upgrade were signaled – and he better flashed back to the Guaranteed Restore Point. He found even a MOS note but still had questions. So time to blog about it in case you hit this error sequence as well.

What happens, and when does it happen?

At first, this is not an AutoUpgrade issue. You may see the same error stack in the post upgrade phase regardless of method (AutoUpgrade, dbupgrade or DBUA) you use to upgrade your database.

This is the error sequence Ernst spotted in the AutoUpgrade console:

Errors in database [ABCD1]
Stage     [GRP]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-2000
[Unexpected exception error]
Cause: Creation of GRP failed
For further details, see the log file located at /u01/app/oracle/cfgtoollogs/autoupgrade/ABCD/ABCD1/103/autoupgrade_20210504_user.log]

-------------------------------------------------
Logs: [/u01/app/oracle/cfgtoollogs/autoupgrade/ABCD/ABCD1/103/autoupgrade_20210504_user.log]
-------------------------------------------------


DATABASE NAME: E661
         CAUSE: ERROR at Line 781619 in [/u01/app/oracle/cfgtoollogs/autoupgrade/ABCD/ABCD1/103/dbupgrade/catupgrd20210504111655e660.log]
        REASON: ORA-01422: exact fetch returns more than requested number of rows
        ACTION: [MANUAL]
        DETAILS: 01422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.

Not nice – but luckily there is a GRP. And he could flash back to it.

In addition, the upgrade summary showed this:

Oracle XML Database                    UPGRADED      19.9.0.0.0  00:01:08
Datapatch                                                        00:01:47
Final Actions                                                    00:01:52
Post Upgrade
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at "SYS.DBMS_STATS", line 40753
    ORA-06512: at "SYS.DBMS_STATS", line 40037
    ORA-06512: at "SYS.DBMS_STATS", line 38914
    ORA-06512: at "SYS.DBMS_STATS", line 38023
    ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24646
    ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24633
    ORA-06512: at "SYS.DBMS_STATS", line 37679
    ORA-06512: at "SYS.DBMS_STATS", line 38596
    ORA-06512: at "SYS.DBMS_STATS", line 39740
    ORA-06512: at "SYS.DBMS_STATS", line 40185
    ORA-06512: at "SYS.DBMS_STATS", line 40734
    ORA-06512: at line 149
Post Upgrade                                                     00:00:06

So in the post upgrade phase, the error happens.

There is a MOS Note, and a patch

Ernst found already MOS Note: 2525596.1 – Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS. In this note he could read the exact error pattern he saw as well – and the recommendation to apply patch 29213893 to cure this issue.

In addition, the note explains that he hit unpublished BUG 29213893 – DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE. But it doesn’t give more detail. Actually, the bug’s subject is a bit misleading as it describes the result but doesn’t say anything about the root cause.

The ORA-1422 happens for gather_table_stats on a table sharing its name with remote table.

So you see this effect when:

select OBJ#, DATAOBJ#, NAME, LINKNAME, NAMESPACE, TYPE# from obj$ where name='USER$';

      OBJ#   DATAOBJ# NAME                           LINKNAME             NAMESPACE  TYPE#
---------- ---------- ------------------------------ -------------------- ---------- ----------
     92815      92815 USER$                          DB2K2                         1          2   
        22         10 USER$                                                        1          2

You see that USER$ has two entries – one with the additional attribute “LINKNAME”.

These two rows cause the trouble – and of course, you MUST NOT delete anything here.

What is the solution?

You need to apply a patch. Patch 29213893 will cure this misbehavior. When you apply it, it will exchange two plb files in ?/rdbms/admin:

  • prvtstai.plb – Package Body dbms_stats_internal
  • prvtstas.plb – Package dbms_stats_internal

The README tells you:

Oracle Database 19 Release 19.11.0.0.210420DBRU

OPTIMIZER Patch for Bug# 29213893 for Generic Platforms

This patch is non-RAC Rolling Installable.
This patch is Data Guard Standby-First Installable.

So unfortunately you can’t apply it rolling in a RAC environment. And as all patch bundles (RUs, RURs, BPs) are always “RAC rolling”, this patch will never make it into an RU or BP.

But for me, this means that you should include this patch by default into your new home provisioning procedure to avoid such issues.

Further Links and Information

  • MOS Note: 2525596.1 – Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS
  • Patch 29213893
  • BUG 29213893 – DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE
  • Oracle 19c Installation with 19.11.0, OJVM and one-off patches

–Mike

Источник

Code you wrote has 1.5 mistakes:

  • 1st: if there’s no such row in U_SPONSOR table, select won’t return NULL as you thought, but no_data_found you should handle; I presume that’s what happened to you
  • 0.5th: remove single quotes from ':new.u_sponsorid' in dbms_output.put_line as you’ll display literally that, not the ID value.

Sample tables and trigger:

SQL> create table u_sponsor (u_sponsorid number);

Table created.

SQL> create table user_registration(u_sponsorid number);

Table created.

SQL> create or replace trigger trg_iu_sponsor
  2    before insert or update of u_sponsorid on user_registration
  3    for each row
  4  declare
  5    l_sponsorid varchar2 (2000);
  6  begin
  7   select u_sponsorid
  8     into l_sponsorid
  9     from u_sponsor
 10     where u_sponsorid = :new.u_sponsorid;
 11  exception
 12    when no_data_found then
 13      raise_application_error(-20010,
 14        'Not a valid SPONSORID: '|| :new.u_sponsorid);
 15  end;
 16  /

Trigger created.

Testing: master row first:

SQL> insert into u_sponsor values (200);

1 row created.

Two details:

SQL> insert into user_registration values (100);
insert into user_registration values (100)
            *
ERROR at line 1:
ORA-20010: Not a valid SPONSORID: 100
ORA-06512: at "SCOTT.TRG_IU_SPONSOR", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_IU_SPONSOR'


SQL> insert into user_registration values (200);

1 row created.

SQL>

However, I guess you used a trigger to enforce referential integrity just for educational purposes, because — if you want to do that properly, create a foreign key constraint. Something like this:

SQL> create table u_sponsor (u_sponsorid number primary key);

Table created.

SQL> create table user_registration(u_sponsorid number references u_sponsor);

Table created.

SQL> insert into u_sponsor values (200);

1 row created.

SQL> insert into user_registration values (100);
insert into user_registration values (100)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C009854) violated - parent key not found


SQL> insert into user_registration values (200);

1 row created.

SQL>

Источник

I am running the below query.

declare
i int;
c_limit constant pls_integer default 10000;
TYPE cdr is table of TABLE_0.ENTITY_ID% type;
c_cdrt_tbl cdr;
cursor c_cdr is (select ENTITY_ID from TABLE_0 where TIME < 1577836800 and status = 2);
BEGIN
open c_cdr ;
LOOP
fetch c_cdr bulk collect into c_cdrt_tbl limit c_limit;
exit when c_cdrt_tbl.count =0;
FORALL i in 1..c_cdrt_tbl.count
delete from TABLE_1 where ID=c_cdrt_tbl(i);
delete from TABLE_2 where ID=c_cdrt_tbl(i);
delete from TABLE_3 where ID=c_cdrt_tbl(i);
delete from TABLE_4 where ID=c_cdrt_tbl(i);
delete from TABLE_5 where ID=c_cdrt_tbl(i);
delete from TABLE_6 where ID=c_cdrt_tbl(i);
commit;
END LOOP;
END;
/

Receiving the below error:

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 18

I am trying to perform cleanup on some tables which are related to one another via the ENTITY_ID (or ID) value. This value is of

type Number(38,0)

in all the mentioned tables.

There might be something wrong with the declaration of the initial variables, and the error suggests (at least to me) that the output of

select ENTITY_ID from TABLE_0 where TIME < 1577836800 and status = 2

is null, however it is not, I have double checked.

Note: Time is in epoch format.

Источник

Running Oracle 10.2.0.3 on Solaris 10. Encountered the error below when testing a full export after applying CPUJan2008. Encountered the same error previously in another database after applying CPUOct2007 on an Oracle 9.2.0.8 database.

EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 18:
PLS-00201: identifier ‘SYS.DBMS_DEFER_IMPORT_INTERNAL’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1204
ORA-06512: at “SYS.DBMS_SQL”, line 323
ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 97
ORA-06512: at “SYS.DBMS_EXPORT_EXTENSION”, line 126
ORA-06512: at line 1

Metalink Note 464672.1 lists the problem as Bug 6392040 and provides a solution.

GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO <user_name>;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO <user_name>;

One important statement to consider from Metalink Note 464672.1:

“There is no fix in this bug because the export utility is no longer supported in 11g and should be replaced by the Data Pump Export.”

Additional References
Metalink Note 464862.1 – “EXP-00008 PLS-00201 When Performing Full Database Export After Applying CPUOCT2007”

Bug 6510213 – “INSTALLED CPUOCT2007; NOW EXP GETS THE ERRORS EXP-00008,ORA-06550,PLS-00201”


This entry was posted on Wednesday, January 16th, 2008 at 17:08 pm and is filed under Oracle, Troubleshooting. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

Источник

Problem Description

——————————-

You are creating a trigger that includes an exception handling block. You wish to raise a user defined error when a certain condition is met within the trigger body using keyword RAISE.

Inside your error handling block you also include a call to RAISE_APPLICATION_ERROR.

Consider this code example —

create table tmp (col1 char(40));

create table violations (col1 varchar2(30));

CREATE OR REPLACE TRIGGER DEMO_RULE_001

BEFORE INSERT OR UPDATE ON TMP

FOR EACH ROW

DECLARE

RULE_001 EXCEPTION;

BEGIN

IF :NEW.col1 = ‘mike’ THEN

dbms_output.put_line(:new.col1);

INSERT INTO VIOLATIONS values (‘violation logged’);

— Raise rule

RAISE RULE_001;

END IF;

EXCEPTION

WHEN RULE_001 THEN

RAISE_APPLICATION_ERROR (-20001,’Guideline Violation, Rule-001.’);

END;

When this trigger is executed, you receive the ora-4088 and ora-6512 errors.

ORA-04088: error during execution of trigger ‘SCOTT.DEMO_RULE_001’

Solution Description

——————————-

You cannot use both RAISE, within the execution block of a trigger, and RAISE_APPLICATION_ERROR, within the exception block.

Explanation

————————

RAISE forces execution to move into the exception block.RAISE_APPLICATION_ERROR, within the exception block, terminates the program.If the trigger body does not complete, the triggering SQL statement and any

SQL statements within the trigger body are rolled back. Thus, execution completes unsuccessfully with a runtime error and it appears as if none of the code within the trigger body gets executed.

Consider this corrected code —

CREATE OR REPLACE TRIGGER DEMO_RULE_001

BEFORE INSERT OR UPDATE ON TMP

FOR EACH ROW

DECLARE

RULE_001 EXCEPTION;

BEGIN

IF :NEW.col1 = ‘mike’ THEN

dbms_output.put_line(:new.col1);

INSERT INTO VIOLATIONS values (‘violation logged’);

— Raise rule

RAISE RULE_001;

END IF;

EXCEPTION

WHEN RULE_001 THEN

—raise_application_error(-20001, ‘Guideline Violation, Rule-001.’);

dbms_output.put_line(‘Guideline Violation, Rule-001.’);

END;

Oracle Support Doc ID 103293.1

Источник

Понравилась статья? Поделить с друзьями:
  • Ora 609 ошибка
  • Org postgresql util psqlexception ошибка при попытке подсоединения
  • Orangefox ошибка 1
  • Org postgresql util psqlexception ошибка отношение не существует
  • Org apache maven plugins ошибка