tags:

views:

54

answers:

2

Hi all,

I'm really newbie about Oracle backup operations. I'm really new in this world and I need to know how to backup a DB schema and restore it in another machine under another schema name.

I cannot afford any mistake since I'll be doing this in our customer site, an making a small mistake could be the last one

I don't want to sound offensive, but doing this in MySQL is really easy, like this:

in server one:

$mysqldump --user=user --password=password db_to_backup > bc_name.sql

-after transfering the sql script to another server

in server two:

mysql>create database db_to_restore;
$mysql --user=user --password=password db_to_restore < bc_name.sql

I need to do the same using Oracle, I read some documentation but I'm still unsure how to do it:

  • First: What's the equivalent of MySQL database in Oracle? tablespace?

  • Second: I think these are the steps to get a backup

    mkdir /opt/oracle/dumptmp/
    CREATE OR REPLACE DIRECTORY dumptmp AS '/opt/oracle/dumptmp/';
    expdp user/pass@tablespace directory=dumptmp dumpfile=dumptmp:full.dmp logfile=dumptmp:full.log full=y
    
  • Third: Then I move the file "full.dmp" to the other server, but I'm not sure how to restore my backup file full.dmp into a new tablespace with a different name to the one it the backup was gotten from:

    SQLPLUS>create tablespace ts_something;
    

then I'm not sure how to proceed from here. Please advice me how to do it using command line commands since my customer does not have GUI tools intalled.

Thanks a lot!

+1  A: 
vls
A: 

What you're explaining is not really a backup, more like schema export & import.

but doing this in MySQL is really easy.

So is doing the same in Oracle.

exp user/password@hoststring file=bc_name.dmp log=bc_name.log full=y statistics=none

& to import it,

imp new_user/new_password@hoststring file=bc_name.dmp log=bc_name.log full=y

If new_user doesn't exist then create the users create user new_user identified by new_password

and grant the rights grant create session,connect,imp_full_database to new_user

Sathya
Sathya
I would go for expdp and impdp rather than exp / imp for two reasons. First, exp / imp is rather depricated. Second, Oracle Datapump operations are a lot faster and you can better manage the export and import operations using set_parallel() function and better logging and other metatdata control features
Amit