Oracle 12c-Step by Step Manual Switchover | Data Guard

Performing Switchover Activity

Convert Primary to Standby

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE  OPEN_MODE   LOG_MODE
----------- ------------------------- -------------------- --------------------
ORADB  PRIMARY                 READ WRITE   ARCHIVELOG

SQL> col destination for a70
SQL>select b.destination,a.dest_id,b.ERROR,max(a.FIRST_TIME),max(a.sequence#) from gv$archived_log a, gv$archive_dest_status b where (a.dest_id='1' or a.applied ='YES')    and a.dest_id=b.dest_id group by  b.destination,a.dest_id,b.ERROR order by a.dest_id;
DESTINATION                                        DEST_ID ERROR    MAX(A.FIR MAX(A.SEQUENCE#)
------------------------------------------------- ------------ ------------ ---------------  ----------------
/u01/app/oracle/product/12.1.0/db_1/dbs           1                      04-AUG-24   68
oradbdr                                                                2                      04-AUG-24   68

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Alter LogFile  

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

Sun Aug 04 19:45:20 2024

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 8103] (oradb)

Sun Aug 04 19:45:20 2024

Waiting for target standby to receive all redo

Sun Aug 04 19:45:20 2024

Waiting for all non-current ORLs to be archived...

Sun Aug 04 19:45:20 2024

All non-current ORLs have been archived.

Sun Aug 04 19:45:20 2024

Waiting for all FAL entries to be archived...

Sun Aug 04 19:45:20 2024

All FAL entries have been archived.

Sun Aug 04 19:45:20 2024

Waiting for potential Physical Standby switchover target to become synchronized...

Sun Aug 04 19:45:21 2024

Active, synchronized Physical Standby switchover target has been identified

Preventing updates and queries at the Primary

Generating and shipping final logs to target standby

Switchover End-Of-Redo Log thread 1 sequence 69 has been fixed

Switchover: Primary highest seen SCN set to 0x0.0x26a747

ARCH: Noswitch archival of thread 1, sequence 69

ARCH: End-Of-Redo Branch archival of thread 1 sequence 69

ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

ARCH: Archiving is disabled due to current logfile archival

Primary will check for some target standby to have received all redo

Waiting for target standby to apply all redo

Final check for a synchronized target standby. Check will be made once.

LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target

Active, synchronized target has been identified

Target has also received all redo

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradb/oradb/trace/oradb_ora_8103.trc

Converting the primary database to a new standby database

Clearing standby activation ID 2826499478 (0xa878f596)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

Archivelog for thread 1 sequence 69 required for standby recovery

Switchover: Primary controlfile converted to standby controlfile succesfully.

Switchover: Complete - Database shutdown required

USER (ospid: 8103): terminating the instance

Sun Aug 04 19:45:24 2024

Instance terminated by USER, pid = 8103

Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

Shutting down instance (abort)

License high water mark = 5

Sun Aug 04 19:45:24 2024

Instance shutdown complete

Sun Aug 04 19:45:43 2024

On New Standby

[oracle@srv1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 4 19:45:37 2024
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> def
DEFINE _DATE           = "04-AUG-2024 19:45:40" (CHAR)
>DEFINE _CONNECT_IDENTIFIER = "oradb" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "" (CHAR)
DEFINE _O_RELEASE      = "" (CHAR)
SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1761607680 bytes
Fixed Size                    2925360 bytes
Variable Size                587205840 bytes
Database Buffers         1157627904 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE        OPEN_MODE   LOG_MODE
----------- ------------------------------ -------------------- --------------------
ORADB  PHYSICAL STANDBY  READ ONLY     ARCHIVELOG

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE        OPEN_MODE                          LOG_MODE
----------- ------------------------------ ------------------------------------  --------------------
ORADB  PHYSICAL STANDBY  READ ONLY WITH APPLY    ARCHIVELOG

Convert Standby to Primary

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME    DATABASE_ROLE        OPEN_MODE                          LOG_MODE
----------- ------------------------------ ------------------------------------  --------------------
ORADB  PHYSICAL STANDBY  READ ONLY WITH APPLY    ARCHIVELOG

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS  CLIENT_PROCESS  SEQUENCE# STATUS
-------------- -------------------------  ----------------- ---------------------
ARCH         ARCH                        0                      CONNECTED
ARCH         ARCH                        0                      CONNECTED
ARCH         ARCH                        0                      CONNECTED
ARCH         ARCH                        0                      CONNECTED
RFS             LGWR                       69                    IDLE
RFS             ARCH                        0                      IDLE
MRP0         N/A                             69                    WAIT_FOR_LOG
7 rows selected.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME     DATABASE_ROLE OPEN_MODE    LOG_MODE
------------ ------------------------  -------------------- --------------------
ORADB   PRIMARY                MOUNTED        ARCHIVELOG

SQL> alter database open;
Database altered.

SQL> select INST_ID,name,open_mode,database_role,log_mode,controlfile_type from gv$database;
 INST_ID NAME    OPEN_MODE   DATABASE_ROLE  LOG_MODE     CONTROL
------------ ----------- -------------------- ------------------------- -------------------- -----------------

1               ORADB  READ WRITE   PRIMARY                 ARCHIVELOG  CURRENT
   

Note :- A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-enable either database.

Please feel free to ask. thank you 🙂

Toufique Khan

Instance Recovery takes place in RAC

RAC relies on the Cluster Services for failure detection. The Cluster Services are a distributed kernel component that monitors whether Cluster Members can communicate with each other and through this process enforces the rule of Cluster Membership. This is taken care of by Cluster Synchronization Service (CSS) with the CSSD process. The functions performed by CSS can be listed below.

1. Form a cluster, and add/remove members from a cluster.
2. Tracks in which members in a cluster are active.
3. Maintains a Cluster Membership list, which is consistent on all member nodes.
4. Provides timely notification of Membership changes.

When a node polls another Node (Target) in the Cluster, and the target has not responded successfully after repeated attempts, a timeout occurs after approximately 60 seconds. Among the responding nodes, the node that was started first and that is alive declares that the other node is not responding and has failed. This node becomes the new MASTER and starts evicting the non-responding node from the cluster. Once the eviction is complete, cluster reformation begins. The reorganization process regroups accessible nodes and removes the failed ones.

LMON is a background process that monitors the entire cluster to manage the global resource. By constantly probing the other instances, it checks and manages instance death and associated recovery for Global Cache Service (GCS). When a node joins or leaves the cluster, it handles the reconfiguration of locks and associated resources. LMON handles the part of recovery associated with global resources. Failover of service is also triggered by the EVMD process by firing a down event.

Once the reconfiguration of the nodes is complete, Oracle in, coordination with the EVMD and CRSD, performs several tasks.

1. Database/Instance recovery.

2. Failover of VIP system service.

3. Failover of the user/database services to another instance.

Database/Instance Recovery

After a node in the cluster fails, it goes through several steps of recovery to complete changes at both the instance (cache) level and database level:

1. During the first phase of recovery, Global Enqueue Services (GES) remasters the enqueues, and Global Cache Services (GCS) remasters its resources from the failed instance among the surviving instances.

2. The first step in the GCS remastering process is for Oracle to assign a new incarnation number.

3. Oracle determines how many more nodes are remaining in the cluster. (Nodes are identified by a numeric starting with zero and incremented by one for every additional node in the cluster).

4. In An Attempt To Recreate The Resource Master Of The Failed Instance, All GCS Resource Requests And Write Requests Are Temporarily Suspended ( Grd Is Frozen).

5. All the dead shadow processes related to the GCS are cleaned from the failed instance.

6. After enqueues are reconfigured, one of the surviving instances can grab the instance recovery enqueue.

7. At the same time as GCS resources are remastered, SMON determines the set of blocks that need recovery. This set is called the Recovery set. With Cache Fusion an instance ships the contents of its block to the requesting instance without writing that dirty block to the disk ( i.e. the on-disk version of the blocks may not contain the changes that are made by either instance). Because of this behavior, SMON needs to merge the content of all the online redo logs of each failed instance to determine the recovery set and the order of recovery.

8. At this stage, buffer space for recovery is allocated, and the resources that were identified in the previous reading of the redo logs are claimed as recovery resources. this is done to prevent other instances from accessing those resources.

9. A new master node for the cluster is created ( A New Master Node Is Only Assigned If The Failed Node Was The Previous Master Node In The Cluster). All GCS shadow processes are now traversed from a frozen state, and this completes the reconfiguration process.

10. During the remastering of GCS from the failed instance ( during cache recovery), Most Work On The Instance Performing Recovery Is Paused, And While Transaction Recovery Takes Place, Works Occur At A Slower Pace. Subsequently, Oracle starts the database recovery process and begins the cache recovery process (i.e., rolling forward committed transactions). This is made possible by reading the redo log files of the failed instance. Because of the shared storage subsystem, redo log files of all instances participating in the cluster are visible to other instances. This makes any one instance that detected the failure read the redo log files of the failed instance and start the recovery process.

11. After completion of the cache recovery, Oracle starts the transaction recovery operation i.e. roll forward the committed transaction and rollback the uncommitted transactions.

Please feel free to ask. thank you 🙂
Toufique Khan

Step by Step Install Oracle 19c Release 3 on Oracle Linux 7 (OL7)

Oracle Database is a robust and trusted relational database management system that has gained immense popularity among enterprises across the globe. This article offers a detailed and user-friendly guide on installing Oracle Database 19c on Oracle Linux.

Hardware Requirements

Requirements for Installing Oracle Database 19c on OL7 or RHEL7 64-bit (x86-64) (Doc ID 2551169.1)

Invoke ./runInstaller

Start the Oracle Universal Installer (OUI) by issuing the following command.

Note: If you encounter any issues during the installation process after running the Installer, please ensure that all the necessary directories, such as oraInventory, have been created. If they are not, Create them and assign appropriate access permissions.

Please feel free to ask. thank you 🙂

Toufique Khan

Oracle 19c-Convert Physical Standby To Snapshot Standby

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database.To Configure snapshot standby, first, we need to create a physical standby and then we will convert from physical standby to Snapshot standby.

 Developers want to testing on Fresh Live Data, but DBA can't allow them to test on Primary, then how to archive developer requirements.. DBA can convert physical standby to snapshot standby in R/W mode.

 Hence Developers can make their changes in Snapshot databases. Whatever changes are done on snapshot standby will be flushed out once converted back to the physical standby database from snapshot standby. 

Primary database changes will not applied to the snapshot standby database why because there is no MRP process running the snapshot database.

No need to enable flashback database.

Only need to have db_recovery_file_dest and db_recovery_file_dest_size on physical standby.  

1. Check Database Role and Verify Archive log GAP                                          

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE       OPEN_MODE                         LOG_MODE
-------------------- -----------------------------  -----------------------------------  -----------
ITMSPRD          PHYSICAL STANDBY  READ ONLY WITH APPLY  ARCHIVELOG
                                      
SQL>select INST_ID,PROCESS,STATUS,THREAD#,sequence#,BLOCK# from gv$managed_standby;
   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
---------- --------- ------------ ---------- ---------- ----------
         1 ARCH      CLOSING               1     665547     761856
         1 DGRD      ALLOCATED         0          0          0
         1 DGRD      ALLOCATED         0          0          0
         1 ARCH      CLOSING               1     665544     737280
         1 ARCH      CLOSING               1     665548     735232
         1 ARCH      CLOSING               1     665549     780288
         1 ARCH      CLOSING               1     665542     745472
         1 ARCH      CLOSING               1     665546     808960
         1 ARCH      CLOSING               1     665543     747520
         1 RFS       IDLE                          1          0          0
         1 RFS       RECEIVING             1     665897      77825
         1 RFS       RECEIVING             1     665895     346113
         1 RFS       RECEIVING             1     665896     126977
         1 RFS       RECEIVING             1     665892     778241
         1 RFS       RECEIVING             1     665893     473089
         1 RFS       RECEIVING             1     665894     393217
         1 MRP0   WAIT_FOR_LOG     1     665892          0
17 rows selected.

SQL>select distinct(THREAD#),max(sequence#) from v$archived_log group by THREAD# order by 1;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1         665891

SQL>select distinct(THREAD#),max(sequence#) from v$archived_log where APPLIED='YES' group by THREAD# order by 1;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1         665889

SQL> select 'Last Log applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union select 'Last Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);
LOGS                 TIME
-------------------- ------------------
Last Log applied :   11-JAN-24:11:40:54
Last Log received :  11-JAN-24:11:41:21

2. Verify Flash Recovery Area and Flashback database

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> show parameter db_recovery_file_dest ;
NAME                                      TYPE       VALUE
------------------------------------ ------------  -------------------------------------------
db_recovery_file_dest              string        /oracle/ora19c/19cbase/fast_recovery_area/
db_recovery_file_dest_size     big integer 15G

SQL> archive log list
Database log mode                  Archive Mode
Automatic archival                  Enabled
Archive destination                 /fodb_arch1/ITMSPRD/arch
Oldest online log sequence     0
Next log sequence to archive  0
Current log sequence              0

SQL> Show parameter db_flashback_retention_target
NAME                                       TYPE      VALUE
------------------------------------   ----------- ------------------------------
db_flashback_retention_target   integer    1440


SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE       OPEN_MODE                         LOG_MODE
-------------------- -----------------------------  -----------------------------------  -----------
ITMSPRD          PHYSICAL STANDBY  READ ONLY WITH APPLY  ARCHIVELOG
                                                                                                                          

3. Cancel MRP on Standby

SQL> alter database recover managed standby database cancel;

Database altered.

4. Covert to Snapshot Standby from Physical Standby

SQL> alter database convert to snapshot standby;
Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE          OPEN_MODE         LOG_MODE
-------------------- -----------------------------    -----------------------   ---------------------
ITMSPRD           SNAPSHOT STANDBY  MOUNTED              ARCHIVELOG
                                                                                                                         
SQL> alter database open;
Database altered.

5. Testing

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE          OPEN_MODE         LOG_MODE
-------------------- -----------------------------    -----------------------   ---------------------
ITMSPRD           SNAPSHOT STANDBY  READ WRITE         ARCHIVELOG                                     

6. Covert Back to Physical Standby from Snapshot Standby                                                                                                  

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE          OPEN_MODE         LOG_MODE
-------------------- -----------------------------    -----------------------   ---------------------
ITMSPRD           SNAPSHOT STANDBY  READ WRITE         ARCHIVELOG                 

SQL> shut immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 6.8719E+10 bytes

Fixed Size                37257296 bytes

Variable Size            1.4764E+10 bytes

Database Buffers      5.3687E+10 bytes

Redo Buffers            231174144 bytes

Database mounted.

SQL> 

SQL> select name,database_role,open_mode,log_mode from v$database;

NAME                DATABASE_ROLE          OPEN_MODE         LOG_MODE
-------------------- -----------------------------    -----------------------   ---------------------
ITMSPRD           SNAPSHOT STANDBY  MOUNTED              ARCHIVELOG                                    

                                                                                                                          

SQL> alter database convert to physical standby;

Database altered.

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE       OPEN_MODE         LOG_MODE
-------------------- -----------------------------  -----------------------  --------------------
ITMSPRD          PHYSICAL STANDBY  MOUNTED             ARCHIVELOG

                                                                                                                          

SQL> shut immediate 

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>  startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 6.8719E+10 bytes

Fixed Size                37257296 bytes

Variable Size            1.4764E+10 bytes

Database Buffers      5.3687E+10 bytes

Redo Buffers            231174144 bytes

Database mounted.

Database opened.

SQL> 

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE       OPEN_MODE         LOG_MODE
-------------------- -----------------------------  -----------------------  --------------------
ITMSPRD          PHYSICAL STANDBY  READ ONLY           ARCHIVELOG

                                                                                   

7. Start MRP on Standby

SQL> alter database recover managed standby database disconnect from session;

Database altered.

                                                                                                    

SQL> select name,database_role,open_mode,log_mode from v$database;
NAME                DATABASE_ROLE       OPEN_MODE                         LOG_MODE
-------------------- -----------------------------  -----------------------------------  -----------
ITMSPRD          PHYSICAL STANDBY  READ ONLY WITH APPLY  ARCHIVELOG

What is snapshot Standby Database? What are benefits?

Converting a physical standby database to a snapshot standby database in Oracle 19c involves several steps. A snapshot standby database allows read/write access to the standby database while remaining able to fall back to the physical standby database. A snapshot standby database provides many advantages over a physical standby database in an Oracle environment. A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

Here are the main benefits: 

1. Read-Write Capability

A snapshot standby database can be opened in read-write mode, allowing users to perform updates and test applications using production-like data without affecting the primary database. This is particularly useful for development and testing scenarios where real-time data is required for validation.

2. Temporary Testing Environment

Organizations can utilize snapshot standby databases to create a temporary testing environment. This allows teams to experiment with changes or new applications on a copy of the production data, ensuring that any potential issues can be identified before deployment in the live environment. After testing, the snapshot can be reverted back to its original state. 

3. Data Protection

While a snapshot standby is open for updates, it continues to receive redo data from the primary database. This ensures that the integrity of the primary database is maintained, as the snapshot standby can be converted back to a physical standby at any time, applying any received redo data and discarding local changes made during the testing phase.

4. Guaranteed Restore Point

Whenever a physical standby database is converted into a snapshot standby database, a guaranteed restore point is automatically created. Once the updateable transactions are completed for testing purposes on the snapshot standby database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means the transactions that were made in the standby database while it was open in READ WRITE mode will be flushed out.

5. Flexibility in Database Management

The ability to switch back and forth between snapshot and physical standby modes provides flexibility in database management. Organizations can conduct necessary testing and development while still maintaining a robust disaster recovery setup, as the snapshot can be reverted to a physical standby without loss of data protection capabilities.

6. Reduced Administrative Overhead

Using a snapshot standby can reduce the need for cloning production databases for testing purposes, simplifying administrative tasks. Since the snapshot is derived from an existing physical standby, it minimizes the overhead associated with managing multiple copies of the database.

Snapshot standby databases provide a versatile solution for testing and development while ensuring data protection and recovery capabilities, making them a valuable tool in Oracle database management. This process allows for testing and development on a standby database without affecting the primary database, while still maintaining the ability to revert changes easily

Please feel free to ask. thank you 🙂

Toufique Khan

The cluvfy (Cluster Verification Utility)

The cluvfy (Cluster Verification Utility) is a tool provided by Oracle to verify the state of various components in an Oracle RAC (Real Application Clusters) environment before and after installation, patching, or other system changes. Here are the key points about the cluvfy utility:

The cluvfy utility performs system checks to ensure the required configuration and preinstallation steps have been completed successfully, so that the Oracle update or patch operation can complete without issues.

Some of the key checks performed by cluvfy include verifying node reachability, node connectivity, shared storage accessibility, free space in the CRS home, CRS integrity, and more.

To use cluvfy, you need to run the commands as the "grid" user. If the Grid Infrastructure is not yet installed, you can run cluvfy from the software files directory (e.g. /u01/orasw/grid). Otherwise, run it from the $ORACLE_HOME.

The cluvfy utility is included with the Grid Infrastructure installation, but you can also download the latest standalone version from My Oracle Support.

The standalone version is backward compatible and can be used with older Oracle RAC versions. Running cluvfy before and after patching Grid Infrastructure is recommended to ensure the patch can be applied successfully and the cluster is in a healthy state after the patch.

In summary, the cluvfy utility is an essential tool for verifying the readiness and health of an Oracle RAC environment and should be used regularly during the installation, configuration, and maintenance of the cluster.

Using the Cluster Verification Utility(CVU)

The CVU can verify the primary cluster components during an operational phase or stage. A component can be basic, such as free disk space, or it can be complex, such as checking Oracle Clusterware integrity. For example, CVU can verify multiple Oracle Clusterware subcomponents across Oracle Clusterware layers. Additionally, CVU can check disk space, memory, processes, and other important cluster components. A stage could be, for example, database installation, for which CVU can verify whether your system meets the criteria for an Oracle RAC installation. Other stages include the initial hardware setup and the establishment of system requirements through the fully operational cluster setup.

When verifying stages, CVU uses entry and exit criteria. In other words, each stage has entry criteria that define a specific set of verification tasks to be performed before initiating that stage. 

This check prevents you from beginning a stage, such as installing Oracle Clusterware, unless you meet the Oracle Clusterware stage's prerequisites.

The exit criteria for a stage define another set of verification tasks that you need to perform after the completion of the stage. Post-checks ensure that the activities for that stage have been completed. 

Post-checks identify stage-specific problems before they propagate to subsequent stages.

The node list that you use with CVU commands should be a comma-delimited list of host names without a domain. The CVU ignores domains while processing node lists. If a CVU command entry has duplicate node entries after removing domain information, then CVU eliminates the duplicate node entries. Wherever supported, you can use the -n all option to verify all of your cluster nodes that are part of a specific Oracle RAC installation. You do not have to be the root user to use the CVU and the CVU assumes that the current user is the Oracle user.

Cluster Verification Utility Requirements

The CVU requirements are:

• At least 30MB of free space for the CVU software on the node from which you run the CVU
• A location for the current JDK, Java 1.4.1 or later
• A working directory with at least 25MB of free space on each node

Verify the Post-installation Configuration of a Cluster

[oracle@collabn1 ~]$ cluvfy stage -post crsinst -n collabn1,collabn2

Performing post-checks for cluster services setup

Checking node reachability...

Node reachability check passed from node "collabn1"

Checking user equivalence...

User equivalence check passed for user "oracle"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.78.0"

Node connectivity passed for subnet "192.168.78.0" with node(s) collabn2,collabn1

TCP connectivity check passed for subnet "192.168.78.0"

Check: Node connectivity using interfaces on subnet "172.16.100.0"

Node connectivity passed for subnet "172.16.100.0" with node(s) collabn1,collabn2

TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...

Subnet mask consistency check passed for subnet "192.168.78.0".

Subnet mask consistency check passed for subnet "172.16.100.0".

Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...

Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.

Checking whether the ASM filter driver is active and consistent on all nodes

ASM filter driver library is not installed on any of the cluster nodes.

ASM filter driver configuration was found consistent across all the cluster nodes.

Time zone consistency check passed

Checking Cluster manager integrity...

Checking CSS daemon...

Oracle Cluster Synchronization Services appear to be online.

Cluster manager integrity check passed

UDev attributes check for OCR locations started...

UDev attributes check passed for OCR locations

UDev attributes check for Voting Disk locations started...

UDev attributes check passed for Voting Disk locations

Default user file creation mask check passed

Checking cluster integrity...

Cluster integrity check passed

Checking OCR integrity...

Checking the absence of a non-clustered configuration...

All nodes free of non-clustered, local-only configurations

Checking daemon liveness...

Liveness check passed for "CRS daemon"

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful

Disk group for ocr location "+OCRVOTE/collabn-cluster/OCRFILE/registry.255.934671619" is available on all the nodes

Checking OCR backup location "/u01/app/12.1.0/grid/cdata/collabn-cluster"

OCR backup location "/u01/app/12.1.0/grid/cdata/collabn-cluster" check passed

NOTE:

This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Checking CRS integrity...

Clusterware version consistency passed.

CRS integrity check passed

Checking node application existence...

Checking existence of VIP node application (required)

VIP node application check passed

Checking existence of NETWORK node application (required)

NETWORK node application check passed

Checking existence of ONS node application (optional)

ONS node application check passed

Checking Single Client Access Name (SCAN)...

Checking TCP connectivity to SCAN listeners...

TCP connectivity to SCAN listeners exists on all cluster nodes

Checking name resolution setup for "collabn-cluster-scan.racattack"...

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...

All nodes have same "hosts" entry defined in file "/etc/nsswitch.conf"

Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed

Checking SCAN IP addresses...

Check of SCAN IP addresses passed

Verification of SCAN VIP and listener setup passed

Checking OLR integrity...

Check of existence of OLR configuration file "/etc/oracle/olr.loc" passed

Check of attributes of OLR configuration file "/etc/oracle/olr.loc" passed

WARNING:

This check does not verify the integrity of the OLR contents. Execute 'ocrcheck -local' as a privileged user to verify the contents of OLR.

OLR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed

User "oracle" is not part of "root" group. Check passed

Oracle Clusterware is installed on all nodes.

CTSS resource check passed

Query of CTSS for time offset passed

CTSS is in Active state. Proceeding with check of clock time offsets on all nodes...

Check of clock time offsets passed

Oracle Cluster Time Synchronization Services check passed

Checking VIP configuration.

Checking VIP Subnet configuration.

Check for VIP Subnet configuration passed.

Checking VIP reachability

Check for VIP reachability passed.

    Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/cwadd/cluster-verification-utility-reference.html#GUID-0CBB99C2-6F24-46E5-8A9B-A1A0C581E86A

Please feel free to ask. thank you 🙂

Toufique Khan

Split Brain Syndrome in Oracle RAC

In a cluster, a private interconnect is used by cluster nodes to monitor each node’s status and communicate with each other. When two or more nodes fail to ping or connect to each other via this private interconnect, the cluster gets partitioned into two or more smaller sub-clusters each of which cannot talk to others over the interconnect. Oblivious of the existence of other cluster fragments, each sub-cluster continues to operate independently of the others. This is called “Split Brain”. In such a scenario, the integrity of the cluster and its data might be compromised due to uncoordinated writes to shared data by independently operating nodes. Hence, the split-brain must be resolved to protect the integrity of the cluster and its data.

Explanation-1:

It will arise when two or more instances attempt to control a cluster database. In a two-node environment, one instance attempts to manage updates simultaneously while another attempts to manage updates. In the Oracle RAC environment, all the instances/servers communicate with each other using high-speed interconnects on the private network. This private network interface or interconnect is redundant and is only used for inter-instance Oracle data block transfers. Now talking about the split-brain concept concerning Oracle RAC systems, it occurs when the instance members in an RAC fail to ping/connect to each other via this private interconnect, but the servers are all physically up and running and the database instance on each of these servers is also running. These individual nodes are running fine and can conceptually accept user connections and work independently. Due to lack of communication, the instance thinks that the other instance that it is not able to connect is down and it needs to do something about the situation. The problem is if we leave these instances running, the sane block might get read, and updated in these individual instances and there would be a data integrity issue, as the blocks changed in one instance, will not be locked and could be over-written by another instance. Oracle has efficiently implemented checks for the split-brain syndrome. When there is no network heart bit then Split brain is done using Disk (vote disk) information.

Explanation-2:

Oracle has efficiently implemented checks for the split-brain syndrome. In RAC if any node becomes inactive, or if other nodes are unable to ping/connect to a node in the RAC, then the node that first detects that one of the nodes is not accessible, will evict that node from the RAC group. e.g. there are 4 nodes in a RAC instance, and node 3 becomes unavailable, and node 1 tries to connect to node 3 and finds it not responding, then node 1 will evict node 3 out of the RAC groups and will leave only Node1, Node2 & Node4 in the RAC group to continue functioning. The split brain concepts can become more complicated in large RAC setups. For example, there are 10 RAC nodes in a cluster. And say 4 nodes are not able to communicate with the other 6. So there are 2 groups formed in this 10-node RAC cluster ( one group of 4 nodes and the other of 6 nodes). Now the nodes will quickly try to affirm their membership by locking the control file, and then the node that locks the control file will try to check the votes of the other nodes. The group with the most number of active nodes gets the preference and the others are evicted. Moreover, I have seen this node eviction issue with only 1 node getting evicted and the rest function fine, so I cannot really testify that that shows it works by experience, but this is the theory behind it. When we see that the node is evicted, usually Oracle rac will reboot that node and try to do a cluster reconfiguration to include back the evicted node. You will see Oracle error: ORA-29740 when there is a node eviction in RAC. There are many reasons for a node eviction like heartbeat not being received by the control, unable to communicate with the Clusterware, etc.

How does the Oracle Grid Infrastructure Clusterware resolve a 'Split Brain' situation?

Voting disk is used by Oracle Cluster Synchronization Services Daemon (ocssd) on each node, to mark its own attendance and also to record the nodes it can communicate with. In a split-brain situation, 

a voting disk is used to determine which node(s) will survive and which node(s) will be evicted.

Which Process evicts nodes from the RAC Cluster?

• CSSD Monitors and evicts nodes.
• Monitors nodes using 2 communication Channels :
• Private Interconnect <=> Network Heartbeat
• Voting Disk Based communication <=> Disk Heartbeat
• Evicts (forcibly removes nodes from a cluster) nodes dependent on heartbeat feedback(failures).

Now, who will decide which node will survive and which node will face fencing ??

The answer is Voting Disk.

In a split-brain situation, a voting disk will be used to determine which node(s) survive and which node(s) will be evicted. 

Please feel free to ask. thank you 🙂

Toufique Khan