I have tried to perform this using Enterprise Manager, however I have never been successful. Here are the manual steps.
Migrating a database from one RAC system to another can be performed by cloning the database, once it’s cloned it can be converted to a cluster database. The following steps are what is required.
Step 1. On the newly created standby database
create pfile='/tmp/SOA02PRD1.ora' from spfile;
Step 2. Modify the /tmp/SOA02PRD1.ora file.
SOA02PRD1.__db_cache_size=1644167168 SOA02PRD1.__java_pool_size=16777216 SOA02PRD1.__large_pool_size=16777216 SOA02PRD1.__shared_pool_size=452984832 SOA02PRD1.__streams_pool_size=0 SOA02PRD2.__db_cache_size=1644167168 SOA02PRD2.__java_pool_size=16777216 SOA02PRD2.__large_pool_size=16777216 SOA02PRD2.__shared_pool_size=452984832 SOA02PRD2.__streams_pool_size=0 SOA02PRD3.__db_cache_size=1644167168 SOA02PRD3.__java_pool_size=16777216 SOA02PRD3.__large_pool_size=16777216 SOA02PRD3.__shared_pool_size=452984832 SOA02PRD3.__streams_pool_size=0 *._log_deletion_policy='all' *.archive_lag_target=0 *.background_dump_dest='/app/oracle/admin/NEWSOA02DOV/bdump' *.compatible='10.2.0.5.0' *.control_file_record_keep_time=42 *.control_files='+DATA1/newsoa02dov/controlfile/current.288.864211119','+FRA1/newsoa02dov/controlfile/current.384.864211121' *.core_dump_dest='/app/oracle/admin/NEWSOA02DOV/cdump' *.db_block_size=8192 *.db_create_file_dest='+DATA1' *.db_domain='toll.com.au' *.db_file_multiblock_read_count=16 *.db_name='SOA02PRD' *.db_recovery_file_dest='+FRA1' *.db_recovery_file_dest_size=354921M *.db_unique_name='NEWSOA02DOV' *.dg_broker_config_file1='+DATA1/dr1NEWSOA02DOV.dat' *.dg_broker_config_file2='+DATA1/dr2NEWSOA02DOV.dat' *.dg_broker_start=true *.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbaup01)(PORT=1529)))(CONNECT_DATA=(SERVICE_NAME=NEWSOA02DOV_XPT.toll.com.au)(INSTANCE_NAME=SOA02PRD)(SERVER=dedicated)))' *.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=atsdbdop01-vip)(PORT=1529))(ADDRESS=(PROTOCOL=TCP)(HOST=atsdbdop02-vip)(PORT=1529)))(CONNECT_DATA=(SERVICE_NAME=SOA02DOV_XPT.toll.com.au)(SERVER=dedicated)))', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=atsdbsuz01-vip)(PORT=1529)))(CONNECT_DATA=(SERVICE_NAME=SOA02SUN_XPT.toll.com.au)(INSTANCE_NAME=SOA02PRD1)(SERVER=dedicated)))' *.job_queue_processes=10 *.log_archive_config='dg_config=(SOA02DOV,SOA02SUN)' *.log_archive_dest_1='LOCATION=use_db_recovery_file_dest','valid_for=(ALL_ROLES,ONLINE_LOGFILE)' *.log_archive_dest_2='LOCATION=use_db_recovery_file_dest','valid_for=(STANDBY_ROLE,STANDBY_LOGFILE)' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=2 *.log_archive_min_succeed_dest=1 *.log_archive_trace=0 *.log_file_name_convert='null','null' *.open_cursors=300 *.pga_aggregate_target=2147483648 *.processes=1600 *.remote_login_passwordfile='exclusive' *.resource_limit=TRUE *.sessions=1765 *.sga_target=2147483648 *.standby_archive_dest='' *.standby_file_management='AUTO' *.undo_management='AUTO' --ADD THIS SECTION BELOW AND MODIFY TO YOUR CONFIGURATION *.cluster_database=TRUE *.cluster_database_instances=3 SOA02PRD1.undo_tablespace=undotbs1 SOA02PRD2.undo_tablespace=UNDOTBS2 SOA02PRD3.undo_tablespace=UNDOTBS3 SOA02PRD1.instance_number=1 SOA02PRD2.instance_number=2 SOA02PRD3.instance_number=3 SOA02PRD1.instance_name=SOA02PRD1 SOA02PRD2.instance_name=SOA02PRD2 SOA02PRD3.instance_name=SOA02PRD3 SOA02PRD1.thread=1 SOA02PRD2.thread=2 SOA02PRD3.thread=3 SOA02PRD1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbaup01-vip.toll.com.au)(PORT=1529))))' SOA02PRD2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbaup02-vip.toll.com.au)(PORT=1529))))' SOA02PRD3.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbaup03-vip.toll.com.au)(PORT=1529))))'
Step 3. Modify the contents of the /var/opt/oracle/oratab file and include the following
SOA02PRD1
4. Before shutting down the database, take a note of the spfile
show parameter spfile;
+DATA/newdssoap01/spfiledssoap01.ora_463
Shutdown abort;
oraenv SOA02PRD1
sqlplus "/ as sysdba"
create spfile='+DATA/newdssoap01/spfiledssoap01.ora_463' from pfile='/tmp/rob.ora';
exit
5. Copy the files to the other nodes in the cluster
cp /app/oracle/product/10.2.0/dbs/initSOA02PRD.ora /app/oracle/product/10.2.0/dbs/initSOA02PRD1.ora
sqlplus / as sysdba
startup mount
6. Copy the password file to each node in the cluster
cp orapwSOA02PRD orapwSOA02PRD1
scp initSOA02PRD1.ora oradbaup02:/app/oracle/product/10.2.0/dbs/initSOA02PRD2.ora
scp initSOA02PRD1.ora oradbaup03:/app/oracle/product/10.2.0/dbs/initSOA02PRD3.ora
scp orapwSOA02PRD oradbaup02:/app/oracle/product/10.2.0/dbs/orapwSOA02PRD2
scp orapwSOA02PRD oradbaup03:/app/oracle/product/10.2.0/dbs/orapwSOA02PRD3
7. Add the database to the cluster (
. oraenv
DSSOAP011
srvctl add database -d NEWDSSOAP01 -n oradbaup01 -m TOLL.COM.AU -o /app/oracle/product/10.2.0 -p +DATA/newdssoap01/spfiledssoap01.ora_463 -r physical_standby -s mount
srvctl add instance -d NEWDSSOAP01 -i DSSOAP011 -n oradbaup01
srvctl add instance -d NEWDSSOAP01 -i DSSOAP012 -n oradbaup02
srvctl add instance -d NEWDSSOAP01 -i DSSOAP013 -n oradbaup03
8. Make the following directories on all nodes
mkdir -p /app/oracle/admin/NEWDSSOAP01/bdump
mkdir -p /app/oracle/admin/NEWDSSOAP01/cdump
mkdir -p /app/oracle/admin/NEWDSSOAP01/udump
9. Start the database
srvctl start database -d NEWDSSOAP01
check with crsstat
10. Ensure listener is setup correctly
. oraenv
CRS
First check that the port is currently configured with the scan listener
srvctl config listener
Name: LISTENER
Type: Database Listener
Network: 1, Owner: oracle
Home:
End points: TCP:1521,1528
Listener is enabled.
Listener is individually enabled on nodes:
Listener is individually disabled on nodes:
It’s not, so we need to add it and restart the listener. Stop any other listener running from the oracle home
srvctl modify scan_listener -endpoints TCP:1521/TCP:1528/TCP:1529
srvctl modify listener -endpoints TCP:1521/TCP:1528/TCP:1529
ps -ef |grep lsnr
oracle 2960 1 0 Nov 21 ? 0:23 /app/oracle/product/10.2.0/bin/tnslsnr LISTENER1 -inherit
kill -9 2960 (make sure you kill the right one)
11. Ensure monitoring is setup correctly and all targets discovered
emctl control agent runCollection oradbaup01:host DiscoverTargets
emctl config agent addinternaltargets
12. Now Configure dataguard from the primary database run the following;
dgmgrl
connect /
DGMGRL> show configuration verbose;
Configuration
Name: SOA02PRD.TOLL.COM.AU
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
SOA02DOV - Primary database
SOA02SUN - Physical standby database
NEWSOA02DOV - Physical standby database
Current status for "SOA02PRD.TOLL.COM.AU":
Warning: ORA-16607: one or more databases have failed
Remove the new Physical standby database and add it again from the primary database.
****** MAKE SURE THERE IS AN ENTRY IN THE TNSNAMES.ORA for the database you are removing and going to add again **********
DGMGRL>remove database 'NEWSOA02DOV'
DGMGRL>add database 'NEWSOA02DOV' as connect identifier is 'NEWSOA02DOV' maintained as physical;
DGMGRL>enable database 'NEWSOA02DOV';
Check the database connection from the standby database and ensure you don't get any time-out messages
If needed start real time apply
SQL>alter database recover managed standby database using current logfile disconnect from session;
TIPS & TRAPS
Make sure correct entries are in tnsnames.ora. Specially, pay attention to local listener, scan listener, vip-listener, use of the service name instead of SID etc.
Eg. On EIL DR node 2:
LISTENER_EILPRD12 = (DESCRIPTION = (PROTOCOL = TCP) (HOST = eildbauz02) (PORT = 1521))
EILPRDSUN = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(HOST = eildr-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = EILPRDSUN)))
EILPRDSUN_EILDBAUZ02 =(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eildbauz02-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = EILPRDSUN) (INSTANCE_NAME = EILPRD12) ) )
EILPRDSUN_EILPRD12_DGMGRL = (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = eildbauz02)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = EILPRDSUN_EILPRD12_DGMGRL)))
Copy password file from a Primary node to corresponding DR node. Eg on node 2 of Primary to node 2 of DR orapwEILPRD12. Otherwise you will see "Fatal NI connect error 12537, connecting to:" errors on alert log, and/or " ORA-12521 " errors in DG log (eg. drcEILPRD11.log ) and very hard to trouble shoot.
If the DR RAC database has lesser nodes than the Primary RAC database, make sure no entries exist in the DR spfile that are not related to the valid nodes in DR. For example, EIL primary is a 4 node RAC and DR is 2 node RAC. By default, DR init ora files will have non-existent node entries such as eildbauz04 etc.
Make sure the SP file of the DR cluster database is located in ASM and local init ora files of each DR node with a 1 line in it (pointing to cluster SP file in ASM) is sitting in $ORACLE_HOME/dbs