Implement High Availability and Disaster Recovery Solutions on Oracle Database 19c

Table of Contents

1. Understanding High Availability and Disaster Recovery

• Training objectives
• Basic concepts: HA vs DR
• Threats to high availability
• Disaster Recovery
• Case study: Carved Rock Fitness
• The role of redundancy
• Recovery metrics: RTO and RPO
• Oracle MAA Reference Architectures
• Bronze Level
• Silver Level
• Gold Level
• Platinum Level

2. Understand, Configure and Manage Data Guard

• Why Data Guard for Carved Rock Fitness
• Data Guard Fundamentals
• Role transitions: Switchover and Failover
• Protection modes
• Planned switchover scenarios
• Data Guard management: choice of tools
• Demo: Querying with the Data Guard Broker
• Demo: Role transition (Switchover)
• Data Guard Configuration
• Demo: Configuring a Data Guard environment
• Data Guard Troubleshooting
• Data Guard configuration settings
• Data Guard Monitoring

3. Understanding and Exploiting RAC for High Availability

• What is Oracle RAC?
• Vertical vs horizontal scalability
• RAC Architecture
• RAC Basic Commands
• Demo: RAC Query Commands
• Database vs Instance in RAC
• RAC instance management commands
• Demo: RAC instance management
• RAC Load Balancing
• Services and SCAN Listener
• Demo: Services, SCAN Listener and connectivity
• RAC Connectivity with Easy Connect and TNS
• Benefits and Considerations of RAC

4. Understanding and Exploiting ASM for High Availability

• What is Oracle ASM?
• ASM Architecture
• ASM-simplified scenarios
• ASM Key Terminology
• How ASM runs
• ASM Query Views and Settings
• ASMCMD Utility
• Demo: Querying an ASM environment

5. Understanding and Exploiting RMAN for Disaster Recovery

• Motivation: Why save?
• Carved Rock Fitness Backup Posture
• Introduction to RMAN
• Ideal backup storage
• Essential RMAN Commands
• The Control File: critical role
• Control File Protection and Backup
• RMAN and AUTOBACKUP CONTROLFILE configuration
• Substitution variable %F in RMAN
• Backup validation
• Recommended RMAN Settings
• Demo: Full Disaster Recovery

1. Understanding High Availability and Disaster Recovery

Training objectives

This training is designed to equip Database Administrators (DBAs) with the skills and understanding to:

• Evaluate, recommend and lead the implementation of high availability and disaster recovery solutions for Oracle Database.
• Minimize downtime in their organization by identifying single points of failure.
• Understand and recommend appropriate Oracle solutions.
• Practically implement various Oracle solutions, including Oracle Data Guard and Oracle RMAN backups.
• Deepen your understanding of Oracle Real Application Clusters (RAC) and the benefits of ASM.

Basic Concepts: HA vs DR

High Availability (HA) and Disaster Recovery (DR) are two separate ideas, but they collectively communicate a common goal: services are almost always available, no matter what.

• If a component fails, or a natural disaster occurs, the impact on the ability to continue providing services is minimized.

Analogy: Most automobiles employ the same HA/DR concepts. The spare wheel or spare fuses in the fuse box illustrate redundancy: in the event of a puncture or blown fuse, a brief downtime occurs, but the impact is minimized thanks to spare parts. The goal is to minimize downtime.

High Availability

If a service, such as a database, is highly available, this means that it is rarely unavailable. High availability means that anything that depends on the database can generally be expected to function properly despite difficult circumstances.

Threats to high availability

Several categories of threats challenge the ability to keep the database operational:

Disaster Recovery

*Disaster Recovery refers to the ability and process of recovery after a disaster. Disasters refer to catastrophic events that take out an entire data center or system:

• Fire
• Storage failure
• Flood
• Regional power outages
• Other similar events

Fundamental questions every DBA should ask:

• Would I be able to recover in the event of a disaster for the systems I currently support?
• Are there single points of failure in the infrastructure?
• Do I have a plan to restore database services?
• Where is this plan stored?
• Have I tested the steps in this plan?

Case Study: Carved Rock Fitness

Carved Rock Fitness, a leader in high-end fitness equipment, recently suffered significant financial losses due to an Oracle Database server component failure. This failure took their entire Oracle Database offline. Order processing has been interrupted for 3 full days.

Management has tasked the IT team with identifying single points of failure in the company’s database infrastructure and developing plans to mitigate the risk of another outage. This responsibility has been specifically assigned to the senior DBA.

The role of redundancy

How ​​to ensure high availability in difficult circumstances?

Redundancy is one of the main ways to ensure high availability. In the context of HA, redundancy refers to the duplication of functions employed to ensure that services remain available, even with the loss of a critical component.

Example: A database server can have three redundant power supplies. The failure of a power supply does not result in a loss of service. It is said that the server can suffer the loss of two power supplies and still operate.

Challenges associated with redundancy

Every organization must weigh the cost associated with providing high availability and disaster recovery, against the cost of downtime itself.

Recovery Metrics: RTO and RPO

Before discussing the specifics of Oracle Database, two important concepts must be introduced:

Cost/risk relationship:

• The smaller the RTO and RPO, the higher the cost.
• Longer recovery times mean the business is offline longer.
• Greater data loss requires more rework.
• Both result in greater financial losses.

There is therefore a balance between risk and cost.

Oracle MAA Reference Architectures

Oracle has created a set of best practices for configuring Oracle Database called Oracle Maximum Availability Architecture (MAA). These are configurations that provide recommendations around high availability and disaster recovery.

There are 4 levels in Oracle MAA:

Important: These tiers are Oracle reference architectures, but are not the only configurations available. It is possible to create custom configurations by mixing and matching solutions.

Bronze Level

Bronze architecture provides limited levels of protection:

• Single instance Oracle Database configured on a single server with Oracle Clusterware.
• ASM (Automated Storage Management) protects against the loss of a single disk.
• Oracle Restart uses Oracle Clusterware to automatically restart the database if the database software crashes.
• Local backups allow rapid recovery from local issues.
• Remote copies of backups provide recovery possibilities from a local disaster.

Scenarios still vulnerable:

• Server failure
• Multiple disk failures
• Datacenter failure

Conclusion: The Bronze level probably would not have protected Carved Rock Fitness against their long hiatus.

Silver Level

The silver architecture provides enhanced levels of protection thanks to Oracle Real Application Clusters (RAC):

• The database can continue processing queries even with complete single node failure.
• Oracle RAC consists of two or more clustered database servers, each running an instance of Oracle Database.
• With Oracle RAC, single node failure does not take offline the database system. The remaining nodes carry the workload.

Note: RAC is an additional licensed option and can be expensive.

Scenarios still vulnerable:

• Anything that would affect the entire cluster, such as a power outage or natural disaster.

Conclusion: Oracle Database configured at Silver level probably would have avoided the recent Carved Rock Fitness outage.

Gold Level

The gold architecture leverages Oracle Data Guard to provide enhanced levels of protection against downtime:

• Data Guard provisions a hot standby database in a remote location.
• Mitigates the risk of downtime associated with component failure, data center failure and/or localized disaster.
• Active Data Guard (extension) provides read-only access to the standby database to increase capacity.
• The standby database is kept up to date by continuous shipping of the Oracle redo from the primary database.
• In the event of a failure of the server hosting the primary database, the standby database can immediately take the role of primary.
• Downtime is often measured in seconds while the role transition takes place.

Licenses:

• Standard Data Guard is included with Enterprise Edition.
• Active Data Guard must be licensed separately.

Conclusion: The Gold level would most likely have protected Carved Rock Fitness from their recent disruption.

Platinum Level

Platinum architecture introduces Oracle GoldenGate, the key underlying technology:

Differences between GoldenGate and Data Guard:

GoldenGate complexity:

• Often requires application changes and architectural considerations.
• Should prevent duplicate identifiers.
• Ensures changes are only applied once.

Conclusion: GoldenGate probably would have protected Carved Rock Fitness from their recent disruption. Oracle suggests that this can virtually eliminate RTO and RPO through logical replication.

2. Understand, Configure and Manage Data Guard

Why Data Guard for Carved Rock Fitness

As a senior DBA, there were two main reasons why I chose Oracle Data Guard as my first step:

1. Data Guard reportedly virtually eliminated the downtime that Carved Rock Fitness experienced due to the recent database server failure. It is easy to articulate precisely how Data Guard would have eliminated the recent outage and what impact, if any, the server failure would have caused in a Data Guard-protected scenario.

2. Carved Rock Fitness already has an Enterprise Edition license of Oracle Database, which covers or includes Oracle Data Guard. This fact was particularly attractive to management. Although Carved Rock had to increase the number of server cores they were licensed for, the cost was minimal compared to other Oracle technology options.

Enhanced scenario: With Data Guard in place, the server failure would have only taken the Oracle database offline for 90 seconds. The financial impact would have been negligible, and management would have felt satisfaction and confidence in their IT posture.

Data Guard Fundamentals

A typical Data Guard configuration consists of two separate but linked Oracle databases, each living on a separate server, ideally located in separate data centers.

Datacenter A (Est)          Réseau          Datacenter B (Ouest)
┌─────────────────┐        ─────────        ┌─────────────────┐
│   Base "East"   │  ──── Redo Shipping ──► │   Base "West"   │
│   (Primaire)    │  ◄─── Redo Apply ──────  │   (Standby)     │
└─────────────────┘                          └─────────────────┘

Database roles:

Process:

• Redo Shipping: process by which the redo (the record of changes made to the primary database) is copied from the primary database to the standby database.
• Redo Apply: the standby database applies these changes, thus remaining synchronized with the primary database.

The role of a database is not permanent and can change to meet various needs.

Role transitions: Switchover and Failover

When the roles of the primary and/or standby database change, this is called a role transition.

After a failover:

• The configuration will consist of only one primary database.
• Other members of the Data Guard configuration will need to be repaired and rebuilt.

Protection modes

Data Guard offers three main protection modes:

Tip: The instructor recommends Maximum Performance mode because it provides the protections of Data Guard without impacting database performance.

Planned switchover scenarios

Several reasons can justify the manual initiation of a switchover:

1. Upgrades: Upgrades or environmental maintenance activities may require downtime, such as server operating system upgrades, memory upgrades, or major database software upgrades. Data Guard can help by taking advantage of the standby server while work is being done on the primary.

2. Patches (Patching): Most Oracle patches support Data Guard Standby First Patching. This allows patches to be applied to the standby database first, then to the primary database. Recommended process:

• Apply patches to standby
• Allow some time to validate the patches
• Perform a switchover to the newly patched standby
• Apply patches to the old primary (now standby)

Caution: Always read the patch instructions carefully to validate the specific approaches supported. Not all patches support Data Guard standby first patching.

3. Database Migrations: Hardware doesn’t last forever. Data Guard can be useful for migrating to a new database server. Suggested approach:

• Provision and deploy a new standby server
• Mount a second standby database on the new server
• Decommission the old standby server
• Perform a switchover to also replace the primary

Data Guard management: choice of tools

Several tools allow you to configure, query and manage Data Guard:

Demo: Querying with the Data Guard Broker

Invocation of the Broker:

# Avec authentification OS
dgmgrl /

# Avec authentification par fichier de mots de passe (recommandé pour les switchovers)
dgmgrl sys/password

show configuration command:

DGMGRL> show configuration;

Example output:

Configuration - MODULE1_A_MODULE1_7nz_iad

  Protection Mode: MaxPerformance
  Members:
  MODULE1_A          - Primary database
  MODULE1_7nz_iad    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 15 seconds ago)

Analysis of output:

• Configuration name: arbitrary (here based on the names of the primary and standby databases).
• Protection Mode: MaxPerformance (default mode).
• Members: list of Data Guard configuration members with their roles.
• Configuration Status: SUCCESS — no error in the configuration.

Demo: Role transition (Switchover)

# 1. Invoquer le Broker avec authentification par fichier de mots de passe
dgmgrl sys/password

# 2. Afficher la configuration
DGMGRL> show configuration;

# 3. Valider la disponibilité du standby pour le switchover
DGMGRL> validate database MODULE1_7nz_iad;
# Output: Ready for Switchover: Yes

# 4. Consulter l'aide pour la syntaxe du switchover
DGMGRL> help switchover;

# 5. Effectuer le switchover
DGMGRL> switchover to MODULE1_7nz_iad;

# 6. Vérifier la configuration après le switchover
DGMGRL> show configuration;

Result: The Broker initiates the necessary steps to perform the role reversal. The old standby becomes primary, and the old primary becomes standby. The Broker reports the success of the switchover in the console.

Configuring Data Guard

The components needed to configure a Data Guard environment:

1. Standby Server

• A server must be set up to host the standby database.
• The operating system must be installed and configured.
• Oracle Database software must be installed.

2. Network connectivity

• Network connectivity between primary and standby database servers must be configured.
• Firewall ports must be open to allow communication.
• Oracle Database defaults to port 1521, but each environment is unique.

3. Creation of the Standby database

• The standby database must be created on the standby server.
• Available methods:
• RMAN duplicate
• RMAN restore from service
• RMAN restore from backup

4. Configuring Data Guard

• Simply having a copy of the primary database on the standby server does not constitute Data Guard.
• A clean Data Guard configuration must be created and both databases must be associated with it.
• Methods: manually via SQL*Plus, Oracle Enterprise Manager, or Data Guard Broker (recommended).

5. Configuration check

• Once everything is configured, check that Data Guard is correctly set up.

Demo: Setting up a Data Guard environment

# Invoquer le Broker
dgmgrl sys/password

# 1. Vérifier l'absence de configuration existante
DGMGRL> show configuration;
# Résultat : ORA-16532: Data Guard broker configuration does not exist

# 2. Créer une nouvelle configuration
DGMGRL> create configuration carvedrock as
          primary database is 'MODULE1_A'
          connect identifier is MODULE1_A;

# 3. Ajouter la base de données standby
DGMGRL> add database 'MODULE1_7nz_iad' as
          connect identifier is MODULE1_7nz_iad
          maintained as physical;

# 4. Activer la configuration (le Broker effectue les étapes en arrière-plan)
DGMGRL> enable configuration;

# 5. Afficher la configuration nouvellement créée
DGMGRL> show configuration;

Data Guard Troubleshooting

Tools available for troubleshooting:

1. The Data Guard Broker log

• Located in the database trace directory, alongside the alert log.
• Provides detailed information about broker and Data Guard activity.
• File name format: drc<base_name>.log (e.g.: drcMODULE1.log).
• First place to look when things aren’t working properly.

2. Alert Log

• Contains a log of a plethora of database activities.
• Always a great place to look when troubleshooting any Oracle Database issues.

3. The Data Guard Broker itself

• Provides many convenient commands for querying configuration status.
• See Oracle documentation for a detailed list of broker commands.

4. SQL*More

• Useful for querying various database parameters related to Data Guard.

Data Guard configuration parameters

-- Interroger les paramètres liés au broker DG
SQL> show parameter dg_broker;

-- Output exemple :
-- dg_broker_config_file1    string    /u01/app/oracle/product/19c/db_1/dbs/dr1MODULE1.dat
-- dg_broker_config_file2    string    /u01/app/oracle/product/19c/db_1/dbs/dr2MODULE1.dat
-- dg_broker_start           boolean   TRUE

-- Interroger le paramètre de configuration d'archive log
SQL> show parameter log_archive_config;
-- Affiche les membres de la configuration Data Guard
-- Exemple : DG_CONFIG=(MODULE1_A,MODULE1_7nz-iad)

-- Interroger les paramètres de shipping de logs
SQL> show parameter log_archive_dest;

Data Guard Monitoring

Useful views for monitoring:

-- Informations sur la base de données et son rôle
SELECT db_unique_name, database_role, open_mode
FROM v$database;

-- Configuration Data Guard
SELECT * FROM v$dataguard_config;

-- Statut Data Guard
SELECT * FROM v$dataguard_status
ORDER BY timestamp;

3. Understanding and Exploiting RAC for High Availability

What is Oracle RAC?

Oracle RAC (Real Application Clusters) is a high availability and scalability solution for Oracle Database.

Vertical vs. horizontal scalability

Oracle RAC leverages horizontal scalability by combining two or more servers into a database cluster.

Combined benefits:

• Oracle Database can continue to service the workload in the event of loss of a single server, with other nodes remaining online.
• Oracle Database capacity is effectively increased in proportion to the number of nodes in the cluster.
• Many planned maintenance activities can now be performed in rolling mode (one node at a time), with the database remaining available.

RAC architecture

Oracle RAC is a combination of many technologies:

          ┌─────────────────────────────────────────────┐
          │             SCAN Listener                    │
          │   (Single Client Access Name)                │
          └─────────────┬───────────────────────────────┘
                        │ Équilibrage de charge
          ┌─────────────┴────────────────┐
          │                              │
   ┌──────┴──────┐                ┌──────┴──────┐
   │  Nœud RAC 1  │◄──Interconnect─►│  Nœud RAC 2  │
   │  (cr1)       │  (InfiniBand/  │  (cr2)       │
   │  Instance 1  │   RoCE)        │  Instance 2  │
   └──────┬──────┘                └──────┬──────┘
          │                              │
          └──────────────┬───────────────┘
                         │
                ┌────────┴────────┐
                │  Stockage ASM   │
                │  (Partagé)      │
                └─────────────────┘

Components:

RAC Basic Commands

Database query commands:

# Voir le statut de la base de données (en cours d'exécution ou non, et où)
srvctl status database -d <nom_base>

# Interroger de nombreux paramètres de configuration pour une base de données donnée
# (emplacement du fichier de mots de passe, spfile, mode de démarrage par défaut)
srvctl config database -d <nom_base>

SCAN Query Commands:

# Interroger le statut des VIP SCAN
srvctl status scan

# Voir les paramètres de configuration SCAN (réseau, sous-réseau, IP virtuelle)
srvctl config scan

ASM Query Commands:

# Déterminer sur quels nœuds les instances ASM s'exécutent
srvctl status asm

# Afficher le répertoire Oracle Home, le nom de l'écouteur ASM
# et le nombre d'instances ASM s'exécutant dans le cluster
srvctl config asm

Demo: RAC Polling Commands

# Vérifier le statut de la base de données sur le cluster
srvctl status database -d CRDB
# Output : Database is not running on node cr1
#          Database is not running on node cr2

# Vérifier le statut des VIP SCAN
srvctl status scan
# Output : SCAN VIP scan1 is enabled
#          SCAN VIP scan1 is running on node cr2
#          SCAN VIP scan2 is enabled
#          SCAN VIP scan2 is running on node cr1
#          SCAN VIP scan3 is enabled
#          SCAN VIP scan3 is running on node cr1

# Vérifier le statut ASM sur le cluster
srvctl status asm
# Output : ASM is running on cr1,cr2

# Vérifier le statut des services de base de données sur le cluster
srvctl status service -d CRDB
# Output : Service reporting is running on instance(s) CRDB1,CRDB2
#          Service shipping is running on instance(s) CRDB1,CRDB2

Note: Virtual IP addresses (Virtual IPs) can float among cluster nodes to maintain high availability during periods when a given node is offline for planned or unscheduled maintenance.

Database vs Instance in RAC

Oracle Database 101 reminder:

In Oracle RAC:

• A single database (physical data files on disk)
• One or more instances (in-memory structures and running processes), each living on a separate server member of the cluster

Each instance can have a local database cache buffer with cached copies of the most recently accessed data on that particular server. Another server in the cluster may have different cached data. Each server can handle the workload independently, including query parsing, data sorting, data filtering.

RAC instance management commands

# Interroger le statut d'une instance individuelle
srvctl status instance -d <nom_base> -i <nom_instance>

# Arrêter une instance individuelle sur un nœud de cluster
srvctl stop instance -d <nom_base> -i <nom_instance>

# Arrêter la base de données RAC entière (toutes les instances)
srvctl stop database -d <nom_base>

# Démarrer la base de données RAC entière
srvctl start database -d <nom_base>

Demo: Managing RAC instances

# 1. Interroger le statut de la base de données
srvctl status database -d CRDB
# Output : Instance CRDB1 is running on node cr1
#          Instance CRDB2 is running on node cr2

# 2. Interroger le statut de l'instance CRDB1
srvctl status instance -d CRDB -i CRDB1
# Output : Instance CRDB1 is running on node cr1

# 3. Arrêter l'instance CRDB1 (pour maintenance sur le nœud 1)
srvctl stop instance -d CRDB -i CRDB1

# 4. Vérifier que CRDB1 est arrêtée mais que CRDB2 continue de fonctionner
srvctl status database -d CRDB
# Output : Instance CRDB1 is not running on node cr1
#          Instance CRDB2 is running on node cr2
# La base de données reste disponible via l'instance sur le nœud cr2

# 5. Arrêter toute la base de données RAC
srvctl stop database -d CRDB

# 6. Vérifier que plus aucune instance ne tourne
srvctl status database -d CRDB
# Output : Instance CRDB1 is not running on node cr1
#          Instance CRDB2 is not running on node cr2

RAC Load Balancing

Load balancing is facilitated in Oracle RAC through a combination of:

1. Database Services — manual mechanism for workload management by the DBA.
2. SCAN Listener (Single Client Access Name) — more automated mechanism for load balancing.

Database Services:

• Allow the DBA to assign services and thus associate workloads with specific cluster nodes.
• Example:
• A reporting service can be configured to run only on node 1.
• A shipping service can be configured to run on nodes 2 and 3.
• This helps separate the ad hoc reporting workload from the OLTP workloads.

SCAN Listener:

• Allows all clients to connect to the database cluster using a single name.
• Route traffic to the least busy node when establishing new connections.
• When a client requests a connection through a specific service, the SCAN Listener routes the request to the least busy node among those where that service is running.

Services and SCAN Listener

Service Query Commands:

# Statut d'un service donné (en cours d'exécution ou non, et sur quelles instances)
srvctl status service -d <nom_base> -s <nom_service>

# Configuration d'un service (activé, rôle primaire/standby, etc.)
srvctl config service -d <nom_base> -s <nom_service>

# Déplacer un service d'une instance à une autre
srvctl relocate service -d <nom_base> -s <nom_service> -oldinst <ancienne_instance> -newinst <nouvelle_instance>

SCAN Listener polling commands:

# Statut des adresses IP virtuelles SCAN
srvctl status scan

# Statut du SCAN Listener
srvctl status scan_listener

# Configuration par défaut du SCAN Listener
srvctl config scan_listener

Demo: Services, SCAN Listener and Connectivity

# 1. Interroger les services de la base de données
srvctl status service -d CRDB
# Output : Service reporting is running on instance(s) CRDB1
#          Service shipping is running on instance(s) CRDB1,CRDB2

# 2. Migrer le service reporting (pour maintenance sur le nœud CRDB1)
srvctl relocate service -d CRDB -s reporting -oldinst CRDB1 -newinst CRDB2

# 3. Vérifier le déplacement du service
srvctl status service -d CRDB
# Output : Service reporting is running on instance(s) CRDB2
#          Service shipping is running on instance(s) CRDB1,CRDB2
# Le service reporting a été déplacé vers CRDB2
# La maintenance sur le nœud 1 peut maintenant être effectuée

RAC Connectivity with Easy Connect and TNS

Easy Connect syntax:

sqlplus username/password@scan-hostname:port/service_name

Example of tnsnames.ora file:

CRDB_reporting =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cr-scan.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = reporting.example.com)
    )
  )

Connection via TNS Alias:

# Vérifier la résolution de l'alias TNS
tnsping CRDB_reporting
# Output : Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cr-scan.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=reporting.example.com)))

# Connexion à la base de données
sqlplus demo/password@CRDB_reporting

# Vérifier l'instance connectée
SQL> SELECT instance_name FROM v$instance;
# Output : CRDB2 (routé vers le nœud le moins occupé par le SCAN)

RAC Benefits and Considerations

Benefits (blessings):

1. High availability — the database can continue to operate with the loss of a node
2. Horizontal scalability — capacity increased proportionally to the number of nodes
3. Rolling patching — maintenance without downtime

Considerations before deploying RAC:

Recommendation for Carved Rock Fitness: Senior DBA recommends against Oracle RAC for this environment because:

• Data Guard would have mitigated the recent outage just as effectively as RAC.
• Carved Rock Fitness does not have capacity issues related to its database infrastructure.
• Data Guard addresses DR considerations in a way that RAC, with a single cluster, cannot.
• Given the significant cost and complexity associated with RAC, it is not recommended for Carved Rock Fitness.

4. Understanding and Exploiting ASM for High Availability

What is Oracle ASM?

Oracle ASM (Automated Storage Management / Automated Storage Manager) implements a storage architecture that:

• Mitigates the risk associated with component failure causing an interruption.
• Simplifies storage management for the DBA.
• May improve performance.
• Maintains redundant copies of user data to mitigate the risk of data loss.

ASM architecture

Conceptually, Oracle ASM can be thought of as a large pool of individual disks intelligently grouped together to maximize high availability and expose storage pools for use by Oracle Database.

Failure Group 1    Failure Group 2    Failure Group 3    Failure Group 4    Failure Group 5
┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│  Contrôleur 1│  │  Contrôleur 2│  │  Contrôleur 3│  │  Contrôleur 4│  │  Contrôleur 5│
│  Disque 1-4  │  │  Disque 5-8  │  │  Disque 9-12 │  │  Disque 13-16│  │  Disque 17-20│
└──────┬───────┘  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘
       │                 │                 │                  │                  │
       └─────────────────┴─────────────────┴──────────────────┴──────────────────┘
                                           │
                              ┌────────────┴────────────┐
                              │       Disk Group DATA    │
                              │  (Expose à Oracle DB)    │
                              └─────────────────────────┘

Failure groups logic:

• Disks sharing common failure points (such as a RAID controller) are grouped into the same failure group.
• ASM distributes data across multiple failure groups to ensure that no group loss results in data loss.

Scenarios simplified by ASM

Disk failure recovery example:

Avant la défaillance :
  Array 1 (FG1) : D103 (copie 1)    Array 3 (FG3) : D103 (copie 2)

Pendant la défaillance d'un disque dans Array 5 (FG5) :
  ASM détecte la défaillance
  ASM copie les données depuis la bonne copie restante (ex: Array 2)
  vers un nouveau disque sain
  → Redondance des données restaurée automatiquement

ASM Key Terminology

Conventions for Disk Groups:

How ASM runs

Oracle ASM is implemented as a special type of Oracle Database instance running on top of Oracle Clusterware:

• The instance has a parameters file like any other Oracle Database instance.
• Does not have data files because ASM is an instance only, not a database.
• Other databases use ASM for their own storage needs.
• ASM is a component of the Oracle Grid Infrastructure architecture (also known as Clusterware).
• When Oracle Grid Infrastructure is installed, ASM can be configured as part of the installation.

Management tools:

• SQL*Plus — to modify various parameters and query the ASM environment.
• ASMCMD — separate command line utility for interacting with the ASM file system.

ASM Query Views and Parameters

-- Vue v$asm_diskgroup : informations sur les disk groups
-- (nom, taille, failure group associé)
SELECT group_number, name, type, total_mb, free_mb
FROM v$asm_diskgroup;

-- Vue v$asm_disk : détails sur les disques qui composent les disk groups
SELECT group_number, disk_number, name, failgroup, total_mb, free_mb, state
FROM v$asm_disk
ORDER BY group_number, disk_number;

-- Vue v$asm_operation : opérations ASM en cours (rebalance, re-silvering, etc.)
SELECT group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes
FROM v$asm_operation;

-- Paramètre asm_power_limit : contrôle les ressources allouées aux opérations ASM
-- Valeur 1 = très peu de ressources, Valeur 11 = beaucoup de ressources
SHOW PARAMETER asm_power_limit;

-- Modifier le paramètre asm_power_limit
ALTER SYSTEM SET asm_power_limit = 4;

Note: These views can be queried directly from the ASM instance or from any database using the ASM instance for storage.

ASMCMD Utility

The asmcmd utility allows you to interact with the ASM file system for operations such as:

# Démarrer ASMCMD
asmcmd

# Afficher le répertoire de travail actuel
ASMCMD> pwd

# Lister le contenu d'un répertoire
ASMCMD> ls

# Changer de répertoire
ASMCMD> cd +DATA

# Naviguer dans le disk group DATA
ASMCMD> ls +DATA/CRDB/DATAFILE/

# Copier un fichier
ASMCMD> cp +DATA/CRDB/DATAFILE/users.267.1234567 /tmp/

# Supprimer un fichier
ASMCMD> rm +DATA/CRDB/DATAFILE/old_file.dbf

Demo: Querying an ASM environment

-- 1. Connecté à l'instance ASM, interroger les disk groups
SELECT group_number, name, type, total_mb, free_mb
FROM v$asm_diskgroup;
-- Output :
-- GROUP# NAME TYPE   TOTAL_MB FREE_MB
-- 1      DATA EXTERN 40960    30720
-- 2      RECO EXTERN 20480    18432

-- 2. Interroger les disques composant les disk groups
SELECT group_number, disk_number, name, failgroup, total_mb
FROM v$asm_disk ORDER BY group_number;
-- Output :
-- GROUP# DISK# NAME        FAILGROUP   TOTAL_MB
-- 1      0     DATA_0000   DATA_0000   10240
-- 1      1     DATA_0001   DATA_0001   10240
-- ... (4 disques pour DATA, 4 disques pour RECO)

-- 3. Interroger le paramètre asm_power_limit
SHOW PARAMETER asm_power_limit;
-- Output : asm_power_limit integer 1

-- 4. Vérifier les opérations ASM en cours
SELECT * FROM v$asm_operation;
-- Output : (aucune opération en cours)

-- 5. Connecté à la BASE DE DONNÉES utilisant ASM
-- Vérifier le paramètre db_create_file_dest
SHOW PARAMETER db_create_file_dest;
-- Output : db_create_file_dest string +DATA

-- 6. Interroger les data files de l'espace de table USERS
SELECT tablespace_name, file_name, bytes/1024/1024 AS mb
FROM dba_data_files
WHERE tablespace_name = 'USERS';
-- Output : USERS  +DATA/CRDB/DATAFILE/users.267.1234567890  100 MB

-- 7. Dans ASMCMD, voir le fichier physique
-- asmcmd> ls +DATA/CRDB/DATAFILE/
-- Output : USERS.267.1234567890
-- (correspond au data file vu depuis la base de données)

5. Understanding and Exploiting RMAN for Disaster Recovery

Motivation: Why save?

Backing up an Oracle database can be as simple as:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Why back up your database:

• Hardware fails
• Users make errors
• Natural disasters are a fact of life
• RMAN makes backup and recovery easy

Carved Rock Fitness Backup Posture

The backup posture before the incident was problematic:

Questions to ask: Do you take backups regularly? Where do your backups reside? When was the last time you tested your backups? Are your recovery skills sharp?

Introduction to RMAN

Oracle Recovery Manager (RMAN) implements the Oracle solution for backup and recovery needs.

• RMAN can manage taking backups, restoring and recovering from a backup.
• RMAN is both a technology and a command line tool (rman).

Ideal backup storage

Best practice: Take local backups to facilitate rapid recovery, then copy those backups to a remote location to mitigate the risk of data loss in a catastrophic scenario.

Essential RMAN Commands

Invocation of RMAN:

# Avec authentification OS
rman /

# Avec authentification par fichier de mots de passe
rman target sys/password

# Spécifier une base cible
rman target /

Querying backups:

-- Liste sommaire de toutes les sauvegardes connues de RMAN
RMAN> LIST BACKUP SUMMARY;

-- Liste détaillée de toutes les sauvegardes connues de RMAN
RMAN> LIST BACKUP;

-- Rapport sur la structure de la base de données (table spaces et data files)
RMAN> REPORT SCHEMA;

Reading the output of LIST BACKUP SUMMARY:

Recommended backup command:

-- Sauvegarder la base de données ET les archive logs
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

The Control File: critical role

The control file is the primary source for all backup metadata:

• Contains a record of all files that make up an Oracle database (all data files).
• Maintains a record of all database backups.
• Without the control file, an Oracle Database cannot be mounted or opened.

Protection and backup of the Control File

Multiplexing of the Control File:

-- Configurer le multiplexage dans le SPFILE
-- (plusieurs emplacements pour le control file)
ALTER SYSTEM SET control_files = 
  '/u01/oradata/db1/control01.ctl',
  '/u02/oradata/db1/control02.ctl',
  '/u03/oradata/db1/control03.ctl'
SCOPE=SPFILE;
-- La base de données doit être redémarrée pour que ce changement prenne effet

Warning: The control file must exist in all locations specified by the control_files parameter before the database can be opened. You can manually copy the control file to each location while the database is not mounted.

Control File Autobackup:

-- Activer l'autobackup du control file
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

-- Configurer l'emplacement de l'autobackup avec le format %F
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/backups/cf_%F';

This feature automatically backs up the control file AND the SP file whenever a backup record is created or the database structure in the control file changes.

RMAN and AUTOBACKUP CONTROLFILE configuration

Show all RMAN configurations:

RMAN> SHOW ALL;

Example output:

RMAN configuration parameters for database with db_unique_name DB1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/backups/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/backups/db1_%U';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19c/db_1/dbs/snapcf_db1.f';

Substitution variable %F in RMAN

The variable %F is a substitution variable in RMAN that is extended to include:

1. The DBID (Database ID) of the database
2. The date on which the control file was saved
3. A hexadecimal sequence number

Example of file name generated by %F:

cf_c-2040676155-20250619-00
   │ └──────────┘ └──────┘ └─
   │   DBID        Date      Séquence hex
   Préfixe "cf"

Advantages of this nomenclature during a disaster:

Validating backups

Basic validation steps:

-- 1. Vérifier que les jobs de sauvegarde s'exécutent sans erreur
-- (Consulter les logs de sauvegarde)

-- 2. Vérifier que RMAN est au courant des sauvegardes
RMAN> LIST BACKUP SUMMARY;

-- 3. Vérifier que les fichiers de sauvegarde existent réellement
RMAN> CROSSCHECK BACKUP;

-- 4. Demander à RMAN de valider que les fichiers requis sont disponibles
RMAN> VALIDATE DATABASE;

-- 5. Validation complète d'un backup set spécifique
RMAN> VALIDATE BACKUPSET 42;

The only real test: Regularly test your ability to completely restore and recover your database from backups alone. This is the only way to know for sure that the backups are good.

Regular testing ensures:

• That the DBA maintains the skills necessary to perform restoration and recovery operations.
• That the organization can effectively recover when a disaster occurs.

Recommended RMAN Settings

-- 1. Autobackup du control file activé
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

-- 2. Emplacement sécurisé pour l'autobackup du control file avec format %F
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/backups/cf_%F';

-- 3. Politique de suppression des archive logs (maintenir au moins 1 copie sur disque)
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

-- 4. Politique de rétention (fenêtre de récupération de 7 jours)
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Explanations:

Demo: Complete disaster recovery

Scenario: Someone accessed the database server and deleted the entire database. Everything is lost.

Situation after disaster:

• The IT team has deployed a new database server.
• Database backup storage (located remotely) has been attached.
• Oracle Database software was installed from a golden image.

7 Step Recovery Process:

Step 1 — Identify the Control File backup

# Interroger le partage de fichiers contenant les sauvegardes de la base de données
ls -la /u03/backups/cf_*

# Output (exemple) :
# cf_c-2040676155-20250615-00
# cf_c-2040676155-20250616-00
# cf_c-2040676155-20250617-00
# cf_c-2040676155-20250618-00
# cf_c-2040676155-20250619-00   ← Choisir la plus récente avant l'incident

# Vérifier le DBID : 2040676155 (documenté en dehors du serveur de base de données)

Step 2 — Restore the SP File from the Control File Backup

# Définir l'environnement Oracle
export ORACLE_SID=MODULE1

# Invoquer RMAN (sans spfile, RMAN crée un environnement temporaire)
rman target /

# Démarrer l'instance sans montage
RMAN> STARTUP NOMOUNT;

# Restaurer le spfile depuis la sauvegarde du control file
RMAN> RESTORE SPFILE FROM '/u03/backups/cf_c-2040676155-20250619-00';
# RMAN place le spfile restauré dans : $ORACLE_HOME/dbs/spfileMODULE1.ora

Step 3 — Review and adjust the SP File if necessary

# Créer un pfile lisible par l'humain depuis le spfile
sqlplus / as sysdba
SQL> CREATE PFILE='/tmp/pfile_module1.ora' FROM SPFILE;

# Réviser le pfile (ajuster les chemins, la mémoire si le nouveau serveur est différent)
# Éditeur de texte pour réviser le pfile
vi /tmp/pfile_module1.ora

# Recréer le spfile depuis le pfile édité (si des modifications ont été apportées)
SQL> CREATE SPFILE FROM PFILE='/tmp/pfile_module1.ora';

Step 4 — Restore the Control File

# Démarrer l'instance sans montage (force bounce avec le nouveau spfile)
RMAN> STARTUP NOMOUNT FORCE;

# Restaurer le control file depuis la sauvegarde
RMAN> RESTORE CONTROLFILE FROM '/u03/backups/cf_c-2040676155-20250619-00';

Step 5 — Mount the database and restore data files

# Monter la base de données (l'instance lit maintenant le control file restauré)
RMAN> STARTUP MOUNT FORCE;
# Le control file connaît maintenant les noms et emplacements de tous les data files
# et a un catalogue de toutes les sauvegardes pour cette base de données

# Restaurer la base de données
# (copie les fichiers requis depuis la sauvegarde vers leurs emplacements appropriés)
RMAN> RESTORE DATABASE;
# Output : Finished restore at 19-JUN-25

Step 6 — Recover the database

# Récupérer la base de données
# (amène les data files restaurés à un état cohérent en appliquant les archived redo logs)
RMAN> RECOVER DATABASE;
# Note : Un message d'erreur apparent peut apparaître :
# "ORA-00308: cannot open archived log..."
# Cela signifie simplement qu'il n'y a plus d'archived logs supplémentaires au-delà
# de la séquence de log 42. Ce n'est PAS une erreur critique.

Step 7 — Open Database

# Ouvrir la base de données avec RESETLOGS
# (nécessaire car c'est un scénario de perte totale sans online redo logs disponibles)
RMAN> ALTER DATABASE OPEN RESETLOGS;

# Vérifier que la base de données est ouverte et en mode lecture-écriture
RMAN> SELECT name, db_unique_name, open_mode FROM v$database;
# Output :
# NAME     DB_UNIQUE_NAME  OPEN_MODE
# MODULE1  module1         READ WRITE

Congratulations! The database was successfully restored from a backup in a total disaster scenario. Recovery is possible until the backup of June 19, 2025 and any archive log backed up beyond that.

6. Summary and Key Points

Oracle MAA: Tier Comparison

Key Recommendations for an Oracle DBA

1. Assess the environment — Identify single points of failure in the database infrastructure.

2. Implement Data Guard — Oracle Data Guard is included in Enterprise Edition and offers an excellent protection/cost ratio. It would have eliminated the majority of “server failure” type interruptions.

3. Configure RMAN correctly:

• Enable control file autobackup.
• Use %F format for file names.
• Store backups locally AND remotely.
• Configure a retention policy aligned with the company’s RPO.

4. Test backups regularly — Perform comprehensive recovery tests. This is the only way to know for sure that the backups are usable.

5. Document the DBID outside the database server — essential for total disaster recovery.

6. Choose the right Data Guard protection mode — Maximum Performance provides Data Guard protection without impacting performance in most cases.

7. Consider RAC only if necessary — RAC provides high availability and scalability, but its cost and complexity must be justified by real capacity or availability needs beyond what Data Guard can offer.

Quick Reference Commands

Data Guard Broker

# Invoquer le broker
dgmgrl /
dgmgrl sys/password

# Afficher la configuration
DGMGRL> show configuration;

# Valider un standby
DGMGRL> validate database <standby_name>;

# Effectuer un switchover
DGMGRL> switchover to <standby_name>;

# Créer une configuration
DGMGRL> create configuration <name> as primary database is '<primary_name>' connect identifier is <primary_name>;
DGMGRL> add database '<standby_name>' as connect identifier is <standby_name> maintained as physical;
DGMGRL> enable configuration;

RAC (srvctl)

srvctl status database -d <db_name>
srvctl status instance -d <db_name> -i <instance_name>
srvctl stop instance -d <db_name> -i <instance_name>
srvctl start database -d <db_name>
srvctl status scan
srvctl status scan_listener
srvctl status asm
srvctl status service -d <db_name>
srvctl relocate service -d <db_name> -s <service> -oldinst <old> -newinst <new>

ASM (SQL*Plus and ASMCMD)

SELECT group_number, name, type, total_mb, free_mb FROM v$asm_diskgroup;
SELECT group_number, name, failgroup, total_mb, state FROM v$asm_disk;
SELECT * FROM v$asm_operation;
SHOW PARAMETER asm_power_limit;
SHOW PARAMETER db_create_file_dest;

asmcmd
ASMCMD> ls +DATA
ASMCMD> cd +DATA/CRDB/DATAFILE
ASMCMD> pwd

RMAN

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP;
RMAN> REPORT SCHEMA;
RMAN> CROSSCHECK BACKUP;
RMAN> VALIDATE DATABASE;
RMAN> SHOW ALL;

-- Récupération complète depuis sinistre total :
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE SPFILE FROM '/path/to/cf_%F';
RMAN> STARTUP NOMOUNT FORCE;
RMAN> RESTORE CONTROLFILE FROM '/path/to/cf_%F';
RMAN> STARTUP MOUNT FORCE;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Search Terms

implement · high · availability · disaster · recovery · oracle · database · 19c · databases · sql · data · rac · guard · asm · rman · backup · commands · control · level · role · carved · exploiting · fitness · restore