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 ServiceOracle 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 databaseWith 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 ---------------- ora11gr2Much 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
Marko,
ReplyDeleteI 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 ?
Hello Nerendra,
ReplyDeletesource 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
Really a very good post thanks for sharing your oracle knowledge
ReplyDeleteHi, Thanks for the guide, very useful for a similar task I was doing (file system to ASM though).
ReplyDeleteJust 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.
Hello Babak,
ReplyDeleteI 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
Hey Marko,
ReplyDeleteI 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.
No problem Babak.
ReplyDeleteYour comment was still useful to me. Error you've experienced was unknown to me.
Thanks again for your feedback.
Regards,
Marko
Hi, I have found very interesting your post, thanx a lot for it...
ReplyDeleteI 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....
Hello J.A.,
ReplyDeletehave 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
Well done keep updates, thanks.
ReplyDeleteThanks 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.
ReplyDeleteI 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.