Oracle 10g database migration on windows 2003

[Nuwank]

Hi,
I have a problem with migrating our ORACLE DEV database to UAT environment.

Scenario:

DEV server setup:

OS: windows 2003 (no service pack)
RDBMS: ORACLE 10g Enterprise (10.1.0.2) with spatial
Database name: ora_dev
Local C: drive for OS
Local H: drive with RAID 5 setup

UAT server setup:

OS: Windows 2003 (no service pack)
RDBMS: ORACLE 10g Enterprise (10.1.0.2) with spatial
Database name: ora_uat
Local C: drive for OS
Local H: drive with RAID 5
SAN connected I: and J: drives

I was able to successfully install oracle 10g on DEV server and get the developers to work on it. A few new table spaces were created by developers on the DEV server.

I need to migrate, the ora_dev database to the UAT database (ora_uat)

What is the easiest method to migrate the complete ora_dev database to UAT?
Or should I only migrate the newly created table spaces to the ora_uat database?

If so how can I do this?
I had problems having different database names in the DEV and UAT environments.

Thanks in advanced

Regards

NuwanK

[SANJAY_G]

You can either clone or do a full export/import. I personally prefer clone. It is much easier.
HTH

[Nuwank]

Thanks for the reply Sanjay..

Cloning didn't work.

the UAT server build is different to the DEV server as i explained.

on the DEV server, the complete oracle installation is on one local drive H:

but on UAT the oracle ora_uat database + control files + backup + archive logs are on J: drive (SAN),
the oracle home + Flash recovery are on I: drive (SAN)

i am new to RMAN. i've tried the RMAN doco recovery procedure. But it still dindn't work.

** the UAT oracle build is a new installation. ie: it doesn't contain any user created table spaces or schema.

what i want to do is actually some how backup only the user created table spaces and restore them to the new ORA_UAT database on the UAT server.

tried to copy the .DBF file and recover them on the UAT. no luck with it. :(

any suggestions are welcome. thanks.

[davey23uk]

transportable tablespaces

[adewri]

You can try out the following.

1) Shutdown the dev database and copy initDBNAME.ora, pwdDBNAME.ora, datafiles, control files, redo logfiles to the new machine.
2) Edit the initDBNAME.ora to change the control file location.
3) Create new service using ORADIM utility and mention the initDBNAME.ora path.
4) Startup mount and rename all datafiles and redologfiles.
5) Alter database open.
6) You need not change the db_name, just use the connect string ora_UAT to connect to this DB with SID as ora_dev and new machine ip/machinename as hostname.
7) Make changes in the listener.ora file to register the database manually.

HTH