ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> DROP TABLE CDR;
DROP TABLE CDR;
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SOLUTION

Check offending reference(s)

SQL> SELECT  TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
     FROM    DBA_CONSTRAINTS
     WHERE   OWNER = 'TEST'
     AND     CONSTRAINT_TYPE='R'
     AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='CDR');

TABLE_NAME     CONSTRAINT_NAME  C R_CONSTRAINT_NAME   STATUS
-------------- ---------------- - ------------------- --------
TRANSACTION    FK_TRANSACTION   R SYS_C0096900        ENABLED

Drop offecdning reference(s)

By just disabling reference(s), it is still not working.

SQL>  alter table TRANSACTION disable constraint FK_TRANSACTION;

Table altered.

SQL> DROP TABLE CDR;
DROP TABLE CDR;
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

Having to drop offending reference(s), then it is working.

SQL>  alter table TRANSACTION drop constraint FK_TRANSACTION;

Table altered.

SQL> DROP TABLE CDR;

Table dropped.

OR

Drop the table with “CASCADE CONSTRAINTS” option.

SQL> DROP TABLE CDR CASCADE CONSTRAINTS;

Table dropped.

ORA-65025 when Renaming Global_Name in PDB

SQL>select * from global_name;

GLOBAL_NAME
--------------------------------------------------
OLDNAME

SQL>  alter pluggable database rename global_name to NEWNAME;
 alter pluggable database rename global_name to NEWNAME
                                                *
ERROR at line 1:
ORA-65045: pluggable database not in a restricted mode

SOLUTION

Open PDB in restricted mode, and close all other instances in RAC environment.

SQL> alter pluggable database OLDNAME open restricted;

Pluggable database altered.

SQL>alter pluggable database rename global_name to NEWNAME;

Pluggable database altered

SQL>select * from global_name;

GLOBAL_NAME
--------------------------------------------------
NEWNAME

ORA-02085: database link DB_LINK connects to SOURCE_DB

After successfully created the database link called “DB_LINK”, the try to access remote table with the following errors:

SQL>  select  count(*) from remote_user.table_name@db_link;
 select  count(*) from REMOTE_USER.TABLE_NAME@DB_LINK                                                         *
ERROR at line 1:
ORA-02085: database link DB_LINK connects to SOURCE_DB
 $ oerr ora 02085
02085, 00000, "database link %s connects to %s"
// *Cause: a database link connected to a database with a different name.
//  The connection is rejected.
// *Action: create a database link with the same name as the database it
//  connects to, or set global_names=false.
//
SQL>  show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
global_names                         boolean     TRUE

SOLUTION

1)Reset parameter global_names to FALSE, and recreate the database link again with any name you want.

SQL> alter system set global_names=false;

SQL> Create public database link DB_link connect to ....

SQL>  select  count(*) from remote_user.table_name@db_link;

  COUNT(*)
----------
      1000

OR

2) Create database link with the same name as source database / PDB GLOBAL_NAME.

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------
SOURCE_DB

SQL> Create public database link SOURCE_DB connect to ....

SQL>  select  count(*) from remote_user.table_name@source_db;

  COUNT(*)
----------
      1000

For connecting to PDB, you need be in the PDB to get right GLOBAL_NAME:

SQL> show con_name;

CON_NAME
------------------------------
SOURCEDBPDB

SQL>  select * from global_name;

GLOBAL_NAME
--------------------------------------------
SOURCEDBPDB

SQL>Create public database link SOURCEDBPDB connect to ....

SQL>  select  count(*) from remote_user.table_name@sourcedbpdb;

  COUNT(*)
----------
      1000

PMON (ospid: 123456): terminating the instance due to error 500

Oracle instance crashes with the following errors, sometimes the instance fails to start up with the same messages:

...
..
.
Errors in file /u02/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_lreg_104781.trc  (incident=287152) (PDBNAME=CDB$ROOT):
ORA-07445: exception encountered: core dump [nscall()+911] [SIGSEGV] [ADDR:0x186] [PC:0x5C6F35F] [Address not mapped to object] []
Incident details in: /u02/app/oracle/diag/rdbms/ractest/RACTEST1/incident/incdir_287152/RACTEST1_lreg_104781_i287152.trc
...
..
.
Tue Aug 24 14:58:01 2021
Instance Critical Process (pid: 29, ospid: 104781, LREG) died unexpectedly
PMON (ospid: 123456): terminating the instance due to error 500
Tue Aug 24 14:58:01 2021
System state dump requested by (instance=1, osid=123456(PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u02/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_diag_104564_20210824145801.trc
Tue Aug 24 14:58:01 2021
Dumping diagnostic data in directory=[cdmp_20210824145801], requested by (instance=1, osid=123456(PMON)), summary=[abnormal instance termination].
Tue Aug 24 14:58:01 2021
Instance terminated by PMON, pid = 123456

The Call Stack Trace in the associated incident trace file ( RACTEST1_lreg_104781_i287152.trc ) shows :

nscall <- nsgrcOpen <- nsgrDo <- nsgrrg_Register <- kmmlrl <- ksucln <- ksbrdp <- opirip
  <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main
nscall()+911         signal   __sighandler()       2500000153 0F2408F7A
                                                   7F2275499F90 0F2408F7A ?
                                                   000000000 ? 000000000 ?
nsgrcOpen()+512      call     nscall()             2500000153 ? 7F2275499E30 ?
                                                   7F2275499F78 7F227549A0B0
                                                   7F227549A0C8 7F2275499F90
nsgrDo()+81          call     nsgrcOpen()          7F22753F2F30 7F2275499D78
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
nsgrrg_Register()+2  call     nsgrDo()             7F2275499D78 7F2275499D78 ?
70                                                 7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
kmmlrl()+12395       call     nsgrrg_Register()    7F22753F2F30 000000008
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
kmlmain()+43         call     kmmlrl()             7F22753F2F30 ? 000000008 ?
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
ksbrdp()+1072        call     kmlmain()            06003EDA0 000000008 ?
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
opirip()+1488        call     ksbrdp()             06003EDA0 ? 000000008 ?
                                                   7F2275499F78 ? 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
opidrv()+616         call     opirip()             000000032 000000004
                                                   7FFDA41BB698 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
sou2o()+145          call     opidrv()             000000032 000000004
                                                   7FFDA41BB698 7F227549A0B0 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
opimai_real()+270    call     sou2o()              7FFDA41BB670 000000032
                                                   000000004 7FFDA41BB698
                                                   7F227549A0C8 ? 7F2275499F90 ?
ssthrdmain()+412     call     opimai_real()        000000000 7FFDA41BB980
                                                   000000004 ? 7FFDA41BB698 ?
                                                   7F227549A0C8 ? 7F2275499F90 ?
main()+236           call     ssthrdmain()         000000000 000000003

CAUSE and SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Snapshot Too Old Error

The ORA-1555 or Snapshot Too Old errors are reported when the read consistent images are unavailable in the Undo tablespace. This happens when there is not enough space in the Undo tablespace to retain the undo records for the long running queries.

Snapshot Too Old Error detected: SQL ID 3jgzkm92q02i8, Snapshot SCN 0x074c.3280364e, Recent SCN 0x074c.33f38d0c, Undo Tablespace UNDOTBS1, Current Undo Retention 14542.

INVESTIGATION and SOLUTION

Tune the SQL

Find sql from v$sql or dba_hist_sqltext, and tune it accordingly

SQL> select SQL_TEXT, SQL_FULLTEXT from v$sql where sql_Id='3jgzkm92q02i8';

SQL>  select SQL_TEXT from DBA_HIST_SQLTEXT where sql_Id='3jgzkm92q02i8';

Check and Increase UNDO_RETENTION

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     1000

SQL> select max(maxquerylen) from v$UNDOSTAT;

MAX(MAXQUERYLEN)
----------------
            1961

SQL> select max(maxquerylen) from DBA_HIST_UNDOSTAT;

MAX(MAXQUERYLEN)
----------------
           22330

Set UNDO_RETENTION to the max of the above values, and make sure the UNDO tablespace has big enough space.

SQL> alter system set UNDO_RETENTION=23000;

UNDO Tablespace Size Advisor

To Get The Output using the historical information in memory :

SQL> SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;

REQUIRED_UNDO_SIZE
-----------------------------------------------------------------------------------------------------------
The Required undo tablespace size using Statistics In Memory is 2048 MB

To Get The Output using Begin/End AWR snapshot id :

SQL> SELECT 'The Required Undo tablespace size During This AWR snaps Range is ' || dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) || ' MB' required_undo_size FROM dual;

REQUIRED_UNDO_SIZE
------------------------------------------------------------------------------------------------------------
The Required Undo tablespace size During This AWR snaps Range is 2048 MB

Monitor UNDO Tablespace

The V$UNDOSTAT view holds undo statistics for 10-minute intervals, which represents statistics across instances, thus each begins time, end time, and statistics value will be a unique interval per instance.

Column nameMeaning
BEGIN_TIMEThe beginning time for this interval check
END_TIMEThe ending time for this interval check
UNDOTSNThe undo tablespace number
UNDOBLKSThe total number undo blocks consumed during the time interval
TXNCOUNTThe total number of transactions during the interval
MAXQUERYLENThe maximum duration of a query within the interval
MAXCONCURRENCYThe highest number of transactions during the interval
UNXPSTEALCNTThe number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
UNXPBLKRELCNTThe number of unexpired blocks removed from undo segments to be used by other transactions
UNXPBLKREUCNTThe number of unexpired undo blocks reused by transactions
EXPSTEALCNTThe number of attempts when expired extents were stolen from other undo segments to satisfy a space request
EXPBLKRELCNTThe number of expired extents stolen from other undo segments to satisfy a space request
EXPBLKREUCNTThe number of expired undo blocks reused within the same undo segments
SSOLDERRCNTThe number of ORA-1555 errors that occurred during the interval
NOSPACEERRCNTThe number of Out-of-Space errors