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
