Intro
acronym -
DG - dataguard
OCI - Oracle Cloud Infrastructure
Most important part of the IT Infrastructure is to have a proper disaster recovery plan . Organization are spending colossal sum of money to
have proper DR infrastructure. Best practice is to create your DR site on different city or different geographical location. OCI provides greater flexibility to create DR
within the same region or different region. Even small/medium scale companies can afford this DR sites. These OCI cloud features enable greater enhancement to infrastructure.
Oracle Data Guard provides an extensive set of services that create, maintain,
manage, and monitor one or more standby databases to enable production Oracle
databases to survive disasters (such as natural disasters) and data
corruption.
Active Data Guard vs Normal Data Guard
Oracle Data Guard maintains these standby databases as a cloned version of the production database and regularly applies changes from Primary Database to Standby Database. Then, If the production database becomes unavailable because of a planned or an unplanned outage, Oracle Data Guard can switch any standby database to the primary role, minimizing the downtime associated with the outage.Active Data Guard: Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, Web-based access, and so on, while continuously applying changes received from the primary database. In active data guard standby database work as be read only with apply state.
Note: For Active Data Guard, when you create the database, you need to select the Extreme Performance.
- Enterprise Edition
- Standard Edition
- High-Performance
- Extreme Performance.
Normal Data Guard: Normal Data Guard enables you to switch to the Standby Database when your primary database is down due to some outage or natural disasters. In normal Data guard, you can still open the standby database in read-mode but changes not applied while in read-only mode.
Types of DG : Physical | Logical | Snapshot
- Physical: Redo logs are Shipped & Applied on Standby Database
- Logical: Mainly used for Rolling Upgrades & Redo logs are shipped & transformed to SQL & then they are applied.
- Snapshot: A snapshot Standby Database receives and archives, but does not apply redo logs data from a primary database.
Creating DG in cloud environment now simplify to less than 4 clicks. This article I will cover the how to create standby database within the same region.
First Navigate to the database and view the database this shows the enable
data guard.
As per this figure we need to feed to Display Name , Region and Availability domain ..etc
Next select the required shape for the database server and provide hostname for the standby server.
Important to select the correct network vcn. We are creating this in public segment to access this via internet.
Next select the database mode and transport mode. For this testing I'm selecting Maximum performance and transport mode as Async.
Error
This errors comes when the port 1512 is not enable for destination.
Data Guard Association cannot be created when standard database service port (1521) is
blocked for instances in
Subnet: ocid1.subnet.oc1.ca-toronto-1.aaaaaaaavkfa3xtnoyn4ce5l4g2ttgzf54lhkofaavnaoxiv3irog7ghkika
by security rules associated with
Subnet: ocid1.subnet.oc1.ca-toronto-1.aaaaaaaaceab2pepfsk54ektbyuvfl2rulk4d4luzbfu2iduyr7cmd2gwibq
Solution
To overcome this we need to create two rules for one for Ingress and one for Egress. Port 1512 should be available to communicate with standby server in region level.
Follow the same steps above to create the standby database.
Once the provisioning is complete check the status from work request.
Verification
We can validate data guard status using dgmgrl utility . Also it's better to check the database role by login to database.
Database Role :
INSTANCE_NAME STATUS HOST_NAME STARTUP_TIME
---------------- ------------ ---------------------------------------------------------------- ----------------
PWSH011 MOUNTED dbsdpl251 26/10/2021 20:55
PWSH012 MOUNTED dbsdpl252 26/10/2021 20:55
SQL> select open_mode,database_role from gv$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
MOUNTED PHYSICAL STANDBY
SQL>
DGMRRL Utility :
#### DG status
DGMGRL> show configuration;
Configuration - PWSH01_yyz16x_PWSH01_yyz1k6
Protection Mode: MaxPerformance
Members:
PWSH01_yyz16x - Primary database
PWSH01_yyz1k6 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 22 seconds ago)
DGMGRL> show database verbose 'PWSH01_yyz1k6';
Database - PWSH01_yyz1k6
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: 4.00 KByte/s
Active Apply Rate: 198.00 KByte/s
Maximum Apply Rate: 198.00 KByte/s
Real Time Query: ON
Instance(s):
PWSH011
PWSH012 (apply instance)
