Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 21c (2024)

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 21c » Here

Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. This article gives an example of the setup and administration of Data Guard using the Data Guard Broker.

TL;DR
Assumptions
Primary Server Setup
- Automatic Config
- Logging
- Initialization Parameters
- Service Setup
Standby Server Setup
- Prepare for Duplicate
- Create Standby using DUPLICATE
Enable Broker
Stop/Start Managed Recovery
Database Switchover
Database Failover
Flashback Database
Read-Only Standby and Active Data Guard
Snapshot Standby

TL;DR

If you already know about Data Guard and want to quickly set up a demo environment using VirtualBox and Vagrant you can follow the instructions in my GitHub repository.

Assumptions

You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I've used Oracle Linux 8 and Oracle Database 21c.
The primary server (ol8-21-dg1.locadomain) has a running instance.
The standby server (ol8-21-dg2.locadomain) has a software only installation.
There is nothing blocking communication between the machines over the listener ports. If you are using the default 1521 port, node 1 should be able to communicate to node 2 on 1521 and node 2 should be able communicate with node 1 on 1521. Check network and local firewalls are not blocking the communication.

Primary Server Setup

Automatic Config

From Oracle 21c onward the PREPARE DATABASE FOR DATA GUARD command simplifies the setup of the primary database. Some of the steps below will not be necessary if you use this command. That will be noted in each section. Here is an example of using the automatic config that is consistent with the rest of this article.

mkdir -p ${ORACLE_BASE}/fast_recovery_areadgmgrl / <<EOFprepare database for data guard with db_unique_name is cdb1 db_recovery_file_dest is "${ORACLE_BASE}/fast_recovery_area" db_recovery_file_dest_size is 20g;exit;EOF

Logging

The steps in this section are done automatically by the PREPARE DATABASE FOR DATA GUARD command. If you are using that approach, please ignore this section.

Check that the primary database is in archivelog mode.

select log_mode from v$database;LOG_MODE------------NOARCHIVELOGSQL>

If it is noarchivelog mode, switch is to archivelog mode.

shutdown immediate;startup mount;alter database archivelog;alter database open;

Enabled forced logging by issuing the following command.

alter database force logging;-- Make sure at least one logfile is present.alter system switch logfile;

Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.

-- If Oracle Managed Files (OMF) is used.alter database add standby logfile thread 1 group 10 size 50m;alter database add standby logfile thread 1 group 11 size 50m;alter database add standby logfile thread 1 group 12 size 50m;alter database add standby logfile thread 1 group 13 size 50m;-- If Oracle Managed Files (OMF) is not used.alter database add standby logfile thread 1 group 10 ('/u01/oradata/cdb1/standby_redo01.log') size 50m;alter database add standby logfile thread 1 group 11 ('/u01/oradata/cdb1/standby_redo02.log') size 50m;alter database add standby logfile thread 1 group 12 ('/u01/oradata/cdb1/standby_redo03.log') size 50m;alter database add standby logfile thread 1 group 13 ('/u01/oradata/cdb1/standby_redo04.log') size 50m;

If you want to use flashback database, enable it on the primary now, so it will be enabled on the standby also. It's very useful as you will see below.

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "cdb1" on the primary database.

SQL> show parameter db_nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_name string cdb1SQL> show parameter db_unique_nameNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_unique_name string cdb1SQL>

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. For this example, the standby database will have the value "cdb1_stby".

Make sure the STANDBY_FILE_MANAGEMENT parameter is set. This will already be set if you are using the PREPARE DATABASE FOR DATA GUARD command.

alter system set standby_file_management=auto;

Service Setup

Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup. Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.

cdb1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-dg1)(PORT = 1521)) ) (CONNECT_DATA = (SID = cdb1) ) )cdb1_stby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-dg2)(PORT = 1521)) ) (CONNECT_DATA = (SID = cdb1) ) )

The "$ORACLE_HOME/network/admin/listener.ora" file on the primary server contains the following configuration.

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-dg1)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb1_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/21.0.0/db_1) (SID_NAME = cdb1) (ENVS="TNS_ADMIN=/u01/app/oracle/product/21.0.0/db_1/network/admin") ) )ADR_BASE_LISTENER = /u01/app/oracle

The "$ORACLE_HOME/network/admin/listener.ora" file on the standby server contains the following configuration. Since the broker will need to connect to the database when it's down, we can't rely on auto-registration with the listener, hence the explicit entry for the database.

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-dg2)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb1_stby_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/21.0.0/db_1) (SID_NAME = cdb1) (ENVS="TNS_ADMIN=/u01/app/oracle/product/21.0.0/db_1/network/admin") ) )ADR_BASE_LISTENER = /u01/app/oracle

Once the listener.ora changes are in place, restart the listener on both servers.

lsnrctl stoplsnrctl start

Standby Server Setup

Prepare for Duplicate

Create a parameter file for the standby database called "/tmp/initcdb1_stby.ora" with the following contents.

*.db_name='cdb1'

Create the necessary directories on the standby server.

mkdir -p /u01/app/oracle/oradata/cdb1/pdbseedmkdir -p /u01/app/oracle/oradata/cdb1/pdb1mkdir -p /u01/app/oracle/fast_recovery_area/cdb1mkdir -p /u01/app/oracle/admin/cdb1/adump

Create a password file, with the SYS password matching that of the primary database.

$ orapwd file=/u01/app/oracle/product/21.0.0/db_1/dbs/orapwcdb1 password=Password1 entries=10

Create Standby Using DUPLICATE

Start the auxiliary instance on the standby server by starting it using the temporary "init.ora" file.

$ export ORACLE_SID=cdb1$ sqlplus / as sysdbaSQL> STARTUP NOMOUNT PFILE='/tmp/initcdb1_stby.ora';

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication.

$ rman TARGET sys/Password1@cdb1 AUXILIARY sys/Password1@cdb1_stby

Now issue the following DUPLICATE command.

duplicate target database for standby from active database dorecover spfile set db_unique_name='cdb1_stby' COMMENT 'Is standby' nofilenamecheck;

If you need to convert file locations, or alter any initialisation parameters, you can do this during the DUPLICATE using the SET command.

duplicate target database for standby from active database dorecover spfile set db_unique_name='cdb1_stby' COMMENT 'Is standby' set db_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/' set log_file_name_convert='/original/directory/path1/','/new/directory/path1/','/original/directory/path2/','/new/directory/path2/' set job_queue_processes='0' nofilenamecheck;

A brief explanation of the individual clauses is shown below.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafiles, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start using the broker.

Enable Broker

At this point we have a primary database and a standby database, so now we need to start using the Data Guard Broker to manage them. Connect to both databases (primary and standby) and issue the following command. This will already be set if you used the PREPARE DATABASE FOR DATA GUARD command.

alter system set dg_broker_start=true;

On the primary server, issue the following command to register the primary server with the broker.

$ dgmgrl sys/Password1@cdb1DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Aug 15 11:07:59 2021Version 21.3.0.0.0Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1"Connected as SYSDBA.DGMGRL> create configuration my_dg_config as primary database is cdb1 connect identifier is cdb1;Configuration "my_dg_config" created with primary database "cdb1"DGMGRL>

Now add the standby database.

DGMGRL> add database cdb1_stby as connect identifier is cdb1_stby;Database "cdb1_stby" addedDGMGRL>

Now we enable the new configuration.

DGMGRL> enable configuration;Enabled.DGMGRL>

The following commands show how to check the configuration and status of the databases from the broker.

DGMGRL> show configuration;Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database Warning: ORA-16905: The member was not enabled yet. cdb1_stby - Physical standby database Warning: ORA-16905: The member was not enabled yet.Fast-Start Failover: DISABLEDConfiguration Status:SUCCESS (status updated 26 seconds ago)DGMGRL> show database cdb1;Database - cdb1 Role: PRIMARY Intended State: TRANSPORT-ON Redo Rate: 35.36 KByte/s in 60 seconds (computed 1 second ago) Instance(s): cdb1Database Status:SUCCESSDGMGRL> show database cdb1_stby;Database - cdb1_stby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 9 seconds ago) Apply Lag: 0 seconds (computed 9 seconds ago) Average Apply Rate: 8.00 KByte/s Real Time Query: OFF Instance(s): cdb1Database Status:SUCCESSDGMGRL>

Stop/Start Managed Recovery

Managed recovery can be stopped and started on the standby database using the following commands from SQL*Plus.

-- Stop managed recovery.alter database recover managed standby database cancel;-- Start managed recovery.alter database recover managed standby database disconnect;

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands. Connect to the primary database (cdb1) and switchover to the standby database (cdb1_stby).

$ dgmgrl sys/Password1@cdb1DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Aug 15 11:16:30 2021Version 21.3.0.0.0Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1"Connected as SYSDBA.DGMGRL> switchover to cdb1_stby;2021-08-15T11:16:59.114+00:00Performing switchover NOW, please wait...2021-08-15T11:16:59.936+00:00Operation requires a connection to database "cdb1_stby"Connecting ...Connected to "cdb1_stby"Connected as SYSDBA.2021-08-15T11:17:00.201+00:00Continuing with the switchover...2021-08-15T11:17:11.815+00:00New primary database "cdb1_stby" is opening...2021-08-15T11:17:11.815+00:00Operation requires start up of instance "cdb1" on database "cdb1"Starting instance "cdb1"...Connected to an idle instance.ORACLE instance started.Connected to "cdb1"Database mounted.Connected to "cdb1"2021-08-15T11:17:34.492+00:00Switchover succeeded, new primary is "cdb1_stby"2021-08-15T11:17:34.515+00:00Switchover processing complete, broker ready.DGMGRL>

Let's switch back to the original primary. Connect to the new primary (cdb1_stby) and switchover to the new standby database (cdb1).

$ dgmgrl sys/Password1@cdb1_stbyDGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Aug 15 11:18:40 2021Version 21.3.0.0.0Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1_stby"Connected as SYSDBA.DGMGRL> switchover to cdb1;2021-08-15T11:18:50.935+00:00Performing switchover NOW, please wait...2021-08-15T11:18:51.757+00:00Operation requires a connection to database "cdb1"Connecting ...Connected to "cdb1"Connected as SYSDBA.2021-08-15T11:18:52.036+00:00Continuing with the switchover...2021-08-15T11:19:00.533+00:00New primary database "cdb1" is opening...2021-08-15T11:19:00.533+00:00Operation requires start up of instance "cdb1" on database "cdb1_stby"Starting instance "cdb1"...Connected to an idle instance.ORACLE instance started.Connected to "cdb1_stby"Database mounted.Connected to "cdb1_stby"2021-08-15T11:19:19.789+00:00Switchover succeeded, new primary is "cdb1"2021-08-15T11:19:19.810+00:00Switchover processing complete, broker ready.DGMGRL>

Database Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements. Connect to the standby database (cdb1_stby) and failover.

$ dgmgrl sys/Password1@cdb1_stbyDGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Aug 15 11:24:36 2021Version 21.3.0.0.0Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1_stby"Connected as SYSDBA.DGMGRL> failover to cdb1_stby;2021-08-15T11:24:46.851+00:00Performing failover NOW, please wait...2021-08-15T11:25:08.354+00:00Failover succeeded, new primary is "cdb1_stby".2021-08-15T11:25:08.354+00:00Failover processing complete, broker ready.DGMGRL>

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.

DGMGRL> reinstate database cdb1;2021-08-15T11:26:01.868+00:00Reinstating database "cdb1", please wait...2021-08-15T11:26:10.779+00:00Operation requires shut down of instance "cdb1" on database "cdb1"Shutting down instance "cdb1"...Connected to "cdb1"ORACLE instance shut down.2021-08-15T11:26:12.293+00:00Operation requires start up of instance "cdb1" on database "cdb1"Starting instance "cdb1"...Connected to an idle instance.ORACLE instance started.Connected to "cdb1"Database mounted.Connected to "cdb1"2021-08-15T11:26:25.950+00:00Continuing to reinstate database "cdb1" ...2021-08-15T11:26:47.061+00:00Reinstatement of database "cdb1" succeeded2021-08-15T11:26:47.061+00:00Reinstate processing complete, broker ready.DGMGRL>

If flashback database is not enabled, you would have to manually recreate cdb1 as a standby. The basic process is the reverse of what you did previously.

# 1) Cleanup the old instance.sqlplus / as sysdba <<EOFshutdown immediate;exit;EOF rm -Rf /u01/app/oracle/oradata/cdb1/*rm -Rf /u01/app/oracle/fast_recovery_area/cdb1rm -Rf /u01/app/oracle/fast_recovery_area/cdb1_stbyrm -Rf /u01/app/oracle/admin/cdb1mkdir -p /u01/app/oracle/fast_recovery_area/cdb1mkdir -p /u01/app/oracle/admin/cdb1/adumpmkdir -p /u01/app/oracle/oradata/cdb1/pdbseedmkdir -p /u01/app/oracle/oradata/cdb1/pdb1rm $ORACLE_HOME/dbs/spfilecdb1.oraexport ORACLE_SID=cdb1sqlplus / as sysdba <<EOFstartup nomount pfile='/tmp/initcdb1_stby.ora';exit;EOF# 2) Connect to RMAN.$ rman target sys/Password1@cdb1_stby auxiliary sys/Password1@cdb1# 3) Duplicate the database.duplicate target database for standby from active database dorecover spfile set db_unique_name='cdb1' COMMENT 'Is standby' nofilenamecheck;# 4) Connect to DGMDRL on the current primary.$ dgmgrl sys/Password1@cdb1_stby# 5) Enable the new standby.DGMGRL> enable database cdb1;

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database, as shown above.

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

To switch the standby database into read-only mode, do the following.

shutdown immediate;startup mount;alter database open read only;

To resume managed recovery, do the following.

shutdown immediate;startup mount;alter database recover managed standby database disconnect from session;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

shutdown immediate;startup mount;alter database open read only;alter database recover managed standby database disconnect from session;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Snapshot Standby

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

Connect to the primary (cdb1) database and convert the standby database (cdb1_stby) to a snapshot standby.

$ dgmgrl sys/Password1@cdb1DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Aug 15 11:29:39 2021Version 21.3.0.0.0Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1"Connected as SYSDBA.DGMGRL> convert database cdb1_stby to snapshot standby;2021-08-15T11:29:56.599+00:00Converting database "cdb1_stby" to a Snapshot Standby database, please wait...2021-08-15T11:30:02.308+00:00Database "cdb1_stby" converted successfully2021-08-15T11:30:02.308+00:00DGMGRL>

When you are finished with the snapshot standby, convert it back to a standby database.

$ dgmgrl sys/Password1@cdb1DGMGRL for Linux: Release 21.0.0.0.0 - Production on Tue Feb 26 22:53:36 2018Version 21.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected as SYSDBA.DGMGRL> convert database cdb1_stby to physical standby;DGMGRL for Linux: Release 21.0.0.0.0 - Production on Sun Aug 15 11:31:00 2021Version 21.3.0.0.0Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected to "cdb1"Connected as SYSDBA.DGMGRL> convert database cdb1_stby to physical standby;2021-08-15T11:31:10.844+00:00Converting database "cdb1_stby" to a Physical Standby database, please wait...2021-08-15T11:31:10.912+00:00Operation requires shut down of instance "cdb1" on database "cdb1_stby"Shutting down instance "cdb1"...Connected to "cdb1_stby"Database closed.Database dismounted.ORACLE instance shut down.2021-08-15T11:31:18.433+00:00Operation requires start up of instance "cdb1" on database "cdb1_stby"Starting instance "cdb1"...Connected to an idle instance.ORACLE instance started.Connected to "cdb1_stby"Database mounted.Connected to "cdb1_stby"2021-08-15T11:31:27.139+00:00Continuing to convert database "cdb1_stby" ...2021-08-15T11:31:45.774+00:00Database "cdb1_stby" converted successfully2021-08-15T11:31:45.774+00:00DGMGRL>

The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.

DGMGRL> show configuration;Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby databaseFast-Start Failover: DisabledConfiguration Status:SUCCESS (status updated 34 seconds ago)DGMGRL>

For more information see:

Data Guard Broker
Creating a Physical Standby Database
Oracle Data Guard Broker
Data Guard Quick Links : 11gR2, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Data Guard : The PREPARE DATABASE FOR DATA GUARD Command in Oracle Database 21c

Hope this helps. Regards Tim...

Back to the Top.