How to copy Oracle database from one server to another server?

How do I copy Oracle Database from one server to another server?

Defenitely there will be a need to copy Production Oracle DB at Demonstration/Training, QA/Acceptance or Development environment.
Most of the times we will be using Oracle Export utility.

step-by-step how to copy database to another server.

Note: you may want to change DB name while copying it to another destination. This case does not change the procedure much and steps required to do so will be specially highlighted in the article.
Also, you may want to change Oracle DBID of the Database. the reason is that Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. For changing Oracle DBID for a database please refer to the corresponding HOWTO.
OK. Here we go. We will imagine a situation when you need top copy PRO (production) DB into TST (test) DB on test server...

1. Instance service creation. (If you have instance already created - skip this step)
On Windows platform at destination DB server you will need to create Windows service for the DB instance to wich we will copy our PRO DB.
For this we will use ORADIM Windows utility.

oradim -new -sid TST -INTPWD mypassword -STARTMODE AUTO

The command above will create and start Windows service named OracleServiceTST.
Now you need to set ORACLE_SID environment variable to the newly created instance name:

set ORACLE_SID=tst

Then you need to specify new instance runtime parameters.  I recommend to do it by exporting pfile from source DB instance, modify it and then import it to the destination DB instance:

a) Exporting pfile from source DB.
Connect source DB server in SQLPlus and run:

create pfile='d:\PROpfile.txt' from spfile;

b) Modifying pfile.
Look inside PROpfile.txt and modify memory parameters making them more suitable for the destination server environment. If you plan to change locations of the DB files on the destination server, then modify the pathes in PROpfile.txt. Please take care and make sure that pathes you specified are correct and exist.
Pay attention to control_files parameter value.
Also look at the parameter db_name. If you like to rename DB (to form unique globalname), change the value of this parameter to the DB name you like.

c) Importing pfile to the destination DB.
Next step is to create Oracle SPFile out of modified PFILE. At the destination DB Server run (make sure ORACLE_SID environment variable is set to newly created instance name as was mentioned above):

sqlplus / as sysdba
create spfile from pfile='d:\PROpfile.txt';

You have created new DB instance service and specified initialization parameters to it. Now it is time to continue.

2. Copying data from source DB server to destination DB server.
Shutdown source DB (and destination DB in case it is running):

shutdown immediate;

and copy all datafiles, logfiles and controlfiles from source DB server to the destination DB server. Please make sure that controlfiles are copied into correct destination DB server locations (the controlfiles names and locations are specified in spfile or pfile of the destination DB. See point b) of step 1 in this HowTo for details). Delete old DB files before copying the new ones if you update already existing DB on destination server.
Do not forget to startup source DB back.

3. Changing destination DB name or destination DB datafiles location

If you do not want to change data files location or rename DB, skip this step.
In case you want to change DB name or modify the location of data files,  follow this procedure:

a) Backup controlfile from source DB to script:

Connect source DB server in SQLPlus and run:

alter database backup controlfile to trace as'd:\createcontrolfile.sql' resetlogs;

b) Modify controlfile creation script:

Look inside d:\createcontrolfile.sql and modify it, specifying new location of datafiles.
If you want to change database name, modify create controlfile statement adding set keywork before database keyword and changing the DB name.

For example:

CREATE CONTROLFILE REUSE SET DATABASE "TST" RESETLOGS  NOARCHIVELOG ...

Since you did shutdown source DB before copying the files using shutdown immediate, please delete the line

RECOVER DATABASE USING BACKUP CONTROLFILE;

in createcontrolfile.sql

Please also remove commented and empty lines from the createcontrolfile.sql file to avoid possible errors.

c) Execute modifyied script on the destination DB:
At the destination DB Server run (make sure ORACLE_SID environment variable is set to newly created instance name as was mentioned above):

sqlplus / as sysdba
@'d:\createcontrolfile.sql';

If everything went OK and you see no errors, then execute

shutdown immediate;

4. Startup destination Db and verify it work OK.
At the destination DB Server run:

sqlplus / as sysdba
startup;