Q
Problem solve Get help with specific problems with your technologies, process and projects.

Steps for copying a database or schema

Follow these steps to copy an entire database or a schema using Oracle's export and import utilities.

We have a Windows XP PC with Oracle8i running on it. I want to install the same database or schema on our Unix...

Server. I have taken a cold backup and an export dump. How do I do this?


Your cold backup of an Oracle database running on a Windows platform cannot be used to create a copy on the Unix platform. You will have to resort to Oracle's export and import utilities. (Note: Oracle10g does add cross platform Transportable Tablespaces and the Data Pump utilities, but you are running 8i so I won't discuss those here.) Oracle's export/import utilities are the tools of choice to move your data to a different platform.

Copying a database

If you want to copy the entire database, the basic steps are as follows:

1. Perform a full export:

exp userid=system/manager file=my_db.dmp log=my_db.log full=y
The FULL=Y parameter forces a full database export. I also make sure to log the output of my export utility to a log file. It becomes a handy reference.

2. FTP the dump file (and log file) to the destination server. Make sure that you FTP in binary mode!

3. Precreate a new database on the destination server.

4. Precreate the tablespaces on the new database to match the same tablespace names of your source database.

5. Import the full database:

imp userid=system/manager file=my_db.dmp log=imp.log full=y
Again, log the output to a file in case there are errors.

Copying a schema

If you only want to copy a schema to the new server, things are basically the same.

1. Perform a schema export:

exp userid=system/manager file=my_db.dmp log=my_db.log owner=SCOTT

The OWNER parameter exports a schema. In my example, that would be the SCOTT schema. Again, I also make sure to log the output of my export utility to a log file.

2. FTP the dump file (and log file) to the destination server. Make sure that you FTP in binary mode!

3. Precreate a new database on the destination server.

4. Precreate the tablespaces on the new database to match the same tablespace names of your source database.

5. Precreate the user in that database.

6. Import the dump file:

imp userid=system/manager file=my_db.dmp log=imp.log fromuser=SCOTT

Again, log the output to a file in case there are errors. The FROMUSER clause tells imp which schema to import. If you wish to change the objects to a new owner, use the TOUSER clause as well.

This was last published in April 2004

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close