Posted by: STAY | March 29, 2012

How to Create a single instance physical standby dataguard for a RAC database

Rac instances are:

[oracle@Rac1 ~]$ olsnodes -n
rac1 1
rac2 2

Data files and logfiles are in one ASM diskgroup called +DATA

Physical standby database will have it’s files in the local file system and will be referred to as STDRAC throughout this post.

1) Create standby directory structure:

[oracle@tstltra u02]$ mkdir -p oradata/STDRAC
[oracle@tstltra u02]$ cd oradata/STDRAC
[oracle@tstltra STDRAC]$ mkdir controlfile datafile onlinelog
[oracle@tstltra STDRAC]$ cd
[oracle@tstltra ~]$ cd /u02
[oracle@tstltra u02]$ mkdir -p flash_recovery/STDRAC
[oracle@tstltra u02]$ mkdir -p flash_recovery/STDRAC/onlinelog

2) Create tns entries on both Rac nodes for standby:

STDRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tstltra)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDRAC)
(SID = STDRAC)
(UR=A)
)
)

3)  Create tns entry on standby server for Rac1

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.localdomain)
)
)

Only one instance will be used to fetch archive log gaps and to send redo when the switch over happens. In a RAC standby multiple instances can receive but there can only be one applier.

4) Create a listener with following listener.ora file on the standby server:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tstltra.localdomain)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STDRAC)
(SID_NAME = STDRAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
)
)

ADR_BASE_LISTENER = /u01/app/oracle

5) Enable Force logging on primary:

SQL> alter database force logging;

Database altered.

6)  Create standby log files for each thread on primary. These should be same size as the online redo log files:

SQL> alter database add standby logfile thread 1;

Database altered.

SQL> alter database add standby logfile thread 2;

Database altered.

7) Add dataguard initialization parameters for primary:

alter system set log_archive_config=’dg_config=(RAC,STDRAC)’ scope=both ;
alter system set log_archive_dest_10=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=RAC’ scope=both;
alter system set log_archive_dest_1=’service=STDRAC LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=STDRAC’ scope=both;
alter system set log_archive_dest_state_1=’enable’ scope=both;
alter system set log_archive_dest_state_10=’enable’ scope=both;
alter system set fal_server=’STDRAC’ scope=both;
alter system set fal_client=’RAC’ scope=both;
alter system set log_archive_max_processes=10 scope=both;
alter system set db_file_name_convert=’/u02/oradata/STDRAC’,’+DATA/RAC’ scope=spfile;
alter system set log_file_name_convert=’/u02/flash_recovery/STDRAC’,’+DATA/RAC’ scope=spfile;
alter system set standby_file_management=’AUTO’ scope=both;

8. Copy the password file to standby host’s ORACLE_HOME/dbs and rename the file. Assuming password file is copied to the standby host

[oracle@Rac1 dbs]$ scp orapwRAC1 tstltra:/u01/app/oracle/product/11.2.0/db/dbs
orapwRAC1 100% 1536 1.5KB/s 00:00

oracle@tstltra dbs]$ mv orapwRAC1 orapwSTDRAC

9) Create standby pfile with following params:

*.audit_file_dest=’/u01/app/oracle/admin/STDRAC/adump’
*.audit_trail=’OS’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/oradata/STDRAC/controlfile/control01.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/u02/oradata’
*.db_domain=’localdomain’
*.db_name=’RAC’
*.db_unique_name=’STDRAC’
*.db_file_name_convert=’+DATA/RAC’,’/u02/oradata/STDRAC’,’+DATA/RAC/tempfile’,’/u02/oradata/STDRAC’
*.log_file_name_convert=’+DATA/RAC’,’/u02/flash_recovery/STDRAC’,’+DATA/RAC’,’/u02/flash_recovery/STDRAC’
*.db_recovery_file_dest=’/data/flash_recovery’
*.db_recovery_file_dest_size=40705720320
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdbyXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_archive_config=’dg_config=(RAC,STDRAC)’
*.log_archive_dest_1=’service=RAC LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=RAC’
*.standby_file_management=’AUTO’
*.log_archive_dest_10=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=STDRAC’
*.open_cursors=300
*.pga_aggregate_target=1326448640
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=3707764736
*.undo_tablespace=’UNDOTBS1′
*.fal_client=’STDRAC’
*.fal_server=’RAC’

SQL> startup nomount pfile=initSTDRAC.ora

10)  Connect to rman target and auxiliary database and create standby from RAC:

[oracle@Rac1 admin]$ rman target / auxiliary sys/tabsserver@STDRAC

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Mar 28 20:41:34 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: RAC (DBID=2365715307)
connected to auxiliary database: RAC (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 28-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0/db/dbs/orapwRAC1’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db/dbs/orapwSTDRAC’ ;
}
executing Memory Script

Starting backup at 28-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 instance=RAC1 device type=DISK
Finished backup at 28-MAR-12

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u02/oradata/STDRAC/controlfile/control01.ctl’;
}
executing Memory Script

Starting backup at 28-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db/dbs/snapcf_RAC1.f tag=TAG20120328T204646 RECID=1 STAMP=779143609
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-MAR-12

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

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/oradata/STDRAC/tempfile/temp.265.777733355”;
switch clone tempfile all;
set newname for datafile 1 to
“/u02/oradata/STDRAC/datafile/system.262.777733307”;
set newname for datafile 2 to
“/u02/oradata/STDRAC/datafile/sysaux.263.777733331”;
set newname for datafile 3 to
“/u02/oradata/STDRAC/datafile/undotbs1.264.777733347”;
set newname for datafile 4 to
“/u02/oradata/STDRAC/datafile/undotbs2.266.777733377”;
set newname for datafile 5 to
“/u02/oradata/STDRAC/datafile/users.267.777733387”;
backup as copy reuse
datafile 1 auxiliary format
“/u02/oradata/STDRAC/datafile/system.262.777733307” datafile
2 auxiliary format
“/u02/oradata/STDRAC/datafile/sysaux.263.777733331” datafile
3 auxiliary format
“/u02/oradata/STDRAC/datafile/undotbs1.264.777733347” datafile
4 auxiliary format
“/u02/oradata/STDRAC/datafile/undotbs2.266.777733377” datafile
5 auxiliary format
“/u02/oradata/STDRAC/datafile/users.267.777733387” ;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/STDRAC/tempfile/temp.265.777733355 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 28-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/rac/datafile/system.262.777733307
output file name=/u02/oradata/STDRAC/datafile/system.262.777733307 tag=TAG20120328T204701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/rac/datafile/sysaux.263.777733331
output file name=/u02/oradata/STDRAC/datafile/sysaux.263.777733331 tag=TAG20120328T204701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.264.777733347
output file name=/u02/oradata/STDRAC/datafile/undotbs1.264.777733347 tag=TAG20120328T204701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/rac/datafile/undotbs2.266.777733377
output file name=/u02/oradata/STDRAC/datafile/undotbs2.266.777733377 tag=TAG20120328T204701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/rac/datafile/users.267.777733387
output file name=/u02/oradata/STDRAC/datafile/users.267.777733387 tag=TAG20120328T204701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-MAR-12

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=1 STAMP=779122191 file name=/u02/oradata/STDRAC/datafile/system.262.777733307
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=779122191 file name=/u02/oradata/STDRAC/datafile/sysaux.263.777733331
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=779122191 file name=/u02/oradata/STDRAC/datafile/undotbs1.264.777733347
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=779122191 file name=/u02/oradata/STDRAC/datafile/undotbs2.266.777733377
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=779122191 file name=/u02/oradata/STDRAC/datafile/users.267.777733387
Finished Duplicate Db at 28-MAR-12

RMAN>

11) Start recovery on standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

Advertisements

Responses

  1. Very good …but a question : in step 7) Add dataguard initialization parameters for primary these parameters modify the spfile ..of only one node or is necessary run in second node of RAC (to modify the spfile of second node ) ?
    Regards !

    • it depends on if you are using a shared spfile or if each rac node is using it’s own spfile in which case you’ll have to update both

      • Ok..i agree…
        ..very thanks for your quickly response !.. 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Categories

%d bloggers like this: