Data Guard Physical Setup using Data Broker in Oracle Database 19c
Environments
- You have two servers (VMs or physical) with an operating system and Oracle installed on them. My environment I’ve used Oracle Linux 7.6 and Oracle Database 19c.
- The primary server (duts-dg1) has a running instance.
- The standby server (duts-dg2) has a software only installation.
- There is nothing blocking communication between the machines over the listener ports.
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;
LOG_MODE
————
NOARCHIVELOG
SQL>
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 not used.
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo01.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo02.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo03.log’) SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo04.log’) SIZE 100M;
— If Oracle Managed Files (OMF) is used.
ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;
If you want to user flashback database, enable it on the primary now, so it will be enabled on the standby also. I always use it in my environments.
ALTER DATABASE FLASHBACK ON;
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “duts” on the primary database.
SQL> show parameter db_name
NAME TYPE VALUE
———————————— ———– ——————————
db_name string duts
SQL> show parameter db_unique_name
NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string duts
SQL>
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 “duts_stby”.
Make sure the STANDBY_FILE_MANAGEMENT parameter is set.
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.
duts =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = duts)
)
)
duts_stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = duts)
)
)
The “$ORACLE_HOME/network/admin/listener.ora” file on the primary server contains the following configuration.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = duts_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
(SID_NAME = duts)
)
)
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 = duts-dg2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = duts_stby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
(SID_NAME = duts)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Once the listener.ora changes are in place, restart the listener on both servers.
lsnrctl stop
lsnrctl start
Standby Server Setup
Prepare for Duplicate
Create a parameter file for the standby database called “/tmp/initduts_stby.ora” with the following contents.
*.db_name=’duts’
Create the necessary directories on the standby server.
mkdir -p /u02/data/duts/pdbseed
mkdir -p /u02/data/duts/pdb1
mkdir -p /u02/app/oracle/fast_recovery_area/duts
mkdir -p /u02/app/oracle/admin/duts/adump
Create a password file, with the SYS password matching that of the primary database.
$ orapwd file=/u01/app/oracle/product/19.0.0/db_1/dbs/orapwduts password=oracle 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=duts
$ sqlplus / as sysdba
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/oracle@duts AUXILIARY sys/oracle@duts_stby
Now issue the following DUPLICATE command.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name=’duts_stby’ COMMENT ‘Is standby 19c’
NOFILENAMECHECK;
If you need to convert file locations, or alter any initialization 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=’duts_stby’ COMMENT ‘Is standby 19c’
SET db_file_name_convert=’/u01/data/duts/’,’/u02/data/duts/’
SET log_file_name_convert=’/u01/data/duts/’,’/u02/data/duts/’
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.
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/oracle@duts
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:39:33 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS duts CONNECT IDENTIFIER IS duts;
Configuration “dg_config” created with primary database “duts”
DGMGRL>
Now add the standby database.
DGMGRL> ADD DATABASE duts_stby AS CONNECT IDENTIFIER IS duts_stby MAINTAINED AS PHYSICAL;
Database “duts_stby” added
DGMGRL>
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 – dg_config
Protection Mode: MaxPerformance
Members:
duts – Primary database
duts_stby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 26 seconds ago)
DGMGRL> SHOW DATABASE duts;
Database – duts
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
duts
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE duts_stby;
Database – duts_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 15.00 KByte/s
Real Time Query: OFF
Instance(s):
duts
Database Status:
SUCCESS
DGMGRL>
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 (duts) and switchover to the standby database (duts_stby).
$ dgmgrl sys/oracle@duts
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:55:33 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO duts_stby;
Performing switchover NOW, please wait…
Operation requires a connection to instance “duts” on database “duts_stby”
Connecting to instance “duts”…
Connected as SYSDBA.
New primary database “duts_stby” is opening…
Operation requires start up of instance “duts” on database “duts”
Starting instance “duts”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “duts_stby”
DGMGRL>
Let’s switch back to the original primary. Connect to the new primary (duts_stby) and switchover to the new standby database (duts).
$ dgmgrl sys/oracle@duts_stby
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:57:20 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO duts;
Performing switchover NOW, please wait…
Operation requires a connection to instance “duts” on database “duts”
Connecting to instance “duts”…
Connected as SYSDBA.
New primary database “duts” is opening…
Operation requires start up of instance “duts” on database “duts_stby”
Starting instance “duts”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “duts”
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 (duts_stby) and failover.
$ dgmgrl sys/oracle@duts_stby
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 15:00:20 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected as SYSDBA.
DGMGRL> FAILOVER TO duts_stby;
Performing failover NOW, please wait…
Failover succeeded, new primary is “duts_stby”
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 duts;
Reinstating database “duts”, please wait…
Operation requires shut down of instance “duts” on database “duts”
Shutting down instance “duts”…
ORACLE instance shut down.
Operation requires start up of instance “duts” on database “duts”
Starting instance “duts”…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “duts” …
Reinstatement of database “duts” succeeded
DGMGRL>
If flashback database is not enabled, you would have to manually recreate duts as a standby. The basic process is the reverse of what you did previously.
# 1) Cleanup the old instance.
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
rm -Rf /u01/data/duts/*
rm -Rf /u01/app/oracle/fast_recovery_area/duts
rm -Rf /u01/app/oracle/fast_recovery_area/duts_stby
rm -Rf /u01/app/oracle/admin/duts
mkdir -p /u01/app/oracle/fast_recovery_area/duts
mkdir -p /u01/app/oracle/admin/duts/adump
mkdir -p /u01/data/duts/pdbseed
mkdir -p /u01/data/duts/pdb1
rm $ORACLE_HOME/dbs/spfileduts.ora
export ORACLE_SID=duts
sqlplus / as sysdba <<EOF
STARTUP NOMOUNT PFILE=’/tmp/initduts_stby.ora’;
EXIT;
EOF
# 2) Connect to RMAN.
$ rman TARGET sys/oracle@duts_stby AUXILIARY sys/oracle@duts
# 3) Duplicate the database.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name=’duts’ COMMENT ‘Is standby 19c’
SET db_file_name_convert=’/u02/data/duts/’,’/u01/data/duts/’
SET log_file_name_convert=’/u02/data/duts/’,’/u01/data/duts/’
SET job_queue_processes=’0′
NOFILENAMECHECK;
# 4) Connect to DGMDRL on the current primary.
$ dgmgrl sys/oracle@duts_stby
# 5) Enable the new standby.
DGMGRL> ENABLE DATABASE duts;
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.
Creation of application services
To facilitate the administration of client connections, and to make SWITCHOVER operations more transparent for clients, it is recommended to create database SERVICES.
Example, service definition « DUTSS » :
begin DBMS_SERVICE.CREATE_SERVICE ( service_name => ‘DUTSS’,
network_name => ‘ DUTSS ‘,
failover_method => ‘BASIC’,
failover_type => ‘SELECT’,
failover_retries => 180,
failover_delay => 1);
end;
/
In this case, there are 180 retries and a delay of 1 second (so basically 3 minutes before switching). This should be adapted depending on your needs and requirements.
These are the services that should be used by client application connections.
Creating the Startup trigger
To manage the automatic start of the services, in particular in the event of a role transition, the following TRIGGER must be created (example for the DUTSS service). The trigger must be created under SYS:
Connect SYS as SYSDBA
CREATE OR REPLACE TRIGGER manage_app_services
AFTER STARTUP
ON DATABASE
DECLARE
role VARCHAR (30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = ‘PRIMARY’
THEN
DBMS_SERVICE.START_SERVICE (‘DUTSS’);
END IF;
END;
/
Then we restart the PRIMARY database to check that the service is started:
sqlplus / as sysdba
shutdown immediate ;
startup
Client connections
To make the role transitions (as a result of a SWITCHOVER or FAILOVER) transparent to users the client database connection string needs to be configured with a failover connection string.
This can be configured at the TNSNAMES.ORA file level by configuring two addresses or two descriptions for the same alias.
Example of an alias defined for DUTS:
DUTS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = DUTSS))
)
With this method, clients will attempt to first connect to the first address (corresponding to the primary server and database). If it is operational (which should be the case in a nominal way), the connection is established with this base.
If this first address does not respond (primary server unavailable or services stopped), then the client tries to connect to the second address (pointing to the standby server). If it is operational (which will be the case only after a SWITCHOVER or a FAILOVER), then the client will connect to the emergency base transparently and automatically.
Hope this helps. See you !!!
André Ontalba – http://www.dbadutra.com
