Move, Clone or Copy an Oracle Database with RMAN

January 7, 2009  |  dba, security

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

database status

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]$ sqlplus “/ as sysdba”
SQL> alter system switch logfile;
SQL> exit;
[oracle@neoface oracle]$ rman target /
RMAN> backup database plus archivelog;

rman backup

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

copybackups1

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!!!!

PS: to provide better look and offer my reader more resources, my site is now supported by advertising. If you feel that it very annoying, please give me some feedback. If you think it deserves to be clicked… just do it 🙂

(2011/07/20) updated to issue a switch logfile before database backup and avoid errors “RMAN-06026: some targets not found – aborting restore”  and “RMAN-06023: no backup or copy of datafile 1 found to restore”


16 Comments


  1. Nice work!
    I’ve saved this page to my step-by-step tutorial collection.

  2. Curt Swartzlander

    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.

  3. 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.

  4. Thanks. It just helped me.

  5. 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

  6. Leonard Oshiyemi

    This is really detailed and informational. You will need to create the temp files once all are said and done.

    Thanks,

  7. excellent superb document

  8. how do we change the database name (sid) using above steps

  9. Vielen herzlichen Dank! Es ist einfach sehr gut 🙂

  10. AT SERVER2 :
    when restore contronfile with command

    restore controlfile from ‘/backup/cf_c-1387073462-20110716-01’;
    error

    RMAN-03002: failure of restore command at 07/16/2011 00:00:53
    RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

    AT SERVER1 : using backup below

    # Configure RMAN settings
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/cf_%F’;
    CONFIGURE BACKUP OPTIMIZATION ON;
    CONFIGURE DEVICE TYPE disk PARALLELISM 1;
    CONFIGURE DEFAULT DEVICE TYPE TO disk;
    CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘/backup/b_%U’;
    #CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT ‘/backup/b_%U’;
    #CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT ‘/backup/b_%U’;
    #CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/backup/snapcf_${DBNAME}.f’;

    # Perform backup of database and archivelogs, deleting backed up archivelogs
    BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

    Please help me

    • In 10g autobackups may be stored in the Flash Recovery Area. Using only the default options RMAN looks for the autobackup during the restore with autobackup in
      $ORACLE_HOME/dbs for UNIX and in $ORACLE_HOME\database for Windows.

      Check it:

      SQL> show parameter db_recovery_file_dest

      NAME TYPE VALUE
      ———————————— ———– ——————————
      db_recovery_file_dest string /u01/app/oracle/flash_recovery_area

      SQL> show parameter db_name

      NAME TYPE VALUE
      ———————————— ———– —–
      db_name string orcl

      After this, try to restore it from FRA:

      RMAN> restore spfile from autobackup db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’ db_name=’orcl’;

      Hope it helps.
      Joao

      • Hi Joao ,

        i use oracle 11.2.0 and when restore spfile from autobackup db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’ db_name=’DB01′; error

        RMAN-03002: failure of restore command at 07/22/2011 05:32:15
        RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

  11. This is the documets I am looking for. I tried to following the steps, I ran into issue on clond database.

    Here is my environment:
    — Productin db1 ( Production database SID=17P)
    — Staging db2 ( Staging database SID=17S)

    We have differnt SID and differnt server.

    I tried to clon from production to staging, the structure is different from production and staging and have different SID.

    –Backup the database by using the RMAN in production.
    RMAN>BACKUP DATABASE PLUS ARCHIVELOG;

    –Copy all the backup file from production database to staging.
    — Create pfile from spfile in production and copy to staging.
    — Modify the copied pfile from production database and match staging structures, such as controlfile, udump, adump directories.
    –In Staging database, startup with modified pfile
    –Rename the existing Staging database controlfiles name and restore the controlfileS by using RMAN without any problem.
    But when I restored the database from RMAN. I got the following errors:

    RMAN> restore database;

    Starting restore at 20-JUL-11
    using channel ORA_DISK_1

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 07/20/2011 15:54:55
    RMAN-06026: some targets not found – aborting restore
    RMAN-06023: no backup or copy of datafile 13 found to restore
    RMAN-06023: no backup or copy of datafile 12 found to restore
    RMAN-06023: no backup or copy of datafile 11 found to restore
    RMAN-06023: no backup or copy of datafile 10 found to restore
    RMAN-06023: no backup or copy of datafile 9 found to restore
    RMAN-06023: no backup or copy of datafile 8 found to restore
    RMAN-06023: no backup or copy of datafile 7 found to restore
    RMAN-06023: no backup or copy of datafile 6 found to restore
    RMAN-06023: no backup or copy of datafile 5 found to restore
    RMAN-06023: no backup or copy of datafile 4 found to restore
    RMAN-06023: no backup or copy of datafile 3 found to restore
    RMAN-06023: no backup or copy of datafile 2 found to restore
    RMAN-06023: no backup or copy of datafile 1 found to restore

    But I had all the backup from production and copied to staging db:

    S Key Type LV Size Device Type Elapsed Time Completion Time
    —— —- — ———- ———– ———— —————
    47 Full 12.72G DISK 00:04:46 19-JUL-11
    BP Key: 347 Status: EXPIRED Compressed: NO Tag: TAG20110719T192106
    Piece Name: E:\ORAEDCSDT\ORAEDCSDTBK\17P\BACKUPSET\2011_07_19\O1_MF_NNNDF_TAG20110719T192106_72D4C469_.BKP
    List of Datafiles in backup set 347
    File LV Type Ckp SCN Ckp Time Name
    —- — —- ———- ——— —-
    1 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\SYSTEM01.DBF
    2 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\UNDOTBS01.DBF
    3 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\SYSAUX01.DBF
    4 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\USERS01.DBF
    5 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\V17P\DTFILEEDCS01.DBF
    6 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTFILEEDCS02.DBF
    7 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTFILEEDCS03.DBF
    8 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTFILEEDCS04.DBF
    9 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTFILEUPMLV.DBF
    10 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTSDR.DBF
    11 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTSER.DBF
    12 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTSCER.DBF
    13 Full 868523115 19-JUL-11 E:\ORAEDCSDT\ORAEDCSDTPRD\17P\DTSOLR.DBF

    S Key Size Device Type Elapsed Time Completion Time
    —— ———- ———– ———— —————
    48 562.50K DISK 00:00:01 19-JUL-11
    BP Key: 348 Status: EXPIRED Compressed: NO Tag: TAG20110719T192604
    Piece Name: E:\ORAEDCSDT\ORAEDCSDTBK\17P\BACKUPSET\2011_07_19\O1_MF_ANNNN_TAG20110719T192604_72D4NFK2_.BKP

    List of Archived Logs in backup set 348
    Thrd Seq Low SCN Low Time Next SCN Next Time
    —- ——- ———- ——— ———- ———
    1 1258 868522509 19-JUL-11 868523887 19-JUL-11

    Any ideas what is wrong? I have no problem cloning database without any problem.

    Thanks,
    Kien

    • Hi Kien,

      Issue a log switch and backup the archivelogs as well.

      SQL> alter system switch logfile;

      (now perform the backup)

      This error is indicating that all pieces required to perform the duplication are not present. When you issue a backup script which backs up all of the archivelogs (after a log switch), the duplicate database script completes successfully.

      You must also back up the archivelogs in order to perform a duplicate from a hot backup.

      Regards
      Joao

  12. Hi ,

    Its very well explained , prior to reading this article I had my doubts about moving database from one server to another , now its all clear .. thanks much.

    Regards
    Ismail

  13. Could not be better explained
    Thanks

Leave a Reply