Tuesday 23 December 2014

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
    union all
    select name from v$tempfile
    union all
    select name from v$controlfile
    union all
    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
  union all
    select name from v$tempfile
    union all
    select name from v$controlfile
    union all
    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


at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)