Oracle cloud Infrastructure (OCI) - Part 2 : OCI -VM Standby database creation

 

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.

Furthermore , OCI provides Oracle Data Guard features address DR (disaster recovery) situations. DG to enables & ensures high availability, data protection, and disaster recovery for your enterprise database.

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.

There are 4 different types of editions in Oracle Cloud

1. Enterprise Edition
2. Standard Edition
3. High-Performance
4. 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)