Thursday, March 10, 2011

11gR2 RAC to RAC Data Guard - 2

The first 11gR2 RAC to RAC Data Guard described the process of setting up a Data Guard between two node RAC. This blog is about another way to achieve the duplication of primary database, and was inspired by the second method of duplication given in RAC to single instance active duplication.

It is assumed all the preparatory work has been carried as described in 11gR2 RAC to RAC Data Guard. The steps below would replace step 13 in the above blog.

1. Instead of creating a pfile with just db_name create a complete pfile that will suite the standby database when it is fully operational. This could be done by creating a pfile on the primary (once data gurad related initialization parameters are set) and modifying it to suite the standby. Below is the one used in this case. The key difference is for control_files only the diskgroup locations are set
cat initrac11g2s1.ora
rac11g2s1.__db_cache_size=373293056
rac11g2s1.__java_pool_size=4194304
rac11g2s1.__large_pool_size=4194304
rac11g2s1.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
rac11g2s1.__pga_aggregate_target=209715200
rac11g2s1.__sga_target=633339904
rac11g2s1.__shared_io_pool_size=0
rac11g2s1.__shared_pool_size=239075328
rac11g2s1.__streams_pool_size=0
rac11g2s2.__db_cache_size=373293056
rac11g2s2.__java_pool_size=4194304
rac11g2s2.__large_pool_size=4194304
rac11g2s2.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
rac11g2s2.__pga_aggregate_target=209715200
rac11g2s2.__sga_target=633339904
rac11g2s2.__shared_io_pool_size=0
rac11g2s2.__shared_pool_size=239075328
rac11g2s2.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/rac11g2s/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+FLASH'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='domain.net'
*.db_file_name_convert='+DATA/rac11g2','+DATA/rac11g2s'
*.db_name='rac11g2'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=9470738432
*.db_unique_name='rac11g2s'
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac11g2sXDB)'
rac11g2s1.fal_client='RAC11G2S1'
rac1g2s22.fal_client='RAC11G2S2'
*.fal_server='RAC11G21','RAC11G22'
rac11g2s1.instance_number=1
rac11g2s2.instance_number=2
*.log_archive_config='dg_config=(rac11g2,rac11g2s)'
*.log_archive_dest_1='service=RAC11G2 LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_uniq
ue_name=rac11g2'
*.log_archive_dest_10='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=rac11g2s'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_10='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=5
*.log_file_name_convert='+FLASH/rac11g2','+FLASH/rac11g2s','+DATA/rac11g2','+DATA/rac11g2s'
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='racb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=631242752
*.standby_file_management='AUTO'
rac11g2s1.undo_tablespace='UNDOTBS1'
rac11g2s2.undo_tablespace='UNDOTBS2'
2. The duplication command is now very simple as all the parameters have been set. The duplication command on step 17 in earlier blog could be replaced with below command. Also multiple rman channels could be enabled to take advantage of parallel file duplication.
run {
allocate channel ch1 device type disk connect 'sys/rac11g2db@rac11g21';
allocate channel ch2 device type disk connect 'sys/rac11g2db@rac11g22';

allocate auxiliary channel ch3 device type disk connect 'sys/rac11g2db@rac11g2s1';
allocate auxiliary channel ch4 device type disk connect 'sys/rac11g2db@rac11g2s1';

duplicate target database for standby from active database;

release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;

}
Output of executing the above command is given below.
rman target / auxiliary sys/rac11g2db@rac11g2s1

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Mar 10 11:39:52 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC11G2 (DBID=379668842)
connected to auxiliary database: RAC11G2 (not mounted)

RMAN> run {
2> allocate channel ch1 device type disk connect 'sys/rac11g2db@rac11g21';
3> allocate channel ch2 device type disk connect 'sys/rac11g2db@rac11g22';
4>
5> allocate auxiliary channel ch3 device type disk connect 'sys/rac11g2db@rac11g2s1';
6> allocate auxiliary channel ch4 device type disk connect 'sys/rac11g2db@rac11g2s1';
7>
8> duplicate target database for standby from active database;
9>
10> release channel ch1;
11> release channel ch2;
12> release channel ch3;
13> release channel ch4;
14>
15> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=156 instance=rac11g21 device type=DISK

allocated channel: ch2
channel ch2: SID=26 instance=rac11g22 device type=DISK

allocated channel: ch3
channel ch3: SID=141 instance=rac11g2s1 device type=DISK

allocated channel: ch4
channel ch4: SID=19 instance=rac11g2s1 device type=DISK

Starting Duplicate Db at 10-MAR-11

contents of Memory Script:
{
backup as copy reuse
targetfile '/opt/app/oracle/product/11.2.0/db_1/dbs/orapwrac11g21' auxiliary format
'/opt/app/oracle/product/11.2.0/db_1/dbs/orapwrac11g2s1' ;
}
executing Memory Script

Starting backup at 10-MAR-11
Finished backup at 10-MAR-11

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/rac11g2s/controlfile/current.257.745418483';
restore clone controlfile to '+FLASH/rac11g2s/controlfile/current.257.745418483' from
'+DATA/rac11g2s/controlfile/current.257.745418483';
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''+DATA/rac11g2s/controlfile/current.257.745418483'', ''+FLASH/rac11g2s/controlfile/current.257.745418483'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Starting backup at 10-MAR-11
channel ch1: starting datafile copy
copying standby control file
output file name=/opt/app/oracle/product/11.2.0/db_1/dbs/snapcf_rac11g21.f tag=TAG20110310T114022 RECID=2 STAMP=745414824
channel ch1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 10-MAR-11

Starting restore at 10-MAR-11

channel ch4: skipped, AUTOBACKUP already found
channel ch3: copied control file copy
Finished restore at 10-MAR-11

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 630501376 bytes

Fixed Size 2229120 bytes
Variable Size 251661440 bytes
Database Buffers 369098752 bytes
Redo Buffers 7512064 bytes
allocated channel: ch3
channel ch3: SID=18 instance=rac11g2s1 device type=DISK
allocated channel: ch4
channel ch4: SID=141 instance=rac11g2s1 device type=DISK

sql statement: alter system set control_files = ''+DATA/rac11g2s/controlfile/current.257.745418483'', ''+FLASH/rac11g2s/controlfile/current.257.745418483'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 630501376 bytes

Fixed Size 2229120 bytes
Variable Size 251661440 bytes
Database Buffers 369098752 bytes
Redo Buffers 7512064 bytes
allocated channel: ch3
channel ch3: SID=16 instance=rac11g2s1 device type=DISK
allocated channel: ch4
channel ch4: SID=143 instance=rac11g2s1 device type=DISK

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set newname for tempfile 1 to
"+data";
switch clone tempfile all;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
backup as copy reuse
datafile 1 auxiliary format
"+data" datafile
2 auxiliary format
"+data" datafile
3 auxiliary format
"+data" datafile
4 auxiliary format
"+data" datafile
5 auxiliary format
"+data" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 10-MAR-11
channel ch1: starting datafile copy
input datafile file number=00002 name=+DATA/rac11g2/datafile/sysaux.257.740770047
channel ch2: starting datafile copy
input datafile file number=00001 name=+DATA/rac11g2/datafile/system.256.740770045
output file name=+DATA/rac11g2s/datafile/system.264.745418537 tag=TAG20110310T114116
channel ch2: datafile copy complete, elapsed time: 00:02:27
channel ch2: starting datafile copy
input datafile file number=00003 name=+DATA/rac11g2/datafile/undotbs1.258.740770049
output file name=+DATA/rac11g2s/datafile/sysaux.275.745418537 tag=TAG20110310T114116
channel ch1: datafile copy complete, elapsed time: 00:02:34
channel ch1: starting datafile copy
input datafile file number=00005 name=+DATA/rac11g2/datafile/undotbs2.264.740770355
output file name=+DATA/rac11g2s/datafile/undotbs1.274.745418685 tag=TAG20110310T114116
channel ch2: datafile copy complete, elapsed time: 00:00:35
channel ch2: starting datafile copy
input datafile file number=00004 name=+DATA/rac11g2/datafile/users.259.740770049
output file name=+DATA/rac11g2s/datafile/undotbs2.273.745418693 tag=TAG20110310T114116
channel ch1: datafile copy complete, elapsed time: 00:00:31
output file name=+DATA/rac11g2s/datafile/users.272.745418721 tag=TAG20110310T114116
channel ch2: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10-MAR-11

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=745418726 file name=+DATA/rac11g2s/datafile/system.264.745418537
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=745418726 file name=+DATA/rac11g2s/datafile/sysaux.275.745418537
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=745418726 file name=+DATA/rac11g2s/datafile/undotbs1.274.745418685
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=745418726 file name=+DATA/rac11g2s/datafile/users.272.745418721
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=745418726 file name=+DATA/rac11g2s/datafile/undotbs2.273.745418693
Finished Duplicate Db at 10-MAR-11

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4
3. Since spfile is not copied from the primary the spfile generated during the duplication won't have any primary db related initializaton parametrs but it will have all the hidden parameters listed same as in rac to single instance duplication. Create a pfile from this genearted spfile and copy the control_files entry added by rman during duplication to initial pfile (initrac11g2s1.ora). During the duplication the control_files entry would have the full OMF path to the controlfiles.
*.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/rac11g2s/controlfile/current.257.745423373','+FLASH/rac11g2s/controlfile/current.257.745423373'#Set by RMAN
*.core_dump_dest='/opt/app/oracle/diag/rdbms/rac11g2s/rac11g2s1/cdump'
*.db_block_size=8192
4. Once the above control_files line is copied to the initial pfile (initrac11g2s1.ora) shutdown the database and start the database in mount mode using this pfile.
shutdown immediate;
startup mount pfile='?/dbs/initrac11g2s1.ora';
5. Create the spfile for all instances with
SQL> create spfile='+DATA/rac11g2s/spfilerac11g2s.ora' from pfile='/opt/app/oracle/product/11.2.0/db_1/dbs/initrac11g2s1.ora';
For making the standby database cluster aware continue from step 22 onwards in the earlier blog.