Migration Database from One machine to another using user managed backup.

Migration Database from One machine to another using user managed backup.

( Please note same OS , same version )


Migration Database from One machine to another using user managed backup.


Put the database in begin backup mode and copy all the datafiles,tempfiles,undo files on another server.

SQL> alter database begin backup;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u04/app/oracle/oradata/prod11/users01.dbf
/u04/app/oracle/oradata/prod11/undotbs01.dbf
/u04/app/oracle/oradata/prod11/sysaux01.dbf
/u04/app/oracle/oradata/prod11/system01.dbf


[oracle@stand oracle]$ cd /u04/app/oracle/oradata/prod11
[oracle@stand prod11]$ ls -ltr
total 1347560
-rw-r----- 1 oracle oinstall  20979712 Jun  8 13:26 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jun  8 13:30 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Jun  8 13:30 redo01.log
-rw-r----- 1 oracle oinstall   5251072 Jun  8 13:33 users01.dbf
-rw-r----- 1 oracle oinstall  31465472 Jun  8 13:33 undotbs01.dbf
-rw-r----- 1 oracle oinstall 702554112 Jun  8 13:33 system01.dbf
-rw-r----- 1 oracle oinstall 461381632 Jun  8 13:33 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jun  8 13:33 redo02.log
-rw-r----- 1 oracle oinstall   9748480 Jun  8 13:34 control02.ctl
-rw-r----- 1 oracle oinstall   9748480 Jun  8 13:34 control01.ctl

[oracle@stand prod11]$ scp -r *.dbf oracle@prod.som.com:/u04/app/oracle/oradata/prod11

Please note - Control file and Redo log files we will copy once we will shutdown database here.


Test Case :

created a table and updated a row on the table.

[oracle@stand prod11]$ sqlplus '/as sysdba'

SQL> create table t ( name varchar2(10));

Table created.

SQL> insert into t values ('test');

1 row created.

SQL> shut immediate;
ORA-01149: cannot shutdown - file 1 has online backup set
ORA-01110: data file 1: '/u04/app/oracle/oradata/prod11/system01.dbf'

SQL> alter database end backup;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Now our current database shut down we will copy redo,archives,pfile,control file on other machine on same location:


[oracle@stand prod11]$ scp *.log oracle@prod.som.com:/u04/app/oracle/oradata/prod11

[oracle@stand prod11]$ scp *.ctl oracle@prod.som.com:/u04/app/oracle/oradata/prod11


[oracle@stand prod11]$ cd /u04/app/oracle/oraarch
[oracle@stand oraarch]$ ls -ltr
total 23372
-rw-r----- 1 oracle oinstall 23895040 Jun  8 13:30 1_2_1031837194.arc
-rw-r----- 1 oracle oinstall     1024 Jun  8 13:30 1_3_1031837194.arc
-rw-r----- 1 oracle oinstall     2048 Jun  8 13:30 1_4_1031837194.arc


[oracle@stand oraarch]$ scp * oracle@prod.som.com:/u04/app/oracle/oraarch/

[oracle@stand oraarch]$ cd $ORACLE_HOME/dbs
-rw-r--r-- 1 oracle oinstall      989 Jun  8 15:06 initprod11.ora
[oracle@stand dbs]$ scp initprod11.ora oracle@prod.som.com:/u04/app/oracle/11g/dbs/



On New machine now :

Now all the files ( redo,archives,control files and pfile ) copied we will startup the other machine database.


SQL> startup nomount pfile='initprod11.ora';
ORACLE instance started.

Total System Global Area  379965440 bytes
Fixed Size                  2213576 bytes
Variable Size             121637176 bytes
Database Buffers          251658240 bytes
Redo Buffers                4456448 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u04/app/oracle/oradata/prod11/system01.dbf'


SQL> recover database;
Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from t;

NAME
----------
test


Hope this test case help to migrate the database from other machince.


Thanks.