Validating Database Files and Backups In RMAN

The VALIDATE option of the BACKUP command in RMAN allows you to verify that database files exist, are in the correct locations, and are free from physical or logical corruption that would prevent RMAN from backing them up.

Key Points:

• RMAN reads files completely but doesn't create backup sets
• Corrupt blocks are recorded in V$DATABASE_BLOCK_CORRUPTION
• Block media recovery can fix corrupted blocks
• Validation entries are removed after repair

Basic Concepts of RMAN Validation

Database Automatic Protection Features:
• Blocks access to datafiles during restore/recovery
• Permits only one restore operation per datafile
• Ensures correct order of incremental backups
• Stores corruption detection information
• Performs continuous block validation

Example 1: Validating Entire Database

RMAN> VALIDATE DATABASE;
Starting validate at 23-DEC-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/app/oradata/TEST/system01.dbf
input datafile file number=00002 name=/u01/app/oradata/TEST/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oradata/TEST/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oradata/TEST/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:45

Validation Results

List of Datafiles
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 15399 93442 4474162
File Name: /u01/app/oradata/TEST/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 62215
Index 0 13060
Other 0 2766

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 22615 87042 4484038
File Name: /u01/app/oradata/TEST/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 19664
Index 0 16520
Other 0 28241

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 33 19200 4474161
File Name: /u01/app/oradata/TEST/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 19167

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
OK 0 1292 3846 4688662
File Name: /u01/app/oradata/TEST/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1228
Index 0 383
Other 0 937

Example 2: Validating Specific Datafile

RMAN> VALIDATE DATAFILE 530;
Starting validate at 25-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=533 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=567 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=263 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00530 name=/oracle/ACP/sapdata20/oracle/datafiles/sr3.data518
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
530 OK 0 3719021 3932160 42465412921
File Name: /oracle/ACP/sapdata20/oracle/datafiles/sr3.data518
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 156570
Index 0 54430
Other 0 2139
Finished validate at 25-JAN-25

Important Notes:

• Regular validation helps prevent backup failures
• Monitor validation results for corruption indicators
• Keep track of validation history
• Address any corruptions promptly

For more detailed information, refer to: How to Check/Validate that RMAN Backups Are Good (Doc ID 466221.1)

Please feel free to ask questions in the comments below. 🙂
Toufique Khan

Gather Stats in Oracle

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather stats periodically using DBMS_STATS package.

DBMS_STATS

The DBMS_STATS package was introduced in Oracle 8i and is Oracle's preferred method of gathering statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. The functionality of the DBMS_STATS package varies greatly between database versions, as do the default parameter settings and the quality of the statistics they generate. It is worth spending some time checking the documentation relevant to your version.

You must gather statistics on a regular basis to provide the optimizer with information about schema objects. New statistics should be gathered after a schema object's data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, you should collect new statistics on the number of rows. After updating data in a table, you do not need to collect new statistics on the number of rows but you might need new statistics on the average row length.

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table
SQL> EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');
SQL> EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);
Cascade will gather Index stats associated with the table.

DBMS_STATS.GATHER_INDEX_STATS is used to gather index stats.
SQL> EXEC DBMS_STATS.gather_index_stats('HR','EMPLOYEES_PK');

DBMS_STATS.GATHER_SCHEMA_STATS package is used to gather entire schema stats.
SQL> EXEC DBMS_STATS.gather_schema_stats('SCOTT');

Gather Other Database Objects Statistics

DBMS_STATS.GATHER_DATABASE_STATS package is used to gather entire database stats.
SQL> EXEC DBMS_STATS.gather_database_stats;

DBMS_STATS.GATHER_DICTIONARY_STATS package will gather dictionary statistics.
SQL> EXEC DBMS_STATS.gather_dictionary_stats;

Gather System and Fixed Object Stats:

There is no specific need to gather system (hardware, CPU, memory) and Fixed objects (X$ views) stats on a regular basis. You should only gather system & fixed objects stats when there is a major change on server hardware or major update to the database level.

SQL> EXEC DBMS_STATS.gather_system_stats;
SQL> EXEC DBMS_STATS.gather_fixed_objects_stats;

Check Stale Statistics: DBA_TAB_STATISTICS allows you to check stale statistics on a specific table:
SELECT owner, table_name, to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS'), stale_stats FROM dba_tab_statistics WHERE table_name='EMPLOYEES' and owner='HR';

DBA_IND_STATISTICS allows you to check stale statistics on specific index:
SELECT owner, table_name, index_name last_analyzed, stale_stats FROM dba_ind_statistics WHERE table_name='EMPLOYEES' and owner = 'HR';

Note: Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for more information on this package.

However, you must use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer, such as:

• To use the VALIDATE or LIST CHAINED ROWS clauses
• To collect information on freelist blocks

Please feel free to ask. thank you 🙂

Toufique Khan

Role of the Server Process In Oracle Database

The server process accesses the database on behalf of user processes to read data from datafiles on disk into the buffer cache in the SGA. When SQL statements need to access certain data, Oracle places data into the buffer cache in the following way. If there is an index associated with the table that can be used by Oracle, the blocks for that index will be read into the buffer cache. Oracle then tracks down the appropriate ROWID corresponding to the data needed. As stated earlier, a ROWID is an exact location on a disk for a row of data. There are three components of a ROWID: the data block address, the block row number, and the block datafile number. Commit this information with the following acronym: BRF, for block-row-file. Once the ROWID for a block containing table data is identified, that block is read into memory. If many rows are to be read into memory, then all blocks containing those rows are placed into data block buffers of the database buffer cache. Figure 1 demonstrates the format for ROWID data in Oracle.

Figure 1: ROWID format in Oracle

However, Oracle is not always able to bring data into memory in the most efficient way, as provided by some indexing mechanisms in Oracle. When there is no index on the table, or if the SQL statement does not make use of the index, Oracle has no choice but to read every data block associated with a table into memory to find the block containing the data required for the user request. This method for obtaining data is called a full table scan. In this case, every data block in a table will be read into memory until the row containing the requested data is found.

Once in the buffer cache, that block will be held in the database buffer cache for a certain period of time. That time period is determined by the server process, the mechanism by which Oracle brought the data block into memory. If the data was brought into memory as part of an indexed search, the data block will be held in memory until the space is needed by another block, and the block in the buffer is the least recently used (LRU) block. In this way, frequently used blocks will stay in the buffer cache for a long time, while infrequently used buffers get eliminated to make way for more block storage. This method is often referred to as the LRU algorithm.

   Figure 2: Server Process in Oracle

     

The method used to keep a data block in the buffer cache is slightly different if Oracle must use a full table scan to find the data the user requests. Oracle simply eliminates those buffers almost immediately after they are read by placing them on the least recently used end of the list of blocks slated for elimination from the buffer cache. Oracle doesn’t simply use the LRU mechanism to hold data blocks brought in from full table scans because doing so would quickly fill the buffer cache with blocks from one table, eliminating blocks from other tables that may be in use by other queries. Without a modified LRU algorithm, full table scans will adversely impact performance not only for the query running the full table scan but for the entire database as well.

What often happens in the buffer cache is blocks from indexes on tables commonly accessed stay in the buffer cache longest. Oracle’s modified LRU algorithm facilitates this usage in the following ways. Index blocks are stored in the buffer cache, and frequent use will keep them in the buffer cache. Since an index block for a commonly used table is likely to be used frequently by users, the blocks of the index will likely live in the buffer cache for long periods of time. Storing the blocks of an index in memory is more effective than storing the actual rows of data in memory for two reasons. The first reason is that the index has much fewer blocks than the table, minimizing the number of blocks that need to be stored in memory in order to have easy access to a table. The second reason is that each index block stores searchable column values for table rows, plus an address on disk that will allow Oracle to know almost exactly where on disk to look for the full row data. The buffer cache then acts as a Super Index of the data in the Oracle database.

Please feel free to ask. thank you 🙂

Toufique Khan

Understanding Oracle RAC Cluster Interconnects: The Network Behind High Availability

Introduction

Deep technical knowledge of critical infrastructure components like Oracle RAC interconnects can significantly boost your career trajectory. Today, I want to share insights about cluster interconnects.

Understanding interconnect technology helps you troubleshoot complex issues in enterprise environments.

Career Growth Insight: Professionals who can articulate both technical details and business benefits of proper interconnect configuration are often viewed as strategic assets rather than just technical resources.

What is a Cluster?

A cluster is a group of independent but interconnected computers that act as a single system. This architecture provides several benefits that modern enterprises require:

• High Availability (HA): If one node fails, services continue running on other nodes
• Load Balancing: Workloads can be distributed across multiple nodes
• Scalability: Additional nodes can be added to increase processing power

Career Development Action: Create a test environment to practice cluster configurations. Document your approach and results to build a portfolio of hands-on experience that differentiates you in job interviews.

The Role of Clusterware Software

Clusterware software (renamed Grid Infrastructure from Oracle 11g onwards) performs several critical functions:

• Managing multiple nodes as a single cluster
• Maintaining data modifications and integrity across cluster nodes
• Managing node memberships and resource dependencies

The Grid Infrastructure software includes:

• Clusterware
• Automatic Storage Management (ASM)
• ASM Cluster File System (ACFS)

Career Insight: Professionals who understand the evolution of Oracle's clustering technology (such as ASM moving from the database software in 10g to Grid Infrastructure in 11g) demonstrate historical knowledge that senior leadership values when making architectural decisions.

Network Architecture for Clustering

Understanding the network foundation of clusters is essential for designing reliable systems:

Network Adapter Types

• Public Network: Client connectivity and application access
• Private Network: Cluster interconnect for node communication
• Node Virtual IP: Enables failover capabilities
• SCAN VIP: Single Client Access Name for simplified client connectivity

Career Advancement Action: Develop a one-page reference guide explaining network requirements for Oracle RAC. Share it with your team to establish yourself as a go-to resource for infrastructure planning.

The Critical Role of Interconnects

The private network (interconnect) serves as the communication backbone between nodes within a cluster. This network carries:

1. Cluster heartbeat messages
2. Cache fusion data block transfers

For optimal performance, this network should provide:

• Minimum 1 Gbps bandwidth for enterprise environments
• 100 Mbps minimum for lab/testing environments
• Many modern implementations now utilize 10 Gbps connections

The interconnect must support UDP/IP protocol, which is often called a "no acknowledgment protocol" since nodes communicate within a trusted network environment.

Technical Skill Enhancement: Learn to use network monitoring tools like iptraf, netstat, and Oracle's oifcfg to analyze interconnect traffic patterns. This diagnostic capability is highly valued during critical outages.

Ensuring Interconnect Reliability

The Risk of Single Points of Failure

If a Network Interface Card (NIC) fails, affected nodes cannot communicate with other cluster members. When a node cannot communicate, the cluster will evict that node to maintain overall system integrity.

Leadership Opportunity: Proactively identifying and addressing single points of failure demonstrates strategic thinking that distinguishes technical leaders from technical contributors.

NIC Bonding: A Redundancy Solution

Oracle recommends implementing OS-level NIC bonding (also called teaming or trunking) to eliminate this single point of failure. This combines two or more NICs to form a logical bond, providing:

• Failover capability if one NIC fails
• Potential throughput aggregation
• Simplified management through a single logical interface

# Example of checking bonded interface configuration
$ cat /proc/net/bonding/bond0

Ethernet Channel Bonding Driver: v3.7.1
Bonding Mode: fault-tolerance (active-backup)
Primary Slave: eth0
Currently Active Slave: eth0
MII Status: up
MII Polling Interval (ms): 100
Up Delay (ms): 0
Down Delay (ms): 0

Slave Interface: eth0
MII Status: up
Speed: 1000 Mbps
Duplex: full
Link Failure Count: 0
Permanent HW addr: 00:0c:29:fc:2d:6e

Slave Interface: eth1
MII Status: up
Speed: 1000 Mbps
Duplex: full
Link Failure Count: 0
Permanent HW addr: 00:0c:29:fc:2d:78
  

Common Interconnect Issues and Solutions

Developing expertise in troubleshooting these common issues can significantly enhance your professional reputation:

Issue	Symptoms	Solution
Insufficient Bandwidth	Slow query performance during high cache fusion activity	Upgrade to higher bandwidth NICs
NIC Failures	Node evictions, cluster instability	Implement NIC bonding
MTU Mismatches	Packet fragmentation, degraded performance	Standardize MTU settings across all components

Performance Optimization Through MTU Tuning

Understanding Maximum Transfer Unit (MTU) settings represents an advanced optimization opportunity:

• Default packet size is typically 1500 bytes
• A standard 8KB Oracle block requires approximately 6 network packets
• Each packet includes overhead (80-100 bytes) for addressing information

By implementing jumbo frames (typically 9000 bytes), you can significantly improve performance:

# Checking current MTU setting
$ ifconfig bond0
bond0: flags=5187<UP,BROADCAST,RUNNING,MASTER,MULTICAST> mtu 1500
        inet 192.168.1.10  netmask 255.255.255.0  broadcast 192.168.1.255

# Setting jumbo frames (requires compatible network hardware)
$ ifconfig bond0 mtu 9000
  

Advanced Skill Development: Create a testing methodology to measure the impact of MTU changes on cache fusion performance. Document your findings with concrete metrics to demonstrate your analytical capabilities.

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

Overview

Oracle Database Switchover is a planned role transition between primary and standby databases with zero data loss. This guide provides a step-by-step procedure for performing a manual switchover in Oracle 12c.

What You'll Learn:

• ✓ How to verify database readiness for switchover
• ✓ Converting primary to standby database
• ✓ Converting standby to primary database
• ✓ Validating successful role transition

Prerequisites:

• Functioning Oracle Data Guard configuration
• Oracle 12c Database
• SYSDBA privileges
• Proper network connectivity between hosts

⏱️ Estimated Time: 20-30 minutes

Oracle 12c Step-by-Step Manual Switchover Guide

A database switchover is a planned operation where you exchange the roles of a primary database and its standby database. This is typically performed during maintenance windows to minimize downtime. Below is a detailed walkthrough of a successful switchover operation.

What is a Switchover?

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.

Step 1: Verify the Current Configuration

First, check the current database role and status on the primary:

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

Step 2: Check Archive Log Status

SQL> col destination for a70
SQL> select b.destination,a.dest_id,b.ERROR,max(a.FIRST_TIME),max(a.sequence#) from gvarchived_log a, gvarchive_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

Step 3: Convert Primary to Standby

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

Step 4: Monitor the Switchover Process

The alert log will show the progress of the switchover:

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

Step 5: Start the New Standby Database

[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.

Step 6: Verify the New Standby Status

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

Step 7: Start Managed Recovery

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

Step 8: Convert the Standby to Primary

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.

Step 9: Open the New Primary Database

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.

Step 10: Verify the Final Configuration

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: The switchover is now complete! The original standby database is now the primary, and the original primary is now the standby.

Key Takeaways

• Always verify database roles and synchronization before initiating a switchover
• Monitor the alert logs during the process to track progress
• Remember to start managed recovery on the new standby after switchover
• Perform a final verification to ensure both databases are in their intended roles
• Document the entire process for future reference and knowledge sharing

Have you performed database switchovers in your environment? What challenges did you face? Share your experiences in the comments below!

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