ORACLE DATA GUARD
Broker Configuration Guide
Step-by-Step Setup & Switchover
Procedures
Primary: ORAP |
Standby: ORAS
1. Pre-Configuration Checks
Before configuring the DataGuard
Broker, verify the database role and switchover status on both the primary and
standby databases.
1.1 Query Database Status
Run the following SQL query on
both PRIMARY and STANDBY:
-- Run on PRIMARY and STANDBY
SELECT
DB_UNIQUE_NAME, DATABASE_ROLE, SWITCHOVER_STATUS, OPEN_MODE
FROM V\$DATABASE;
ℹ️ Confirm DATABASE_ROLE
shows PRIMARY on the primary node and PHYSICAL STANDBY on the standby node
before proceeding.
2. Enable DataGuard Broker
Enable the DataGuard Broker on
both the PRIMARY and STANDBY databases by setting the DG_BROKER_START
parameter.
2.1 Set DG_BROKER_START on Both
Nodes
-- Execute on PRIMARY AND STANDBY
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
⚠️ Run this command on
BOTH servers. Skipping either node will prevent the broker from starting
correctly.
3. Clear Archive Log Destination
on Standby
On the standby server (ORAS),
clear the LOG_ARCHIVE_DEST_2 parameter. The broker will manage log shipping
after configuration.
3.1 Clear LOG_ARCHIVE_DEST_2 (Run on STANDBY — ORAS)
-- Run on STANDBY (oras) only
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='';
4. Create DataGuard Broker
Configuration
Connect to DGMGRL on the PRIMARY
server and create the broker configuration.
4.1 Connect to DGMGRL on Primary
dgmgrl
sys/password as sysdba
4.2 Check Existing Configuration
DGMGRL>
show configuration;
4.3 Create the Broker Configuration
Create a new configuration named
'ora' with the primary database 'orap':
DGMGRL> create configuration 'ora'
as primary database is 'orap'
connect identifier
is orap;
4.4 Add the Standby Database
Add the standby database 'oras' as
a Physical Standby:
DGMGRL> add database 'oras'
as connect identifier is 'oras'
maintained as
physical;
4.5 Verify Configuration
DGMGRL>
show configuration;
5. Set Static Connect
Identifiers
Set the static connect identifiers
for both the primary and standby databases to allow the broker to restart
instances if needed.
5.1 Primary Database (ORAP) — HOST:
WIN19PRIMARY, PORT: 1522
DGMGRL> edit database orap set property
staticconnectidentifier=
'(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=WIN19PRIMARY))
(CONNECT_DATA=
(SERVICE_NAME=ora)(INSTANCE_NAME=ora)(SERVER=DEDICATED)
)
)';
5.2 Standby Database (ORAS) — HOST:
WIN19STANDBY, PORT: 1522
DGMGRL> edit database oras set property
staticconnectidentifier=
'(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=WIN19STANDBY))
(CONNECT_DATA=
(SERVICE_NAME=ora)(INSTANCE_NAME=ora)(SERVER=DEDICATED)
)
)';
6. Set Lag Threshold Properties
Configure the ApplyLagThreshold
and TransportLagThreshold to 0 on both databases to enable alerting on any lag.
6.1 Primary Database Properties
DGMGRL> edit database orap set property ApplyLagThreshold=0;
DGMGRL> edit database orap set property TransportLagThreshold=0;
6.2 Standby Database Properties
DGMGRL> edit database oras set property ApplyLagThreshold=0;
DGMGRL> edit database oras set property TransportLagThreshold=0;
7. Enable Configuration &
Verify
7.1 Enable the Broker Configuration
DGMGRL>
enable configuration;
7.2 Verify Configuration Status
DGMGRL>
show configuration;
ℹ️ The configuration
status should show SUCCESS for both databases. If any errors appear, resolve
them before proceeding to switchover.
8. Perform Switchover
Perform a controlled switchover
from the primary database (ORAP) to the standby database (ORAS).
⚠️ Ensure all application
connections are quiesced before performing a switchover in a production
environment.
8.1 Initiate Switchover to ORAS
DGMGRL>
switchover to 'oras';
8.2 Verify Roles in SQL*Plus After
Switchover
-- Run in SQL*Plus on both nodes
SELECT
DB_UNIQUE_NAME, DATABASE_ROLE, SWITCHOVER_STATUS
FROM V\$DATABASE;
9. Data Replication Verification
After switchover, verify that data
replication is working correctly by creating a test table and inserting rows on
the new primary.
9.1 Create Employee Test Table
DROP TABLE employee;
COMMIT;
CREATE
TABLE employee (
emp_id INT,
emp_name VARCHAR2(12),
date_of_joining TIMESTAMP DEFAULT
systimestamp
);
9.2 Insert Initial Records
INSERT INTO employee (emp_id, emp_name) VALUES (1, 'Rock');
INSERT
INTO employee (emp_id, emp_name) VALUES (2, 'Water');
COMMIT;
ALTER
SYSTEM CHECKPOINT;
ALTER
SYSTEM SWITCH LOGFILE;
ALTER
SYSTEM ARCHIVE LOG CURRENT;
COLUMN
DATE_OF_JOINING FORMAT A30;
SELECT * FROM employee;
9.3 Insert Second Batch & Verify
INSERT INTO employee (emp_id, emp_name) VALUES (3, 'Air');
COMMIT;
ALTER
SYSTEM CHECKPOINT;
ALTER
SYSTEM SWITCH LOGFILE;
ALTER
SYSTEM ARCHIVE LOG CURRENT;
COLUMN
DATE_OF_JOINING FORMAT A30;
SELECT * FROM employee;
9.4 Insert Third Batch & Verify
INSERT INTO employee (emp_id, emp_name) VALUES (4, 'Stone');
COMMIT;
ALTER
SYSTEM CHECKPOINT;
ALTER
SYSTEM SWITCH LOGFILE;
ALTER
SYSTEM ARCHIVE LOG CURRENT;
COLUMN
DATE_OF_JOINING FORMAT A30;
SELECT * FROM employee;
10. Final Status Check
Run the following query to get a
consolidated view of the database role, switchover status, and host
information:
10.1 Comprehensive Status Query
COLUMN DB_UNIQUE_NAME
FORMAT A10;
COLUMN
DATABASE_ROLE FORMAT A20;
COLUMN
SWITCHOVER_STATUS FORMAT A20;
COLUMN
HOST_NAME FORMAT A15;
COLUMN
MACHINE FORMAT A15;
host
cls
SELECT
host_name, db_unique_name, database_role, switchover_status
FROM v\$database, v\$instance;
11. Reference Commands
ℹ️ The following command
is provided for reference only. Use it to start managed recovery on the standby
if needed outside of broker management.
11.1 Start Managed Standby Recovery
(Reference Only)
-- Reference ONLY
ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;
Summary — Configuration Steps
|
Step |
Action |
Description |
|
1 |
Pre-Check
Database Status |
Query V$DATABASE for role,
switchover status, and open mode on both nodes. |
|
2 |
Enable DG
Broker |
Set DG_BROKER_START=TRUE
SCOPE=BOTH on PRIMARY and STANDBY. |
|
3 |
Clear Archive
Dest |
Set LOG_ARCHIVE_DEST_2=''
on the standby server (ORAS). |
|
4 |
Create
Configuration |
Use DGMGRL to create the
'ora' configuration with primary 'orap' and add standby 'oras'. |
|
5 |
Set Static
Connect IDs |
Configure
staticconnectidentifier property for both ORAP and ORAS. |
|
6 |
Set Lag
Thresholds |
Set ApplyLagThreshold and
TransportLagThreshold to 0 on both databases. |
|
7 |
Enable &
Verify |
Run 'enable configuration'
and confirm SUCCESS status. |
|
8 |
Switchover |
Execute switchover to
'oras' and verify database roles in SQL*Plus. |
|
9 |
Test
Replication |
Insert rows into the
employee table and verify data on standby. |
|
10 |
Final Status
Check |
Query v$database and
v$instance for host name, role, and switchover status. |
