Showing results for 
Search instead for 
Did you mean: 

Creating Database and Importing from Backup

Go to solution
Frequent Advisor

Creating Database and Importing from Backup

We are about to install Oracle 7.3.4 on HPUX. We have a server currently running a database , and we want the new server to run the same database. On the new server, the location of the logfiles and datafiles of the database and datafiles of tablespaces will be different. Other than that , the database can be exactly as on the old server. We have a full daily export backup of the database on the old server .I would like you to help me decide whether what I am thinking is right :
This is what I am planning to do :
1) Install Oracle on new machine
2) Manually create database specifying the new locations of the logfiles and datafiles.
3) Manually create the tablespaces specifying
new locations of datafiles.
4) Import from the daily backup of old server, with ignore=N , so that the database and tablespaces (which already exist) won't be recreated from the details on backup, but will import all other details like tables, indexes etc from backup.
I hope that by doing this all the details from backup(including users, roles etc) will be restored as needed.
Another question is, when I'm finally happy that the database is okay, can I create database , tablespaces manually again and import from latest backup. I hope when I issue the create database, all details of the database (incl users, roles etc) will be dropped and recreated again.
Any replies with any existing notes etc.. would be much appreciated. Many Thanks
Manish Srivastava
Trusted Contributor

Re: Creating Database and Importing from Backup


Your procedure looks to be ok. You can refer to the FAQ for more details:

Frequent Advisor

Re: Creating Database and Importing from Backup

I think I should use ignore=Y , if I don't want import to recreate the database anmd tablespaces ?
Manish Srivastava
Trusted Contributor

Re: Creating Database and Importing from Backup


Yes you do need to have IGNORE=Y if you have the database else it will crib as objects are already present.

Jean-Luc Oudart
Honored Contributor

Re: Creating Database and Importing from Backup

If you really want to have a identical image of the source database I would suggest to restore onto new server and clone the database.

As mentioned in one of your previous threads you can change the file locations.

I would not use exp/imp unless I want to restrict the DB refresh to a speific schema and/or specific tables.
If you go the exp/imp route ensure you have the NLS_LANG set correctly on source and target.

fiat lux
Honored Contributor

Re: Creating Database and Importing from Backup


The main steps are:
1. Perform a full export from the source database as a DBA user.
For example:
exp system/manager full=y file=expdat.dmp

2. Move the dump file to the target database server via ftp. (Note: remember to change to BINARY mode to avoid corrupting the file. To configure ftp for binary mode, enter 'binary' at the ftp prompt)

3. Create a database on the target server.

4. Before performing the import, you will need to precreate your tablespaces. This is necessary since the import will want to create the corresponding datafiles in the same file structure as was at the source database. Since your file structure will be different on the target database, precreating the tablespaces will allow you to specify a file structure that will work.

5. Set your environment variable â NLS_LANG

6. Perform a full import with the parameter IGNORE=Y into the target database as a DBA user.
imp system/manager full=y ignore=y file=expdat.dmp

Using IGNORE=Y will tell Oracle to ignore any creation errors during the import, allowing the import to complete.

If you want to test it first then do it again later. it is advisable that you script everything so that you can change them if need be.

hope this help!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Frequent Advisor

Re: Creating Database and Importing from Backup

Many Thanks Yogee for the detailed response
R. Allan Hicks
Trusted Contributor

Re: Creating Database and Importing from Backup

Something to consider from an amount of work standpoint.....

If you can

1.) Do a cold backup using your favorite UNIX backup utility.
2.) Restore the datafile to their new homes.
3.) Do a startup mount
4.) Issure 'alter datbase rename file 'oldlocation' to 'newlocation';

example: alter database rename file '/u1/orcl/foo.dbf' to '/u6/orcl/foo.dbf';

5.) alter database open;

The rename does not rename the file, but redefines the definitions in the the data dictionary. So, if you have only a few datafiles, you may save some work with the rename. The downside is that your database is unavailable during the cold backup.
"Only he who attempts the absurd is capable of achieving the impossible