Restore/Recover datafile In Standby From Service In 12c/19c

This tutorial explains the steps to restore and recover a datafile lost in an Oracle standby database. We'll walk through the process step by step using a practical example.

Environment Details

• Production Database (db_unique_name): DHCP
• Standby Database (db_unique_name): DHCPDR

Step 1: Start the Standby Database in MOUNT State

$ sqlplus / as sysdba
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 28 15:40:58 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073737776 bytes
Fixed Size 8933424 bytes
Variable Size 926941184 bytes
Database Buffers 130023424 bytes
Redo Buffers 7839744 bytes
Database mounted.

Step 2: Verify the Standby Database Status

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

Step 3: Connect to RMAN and Restore the Datafile

ora19c@DHCPDR:~$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 28 15:48:58 2025
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DHCP (DBID=1881015276, not open) RMAN> restore datafile 53 from service DHCP;
Starting restore at 28-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=392 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=2 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=237 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=276 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service DHCP
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 0053 to /sapdata2/DHCP/datafiles/sr_datafile_53.dbf
channel ORA_DISK_1: restore complete, elapsed time: 01:44:06
Finished restore at 28-JAN-25
Recovery Manager complete.

Important Notes:

• Ensure the standby database is in MOUNT state before starting the restore
• Verify the database role and status before proceeding
• Make sure the service name is correctly specified in the RMAN restore command
• Monitor the restore progress and verify completion

The restore process successfully completed, and the datafile has been restored to its original location. The elapsed time for this restore was approximately 1 minutes and 44 seconds.

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

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 and recovery operations.
• Permits only one restore operation per datafile at a time.
• Ensures the correct order of incremental backups during recovery.
• Stores corruption detection information in the database.
• Performs continuous block validation to detect corruption early.

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

RMAN> 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
---- ------ -------------- ------------ --------------- ----------
4    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.