1. General information
With the ever increasing level of security, the need to keep up to date with the latest patches of the Oracle database also increases.
Patching of an Oracle database becomes easier and easier with the release of Oracle 19c and the AutoUpgrade tool, but that subject is covered in another document.
In this document we describe the setup of a Dataguard environment and the patching of it. At Kramp we use Dataguard for the RMAN catalog database, to ensure that this database is always available. The RMAN catalog database is a very important database, as without it backups of a database can not be created.
Next to the setup of the Dataguard environment we will describe the patching of it. Understandably it differs from patching a Single Instance database, because of the redo apply process.
Note that in normal situations the use of dataguard is a licensed option. Since we however use it here on the database used for the RMAN catalog, which is a free-to-use database, also the use of dataguard is free of charge.
Note that we’re talking here about standard dataguard. Active dataguard where a standby database would be opened up read-only is a must-license feature always.
This document does not cover the contents of the database involved in this setup.
2. Setup of Dataguard
In this section the setup of the dataguard environment is described. In the examples the setup of the test environment for the RMAN catalog database is used for the screenshots and statements.
The basic setup of the environment consists of two Linux servers:
vtl1g002: the primary server, running an Oracle 19c EE database
vtl1g003: the secondary server, running the exact same Oracle version
It is important for a Dataguard setup that both primary and secondary servers run the same Oracle version and patch level.
On the primary server an empty database is created in the CDB/PDB setup called TSMT01C.
The OSuser for this installation is called “tsmt01”, so all file system locations are based on the Kramp standard: /opt/tsmt01/xxx
In the below sub sections the setup is explained in steps.
2.1. Enable archive logging
Dataguard relies on the shipping of redo information from primary server to the secondary server. To support this, both the primary and secondary server databases must have archived log mode enabled.
Start SQLplus on the primary server database and execute the following commands:
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
After enabling archivelog mode, make sure the database is backed up and archived logfiles are cleaned out.
After that the database must be set into Force Logging mode, so that all actions done on the database will result in redo-generation, even if a statement uses the NOLOGGING option.
To enable force logging, execute the following statement:
alter database force logging;
alter system switch logfile;
2.2. Create standby logfiles
Now create standby redo logs on the primary database in case of switchovers. The standby redo logs should be as big as the largest online redo logfile and there should be one extra group per thread. In our case we have 6 online redo file of 200Mb each.
This means we create 7 standby redo logs of the same size. They will be located in a subdirectory of the online log location.
First create this directory on both the primary and secondary server:
$ mkdir /opt/tsmt01/re01/standby
Now we can create standby log file from within SQLplus.
This is only done on the primary database, as we will copy this database in a later step completely to the secondary server:
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby01.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby02.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby03.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby04.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby05.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby06.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby07.log’ size 200m;
2.3. Initialization Parameters
For a dataguard solution both the primary & standby databases should have the same name (db_name).
They should however distinguish themselves based on db_unique_name.
In our case, the primary database is in the Amsterdam datacentre and will therefore be called TSMT01C_AMS. The standby is located in the Enschede datacentre and is called TSMT01C_ENS.
In this step we will already name the primary instance. After we copy the primary to the standby server, we will rename it then,
alter system set db_unique_name=’TSMT01C_AMS’ scope=spfile;
Bounce the database to activate the above change.
Next parameter to be changed is “standby_file_management”. When set to “AUTO”, it makes sure that a physical change on the primary database, like adding or extending an tablespace is also reflected to the standby database.
This parameter is dynamic, so it does not need a restart (again):
alter system set standby_file_management=AUTO scope=both;
Enable this both on the primary and on standby database as this feature should also work after a failover or switchover.
If this parameter is not set to AUTO is will result in redo-apply on the standby server being unable to apply changes when dataguard is up and running.
If e.g. a tablespace is added on the primary database, the datafile(s) belonging to that new tablespace will not be present on the standby server. If this happens, the below error is seen in the DGMGRL console when looking at the configuration.
DGMGRL>; show configuration;
Configuration - tsmt01c_config
Protection Mode: MaxPerformance
Members:
tsmt01c_ams - Primary database
tsmt01c_ens - Physical standby database
Error: ORA-16766: Redo Apply is stopped
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 37 seconds ago)
2.4. Duplicate the database
If the database is shutdown, then first copy the contents of the spfile into a pfile.
SQL> create pfile from spfile;
File created.
Change directory to the $ORACLE_HOME/dbs directory and copy the pfile to the standby server:
$ scp initTSMT01C.ora vtl1g003:`pwd`
On the standby server copy the created initfile to ‘/tmp’ for use with RMAN as temporary initfile.
$ cp /opt/tsmt01/ora/19.0.0.0/dbs/initTSMT01C.ora /tmp/inittemp.ora
Now start the instance on the standby server in NOMOUNT mode
SQL> startup nomount pfile=’/tmp/inittemp.ora’;
Then use RMAN on the standby server connecting to the primary database as target and to the -to be- standby as auxiliary.
$ rman target sys/<password>@tsmt01c_ams auxiliary sys/<password>@tsmt01c_ens
Issue the following RMAN commands to copy the database:
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='TSMT01C_ENS'
nofilenamecheck;
Note the option “for standby”, telling the duplicate command that this database is used for a standby database. It will thus not change a DBID after the clone.
The duplicate command can ofcourse be extended with db_file_name_convert or log_file_name_convert options, if needed.
Wait for the command to finish before continuing.
Finally create a password file on the standby server, with the same password as used for SYS on the primary database server.
Preferably we copy the password file from the primary server to the standby server, to make sure that it contains the same password.
On primary server:
$ cd $ORACLE_HOME/dbs
$ scp orapwTSMT01C vtl1g003:`pwd`
As soon as the standby database has been created, add the same standby logfiles as on the primary:
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby01.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby02.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby03.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby04.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby05.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby06.log’ size 200m;
alter database add standby logfile ‘/opt/tsmt01/re01/standby/tsmt01c_stdby07.log’ size 200m;
2.5. Listener.ora
Edit the listener.ora on both the primary and secondary server to contain a static entry for the databases. This is needed for the dataguard broker to be able to access the database, even if it is not open.
Remember that only open databases will automatically register with a listener. When a database is in mounted mode.
Most documentation found on the Internet name a standby database to contain the “_stdby” extension, like “cdb1_stdby”
This is however not really smart, especially if a failover takes place and suddenly the standby database called “cdb1_stdby” suddenly becomes the primary databases.
This is why we name the databases following the physical location.
On the primary database add the following entry to the listener.ora:
SID_LIST_TSMT01 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TSMT01C_AMS_DGMGRL)
(ORACLE_HOME = /opt/tsmt01/ora/19.0.0.0)
(SID_NAME = TSMT01C)
(ENVS="TNS_ADMIN=/opt/tsmt01/ora/network/admin")
)
)
On the standby server add the following:
SID_LIST_TSMT01 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TSMT01C_ENS_DGMGRL)
(ORACLE_HOME = /opt/tsmt01/ora/19.0.0.0)
(SID_NAME = TSMT01C)
(ENVS="TNS_ADMIN=/opt/tsmt01/ora/network/admin")
)
)
Note the use of CAPITALS in the database names. This is important, as it must match how it is used later in the Dataguard Broker setup.
Restart both listener processes:
lsnrctl stop
lsnrctl start
2.6. Tnsnames.ora
Update the tnsnames.ora file on both servers to have entries for both db_unique_names and the static “gloval_name” as defined in the listener.ora. The following entries therefore need to be added to the tnsnames.ora file on both servers:
TSMT01C_AMS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vtl1g002.kramponline.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = TSMT01C)
)
)
TSMT01C_ENS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vtl1g003.kramponline.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = TSMT01C)
)
)
Note the use of SID i.o SERVICE_NAME as the Dataguard Broker will need to connect to the databases when they are down. In that case the services are not available.
3. Enable the Broker
At this point we are ready to start the Dataguard synchronization process. The first step is to start the Dataguard Broker on both databases.
Since we want to prevent the standby database from becoming its own incarnation, we only start it in “mount” mode.
The primary database can be opened in “open” mode.
3.1. Start the broker process
On the primary database:
$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
….
….
Database mounted.
Database opened.
SQL> alter system set dg_broker_start=true scope=both;
On the standby database:
$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
….
….
Database mounted.
SQL> alter system set dg_broker_start=true scope=both;
3.2. Create Broker configuration
On the primary server start the dataguard broker to register this server.
$ dgmgrl sys/<password>@tsmt01c_ams
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Dec 5 16:27:35 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "TSMT01C_AMS"
Connected as SYSDBA.
DGMGRL> create configuration tsmt01c_config as primary database is TSMT01C_AMS connect identifier is TSMT01C_AMS;
Configuration "tsmt01c_config" created with primary database "tsmt01c_ams"
DGMGRL>
Now add the standby database:
DGMGRL> add database TSMT01C_ENS as connect identifier is TSMT01C_ENS;
Database "tsmt01c_ens" added
DGMGRL>
It is important to use capitals in these statements for the database name if there are also capitals in the listener configuration.
If there is somewhere a discrepancy between these configurations, one can expect an ORA-01017 when trying to setup the dataguard configuration.
You can check the configuration using the “show configuration” command:
DGMGRL> show configuration
Configuration - tsmt01c_config
Protection Mode: MaxPerformance
Members:
tsmt01c_ams - Primary database
tsmt01c_ens - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
Configuration status is “DISABLED” which means that dataguard is not active yet.
We need to enable the configuration to let the standby database become and stay in sync with the primary database.
DGMGRL> enable configuration;
Enabled.
DGMGRL>
This commando may take a while depending on the amount of data that has to be synced.
3.3. Tnsnames.ora
Before this installation can be successfully used and before we can do e.g. a switchover or a failover, clients connecting to this Dataguard database need to be made aware about the existence of two hosts, possibly serving the primary database.
To solve this an extra host address needs to be added to the tnsnames.ora entry for this database. There where a single instance usually only has one address line in tnsnames.ora, for a Dataguard setup you need to add one more.
The tnsnames entry for the above configuration therefor looks like this:
TSMT01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=vtl1g002.kramponline.com)(PORT = 1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=vtl1g003.kramponline.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TSMT01)
)
)
This way this entry enables the client to probe for which of the hosts is the active primary host and connections will always succeed
3.4. Monitoring
The configuration and activity can basically be monitored from the Dataguard Broker command line. More detailed monitoring and alerting on issues is better done from Oracle Enterprise Manager.
The fastest way to check the status of the Dataguard setup is to use the “show configuration” command from the Dataguard Broker command line.
This looks this:
DGMGR> show configuration
Configuration - tsmt01c_config
Protection Mode: MaxPerformance
Members:
tsmt01c_ens - Primary database
tsmt01c_ams - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 44 seconds ago)
It is important that the status show “SUCCESS”
If one looks in detail to the above screen output, it is visible that it is different from that on the previous page. There the tsmt01c_ams was primary database. Now we have switched roles and tsmt01c_ens is the primary database.
In the next chapter this is explained in detail
3.5. Switchover
From a dataguard setup, the members can switch roles. This can be done manually using a switchover, or automatically in case of a failover.
A failover can occur when e.g. de primary database server physically fails. The broker can in that case decide to promote the standby to be the primary database.
A failover or the setup of it, is not discussed in this document.
A switchover can be convenient in case of maintenance. If e.g. one of the servers needs to be shutdown, patched or something else, the standby database can take over the role of primary database. This makes it possible to shutdown the “before” primary database server.
A switchover making the tsmt01c_ams the primary database server looks like this:
DGMGRL> switchover to tsmt01c_ams;
Performing switchover NOW, please wait...
New primary database "tsmt01c_ams" is opening...
Operation requires start up of instance "TSMT01C" on database "tsmt01c_ens"
Starting instance "TSMT01C"...
Connected to an idle instance.
ORACLE instance started.
Connected to "TSMT01C_ENS"
Database mounted.
Connected to "TSMT01C_ENS"
Switchover succeeded, new primary is "tsmt01c_ams"
DGMGRL>
Confirming the switchover by looking at the resulting configuration shows:
DGMGRL> show configuration
Configuration - tsmt01c_config
Protection Mode: MaxPerformance
Members:
tsmt01c_ams - Primary database
tsmt01c_ens - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 51 seconds ago)
DGMGRL>
3.6. Failovers
With Dataguard it is also possible to let the configuration automatically failover to the standby node in case of a failure of the primary node.
This is called Fast Start Fail Over, of FSFO
To configure this, we should setup a so-called observer-server, a standalone server that is responsible for monitoring the primary database.
In case the observer notices that the primary database is not responding for a couple of seconds, for what kind of reason, it instructs the dataguard broker to switch the configuration to the standby database.
For the current purpose of our Dataguard Setup, we do not need automatic failover, so we skip this configuration for now.
No comments:
Post a Comment