Re: Core Dump during Import on 8.1.6 (related to duck pecking)

From: <Cherie_Machler_at_gelco.com>
Date: Tue, 12 Dec 2000 09:28:38 -0600
Message-Id: <10708.124324@fatcity.com>

Jay,

This is an error that we are getting here on 8.1.5. Following is cut and pasted from Metalink based on the error number we are getting on an 8.1.5 export. Perhaps it applies to your situation:

Note:100405.1
Subject: ALERT: 8.1.5 Export may Create a CORRUPT Export File Type: ALERT
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 29-FEB-2000
Last Revision Date: 20-JUL-2000
Language: USAENG

Bug:882543 Export may create a Corrupt Export file without any warnings

Versions Affected

This problem:

        Affects Oracle release 8.1.5.0 and 8.1.5.1
        Is fixed in Oracle 8.1.6.0 onwards
        Does not affect Oracle 8.0 releases

Platforms Affected

GENERIC - affects all platforms

Description

Export of an object table can create a corrupt export file without any warnings or errors if the objects being exported have been previously imported into the current schema. An example is included in a later section of this alert.

There is also a variation of this bug which can cause EXP-56 and OCI-22303 errors during export.

Likelihood of Occurrence

In order to hit the corrupt export problem you must:

Be using user defined types (ADTs) in the table definition
Have exported that table (and hence type definition)
Have dropped the owning user
Imported the export back into the same database
(either as the same user or a new user)
Be on 8.1.5 or using 8.1.5 EXPORT views

In order to hit the EXP-56 variation of the problem the original user must not have been dropped (as in step 'c') and the export file has to have been imported into another schema.

What are the chances of hitting this problem ?

Connect as a SYSDBA user (or SYS) and run this statement:

        SELECT  o.obj#               OBJECT_ID,
                ou.name||'.'||o.name OBJECT,
                nvl(u.name,'*dropped*') OTHER_OWNER
          FROM obj$ o, obj$ p, user$ u, user$ ou
         WHERE o.oid$ is not null
           and o.owner#=ou.user#
           and o.oid$=p.oid$
           and p.owner#=u.user#(+)
           and o.obj#!=p.obj#
           and o.type#!=10
        ;

If this reports any rows with OTHER_OWNER of "*dropped*" then any tables using the listed object type can generate a corrupt export file when exported. Tables directly referencing a type can be found from DBA_DEPENDENCIES.
eg:

         SELECT * FROM dba_dependencies
          WHERE referenced_owner = '<<Object_Owner>>'
            AND referenced_name  = '<<Object_Name>>'
         ;

If this reports rows with a named "OTHER_OWNER" then tables using that type may error with EXP-56 when exported.

Possible Symptoms

When the original owning user has been dropped then export succeeds with no errors / warnings but produces a corrupt export file. On subsequent import of a corrupt export file possible symptoms include:

Import hangs on import of the affected object table
(this occurs even on a SHOW=Y import)
Import may error with IMP-00009 "abnormal end of export file" reported after the affected object table.

The variation of this problem may cause export to fail with errors such as:

        EXP-00056: ORACLE error 22303 encountered
        OCI-22303: type "SSS"."XXXTYPE" not found

Example Sequence of events leading to a Corrupt Export file

The steps below illustrate a common sequence of events which lead to a corrupt export file:

        rem Create a user (called SSS) for this example
        rem
          connect / as sysdba
          create user SSS identified by SSS ;
          grant connect, resource, dba to sss;

        rem Connect and create an object table
        rem
          connect sss/sss
          create type XXXtype AS VARRAY(10) of VARCHAR2(6);
          create table XXXTAB ( A XXXTYPE );

        Export this table:
          exp sss/sss file=xxxtab.dmp tables=xxxtab

        rem Drop and recreate the user
        rem The created user may have the same or a different name
        rem
          connect / as sysdba
          Drop user sss cascade;
          create user SSS identified by SSS ;
          grant connect, resource, dba to sss;

        Import the export file containing the object table:
          imp sss/sss file=xxxtab.dmp full=y

From this point onwards any export of the table "SSS.XXXTAB" will write corrupt column count information to the export file. eg: The following command would now generate a corrupt export file:

exp sss/sss file=bad.dmp tables=xxxtab

Subsequent import of such an export file may hang or error.

Workaround

When dropping users do NOT use DROP USER CASCADE but instead:
Drop all of the users objects
Use DROP USER with no cascade option This will cause the problem 'not-exists' dictionary entries to be deleted when the user is dropped.

References

  Base bug for this alert                       [BUG:882543]
  "NOT EXISTS" objects not cleaned up           [BUG:841399]

EXP-56 possible after fix for 882543 applied [BUG:1218370]

                                                      Oracle Support Services

Cherie

---------------------- Forwarded by Cherie Machler/GELCO on 12/12/2000 10:23 AM
---------------------------

"Jay Hostetter" <jhostetter_at_decommunications.com> on 12/12/2000 08:27:05 AM

To: Cherie Machler/GELCO_at_GELCO
cc:

Subject: Re: Core Dump during Import on 8.1.6 (related to duck pecking)

Cherie,

Thanks, I'll check it out. I couldn't find anything relevant, but I'll look again.
Sorry, I've never been to Minneapolis.

Jay

>>> <Cherie_Machler_at_gelco.com> 12/12/00 09:25AM >>> Jay,

I believe this is a known bug for export/import on 8.1.5 and 8.1.6. If I remember correctly, it is related
to using table names. You should be able to find it on Metalink. I'll look around and
see if I can find the bug number.

On a personal note, did you ever work on assignment at Deluxe Corp. in Minneapolis?

Cherie

---------------------- Forwarded by Cherie Machler/GELCO on 12/12/2000 09:19 AM
---------------------------

"Jay Hostetter" <jhostetter_at_decommunications.com> on 12/12/2000 07:50:32 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Cherie Machler/GELCO)

Subject: Core Dump during Import on 8.1.6 (related to duck pecking)

  Some consultants have arrived with an export file from a 7.3.4 database. We must import this file into an 8.1.6.2.0 database (identical to the scenario that Dennis Taylor posted). Every time I import this file, I get a "Memory Fault (coredump)" error. There is no trace file, no alert log entry, just a core file.
  I am doing a full import. I have tried creating the schema owner and importing using TOUSER, but I still get the coredump. I have checked kernel parameters (this is a new system). I have relinked Oracle. I have increased the SGA and tablespaces, but to no avail. We installed 8.0.5 on the same machine, and imported the file with no problem. We exported it from 8.0.5 and tried the full import into 8.1.6, but we still get the coredump.   I did manage to import a different export file that the consultants brought with them (from 8.0.5) into a seperated 8.1.6 database.   I have logged a TAR, but thought I would run it by the real experts for some suggestions.
  This is a new install of 8.1.6, patched to 8.1.6.2 on a cluster running Tru64 version 5.1.

Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  INET: jhostetter_at_decommunications.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

Received on Tue Dec 12 2000 - 09:28:38 CST