Preparing for an Oracle standby database

In the Oracle environment the concept of a standby database is one of the major methods of providing disaster recovery. Oracle knows of 2 types of standby databases:

• physical standby: where the standby database contains exactly the same as the primary database, and normal recovery is performed using the archived redo stream of the primary database
• logical standby: where the standby database may deviate from the primary database, so no physical consistency exists. The redo stream of the primary database is mined for SQL statements which are applied transactionally on the standby database, making the standby database logically look the same as the primary database.

In terms of disaster recovery the primary database is most likely chosen, as you can switch roles between primary and standby database without any further configuration steps other than setting up the standby database. This is because both databases look exactly like one another: the only noticable difference is the database's unique name. We will focus on the physical standby database from now on in this post and where I refer to "standby database" the physical variant is meant.

With setting up a standby environment we immediately are confronted with the big difference between the Standard Edition 2 (SE2) and Enterprise Edition (EE) flavors of the Oracle RDBMS. With EE we have a plethora of options, from manual to almost full automated setups and even with opening the standby database (in snapshot mode, or - in case the Active Data Guard license has been bought - read-write mode). SE2 doesn't have all this functionality. There it comes down to feeding archived redo to the standby database to have the standby database remain up to par with the primary database. This particular post will focus on the EE version of Oracle.

Preparations

Setting up a standby database environment first of all requires a machine where we can run such a standby database. As we already have created a VM called "primary" with a full stack of Oracle software installed it seems obvious that we use that VM as basis for the to-be-created "standby" VM. VirtualBox allows you to create a full clone of a VM using a few clicks. Follow along! The steps to clone involve:

1. Cleanly shut down the primary database VM. This is required to get a consistent state of both VMs.
2. Next, right-click on the primary VM and choose "Clone..." from the popup menu:
   
3. A new dialog will open where we'll enter the details of the clone VM. Note that I specifically choose to change the MAC addresses for all network adapters:
   
4. Specify that we wish to have to full clone so the standby VM will have a completely independent lifespan of the primary VM:
   
   When pressing the "Finish" button the clone operation will commence:
   

Now we have clone of the primary VM called "Standby" within VirtualBox. However... the guest OS still thinks it's called "primary.mydomain.com". Next to that, as we've changed the MAC addresses, the new MAC addresses cause DHCP to be enabled for the network interfaces. So within the VM itself there is some work to be done as well! In order to correct the TCP/IP protocol stack setup in the cloned VM, start it up first and log in as the "root" user (remember: it's a clone of the primary VM, so you can use the same password as the one used for that VM). Once logged in, start "nmtui" (the Network Manager text user interface):

Within Network Manager, first set the machine's host name:

Next, for the enp0s3 interface, adjust the IPv4 settings and disable IPv6:

When everything has been configured, exit Network Manager and reboot for the changes (especially the host name change) to take effect. We now have 2 environments with an installed Oracle stack. Now let's get our hands dirty!

Prerequisites for a physical standby database

So... what do we need for a physical standby database? Well, that's quite easy:

• First we need a running instance on the standby side with of course the same database name set (governed through the DB_NAME instance parameter), but with a different unique name (DB_UNIQUE_NAME instance parameter).
• We also need a copy of the primary password file. If one doesn't exist at the primary site, then create one and copy that one to the standby site.
• Let's not forget the static listener entries en TNS aliases which we'll use for the databases to communicate with each other.
• In order to create a copy of your primary database you need a full database backup of the primary database, including a standby control file.
• We also need to set a bunch of instance parameters for proper file creation, file name conversion between primary and standby sites, etc.

Let's take the show to Europe with a primary database called "thehague" (with PDB "mypdb1") and and a standby database named "berlin".

creating the primary database

First create the primary database if it doesn't already exist. For this we'll use a response file named /u01/stage/cdb.rsp with the following contents:

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=thehague.mydomain.com
sid=thehague
databaseConfigType=SI
RACOneNodeServiceName=
policyManaged=false
createServerPool=false
serverPoolName=
cardinality=
force=false
pqPoolName=
pqCardinality=
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=mypdb1
useLocalUndoForPDBs=true
pdbAdminPassword=Welcome1
nodelist=
templateName=/u01/app/oracle/product/19.20/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
sysPassword=oracle
systemPassword=oracle
serviceUserPassword=
emConfiguration=
emExpressPort=5500
runCVUChecks=FALSE
dbsnmpPassword=oracle
omsHost=
omsPort=0
emUser=
emPassword=
dvConfiguration=false
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=false
datafileJarLocation=/u01/app/oracle/product/19.20/dbhome_1/assistants/dbca/templates/
datafileDestination=/u01/oradata/thehague/
recoveryAreaDestination=/u01/fra/thehague/
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
registerWithDirService=false
walletPassword=
listeners=
variablesFile=
variables=ORACLE_BASE_HOME=/u01/app/oracle/product/19.20/dbhome_1,DB_UNIQUE_NAME=thehague,ORACLE_BASE=/u01/app/oracle,PDB_NAME=mypdb1,DB_NAME=thehague,ORACLE_HOME=/u01/app/oracle/product/19.20/dbhome_1,SID=thehague
initParams=audit_trail=none,audit_file_dest=/u01/app/oracle/admin/thehague/adump,compatible=19.0.0,db_block_size=8192,db_domain=mydomain.com,db_name=thehague,db_unique_name=thehague,nls_language=American,nls_territory=America,db_create_file_dest=/u01/oradata/thehague/,db_create_online_log_dest_1=/u01/oradata/thehague/,db_create_online_log_dest_2=/u01/fra/thehague/,db_recovery_file_dest=/u01/fra/thehague/,db_recovery_file_dest_size=8G,undo_tablespace=UNDOTBS1,open_cursors=300,processes=200,sga_target=4G,pga_aggregate_target=512M,diagnostic_dest=/u01/app/oracle,dispatchers=(PROTOCOL=TCP)(SERVICE=thehagueXDB),remote_login_passwordfile=EXCLUSIVE
sampleSchema=false
databaseType=MULTIPURPOSE
automaticMemoryManagement=false

Creation of a database is done by simply executing:

$ export ORACLE_HOME=/u01/app/oracle/product/19.20/dbhome_1
$ PATH=${ORACLE_HOME}/bin:${PATH}
$ dbca -silent -createDatabase -responseFile /u01/stage/cdb.rsp

After a little while we'll have a container database called "thehague" we can use for creating a standby. But... first we need to make sure to implement an important action that frequently is forgotten: the configuration of the primary database using similar parameters in case the roles between primary and standby databases are switched and the old primary database then becomes standby! So, on the primary side we need to perform the following actions:

$ sqlplus "/ as sysdba"
SQL> ALTER SYSTEM SET db_file_name_convert='berlin','thehague','BERLIN','THEHAGUE' sid='*' scope=spfile;
SQL> ALTER SYSTEM SET log_file_name_convert='berlin','thehague','BERLIN','THEHAGUE' sid='*' scope=spfile;
SQL> ALTER SYSTEM SET standby_file_management=auto sid='*' scope=spfile;
SQL> SHUTDOWN IMMEDIATE;

Now start the database instance again for the above parameters to take effect, and at the same time make sure that ARCHIVELOG mode is enabled:

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> EXIT

Now that the database is running in ARCHIVELOG mode, make sure we prepare it for a Data Guard setup:

$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
RMAN> EXIT

Ignore the RMAN-8591 message given.

Configuring the password file

When a password file doesn't exist already for the primary database, create one at the primary site using:

$ orapwd file=${ORACLE_HOME}/dbs/orapwthehague

The password file (name is orapw${ORACLE_SID} and is residing in ${ORACLE_HOME}/dbs) should then be copied over to the standby.mydomain.com machine for use with the standby database:

$ scp ${ORACLE_HOME}/dbs/orapwthehague standby.mydomain.com:/u01/app/oracle/product/19.20/dbhome_1/dbs/orapwberlin

Configuring the network connectivity

For two databases to communicate with one another we have to set up TNS aliases so we can refer to logical names when we wish to access a particular database address. Next to that of course we need a listener configured which services incoming database requests. Let's start with the listener on the primary database side. Create the /u01/app/oracle/product/19.20/dbhome_1/network/admin/listener.ora file with the following contents:

LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcp)(HOST = primary.mydomain.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = ipc)(KEY = LSNR_PRIM))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = thehague_dgb)
      (SID_NAME = thehague)
      (ORACLE_HOME = /u01/app/oracle/product/19.20/dbhome_1)
    )
    (SID_DESC =
      (SID_NAME = thehague)
      (ORACLE_HOME = /u01/app/oracle/product/19.20/dbhome_1)
    )
  )

After creation of the listener.ora, start the listener:

$ lsnrctl start

Similarly we need a /u01/app/oracle/product/19.20/dbhome_1/network/admin/listener.ora on the standby.mydomain.com machine:

LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcp)(HOST = standby.mydomain.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = ipc)(KEY = LSNR_STBY))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = berlin_dgb)
      (SID_NAME = berlin)
      (ORACLE_HOME = /u01/app/oracle/product/19.20/dbhome_1)
    )
    (SID_DESC =
      (SID_NAME = berlin)
      (ORACLE_HOME = /u01/app/oracle/product/19.20/dbhome_1)
    )
  )

On the standby we need to start the listener after creation of the listener.ora as well:

$ lsnrctl start

Next, we'll create a /u01/app/oracle/product/network/admin/tnsnames.ora file (as we don't have an LDAP server at hand) with 2 aliases per database:

• One for the redo transport. We'll call this alias <dbname>_dgt.
• One for instance management. We'll call this alias <dbname>_dgmgrl.

The tnsnames.ora contents reads:

thehague_dgt =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = tcp)
      (HOST = primary.mydomain.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = thehague)
      (SERVER = dedicated)
    )
  )

thehague_dgmgrl =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = tcp)
      (HOST = primary.mydomain.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = thehague)
      (SERVER = dedicated)
      (UR = A)
    )
  )

berlin_dgt =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = tcp)
      (HOST = standby.mydomain.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = berlin)
      (SERVER = dedicated)
    )
  )

berlin_dgmgrl =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = tcp)
      (HOST = standby.mydomain.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = berlin)
      (SERVER = dedicated)
      (UR = A)
    )
  )

Create this file on the primary side and copy it over to the standby machine in the corresponding directory.

Installing Oracle19c on Linux

Installation of Oracle19c on Linux is almost like a walk in the park. For this setup we'll choose a virtual machine setup with the following characteristics:

• VirtualBox 7.0.10
• For the guest OS: Oracle Enterprise Linux 8.8
• Oracle19c version 19.20 (the 19.3 base release with the 19.20 Release Update and the latest Monthly Recommended Patches bundle and JVM patch) together with the latest OPatch release

In this post I will use my own DNS server setup. If not having installed or configured a DNS server yet, please do so, as you'll need the configuration information of this particular server when setting up the guest OS within the VM. The steps I followed for my installation include:

• configure a VM for running Oracle19c
• installing OEL8.8 into the VM
• installing the Oracle19c software
• create a container database
• configure a listener for incoming database connections

The environment will have the following characteristics:

• name: primary.mydomain.com
• IP address: 192.168.128.11
• DNS server: 192.168.128.254
• container database name: v19cdb
• PDB name: v19pdb1

The following software will be downloaded upfront:

• Oracle Database 19c for Linux x86-64 in ZIP format
• the Database Release Update 19.20
• the Database MRP 19.20.0.0.230919
• the JDK bundle patch 19.0.0.0.230718

Configure a VM for running Oracle19c

To create a VM for use with the Oracle19c software we can roughly follow the steps outlined in my DNS server post, with the following changes:

• A 60 GB disk should be created to support:
  • a staging area with the unpacked Oracle software bundles
  • a directory containing the Oracle RDBMS software installation (the so-called Oracle Home)
  • a directory containing the database files
  • a directory containing recovery structures for the database (the so-called Fast Recovery Area, or FRA for short)
• 2 network interfaces: one for a host-only network which can contact the DNS server, and one for access to the public network (for e.g. system updates)
• 8 GB of memory

Simply create the VM with the above characteristics and attach the OEL8.8 ISO image.

Installing OEL8.8 into the VM

The OS installation can also be done using the same steps as outlined in my DNS server blog. Make sure that the IP address is set properly and that the DNS client configuration (i.e.: the last commands from the DNS setup blog) have been executed properly, so the new VM will be able to interact properly with our own DNS server. The one action I perform differently as compared to the other VM setups is that here I choose to have my own special partitioning scheme to maximize the disk size for the Oracle software:

When pressing the "Done" button at the top left of the screen a new window will pop up where I can select my own partitioning scheme. First select the "create automatically" link to get a default setup:

Now it's time to adjust this default setup. We won't be using the home directories of individual users much, as we'll store all Oracle installation files in the /u01 directory structure on the / filesystem. Select the "/home" mount point on the left side of the screen and then on the right lower the size in the "Desired Capacity" field:

Next, enlarge the "/" filesystem to a maximum size. To do this, e.g. enter a size larger than the disk size in the "Desired Capacity" field and click e.g. back on the "/home" link. This will auto-fill the desired capacity to the maximum allowed size:

When finished, press the "Done" button in the top left of the screen to continue the installation. This will prompt a confirmation. Press "Accept Changes" and continue with the installation:

Make sure that in the end the DNS server is queryable and that the guest OS has been updated to the latest security fixes.

Installing the Oracle19c software

In order to run Oracle19c on Linux you first need to install an Oracle-provided RPM to set up the OS with an "oracle" account:

# dnf install -y oracle-database-preinstall-19c

Once the Oracle RPM is installed, let's provide the "oracle" account with a password (do so if you're confident with using an open account; if not, make sure you can use "sudo" to use the "oracle" account!) and create the necessary directory structures for use with Oracle:

# passwd oracle
# mkdir -p /u01/app/oracle/product/19.20/dbhome_1
# mkdir -p /u01/app/oraInventory
# mkdir -p /u01/oradata
# mkdir -p /u01/fra
# mkdir /u01/stage
# chown -R oracle:oinstall /u01/app/oracle /u01/app/oraInventory /u01/oradata /u01/fra /u01/stage
# chmod -R 775 /u01/app/oracle /u01/app/oraInventory /u01/oradata /u01/fra /u01/stage

Now prepare the OS for creation of a database already:

# echo vm.nr_hugepages=20000 >> /etc/sysctl.conf
# sysctl -p

It's time to move on to the installation of the Oracle software. Assume the identity of the "oracle" user:

# sudo -iu oracle

Place all Oracle related ZIP files (downloaded from Oracle Technology Network - base release 19.3 - and My Oracle Support - the RU and MRP bundle as well as the latest OPatch bundle) into /u01/stage so we can start preparing for the actual installation. Then extract all required files:

$ unzip /u01/stage/LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.20/dbhome_1
$ mv /u01/app/oracle/product/19.20/dbhome_1/OPatch /u01/app/oracle/product/19.20/dbhome_1/OPatch.old
$ unzip p6880880_190000_Linux-x86-64.zip -d /u01/app/oracle/product/19.20/dbhome_1
$ mkdir /u01/stage/RU
$ mkdir /u01/stage/MRP
$ mkdir /u01/stage/OneOffs
$ unzip /u01/stage/p35320081_190000_Linux-x86-64.zip -d /u01/stage/RU
$ unzip /u01/stage/p35755640_1920000DBRU_Linux-x86-64.zip -d /u01/stage/MRP
$ unzip /u01/stage/p35336174_190000_Linux-x86-64.zip -d /u01/stage/OneOffs

We've set the stage for installation! Next step is to create a response file for runInstaller to use. For this setup we use the response file named /u01/stage/swinstall.rsp:

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=false

Performing the installation can now be done with some simple steps. Note that we need to set the distribution of the installation to OEL7.8 to have runInstaller not to fail with an unsupported OS check assertion:

$ export CV_ASSUME_DISTID=OEL7.8
$ export ORACLE_HOME=/u01/app/oracle/product/19.20/dbhome_1
$ PATH=${ORACLE_HOME}/bin:${PATH}
${ORACLE_HOME}/runInstaller -silent -applyRU /u01/stage/RU/35320081 -applyOneOffs /u01/stage/MRP/35755640/33974554,/u01/stage/MRP/35755640/35156218,/u01/stage/MRP/35755640/35182029,/u01/stage/MRP/35755640/35395648,/u01/stage/MRP/35755640/35499173,/u01/stage/MRP/35755640/35598272,/u01/stage/MRP/35755640/35598911,/u01/stage/MRP/35755640/35635081,/u01/stage/MRP/35755640/35646719,/u01/stage/MRP/35755640/35655714,/u01/stage/MRP/35755640/35775859,/u01/stage/MRP/35755640/35809185,/u01/stage/OneOffs/35336174 -responseFile /u01/stage/swinstall.rsp

Note that for convenience we both install the base release and patch up to the latest patches at the same time without hardly any user interference. The only thing we need to do after installation is to run a couple of scripts within the security context of the "root" account:

# /u01/app/oraInventory/orainstRoot.sh
# /u01/app/oracle/product/19.20/dbhome_1/root.sh

Thank to persons like Mike Dietrich and Daniel Overby Hansen installing and patching at the same time is a lot easier than it used to be!

Create a container database

We're up to the point that the OS is configured and the Oracle software tree has been installed and patched. Now awaits the final step of this exercise: creation of a container database. This also can be done with minimal effort using a silent database creation. Note that in a previous step we already configured Linux' HugePages support so we can configure an instance with a 4 GB SGA. It's time to create the /u01/stage/credb.rsp response file:

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=v19cdb.mydomain.com
sid=v19cdb
databaseConfigType=SI
RACOneNodeServiceName=
policyManaged=false
createServerPool=false
serverPoolName=
cardinality=
force=false
pqPoolName=
pqCardinality=
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=v19pdb1
useLocalUndoForPDBs=true
pdbAdminPassword=Welcome1
nodelist=
templateName=/u01/app/oracle/product/19.20/dbhome_1/assistants/dbca/templates/General_Purpose.dbc
sysPassword=oracle
systemPassword=oracle
serviceUserPassword=
emConfiguration=
emExpressPort=5500
runCVUChecks=FALSE
dbsnmpPassword=oracle
omsHost=
omsPort=0
emUser=
emPassword=
dvConfiguration=false
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=false
datafileJarLocation=/u01/app/oracle/product/19.20/dbhome_1/assistants/dbca/templates/
datafileDestination=/u01/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/u01/fra/{DB_UNIQUE_NAME}/
storageType=FS
diskGroupName=
asmsnmpPassword=
recoveryGroupName=
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
registerWithDirService=false
dirServiceUserName=
dirServicePassword=
walletPassword=
listeners=
variablesFile=
variables=ORACLE_BASE_HOME=/u01/app/oracle/product/19.20/dbhome_1,DB_UNIQUE_NAME=v19cdb,ORACLE_BASE=/u01/app/oracle,PDB_NAME=v19pdb1,DB_NAME=v19cdb,ORACLE_HOME=/u01/app/oracle/product/19.20/dbhome_1,SID=v19cdb
initParams=audit_trail=none,audit_file_dest=/u01/app/oracle/admin/v19cdb/adump,compatible=19.0.0,db_block_size=8192,db_domain=mydomain.com,db_name=v19cdb,db_unique_name=v19cdb,nls_language=Dutch,nls_territory=The Netherlands,db_create_file_dest=/u01/oradata/v19cdb/,db_recovery_file_dest=/u01/fra/v19cdb,db_recovery_file_dest_size=8G,undo_tablespace=UNDOTBS1,open_cursors=300,processes=2000,sga_target=4G,pga_aggregate_target=512M,diagnostic_dest=/u01/app/oracle,dispatchers=(PROTOCOL=TCP)(SERVICE=v19cdbXDB),remote_login_passwordfile=EXCLUSIVE
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false

This response file defines some default parameter values, locations and SGA size. Creating a database is as simple as:

$ export ORACLE_HOME=/u01/app/oracle/product/19.20/dbhome_1
$ PATH=${ORACLE_HOME}/bin:${PATH}
$ dbca -silent -createDatabase -responseFile /u01/stage/credb.rsp

We'll receive some warnings on the default chosen passwords, but that's expected. In the end a CDB is created with one PDB, as supplied in the response file:

Configure a listener for incoming database connections

Rests us one final step: configuring a listener so we can service incoming database connections. Even this can be done without any user interaction using solely a response file supplied to a wizard. For this, create the /.u01/stage/netca.rsp response file:

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"
SHOW_GUI=false
LOG_FILE=""/u01/app/oracle/cfgtoollogs/netca/netca.log""

[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;primary.mydomain.com;1521"}

Calling Net Configuration Assistant allows the definition of the listener:

$ export ORACLE_HOME=/u01/app/oracle/product/19.20/dbhome_1
$ PATH=${ORACLE_HOME}/bin:${PATH}
$ netca -silent -responsefile /u01/stage/netca.rsp

This results in listener capable of servicing our freshly created PDB:

Our database is now accessible through the mydomain.com network domain!

DNS in a test environment

A playground for Oracle software most likely requires a DNS (Domain Name System) server somewhere in the network, whether it be for a simple Data Guard setup, or for checking out what Real Application Clusters (RAC for short) has to offer.

Setting up a minimal DNS server for your own private domain is relatively simple to perform using a tool such as VirtualBox. The steps I present here will use the BIND (Berkeley Internet Name Domain) implementation which is freely available on Linux, run from within a VirtualBox environment.

The setup I present here uses the following pieces of software:

• VirtualBox 7.0.10
• For the guest OS: Oracle Enterprise Linux 9.2

Installing VirtualBox is a trivial task and won't be discussed here. Detailed configuration of DNS also falls outside the scope of this blog. Refer to e.g. the BIND documentation on the Red Hat site for more details on the intricacies of BIND.

The steps I followed for setting up my own DNS server include:

• configure a host-only network in VirtualBox
• configure a VM for running BIND
• installing OEL9.2 into the VM
• configuring BIND

Using these steps a DNS server will be built with the following characteristics:

• the domain serviced is named mydomain.nl
• the DNS server will have a fixed IPv4 address of 192.168.128.254
• the DNS server will service IPv4 addresses in the 192.168.128.x subnet
• the subnet gateway is located at 192.168.128.1
• DNS will be setup containing several machines for the various local Oracle tests, including Data Guard and RAC

As indicated, this blog post is making use of Oracle Enterprise Linux 9.2. Red Hat Enterprise Linux 9 (or any derivatives) should also be usable; for other types of systems, please consult the documentation on what utilities to use and system packages to install, and at what locations the various configuration files are located.

Configuring a host-only network in VirtualBox

The first step in the setup is to make sure we have a network to communicate over. As this DNS server will only be used by VMs within VirtualBox on a single host machine, setting up a host-only network is the obvious choice to implement such a network. For this, create a new host-only network and assign a specific IP address range to this network. Open the Network Manager within VirtualBox:

From here, create a new host-only network, select the "Configure Adapter Manually" radio button and enter the IP address of the subnet gateway (192.168.128.1 in our case):

Press the "Apply" button when finished. After that, for ease of use, navigate to the "DHCP Server" tab and enable the DHCP server for the range 192.168.128.100 to 192.168.128.200:

Once again, press "Apply" to commit to the changes. Now we're ready to create a VM using this network.

Configure a VM for running BIND

Now we're ready to create a VM using the network created. For this, select the "New" option in the "Machine" menu:

A wizard pops up to guide us through the definition of a VM. Enter a name for your new VM (we'll chose OL9DNS here), the location where the VM should be created, and the ISO image of the OLE9.2 distribution. As we want to arrange for some specific size, skip the unattended install, and press the "Next" button:

A VM with 2GB of memory and 1 CPU is more than enough for a DNS server to run; press "Next":

A 20 GB hard disk for storing both the OS and the DNS configuration suffices as welll; press "Next":

End the definition of the VM configuration by pressing the "Finish" button at the summary page:

Now, in order to use our configured network, the VM needs to be altered for this to happen. In the VirtualBox main interface, right-click on the OL9DNS VM just created and select "Settings..." from the popup menu:

Navigate to the "Network" settings and for "Adapter 1" choose "Host-only" attachment type and the virtual network adapter name of the newly created network ("VirtualBox Host-only Ethernet Adapter #3" in our case) as the network:

Next, navigate to the "Adapter 2" tab, enable the adapter, and choose for a bridged network connection to allow us to communicate with the Internet (e.g. for accessing Oracle's YUM repository). When having configured this adapter as well, press "OK" to continue:

Installing OEL9.2 into the VM

So far so good. Now it's time to roll up the sleeves and actually start installing something. First step: the OS which hosts the DNS server. So let's start the newly created VM to initiate the installation process of OEL9.2:

The boot process starts, where we select to skip the medium check and directly commence to the OS installation:

Select the language for the OS to install and continue the installation:

From here we'll present the installation with a particular configuration by providing the necessary information in a particular order:

The following information will be provided:

1. We need to configure the device on which to install the OEL9.2 release. Press the "Installation Destination" button on the main installation screen, which will bring up the following screen:
   
   
   If you don't want to make any adjustments to the default setup, simply press the "Done" button to navigate back to the main installation screen.
2. Next, press the "Software Selection" button to select what to install. For a DNS server we don't need any full-blown GUI environment. Simply choose "Server" and press the "Done" button:
   
3. Next, configure the network by pressing the "Network & Host Name" button:
   
   Here we need to perform actions in a certain order as well:
   1. First, define the proper host name at the bottom left of the page. You'll see the actual host name being changed on the right when pressing the "Apply" button.
   2. Then select the "enp0s3" adapter. This is the OS adapter associated with the 1st adapter defined for the VM and will hence use our host-only network setup.
   3. After having selected the "enp0s3" adapter, press the "Configure" button to start configuring this adapter. As chosen, the IPv4 address of this server will be 192.168.128.254, and the domain we'll use here reads "mydomain.com". This is all done from the "IPv4" tab:
      
      Now press the "Routes..." button to force the adapter to only be used for 192.168.128.x traffic:
      
      Press "OK" to continue with the configuration. We won't be using IPV6, so disable this protocol:
      
      Then press "Save" to save the network changes for this adapter.
   4. Now press the slider on the top right hand side of the screen to enable this adapter for use.
   5. Similarly, we need to configure the Internet facing adapter (enp0s8). Here we simply select the adapter and enable it without any additional configuration, forcing it to access the bridged network and request a DHCP configuration:
      
      In our case an IP address in the 192.168.178.x range is selected and the DNS servers are automatically configured through DHCP as well. This adapter will service our default gateway to the external network (e.g. for access to Oracle's YUM Repository).
   Now the network configuration has been provided, so we can press the "Done" button at the top left of the screen.
4. As a network has been defined, we can set up the date and time characteristics for this VM to use NTP. We only need to select the proper timezone and enable NTP use:
   
   Press "Done" when finished.
5. Finally, for ease of maintenance, we'll enable the use of the root account in this VM:
   
   Enable the root account by entering a valid password, including confirmation of that password. When done, press the "Done" button to move back to the main installation screen.

Back on the main installation screen we now can commence with the actual installation of OEL9.2 by pressing the "Begin installation" button:

A progress window will appear. Once the installation is finished, a reboot is required to wrap up the configuration of the newly installed system:

Finish the installation by making sure that the OS is fully updated with all latest security patches, by logging on to the console as the root account (which we happy enabled) and updating the OS:

# dnf update -y
# reboot

Now the system is ready for the installation and configuration of a DNS server!

Configuring BIND

The last hurdle in the setup of our own DNS server is the installation and configuration of BIND. We will install a default secure BIND server without any change-root support. To install this application, log on to the console of the system as the root account and issue:

# dnf install -y bind bind-utils

Now edit the /etc/named.conf file and change the following entries in the options section of the file:

• adjust the listening address to include our IP address:
  
  # sed -r -i -e '/[[:space:]]listen-on[[:space:]]/s/^.*$/ listen-on port 53 { 127.0.0.1; 192.168.128.254; };/' /etc/named.conf
  We won't adjust the IPv6 listening address, as we don't use that protocol.
• define which IP addresses can query our DNS server:
  
  # sed -r -i -e '/[[:space:]]allow-query[[:space:]]/s/^.*$/ allow-query { localhost; 192.168.128.0/24; };/' /etc/named.conf
• define from what IP addresses recursive queries are allowed:
  
  # sed -r -i -e '/[[:space:]]allow-query[[:space:]]/a \ allow-recursion { localhost; 192.168.128.0/24; };' /etc/named.conf

Let's check what we have done is syntactically correct:

# named-checkconf

Whenever there is an error reported this error should be resolved before continuing the setup. If all's well, then we can bring the BIND server online by opening the relevant firewall ports and enabling the BIND service at the OS level:

# firewall-cmd --permanent --add-service=dns
# firewall-cmd --reload
# systemctl enable --now named

Let's check if we can access our own BIND server:

# dig @localhost www.oracle.com

We should receive an output similar to:

In case no address can be resolved, review the configuration steps carefully.

Now we have configured the DNS listening part, it's up to defining the topology of the network itself. In order to be able to use host names we need to be able to resolve these host names into IP addresses. For this we'll define a forwarder file called /var/named/forward.mydomain.com having the following contents:

$TTL    86400
@               IN SOA  ol9dns.mydomain.com. root.ol9dns.mydomain.com. (
                                      42        ; serial (d. adams)
                                      3H        ; refresh
                                      15M       ; retry
                                      1W        ; expiry
                                      1D )      ; minimum
                IN NS           ol9dns.mydomain.com.
ol9dns          IN A            192.168.128.254
standalone      IN A            192.168.128.10
primary         IN A            192.168.128.11
standby         IN A            192.168.128.12
clus1           IN A            192.168.128.21
clus1-vip       IN A            192.168.128.22
clus2           IN A            192.168.128.23
clus2-vip       IN A            192.168.128.24
clusscan        IN A            192.168.128.25
clusscan        IN A            192.168.128.26
clusscan        IN A            192.168.128.27

In order to have BIND use this file we need to indicate that the file exists. This is done by adding a so-called zone to the BIND zone configuration file:

# cat <<__ZONE__ >> /etc/named.rfc1912.zones

zone "mydomain.com" {
    type master;
    file "forward.mydomain.com";
    allow-query { any; };
    allow-transfer { none; };
};
__ZONE__

In order to check whether BIND can work with it, let's check whether the syntax is OK, and if so, restart BIND to pick up the new configuration:

# named-checkconf
# systemctl restart named

Now let's check whether we can resolve any of our local addresses:

We have lift-off! But wait, this is only one part: from host name to IP address. When we do anything network related we should also be able to resolve an IP address back to a host name. This is where the reverse zone comes into the picture. To define the reverse zone, we create a configuration file /var/named/reverse.mydomain.com with the following contents:

$TTL    86400
@       IN      SOA     ol9dns.mydomain.com. root.ol9dns.mydomain.com.   (
                                      1997022700 ; Serial
                                      28800      ; Refresh
                                      14400      ; Retry
                                      3600000    ; Expire
                                      86400 )    ; Minimum
        IN      NS      ol9dns.mydomain.com.
254     IN      PTR     ol9dns.mydomain.com.
10      IN      PTR     standalone.mydomain.com.
11      IN      PTR     primary.mydomain.com.
12      IN      PTR     standby.mydomain.com.
21      IN      PTR     clus1.mydomain.com.
22      IN      PTR     clus1-vip.mydomain.com.
23      IN      PTR     clus2.mydomain.com.
24      IN      PTR     clus2-vip.mydomain.com.
25      IN      PTR     clusscan.mydomain.com.
26      IN      PTR     clusscan.mydomain.com.
27      IN      PTR     clusscan.mydomain.com.

Again we need to make BIND aware of the existence of this configuration file by adding a reverse zone:

# cat <<__REVZONE__ >> /etc/named.rfc1912.zones

zone "128.168.192.in-addr.arpa" {
    type master;
    file "reverse.mydomain.com";
    allow-query { any; };
    allow-transfer { none; };
};
__REVZONE__

Again check for syntax and if everything is in order, restart the BIND server to pick up the configuration changes::

# named-checkconf
# systemctl restart named

Then check whether an IP address can be resolved back into a host name:

In the end we now need to adjust the DNS search order for proper resolution of the host names. To do this, you need to make sure our own DNS server is the first to be queried:

# nmcli connection modify enp0s3 +ipv4.dns 192.168.128.254
# nmcli connection modify enp0s3 +ipv4.dns 84.116.46.21
# nmcli connection modify enp0s3 +ipv4.dns 84.116.46.20
# nmcli connection modify enp0s3 ipv4.dns-priority 1
# nmcli connection modify enp0s8 ipv4.dns-priority 2
# nmcli networking off
# nmcli networking on

Note: these last 2 DNS servers are supplied by my ISP. Use the appropriate DNS servers of your ISP, or use e.g. Google's 8.8.8.8 and 8.8.4.4 servers.

Hooray! This leaves us with a functional DNS server on our 192.168.128.x network!