Oracle Database Discussions

Greetings All,

Oracle Enterprise 11g r2, on a Windows2008 platform.

I would appreciate some advice regarding moving/copying a database to a new server. Some of the information below may not be pertinent to my goal. Please be patient as I am a newbie.

I have installed oracle and created a database (prod03) on the new/target server. I created the same tablespaces (and datafiles/names) as are on the existing/source server (prod01), except that on the new/target server (prod03) there is 1 more data file for the USERS tablespace than there is on the existing/source server (prod01).

My initial thought was to perform a expdp full=y.
The database contains 220 schemas, when I performed an expdp full=y estimate only it indicated 220Gb. I think this would take much more time to export and then import than what I hope to be able to do below.

I would like to be able copy the datafiles from the source server prod01 server over to the target server prod03, some names/locations will change.

One scenario I found (http://www.dba-oracle.com/oracle_tips_db_copy.htm) was to backup the control file to trace on the old/source server (prod01). Copy everything to the new/target server. Tweak the file that creates the new control file.

Step 4 of the above mentioned link says to change
CREATE CONTROLFILE REUSE DATABASE "PROD01" NORESETLOGS to
CREATE CONTROLFILE SET DATABASE "PROD03" RESETLOGS

Notice the change from REUSE to SET. I am not sure if this is right for my situation.

Could I issue a backup control file to trace on the target server (prod03), add the reference to the additional datafile. Copy over all of the datafiles for all of the tablespaces (users,system/sysaux/undotbs1,temp),
Delete the existing control file, and generate the new control file.

Then perhaps issue a startup resetlogs or startup recover?

Thanks for your time,
Bob

Edited by: Snyds on May 17, 2012 12:26 PM