Emerson Lima

This article is Part 2 of a two-part series that describes the steps for Setting Active Data Guard Physical Standby in RAC One Node Architecture. You can find part 1 here.

1. When duplicate database process is finished (Part 1 of the article), add Standby database in Clusterware

 
srvctl add database -d analytics_dg -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -c RACONENODE -p '+DG_DATA/analytics_DG/PARAMETERFILE/spfileanalytics_dg.ora' -r PHYSICAL_STANDBY -s MOUNT -n analytics_dg -e hostdr

2. shut down a database

sqlplus> shutdown immediate;

3. Copy “Oracle password file” with new instance name “orapwanalyticsdg_1”

cd $ORACLE_HOME/dbs
cp orapwanalytics_dg orapwanalyticsdg_1

4. Start database

$ srvctl start database -d analytics_dg
$ srvctl config database -d analytics_dg
Database unique name: analytics_dg
Database name: analytics_dg
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DG_DATA/analytics_DG/PARAMETERFILE/spfileanalytics_dg.ora
Password file:
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DG_DATA,DG_RECO
Mount point paths:
Services:
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: analyticsdg
Candidate servers: hostdr
OSDBA group: sdba
OSOPER group: soper
Database instances:
Database is administrator managed

5. According to Oracle Restart’s pattern, analytics_dg instance will be called analyticsdg_1 therefore, listener.ora must be updated.

Run “listener reload” command in order to force listener to read the “listener.ora” entries again. Attention! This operation will affect listener’s availability.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = analytics_dg_dgmgrl)
# (SID_NAME = analytics_dg)
(SID_NAME = analyticsdg_1)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
)
)

6. Redo Transport Service will be manually enabled to validate connections and settings.

Parameters DB_BLOCK_CHECKSUM, DB_BLOCK_CHECKING and DB_LOST_WRITE_PROTECT will be set up for both databases, primary and standby, to prevent and detect corrupted blocks.

6.1. Adjust parameters in Standby database

sqlplus> alter system set log_archive_config='dg_config=(analytics,analytics_dg)' scope=both sid='*' ;
sqlplus> alter system SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_ROLES, ALL_LOGFILES) db_unique_name=analytics_dg' scope=both sid='*' ;
sqlplus> alter system set log_archive_dest_2='service=analytics LGWR ASYNC NOAFFIRM max_failure=10 max_connections=1 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=analytics' scope=both sid='*';
sqlplus> alter system set fal_server='analytics' scope=both sid='*';
sqlplus> alter system set fal_client='analytics_dg' scope=both sid='*';
sqlplus> alter system set log_archive_max_processes=4 scope=both sid='*';
sqlplus> alter system set standby_file_management='AUTO' scope=both sid='*';
sqlplus> alter system set db_file_name_convert='+DG_DATA_DR/analytics','+DG_DATA/analytics_DG','+DG_RECO_DR/analytics','+DG_RECO/analytics_DG' scope=spfile sid='*';
sqlplus> alter system set log_file_name_convert='+DG_DATA_DR/analytics','+DG_DATA/analytics_DG','+DG_RECO_DR/analytics','+DG_RECO/analytics_DG' scope=spfile sid='*';
sqlplus> alter system set db_block_checksum=FULL scope=both sid='*' ;
sqlplus> alter system set db_block_checking=MEDIUM scope=both sid='*' ;
sqlplus> alter system set db_lost_write_protect=TYPICAL scope=both sid='*' ;
sqlplus> alter system set log_archive_dest_state_1='enable' scope=both sid='*';
sqlplus> alter system set log_archive_dest_state_2='enable' scope=both sid='*';

6.2. Adjust parameters in Primary database:

sqlplus> alter system set log_archive_config='dg_config=(analytics,analytics_dg)' scope=both sid='*' ;
sqlplus> alter system SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_ROLES,ALL_LOGFILES) db_unique_name=analytics' scope=both sid='*' ;
sqlplus> alter system set log_archive_dest_2='service=analytics_dg LGWR ASYNC NOAFFIRM max_failure=10 max_connections=1 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=analytics_dg' scope=both sid='*';
sqlplus> alter system set fal_server='analytics_dg' scope=both sid='*';
sqlplus> alter system set fal_client='analytics' scope=both sid='*';
sqlplus> alter system set log_archive_max_processes=4 scope=both sid='*' ;
sqlplus> alter system set standby_file_management='AUTO' scope=both sid='*' ;
sqlplus> alter system set db_file_name_convert='+DG_DATA/analytics_DG','+DG_DATA_DR/analytics','+DG_RECO/analytics_DG','+DG_RECO_DR/analytics' scope=spfile sid='*';
sqlplus> alter system set log_file_name_convert='+DG_DATA/analytics_DG','+DG_DATA_DR/analytics','+DG_RECO/analytics_DG','+DG_RECO_DR/analytics' scope=spfile sid='*';
sqlplus> alter system set db_block_checksum=TYPICAL scope=both sid='*' ;
sqlplus> alter system set db_block_checking=MEDIUM scope=both sid='*' ;
sqlplus> alter system set db_lost_write_protect=TYPICAL scope=both sid='*' ;
sqlplus> alter system set log_archive_dest_state_1='enable' scope=both sid='*' ;
sqlplus> alter system set log_archive_dest_state_2='enable' scope=both sid='*' ;

6.3. The “status” attribute from Primary and Standby must return ‘VALID’

sqlplus> select dest_id,status,destination ,error from v$archive_dest where dest_id <=5;
 ID DB_status Archive_dest Error
---------- --------- ----------------------------- -------
1 VALID USE_DB_RECOVERY_FILE_DEST
2 VALID analytics_dg
3 INACTIVE
4 INACTIVE
5 INACTIVE

sqlplus> select dest_id,status,database_mode,recovery_mode from v$archive_dest_status where status <> 'INACTIVE';
 DEST_ID STATUS DATABASE_MODE RECOVERY_MODE
---------- --------- --------------- -----------------------
1 VALID OPEN IDLE
2 VALID MOUNTED-STANDBY IDLE

6.4. Manually start Redo Apply:

sqlplus> alter database recover managed standby database using current logfile disconnect;

6.5. Synchronization between Primary and Standby Database validation

sqlplus> select * from v$archive_gap;
sqlplus> select name, value, datum_time, time_computed from v$dataguard_stats where name like 'apply lag';
sqlplus> select file_type, number_of_files, percent_space_used from v$recovery_area_usage;
sqlplus> select current_scn from v$database;
sqlplus> select sequence#, first_time, applied from v$archived_log order by sequence#;

6.6. Stop Redo Apply before setting up Data Guard Broker

sqlplus> alter database recover managed standby database cancel;
Database altered.

7. Set up Data Guard Broker in Primary and Standby Database

Note: Data Guard setting files will be created in ASM in different diskgroups: dg_broker_config_file1 and dg_broker_config_file2

7.1. In Primary database:

sqlplus> alter system set dg_broker_config_file1 = '+DG_DATA_DR/analytics/BROKERCFG/brokeranalytics1.dat' scope=both sid='*';
sqlplus> alter system set dg_broker_config_file2 = '+DG_RECO_DR/analytics/BROKERCFG/brokeranalytics2.dat' scope=both sid='*';
sqlplus> alter system set log_archive_dest_2='' scope=both sid='*' ;
sqlplus> alter system set DG_BROKER_START=FALSE scope=both sid='*';
sqlplus> alter system set DG_BROKER_START=TRUE scope=both sid='*';

7.2. In Standby database:

sqlplus> alter system set dg_broker_config_file1 = '+DG_DATA/analytics_DG/BROKERCFG/brokeranalytics_dg1.dat' scope=both sid='*';
sqlplus> alter system set dg_broker_config_file2 = '+DG_RECO/analytics_DG/BROKERCFG/brokeranalytics_dg2.dat' scope=both sid='*';
sqlplus> alter system set log_archive_dest_2='' scope=both sid='*' ;
sqlplus> alter system set DG_BROKER_START=FALSE scope=both sid='*';
sqlplus> alter system set DG_BROKER_START=TRUE scope=both sid='*';

7.3. Broker settings:

$ dgmgrl
dgmgrl> connect sys/xxxxx@analytics
Connected as SYSDBA.
dgmgrl> create configuration 'analytics_cfg' as primary database is 'analytics' connect identifier is analytics;
Configuration "analytics_cfg" created with primary database "analytics"
dgmgrl> add database 'analytics_dg' as connect identifier is analytics_dg maintained as physical;
Database "analytics_dg" added
dgmgrl> edit database 'analytics_dg' set property logxptmode=async;
Property "logxptmode" updated
dgmgrl> edit configuration set protection mode as MAXPERFORMANCE;
Succeeded.
dgmgrl> enable configuration; 
Enabled.

Use SHOW CONFIGURATION command to show a brief settings summary.

dgmgrl> show configuration

Configuration - analytics_cfg
Protection Mode: MaxPerformance
Databases:
analytics - Primary database
analytics_dg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

7.4. Validate settings with commands

dgmgrl> show database verbose 'analytics_dg' 'RecvQEntries';
dgmgrl> show database verbose 'analytics';
dgmgrl> show database verbose 'analytics_dg';

Node 1:

sqlplus> select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
--------- -------------------- -------------------- -------------------- -----------
analytics READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

Node 2:

sqlplus> select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVE DATABASE_ROLE
--------- -------------------- -------------------- -------------------- -----------
analytics READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY

Standby DB:

sqlplus> select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
--------- ---------------- -------------------- -------------------- ----------------
analytics MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY

8. Adjusts on Broker will be made to settings “MaxConnections”, “TransportDisconnectedThreshold” and “TransportLagThreshold” [1]

“MaxConnections” property specifies how many ARCn processes will be used in parallel to transfer data from a redo log file to the remote site when there is a GAP in the process. If MaxConnections is defined with a value higher than 1, redo transport services uses multiple ARCn processes to transfer data from a redo log file to standby.

“TransportDisconnectedThreshold” property can be used to create a warning status to a logical or physical waiting or instant capture when the last primary database communication exceeds the property specified value. The property’s value is expressed in seconds.

“TransportLagThreshold” property can be used to create a warning status to a logical or physical waiting or instant capture when the database transportation delay exceeds the property specified value. The property’s value is expressed in seconds.

dgmgrl> edit database 'analytics' SET PROPERTY 'MaxConnections'= 4;
Property "MaxConnections" updated
dgmgrl> edit database 'analytics' SET PROPERTY TransportDisconnectedThreshold='180';
Property "transportdisconnectedthreshold" updated
dgmgrl> edit database 'analytics' SET PROPERTY TransportLagThreshold='900';
Property "transportlagthreshold" updated
dgmgrl> edit database 'analytics_dg' SET PROPERTY 'MaxConnections'= 4;
Property "MaxConnections" updated
dgmgrl> edit database 'analytics_dg' SET PROPERTY TransportDisconnectedThreshold='180';
Property "transportdisconnectedthreshold" updated
dgmgrl> edit database 'analytics_dg' SET PROPERTY TransportLagThreshold='900';
Property "transportlagthreshold" updated

9. Change exclusion policy archivelogs in primary database.

rman> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

Active Dataguard activation

10. Stop Redo Apply in broker

$ dgmgrl
dgmgrl> connect sys
dgmgrl> edit database 'analytics_dg' set state = 'apply-off';
dgmgrl> show database 'analytics_dg'

11. Open standby instance in read only mode.

sqlplus> alter database open read only;

12. Start Redo Apply in broker

$ dgmgrl
dgmgrl> connect sys
dgmgrl> edit database 'analytics_dg' set state = 'apply-on';
dgmgrl> show database 'analytics_dg'

References