This blog is about duplicating a RAC source database to a single instance in a different host. RAC is a 11.2 with two nodes
olsnodes -nand two diskgroups for data (+DATA) and flash recovery (+FLASH).
rac4 1
rac5 2
1. Install Oracle software (Enterprise or Standard, same as source edition) on the host where duplicate database will be created or clone from an existing single instance Oracle Home. Also create locations for audit trail (adump directory) and location to store the control files.
2. Create a static listener entry on the listener.ora in duplicate database Oracle Home.
SID_LIST_LISTENER =stdaux is single instance name.
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdaux)
(SID_NAME = stdaux)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/ent)
)
)
3. Create a pfile that only contains the db_name entry
db_name='stdaux'4. Create a password file in the duplicate Oracle Home with the same sys password as the source database sys password
orapwd file=orapwstdaux password=password ignorecase=y5. Start the duplicate instance in nomount state startup nomount;6. Create a TNS entry in the tnsnames.ora file in the source database Oracle Home. Adding this to only one node's tnsnames.ora file is suffice, provided duplicate command is run from this node.
STDAUX =7. Connect to the target (source database) as well as the auxiliary (duplicate database) using rman
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate-hostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = stdaux)
)
)
rman target / auxiliary sys/password@stdauxDuplication will fail if connection to the auxiliary instance is not through Oracle Net. 8. The command used will copy the spfile from source to duplicate while overriding parameter values set using various clauses (set and parameter_value_convert). In 11gR2 RAC remote listener is set to scan listener and local listener is set to VIP TNS entry, which is something not required in a single instance. These parameters will be reset.
Since db_create_file_dest and db_recovery_file_dest will be used in command data files will be created as OMF in these locations. But control file location doesn't get created automatically, so it is necessary to create them manually as mentioned on step 1.
During this step if the duplication process terminates due to an error, delete the spfile on the duplicate Oracle Home (if it was created by the duplicate process) and shutdown and start up the instance in nomount state. Correct the error and try again.
RMAN> DUPLICATE TARGET DATABASErac11g2 is the RAC database name which will appear on various dump file paths and will be replaced by stdaux. Any reference paths to ASM diskgroup will be replaces with directory paths.
2> TO stdaux
3> FROM ACTIVE DATABASE
4> SPFILE PARAMETER_VALUE_CONVERT 'rac11g2', 'stdaux', '+DATA', '/data/oradata', '+FLASH','/data/flash_recovery'
5> SET cluster_database='false'
6> reset REMOTE_LISTENER
7> reset local_listener
8> SET db_create_file_dest = '/data/oradata'
9> set db_recovery_file_dest = '/data/flash_recovery';
Starting Duplicate Db at 21-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '+DATA/rac11g2/spfilerac11g2.ora' auxiliary format
'/opt/app/oracle/product/11.2.0/ent/dbs/spfilestdaux.ora' ;
sql clone "alter system set spfile= ''/opt/app/oracle/product/11.2.0/ent/dbs/spfilestdaux.ora''";
}
executing Memory Script
..
..
..
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_sysaux_12lr0eq9_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_undotbs1_13lr0es0_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_users_15lr0esj_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=732970731 file name=/data/oradata/STDAUX/datafile/o1_mf_undotbs2_14lr0esf_.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 21-OCT-10
9. At the end of the successful execution of the command, duplicate process is complete. Single instance would have chosen one of the undo tablespace (UNDOTBS1) as the system undo tablespace, while other one (UNDOTBS2) will also be there as a tablespace. If needed some tablespaces could be skipped during the duplicate process but trying to skip undo tablespaces could result in the following error
RMAN-00571: ===========================================================10. Though the duplicate instance get created the spfile will still have parameters that were set at instance level.
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 10/21/2010 16:46:01
RMAN-05514: Tablespace UNDOTBS2 has undo information, cannot skip
rac11g21.__db_cache_size=369098752These instance level settings could be reset on the duplicate instance or create a pfile and delete the unwanted entries and create a spfile out of the modified pfile.
rac11g22.__db_cache_size=339738624
stdaux.__db_cache_size=436207616
rac11g21.__java_pool_size=4194304
rac11g22.__java_pool_size=25165824
stdaux.__java_pool_size=4194304
rac11g21.__large_pool_size=8388608
rac11g22.__large_pool_size=8388608
stdaux.__large_pool_size=4194304
rac11g21.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
rac11g22.__oracle_base='/opt/app/orac
..
..
rac11g22.instance_number=2
rac11g21.instance_number=1
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=631242752
rac11g22.thread=2
rac11g21.thread=1
rac11g22.undo_tablespace='UNDOTBS2'
rac11g21.undo_tablespace='UNDOTBS1'
The way to create a duplicate instance without the unwanted parameter entries is to create a pfile with all the necessary parameter entries and use that to start the duplicate in nomount state. Then when running the duplicate command omit the spfile clause.
Replace the pfile created on step 3 with a pfile with following entries (or more if needed)
*.audit_file_dest='/opt/app/oracle/admin/stdaux/adump'Start the duplicate instance in nomount state and follow the rest of the steps unitl the execution of duplicate command.
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oradata/stdaux/controlfile/o1_mf_62n35l5c_.ctl','/data/flash_recovery/stdaux/controlfile/o1_mf_62n35lcp_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/oradata'
*.db_domain='domain.net'
*.db_name='stdaux'
*.db_recovery_file_dest='/data/flash_recovery'
*.db_recovery_file_dest_size=40705720320
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdauxXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1326448640
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3707764736
*.undo_tablespace='UNDOTBS1'
Replace the above given duplicate command with
RMAN> DUPLICATE TARGET DATABASEThe duplicate instance's spfile will not contain any RAC instance specific entries, but unlike a spfile of a database created from the scratch it will show all the hidden Oracle parameters.
2> TO stdaux
3> FROM ACTIVE DATABASE;
Starting Duplicate Db at 21-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
..
..
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 21-OCT-10
Related Post
Duplicate Database Without Target Connection or Catalog Connection
