best way to "update" a DB with import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: best way to "update" a DB with import

  1. #1
    Join Date
    Apr 2001
    Posts
    257
    Hi,

    I have two DB servers and they originally contain identical DB. However, since people update database B and the export dump from database B is done regularly, what is the safest way to import the dump file into database A? All the objects and data are in a single user's schema.

    Should I drop the user, recreate it, and then import from the dump file? Will I be missiing something if I do it this way?

    Thanks!

  2. #2
    Join Date
    Apr 2001
    Posts
    257
    BTW, I forgot to mention: the goal is the keep both databases identical after the import.

    Thanks!

  3. #3
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    That should work fine. Make sure that no objects in Database A is accessed during this import process process.
    -- Dilip

  4. #4
    Join Date
    Sep 2001
    Posts
    200
    Make sure users are not accessing db A while you are taking the dump file. Or you do your export with CONSISTENT=Y. but you would need a big temp space.
    Life is what is happening today while you were planning tomorrow.

  5. #5
    Join Date
    Jul 2002
    Posts
    132
    U dont have to drop the user and recreate it. Just drop the objects belonging to the schema. Then u can import using the fromuser,touser option.

    I wud suggest that if u want identical databases, it is better u go for managed standby databases.

  6. #6
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    And what would be the best way to drop the schema objects? I drop the user with cascade option only to delete all objects in schema. if someone has got better way, plz share the thoughts.
    -- Dilip

  7. #7
    Join Date
    Jul 2002
    Posts
    132
    Use this script to drop objects belonging to the user

    spool drop.sql
    select 'drop ' || object_type || ' ' || owner || '.' || object_name || ';'
    from dba_objects
    where owner = upper('&1')
    and object_type not in ('PACKAGE BODY','INDEX')
    order by object_type, object_name
    /
    spool off

    After the objects are dropped, just coalesce the tablespaces belonging to the user. Next u can import the objects with fromuser,touser option

  8. #8
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Thanks Sonia.

    But I still think Drop user CASCADE is a better option.
    -- Dilip

  9. #9
    Join Date
    Jul 2002
    Posts
    132
    Dilip,

    Whatever it is, u need to run a script before u start the import-whether it is dropping the objects in a schema or dropping the user and then running the script for the user creation- it is matter of which way u like.

  10. #10
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Sonia,
    we dont have to run any scripts beforehand to delete the objects, IF u drop the user with CASCADE option. The objects which your scripts will delete will be deleted by this command. Or am I missing something?

    Thanks Again
    -- Dilip

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