DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: uncharacteristic exp/imp behaviour

  1. #1
    Join Date
    Sep 2001
    Makati, Philippines

    uncharacteristic exp/imp behaviour

    8.1.7 db
    solaris 8

    I just do a semi-migration from one machine to another(same solaris).
    The objective was migrate a sub-system from a production to another server.
    3 schemas-involved, with selected tables only with data to be migrated. But the whole structures for all the 3 schema should be present also.

    The steps that I done:
    source server
    1. create an export file as system for all of the 3 schemas without data, and CONSTRAINT=Y, INDEX=Y, GRANTS=Y
    2. create 3 export files for each SCHEMA as schema owner with ROWS=Y
    3. create an import with INDEXFILE set for scripting of indexes(for full back purposes).
    4. create scripts for extraction of table privileges, roles, enable/disable triggers, enable/disable constraints, public synonyms.

    destination server
    4. create the users to own the schema, tablespaces, roles
    5. import the export file for all 3 schema without data.
    6. disable triggers, constraints.
    7. import an export file for each schema with data.
    8. review logs, and make sure all data are imported.
    -- there were MLOG$* tables with errors but irrelevant since
    MATERIALIZED VIEW is not necessary on the destination site.
    9. after other wrap-up thing has been done

    one table which was not part of the listed table to contain data during importing got an error when we try to insert data even manually.

    Here is the detail of the error:

    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist

    What surprise me is that I use the user as the owner itself, and it even have a public synonym. I remember I drop all MLOG$ objects before we do the insert, and that's all that is close that can relate to internal dictionary tables which the nature of ORA-604 says.

    But when I try to re-create the table, extracted from the its own schema itself including constraints, indexes, grants then it allows
    now to insert data.

    I'm just being bugged by this scenario, though it is resolve now, I just don't have any idea what have happened.

    Any thought guys or experience that you can share?


  2. #2
    Join Date
    Sep 2002
    1. shutdown database
    2. copy all files to new server with same directory structure
    3. startup

    so much easier

    or use rman to clone

  3. #3
    Join Date
    Sep 2001
    Makati, Philippines
    thanks davey,
    But I am not duplicating the whole database. And it is imposible because my target server is way much short in terms of resources(disk space).
    Only selected schema, and selected tables from that schemas to populate data.

    BTW, i didn't issue a drop materialize view log command instead i just drop the MLOG$*, that is probably the reason why some/all related internal object(related to MV logs) where not flash.

  4. #4
    Join Date
    Nov 2000
    Pittsburgh, PA
    Have you looked into transportable tablespaces?

  5. #5
    Join Date
    Jan 2001
    How big are the tables involved?
    I remember when this place was cool.

  6. #6
    Join Date
    Sep 2001
    Makati, Philippines
    Originally posted by gandolf989
    Have you looked into transportable tablespaces?

    I have not, but by first look alone with naming conventions of segment_name I'm sure segments were not organized in separate tablespaces(not self-contained).

    Mr. Hanky,
    just about 20G. For now, I will be preparing scripts( and testing later on), that should immediately update the data coming from prod to test area.

    Sorry for the delayed response, just got busy with other things.

    Anyway, does anyone of you have a site that can refer to me regarding ADAPTEC U320 SCSI driver for Linux ES 2.1(ibm xSeries 206)? Just can't find material even n website of ADAPTEC, IBM, and Linux.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width