best way to "update" a DB with import

[a128]

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!

[a128]

BTW, I forgot to mention: the goal is the keep both databases identical after the import.

Thanks!

[patel_dil]

That should work fine. Make sure that no objects in Database A is accessed during this import process process.

[ndisang]

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.

[soniaarora]

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.

[patel_dil]

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.

[soniaarora]

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

[patel_dil]

Thanks Sonia.

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

[soniaarora]

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.

[patel_dil]

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