Oracle export/import

[csunix]

I want to copy my live database to a training database.
What export/import parameters do I use. The schema owner is the same for both databases and they are both the same size.

 

[KenCunningham]

Why are you using exp/imp? Could you not just copy everything (except control files) from live into training (renaming as appropriate) and then start up using an edited copy of a alter database backup controlfile to trace command in your live database. There are further instructions here:

http://www.jlcomp.demon.co.uk/faq/db_move.html

if required. Hope this helps.

 

[SantaMufasa]

CS,

I sense that your use of the term, database may be ambiguous here. When you say database, do you mean the entire Oracle installation's collection of all users and the objects that they own (i.e., Oracle's definition for database) [in which case, Ken's suggestion is right on and the one you want to use] or do you mean a single user's/schema's own objects (i.e., all other db vendors' definition for database)?

If your meaning is the latter one (i.e, you wish to clone the "live" schema within Oracle database instance "X" to the "training" schema, also residing in the Oracle database instance "X"), then here are the steps (and commands) that I would use:

1. Create the "training" schema:

create user training identified by <password>
default tablespace <app-ts-name>
temporary tablespace <temp-ts-name>
quota unlimited on <app-ts-name>;
grant connect, resource to <training>;

2. Export the live schema:

exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=<dump-file-name> log=<log-file-name> statistics=none owner=live userid=live/<password>@<TNS alias for 'X' instance>

3. Import the dump file of the live schema into the newly created, empty training schema:

imp buffer=15000000 grants=y feedback=1000 fromuser=LIVE touser=TRAINING file=<dump-file-name> log=<log-file-name> userid=live/<password>@<TNS alias for 'X' instance>

The "imp" command, above, presumes that "live" has DBA priviliges to write to the "training" schema. If "live" hasn't those privileges, then just replace "userid=live/<password>@..." with some DBA user.

Also, in both the "exp" and "imp" commands, above, they should appear on a single command line from your o/s prompt.

Let us know if this is what you wanted.


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.

 

[HeathRamos]

the only thing I would add is that if there is a lot of data (over 2 gb) you will have to create multiple export files. You can use the filesize parameter along with listing the file names.

Example:

exp system/<password> fromuser=user1 touser=user2 filesize=1000m file=file1.dmp,file2.dmp,file3.dmp log=logfile.log

You could also use the show=y in the import as a test. It will not do anything but you can look at the log file it generates to see if there are any errors.

You could also pre-create tablespaces and use the ignore=y parameter so you won't get errors when it tries to create the tablespaces during the import.

Depending on what you are trying to do, I still think creating a new database is the easiest thing to do (meaning copying data files to new location, creating new control file with new SID, etc).

 

[HeathRamos]

it looks like one thing I said is wrong.

the import would only try to create tablespaces if you do a full database export and import

sorry about that