Creating duplicate database from a full export

[kenny chia]

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?

[Arunvijai_4]

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

[Yogeeraj_1]

hi,

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

the error is related to this!

hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

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

[kenny chia]

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

[Yogeeraj_1]

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

[Jean-Luc Oudart]

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

[kenny chia]

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?

[Yogeeraj_1]

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

[kenny chia]

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 '...'