Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN. Imagine that you have a database on one node and you want to copy it to another node without shuting down your database and move your datafiles to a different directory structure… This will be demonstrated here by using RMAN.
ASSUMPTIONS
Source Database
- 10.2.0.4 database online (sid neo) at server1 (app)
- archivelog mode is enabled
- db datafiles are in the directory /opt/oracle/oradata/neo2
- database will be backed up online with RMAN to /u01/backup
Destiny Database
- 10.2.0.4 Oracle Home installed without any database running at server2 (mynode2.com)
- db datafiles must be created / moved to different directory: /opt/oracle/oradata/neo
- only the manual backup created at server1 will be moved to server2
AT SERVER1
Logon as oracle user software owner at server1 and set your environment variables. Then open RMAN and backup the source database we want to copy /move / clone.
[oracle@neoface oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@neoface oracle]$ export ORACLE_SID=neo
[oracle@neoface oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@neoface oracle]$ rman target /
RMAN> backup database plus archivelog;
The RMAN backup created the following files at /u01/backup:
cf_NEO_c-1689570411-20090106-00 (control file backup) back_NEO_675389594_736_1 back_NEO_675389780_737_1 back_NEO_675390018_738_1 back_NEO_675390293_739_1
Copy those 5 backup files to server2
[oracle@neoface oracle]$ scp /u01/backup/back_NEO* root@mynode2.com:/u01/backup/
Create an initialization file (pfile) from the current spfile. Then copy it to the server2.
[oracle@neoface oracle]$ sqlplus “/ as sysdba”
SQL> create pfile from spfile;
SQL> exit;
[oracle@neoface oracle]$ scp /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora oracle@mynode2.com:/opt/oracle/product/10.2.0/db_1/dbs/initneo.ora/
AT SERVER2
Logon at server2 to do the following steps:
- create the OS directories to hold the datafiles and the admin log files and pfile:
- edit the pfile to modify the instance name in parameters like bdump, udump, etc
- change the onwership of pfile to belong to oracle user
- connect to RMAN and startup the database in nomount mode
- restore the control file from the backup
- mount the database
- validate catalog by crosschecking and cataloging the 4 backups pieces we copied
- rename the datafiles and redolog files and restoring the database
Switch to oracle user and create datafiles directories :
[root@mynode2 root] su – oracle
[oracle@mynode2 oracle]$ mkdir /opt/oracle/admin/neo -p
[oracle@mynode2 oracle]$ cd /opt/oracle/admin/neo
[oracle@mynode2 oracle]$ mkdir cdump udump bdump pfile
[oracle@mynode2 oracle]$ mkdir /opt/oracle/oradata/neo -p
Edit your pfile accordingly your new directory structure:
[oracle@mynode2 oracle]$ vi /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora
Set environment variables and start working on RMAN:
[oracle@mynode2 oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@mynode2 oracle]$ export ORACLE_SID=neo
[oracle@mynode2 oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@mynode2 oracle]$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from ‘/u01/backup/cf_NEO_c-1689570411-20090106-00′;
RMAN> alter database mount ;
RMAN> exit
Now that the database is mounted, we’ll check the correct database SCN from the current log that we’ll use later to recover the database. Take note of your current SCN.
[oracle@mynode2 oracle]$ sqlplus “/ as sysdba”
SQL> select group#, first_change#, status, archived from v$log;
GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 336565140 ACTIVE YES
2 336415067 CURRENT NO
3 336523814 INACTIVE YES
SQL> exit;
[oracle@mynode2 oracle]$ rman target /
As we only copied to this server the backup we created at the beggining and we did not copy all the backups we had on server1 we must crosscheck the catalog against the OS files. Run the following commands at RMAN prompt :
RMAN> CROSSCHECK backup;
RMAN> CROSSCHECK copy;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
Now let’s catalog the 4 backup pieces that we copy to this server2:
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389594_736_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389780_737_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390018_738_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390293_739_1′;
Next, as we changed the directory of our datafiles we must rename the redologs:
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo01.log’ to ‘/opt/oracle/oradata/neo/redo01.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo02.log’ to ‘/opt/oracle/oradata/neo/redo02.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo03.log’ to ‘/opt/oracle/oradata/neo/redo03.log’;
If you use BLOCK CHANGE TRACKING to allow fast incremental backups, and if you want to move the datafiles to different directory you must disable this feature and enabling it by specifying the new dir:
RMAN> ALTER DATABASE disable block change tracking;
RMAN> ALTER DATABASE enable block change tracking using file ‘/opt/oracle/oradata/neo/block_change_tracking.f’;
This will avoid errors like ORA-19751 and ORA-19750
Now let’s run the script that will restore our database, renaming the datafiles and recovering until the archivelog with SCN 336415067, the current one.
RMAN> run {
set newname for datafile 1 to “/opt/oracle/oradata/neo/system01.dbf”;
set newname for datafile 2 to “/opt/oracle/oradata/neo/undotbs01.dbf”;
set newname for datafile 3 to “/opt/oracle/oradata/neo/sysaux01.dbf”;
set newname for datafile 4 to “/opt/oracle/oradata/neo/data01.dbf”;
set newname for datafile 5 to “/opt/oracle/oradata/neo/index01.dbf”;
set newname for datafile 6 to “/opt/oracle/oradata/neo/users01.dbf”;
set newname for datafile 7 to “/opt/oracle/oradata/neo/streams.dbf”;
set newname for datafile 8 to “/opt/oracle/oradata/neo/data01brig.dbf”;
set newname for datafile 9 to “/opt/oracle/oradata/neo/index02.dbf”;
restore database;
switch datafile all;
recover database until scn 336415067;
}
RMAN> ALTER DATABASE open resetlogs;
I didn’t manage to avoid errors like ORA-01110 and ORA-01180 at RMAN without using the “until” clause in the “recover database” sentence instead, like most people use it, as the first instruction after the run command.
Later I’ll upload a video for you easily see it running flawlessly 
Btw, HAPPY NEW YEAR!!!!
Nice work!
I’ve saved this page to my step-by-step tutorial collection.
By: Stelmd Dmitriy on January 7, 2009
at 10:28 am
I have been looking for documentation like this! Thank you. I do have one question. Can the RMAN backup directory be different on the 2nd server and how would you tell the control file where it is? Our production server backs up to /u04, but both test servers currently do not have a /u04. It would be helpful to relocate the backups to say /u02 if possible.
By: Curt Swartzlander on June 23, 2009
at 4:47 pm
I am using Red hat 4 and Oracle 10g, I am facing problem while cloning a database in same server using RMAN, Below are the steps i performed…..
1. target database(Richard) is open 2. Catalog database(Kill) is open 3. Auxiliary database(dup) which is clonedb
I Created all the directories for clone same as Target database
II created pfile for clonedb
III traced controlfile for clone db.
IV export ORACLE_SID= richard
rman
rman> Connect target /
rman> backup database plus Archivelog
rman> exit
Then configured tnsnames.ora and listener.ora for clonedb “dup”
then i started the listener
lsnctrl> start
export oracle_sid=dup
sqlplus /nolog
sql> startup pfile= ‘/path’ nomount;
i just checked all the three database whether its working fine
tnsping richard 4 then tnsping kill 4 —— these two commands worked fine.
then i tried it for clonedb tnsping dup 4 —–it shows a error called cannot resolve name
then i started the RMAN
export ORACLE_SID=richard
rman
rman> connect target /
rman> connect catlog
rman> connect auxiliary sys/password@dup
i got a error here cannot resolve auxiliary name.
where did i made mistake.
By: Julius.SP on August 4, 2009
at 5:32 pm
Thanks. It just helped me.
By: Michael on August 14, 2009
at 4:25 pm
Thanks for posting this, it has been a big help. I am having a bit of trouble however.
First, let me state that I am not a DBA. I am a Java Programmer that has been “promoted” to DBA due to staff cuts. So, while trying the best I can, I know I am over my head. But I appreciate this post, and any help that can be offered.
Following these directions, I get an error at the last step. Without posting the entire stream (which I can do), my final error is:
RMAN-06556: datafile 1 must be restored from backup older than scn 20114168
Now.. I did have a few anomalies, per the instructions above, which might be ok… here they are:
1. I only had three backup files (plus the control file), not four. I assume this is ok.
2. My SCN info, looks different. Mine looks like this:
GROUP# FIRST_CHANGE# STATUS ARC
———- ————- —————- —
1 20114108 INACTIVE YES
2 20104214 INACTIVE YES
3 20114168 CURRENT NO
I used 20114168 in my final recover. While I assume this is correct, this difference concerned me the most.
3. The CATALOG commands only worked for me as SQL statements, not RMAN commands. I assume this ok.
4. I did not have as many tablespaces(?) as in the example, and was not sure how to order them by datafile #. I followed the example as best I could starting with system01.dbf as datafile 1 and my users01.dbf as my database 5. This confused me a bit.
I guess that is. I four complete times with the same error at the end…. so not sure what I am doing wrong, although certainly seems like that SCN number is an issue.
This original post has certainly increased my understanding of RMAN, and I appreciate any further help.
I am using oracle 10.2.0.4 in a CentOS 5.3 environment.
Thank you
Doug
By: Doug Houck on August 21, 2009
at 8:00 pm