Objective: Setup Dataguard broker for heterogenous oracle dataguard
Youtube Video:
Steps:
1. Create directory in ASM, in DATA.dg and for both the broker files
directory 1: +DATA/<dbuniquename>/DG
directory 2: +FRA/<dbuniquename>/DG
In our case, 2 directories in the same mountpath
primary: +DATA/ORACL19C/DG1 +DATA/ORACL19C/DG2 Standby: /oradata/ORACL19CSB1/DG1 /oradata/ORACL19CSB1/DG2 Output: primary: ASMCMD> pwd +data/ORACL19C ASMCMD> ls -l Type Redund Striped Time Sys Name Y ARCHIVELOG/ Y AUTOBACKUP/ Y CONTROLFILE/ Y DATAFILE/ N DG1/ N DG2/ Y ONLINELOG/ Y PARAMETERFILE/ Y PASSWORD/ Y TEMPFILE/ ASMCMD> standby: [oracle@vcentos79-oracle-ggtgt DG2]$ ls -ld /oradata/ORACL19CSB1/DG1 /oradata/ORACL19CSB1/DG2 drwxr-xr-x. 2 oracle oinstall 6 Nov 1 14:55 /oradata/ORACL19CSB1/DG1 drwxr-xr-x. 2 oracle oinstall 6 Nov 1 14:55 /oradata/ORACL19CSB1/DG2 [oracle@vcentos79-oracle-ggtgt DG2]$ |
2. Set the dg_broker_config_file1 and dg_broker_config_file2 parameters on all primary and standby dbs.
Primary:
alter system set dg_broker_config_file1='+DATA/ORACL19C/DG1/dr1ORACL19C.dat';
alter system set dg_broker_config_file2='+DATA/ORACL19C/DG2/dr2ORACL19C.dat';
Standby:
alter system set dg_broker_config_file1='/oradata/ORACL19CSB1/DG1/dr1ORACL19CSB1.dat';
alter system set dg_broker_config_file2='/oradata/ORACL19CSB1/DG2/dr2ORACL19CSB2.dat';
Primary op: SQL> create pfile='/home/oracle/dba/SbySetup/pfileORACL19C_01nov24.ora' from spfile; File created. SQL> alter system set dg_broker_config_file1='+DATA/ORACL19C/DG1/dr1ORACL19C.dat'; System altered. SQL> alter system set dg_broker_config_file2='+DATA/ORACL19C/DG2/dr2ORACL19C.dat'; System altered. SQL> sho parameter broker NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1)) dg_broker_config_file1 string +DATA/ORACL19C/DG1/dr1ORACL19C .dat dg_broker_config_file2 string +DATA/ORACL19C/DG2/dr2ORACL19C .dat dg_broker_start boolean FALSE use_dedicated_broker boolean FALSE SQL> Standby op: SQL> create pfile='/home/oracle/dba/SBYSetup/pfileORACL19C_01nov24.ora' from spfile; File created. SQL> alter system set dg_broker_config_file1='/oradata/ORACL19CSB1/DG1/dr1ORACL19CSB1.dat'; System altered. SQL> alter system set dg_broker_config_file2='/oradata/ORACL19CSB1/DG2/dr2ORACL19CSB2.dat'; System altered. SQL> sho parameter broker NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1)) dg_broker_config_file1 string /oradata/ORACL19CSB1/DG1/dr1OR ACL19CSB1.dat dg_broker_config_file2 string /oradata/ORACL19CSB1/DG2/dr2OR ACL19CSB2.dat dg_broker_start boolean FALSE use_dedicated_broker boolean FALSE SQL> |
3. Store the log_archive_dest_2 parameter value in both primary and standby
For example for DBTST01:
Primary: log_archive_dest_2 string SERVICE=ORACL19CSB1 ASYNC VALI D_FOR=(ONLINE_LOGFILES,PRIMARY _ROLE) DB_UNIQUE_NAME=ORACL19C SB1 alter system set log_archive_dest_2='SERVICE=ORACL19CSB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACL19CSB1' scope=both sid='*'; Standby: log_archive_dest_2 string service=ORACL19C ASYNC valid_f or=(ONLINE_LOGFILE,PRIMARY_ROL E) db_unique_name=ORACL19C alter system set log_archive_dest_2='service=ORACL19C ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ORACL19C' scope=both sid='*'; |
4. Reset log_archive_dest_2 parameter in both primary and standby
For example in ORACL19C:
Both primary and standby:
alter system set log_archive_dest_2='';
primary op: SQL> sho parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string standby op: SQL> sho parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string |
5. Start dataguard broker in primary and standby
For example in ORACL19C:
Both primary and standby:
alter system set dg_broker_start=TRUE;
primary op: SQL> alter system set dg_broker_start=TRUE; System altered. SQL> sho parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE SQL> sby op: SQL> alter system set dg_broker_start=TRUE; System altered. SQL> sho parameter dg_broker_start NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE SQL> |
6. Connect to dgmgrl as sys user in both primary and standby
For example in ORACL19C:
Both primary and standby:
dgmgrl
connect sys/<password>
7. Create configuration on primary
For example in ORACL19C:
Primary: CREATE CONFIGURATION 'ORACL19C_CONFIGURATION' AS PRIMARY DATABASE IS 'ORACL19C' CONNECT IDENTIFIER IS ORACL19C; primary op: DGMGRL> connect sys@ORACL19C Password: Connected to "ORACL19C" Connected as SYSDBA. DGMGRL> CREATE CONFIGURATION 'ORACL19C_CONFIGURATION' AS PRIMARY DATABASE IS 'ORACL19C' CONNECT IDENTIFIER IS ORACL19C; Configuration "ORACL19C_CONFIGURATION" created with primary database "ORACL19C" DGMGRL> show configuration; Configuration - ORACL19C_CONFIGURATION Protection Mode: MaxPerformance Members: ORACL19C - Primary database Fast-Start Failover: Disabled Configuration Status: DISABLED |
8. Add standby database to the configuration
For example in ORACL19C:
Primary:
ADD DATABASE 'ORACL19CSB1' AS CONNECT IDENTIFIER IS ORACL19CSB1 MAINTAINED AS PHYSICAL;
primary op: DGMGRL> ADD DATABASE 'ORACL19CSB1' AS CONNECT IDENTIFIER IS ORACL19CSB1 MAINTAINED AS PHYSICAL; Database "ORACL19CSB1" added DGMGRL> show configuration; Configuration - ORACL19C_CONFIGURATION Protection Mode: MaxPerformance Members: ORACL19C - Primary database ORACL19CSB1 - Physical standby database Fast-Start Failover: Disabled Configuration Status: DISABLED DGMGRL> |
9. Set the log_archive_dest_2 parameter now in both the primary and standby db using the information we stored in step (3)
primary op: SQL> alter system set log_archive_dest_2='SERVICE=ORACL19CSB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACL19CSB1' scope=both sid='*'; System altered. SQL> set lines 1200 pages 3000 SQL> sho parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=ORACL19CSB1 ASYNC VALI D_FOR=(ONLINE_LOGFILES,PRIMARY _ROLE) DB_UNIQUE_NAME=ORACL19C SB1 sby op: SQL> alter system set log_archive_dest_2='service=ORACL19C ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ORACL19C' scope=both sid='*'; System altered. SQL> sho parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service=ORACL19C ASYNC valid_f or=(ONLINE_LOGFILE,PRIMARY_ROL E) db_unique_name=ORACL19C |
10. Enable the dataguard configuration in primary:
ENABLE CONFIGURATION;
primary op: DGMGRL> ENABLE CONFIGURATION; Enabled. DGMGRL> show configuration; Configuration - ORACL19C_CONFIGURATION Protection Mode: MaxPerformance Members: ORACL19C - Primary database Warning: ORA-16905: The member was not enabled yet. ORACL19CSB1 - Physical standby database Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 234 seconds ago) DGMGRL> show configuration; Configuration - ORACL19C_CONFIGURATION Protection Mode: MaxPerformance Members: ORACL19C - Primary database Warning: ORA-16905: The member was not enabled yet. ORACL19CSB1 - Physical standby database Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 243 seconds ago) DGMGRL> show configuration; Configuration - ORACL19C_CONFIGURATION Protection Mode: MaxPerformance Members: ORACL19C - Primary database ORACL19CSB1 - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 44 seconds ago) DGMGRL> |
11. Launch the show configuration command in primary:
show configuration;
primary op: DGMGRL> show configuration; Configuration - ORACL19C_CONFIGURATION Protection Mode: MaxPerformance Members: ORACL19C - Primary database ORACL19CSB1 - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 44 seconds ago) DGMGRL> |
12. show database verbose <db_unique_name>;
to have a detailed view of the db setting in dgmgrl
primary op: DGMGRL> show database ORACL19C; Database - ORACL19C Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): ORACL19C1 ORACL19C2 Database Status: SUCCESS DGMGRL> show database ORACL19CSB1; Database - ORACL19CSB1 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 3.00 KByte/s Real Time Query: ON Instance(s): ORACL19CSB1 Database Status: SUCCESS DGMGRL> Verbose op preserved below: DGMGRL> show database verbose ORACL19C; Database - ORACL19C Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): ORACL19C1 ORACL19C2 Properties: DGConnectIdentifier = 'oracl19c' ObserverConnectIdentifier = '' FastStartFailoverTarget = '' PreferredObserverHosts = '' LogShipping = 'ON' RedoRoutes = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '30' TransportLagThreshold = '30' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' ApplyInstances = '0' StandbyFileManagement = '' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '0' LogArchiveMinSucceedDest = '0' DataGuardSyncLatency = '0' LogArchiveTrace = '0' LogArchiveFormat = '' DbFileNameConvert = '' LogFileNameConvert = '' ArchiveLocation = '' AlternateLocation = '' StandbyArchiveLocation = '' StandbyAlternateLocation = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' LogXptStatus = '(monitor)' SendQEntries = '(monitor)' RecvQEntries = '(monitor)' HostName(*) StaticConnectIdentifier(*) TopWaitEvents(*) SidName(*) (*) - Please check specific instance for the property value Log file locations(*): (*) - Check specific instance for log file locations. Database Status: SUCCESS DGMGRL> show database verbose ORACL19CSB1; Database - ORACL19CSB1 Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 3.00 KByte/s Active Apply Rate: 2.11 MByte/s Maximum Apply Rate: 2.23 MByte/s Real Time Query: ON Instance(s): ORACL19CSB1 Properties: DGConnectIdentifier = 'oracl19csb1' ObserverConnectIdentifier = '' FastStartFailoverTarget = '' PreferredObserverHosts = '' LogShipping = 'ON' RedoRoutes = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '30' TransportLagThreshold = '30' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' ApplyInstances = '0' StandbyFileManagement = '' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '0' LogArchiveMinSucceedDest = '0' DataGuardSyncLatency = '0' LogArchiveTrace = '0' LogArchiveFormat = '' DbFileNameConvert = '' LogFileNameConvert = '' ArchiveLocation = '' AlternateLocation = '' StandbyArchiveLocation = '' StandbyAlternateLocation = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' LogXptStatus = '(monitor)' SendQEntries = '(monitor)' RecvQEntries = '(monitor)' HostName = 'vcentos79-oracle-ggtgt' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vcentos79-oracle-ggtgt)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORACL19CSB1_DGMGRL)(INSTANCE_NAME=ORACL19CSB1)(SERVER=DEDICATED)))' TopWaitEvents = '(monitor)' SidName = '(monitor)' Log file locations: Alert log : /u01/app/oracle/diag/rdbms/oracl19csb1/ORACL19CSB1/trace/alert_ORACL19CSB1.log Data Guard Broker log : /u01/app/oracle/diag/rdbms/oracl19csb1/ORACL19CSB1/trace/drcORACL19CSB1.log Database Status: SUCCESS DGMGRL> |
13. Verify the pfile difference between pre/post dg broker config
primary op: [oracle@vcentos79-oracle-rac1 SbySetup]$ diff pfileORACL19C_01nov24.ora pfileORACL19C_post.ora 37a38,40 ..<removed known diff> < *.fal_server='ORACL19CSB1' --- > *.fal_server='' 47c50,51 < *.log_archive_dest_2='SERVICE=ORACL19CSB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACL19CSB1' --- > *.log_archive_dest_2='service="oracl19csb1"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="ORACL19CSB1" net_timeout=30','valid_for=(online_logfile,all_roles)' > *.log_archive_dest_state_2='ENABLE' [oracle@vcentos79-oracle-rac1 SbySetup]$ [oracle@vcentos79-oracle-ggtgt SBYSetup]$ diff pfileORACL19C_01nov24.ora pfileORACL19CSB1_post.ora 51a52,54 ..<removed known diff> 54c57 < *.fal_server='ORACL19C' --- > *.fal_server='oracl19c' 60c63 < *.log_archive_dest_2='service=ORACL19C ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ORACL19C' --- > *.log_archive_dest_2='' [oracle@vcentos79-oracle-ggtgt SBYSetup]$ |
DGMGRL performed the following changes:
So in the primary fal_server is removed from primary and log_archive_Dest_2 is removed from sby and log_Archive_Dest_2 is modified on primary
Thanks
No comments:
Post a Comment