Oracle Flashback Table

Oracle has two different data recovery capabilities that use the term ‘Flashback’. The first is flashback database, which is what most people think of when they hear the term flashback. Flashback database is the ability to rollback the entire database to a restore point, or a point in time. This ability requires that flashback be turned on at the database level (alter database flashback on;) and uses what oracle calls flashback logs as well as archive logs.

The other flashback capability is flashback table. This capability is always available but is entirely dependent upon the available undo space and the undo retention setting.

The flashback table capability, while less well known, is the most commonly used overall because it is a fast and easy way to recover from user error.

Since the flashback table capability uses the undo tablespace, it is extremely time dependent. Wait too long, and the ability to flash back you table disappears. Thus, it is critical that any user errors that require recovery be identified quickly so the data can be recovered. Ensure that users are not afraid to admit making mistakes.

The flashback table process is fairly straightforward. Suppose for example, you ran this command:

Update owner.table_name set person_last_name=’SMITH’; where person_id=1113;

Not only have we immediately learned a lesson in double checking for typos, but we have updated an entire table when we meant to update just a single row. Or perhaps a few rows. Regardless, this must be fixed quickly.

The flashback table process:

First, to flashback a table, row movement must be enabled. To enable row movement run this command:

Alter table owner.table_name enable row movement;

Then, the simplest version of this command is simply flashback table:

FLASHBACK TABLE owner.table_name TO_TIMESTAMP (SYSTIMESTAMP – INTERVAL ’90’ minute);

This will affect the entire table, which may not be desirable. It is also possible to use this technology to retrieve data from a point in time without flashing back the entire table, using flashback query:

Create table owner.working_table_name as (select * from owner.table_name as of timestamp TO_TIMESTAMP (SYSTIMESTAMP – INTERVAL ’90’ minute));

Once you have the correct data set, you can use the table you just created to fix the bad rows in the table:

Update owner.table_name o set person_last_name=(select person_last_name from owner.working_table_name w where o.person_id=w.person_id and w.person_id !=1113)

You can also select the data with just the select, though generally you want a copy of the good data to work with:

select * from owner.table_name as of timestamp TO_TIMESTAMP (SYSTIMESTAMP – INTERVAL ’90’ minute);

Regardless, remember that the flashback ability is time dependent so you will want to make sure you have a copy of the data you are working with. Loading the good data into a separate table is highly recommended both for recovery purposes and to so that a record of the fix is maintained for later use.

In summary, Oracle’s flashback table and flashback query capability can be a useful tool for the Oracle DBA toolset for recovering from user error.

Tips For Building Dataguard Standbys

There is an old saying that lessons are learned by making mistakes. And it is very true. All the tips in this article stem from the mistakes I have made in the past in building Dataguard Standbys.

As usual for my blog posts, let’s start with a few definitions:

The SID, database name, and database unique name of our primary database is primdbnm. The SID and database name of the standby database is also primdbnm. The unique name of the standby database is stdbydb.

Also, please note, this article is written for those who have some experience working with standby databases. It is not intended to explain how to build one from scratch.

The name of the tnsname entry of the primary database is PRIMTNS. The name of the tnsnames entry for the standby database is STDBYTNS. An example of what they may look like is below (by tns entry, I mean the entry in the tnsnames.ora file for the applicable instance):

PRIMTNS =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primdbhost.local.net)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = primdbnm)

)

)

 

STDBYTNS =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = stdbydbhost.local.net)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = primdbnm)

)

)

 

The static listener entries for each of these would look like this (assuming oracle home is /u01/app/oracle/product/19.3.0/dbhome_1). Please note that using the format <DB_UNIQUE_NAME>_DGMGRL is no longer required, but there is no problem with using it.

 

Primary static listener:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primdbhost.local.net)(PORT = 1521))

)

)

)

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = primdbnm_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

(SID_NAME = primdbnm)

)

)

 

Standby Static Listener:

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = stdbydbhost.local.net)(PORT = 1521))

)

)

)

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = stdbydb_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)

(SID_NAME = primdbnm)

)

)

 

 

Two common mistakes from the tnsnames and listener configurations are: Forgetting to reload the listener after adding the static listener entry and

Making the unique name the same for the primary and the standby database

 

In Oracle 19c, there are really only three init.ora settings to worry about for physical dataguard. Those are:

• LOG_ARCHIVE_CONFIG
• LOG_ARCHIVE_DEST_2
• FAL_SERVER

The log archive config setting is a record of the database unique names that are involved in the physical Dataguard configuration. Remember that these are the database unique names, not the SIDs and not the db names. This entry is the same on both the primary and the standby database. This is what our standby configuration will use:

LOG_ARCHIVE_CONFIG=’dg_config=(primdbnm, stdbydb)’

Next, LOG_ARCHIVE_DEST_2. This contains connection information for the standby database and is used by the primary database to connect to the standby. The service is the tnsnames entry for the standby database. Below is the setting for LOG_ARCHIVE_DEST_2 on the primary database:

LOG_ARCHIVE_DEST_2= ‘SERVICE=STDBYTNS LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbydb’

Note that ‘VALID_FOR=’ section. That is telling us that it is for the online logfiles, and when the current database is in the PRIMARY_ROLE (that is, when this database is the primary). This means that we can configure a similar LOG_ARCHIVE_DEST_2 entry for the standby, but it will only be active if the standby database becomes the primary database. The DB_UNIQUE_NAME is the DB_UNIQUE_NAME for the standby database.

If the LOG_ARCHIVE_DEST_2 is set on the standby database, it would be the following:

LOG_ARCHIVE_DEST_2= ‘SERVICE=PRIMTNS LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=primdbnm’

Next, the FAL_SERVER. The FAL_SERVER is simply the tnsnames entry for the primary database. It is set on the standby database. The FAL_SERVER tells the standby database where to get archive logs from if it falls behind. You can have a FAL_SERVER entry on the primary database but it will be ignored unless it has been switched to the standby. On the primary, the FAL_SERVER would be the tnsnames entry for the standby database. This is the FAL_SERVER setting on the standby database:

FAL_SERVER=PRIMTNS

If you choose to set the FAL_SERVER on the primary database, it would be the following:

FAL_SERVER=STDBYTNS

The next common problem is the passwd file (orapwstdbydb). Since oracle 12.1, it has been necessary to copy the password file from the primary to the standby database rather than create it on the standby. I normally copy it to the standby, and then copy it to the instance name of the standby database.

Below are the most common errors and fixes that I have run into when building Dataguard standby databases:

1. ‘The database is not in a Dataguard configuration’ (when setting LOG_ARCHIVE_DEST_2). This means that the LOG_ARCHIVE_CONFIG is defined, but the DB_UNIQUE_NAME given in the LOG_ARCHIVE_DEST_2 setting is not one of the databases in the LOG_ARCHIVE_CONFIG. Usually it means you just mis-typed the DB_UNIQUE_NAME somewhere.
2. ORA-1017 logging onto primary (or standby). Most often this means that the password file needs to be copied from the primary and the standby. In case of RAC it can also mean that the password files are different between two or more instances in the cluster. In the latter case, choose one instance (usually instance one) and copy that password file to all the other instances and rename it for each instance. If running RAC to RAC Dataguard, make sure you do this process on all the standby nodes also. Usually this error shows up in the alert log.
3. TNS-505 or other connection error logging into standby. Usually this means that tnsnames entry for the standby is not present on the primary. They key point to remember is that the tnsnames.ora file should be identical on both the primary and standby hosts. Usually this will show up in the alert log.
4. TNS-505 or similar error logging onto auxiliary while running rman duplicate. This will show up on your screen and the duplicate process will error out. See item 3, same problem.
5. ‘FAL: Error fetching gap sequence, no FAL server specified’ (normally this is in the alert log). Usually this means the FAL_SERVER is not defined on the standby.
6. ‘ORA-16047: DGID mismatch between destination setting and target database’ (normally this is in the alert log). The LOG_ARCHIVE_CONFIG is different between the primary and standby, or it is missing on the primary or standby.
7. If you get a series of errors like those shown below, the nofilenamecheck option was not used in the duplicate command. Before adding the nofilenamecheck option, ensure that you really are copying to another host. It will overwrite existing files if the option is used incorrectly:

   RMAN-03002: failure of Duplicate Db command at 10/14/2021 15:25:05
   

   RMAN-05501: aborting duplication of target database
   

   RMAN-05001: auxiliary file name <filename> conflicts with a file used by the target database
   

    

Other hints when building a Dataguard standby:

1. Once you have the standby database in startup/nomount mode make sure you can use the same password to connect to both the primary and standby using tnsnames from both the primary and standby host:

   sqlplus sys/password@PRIMTNS as sysdba

   sqlplus sys/password@STDBYTNS as sysdba

2. If you are using standby redo logs, build them on the primary before starting the build of the standby. Also, to make sure the standby redo logs get built on the standby, make sure that the LOG_FILE_NAME_CONVERT parameter is correct. If it is wrong, the standby redo logs will not be created, and you will need to create them manually.
3. Build a tnsnames entry that points to the static listener entry using DB_UNIQUE_NAME_DGMGRL, and ensure you can connect to it. Earlier versions of Oracle required a static listener entry with this name, and it is still useful for validation. This is done to validate that the static listener entry is correct, which you will need if using the ..from active database option. EG:

    

   STDBYDG =
   

   (DESCRIPTION =
   

       (ADDRESS = (PROTOCOL = TCP)(HOST = stdbydbhost.local.net)(PORT = 1521))
   

   (CONNECT_DATA =
   

   (SERVER = DEDICATED)
   

   (SERVICE_NAME = stdbynm_DGMGRL)
   

   (UR=A
   

   )
   

   )
   

   PRIMDG =
   

   (DESCRIPTION =
   

       (ADDRESS = (PROTOCOL = TCP)(HOST = stdbydbhost.local.net)(PORT = 1521))
   

   (CONNECT_DATA =
   

   (SERVER = DEDICATED)
   

   (SERVICE_NAME = primdbnm_DGMGRL)
   

   (UR=A
   

   )
   

   )
   

    

4. If at any point your ‘duplicate target database for standby…’ command errors out, immediately exit rman with the ‘exit’ command. Do this because you probably do not want to shut down your primary database, and the primary database is the target database for the rman duplicate command. This is a safety step to prevent errors.
5. If you are creating a standby for a running RAC, make sure you create the necessary tnsnames entries on all RAC nodes.
6. In RAC and HAS, put the tnsnames.ora file in clusterware or has home, CRS_HOME/network/admin. Then create a softlink to point the DATABASE_HOME/network/admin/tnsnames.ora to the tnsnames.ora file in the CRS_home. The command is similar to this:

    

   ln -s /u01/app/19.3.0/grid/network/admin/tnsnames.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
   

To make your life easier do the following:

1. Use the Dataguard broker.
2. Always enable flashback database on the standby and primary. This will make recovery much easier in case of failover.
3. Make sure that the STANDBY_FILE_MANAGEMENT=AUTO on both primary and standby.
4. If you enable HAS (high availability services) or CRS (cluster ready services, ie RAC) the static listener entry is not required for management purposes, it is only needed if you do the initial build using the rman duplicate from active database option, and is only used for the initial build.
5. As soon as you start the rman duplicate process, tail both the primary and standby alert logs. This will make diagnosing issues much easier.
6. Use standby redo logs.
7. If you are planning to use the data guard broker, make sure the configuration works before you enable the broker. Once it’s running properly, then configure and enable the broker.
8. You probably do not want to do fast start failover. I have never met a case where automatic failover or switchover to the standby was desirable, though such cases probably do exist. Almost invariably there is prep required by multiple applications before failing over.
9. If possible, you will always want to do a switchover in preference to a failover.
10. Have a plan for a failover. Rehearse it.
11. Have a plan for a switchover. Rehearse it.

Please note that steps 10 and 11 are absolutely critical for DBA job security. If you cannot rehearse on one of your company’s existing configurations, such as test or QA, then build instances using Oracle virtual box and do it there. It is absolutely critical that the DBA understand the possible problems and fixes involved in the switchover and failover process.

 

 

Dataguard Broker -19c

One of the nice features of Oracle that the company continues to enhance with each release is oracle Dataguard. For those unfamiliar with the Dataguard broker, the broker manages oracle Dataguard standbys. It is used for switchovers, failovers, and checking the status of your Oracle Dataguard environment. In this blog, we are going to discuss a few of those features, both the features that have been there for some time, as well as the newer features.

Because the building of an Oracle Dataguard environment is very well documented on multiple blogs (including my own, https://dbakerber.wordpress.com/2014/06/03/enabling-dataguard-broker-observer-2/ , https://wordpress.com/post/dbakerber.wordpress.com/150, https://wordpress.com/post/dbakerber.wordpress.com/66), I have not gone over the steps for building the standbys in this post. But in summary, below is what I have built:

I have built a home cluster in VMware workstation as follows using the instructions I wrote here: https://dbakerber.wordpress.com/2021/02/05/vmware-workstation-creation-for-oracle-rac-part-1/ . Note that this was all built in Oracle 19c using Oracle Enterprise Linux 7:

1. 2 Node cluster, with Oracle instances dgex1 and 2. This is the primary instance.
2. Standby instance 1 on host catwork.
3. Standby instance 2 on host catwork2.

I have also built a small Oracle OEM configuration for the purposes of blogging.

Now, to get to business. Starting with Oracle 12.2, the database validation feature was added. The command validates the status of the primary and standby databases, like this: validate database <verbose> ‘dgcpy’;

DGMGRL> validate database verbose ‘dgcpy2’;

 

Database Role: Physical standby database

Primary Database: dgex

 

Ready for Switchover: Yes

Ready for Failover: Yes (Primary Running)

 

Flashback Database Status:

dgex : On

dgcpy2: On

 

Capacity Information:

Database Instances Threads

dgex 2 2

dgcpy2 1 2

Warning: the target standby has fewer instances than the

primary database, this may impact application performance

 

Managed by Clusterware:

dgex : YES

dgcpy2: YES

 

Temporary Tablespace File Information:

dgex TEMP Files: 1

dgcpy2 TEMP Files: 1

 

Data file Online Move in Progress:

dgex: No

dgcpy2: No

 

Standby Apply-Related Information:

Apply State: Running

Apply Lag: 0 seconds (computed 1 second ago)

Apply Delay: 0 minutes

 

Transport-Related Information:

Transport On: Yes

Gap Status: No Gap

Transport Lag: 0 seconds (computed 0 seconds ago)

Transport Status: Success

 

Log Files Cleared:

dgex Standby Redo Log Files: Cleared

dgcpy2 Online Redo Log Files: Cleared

dgcpy2 Standby Redo Log Files: Available

 

Current Log File Groups Configuration:

Thread # Online Redo Log Groups Standby Redo Log Groups Status

(dgex) (dgcpy2)

1 2 3 Sufficient SRLs

2 2 3 Sufficient SRLs

 

Future Log File Groups Configuration:

Thread # Online Redo Log Groups Standby Redo Log Groups Status

(dgcpy2) (dgex)

1 2 3 Sufficient SRLs

2 2 3 Sufficient SRLs

 

Current Configuration Log File Sizes:

Thread # Smallest Online Redo Smallest Standby Redo

Log File Size Log File Size

(dgex) (dgcpy2)

1 200 MBytes 200 MBytes

2 200 MBytes 200 MBytes

 

Future Configuration Log File Sizes:

Thread # Smallest Online Redo Smallest Standby Redo

Log File Size Log File Size

(dgcpy2) (dgex)

1 200 MBytes 200 MBytes

2 200 MBytes 200 MBytes

 

Apply-Related Property Settings:

Property dgex Value dgcpy2 Value

DelayMins 0 0

ApplyParallel AUTO AUTO

ApplyInstances 0 0

 

Transport-Related Property Settings:

Property dgex Value dgcpy2 Value

LogShipping ON ON

LogXptMode ASYNC ASYNC

Dependency <empty> <empty>

DelayMins 0 0

Binding optional optional

MaxFailure 0 0

ReopenSecs 300 300

NetTimeout 30 30

RedoCompression DISABLE DISABLE

 

This handy output shows everything we need to know about the status of the Dataguard standby. It shows the switchover status (ready for switchover). It shows if it is ready for failover, notes that the primary is running, and the settings of some critical properties.

Now, take special note of the section “Managed by clusterware”. This section is especially relevant starting with Oracle 12.2. That is when the static listener entry (dgcpy_DGMGLR) became no longer required. If your database is registered and managed by clusterware (CRS) or oracle High Availability Services (HAS), the broker will use the services to connect to the database when required rather than the static listener entry. And there is a validate option for this:

DGMGRL> validate static connect identifier for ‘dgcpy’;

Oracle Clusterware on database “dgcpy” is available for database restart.

This is a very useful feature. Keep in mind however, that if you are building the standby with the ‘duplicate target database for standby from active database’ command, will need the static connect identifier for the build. Once the standby is working, you can register that database in Oracle clusterware or HAS, but the static connect identifier is required for the initial build.

Some other useful commands:

Validate database <databasename> spfile; will show differences in the spfile among instances:

DGMGRL> validate database ‘dgcpy’ spfile;

Connecting to “dgex”.

Connected to “dgex”

 

Connecting to “dgcpy”.

Connected to “dgcpy”

 

 

Parameter settings with different values:

 

cluster_database:

dgex (PRIMARY) : true

dgcpy : NOT SPECIFIED

 

dispatchers:

dgex (PRIMARY) : (PROTOCOL=TCP) (SERVICE=dgexXDB)

dgcpy : NOT SPECIFIED

 

undo_tablespace:

dgex (PRIMARY) : NOT SPECIFIED

dgcpy : UNDOTBS2

 

Validate network configuration verifies that there are no problems with the network settings. This is extremely useful as 90% of the problems that you run into with Oracle Dataguard will be related to the network:

DGMGRL> validate network configuration for all;

Connecting to instance “dgex1” on database “dgex” …

Connected to “dgex”

Checking connectivity from instance “dgex1” on database “dgex to instance “dgex” on database “dgcpy”…

Succeeded.

Checking connectivity from instance “dgex1” on database “dgex to instance “dgex” on database “dgcpy2″…

Succeeded.

Connecting to instance “dgex2” on database “dgex” …

Connected to “dgex”

Checking connectivity from instance “dgex2” on database “dgex to instance “dgex” on database “dgcpy”…

Succeeded.

Checking connectivity from instance “dgex2” on database “dgex to instance “dgex” on database “dgcpy2″…

Succeeded.

Connecting to instance “dgex” on database “dgcpy” …

Connected to “dgcpy”

Checking connectivity from instance “dgex” on database “dgcpy to instance “dgex1” on database “dgex”…

Succeeded.

Checking connectivity from instance “dgex” on database “dgcpy to instance “dgex2” on database “dgex”…

Succeeded.

Checking connectivity from instance “dgex” on database “dgcpy to instance “dgex” on database “dgcpy2″…

Succeeded.

Connecting to instance “dgex” on database “dgcpy2” …

Connected to “dgcpy2”

Checking connectivity from instance “dgex” on database “dgcpy2 to instance “dgex1” on database “dgex”…

Succeeded.

Checking connectivity from instance “dgex” on database “dgcpy2 to instance “dgex2” on database “dgex”…

Succeeded.

Checking connectivity from instance “dgex” on database “dgcpy2 to instance “dgex” on database “dgcpy”…

Succeeded.

 

Oracle Clusterware on database “dgex” is available for database restart.

 

Oracle Clusterware on database “dgcpy” is available for database restart.

 

Oracle Clusterware on database “dgcpy2” is available for database restart.

 

So, Oracle has thoroughly enhanced the Dataguard broker with an entire series of new features. For those of us who used to primarily use the ‘show configuration’ and ‘show database’ options, this is a very nice enhancement.

DGMGRL> show configuration

 

Configuration – dgex_config

 

Protection Mode: MaxPerformance

Members:

dgex – Primary database

dgcpy – Physical standby database

dgcpy2 – Physical standby database

 

Fast-Start Failover: Disabled

 

Configuration Status:

SUCCESS (status updated 60 seconds ago)

 

DGMGRL> show configuration verbose;

 

Configuration – dgex_config

 

Protection Mode: MaxPerformance

Members:

dgex – Primary database

dgcpy – Physical standby database

dgcpy2 – Physical standby database

 

Properties:

FastStartFailoverThreshold = ’30’

OperationTimeout = ’30’

TraceLevel = ‘USER’

FastStartFailoverLagLimit = ’30’

CommunicationTimeout = ‘180’

ObserverReconnect = ‘0’

FastStartFailoverAutoReinstate = ‘TRUE’

FastStartFailoverPmyShutdown = ‘TRUE’

BystandersFollowRoleChange = ‘ALL’

ObserverOverride = ‘FALSE’

ExternalDestination1 = ”

ExternalDestination2 = ”

PrimaryLostWriteAction = ‘CONTINUE’

ConfigurationWideServiceName = ‘dgex_CFG’

 

Fast-Start Failover: Disabled

 

Configuration Status:

SUCCESS

 

DGMGRL> show database dgex;

 

Database – dgex

 

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

dgex1

dgex2

 

Database Status:

SUCCESS

 

DGMGRL> show database verbose dgex;

 

Database – dgex

 

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

dgex1

dgex2

 

Properties:

DGConnectIdentifier = ‘dgex’

ObserverConnectIdentifier = ”

FastStartFailoverTarget = ”

PreferredObserverHosts = ”

LogShipping = ‘ON’

RedoRoutes = ”

LogXptMode = ‘ASYNC’

DelayMins = ‘0’

Binding = ‘optional’

MaxFailure = ‘0’

ReopenSecs = ‘300’

NetTimeout = ’30’

RedoCompression = ‘DISABLE’

PreferredApplyInstance = ”

ApplyInstanceTimeout = ‘0’

ApplyLagThreshold = ’30’

TransportLagThreshold = ’30’

TransportDisconnectedThreshold = ’30’

ApplyParallel = ‘AUTO’

ApplyInstances = ‘0’

StandbyFileManagement = ”

ArchiveLagTarget = ‘0’

LogArchiveMaxProcesses = ‘0’

LogArchiveMinSucceedDest = ‘0’

DataGuardSyncLatency = ‘0’

LogArchiveTrace = ‘0’

LogArchiveFormat = ”

DbFileNameConvert = ”

LogFileNameConvert = ”

ArchiveLocation = ”

AlternateLocation = ”

StandbyArchiveLocation = ”

StandbyAlternateLocation = ”

InconsistentProperties = ‘(monitor)’

InconsistentLogXptProps = ‘(monitor)’

LogXptStatus = ‘(monitor)’

SendQEntries = ‘(monitor)’

RecvQEntries = ‘(monitor)’

HostName(*)

StaticConnectIdentifier(*)

TopWaitEvents(*)

SidName(*)

(*) – Please check specific instance for the property value

 

Log file locations(*):

(*) – Check specific instance for log file locations.

 

Database Status:

SUCCESS

 

DGMGRL>

 

Oracle Enterprise Manager (OEM) also has some useful tools for managing Dataguard standbys. You can build a new standby there, and you can also get the status of the existing standbys. The status page is below, along with the output of the verification within OEM. Note that you can also manage switchover and failovers from there.

 

When you hit the ‘verify configuration’ option, it goes through a thorough check, and will even fix issues.

 

 

So, Oracle Dataguard has become an extremely robust and useful tool for Oracle High Availability. Every DBA should learn and understand this option for Oracle.

In my next post, I will go over the steps to turn one of the physical standbys to a logical standby.

 

Same Command, Multiple DBs

There are occasions when the DBA needs to run the same SQL command on multiple database instances. In this blog, we will provide an example of how to do that in the two most likely cases: 1- all the DBs reside on the server where you are working, or, 2- you have tnsnames entries for each database instance.

First the caveats. If you are using option 1, your oratab file must have entries for each instance running on the server. If you are using option 2, you must use the same password for each instance, or you must have the password information immediately available to the script. Also, for option 2, you will need the Oracle client installed on the host where you are running the commands.

We are going to assume that we only want to query Oracle instances that are up and running, so we design a for loop to scan through all the running database instances. The full script is below. We will break it down after you see the entire script:

#!/bin/bash

ORAENV_ASK=NO # Tells oraenv to not prompt for the oracle sid

export ORAENV_ASK

#makes sure the commands we need are in our path.

export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

for ORACLE_SID in `ps -e -o “cmd” | grep pmon|grep -v grep| awk -F “_” ‘{print$3}’`

do

export ORACLE_SID

. /usr/local/bin/oraenv

echo SID=$ORACLE_SID

sqlplus -s / as sysdba <<!

select count(1) Logfiles from v\$log;

select count(1) Standby_logs from v\$standby_log;

exit

!

done

The script above finds each pmon process running on the host, then extracts the oracle sid from the process name. It then uses the Oracle sid to set the environment for that oracle instance. The file /etc/oratab is required by the oraenv script to set the environment for the oracle database, which is why I noted above that the oratab is required. Since these are local instances, we don’t need the password to log in. Note that since this is a shell script, and the special characters for the shell script include dollar signs, we must escape (precede it with a ‘\’) the dollar sign in order to query the specific views we need.

Breaking down the scrip:

This section sets the environment. It tells us that this is a bash shell script, sets the options for the oraenv script, and sets the path so that we can call this from a cron script or detached process should we so desire:

#This is a bash shell script

#!/bin/bash

ORAENV_ASK=NO # Tells oraenv to not prompt for the oracle sid

export ORAENV_ASK

#makes sure the commands we need are in our path.

export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

Next, the section below defines what we are looking for. We are looking for processes with the string ‘pmon’ in the name. It then extracts the Oracle SID from the process name, which always follows the underscore, e.g., pmon_orcl. In that example, the Oracle sid would be orcl. It also makes sure to exclude the process that is looking for the string ‘pmon’. Word ‘for’ tells us we are starting some sort of loop, in this case for each oracle sid we find, it is going to execute everything between the ‘do’ and the ‘done’:

for ORACLE_SID in `ps -e -o “cmd” | grep pmon|grep -v grep| awk -F “_” ‘{print$3}’

The lines below set the environment for the Oracle sid we just extracted. The oraenv script will direct the query to the appropriate oracle home and database instance. Note that earlier we set ORAENV_ASK=NO so that we are not prompted for a new oracle sid. Instead the oraenv script will use the value in the $ORACLE_SID variable:

export ORACLE_SID

. /usr/local/bin/oraenv #sets the environment for the oracle instance

This section calls SQLPLUS and tells it to run all the commands between the two exclamation marks (!). Note the ‘\’ embedded in the query to escape the dollar sign, which is a special character in Linux:

sqlplus -s / as sysdba <<!

select count(1) Logfiles from v\$log;

select count(1) Standby_logs from v\$standby_log;

exit;

!

The ‘done’ statement simply means we are at the end of the loop.

And below is what the output could look like:

SID=ORCL

LOGFILES

———-

16

STANDBY_LOGS

————

20

SID=myorcl

LOGFILES

———-

16

STANDBY_LOGS

————

20

Now, the same script using just a list of tns entries would look like this (assuming the client is installed in /home/oracle/client):

#!/bin/bash

export ORACLE_HOME=/home/oracle/client

export PATH=$ORACLE_HOME/bin:$PATH

FILENAME=”tns_names.txt”

IFS=”,”

while read tns user pass

do

sqlplus -s ${user}/${pass}@${tns} <<!

select count(1) Logfiles from v\$log;

select count(1) Standby_logs from v\$standby_log;

exit;

!

done < $FILENAME

Now let’s break this one down.

The commands below set the environment. SQLPLUS will be in the bin directory under the client home directory, so that must be in your path. Also, oracle will automatically look in the $ORACLE_HOME/network/admin directory for the tnsnames.ora file which contains the descriptors for the databases that our tnsname points to. If you prefer, you can set the TNS_ADMIN variable to point to the directory containing the tnsnames.ora file.

#!/bin/bash

export ORACLE_HOME=/home/oracle/client

export PATH=$ORACLE_HOME/bin:$PATH

Next, the filename. The filename points to a comma separated file in this format:

Tnsname,username,password. This is not the tnsnames.ora file. The tnsnames.ora file contains the full connect descriptor. The tns_names.txt file will contain only the database instances that this script is supposed to hit.

FILENAME=”tns_names.txt”

Next, IFS. IFS is the internal Linux variable that defines the internal field separator for the records being read in.

IFS=”,”

Next, we just loop. It reads the comma separated line from the file:

while read tns user pass

do

And just as in the previous example, it executes the commands in sqlplus on each database instance:

sqlplus -s ${user}/${pass}@${tns} <<!

select count(1) Logfiles from v\$log;

select count(1) Standby_logs from v\$standby_log;

exit;

!

Finally, the final line tells Linux where to read from. It seems a little backward, but that is the way Linux works. It reads through the entire command to find the input file:

done < $FILENAME

The output for this script is identical to the one above, though we must include the username/password. Please note that if you connect as sys, you will need to change the sqlplus line to read like this:

sqlplus -s ${user}/${pass}@${tns} as sysdba <<!

And there it is, a simple structure to use to run SQL commands on multiple oracle databases.

Note that you can also do this from within Oracle Enterprise Manager (OEM). All Targets > Databases > Run Sql will take you to a screen where you can run the same SQL commands on multiple database instances using the saved credentials in OEM.

DevOPs and the DBA. Or, DBAs are not Developers

This blog is a little bit of a different topic for us DBAs, but I decided an occasional editorial is fine. And it’s my blog after all.

Currently, just about every company involved in software development has adopted the agile model of software development, under the heading of Dev-Ops. And this is fine if you are under pressure to continually improve software, and the more frequent bugs resulting from this rapid development are worth the risk. However, corporate culture is creating its own problems with this approach. And it’s pretty typical of large corporations, that classic attempt to fit the square peg into the round hole.

From the corporate offices, everyone in IT looks like developers, regardless of their actual job. As a consequence, many large companies seem to have decided that everyone in their IT department is involved in Dev-Ops, regardless of their actual job responsibilities. And this makes them participate in those Dev-Ops activities, such as scrums, planning epics, and writing stories and tasks. But really the job of the DBA is support. The DBA keeps the database up and running, monitors it for problems, manages backup and recovery, and tunes it for performance. The only thing a DBA does that is part of development may be creating and modifying schemas per the specifications of the application. The software that the DBA works on is the database, and he or she does not write code that runs the database.

The fact is that only software developers belong on Dev-Ops teams. And only software developers should be involved in the whole agile development epic-sprint-story development model. A DBA, whether Postgres, MySQL, Sql Server, or Oracle is not a developer. The job of a DBA is normally to keep the databases up and running, and often to make the schema changes requested by the development team. It is not the job of the DBA to develop software, at least not beyond what is needed to monitor the databases. Similarly, Linux Admins, storage Admins, etc. are not part of the Dev-Ops team. The DBA’s job is support, of both the databases that the Dev-ops team is hitting and the corporate databases such as E-Business or others.

Fundamentally the task of these vital personnel is to keep the database and related infrastructure up, running, and performant. Not to write programs. Often, the primary performance metric of the DBA is database up time. Not lines of code or new releases. That fact alone should make it clear that the job of the DBA is support, not development.

A DBA may have projects, such as re-platforming the databases, that require planning and work. But the steps in these projects are not small, incremental steps that the Dev-Ops model is designed for. These are traditional projects with dependencies and dates on which major changes are implemented. You do not, for example, move a 100 TB or even 1 TB business critical database to a new platform, whether AWS, Private Cloud, or physical server, in a series of small steps in which mistakes are tolerated. You plan the move in advance, you rehearse it with a deadline in mind, and you make the move as a single step. This is not an Agile project, it’s a project with milestones and often a project manager.

If your company has your DBAs working in a Dev-Ops model, you are probably creating unhappy DBAs by trying to fit their work of keeping the company running into a model that is entirely unsuited for it. The continuous updating of stories and tasks that you are requiring of them is really causing him or her to waste time that is better used on his real job of keeping the company running. Dev-ops is for developers, not DBAs

 

Creating an Oracle RAC (Real Application Cluster) using ASMLIB or Udev

In the previous post in this series we discussed the two most common methods for mapping shared storage for an Oracle RAC. In this post, we are going to discuss creating RAC using asmlib. The changes for creation using udev are covered at the end of this post. As a review, below is the section of the previous post that discussed configuring shared storage using asmlib:

CONFIGURING SHARED STORAGE USING ORACLE ASMLIB

Now, if you are going to map the shared storage using asmlib, there are more steps involved than using udev. The base oracleasm package is part of the kernel. Add the oracleasm support package: ‘yum -y install oracleasm-support’ on all nodes. Then run the command ‘oracleasm configure -i’ to set up the asmlib packages. Answer the questions as shown below:

[root@racnode2 ~]# oracleasm configure -i

Configuring the Oracle ASM library driver.

 
 

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets (‘[]’). Hitting <ENTER> without typing an

answer will keep that current value. Ctrl-C will abort.

 
 

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

[root@racnode2 ~]#reboot

 
 

Ensure that the above commands are done on all nodes in the cluster. As noted, reboot after configuring asm.

 
 

The next set of commands are done on only the first cluster node.

First, create partitions on the shared disks. Repeat for each disk:

[root@racnode1 ~]# fdisk /dev/sdb

Welcome to fdisk (util-linux 2.23.2).

 
 

Changes will remain in memory only, until you decide to write them.

Be careful before using the write command.

 
 

Device does not contain a recognized partition table

Building a new DOS disklabel with disk identifier 0x619c61f3.

 
 

Command (m for help): n

Partition type:

p primary (0 primary, 0 extended, 4 free)

e extended

Select (default p): p

Partition number (1-4, default 1):

First sector (2048-41943039, default 2048):

Using default value 2048

Last sector, +sectors or +size{K,M,G} (2048-41943039, default 41943039):

Using default value 41943039

Partition 1 of type Linux and of size 20 GiB is set

 
 

Command (m for help): w

The partition table has been altered!

 
 

Calling ioctl() to re-read partition table.

Syncing disks.

[root@racnode1 ~]#

 
 

Note that on the above, the first sector is 2048 to ensure that the partition starts on a sector boundary. This partition alignment is the default in OEL7, but make sure that the sector alignment is there. It makes a substantial difference in performance.

The next step is to mark each disk for oracle asm: ‘oracleasm createdisk <name> <device>’. For example, ‘oracleasm createdisk ASMDISK1 /dev/sdb1’. Do this for each node, something like this:

[root@racnode1 ~]# oracleasm createdisk asmdisk1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk2 /dev/sdc1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk3 /dev/sdd1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk4 /dev/sde1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk5 /dev/sdf1

Writing disk header: done

Instantiating disk: done

 
 

Next, on each additional host run the command ‘oracleasm scandisks’, then ‘oracleasm listdisks’. The output should look something like this:

[root@racnode2 ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks…

Scanning system for ASM disks…

Instantiating disk “ASMDISK1”

Instantiating disk “ASMDISK2”

Instantiating disk “ASMDISK4”

Instantiating disk “ASMDISK3”

Instantiating disk “ASMDISK5”

[root@racnode2 ~]# oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASMDISK4

ASMDISK5

[root@racnode2 ~]#

 
 

This completes the shared device configuration using oracle asmlib.

See this link for shared device configuration using udev:

https://dbakerber.wordpress.com/2019/10/16/udev-rules-for-oracle-storage/

The cluster build step by step that follows uses asmlib. At the end of this post, the changes required when using udev rules are noted. The changes are fairly minor.

Personally, I prefer to use udev rules because it allows us to skip the partitioning step, and reduces the number of oracle packages running on the host. Also, it can be problematic patching when using asmlib since it is a kernel package for OEL.

Now download the Oracle GI (Grid Infrastructure) software from here. You should download this onto the first cluster node as the oracle user:

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

Note that this is an entire home installation. Once downloaded, create a directory for the files. Most people create a /u01 mountpoint and file system for the Oracle binaries. Note that initially this must be owned by Oracle. Run the following commands (as root) on all the cluster nodes:

#mkdir -p /u01

#chown oracle:dba /u01

 

Next, log back in as Oracle, and create the full path for the oracle GI home on the first node. The installation process will create the required directories on the other nodes as long as /u01 has been created and is owned by oracle:

[oracle@racnode1 ~]$ mkdir -p /u01/app/19.3.0/grid

Next, move or copy the installation file to that location, and unzip the GI home.

[oracle@racnode1 grid]$ mv LINUX.X64_193000_grid_home.zip /u01/app/19.3.0/grid/

[oracle@racnode1 grid]$ cd /u01/app/19.3.0/grid/

[oracle@racnode1 grid]$ unzip LINUX.X64_193000_grid_home.zip

 

Note that we will be using xwindows to complete the installation. That is the reason we chose the GUI installation option when we first created the VMware guests. If you have questions on how to configure xwindows with putty, see this post (look section 2): https://dbakerber.wordpress.com/2015/09/23/installing-oracle-software-part-1-prerequisites/.

 

For further reference, below is the hosts file for this installation. I have configured dnsmasq for names resolution:

 

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.12.1.107 racnode1 racnode1.localdomain

10.12.1.136 racnode2 racnode2.localdomain

 

10.12.1.108 racnode1-vip racnode1-vip.localdomain

10.12.1.137 racnode2-vip racnode2-vip.localdomain

 

10.12.1.146 racnode1-scan racnode1-scan.localdomain

10.12.1.147 racnode1-scan racnode1-scan.localdomain

10.12.1.148 racnode1-scan racnode1-scan.localdomain

 

192.168.63.101 racnode1-priv1 racnode1-priv1.localdomain

192.168.44.101 racnode1-priv2 racnode1-priv2.localdomain

192.168.63.102 racnode2-priv1 racnode2-priv1.localdomain

192.168.44.102 racnode2-priv2 racnode2-priv2.localdomain

 

Start the installation process by running the gridSetup.sh program.

[oracle@racnode1 grid]$ ./gridSetup.sh

 

We are building a new cluster.

 

This is a standalone cluster.

The name of the cluster isn’t really important, though the name must be 15 characters or fewer in length, and contain only letters, numbers, and hyphens (no underscores). The requirements are the same for the scan name, and the length is not validated here, so make sure you check it yourself. If the names are too long, the root script will fail.

 

This automatically includes the name of the host you are running on. Add additional hosts.

 

Next, click on the ss connectivity button, shown below.

Enter the oracle password, and tell it to setup. This sets up passwordless ssh across all the nodes in the cluster.

 

 

Click ok, then next.

Assign the networks as shown below. We will used the ens35 network in the database setup, so make sure it stays as do not use.

 

 

While Oracle wants you to create the GIMR, I have never seen any purpose in it at all. As far as I can tell, it just takes valuable resources. And in any case, this is not a production build.

On the next screen, the /dev/sd* devices will show up. First, try and changing the disk discovery string to ORCL:*. Sometimes this works. Most often, I have had to change it to /dev/oracleasm/disks/*

 

The shared disks should now show up. Three will be needed for normal redundancy, and since this is the data for the cluster, the group should be either normal or high redundancy. Five disks would be needed for high redundancy. Enable the ASM filter driver if you desire.

Next, enter the passwords as appropriate. I typically use the same password for everything, then change and/or lock accounts as required.

Do not enable IPMI.

 

Register the cluster with OEM if you want. You need to have the OEM agent already installed, and OEM set up and running in order to do this.

I normally use dba for all groups. Sometimes oinstall is also used.

Don’t worry about any errors on the groups.

Take the defaults for oracle base and the inventory location. For a GI RAC home, the location must be outside of the oracle base.

 

I normally run the root scripts myself so I can better monitor for errors.

 

Check for the prereqs, and run the fixup script if necessary.

The items below can be safely ignored.

Continue.

Click install, and let it run.

When all files are set up and copied, you will be prompted to run the root scripts. Run orainstRoot.sh on each node, then run root.sh on each node. Note that root.sh must be run on node 1 first, then on the other nodes in any order.

Below is the output of the root scripts.

[root@racnode1 ~]# /tmp/GridSetupActions2021-02-11_10-43-18AM/CVU_19.0.0.0.0_oracle/runfixup.sh

All Fix-up operations were completed successfully.

[root@racnode1 ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

 

Changing groupname of /u01/app/oraInventory to oinstall.

The execution of the script is complete.

[root@racnode1 ~]# /u01/app/19.3.0/grid/root.sh

Performing root user operation.

 

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/19.3.0/grid

 

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin …

Copying oraenv to /usr/local/bin …

Copying coraenv to /usr/local/bin …

 

 

Creating /etc/oratab file…

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Relinking oracle with rac_on option

Using configuration parameter file: /u01/app/19.3.0/grid/crs/install/crsconfig_params

The log of current session can be found at:

/u01/app/oracle/crsdata/racnode1/crsconfig/rootcrs_racnode1_2021-02-11_11-30-32AM.log

2021/02/11 11:30:44 CLSRSC-594: Executing installation step 1 of 19: ‘SetupTFA’.

2021/02/11 11:30:44 CLSRSC-594: Executing installation step 2 of 19: ‘ValidateEnv’.

2021/02/11 11:30:44 CLSRSC-363: User ignored prerequisites during installation

2021/02/11 11:30:44 CLSRSC-594: Executing installation step 3 of 19: ‘CheckFirstNode’.

2021/02/11 11:30:47 CLSRSC-594: Executing installation step 4 of 19: ‘GenSiteGUIDs’.

2021/02/11 11:30:48 CLSRSC-594: Executing installation step 5 of 19: ‘SetupOSD’.

2021/02/11 11:30:48 CLSRSC-594: Executing installation step 6 of 19: ‘CheckCRSConfig’.

2021/02/11 11:30:49 CLSRSC-594: Executing installation step 7 of 19: ‘SetupLocalGPNP’.

2021/02/11 11:32:02 CLSRSC-594: Executing installation step 8 of 19: ‘CreateRootCert’.

2021/02/11 11:32:15 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.

2021/02/11 11:32:16 CLSRSC-594: Executing installation step 9 of 19: ‘ConfigOLR’.

2021/02/11 11:32:33 CLSRSC-594: Executing installation step 10 of 19: ‘ConfigCHMOS’.

2021/02/11 11:32:33 CLSRSC-594: Executing installation step 11 of 19: ‘CreateOHASD’.

2021/02/11 11:32:40 CLSRSC-594: Executing installation step 12 of 19: ‘ConfigOHASD’.

2021/02/11 11:32:41 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.service’

2021/02/11 11:33:09 CLSRSC-594: Executing installation step 13 of 19: ‘InstallAFD’.

2021/02/11 11:33:17 CLSRSC-594: Executing installation step 14 of 19: ‘InstallACFS’.

2021/02/11 11:33:29 CLSRSC-594: Executing installation step 15 of 19: ‘InstallKA’.

2021/02/11 11:33:38 CLSRSC-594: Executing installation step 16 of 19: ‘InitConfig’.

 

ASM has been created and started successfully.

 

[DBT-30001] Disk groups created successfully. Check /u01/app/oracle/cfgtoollogs/asmca/asmca-210211AM113420.log for details.

 

2021/02/11 11:35:27 CLSRSC-482: Running command: ‘/u01/app/19.3.0/grid/bin/ocrconfig -upgrade oracle oinstall’

CRS-4256: Updating the profile

Successful addition of voting disk b349b830f8644fc2bf0433b684627a8b.

Successful addition of voting disk 39ef9e8dd1674f06bfdc2b727c5b0583.

Successful addition of voting disk 9b708235ed7e4f1cbfd211a72b8facfa.

Successfully replaced voting disk group with +DATA.

CRS-4256: Updating the profile

CRS-4266: Voting file(s) successfully replaced

## STATE File Universal Id File Name Disk group

— —– —————– ——— ———

1. ONLINE b349b830f8644fc2bf0433b684627a8b (/dev/oracleasm/disks/ASMDISK1) [DATA]

2. ONLINE 39ef9e8dd1674f06bfdc2b727c5b0583 (/dev/oracleasm/disks/ASMDISK2) [DATA]

3. ONLINE 9b708235ed7e4f1cbfd211a72b8facfa (/dev/oracleasm/disks/ASMDISK3) [DATA]

Located 3 voting disk(s).

2021/02/11 11:37:30 CLSRSC-594: Executing installation step 17 of 19: ‘StartCluster’.

2021/02/11 11:39:12 CLSRSC-343: Successfully started Oracle Clusterware stack

2021/02/11 11:39:12 CLSRSC-594: Executing installation step 18 of 19: ‘ConfigNode’.

2021/02/11 11:42:21 CLSRSC-594: Executing installation step 19 of 19: ‘PostConfig’.

2021/02/11 11:43:18 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster … succeeded

[root@racnode1 ~]#

 

After clicking ok that the scripts have been run, it will run through several more steps and then continue to the finish screen. Sometimes the cluster integrity check fails, but if ASM is running and you can see the shared space in ASM, everything should be fine.

 

Click close.

At this point, the cluster is built.

 

Notes for using udev rules instead of ASMLIB.

If you choose to use udev rules, the differences are minor. Where we entered the disc discover path as /dev/oracleasm/disks above, change it to /dev/oracleasm/*, as below:

You will notice that the names of the disks are the names defined in the file /etc/udev/rules.d/99-oracleasm.rules.

 

Once again, to create a disk group with normal redundancy, we need three disks.

 

The remainder of the process is the same as above.

In this post, we have completed the creation of an Oracle RAC (Real Application Cluster) on VMware workstation guests using oracle enterprise linuz 7.9 (OEL7.9).

Oracle Software Installation on a VMware Workstation Guest – Phase 1, Shared Storage

In my previous posts, I covered the creation of a VMware workstation guest in Oracle Enterprise Linux to be used in an Oracle RAC (Real Application Cluster). In this post, we will discuss the next steps in creation of the Oracle RAC using ASM. We will cover using both asmlib and udev rules for identify the Oracle ASM storage.

An Oracle database RAC is always installed using storage that is shared on all nodes in the cluster. When we created the VMware guests, we also created a group of VMDKS (shared disks) that are mapped to all the VMware guests. These will be used for the ASM storage.

You may recall that when the workstations were set up, these lines were added to the VMX file:

disk.locking = “FALSE”

diskLib.dataCacheMaxSize = “0”

disk.enableuuid = “TRUE”

bios.bootdelay = “8000”

The first two settings, disk.locking and disklib.DatacacheMaxSize, are required for the disks to be shared properly. The disk.enableuuid setting is required in order to use udev rules for shared disks. This is not required if oracle asmlib is used, but does not prevent asmlib usage either.

The first step is to install the oracle preinstall package. On each node of your cluster install the package oracle-database-preinstall-19c. ‘yum -y install oracle-database-preinstall-19c’. This will install the required packages for the oracle database, set up certain operating system parameters in sysctl.conf, and create the Oracle user with appropriate groups and privileges.

Next, on each node, set the password for the oracle user. The password should be the same on all nodes. We are going to use 0Racle@ (zero, not o) for the purpose of this exercise.

As root:

#passwd oracle

Changing password for user oracle

New password:

Retype new password:

passwd: all authentication tokes updated successfully.

 

Do this on all the cluster nodes. It sets the oracle password and allows the user to log in as the oracle user.

Now, in order to configure the shared storage, you only use one of the two options below. Either use udev rules or asmlib. Do not do both.

CONFIGURING SHARED STORAGE USING UDEV RULES.

Now we prepare the shared storage. First, lets discuss doing this with udev rules, which is the method I prefer. In order to use udev rules, the disk.enableuuid options must be set to true. This causes VMware to generate a UUID for each vmdk. Identifying the UUID used to be a fairly involved process, but since the onset of RHEL/OEL 7 it has become much simpler. There is a discussion here on how to do it: https://dbakerber.wordpress.com/2019/10/16/udev-rules-for-oracle-storage/, and a short version of that information follows.

This short script generates the required lines which you will use:

#!/bin/sh

COUNTER=0

for disk in `ls /dev/sd* -c1 | grep -v sda`

do

shortserial=`udevadm info –query=property –name $disk | grep ID_SERIAL_SHORT | awk -F “=” ‘{print $2}’`

COUNTER=$((COUNTER+1))

echo KERNEL==\”sd?\”, ENV{ID_SERIAL_SHORT}==\”$shortserial\” SYMLINK+=\”oracleasm/asm-disk$COUNTER\”, OWNER=\”oracle\”, GROUP=\”dba\”, MODE=\”0660\”

done

 

Output:

KERNEL==”sd?”, ENV{ID_SERIAL_SHORT}==”6000c29db4db52b2e2bee9c23aa56fa5″ SYMLINK+=”oracleasm/asm-disk1″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?”, ENV{ID_SERIAL_SHORT}==”6000c29d94c12a97079919b62a7b03ad” SYMLINK+=”oracleasm/asm-disk2″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?”, ENV{ID_SERIAL_SHORT}==”6000c297d9addf5b1cdb04390a28709c” SYMLINK+=”oracleasm/asm-disk3″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?”, ENV{ID_SERIAL_SHORT}==”6000c29e9e2a48db3724ce436216e2bc” SYMLINK+=”oracleasm/asm-disk4″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

KERNEL==”sd?”, ENV{ID_SERIAL_SHORT}==”6000c2991798200359672b5ee090813b” SYMLINK+=”oracleasm/asm-disk5″, OWNER=”oracle”, GROUP=”dba”, MODE=”0660″

 

Create a file called /etc/udev/rules.d/99-oracleasm.rules and copy the output into this file. There should be 1 line for each shared vmdk. Now copy this file to each node in your cluster. Note that the same file should be on every server. This ensures that each vmdk will have the same name on each. After copying the file, reboot each node.

After reboot, check to make sure the shared storage is available:

[oracle@racnode1 ~]$ ls -la /dev/oracleasm

total 0

drwxr-xr-x 2 root root 140 Feb 10 14:38 .

drwxr-xr-x 21 root root 3660 Feb 10 14:38 ..

lrwxrwxrwx 1 root root 6 Feb 10 14:38 asm-disk1 -> ../sdf

lrwxrwxrwx 1 root root 6 Feb 10 14:38 asm-disk2 -> ../sdc

lrwxrwxrwx 1 root root 6 Feb 10 14:38 asm-disk3 -> ../sdb

lrwxrwxrwx 1 root root 6 Feb 10 14:38 asm-disk4 -> ../sde

lrwxrwxrwx 1 root root 6 Feb 10 14:38 asm-disk5 -> ../sdd

[oracle@racnode1 ~]$

 

On each node, you should see the above output. Note that the /sd letters may be different for each host, but the asm-diskx mapping will be the same, which is the reason for the udev rules. When we map these in ASM, we will use the asm-diskx name.

 

CONFIGURING SHARED STORAGE USING ORACLE ASMLIB

Now, if you are going to map the shared storage using asmlib, there are more steps involved than using udev. The base oracleasm package is part of the kernel. Add the oracleasm support package: ‘yum -y install oracleasm-support’ on all nodes. Then run the command ‘oracleasm configure -i’ to set up the asmlib packages. Answer the questions as shown below:

[root@racnode2 ~]# oracleasm configure -i

Configuring the Oracle ASM library driver.

 

This will configure the on-boot properties of the Oracle ASM library

driver. The following questions will determine whether the driver is

loaded on boot and what permissions it will have. The current values

will be shown in brackets (‘[]’). Hitting <ENTER> without typing an

answer will keep that current value. Ctrl-C will abort.

 

Default user to own the driver interface []: oracle

Default group to own the driver interface []: dba

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

[root@racnode2 ~]#reboot

 

Ensure that the above commands are done on all nodes in the cluster. As noted, reboot after configuring asm.

 

The next set of commands are done on only the first cluster node.

First, create partitions on the shared disks. Repeat for each disk. The first disk to be partitioned should be /dev/sdb. But verify that is the correct disk by running fdisk -l to verify it is one of the shared disks.

[root@racnode1 ~]# fdisk /dev/sdb

Welcome to fdisk (util-linux 2.23.2).

 

Changes will remain in memory only, until you decide to write them.

Be careful before using the write command.

 

Device does not contain a recognized partition table

Building a new DOS disklabel with disk identifier 0x619c61f3.

 

Command (m for help): n

Partition type:

p primary (0 primary, 0 extended, 4 free)

e extended

Select (default p): p

Partition number (1-4, default 1):

First sector (2048-41943039, default 2048):

Using default value 2048

Last sector, +sectors or +size{K,M,G} (2048-41943039, default 41943039):

Using default value 41943039

Partition 1 of type Linux and of size 20 GiB is set

 

Command (m for help): w

The partition table has been altered!

 

Calling ioctl() to re-read partition table.

Syncing disks.

[root@racnode1 ~]#

 

Note that on the above, the first sector is 2048 to ensure that the partition starts on a sector boundary. This partition alignment is the default in OEL7, but make sure that the sector alignment is there. It makes a substantial difference in performance.

The next step is to mark each disk for oracle asm: ‘oracleasm createdisk <name> <device>’. For example, ‘oracleasm createdisk ASMDISK1 /dev/sdb1’. Do this for each node, something like this:

[root@racnode1 ~]# oracleasm createdisk asmdisk1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk2 /dev/sdc1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk3 /dev/sdd1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk4 /dev/sde1

Writing disk header: done

Instantiating disk: done

[root@racnode1 ~]# oracleasm createdisk asmdisk5 /dev/sdf1

Writing disk header: done

Instantiating disk: done

 

Next, on each additional host run the command ‘oracleasm scandisks’, then ‘oracleasm listdisks’. The output should look something like this:

[root@racnode2 ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks…

Scanning system for ASM disks…

Instantiating disk “ASMDISK1”

Instantiating disk “ASMDISK2”

Instantiating disk “ASMDISK4”

Instantiating disk “ASMDISK3”

Instantiating disk “ASMDISK5”

[root@racnode2 ~]# oracleasm listdisks

ASMDISK1

ASMDISK2

ASMDISK3

ASMDISK4

ASMDISK5

[root@racnode2 ~]#

 

This completes the shared device configuration using oracle asmlib.

 

Next, we install the Oracle Grid Infrastructure software. This will be done in my next post.

VMware Workstation Creation for Oracle RAC (part 3)

In this post, we will complete the buildout of the VMware workstation guests for an Oracle RAC (real application cluster).

At this point, we have built a single VMware guest with prerequisites need to create an Oracle RAC. Next, we will create the additional host or hosts that are required, and make the changes to those hosts needed to complete the build of an Oracle RAC.

The first step is to shut down your existing workstation if you have not already. Just use the command ‘shutdown -h now’.

Next, we clone the VM. From VMware workstation, choose Manage=>clone.

Clone the current state of the machine.

You will be making a full clone. Linked clones do not work for this.

Choose the name and location, and choose finish. The new workstation will be built. Since the full disk space must be preallocated, it will take some time.

When completed, click close. Do not start up the new host yet. There are some settings that need to be changed. Specifically, we want to assign new mac addresses to the NICs in the VM. Any routers that used reserved IPs will use the MAC address to assign them, so we want to make sure this workstation has different MAC addresses than the original.

On the settings screen, click on a network card and choose ‘advanced’.

Where it says MAC address, choose generate, then OK.

Do the same for each NIC.

Now that you have assigned the MAC addresses, power on the VM.

Log into the VM as the root user, and go to applications=>system tools=>settings=>network

Then, just as done in the initial set up, set up the IP addresses. The first NIC should have a public IP assigned, and you will want to reserve that in your network.

Assign appropriate IP addresses to the other NICs. Example below:

Next, save the changes and exit the conversation.

Open a command prompt window and set the host name. The command is: ‘hostnamectl set-hostname <new host name>’ , in our case: ‘hostnamectl set-hostname racnode2’.

Do the same for each cluster node. I have had as many as a 4 node cluster running on my desktop hardware, but the performance was not very good at that point.

Our next step is to set up the hosts file for cluster. In an Oracle RAC, each host needs the following address set:

1 physical address on the public network

1 virtual address on the public network

1 private address on a network

A second private address on a private network is optional.

In addition, each cluster needs a scan name that resolves to 3 different IP addresses on the public network. That is a single name that resolves to three addresses.

Below is an example of a typical hosts file for an Oracle RAC:

10.12.1.106    racnode1    racnode1.localdomain

10.12.1.117    racnode2    racnode2.localdomain

10.12.1.107    racnode1-vip    racnode1-vip.localdomain

10.12.1.118    racnode2-vip    racnode2-vip.localdomain

192.168.63.101 racnode1-priv    racnode1-priv.localdomain

102.168.44.101 racnode1-priv2    racnode1-priv2.localdomain

192.168.63.102    racnode2-priv    racnode2-priv.localdomain

192.168.44.102    racnode2-priv2    racnode2-priv2.localdomain

10.12.1.120    racnode-scan    racnode-scan.localdomain

10.12.1.121    racnode-scan    racnode-scan.localdomain

10.12.1.122    racnode-scan    racnode-scan.localdomain

In this example, the 10.12.1.x is the public network, and the addresses that start with 192.168.x.x are the private network. Note that the virtual IPs and scan addresses are on the public network. Also note how the scan address has 3 IPs, but the name is same on all three.

Next, restart dnsmasq (systemctl restart dnsmasq), and verify that the names resolve properly. Once this is verified, copy the hosts file to all the RAC nodes and restart dnsmasq there.

Now, shut down all other nodes except the first so we can build and add the shared storage.

On the screen for the VMware workstation, choose settings. We are choosing to do the creation from racnode2, but they can be created on any of the workstations. Sadly you can only create one disk at a time.

Click on add, and choose hard disk, then next:

Take the default, SCSI.

Create a new virtual disk.

On the next screen, choose options below. These disks are going to be used as ASM storage, so you will want all disks to be the same size. We are choosing 40G. The pre-allocation and single file options are in order to allow for best performance.

Choose next, and pick the location for the shared disk. I have multiple HDDs attached to my desktop, so normally I spread these shared disks around multiple drives. The first one goes here:

Typically I will put the second disk on F or G drive. But that is entirely up to you.

Click finish.

The disk will be allocated. Make sure that you note the full path of the disk. You will need it for the next steps.

Once the disk is created, we need to add it to the other nodes in the cluster.

So, the first part of the process is the same.

On the screen for the VMware workstation, choose settings. Then click on add, and choose hard disk, then next:

This time, we choose ‘Use an existing virtual disk’.

Next, choose the file.

Note that there is a racnode2.vmdk and a racnode2-flat.vmdk. The file we want is the first one. The second is the actual data, but VMware is looking for the first file that contains information about the disk.

Click on finish, and the disk is added to the VM.

Now, we repeat the process for as many shared vmdk’s that we will need. I usually have at least 5, and as many as 10 if I want multiple asm disk groups. Below is an example of a completed VMware workstation with a normal complement of shared disks.

Note that we have two different sizes of shared disks, as this RAC has two disk groups.

Now we have completed all the requirements for setting up VMware workstations for building our home RAC. Remember, this is not a production system and never will be. It however works very well for blogging and prototyping. The final step is installing the Oracle software and building the Real Application Cluster, which I will do in my next post.

VMware Workstation Creation for Oracle RAC (part 2)

In this post, we will complete the configuration of an Oracle Enterpise Linux VMware workstation guest for use in Oracle RAC.

In our previous post, we completed the initial build of the VMware workstation, including creation of the guest, installation of software, and the configuration of the file systems. In this brief entry, we will complete the operating system configuration for the guest.

The first step is to disable Secure Linux (selinux).

To do this, simple edit the file /etc/selinux/config. Where it says selinux=target, change the line to read selinux=disabled.

Next, disable the firewall. While it is possible just to open the appropriate ports, its easier to disable it entirely:

systemctl stop firewalld

systemctl disable firewalld

Now remove libvirt. This interferes with the dnsmasq software that we will use for names resolution:

systemctl stop libvirtd.service

Systemctl disable libvirtd.service

Now update the software to the latest releases. This will run for some time, usually the software changes are extensive.

yum -y update

Next, edit /etc/dnsmasq.conf and add the following line to the bottom of the file:

local=/localdomain/

Modify /etc/resolv.conf to read as follows:

search localdomain

nameserver 127.0.0.1

nameserver 10.12.1.1 # this would be your local nameserver, often 192.168.1.1

Next, update resolv.conf so it is not modified on reboot, ‘chattr +i /etc/resolv.conf’

At this point, your operating system updates are complete. Next, shutdown the guest ‘shutdown -h now’.

Now, modify the settings in the vmx file for the guest to accommodate shared storage. I also add a line to give a boot up delay so I can enter the bios on startup if I desire. The file is normally in a directory named for the guest in the documents and settings directory under virtual machines. Eg, C:\Users\Andrew Kerber\Documents\Virtual Machines\racnode1\racnode1.vmx

disk.locking = “FALSE”

diskLib.dataCacheMaxSize = “0”

disk.enableuuid = “TRUE”

bios.bootdelay = “8000”

Now, start the VM back up and ensure it is working properly. At this point the VM and operating system are configured properly for use in an Oracle RAC.

VMware Workstation Creation for Oracle RAC (part 1)

Building an Oracle Enterprise Linux VMware Workstation Guest for Oracle RAC

In this article, we will discuss building an Oracle Real Application Cluster using a Linux VMware workstation guests on a Windows 10 host. While not usable for a production environment, VMware workstation can be used for blogging and for doing basic setup validations.

The basic steps are as follows:

1. Purchase and download VMware workstation. You can also so this with Oracle’s Virtual Box (which is free), but the steps are slightly different.
2. Download Oracle Linux (OEL). You can download VMware templates, but I prefer to download OEL itself from the Oracle E-delivery Cloud.
3. Use VMware virtual network editor to build the internal network.
4. Start VMware workstation and build your VM.
5. Clone the VM as many times as needed for you cluster.

I am going to skip over the steps for downloading the software from Oracle E-Delivery. I downloaded OEL version 7.9.

Start VMware workstation and choose File>New Virtual Machine. Choose custom for the configuration.

 

 

 

 

 

 

 

On the next screen, choose workstation 16x. The newest version.

 

 

Under the guest operating system installation, choose the location of the OEL 7.9 ISO that we downloaded earlier.

 

 

 

Next, choose an appropriate name and location for your virtual machine.

Choose an appropriate processor configuration. Remember that this VM is running off of the hardware on the machine that you are using to build from. How much horsepower do you have to spare? In my case, I specifically configured my desktop with extra cores and RAM so I could build VMs on it. Even so, a 3 node RAC will be resource intensive.

 

Next, Choose 8G RAM. This is sufficient to run Oracle and still have memory for the desktop machine.

 

 

Next, choose an appropriate network type for the local network connection. This will normally be NAT or bridged.

 

 

Then choose an appropriate IO controller type. The default is normally fine.

 

Choose SCSI for the disk type.

 

 

We are going to create a new virtual disk.

 

On the next step, we do not take the defaults. We want to use a single file for the disk, and we want to pre-allocate the space. This gives the best performance. 150G is sufficient for a DB home, GI home, and Agent Home.

On the next screen we will customize the hardware, in this case the network interfaces.

Earlier we defined the following networks with the virtual network editor that comes with VMware workstation:

 

Each VM will have the first NIC on VMnet0, the second on VMnet1, and the third on VMnet2. VMnet0 is the public network. And 1 and 2 are private networks. So, on the next screen we choose customize hardware, create two new NICs and reconfigure the first NIC as shown below:

 

When done with the NICs, choose finish, and it will build your VM. Since it allocates 150G, it will take some time to do this.

When done building the VM it will boot up on the ISO image you specified earlier. Choose install.

Choose the appropriate language options then continue.

This will take you to the installation summary page, which is where most of the work is done. The first step is to configure the ‘Network and host name’ within the VM. Make sure you know the IP ranges for the networks that you have defined. In this case, the two local networks are 192.168.63.x and 192.168.44.x. The public network is 10.12.1.x.

For the first NIC, just turn it on and tell it to start on power up. It should have been assigned an IP within your home network. Once you see the IP that was specified, you will want to go to your home router and assigned this VM the IP it was initially granted as a reserved IP in your network. This will make sure that the names stay assigned to the same IP address.

 

 

On each NIC, make sure you choose ‘automatically connect when available’ so that the NIC activates on boot up.

 

On the second and third NICs, assign a static IP within the network range of the local network that has been assigned to this NIC.

When done with each NIC, choose ‘save’. Ensure that the configuration is saved. At this point, it should say that the network is connected.

 

Next, choose installation destination. It will bring up the screen below. Choose the ‘I will configure partitioning’ option, then click done.

After clicking done, it will bring up the screen below. Tell it to create the partitions automatically, you will then edit the layout it produces.

We will modify the configuration below to meet the requirements of an Oracle configuration. Oracle requires 16G swap, and normally you want a large / file system

 

Change the layout above to what you see here. A large / mount point, and 16G Swap.

 

When prompted, accept the partitioning changes.

Next, set the time zone as appropriate.

Next go to software selection and choose server with GUI. For additional packages, choose what you would like. I general choose system administration tools,

large systems performance, and performance tools. Don’t worry about the Oracle software prerequisites, we will get those later.

 

When done selecting software, return to the main screen. Choose whether or not to implement kdump (I normally turn it off) .

Then click on begin installation. During the installation process it will ask you to set the root password and create a user. GO ahead and do this.

Let the installation process complete. It will reboot and ask you to accept the OEL license. If you want to use the software you have to accept the license.

It will reboot again. Then log in as root and go through the various first time user screens. I always turn off the location sharing.

At this point, we are ready to complete the setup. There are four steps involved in this:

1. Configure selinux and firewall for the oracle database.
2. Update packages to the most recent version.
3. Configure DNSMASQ.
4. Install the required Oracle packages.

We will discuss those steps in the next blog.