How to Copy an Oracle Database to New Machine

How to Copy an Oracle Database to New Machine

 

How to Copy an Oracle Database to another Machine

 

Please follow the oracle document 174226.1 for copy oracle database to another machine.

 

Pre-requisites: The copy is between 2 machines; both have to be on the same OS and have to have exactly the same database version installed.

STEP1:

Make sure the database you want to copy was closed with a SHUTDOWN IMMEDIATE, SHUTDOWN NORMAL or SHUTDOWN TRANSACTIONAL.

STEP2:

Copy init.ora and control files to create instance and be able to go in mount mode.  Check the init.ora for the locations where the controlfiles have to be, if those locations are not valid on the machine put the control files on different places and adjust the init.ora accordingly.

Note: In my case I did not have init.ora file of database, so created manually following mandatory parameters:

My Initjicoar.ora file is:

-----------------------------------------------------------------------------------------------------------------------

*.db_name=jicoar

#*.db_file_name_convert='E: \oracle\oradata\jicoar\jicoar','E:\oracle\product\10.2.0\jicoar'

#*.log_file_name_convert='E: \oracle\oradata\jicoar\jicoar','E:\oracle\product\10.2.0\jicoar'

*.control_files='E:\oracle\product\10.2.0\jicoar\CONTROL01.CTL','E:\oracle\product\10.2.0\ji coar\CONTROL02.CTL','E:\oracle\product\10.2.0\jicoar\CONTROL03.CTL'

#Note: here controlfile locations are new location

*.user_dump_dest='E: \oracle\product\10.2.0\admin\jicoar\udump'

*.background_dump_dest='E: \oracle\product\10.2.0\admin\jicoar\bdump'

*.core_dump_dest='E: \oracle\product\10.2.0\jicoar\admin\jicoar\cdump'

*.compatible='10.2.0.1'

*.sga_max_size='1024M'

*.sga_target='1024M'

*.UNDO_TABLESPACE='UNDOTBS1'

*.undo_management='auto'

----------------------------------------------------------------------------------------------------------------------

 

STEP3: Copy the data files (all of them) and Copy the redo-log files (all of them).

 

STEP4:

 a. (UNIX only) set the environment variables:

     ORACLE_SID - set to the database name you wish to create

     ORACLE_HOME - set to full pathname of the Oracle system home directory

     PATH - needs to include $ORACLE_HOME/bin

  b. (NT/2000 only) Do 'set ORACLE_SID='

     Use oradim to create the service for the instance.

C:\oradim –help  (you can get all information)

STEP5:

Strartup the database in mount state:

The below command I got the error for oracle compatible error, so I changed the COMPATIBLE parameter to 10.2.0.1 in init.ora file:

SQL> startup mount pfile='E:\oracle\product\10.2.0\db_1\database\initjicoar.ora';

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  1300856 bytes

Variable Size             264242824 bytes

Database Buffers          801112064 bytes

Redo Buffers                7086080 bytes

ORA-00201: control file version 10.2.0.1.0 incompatible with ORACLE version

10.2.0.0.0

ORA-00202: control file: 'E:\ORACLE\PRODUCT\10.2.0\JICOAR\CONTROL01.CTL'

 

Now try to raise the same above command.

SQL> startup mount pfile='E:\oracle\product\10.2.0\db_1\database\initjicoar.ora';

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  1300856 bytes

Variable Size             264242824 bytes

Database Buffers          801112064 bytes

Redo Buffers                7086080 bytes

Database mounted.

STEP6:

RENAME THE FILENAME, here if you without renaming the files and trying to open the database, you will get the below following error.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'E:\ORACLE\ORADATA\JICOAR\JICOAR\SYSTEM01.DBF'

 RENAME THE FILENAME

Then do a rename of the copied datafiles if they are not in the same path as on the other machine.

For all the files that are in the result of the query:

SQL> SELECT NAME FROM V$DATAFILE;

 SQL> alter database rename file ‘E:\oracle\oradata\jicoar\jicoar\SYSTEM01.DBF'

  2  to 'E:\oracle\product\10.2.0\jicoar\SYSTEM01.DBF';

Database altered.

SQL> alter database rename file  'E:\oracle\oradata\jicoar\jicoar\SYSAUX01.DBF'

  2  to 'E:\oracle\product\10.2.0\jicoar\SYSAUX01.DBF';

Database altered.

NOTE: DO ALL DATAFILE, TEMP FILES AND REDO LOG FILES (or redo logs create new redo log files)

STEP7:

RENAME THE REDO LOGS

SQL> select member from v$logfile;

MEMBER

-------------------------------------------------------------------------------

E:\ORACLE\ORADATA\JICOAR\JICOAR\REDO03.LOG

E:\ORACLE\ORADATA\JICOAR\JICOAR\REDO02.LOG

E:\ORACLE\ORADATA\JICOAR\JICOAR\REDO01.LOG

SQL> alter database rename file  'E:\oracle\oradata\jicoar\jicoar\REDO01.LOG'

  2  TO 'E:\oracle\product\10.2.0\jicoar\REDO01.LOG';

Database altered.

SQL> alter database rename file  'E:\oracle\oradata\jicoar\jicoar\REDO02.LOG'

  2  TO 'E:\oracle\product\10.2.0\jicoar\REDO02.LOG';

Database altered.

SQL> alter database rename file  'E:\oracle\oradata\jicoar\jicoar\REDO03.LOG'

  2  TO 'E:\oracle\product\10.2.0\jicoar\REDO03.LOG';

Database altered.

STEP8:

FINALLY OPEN THE DATABASE

SQL> ALTER DATABASE OPEN;

Database altered.

STEP9:

FIND THE INVALID OBJECTS AND RECOMPILE IT

SQL> select count(*) from dba_objects where STATUS='INVALID';

  COUNT(*)

----------

        37

SQL> @?/RDBMS/ADMIN/utlrp.sql

SUCCESSFULLY COMPLIED...

 

 

For RMAN copies you should follow up:

How to restore cold backup of database taken using Rman (Doc ID 1391384.1)

How to Move/Restore DB to New Host and File System using RMAN (Doc ID 1338193.1)

Note 18070.1   How To Make A Copy Of A Database On The Same Unix Machine

(Using a cold backup)

Note 174226.1 How To Copy An Oracle Database To Another Machine

(Using a cold backup)