General
cancel
Showing results for 
Search instead for 
Did you mean: 

Creating duplicate database from a full export

kenny chia
Regular Advisor

Creating duplicate database from a full export

Hi
I am attempting to duplicate an Oracle8i database with all the user data.

I have the database creation scripts available, but as changes to the database are made over the years, they are not reflected in the creation scripts.

This is what i did
1. Perform full export from the source database
2. Copy all the oracle files (excluding the datafiles) to the target system
3. sqlplus internal into the database
4. Do a startup nomount
5. Run the CREATE DATABASE command, creating the system and redo files
4. Tried to import the file but encountered the following error

IMP-00058: ORACLE error 1033 encountered
ORA-01033: ORACLE initialization or shutdown in progress

It seems that import will only work with a mounted database

5. Tried to mount the database but the following error occurred
ORA-01403: no data found

What is the correct way to duplicate the database? Must I run all the user and tablespace creation script?


All Your Bases Are Belong To Us!
12 REPLIES
Arunvijai_4
Honored Contributor

Re: Creating duplicate database from a full export

Please check this link, Duplicate a Database Using RMAN
http://www.oracle-base.com/articles/9i/DuplicateDatabaseUsingRMAN9i.php
http://www.orafaq.com/faqiexp.htm

-Arun
"A ship in the harbor is safe, but that is not what ships are built for"
Yogeeraj_1
Honored Contributor

Re: Creating duplicate database from a full export

hi,

did you run the catalog.sql and catproc.sql?

the error is related to this!

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Creating duplicate database from a full export

hi,

you could have also:

a. By copying the datafiles
============================
1. Backup controlfile to trace
2. Shutdown database
3. Copy the datafiles
4. make necessary adjustments to the controlfile
5. Run the controlfile script


b. Use RMAN to duplicate the database
========================================
This is much reliable and easy technique though you have to understand how it all works!

hope this helps too!

kind regads
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
kenny chia
Regular Advisor

Re: Creating duplicate database from a full export

Hi Yogeeraj
I tried running catalog.sql and catproc.sql but it still gives me the ORA-01403: no data found error when I start the database
So it seems that a full database export is not very useful to create a database, in what situation will a full database export be useful?

I think I will stop the database and copy the datafiles, The target system has exactly the same directory structure, so there is no need to backup and edit the control file script
All Your Bases Are Belong To Us!
Yogeeraj_1
Honored Contributor

Re: Creating duplicate database from a full export

hi,

You have to explicity create the database -- that will create a database with one tablespace -- system. IMP will create the rest. IMP does not CREATE a database -- you must. But once you have, it'll fill it up.

Normally, you usually full import into a minimally configured database.

Full import (is very slow on todays sized databases) and it better to use user level exp/imports in parallel.

Shutting down the database and moving around the datafiles is indeed a more easy solution (it however implies a downtime..

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: Creating duplicate database from a full export

Hi

you did not specify if source and target are same oracle version and same platform (OS).
If this is the case and have a cold backup you could just clone the database, this would be so much quicker.
If you cannot shut down the database for a cold backup, RMAN is probably the tool you are looking for.

If you want to change platform, you may wan to check this Metalink doc Note:132904.1 "Compatibility Matrix for Export & Import Between Different Oracle Versions"

Regards
Jean-Luc
fiat lux
kenny chia
Regular Advisor

Re: Creating duplicate database from a full export

Hi
I found out the cause of my problems, before I create the database, I should have deleted the oracle password file and recreate it

anyway, here is my brief method of recreating a database from a full export file in another machine

1. Perform a database export with full=y
2. Recreate the oracle password file
3. Create the database using the create database command
4. Run catalog.sql
5. Run catproc.sql
6. Shutdown and open the database
7. Perform the full import

The original oracle password file is from the server I wanted to duplicate. But why must it be recreated before the create database command?
All Your Bases Are Belong To Us!
Yogeeraj_1
Honored Contributor

Re: Creating duplicate database from a full export

hi,

The password is normally created using the following command:
orapwd file=orapw password= entries=

The file name IS "orapw" and the SID for the database in which the password file is being created has to be used.

The password MUST be the password for sys.

questions:
1. did you create the database using the same SID?
2. Did you have the same password for sys?

revert

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
kenny chia
Regular Advisor

Re: Creating duplicate database from a full export

The SID and password is the same, its just that if I did not recreate the oracle password before database creation, the database creation will fail.

The fail message is
ORA-01991: invalid password file '...'
All Your Bases Are Belong To Us!
Yogeeraj_1
Honored Contributor

Re: Creating duplicate database from a full export

hi again,

what is the value for remote_login_passwordfile in your init.ora?

Normally, you should have set the following parameter to none in init.ora

remote_login_passwordfile = none

and then create database.

Then after successful creation of database and other objects then shutdown.

Now create password file using orapwd utility and change
remote_login_passwordfile = exclusive
and start database.


You could also have set remote_login_passwordfile=Shared before and created the password file before creating the database.

kind regards
yogeeraj

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

Re: Creating duplicate database from a full export

For my database
remote_login_passwordfile = exclusive

Ok I take note
All Your Bases Are Belong To Us!
kenny chia
Regular Advisor

Re: Creating duplicate database from a full export

Thread closed
All Your Bases Are Belong To Us!