Category: ORA- ERRORS

ORA-39083: Object type TYPE failed to create with error

Using “transform=OID:n” option to avoid ORA-39083 in DataPump.

SYMPTOMS

While importing an ORMB schema from default installation to a new schema for another new ORMB environment, ORA-39083 error occurs:

$impdp directory=DUMP_DIR dumpfile=ORMB.dmp                     \ REMAP_TABLESPACE=CISTS_01:CISADM_1 SCHEMAS=CISADM,CISUSER,      \
CISOPR,CISREAD REMAP_SCHEMA=CISADM:CISADM_1, CISUSER:CISUSER_1, \
CISOPR:CISOPR_1,CISREAD:CISREAD_1,CIS_USER:CIS_USER_1,          \
CIS_READ:CIS_READ_1 logfile=imp_cisadm_2_cisadm_1.log  
...
..
.
CREATE TYPE "CISADM"."TY_RMB_DELETE_BILL_TAB"   OID '40ACB20E52264074E053530F040AF48D' AS TABLE OF "CISADM"."TY_RMB_DELETE_BILL_DTL"
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal

SOLUTION

Add “transform=OID:n” as an impdp parameter.

$impdp directory=DUMP_DIR transform=OID:n dumpfile=ORMB.dmp       \
REMAP_TABLESPACE=CISTS_01:CISADM_1 SCHEMAS=CISADM,CISUSER,CISOPR, \
CISREAD REMAP_SCHEMA=CISADM:CISADM_1,CISUSER:CISUSER_1,           \
CISOPR:CISOPR_1, CISREAD:CISREAD_1,CIS_USER:CIS_USER_1,           \
CIS_READ:CIS_READ_1 logfile=imp_cisadm_2_cisadm_1.log

ORA-01623: log 31 is current log for instance RACTEST3 (thread 3) – cannot drop

Disable thread before dropping the online redo logfiles.

Three nodes RAC database was migrated to two nodes RAC database, while trying to drop online redo logs of thread 3,  the errors are as below:

SQL> alter database drop logfile group 31;
alter database drop logfile group 31
*
ERROR at line 1:
ORA-01623: log 31 is current log for instance RACTEST3 (thread 3) - cannot drop
ORA-00312: online log 31 thread 3: '+DATA1/ractest/onlinelog/group_31.409.924815767'
ORA-00312: online log 31 thread 3: '+FRA/ractest/onlinelog/group_31.262.924815769'

SOLUTION

Disable thread 3 by :

SQL> alter database disable thread 3;

Database altered.

Then drop the redo logs successfully.

SQL> alter database drop logfile group 31;

Database altered.

SQL> alter database drop logfile group 32;

Database altered.

SQL> alter database drop logfile group 33;
Database altered.

...
..
.

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

For 8k block size tablespace, maximum cluster chain block count is 65534. Otherwise bigger block size tablespace is recommended.

This article demonstrates how to reproduce  ORA-02475  error , and explains why it happens and how to resolve this issue.

  1. Create cluster tables and indexes.
SQL> CREATE CLUSTER emp_dept (deptno NUMBER(3)) TABLESPACE users;

Cluster created.

SQL> CREATE TABLE dept 
     (
        deptno NUMBER(3) PRIMARY KEY,
        deptname VARCHAR2(15) NOT NULL
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE TABLE emp 
     (
        empno NUMBER(10) PRIMARY KEY,
        ename VARCHAR2(15) NOT NULL,
       deptno NUMBER(3) REFERENCES dept
     )
     CLUSTER emp_dept (deptno);

Table created.

SQL> CREATE INDEX emp_dept_index
         ON CLUSTER emp_dept
         TABLESPACE users ;

Index created.

SQL> insert into dept values ( 1,'DEPT1 1');

1 row created.

SQL> insert into dept values (2,'DEPT1 2');

1 row created.

SQL> insert into dept values (3, 'DEPT1 3');

1 row created.

SQL> commit;

Commit complete.

2.  Insert records into cluster table EMP until gets ORA-02475 error.

SQL> set serveroutput on;
begin
    for i in 1 .. 1000000000
    loop
       begin
           insert into emp values (i,'emp '||i, 3);
           commit;
       exception
       when others
       then
           dbms_output.put_line( SQLERRM );
          exit;
       end;
   end loop;
end;
/

ORA-02475: maximum cluster chain block count of 65534 has been exceeded

PL/SQL procedure successfully completed.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-00845: MEMORY_TARGET not supported on this system

Make sure Oracle Databases are configured by using ASMM, AMM or HUGE PAGES correctly to avoid performance issues.

SYMPTOM

When start up a database, Database failed to be up with ORA-00845 error.

[oracle@racnode1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 15 21:08:17 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

The alert log has messages as below:

Sun Mar 15 21:08:31 2015
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 1073741824 bytes. /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 659394560 and used is 499130368 bytes. Ensure that the mount point is /dev/shm for this directory.

SOLUTION

Please confirm that ORACLE_HOME is set correctly. This error sometimes happens when ORACLE_HOME is not set correctly.

Make sure that the /dev/shm size is configured large enough, like in:

# mount -t tmpfs shmfs -o size=2g /dev/shm

In this case, the size of the shared memory device is configured to be 2GB.

In order to make the same change persistent across system reboots, add an entry for this to the /etc/fstab mount table:

shmfs /dev/shm tmpfs size=2g 0 0

NOTE:

  1. You should check with your System Administrator what the “best” size for /dev/shm is, based on what has been reported in the alert file.
  2. Also, many best practices now suggest disabling AMM especially in Exa* Engineered boxes that have larger memory capability, and can use Huge / Large pages.
    This is because AMM and Huge / Large pages are mutually exclusive and overall performance will be better using Huge pages.

ORA-00600 internal error code arguments [ktbdchk1: bad dscn] after DG switchover

A bug caused error ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn]

ISSUE

After a successful switchover, there are a couple of ORA-00600 errors in alert.log .

Sat Jan 31 22:32:29 2015
Errors in file /u01/app/oracle/diag/rdbms/testdb/TETSDB4/trace/TESTDB4_ora_119810.trc (incident=112769):
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/testdb/tetsDB4/incident/incdir_112769/TESTDB4_ora_119810_i112769.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

Open incident 112796 trace file, we see the current SQL is just a normal INSERT DML :

*** 2015-01-31 22:32:29.749
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=40c9ma8n8k17u) -----
insert into TEST_TABLE (EVENTSEQNO,
JOBSEQNO, EVENTTYPE, EVENTDESCRIPTION,
JOBSTATUS, EVENTDT, BUSINESSREFID, LOGROLE, EVENTLOGDT, TARGETCOMPNAME, TARGETOPNAME, TARGETENDPOINTREF, EVENTINFORMATION, MSGSIZE, RECORDCOUNT) values ( test_Seq.nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 )

Run DBVERIFY and generates logs as below:

Page 15793939 failed with check code 6056
itl[45] has higher commit scn(0x0001.ff6a9a92) than block scn (0x0001.061dd22a)
Page 15794110 failed with check code 6056
itl[9] has higher commit scn(0x0001.ff71d940) than block scn (0x0001.0656d6b2)
Page 15794200 failed with check code 6056
itl[20] has higher commit scn(0x0001.ff7137bc) than block scn (0x0001.0651314d)
Page 15795600 failed with check code 6056
itl[46] has higher commit scn(0x0001.ff757e17) than block scn (0x0001.066372e8)
Page 15795925 failed with check code 6056

DBVERIFY - Verification complete

Total Pages Examined : 20185088
Total Pages Processed (Data) : 8205902
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 4629967
Total Pages Failing (Index): 2912
Total Pages Processed (Lob) : 5585318
Total Pages Failing (Lob) : 0
Total Pages Processed (Other): 125612
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1638289
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)

Threr is no physical or logical blocks corruption recorded in alert.log or in view v$database_block_corruption or in table INVALID_ROWS after using dbverify and analyze table VALIDATE STRUCTURE cascade and RMAN validate check.

Use sql to identify the affected indexes, then rebuild those indexes and run DBVERIFY, the “Total Pages Failing (Index)” number drops a little bit but not disappearing. Here Page 15795925 is db block#.

SQL>SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id =8 and 15795925 between block_id AND block_id + blocks-1;
SQL> ! cat /u01/app/oracle/product/11.2.0/dbhome_3/rdbms/admin/utlvalid.sql
rem
Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM UTLVALID.SQL
Rem FUNCTION
Rem Creates the default table for storing the output of the
Rem analyze validate command on a partitioned table
Rem NOTES
Rem MODIFIED
Rem syeung 06/17/98 - add subpartition_name
Rem mmonajje 05/21/96 - Replace timestamp col name with analyze_timestamp
Rem sbasu 05/07/96 - Remove echo setting
Rem ssamu 01/09/96 - new file utlvalid.sql
Rem

create table INVALID_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
SQL> @/u01/app/oracle/product/11.2.0/dbhome_3/rdbms/admin/utlvalid.sql

Table created.
SQL> ANALYZE TABLE esb_tracker.esb_event VALIDATE STRUCTURE CASCADE online;

Table analyzed.

SQL> select * from INVALID_ROWS;

no rows selected
RMAN> validate check logical datafile 8;

Starting validate at 04-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=771 instance=TESTDB4 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00008 name=+DG1/testdb/datafile/testuser_tb.583.845563563
channel ORA_DISK_1: validation complete, elapsed time: 00:09:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 731827 25034752 4440865831
File Name: +DG1/testdb/datafile/testuser_tb.583.845563563
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 2477746
Index 0 1231472
Other 0 20593707

Finished validate at 04-FEB-15
SQL>Select * from v$database_block_corruption ;

no rows selected

Use the following query to confirm after rebuilding indexes, those impacted pages are returned to FREELIST. 

For example :

SQL>Select * 
      from dba_free_space 
     where file_id=8 and 15768657 between block_id AND block_id + blocks-1;

TABLESPACE_NAME  FILE_ID BLOCK_ID BYTES    BLOCKS RELATIVE_FNO
---------------- ------- -------- -------- ------ -----------
TESTUSER_TB     8        15761536 67108864  8192   1024

SOLUTION

To set hidden parameter “_ktb_debug_flags”=8 to make this bug fixing effective , though Oracle declared it has been fixed since 11.2.0.2 according to 1498717.1.

SQL>alter system set "_ktb_debug_flags"=8 scope=both sid='*';

Reference:

ORA-1555 / ORA-600 [ktbdchk1: bad dscn] ORA-600 [2663] in Physical Standby after switch-over (Doc ID 1498717.1)