Oracle Active Data Guard Features and Benefits

Oracle Active Data Guard enhances the performance of production databases by offloading resource-consuming operations to one or more standby databases. This protects interactive users and critical business tasks from the impact of long-running operations. 

Active Data Guard enables a physical standby database to be used for real-time reporting with minimal latency. Compared to traditional replication methods, it is simple to use and has very good performance.

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes or archived logs received from the production database.

Active Data Guard also enables fast incremental backups when offloading backups to a standby database, and can provide additional benefits of high availability and disaster protection against planned or unplanned outages at the production site.

Features

• Physical Standby with Real-Time Query
• Fast Incremental Backup on Physical Standby

Benefits

• Increase performance– Offload unpredictable workloads to an up-to-date replica of the production database
• Simplify operations– Eliminate management complexity that accompanies traditional replication solutions
• Eliminate compromise– The reporting replica is up to date and online at all times, which is not possible with traditional storage mirroring technology
• Reduce cost– An Oracle Active Data Guard physical standby database can also provide disaster recovery and/or serve as a test database – no additional storage or servers required
• Enables a standby database for use in real-time for reporting
• Offloads backup operations
• Insulates critical operations from unexpected system impacts
• Provides high availability and disaster protection

Read More

Oracle Dataguard Broker Configuration (DGMGRL)

Data Guard Broker is a command-line interface that makes managing primary and standby databases easy. DBA can use a single command to switch from primary and standby and vice versa.

Several pre-defined commands can be used to manage Data Guard. Data guard configuration is one of the steps in the primary and standby database setup.

 

The steps below will describe the configuration and other uses

 

Step 1: Add the configuration file to both the databases (primary/standby)

 

Configuration files save the status of the primary and standby database

 

SQL> show parameter DG_BROKER_START

 

NAME                                 TYPE        VALUE

----------------------------------- ----------- --------------------

dg_broker_start                      boolean     TRUE

 

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

 

System altered.

 

SQL> alter system set dg_broker_config_file1='/u01/oracle/prim_db/dguard/dg1_prim_db.cfg' scope=both;

 

System altered.

 

SQL> alter system set dg_broker_config_file2='/u01/oracle/prim_db/dguard/dg2_prim_db.cfg' scope=both;

 

System altered.

 

Perform similar steps on the Standby side as well

 

Step 2: Set up of DG_BROKER_START parameter on the primary and standby databases

 

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

 

System altered.

 

SQL> show parameter DG_BROKER_START

 

NAME                                 TYPE        VALUE

---------------------------------- ----------- --------------------

dg_broker_start                      boolean     TRUE

 

Step 3: Set up local listener parameters on the primary and the standby.

 

Primary-> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST =prim_server.pfizer.com)(PORT = 1522))' scope=both;

 

Standby-> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST =standby_server.pfizer.com)(PORT = 1522))' scope=both;

 

Step 4: DGMGRL configuration

 

Connect to primary db through dgmgrl

 

prim_server.pfizer.com-prim_db $ dgmgrl /

DGMGRL for Linux: Release 19.20.0.0.0 - Production on Wed Apr 09 04:52:13 2025

 

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

 

Welcome to DGMGRL, type "help" for information.

Connected to "prim_db"

Connected as SYSDG.

DGMGRL> create configuration prim_db_conf as Primary Database is 'prim_db' connect identifier is prim_db.pfizer.com;

Configuration "prim_db_conf" created with primary database "prim_db"

DGMGRL>

DGMGRL> ADD DATABASE 'standby_db' AS CONNECT IDENTIFIER IS standby_db.pfizer.com MAINTAINED AS PHYSICAL;

Database "standby_db" added

DGMGRL>

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration;

 

Configuration - prim_db_conf

 

  Protection Mode: MaxPerformance

  Members:

  prim_db - Primary database

  standby_db - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS   (status updated 4 seconds ago)

 

Output should be SUCCESS; if not, we need to rectify the issues.

 

 

Step 5:  Monitor and manage and Data Guard Broker configuration

 

Check configuration status:

DGMGRL> show configuration;

 

Check configuration details of each database

DGMGRL> show database 'prim_db';

DGMGRL> show database 'standby_db';

 

Enable or Disable configuration for individual database

DGMGRL> enable database 'prim_db';

DGMGRL> disable database 'standby_db';

 

Useful Commands in DGMGRL

 

CREATE CONFIGURATION

ADD DATABASE

EDIT DATABASE

SHOW DATABASE

ENABLE CONFIGURATION

SWITCHOVER TO

FAILOVER TO

Read More

Oracle ASM Scrubbing

What is ASM Scrubbing?

 

ASM scrubbing is a process used to identify and repair data corruption issues within an ASM disk group. ASM Scrubbing scans the data stored in the disk group and checks for inconsistencies or corruption. The process typically involves reading each data block, comparing it to expected values, and correcting errors by relying on redundancy mechanisms.

 

The Importance of ASM Scrubbing

 

1. Data Integrity

2. Performance Optimization

3. Redundancy Assurance

4. Preventing Data Loss

5. Proactive Health Monitoring

 

How ASM Scrubbing Works

 

ASM scrubbing uses the redundancy features inherent in the ASM system to detect and correct errors.

The process typically works as follows:

 

1. Scanning Data Blocks: ASM scrubbing scans all the data blocks in a disk group, checking for any signs of corruption, such as bit errors or inconsistencies between mirrored copies.

 

2. Verifying Data Integrity: It cross-references each block with its mirror or redundant copy. If a mismatch is found between the blocks, it flags this as a potential error.

 

3. Correcting Errors: If errors are found, ASM attempts to correct them by retrieving data from the redundant copy (if available). For example, in a mirrored setup, if one disk’s data is corrupted, the data from the other disk is used to restore the correct information.

 

4. Logging and Reporting: During the scrubbing process, any identified errors are logged and reported to the system administrators. This allows administrators to monitor and assess the system’s health over time.

 

5. Automated vs. Manual Scrubbing: While ASM scrubbing can be configured to run automatically on a scheduled basis, it can also be manually initiated if needed. This flexibility allows system administrators to choose when to run the scrub, depending on system load and maintenance schedules.

 

Below commands can be used to perform scrubbing

ALTER DISKGROUP Dg_NAME SCURB POWER LOW;

ALTER DISKGROUP Dg_NAME SCURB FILE 'file-name' REPAIR POWER HIGH FORCE;

ALTER DISKGROUP Dg_NAME SCURB DISK Disk-name  REPAIR POWER HIGH FORCE;

 

REPAIR - this option allows automatic repairs disk corruptions; progress can be seen using V$ASM_OPERATION view in the database

 

Read More

ORACLE : Creating a new pluggable database using PDB$SEED

As a database administrator, you might come across creating a pluggable database, here is the method to create a pluggable database (PDB), Follow the below steps to create a pluggable database using the default pluggable database PDB$SEED

Step 1: Connect to the Container database using admin privileges

 

[oracle@localhost Desktop]$ sqlplus sys/***@free as sysdba

 

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sun Mar 24 06:50:54 2024

Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

 

 

 

Step 2 : Check existing container databases including pluggable databases

SQL> show pdbs

 

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

       2 PDB$SEED               READ ONLY  NO

       3 FREEPDB1               READ WRITE NO

 

Here we can see one default pluggable database and one application pdb (freepdb1)

Check the container id of each container database, this will help to identify datafiles/temp file etc associated with a particular container database

 

SQL> select name,con_id,dbid from v$containers;

 

NAME              CON_ID       DBID

-------------------- ---------- ----------

CDB$ROOT                1 1408182090

PDB$SEED                2  871956951  <-- create a new pdb using this

FREEPDB1                3 1423580300

 

Check the datafile and temp file associated with PDB$SEED to identify the path of datafiles for the new database

SQL> select name from v$datafile where con_id=2;

NAME

-----------------------------------------------------------------

/opt/oracle/oradata/FREE/pdbseed/system01.dbf

/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf

/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf

                              

SQL> select name from v$tempfile where con_id=2;

NAME

-----------------------------------------------------------------

/opt/oracle/oradata/FREE/pdbseed/temp01.dbf

 

 

Step 3: Create a directory for new pluggable database datafiles

 

[oracle@localhost Desktop]$

[oracle@localhost Desktop]$ mkdir -p /opt/oracle/oradata/FREE/pdb2

[oracle@localhost Desktop]$

 

 

Step 4 : Create a pluggable database

 

SQL> create pluggable database PDB2 admin user pdb2_user identified by "test123"

default tablespace PDB2_DEFAULT

datafile '/opt/oracle/oradata/FREE/pdb2/PDB2_DEFAULT.dbf' size 100m autoextend on

file_name_convert=('/opt/oracle/oradata/FREE/pdbseed','/opt/oracle/oradata/FREE/pdb2');  2    3    4 

 

Pluggable database created.

 

SQL> show pdbs

 

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

       2 PDB$SEED               READ ONLY  NO

       3 FREEPDB1               READ WRITE NO

       4 PDB2                   MOUNTED

 

Step 5: Open pluggable database

 

SQL> alter pluggable database PDB2 open;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

       2 PDB$SEED               READ ONLY  NO

       3 FREEPDB1               READ WRITE NO

       4 PDB2                   READ WRITE NO

 

Here we can see PDB2 pluggable database has been created successfully.

Read More

lsb_release: command not found... : Linux/ Database Admin

As Database Admin, Linux Admin, or system admin, you must have come across this error or situation, 

Here I was trying to see release details of the Linux operating system using the lsb_release command but it failed with the below error. 

On seeing this error, I thought lsb_release was not installed on the server, so I tried to install using the yum command but it failed with the error "Error: Unable to find a match: lsb_release" 

[root@localhost bin]# lsb_release 

bash: lsb_release: command not found...

 

[root@localhost bin]# yum install lsb_release

Last metadata expiration check: 0:07:24 ago on Sat 30 Dec 2023 11:04:11 AM UTC.

No match for argument: lsb_release

Error: Unable to find a match: lsb_release

[root@localhost bin]# 

 

So here is the trick, you need to identify which package provides this command using the below method

[root@localhost bin]# yum whatprovides lsb_release

Last metadata expiration check: 0:05:10 ago on Sat 30 Dec 2023 11:04:11 AM UTC.

redhat-lsb-core-4.1-47.0.1.el8.i686 : LSB Core module support

Repo        : ol8_appstream

Matched from:

Filename    : /usr/bin/lsb_release

redhat-lsb-core-4.1-47.0.1.el8.x86_64 : LSB Core module support

Repo        : ol8_appstream

Matched from:

Filename    : /usr/bin/lsb_release

[root@localhost bin]# 

 

Here we see package redhat-lsb-core provides the lsb_release command, so to access lsb_release we need to install the redhat-lsb-core package, let's try to install it

 

[root@localhost bin]# yum install redhat-lsb-core

Last metadata expiration check: 0:12:20 ago on Sat 30 Dec 2023 11:04:11 AM UTC.

Dependencies resolved.

==============================================================================================================================================================

 Package                                        Architecture               Version                                Repository                             Size

==============================================================================================================================================================

Installing:

 redhat-lsb-core                                x86_64                     4.1-47.0.1.el8                         ol8_appstream                          46 k

Installing dependencies:

 m4                                             x86_64                     1.4.18-7.el8                           ol8_baseos_latest                     222 k

 mailx                                          x86_64                     12.5-29.el8                            ol8_baseos_latest                     257 k

 ncurses-compat-libs                            x86_64                     6.1-9.20180224.el8                     ol8_baseos_latest                     328 k

 patch                                          x86_64                     2.7.6-11.el8                           ol8_baseos_latest                     139 k

 postfix                                        x86_64                     2:3.5.8-7.el8                          ol8_baseos_latest                     1.5 M

 redhat-lsb-submod-security                     x86_64                     4.1-47.0.1.el8                         ol8_appstream                          22 k

 spax                                           x86_64                     1.5.3-13.el8                           ol8_baseos_latest                     217 k

 

Transaction Summary

==============================================================================================================================================================

Install  8 Packages

 

Total download size: 2.7 M

Installed size: 7.0 M

Is this ok [y/N]: y

Downloading Packages:

(1/8): mailx-12.5-29.el8.x86_64.rpm                                                                                           980 kB/s | 257 kB     00:00    

(2/8): m4-1.4.18-7.el8.x86_64.rpm                                                                                             820 kB/s | 222 kB     00:00    

(3/8): ncurses-compat-libs-6.1-9.20180224.el8.x86_64.rpm                                                                      1.1 MB/s | 328 kB     00:00    

(4/8): patch-2.7.6-11.el8.x86_64.rpm                                                                                          1.3 MB/s | 139 kB     00:00    

(5/8): spax-1.5.3-13.el8.x86_64.rpm                                                                                           2.1 MB/s | 217 kB     00:00    

(6/8): redhat-lsb-core-4.1-47.0.1.el8.x86_64.rpm                                                                              691 kB/s |  46 kB     00:00    

(7/8): redhat-lsb-submod-security-4.1-47.0.1.el8.x86_64.rpm                                                                   468 kB/s |  22 kB     00:00    

(8/8): postfix-3.5.8-7.el8.x86_64.rpm                                                                                         5.6 MB/s | 1.5 MB     00:00    

--------------------------------------------------------------------------------------------------------------------------------------------------------------

Total                                                                                                                         4.9 MB/s | 2.7 MB     00:00     

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

  Preparing        :                                                                                                                                      1/1 

  Installing       : redhat-lsb-submod-security-4.1-47.0.1.el8.x86_64                                                                                     1/8 

  Installing       : spax-1.5.3-13.el8.x86_64                                                                                                             2/8 

  Running scriptlet: spax-1.5.3-13.el8.x86_64                                                                                                             2/8 

  Running scriptlet: postfix-2:3.5.8-7.el8.x86_64                                                                                                         3/8 

  Installing       : postfix-2:3.5.8-7.el8.x86_64                                                                                                         3/8 

  Running scriptlet: postfix-2:3.5.8-7.el8.x86_64                                                                                                         3/8 

  Installing       : patch-2.7.6-11.el8.x86_64                                                                                                            4/8 

  Installing       : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                                                                        5/8 

  Installing       : mailx-12.5-29.el8.x86_64                                                                                                             6/8 

  Installing       : m4-1.4.18-7.el8.x86_64                                                                                                               7/8 

  Running scriptlet: m4-1.4.18-7.el8.x86_64                                                                                                               7/8 

  Installing       : redhat-lsb-core-4.1-47.0.1.el8.x86_64                                                                                                8/8 

  Running scriptlet: redhat-lsb-core-4.1-47.0.1.el8.x86_64                                                                                                8/8 

  Verifying        : m4-1.4.18-7.el8.x86_64                                                                                                               1/8 

  Verifying        : mailx-12.5-29.el8.x86_64                                                                                                             2/8 

  Verifying        : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                                                                        3/8 

  Verifying        : patch-2.7.6-11.el8.x86_64                                                                                                            4/8 

  Verifying        : postfix-2:3.5.8-7.el8.x86_64                                                                                                         5/8 

  Verifying        : spax-1.5.3-13.el8.x86_64                                                                                                             6/8 

  Verifying        : redhat-lsb-core-4.1-47.0.1.el8.x86_64                                                                                                7/8 

  Verifying        : redhat-lsb-submod-security-4.1-47.0.1.el8.x86_64                                                                                     8/8 

 

Installed:

  m4-1.4.18-7.el8.x86_64          mailx-12.5-29.el8.x86_64                 ncurses-compat-libs-6.1-9.20180224.el8.x86_64       patch-2.7.6-11.el8.x86_64   

  postfix-2:3.5.8-7.el8.x86_64    redhat-lsb-core-4.1-47.0.1.el8.x86_64    redhat-lsb-submod-security-4.1-47.0.1.el8.x86_64    spax-1.5.3-13.el8.x86_64    

 

Complete!

[root@localhost bin]# 

 

After installation, I was able to access it 

 

[root@localhost bin]# lsb_release 

LSB Version:   :core-4.1-amd64:core-4.1-noarch

[root@localhost bin]# 

 

Read More