Export/Import to recover database

[yodaa]

Hi,

I'm trying to recover a database by importing a full export dump. This does not work as I wish.

My procedure for doing this is:
1. Create dump directory "dmp_dir" in SQLPLUS
2. Export the database using:
expdp user/passwd@dbname full=yes directory=dmp_dir dumpfile=dmp.dmp logfile=exp_log.log
3. Delete the database from the DBCA
4. Create a new in the DBCA based on the "General Purpose" template
6. Start the dbconsole with:
emctl start dbconsole
7. Create dump directory "dmp_dir" in SQLPLUS
8. Full import of dmp.dmp with:
impdp user/passwd@dbname full=yes directory=dump_dir dumpfile=dmp.dmp logfile=inp_log.log

The import complains and says that "......already exists". This applies for tables, trigger and other db objects.

What am I doing wrong?

Thanks,
yooda

 

[dbtoo2001]

You should not use Oracle import and export utilities as the sole method of backing up your data."

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/backrec.htm#i1011713

http://asktom.oracle.com/pls/ask/f?...0_P8_DISPLAYID,F4950_P8_CRITERIA:886028302502

.

 

[yodaa]

dbtoo2001,

We have backups running for the machine that the database resides on. But my thing is that sometime I do some SQL work on the database which requires me to take a dump to be able to restore data if I mess something up. That's why I want to import the dump to restore the data.

If something goes terribly wrong with the database/machine then the backups will be restored by responsible personnel.

How can I import the dump without bumping into the "already exists" error?

Thanks,
yodaa

 

[alexhu]

Give us a clue as to 'what' already exists

i.e a log excerpt of the import

Alex

 

[yodaa]

alexhu,

I've figured why this isn't working. I'm approching this in a wrong way, I think!

My question is:
How may I make a full export of all data in a schema (yodaa) and then import it again in order to overwrite the existing data for that schema?

I already procude a dmp files with

./exp usr/pswd@db file=/db_dumps/dmp.dmp owner=yodaa statistics=none ROWS=Y INDEXES=N GRANTS=N CONSTRAINTS=N BUFFER=90000

How can I import this export in order to restore data?

Thanks,
yodaa

 

[SantaMufasa]

Yodaa,

To restore a schema from an export dump file, you must "empty out" the target schema so that just the schema's name remains. Then execute the schema import.

To empty out the target schema, you can either drop the Oracle user then recreate the user or use the following script to drop all the schema objects individually:

set feedback off
set pagesize 0
spool temp.sql
select 'DROP TABLE '||table_name||' cascade constraints;'
from user_tables
order by table_name
/
select 'DROP '||object_type||' '||object_name||';'
from user_objects 
where object_type not in ('TABLE','INDEX','TRIGGER')
order by object_type, object_name
/
spool off
set pagesize 35
set feedback on
prompt
prompt To actually drop objects from this schema, '@temp.sql' at the SQL> prompt
prompt

Let us know that everything worked for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.