Monday, October 14, 2013

RMAN Duplicate from Active Database - ASM to non-ASM

In this post I want to explain how to create duplicate database from active 11gR2 database using RMAN. There are many blog posts covering that subject but most of them are covering non-ASM to non-ASM, ASM to ASM or non-ASM to ASM duplications.
I want to cover ASM to non-ASM duplication from active database as this subject is not widely covered.


Active duplication from ASM to non-ASM (or other duplications) can be little tricky. You could easily lose quite of time on troubleshooting if you don't comply all prerequisites during preparation. I will show you examples of some errors you could experience.

I haven't used active database duplication in production and practiced much with this feature. But still was confident that I will perform task successfully from the first time. After few hours of troubleshooting and few failed attempts I ended up on detailed reading Oracle Documentation - what I should do at the first place.
Learn on my mistakes :)


So let’s start with little demo.


- Create password file for auxiliary instance

The easiest method would be to copy password file from the source database.
$ scp orapwora11gr2 ora11gr2@192.168.56.107:/u01/app/ora11gr2/product/11.2.0/dbhome_1/dbs
ora11gr2@192.168.56.107's password:
orapwora11gr2                                                                                             100% 1536     1.5KB/s   00:00


- Network setup

For auxiliary database create static instance registration to listener.
Add to listener.ora:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora11gr2)
      (ORACLE_HOME = /u01/app/ora11gr2/product/11.2.0/dbhome_1)
      (SID_NAME = ora11gr2)
    )
  )

For both databases tnsnames.ora would be:
duplicate =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
      (UR=A)
    )
  )

ora11gr2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.109)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11gr2)
      (UR=A)
    )
  )

Start both listeners.


- Create needed directories on target host
$ mkdir -p /u01/app/ora11gr2/admin/ora11gr2/adump
$ mkdir -p /u01/oradata/fra
$ mkdir -p /u01/oradata/ora11gr2/datafiles
$ mkdir -p /u01/oradata/ora11gr2/redologs


- Create simple parameter file and test connectivity
$ cd $ORACLE_HOME/dbs
$ cat initora11gr2.ora
db_name=ora11gr2

Start NOMOUNT auxiliary database using specified parameter file
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 12 00:26:18 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initora11gr2.ora
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
SQL>

Test connectivity.
Target:
$ sqlplus sys/oracle@ora11gr2 as sysdba

Source:
$ sqlplus sys/oracle@duplicate as sysdba


I am executing this commands on target host but you could also execute them on source.

Run this command to establish connection between databases.
$ rman target sys/oracle@ora11gr2 auxiliary sys/oracle@duplicate

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 12 00:39:00 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11GR2 (DBID=138112863)
connected to auxiliary database: ORA11GR2 (not mounted)


- We are ready to test duplicate scenarios


Let's start with this simple script.
run
{
         DUPLICATE TARGET DATABASE TO “ora11gr2”
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump';
}

I get error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:39:29
RMAN-06136: ORACLE error from auxiliary database: ORA-00200: control file could not be created
ORA-00202: control file: '+data1'
ORA-17502: ksfdcre:4 Failed to create file +data1
ORA-15001: diskgroup "DATA1" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Synchronization Service
Oracle wants to create control file is on '+DATA1' but I don't have ASM instance running on target host. I will change location for control file adding "SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'" to the script.

As directory structure on target host is completely different I will change location for DIAGNOSTIC_DEST also.
run
{
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';
}

This time I get:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 00:50:24
RMAN-05501: aborting duplication of target database
RMAN-05517: tempfile +DATA1/ora11gr2/tempfile/temp.280.821541379 conflicts with file used by target database
With specified DB_FILE_NAME_CONVERT parameter location for temporary file is not changed. My temporary file is not in "+DATA1/ora11gr2/datafile/", but in "+DATA1/ora11gr2/tempfile/".


I will use "SET NEWNAME FOR TEMPFILE" to change location.
run
{
         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl';
}

Again error:
...
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA1/ora11gr2/archivelog/2013_10_12/thread_1_seq_222.1249.828580067" auxiliary format
 "+FRA1"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 12.10.2013 01:07:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=222 RECID=247 STAMP=828580069
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/12/2013 01:07:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/12/2013 01:07:52
ORA-17628: Oracle error 19505 returned by remote Oracle server
Oracle cannot perform recovery because '+FRA' diskgroup is missing on target host.

Now it is obvious that I could save some time if I had checked parameter file on the source host before duplication.

In my case RMAN copies parameter file from source to the target host. As I have different directory structure on the target host I have to update directory locations using SET commands.
run
{
         SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf';
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
         DB_FILE_NAME_CONVERT '+DATA1/ora11gr2/datafile/','/u01/oradata/ora11gr2/datafiles/'
         SPFILE
         SET LOG_FILE_NAME_CONVERT '+DATA1/ora11gr2/onlinelog/','/u01/oradata/ora11gr2/redologs/'
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'
         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'
         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';
}
Excerpt from RMAN log:
…
contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 12.10.2013 09:34:14

Ah, finally!
Finished duplication without errors.

Let's check locations of database files on the target host.
SQL> select name from v$datafile
  2  union all
  3  select name from v$tempfile
  4  union all
  5  select name from v$controlfile
  6  union all
  7  select member from v$logfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/ora11gr2/datafiles/system.268.824220237
/u01/oradata/ora11gr2/datafiles/sysaux.283.824220237
/u01/oradata/ora11gr2/datafiles/undotbs1.279.824220239
/u01/oradata/ora11gr2/datafiles/users.270.824220237
/u01/oradata/ora11gr2/datafiles/example.297.824220239
/u01/oradata/ora11gr2/datafiles/secure1.277.825195489
/u01/oradata/ora11gr2/datafiles/insecure1.267.825195681
/u01/oradata/ora11gr2/datafiles/lobdata.266.827849207
/u01/oradata/ora11gr2/datafiles/dbfstbs.265.827850825

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/group_3.282.821541363
/u01/oradata/ora11gr2/redologs/group_3.281.821541367
/u01/oradata/ora11gr2/redologs/group_2.292.821541357
/u01/oradata/ora11gr2/redologs/group_2.291.821541361
/u01/oradata/ora11gr2/redologs/group_1.294.821541349
/u01/oradata/ora11gr2/redologs/group_1.293.821541355

17 rows selected.

Hm... I don't like this ASM file naming for my duplicate database.

Again, modify script.
run
{
  SET NEWNAME FOR DATAFILE 1 TO '/u01/oradata/ora11gr2/datafiles/system01.dbf'; 
  SET NEWNAME FOR DATAFILE 2 TO '/u01/oradata/ora11gr2/datafiles/sysaux01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/u01/oradata/ora11gr2/datafiles/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 4 TO '/u01/oradata/ora11gr2/datafiles/users01.dbf'; 
  SET NEWNAME FOR DATAFILE 5 TO '/u01/oradata/ora11gr2/datafiles/example01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/u01/oradata/ora11gr2/datafiles/secure1.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '/u01/oradata/ora11gr2/datafiles/insecure1.dbf';
  SET NEWNAME FOR DATAFILE 9 TO '/u01/oradata/ora11gr2/datafiles/lobdata.dbf';
  SET NEWNAME FOR DATAFILE 10 TO '/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf';
  SET NEWNAME FOR TEMPFILE 1 TO '/u01/oradata/ora11gr2/datafiles/temp01.dbf'; 
         DUPLICATE TARGET DATABASE TO "ora11gr2"
         FROM ACTIVE DATABASE
                     LOGFILE
      GROUP 1 ('/u01/oradata/ora11gr2/redologs/redo01a.log', 
               '/u01/oradata/ora11gr2/redologs/redo01b.log') SIZE 50M REUSE, 
      GROUP 2 ('/u01/oradata/ora11gr2/redologs/redo02a.log', 
               '/u01/oradata/ora11gr2/redologs/redo02b.log') SIZE 50M REUSE,
      GROUP 3 ('/u01/oradata/ora11gr2/redologs/redo03a.log', 
               '/u01/oradata/ora11gr2/redologs/redo03b.log') SIZE 50M REUSE
         SPFILE
         SET AUDIT_FILE_DEST '/u01/app/ora11gr2/admin/ora11gr2/adump'
         SET CONTROL_FILES '/u01/oradata/ora11gr2/datafiles/control01.ctl'
         SET DB_RECOVERY_FILE_DEST '/u01/oradata/fra'
         SET DB_CREATE_FILE_DEST '/u01/oradata/ora11gr2/datafiles/'
         SET DIAGNOSTIC_DEST '/u01/app/ora11gr2';
}

Check database files.
SQL> select name from v$datafile
  2  union all
  3  select name from v$tempfile
  4  union all
  5  select name from v$controlfile
  6  union all
  7  select member from v$logfile;


NAME
--------------------------------------------------------------------------------
/u01/oradata/ora11gr2/datafiles/system01.dbf
/u01/oradata/ora11gr2/datafiles/sysaux01.dbf
/u01/oradata/ora11gr2/datafiles/undotbs01.dbf
/u01/oradata/ora11gr2/datafiles/users01.dbf
/u01/oradata/ora11gr2/datafiles/example01.dbf
/u01/oradata/ora11gr2/datafiles/secure1.dbf
/u01/oradata/ora11gr2/datafiles/insecure1.dbf
/u01/oradata/ora11gr2/datafiles/lobdata.dbf
/u01/oradata/ora11gr2/datafiles/dbfstbs.dbf

/u01/oradata/ora11gr2/datafiles/temp01.dbf

/u01/oradata/ora11gr2/datafiles/control01.ctl

/u01/oradata/ora11gr2/redologs/redo03a.log
/u01/oradata/ora11gr2/redologs/redo03b.log
/u01/oradata/ora11gr2/redologs/redo02a.log
/u01/oradata/ora11gr2/redologs/redo02b.log
/u01/oradata/ora11gr2/redologs/redo01a.log
/u01/oradata/ora11gr2/redologs/redo01b.log


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
ora11gr2
Much better!


I am currently preparing for “Oracle Database 11g: New Features for Administrators” exam and this is how I practice/learn more about new features.

Pursuing Oracle Certification path forces me to learn more about new features that I don't use much in my daily work. I would recommend to any Oracle expert to choose one of the exams and prepare themselves for that exam. You don't have to take exam (if it's too expensive for you) - after all, all the fun is in preparation ;-)


REFERENCES: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmdupdb.htm

11 comments:

  1. Marko,

    I am bit confused with the naming convention you have used here. Which DB instance is being duplicated? Is the database on 192.168.56.107 being duplicated over to on 192.168.56.109 ?

    ReplyDelete
  2. Hello Nerendra,

    source database is on 192.168.56.109 host and it is duplicated to 192.168.56.107 host - check my tnsnames.ora file.

    But you are correct - it is little confusing. I could use names instead of IP addresses and set different name for duplicate database to avoid confusion.

    Thanks for comment.

    Regards,
    Marko





    ReplyDelete
  3. Really a very good post thanks for sharing your oracle knowledge

    ReplyDelete
  4. Hi, Thanks for the guide, very useful for a similar task I was doing (file system to ASM though).

    Just one thing: I think you might need a change on the Rman script: on 11g I came across RMAN-06588 errors, while everything seemed ok.

    As it turns out: "DUPLICATE does not have a log_file_name_convert clause. Thus the string log_file_name_clause is considered another DB_FILE_NAME_CONVERT pattern." (Metalink 1130929.1).

    I deleted log convert clause and everything went fine.

    Thanks again for the guide.

    ReplyDelete
  5. Hello Babak,

    I have checked Metalink note you specified.
    Script noted there in "SYMPTOMS" cannot relate to my script as I'am not using log_file_name_convert in DUPLICATE clause.

    You can see in my scripts that log_file_name_convert is added in SPFILE and cause of that I don't experience RMAN-06588 error.

    Thanks for comment.

    Regards,
    Marko

    ReplyDelete
  6. Hey Marko,

    I believe I've made a mistake in judging your script; I had to startup my "duplicate" instance using spfile, so I had to change the script (to remove the SPFILE clause). So obviously my circumstances were different, and that must have caused me issues.

    Sorry for the confusion, and thanks again for the guide. It was very useful to me.

    B.

    ReplyDelete
  7. No problem Babak.
    Your comment was still useful to me. Error you've experienced was unknown to me.

    Thanks again for your feedback.

    Regards,
    Marko

    ReplyDelete
  8. Hi, I have found very interesting your post, thanx a lot for it...

    I have to duplicate a database that is in RAC with ASM to a Non-RAC and Non-ASM database, in different server, but I'm getting this messages:

    connected to auxiliary database (not started)
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 01/13/2014 12:43:38
    RMAN-05501: aborting duplication of target database
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-04014: startup failed: ORA-00439: feature not enabled: Real Application Clusters

    Do you have any idea to avoid/fix it???

    Thank for your atention...

    Regards...

    J.A....

    ReplyDelete
  9. Hello J.A.,

    have you tried to add SET CLUSTER_DATABASE=FALSE in RMAN duplicate command? You're probably using CLUSTER_DATABASE=TRUE for non-RAC database.

    Note that I haven't tried RAC ASM to non-RAC non-ASM scenario so I might be wrong with my advice.

    Thanks for comment.

    Regards,
    Marko

    ReplyDelete
  10. Thanks Marco. I've done lots of backup-based duplication, but not active duplication for many years and not asm / omf to non-rac. I ran into the same errors you did yesterday and did not finish, so I was researching some more to fix the remaining errors. I found a Oracle doc stating that omf named source files cannot use db_file_name_convert to rename them. Your nice writeup confirms that, as I had planned to use set newname today to hopefully fix my remaining errors.
    I have another wrinkle I need to solve and that is to duplicate from db1 instance name / db database name in rac/asm to db instance name / db database name non asm. I seemed to have had some connectivity errors early on using same names on source and dest, so I changed to different name for dest just to get past those problems. After getting the basic duplication working, I'm going to return to the same name issue. Should be doable as it works in non-rac. Thanks for taking the time to share this.

    ReplyDelete