Active Dataguard Setup using ASM and RMAN
Data Guard ModesOracle Data Guard can operate in 3 different modes:
- Maximum Protection: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will shutdown to ensure maximum protection mode.
- Maximum Performance: Transactions are not allowed to commit as soon as the redo are written to the online redo logs. The redo stream is asynchronously propagated to the secondary databases to ensure maximum performance mode.
- Maximum Availability: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.
These are the steps to follow:
- Enable forced logging
- Create a password file
- Configure a standby redo log
- Enable archiving
- Set up the primary database initialization parameters
- Configure the listener and tnsnames to support the database on both nodes
Oracle 11g Active Dataguard Setup Steps using ASM & RMAN
#############################################
Database Name :- sbi
Primary db_unique_name :- sbi
Standby db_unique_name :- sbistby
############################################
Step 1
Set Parameter for LOG_ARCHIVE_CONFIG
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTMP1,TESTMP2)';
Step 2
Set primary Log Archive Destination
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/sbi VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sbi';
Step 3
Set standby log archive destination
alter system set LOG_ARCHIVE_DEST_2='SERVICE=sbistby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbistby';
Step 4
Enable LOG_ARCHIVE_DEST_1
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
Step 5
Setup FAL_SERVER & FAL_CLIENT
alter system set FAL_SERVER=sbistby;
alter system set FAL_CLIENT=sbi;
Step 6
Set Parameters for FILE_NAME_CONVERT
alter system set DB_FILE_NAME_CONVERT='+DGCHICAGO/','+DGTEXAS/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+DGCHICAGO/','+DG2TEXAS' scope=spfile;
Step 7
*On standby* Add listener entry
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sbistby)
(ORACLE_HOME = /u01/oradb/db1)
(SID_NAME = sbistby)
)
)
Step 8
Add entries to tnsnames.ora to both nodes
sbi =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.108.256.249)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = sbi))
)
sbistby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.108.256.251)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = sbistby))
)
Step 9
Copy Password file onto standby or create password file with same password
copy password file
orapwd file=/u01/oradb/db1/dbs/orapwsbi force=y password=sys
orapwd file=/u01/oradb/db1/dbs/orapwsbistby force=y password=sys
Step 10
Create Standby Pfile
Only one parameter needed - > DB_NAME
DB_NAME=sbistby
Step 11
Startup Standby
sqlplus / as sysdba
startup nomount
Step 12
On primary system
rman target sys/sys@sbi auxiliary sys/sys@sbistby
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'sbi','sbistby'
set db_unique_name='sbistby'
set db_file_name_convert='+DGCHICAGO/','+DGTEXAS/'
set log_file_name_convert='+DGCHICAGO/','+DGTEXAS/'
set control_files='+DGTEXAS/control01.ctl'
set log_archive_max_processes='5'
set fal_client='sbistby'
set fal_server='sbi'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(sbi,sbistby)'
set log_archive_dest_2='SERVICE=sbistby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbistby' ;
}
Step 14
*On standby* For active Dataguard
STANDBY> alter database recover managed standby database cancel;
STANDBY> alter database open;
STANDBY> alter database recover managed standby database disconnect;
#############################################
Database Name :- sbi
Primary db_unique_name :- sbi
Standby db_unique_name :- sbistby
############################################
Step 1
Set Parameter for LOG_ARCHIVE_CONFIG
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTMP1,TESTMP2)';
Step 2
Set primary Log Archive Destination
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/sbi VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sbi';
Step 3
Set standby log archive destination
alter system set LOG_ARCHIVE_DEST_2='SERVICE=sbistby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbistby';
Step 4
Enable LOG_ARCHIVE_DEST_1
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
Step 5
Setup FAL_SERVER & FAL_CLIENT
alter system set FAL_SERVER=sbistby;
alter system set FAL_CLIENT=sbi;
Step 6
Set Parameters for FILE_NAME_CONVERT
alter system set DB_FILE_NAME_CONVERT='+DGCHICAGO/','+DGTEXAS/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+DGCHICAGO/','+DG2TEXAS' scope=spfile;
Step 7
*On standby* Add listener entry
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sbistby)
(ORACLE_HOME = /u01/oradb/db1)
(SID_NAME = sbistby)
)
)
Step 8
Add entries to tnsnames.ora to both nodes
sbi =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.108.256.249)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = sbi))
)
sbistby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.108.256.251)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = sbistby))
)
Step 9
Copy Password file onto standby or create password file with same password
copy password file
orapwd file=/u01/oradb/db1/dbs/orapwsbi force=y password=sys
orapwd file=/u01/oradb/db1/dbs/orapwsbistby force=y password=sys
Step 10
Create Standby Pfile
Only one parameter needed - > DB_NAME
DB_NAME=sbistby
Step 11
Startup Standby
sqlplus / as sysdba
startup nomount
Step 12
On primary system
rman target sys/sys@sbi auxiliary sys/sys@sbistby
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'sbi','sbistby'
set db_unique_name='sbistby'
set db_file_name_convert='+DGCHICAGO/','+DGTEXAS/'
set log_file_name_convert='+DGCHICAGO/','+DGTEXAS/'
set control_files='+DGTEXAS/control01.ctl'
set log_archive_max_processes='5'
set fal_client='sbistby'
set fal_server='sbi'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(sbi,sbistby)'
set log_archive_dest_2='SERVICE=sbistby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbistby' ;
}
Step 14
*On standby* For active Dataguard
STANDBY> alter database recover managed standby database cancel;
STANDBY> alter database open;
STANDBY> alter database recover managed standby database disconnect;
Archive log Deletion Policy
Configured in RMAN to tell RMAN when to delete archive logs. i.e after shipping or after apply. For example,
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Or
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Or
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;