Perform Backup and Recovery Operations on Oracle Database 19c

Technology: Oracle Database 19c, RMAN, SQL*Plus, Flashback Technology

Table of Contents

1. Module 1 — Understanding Oracle Backup and Recovery Concepts (25m 32s)

• Target audience, tools and prerequisites
• Why backup and recovery?
• Logical and physical backups
• Backup tools and fault types
• Oracle Backup and Recovery Architecture
• Fast Recovery Area (FRA)
• Demo: Configure the database for FRA

2. Configure and use RMAN for backup operations (1h 0m 38s)

• Consistent and inconsistent backups
• Oracle Recovery Manager (RMAN)
• RMAN backup methods: Image Copies and Backup Sets
• Connection to RMAN
• Demo: First steps with RMAN
• Recovery Catalog
• Demo: Create and connect to Recovery Catalog
• Full and incremental backups with RMAN
• Demo: Full and Incremental Backups
• Demo: List backups
• Demo: Back up control files and archived redo logs
• Demo: Validate backups
• Demo: Delete saves
• Obsolete Backups
• Expired Backups
• RMAN Scripts
• Demo: RMAN Scripts
• Demo: RMAN scripts with parameters

3. Perform database recovery operations (37m 13s)

• Complete and incomplete recovery
• Recovery with RMAN
• Demo: Complete recovery of a PDB — loss of USERS datafile
• Demo: Complete recovery of a PDB — loss of SYSTEM datafile
• Demo: Point-in-time Recovery of a PDB
• Demo: Complete recovery of a CBD
• Demo: Point-in-time Recovery of a CDB
• Demo: Recovery following the loss of a control file
• Block Media Recovery
• Demo: Block Media Recovery
• User Managed Backup and Recovery
• Demo: Manual recovery without RMAN

4. Implement Flashback Technology for Error Recovery (42m 22s)

• Flashback Technologies
• Flashback Prerequisites
• Demo: Configuring Flashback prerequisites
• Demo: Flashback Query
• Demo: Flashback Version Query
• Demo: Flashback Drop
• Demo: Flashback Transaction Query
• Demo: Flashback Table
• Demo: Flashback Database
• Demo: Restore Points
• Demo: Monitor Flashback Logs and Storage

5. Monitor and troubleshoot backup and recovery operations (40m 58s)

• Importance of reporting and monitoring
• Use RMAN Reports and Views to Monitor Backups
• Demo: RMAN reports and views for monitoring
• Understanding RMAN errors
• Test and validate recovery scenarios
• Data Recovery Advisor — Analysis and interpretation of recommendations
• Demo: Media recovery with the Data Recovery Advisor
• Demo: Corrupted block recovery with Data Recovery Advisor
• Backup Optimization: Incremental Backups and Parallelism
• Demo: Block Change Tracking and parallelism
• Optimization and compression of backups
• Demo: Optimization and compression of backups
• Optimize performance with V$BACKUP_ASYNC_IO
• Demo: Optimize performance with V$BACKUP_ASYNC_IO

1. Understand Oracle Backup and Recovery Concepts

1.1 Target audience, tools and prerequisites

Backup and recovery capabilities are the cornerstone of any organization’s business continuity and data protection strategies, protecting their most valuable asset: data.

Target audience:

• DBA (Database Administrators) responsible for maintaining and protecting database systems
• System administrators responsible for managing operating systems and the infrastructure that supports Oracle databases
• Technical support engineers who provide support for Oracle Database environments
• IT managers who oversee database operations and need to understand the importance of backup and recovery strategies

Tools used:

• SQL*Plus: command line tool for interacting with Oracle
• RMAN (Oracle Recovery Manager): main tool for backups and recoveries

Prerequisites: – Basic DBA Skills

• Fundamental Understanding of Oracle Database
• Accessing an Oracle Database 19c instance

1.2 Why backup and recovery?

Backup and recovery capabilities are essential to effectively manage databases. They act as a safety net against data loss and unexpected outages.

Role of backups:

• Insurance policy against accidental deletion, data corruption or cyber attacks
• Ensuring continuity of business operations and regulatory compliance
• Quickly restore operations, minimizing downtime and potential financial losses

Role of recovery techniques:

• Maintain data integrity and consistency after system failures, crashes or unexpected events
• Maintaining customer trust and ensuring the smooth running of the business

Key Principle: In the world of databases, it’s all about keeping information secure, consistent, and available when it’s needed most.

1.3 Logical and physical backups

Logical Backups

Logical backups extract the logical structure and contents of logical database objects (tables, views, stored procedures, etc.).

• Primary tool: Oracle Data Pump (formerly Oracle Export/Import)
• Data Pump exports data and metadata to a portable format file (binary, non-human readable, usable only with Data Pump Import)
• Advantages:
• Granularity: ability to specify specific objects, schemas or tablespaces
• Ideal for data migration or selective object backups
• Ability to migrate data from one Oracle version to another

Note: This course does not cover logical backups. To learn Data Pump, refer to the course Importing and Exporting Oracle Data for DBAs.

Physical Backups

Physical backups make copies of the different physical files in the database:

• Data files
• Control files
• Archived redo logs
• Server parameter files

Main tool: RMAN (Oracle’s main tool for physical backups)

Recovery process example:

1. Backup taken at 2:00 p.m.
2. Failure occurred around 6:00 p.m.
3. Restoring the database to 2:00 p.m. state using backup files
4. Applying archived redo logs one by one to bring the database to the necessary point

Redo files contain information about all database changes: DML changes (INSERT, UPDATE, DELETE) and DDL changes (CREATE, DROP of objects). They also maintain the sequence of events and therefore can be used to replay transactions.

1.4 Backup tools and fault types

Oracle Tools for Backup and Recovery

Note: This course focuses exclusively on tools provided by Oracle. There are also third-party tools and OS utilities.

Failure Types

Instance Failure:

• An Oracle Database instance closes unexpectedly
• Causes: Power outages, critical background process failures, emergency shutdown procedures
• Oracle automatically performs an instance recovery on reboot: roll forward changes to online redo logs, then roll back uncommitted transactions

Media Failure:

• Physical failure of a storage component (hard drive or other media type)
• Typically requires manual recovery using RMAN backups and archived redo logs

User Error:

• Accidental deletion or modification of data, without the possibility of automatic recovery
• Oracle Flashback Technology can often recover data with minimal impact and no downtime
• In severe cases it may be necessary to perform a full point-in-time recovery with RMAN backups

1.5 Oracle Backup and Recovery Architecture

Essential elements of Oracle’s backup and recovery strategy:

• Back up files: data files, archived redo logs, control files, server parameter files
• Use RMAN tools, Flashback Database or manual recovery in case of failure

Oracle instance components (relevant background processes)

+------------------------------------------+
|              SGA (System Global Area)     |
|  +--------------------+  +-------------+ |
|  | Database Buffer    |  | Redo Log    | |
|  | Cache              |  | Buffer      | |
|  +--------------------+  +-------------+ |
+------------------------------------------+
         |                      |
         v                      v
  [Data Files]          [Redo Log Files]
  [Control File]        [Archived Log Files]

Transaction flow:

1. User issues an SQL statement (INSERT, UPDATE, DELETE, DDL)
2. The data blocks are extracted from the data files to the Database Buffer Cache and modified (dirty blocks)
3. Redo entries (changes) are written to the Redo Log Buffer
4. When issuing a COMMIT: the Log Writer (LGWR) empties the redo log buffer to the online redo log files — the transaction is confirmed. Oracle returns the confirmation to the user.
5. The Database Writer (DBWR) periodically writes dirty blocks from the buffer cache to the data files on disk
6. The Checkpoint Process (CKPT) signals the DBWR to write the dirty blocks and updates the headers of the data files and the control file with the checkpoint information

Archive Process (ARC):

• Copies online redo log files to archived redo log files when they are full
• These archived redo logs allow recovery from any point in time

Recovery principle: In the event of a failure, Oracle can use archived redo logs to replay transactions and bring the database to its most recent possible state.

1.6 Fast Recovery Area (FRA)

The Fast Recovery Area (also sometimes called Flash Recovery Area) is a unified storage location for all recovery-related files. It serves as a centralized disk space managed by Oracle to store and manage various backup and recovery components.

FRA Objective

The idea behind FRA is that, by enabling fast backups for recent data and keeping data available on disk, it reduces the need for system administrators to retrieve backup tapes for recovery operations.

Oracle Recommendation: The FRA should be located on a separate storage device from the database files, so that backups are not affected if the database file storage device fails.

FRA Content

When FRA is enabled, Oracle writes all backup-related files to it:

• RMAN Backups
• Archived redo logs
• Automatic backups of control file and SPFILE
• Flashback logs

RMAN uses the default FRA when no specific backup destination is specified.

Automatic management

RMAN automatically manages files in FRA by removing obsolete backups and archive files that are no longer needed for recovery.

Configuration parameters

-- Vérifier les valeurs actuelles
SHOW PARAMETER db_recovery_file_dest;
SHOW PARAMETER db_recovery_file_dest_size;

-- Modifier les paramètres
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 255G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u03/app/oracle/fast_recovery_area' SCOPE=BOTH;

1.7 Demo: Configure Database for FRA

Demo Objectives:

1. Explore the demo environment
2. Enable Archive Log Mode for the database
3. Configure the Fast Recovery Area

Demo script — File 01_01.txt

-- Connexion à la root container database
sqlplus / as sysdba

-- Lister les containers
SELECT name, open_mode FROM V$containers;
exit;

-- Connexion en tant qu'utilisateur demo dans la PDB
sqlplus demo/demo@devpdb

desc demotable;
select * from demotable;
exit;

-- Retour à la root database
sqlplus / as sysdba

-- Vérifier le mode d'archivage
archive log list;

-- Activer l'Archive Log Mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;

-- Confirmer que l'archivage est actif
archive log list;

-- Configurer la FRA
show parameter db_recovery_file_dest;
alter system set db_recovery_file_dest_size=255G scope=both;
show parameter db_recovery_file_dest;
quit;

Prepare script — Create user demo (01-createuserdemo.txt)

-- Connexion à la PDB devpdb en tant que sysdba
sqlplus sys/oracle@devpdb as sysdba

-- Créer l'utilisateur demo
create user demo identified by demo
  temporary tablespace temp
  default tablespace users
  quota unlimited on users;
grant connect, resource to demo;
exit;

-- Connexion en tant que demo pour créer la table de test
sqlplus demo/demo@devpdb

CREATE TABLE DEMOTABLE(name VARCHAR2(20));
INSERT INTO DEMOTABLE VALUES('TEST');
COMMIT;
EXIT;

Summary of SQL*Most Observed Views

After configuration, the output of SELECT name, open_mode FROM V$containers displays:

• CDB$ROOT — READ WRITE
• PDB$SEED — READ ONLY
• ORCLPDB — READ WRITE
• DEVPDB — READ WRITE (contains user demo with table demotable)

2. Configure and use RMAN for backup operations

2.1 Consistent and inconsistent backups

Consistent backups (Cold Backup)

• Backup taken after cleanly closing the database (SHUTDOWN NORMAL, IMMEDIATE or TRANSACTIONAL)
• If the database is stopped with the ABORT option, the backup is inconsistent (unless the database is read-only)
• Database is offline during backup
• Can be done with the database in NOARCHIVELOG mode
• The SCN (System Change Numbers) of the data files and the control file are synchronized: the database can be restored without additional recovery

Inconsistent backups (Hot Backup)

• Backups taken while the database is open and running
• The database must be in ARCHIVELOG mode
• Users can continue to access the database during backup
• Because SCNs do not match between data file headers, Oracle cannot open the database until all SCNs match — so recovery is required to apply redo log changes

Common practice: Most companies cannot afford downtime, so hot backups are more common in production. The ARCHIVELOG mode is therefore an essential prerequisite.

2.2 Oracle Recovery Manager (RMAN)

RMAN is a built-in command line tool and Oracle’s recommended tool for backup and recovery.

What RMAN can backup and recover

• Data files
• Control files
• Server parameter files
• Archived redo log files

With these files, it is possible to completely rebuild the database.

Multitenant architecture support

In the Multitenant architecture, RMAN allows you to perform backup and recovery operations on:

• The entire CDB (Multitenant Container Database)
• The root
• One or more PDBs (Pluggable Databases)

The commands used for CDB and PDB are identical to those used for non-CDB, with slight syntax variations.

RMAN Key Features

2.3 RMAN backup methods: Image Copies and Backup Sets

Image Copies

Image Copies are exact, byte-for-byte copies of the original files.

• Exact copy of a single data file, archived redo log file or control file
• Essentially equivalent to a cp at OS level, but registered in the RMAN repository
• RMAN can create image copies only on disk
• Compression and encryption cannot be applied
• Take up more space, but are quicker to restore (no overhead linked to reading a backup set)
• If a copy image is available on disk, RMAN can use it directly as a replacement for the data file without having to copy it from the backup location

Backup Sets

Backup Sets are logical entities produced by the RMAN BACKUP command in a proprietary format.

• Can be stored on disk and tape
• RMAN can use block compression when creating backup sets
• Supports encryption of backup sets
• RMAN ignores unused blocks of the data file when creating backup sets (block-level copying)
• Result: backups generally smaller than image copies
• Also reduces the time needed to create backups

By default, RMAN creates backup sets. This is the format used in this course.

2.4 Connection to RMAN

RMAN is a command line tool, invoked by typing rman.

Connection methods

# Connexion à la root database via OS authentication
rman target /

# Connexion avec Oracle Net Service Name
rman target sys/<password>@orclcdb

# Connexion sans mot de passe en clair (RMAN demandera le mot de passe)
rman target sys@orclcdb

# Connexion d'abord à RMAN puis connect
rman
RMAN> connect target /

# Connexion à une PDB spécifique
rman target sys/<password>@orclpdb

SYSBACKUP role

SYSBACKUP is a role created specifically by Oracle for backup and recovery operations in CDBs and PDBs.

• Provides a more secure alternative to the SYSDBA privilege
• Grants only necessary permissions for backup and recovery tasks: startup, shutdown, ALTER DATABASE, CREATE RESTORE POINTS, etc.
• Best practice: create users with this privilege for backup/recovery operations
• Can be granted to a common user in a CDB (access to root and all PDBs)
• Can also be granted at PDB level (for local PDB administrators)

2.5 Demo: Getting started with RMAN

Goals: Connect to target database via RMAN, understand configuration settings and some basic commands.

Demo script — File 02_01-rmanbasic.txt

# Connexion à la root container database
rman
RMAN> connect target /

set echo off

# Vérification de la connexion
SELECT name, dbid FROM V$containers;
exit;

# Connexion directe
rman target /
exit;

# Connexion avec Net Service Name
rman target sys/oracle@orclcdb
exit;

# Connexion sans mot de passe (RMAN demande)
rman target sys@orclcdb
exit;

# Connexion à une PDB
rman target sys/oracle@orclpdb
SELECT name, dbid FROM V$containers;
exit;

# Connexion à la root + affichage de la configuration
rman target /
show all;

# Changer le répertoire de sauvegarde (le répertoire doit exister)
configure channel device type disk format '/u01/backup/%U';
show all;

# Changer le type de périphérique par défaut
configure default device type to sbt;
show all;

# Remettre les configurations par défaut
configure channel device type disk clear;
configure default device type clear;
show all;

# Sauvegardes diverses
backup database plus archivelog;
list backup;

backup database root;
backup pluggable database orclpdb;
exit;

# Connexion à une PDB spécifiquement
rman target sys/oracle@orclpdb

backup database plus archivelog;
list backup;

backup database tag 'orclbkup' plus archivelog;
list backup;

BACKUP DATAFILE 9,10;
BACKUP TABLESPACE users, system;
exit;

2.6 Recovery Catalog

A Recovery Catalog is a dedicated schema in a separate Oracle database that stores metadata about RMAN backups and target databases.

Benefits of Recovery Catalog

• If the primary database fails, it is still possible to connect to the recovery catalog to obtain backup information
• Centralized repository: can store metadata for multiple databases, simplifying administration
• Longer history than the control file (useful if a recovery needs to go back further in time than the control file history)
• Redundancy: if the target control file and all backups are lost, the RMAN metadata still exists in the recovery catalog
• Required in a Data Guard environment
• Ability to store RMAN scripts in the recovery catalog, available to any RMAN client that can connect to the target database and the catalog

Steps for creating the Recovery Catalog

1. Create the catalog owner (typically a new user with their own tablespace)
2. Create the catalog in this schema (CREATE CATALOG)
3. Register target databases (REGISTER DATABASE)

Warning: For production, always use a separate database for the recovery catalog. For demos, it is created in the same database.

2.7 Demo: Create and connect to Recovery Catalog

Demo script — File 02_02-recoverycatalog.txt

-- Connexion à la PDB orclpdb
sqlplus sys/oracle@orclpdb as sysdba

-- Créer le tablespace du catalog
-- (Oracle Managed Files actif, pas besoin de spécifier le chemin du datafile)
create tablespace recoverytbs;

-- Alternative si OMF non activé :
-- create tablespace recoverytbs datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/recv.dbf'
-- SIZE 10M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Créer l'utilisateur du catalog
create user rman identified by oracle
  temporary tablespace temp
  default tablespace recoverytbs
  quota unlimited on recoverytbs;

grant recovery_catalog_owner to rman;
quit;

# Créer le catalog dans le schéma rman
rman catalog rman/oracle@orclpdb
RMAN> create catalog;
RMAN> exit;

# Connecter RMAN à la base cible ET au catalog
rman target / catalog rman/oracle@orclpdb

# Enregistrer la base de données dans le catalog
RMAN> register database;

# Voir le contenu du recovery catalog
RMAN> report schema;
RMAN> show all;
RMAN> exit;

After executing REPORT SCHEMA, RMAN displays the metadata of the target database (tablespaces and data files). The SHOW ALL command displays the configuration parameters synchronized with the control file.

2.8 Full and incremental backups with RMAN

Full Backup

A full backup of a data file includes all used blocks of the data file. It can be an Image Copy or a Backup Set.

Incremental Backup

An incremental backup copies only blocks in the data file that have changed between backups.

Advantages:

• Reduced save time
• Network Bandwidth Saving
• Improved overall backup performance

Incremental backup levels

Level 1 Types:

• Differential: (default) Only includes blocks changed since the most recent Level 0 or Level 1 backup
• Cumulative: Includes all blocks changed since the most recent Level 0 backup

Constraints:

• Level 0 incremental backups can be Backup Sets or Image Copies
• Level 1 incremental backups can only be Backup Sets

Incremental Strategy Example

Dimanche 01:00 — Level 0 (tous les blocs utilisés)
Lundi    01:00 — Level 1 différentiel (blocs changés depuis dimanche)
Mardi    01:00 — Level 1 différentiel (blocs changés depuis lundi)
Mercredi 01:00 — Level 1 cumulatif  (blocs changés depuis dimanche)

2.9 Demo: Full and Incremental Backups

Demo script — File 02_03-rmanfullincremental.txt

rman target / catalog rman/oracle@orclpdb

# Sauvegarde complète avec archive logs
backup database plus archivelog;

# Sauvegarde incrémentielle Level 0
backup incremental level 0 database tag 'inc_0' plus archivelog;

# Sauvegarde incrémentielle Level 1 différentielle
backup incremental level 1 database tag 'inc_1' plus archivelog;

# Sauvegarde incrémentielle Level 1 cumulative
backup incremental level 1 cumulative database tag 'inc_cum_1' plus archivelog;

# Sauvegarde complète d'une PDB
backup pluggable database devpdb tag 'devpdb_full' plus archivelog;

# Sauvegarde Level 0 d'une PDB
backup incremental level 0 pluggable database devpdb tag 'devpdb_inc_0' plus archivelog;

# Sauvegarde Level 1 différentielle d'une PDB
backup incremental level 1 pluggable database devpdb tag 'devpdb_inc_1' plus archivelog;

# Sauvegarde Level 1 cumulative d'une PDB
backup incremental level 1 cumulative pluggable database devpdb tag 'devpdb_cum_1' plus archivelog;

exit;

Note: With Oracle Managed Files (OMF) enabled, the directory and file names are automatically generated. The control file and the SPFILE are backed up automatically (auto backup enabled).

2.10 Demo: List backups

Purpose: Different ways to get information about previous backups.

rman target / catalog rman/oracle@orclpdb

# Informations complètes sur toutes les sauvegardes (CDB + PDBs depuis la root)
LIST BACKUP;

# Informations sur les data files uniquement (pas les archivelogs, control, SPFILE)
LIST BACKUP OF DATABASE;

# Sauvegardes d'une PDB spécifique
LIST BACKUP OF DATABASE devpdb;

# Sauvegardes de data files spécifiques
LIST BACKUP OF DATAFILE 15, 16;

# Informations sur tous les archive logs
LIST ARCHIVELOG ALL;

# Sauvegardes des archive logs
LIST BACKUP OF ARCHIVELOG ALL;

# Sauvegarde du control file
LIST BACKUP OF CONTROLFILE;

# Sauvegardes par tag
LIST BACKUP TAG 'devpdb_full';

2.11 Demo: Back up control files and archived redo logs

Demo script — File 02_05-archivelogcontrol.txt

# Voir les archivelogs dans le fast recovery area
cd /u03/app/oracle/fast_recovery_area/PSDB_727_SJC/archivelog/2025_04_02
ls -ltr

sqlplus / as sysdba

# Générer un nouveau archivelog file
alter system switch logfile;

# Vérifier le nouveau fichier archivelog
quit;

rman target / catalog rman/oracle@orclpdb

# Sauvegarder tous les archivelogs
backup archivelog all;

# Vérifier les sauvegardes d'archivelogs
list backup of archivelog all;

# Afficher la configuration RMAN
show all;

# Activer la sauvegarde automatique du control file et SPFILE
CONFIGURE CONTROLFILE AUTOBACKUP ON;

# Sauvegarder manuellement le control file
BACKUP CURRENT CONTROLFILE;

# Depuis RMAN, basculer vers le terminal OS
host;
mkdir /u01/backups
exit;

# Sauvegarder le control file à un emplacement spécifique
# %U = nom unique
BACKUP CURRENT CONTROLFILE FORMAT '/u01/backups/%U';

# Vérifier les sauvegardes du control file
list backup of controlfile;

exit;

2.12 Demo: Validate backups

Purpose: Use RMAN commands to validate files and backups, ensuring their integrity and readiness for restoration.

Demo script — File 02_06-rmanvalidate.txt

rman target / catalog rman/oracle@orclpdb

# Vérification de la corruption physique
# (quand la base de données ne peut pas reconnaître le bloc)
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
# Les corruptions de blocs sont visibles dans V$DATABASE_BLOCK_CORRUPTION

# Vérification de la corruption logique
# (quand la base peut voir le bloc mais le contenu est logiquement incohérent)
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

# Valider les fichiers de sauvegarde sans effectuer la restauration
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

# Valider un backup set spécifique
LIST BACKUP;
VALIDATE BACKUPSET 1619;

# Valider des objets de base de données spécifiques
VALIDATE DATAFILE 1;
VALIDATE TABLESPACE users;

# Valider tous les data files, control files et SPFILE
VALIDATE DATABASE;

# Inclure les vérifications de corruption logique
VALIDATE CHECK LOGICAL DATABASE;

# Valider les data files et control files d'une PDB
VALIDATE PLUGGABLE DATABASE devpdb;

exit;

Note: The RESTORE VALIDATE command simulates the restore process without actually performing it. The absence of an RMAN error stack confirms that the backups can be used successfully during a real restore.

2.13 Demo: Delete backups

Purpose: Delete backups to manage storage space.

Demo script — File 02_07-rmandelete.txt

rman target / catalog rman/oracle@orclpdb

# Lister les sauvegardes de la PDB
list backup of database devpdb;

# Supprimer un backup set spécifique (avec confirmation)
delete backupset <backupset>;

# Supprimer sans confirmation (utile dans les scripts)
delete noprompt backupset <backupset>;

# Supprimer les sauvegardes d'un tablespace
delete noprompt backup of tablespace users;

# Supprimer par tag
delete noprompt backup tag 'devpdb_full';

# Supprimer toutes les sauvegardes du control file
delete noprompt backup of controlfile;

# Supprimer toutes les sauvegardes d'archivelogs
delete noprompt backup of archivelog all;

# Supprimer les sauvegardes d'une PDB
delete noprompt backup of pluggable database devpdb;

# Supprimer toutes les sauvegardes de la base
delete noprompt backup of database;

# Supprimer les archivelogs déjà sauvegardés (au moins 1 fois sur disque)
delete noprompt archivelog like '%' backed up 1 times to device type disk;

# Sauvegarder les archivelogs et les supprimer en même temps
backup archivelog all delete all input;

# Supprimer toutes les sauvegardes de la base cible
delete noprompt backup;

exit;

2.14 Obsolete Backups

An obsolete backup is a backup of a data file, control file or archived redo log that is no longer necessary for database recovery according to the configured retention policy.

Retention policies

-- Politique basée sur une fenêtre de récupération (recovery window)
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
-- Les sauvegardes de plus de 3 jours seraient considérées obsolètes

-- Politique basée sur la redondance
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
-- Si on a plus d'une sauvegarde, l'ancienne serait obsolète

Obsolete backup management commands

# Identifier les sauvegardes obsolètes
REPORT OBSOLETE;

# Supprimer les sauvegardes obsolètes
DELETE OBSOLETE;

Note: If an FRA is configured, the database automatically deletes obsolete or tape-backed files when more recovery space is needed for new files.

Demo script — File 02_08-rmanobsolete.txt

rman target / catalog rman/oracle@orclpdb

# Vérifier la politique de rétention actuelle
show all;

# Définir la politique à redondance 1
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

# Vérifier les sauvegardes obsolètes
report obsolete;

# Créer deux sauvegardes successives pour générer de l'obsolescence
backup incremental level 0 pluggable database devpdb plus archivelog;
backup incremental level 0 pluggable database devpdb plus archivelog;

# La première sauvegarde devient obsolète
report obsolete;

# Supprimer la sauvegarde obsolète
delete obsolete;

exit;

2.15 Expired Backups

Expired backups are backup pieces or sets saved in the RMAN repository but not found in their designated backup location.

Common causes:

• Backup files were deleted from the file system without updating the RMAN catalog
• Save files have been moved to another location
• Storage containing save files is temporarily unavailable

Best practice: If you need to delete a backup piece or backup set, always do it via the RMAN interface, otherwise RMAN will have no knowledge of it.

Demo script — File 02_09-expired.txt

rman target / catalog rman/oracle@orclpdb

# Vérifier l'état de toutes les sauvegardes
crosscheck backup;
# RMAN signale que toutes les backup pieces sont disponibles

# Afficher les sauvegardes expirées
list expired backup;
# Rien trouvé

list backup;
quit;

# Supprimer manuellement un backup piece (SANS RMAN = mauvaise pratique démontrée)
rm <BACKUP_PIECE_LOCATION>

rman target / catalog rman/oracle@orclpdb

# Relancer le crosscheck — RMAN signale maintenant la pièce comme expirée
crosscheck backup;

# Voir les sauvegardes expirées
list expired backup;

# Supprimer l'enregistrement de la sauvegarde expirée du référentiel RMAN
delete expired backup;

exit;

2.16 RMAN Scripts

An RMAN script is a collection of RMAN commands used to automate backups and provide consistency.

RUN block

The RUN block allows you to group RMAN commands to execute them in groups, with the possibility of overriding the configuration parameters:

RUN {
    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/backups/%d_%T_%U';
    BACKUP ARCHIVELOG ALL DELETE INPUT;
}

Command File Scripts

• Plain text files containing RMAN commands
• Stored on file system
• Typical extension: .rman
• Executed from RMAN prompt with @ symbol

# Exécution depuis l'invite RMAN
@wkly_bkup.rman

# Exécution depuis le terminal
rman target / catalog rman/oracle@orclpdb @wkly_bkup.rman

Scripts stored in the Recovery Catalog

• Can be local (for a single database) or global (shared between several databases using the same catalog)
• Created with the CREATE SCRIPT command
• Executed with RUN { EXECUTE SCRIPT script_name; }

-- Créer un script global dans le catalog
CREATE GLOBAL SCRIPT custom_backup {
    BACKUP ARCHIVELOG ALL DELETE INPUT;
}

-- Script local (sans le mot-clé GLOBAL)
CREATE SCRIPT local_backup {
    BACKUP DATAFILE &1;
}

-- Lister les scripts
LIST SCRIPT NAMES;

-- Afficher le contenu d'un script
PRINT SCRIPT custom_backup;

-- Exécuter un script
RUN { EXECUTE SCRIPT custom_backup; }

2.17 Demo: RMAN Scripts

Demo script — File 02_10-rmanscript.txt

rman target / catalog rman/oracle@orclpdb

# Nettoyage préalable
delete noprompt backup of archivelog all;

ALTER SYSTEM SWITCH LOGFILE;

list archivelog all;
quit;

# Créer et exécuter un script en ligne avec le bloc RUN
rman target / catalog rman/oracle@orclpdb
set echo off;

RUN {
    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/backups/%d_%T_%U';
    BACKUP ARCHIVELOG ALL DELETE INPUT;
}

list archivelog all;
list backup of archivelog all;

# Créer un command file script
mkdir /u01/backups/scripts
cd /u01/backups/scripts

# Créer le fichier arc_bkup.rman
# Contenu du fichier :
# RUN {
#     CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/backups/%d_%T_%U';
#     BACKUP ARCHIVELOG ALL DELETE INPUT;
# }

# Exécuter le script depuis la ligne de commande
rman target / catalog rman/oracle@orclpdb @arc_bkup.rman

# Créer un shell script pour automatisation
# Contenu de arc_bkup.sh :
# #!/bin/bash
# rman target / catalog rman/oracle@orclpdb @arc_bkup.rman

chmod +x arc_bkup.sh
./arc_bkup.sh

# Créer un script dans le recovery catalog
rman target / catalog rman/oracle@orclpdb
set echo off;

CREATE GLOBAL SCRIPT arc_bkup {
    backup archivelog all delete input;
}

# Lister les scripts
LIST SCRIPT NAMES;

# Afficher le contenu du script
PRINT SCRIPT arc_bkup;

# Exécuter le script
RUN {
    EXECUTE SCRIPT arc_bkup;
}

exit;

2.18 Demo: RMAN Scripts with Parameters

RMAN scripts can accept parameters with the notation &1, &2, etc.

Demo script — File 02_11-rmanscript_param.txt

rman target / catalog rman/oracle@orclpdb

set echo off;

# Nettoyage
delete noprompt backup;

# Script interactif avec paramètre — RMAN demandera la valeur de &1
RUN {
    backup datafile &1;
}
# Entrer par exemple : 7

exit;

# Créer un command file script avec paramètre
cd /u01/backups/scripts

# Créer bkup_datafile.rman :
# RUN {
#     backup datafile &1;
# }

# Exécuter avec le paramètre 7
rman target / catalog rman/oracle@orclpdb @bkup_datafile.rman 7
# Si le paramètre n'est pas fourni, RMAN le demandera interactivement

# Créer un shell script automatisé avec paramètre
# Contenu de bkup_datafile.sh :
# #!/bin/bash
# rman target / catalog rman/oracle@orclpdb @bkup_datafile.rman $1

chmod +x bkup_datafile.sh
./bkup_datafile.sh 7

# Script stocké dans le catalog avec paramètre
rman target / catalog rman/oracle@orclpdb

CREATE SCRIPT bkup_datafilescript {
    backup datafile &1;
}

list script names;

# Exécuter le script stocké (RMAN demandera le numéro de fichier)
RUN {
    EXECUTE SCRIPT bkup_datafilescript;
}

exit;

# Shell script pour le script stocké
# Contenu de bkup_datafilescript.sh :
# #!/bin/bash
# rman target / catalog rman/oracle@orclpdb @bkup_datafilescript.rman $1

chmod +x bkup_datafilescript.sh
./bkup_datafilescript.sh 7

3. Perform database recovery operations

3.1 Complete and incomplete recovery

Complete Recovery

According to Oracle, full recovery consists of recovering a database to the most recent point in time without loss of committed transactions.

• Typically caused by media failure or accidental deletion
• Does not require opening the database with the RESETLOGS option
• Objective: return the database to normal operation by restoring damaged files from RMAN backups and recovering all changes by applying redo logs up to the last committed transaction

Incomplete Recovery (Point-in-time Recovery)

Incomplete recovery involves recovering a database to a specific point in time, SCN, or log sequence number.

Example:

• Backup at 01:00
• Normal day until 5:00 p.m.
• A user accidentally deletes an important table at 3:00 p.m.
• Solution: restore the 01:00 backup and apply redo logs until just before 15:00

Consequences:

• Loss of transactions occurred after recovery point
• Required to open the database with the RESETLOGS option to synchronize the SCNs between the data files and the control file

3.2 Recovery with RMAN

Components recoverable by RMAN

• Individual data files and tablespaces
• Specific tables or table partitions
• Complete database (all its components)
• One or more PDBs, the root, or the entire CDB

General recovery steps with RMAN

1. Put the base in the correct state:

• CDB recovery → MOUNTED state
• PDB Recovery → PDB must be closed
• Recovery tablespaces/data files → must be offline

2. Restore backup files (RESTORE)

3. Recover by applying redo logs (RECOVER)

4. Open database:

• Complete recovery → ALTER DATABASE OPEN
• PITR Recovery → ALTER DATABASE OPEN RESETLOGS

Syntax for full recovery

# Étapes de récupération complète
RUN {
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN;
    ALTER PLUGGABLE DATABASE ALL OPEN;
}

Syntax for point-in-time recovery (PITR)

# Récupération jusqu'à un timestamp
RUN {
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    SET UNTIL TIME "TO_DATE('02-APR-2025 04:44:09 AM','DD-MON-YYYY HH:MI:SS AM')";
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS;
    ALTER PLUGGABLE DATABASE ALL OPEN;
}

# Récupération jusqu'à un SCN
RUN {
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    SET UNTIL SCN 3486213;
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS;
    ALTER PLUGGABLE DATABASE ALL OPEN;
}

3.3 Demo: Complete recovery of a PDB — loss of USERS datafile

Scenario: Complete recovery of the PDB DEVPDB following the loss of the USERS data file.

Demo script — File 03-01-full_recovery_pdb.txt

-- Connexion et vérification préalable
sqlplus / as sysdba
alter session set container=devpdb;
select * from demo.demotable;
quit;

# Prise d'une sauvegarde Level 0 de préparation
rman target / catalog rman/oracle@orclpdb

delete noprompt backup;
backup incremental level 0 database tag 'level0' plus archivelog;
list backup of database devpdb;
quit;

# Simulation de la panne : suppression du users datafile
rm <USERS_DATAFILE_LOCATION>;

-- Vérification de l'erreur
sqlplus / as sysdba
alter system flush buffer_cache;   -- Forcer Oracle à aller chercher dans les data files
alter session set container=devpdb;
select * from demo.demotable;
-- Erreur : ORA-01116 - impossible d'ouvrir le data file

-- Localiser le log d'alerte
SELECT * FROM V$DIAG_INFO WHERE NAME = 'Diag Alert';
quit;

# Diagnostiquer avec RMAN
rman target / catalog rman/oracle@orclpdb

set echo off;
validate database;    -- Signale l'erreur RMAN-06169

report schema;        -- Identifier le nom du fichier pour le numéro de fichier en erreur

-- Requête diagnostic
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES'
OR (RECOVER IS NULL AND ERROR IS NOT NULL);
exit;

-- Prendre le data file hors ligne (car impossible de fermer la PDB directement)
sqlplus / as sysdba
alter pluggable database devpdb close;
-- Erreur : ORA-01110 — data file manquant

alter session set container=devpdb;
ALTER DATABASE DATAFILE 16 OFFLINE;

alter session set container=cdb$root;
alter pluggable database devpdb close;
quit;

# Récupération avec RMAN
rman target / catalog rman/oracle@orclpdb

set echo off;

RUN {
    RESTORE PLUGGABLE DATABASE devpdb;
    RECOVER PLUGGABLE DATABASE devpdb;
    sql 'DEVPDB' 'alter database datafile 16 online';
    ALTER PLUGGABLE DATABASE devpdb open;
}
quit;

-- Vérification
sqlplus demo/demo@devpdb
SELECT * FROM demotable;
quit;

3.4 Demo: Complete recovery of a PDB — loss of the SYSTEM datafile

Scenario: Recovery following loss of the PDB SYSTEM data file. The steps differ because it is impossible to take the SYSTEM data file offline.

Demo script — File 03-02-full_recovery_pdb.txt

rman target / catalog rman/oracle@orclpdb
list backup of database devpdb;
quit;

# Simulation de la panne : suppression du data file SYSTEM de la PDB
rm <SYSTEMS_DATAFILE_LOCATION>;

# Diagnostiquer avec RMAN
rman target / catalog rman/oracle@orclpdb

validate database;
report schema;
set echo off;

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES'
OR (RECOVER IS NULL AND ERROR IS NOT NULL);
exit;

-- Tentatives de fermeture (toutes échouent)
sqlplus / as sysdba
alter pluggable database devpdb close;
-- Erreur : ORA-01110

alter session set container=devpdb;
ALTER DATABASE DATAFILE 13 OFFLINE;
-- Erreur : ORA-01541 — le tablespace SYSTEM ne peut pas être mis hors ligne

alter session set container=cdb$root;
alter pluggable database devpdb close;
-- Toujours une erreur : ORA-01116

Shutdown immediate;
-- Échec également : ORA-01116
quit;

# Récupération via RMAN
# NE PAS utiliser le catalog car la base principale est arrêtée

rman target /

set echo off;

RUN {
    shutdown abort;
    startup mount;
    RESTORE PLUGGABLE DATABASE devpdb;
    RECOVER PLUGGABLE DATABASE devpdb;
    ALTER DATABASE OPEN;
    ALTER PLUGGABLE DATABASE ALL OPEN;
}
quit;

-- Vérification
sqlplus demo/demo@devpdb
SELECT * FROM demotable;
quit;

3.5 Demo: Point-in-time Recovery of a PDB

Scenario: Recovery of a PDB following the accidental deletion of a table.

Demo script — File 03-03-pdb-pitr.txt

-- Capturer l'heure avant la suppression
sqlplus demo/demo@devpdb

SELECT * FROM demotable;

-- Capturer l'heure actuelle
select TO_CHAR(sysdate, 'DD-MON-YYYY HH:MI:SS AM') FROM dual;
-- Exemple : 02-APR-2025 04:44:09 AM

-- Simulation : suppression accidentelle de la table
DROP TABLE demotable;
SELECT * FROM demotable;   -- Erreur attendue
exit;

# Récupération PITR avec RMAN
rman target / catalog rman/oracle@orclpdb

RUN {
    ALTER PLUGGABLE DATABASE devpdb CLOSE;
    SET UNTIL TIME "TO_DATE('02-APR-2025 04:44:09 AM','DD-MON-YYYY HH:MI:SS AM')";
    RESTORE PLUGGABLE DATABASE devpdb;
    RECOVER PLUGGABLE DATABASE devpdb;
    ALTER PLUGGABLE DATABASE devpdb OPEN RESETLOGS;
}
exit;

-- Vérification
sqlplus demo/demo@devpdb
SELECT * FROM demotable;   -- La table et les données sont restaurées
quit;

3.6 Demo: Full CBD Recovery

Scenario: Media recovery of a CDB following the loss of a SYSTEM data file.

Demo script — File 03-04-cdbfulldeletesystem01.dbf.txt

rman target / catalog rman/oracle@orclpdb
list backup of database root;
exit;

# Suppression du data file SYSTEM de la CDB
rm <system_data_file>

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;   -- Échoue
exit;

# Récupération avec RMAN (sans catalog car la base est arrêtée)
rman target /

RUN {
    STARTUP MOUNT;
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN;
    ALTER PLUGGABLE DATABASE ALL OPEN;
}
exit;

-- Vérification
sqlplus / as sysdba
select instance_name, status from v$instance;
-- Affiche la CDB en état OPEN
quit;

3.7 Demo: Point-in-time Recovery of a CDB

Scenario: Point-in-time recovery of an entire CDB (affects all PDBs and root).

Demo script — File 03-05 cdbpitr.txt

-- Capturer le SCN avant la suppression
sqlplus / as sysdba
SELECT current_scn FROM v$database;
-- Exemple : 3486213

alter session set container=devpdb;
SELECT * FROM demo.demotable;
DROP TABLE demo.demotable;   -- Simulation de suppression
quit;

# Récupération PITR de la CDB avec RMAN (sans catalog)
rman target /

set echo off;

RUN {
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    SET UNTIL SCN 3486213;
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS;
    ALTER PLUGGABLE DATABASE ALL OPEN;
}

# Voir les incarnations de la base
list incarnation of database;
exit;

-- Vérification
sqlplus / as sysdba
alter session set container=devpdb;
SELECT * FROM demo.demotable;   -- Données restaurées
exit;

# Prendre une sauvegarde Level 0 après RESETLOGS (bonne pratique)
rman target / catalog rman/oracle@orclpdb
backup incremental level 0 database tag 'inc_0' plus archivelog;
quit;

Note on incarnations: Opening with RESETLOGS creates a new incarnation of the database. The LIST INCARNATION OF DATABASE command displays the current and parent incarnations with their SCN information.

3.8 Demo: Recovery following the loss of a control file

Scenario: Two recovery methods after the loss of a control file.

Demo script — File 03-06-controlfile.txt

-- Vérifier les control files multiplex
sqlplus / as sysdba
show parameter control_files;
quit;

-- Suppression d'un control file
rm <location_of_your_control_file>

Method 1: Use an available multiplexed control file

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;   -- Erreur en raison du control file manquant
SHUTDOWN ABORT;
exit;

-- Copier le control file depuis l'autre emplacement multiplexé
cp /u03/app/oracle/fast_recovery_area/PSDB_DZW_SJC/control02.ctl \
   /u02/app/oracle/oradata/PSDB_dzw_sjc/control01.ctl

sqlplus / as sysdba
STARTUP;
ALTER PLUGGABLE DATABASE ALL OPEN;
-- La base peut maintenant s'ouvrir normalement
exit;

Method 2: Restore the control file from backup

-- Supprimer à nouveau le control file pour la démo de la méthode 2
rm /u02/app/oracle/oradata/PSDB_dzw_sjc/control01.ctl

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;   -- Échoue
SHUTDOWN ABORT;
STARTUP NOMOUNT;
quit;

# Restaurer le control file (sans catalog car la base est arrêtée)
rman target /

restore controlfile from autobackup;
ALTER DATABASE MOUNT;

ALTER DATABASE OPEN;            -- Échouera — RESETLOGS requis après restauration de control file
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
ALTER PLUGGABLE DATABASE ALL OPEN;

3.9 Block Media Recovery

Block Media Recovery (BMR) is used to recover one or more corrupted data blocks in a data file, without having to take the entire data file offline.

Benefits of Block Media Recovery

• Reduces MTTR (Mean Time To Recover): only blocks requiring recovery are restored
• Affected data files may remain online during recovery
• Without BMR, if even a single block is corrupted, the entire data file must be taken offline
• Only specific blocks being retrieved are unavailable

Prerequisites

• The database must be in ARCHIVELOG mode
• The base must be mounted or opened with a current control file
• The backups of the data file containing the blocks to be corrected must be full or Level 0 backups
• RMAN can only use archived redo logs for recovery

Use cases

BMR is most useful for:

• Physical corruption issues involving a known small number of blocks
• Random and intermittent I/O errors that do not cause widespread data loss
• Memory corruptions written to disk

Limitation: BMR is not intended for cases where the extent of data loss or corruption is unknown and the entire data file requires recovery. In these cases, media recovery of the data file is the best solution.

3.10 Demo: Block Media Recovery

Demo script — File 03-07-BlockCorruption.txt

# Préparer une sauvegarde Level 0 (requise pour le BMR)
rman target / catalog rman/oracle@orclpdb

delete noprompt backup;
backup incremental level 0 database tag 'inc_0' plus archivelog;
exit;

-- Identifier le file_id et block_id de la table
sqlplus sys/oracle@devpdb as sysdba

SELECT file_id, block_id FROM dba_extents
WHERE segment_name = 'DEMOTABLE'
AND owner = 'DEMO';
-- Exemple : file_id = 16, block_id = 128

-- Obtenir le chemin du data file
SELECT file_name FROM dba_data_files
WHERE file_id = 16;

exit;

# Corruption manuelle d'un bloc (UNIQUEMENT en environnement de démo !)
dd if=/dev/urandom \
   of=/u02/app/oracle/oradata/PSDB_dzw_sjc/PSDB_DZW_SJC/31D06CBACFAA55FFE0639C00000A782E/datafile/o1_mf_users_myv17sst_.dbf \
   bs=8192 count=1 seek=128 conv=notrunc

# Détection et récupération avec RMAN
rman target / catalog rman/oracle@orclpdb

validate database;

-- Identifier les blocs corrompus
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

-- Récupération du bloc spécifique (base de données reste EN LIGNE)
RECOVER DATAFILE 16 BLOCK 128;

-- Vérification : plus de corruption
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

quit;

-- Vérification finale
sqlplus sys/oracle@devpdb as sysdba
SELECT * FROM demo.demotable;   -- Les données sont accessibles
exit;

3.11 User Managed Backup and Recovery

When RMAN is not available, it is possible to perform manual backup and recovery.

User Managed backup procedure

-- Connexion à la PDB cible
sqlplus sys/oracle@mypdb as sysdba

-- Obtenir les noms et emplacements des data files
SELECT NAME FROM V$DATAFILE;

-- Mettre la base en mode hot backup
ALTER DATABASE BEGIN BACKUP;

-- Copier les data files (via OS ou snapshots de stockage)
-- cp /path/to/datafiles/* /backup/location/

-- Vérifier l'état de la sauvegarde
SELECT * FROM v$backup;

-- Terminer le mode hot backup
ALTER DATABASE END BACKUP;

-- Sauvegarder le control file
ALTER DATABASE BACKUP CONTROLFILE TO '/u01/backups/control01.ctl';

-- Archiver le log courant
ALTER SYSTEM ARCHIVE LOG CURRENT;

-- Copier les archive logs vers la destination de sauvegarde
-- cp /path/to/archivelogs/* /backup/location/

-- Sauvegarder le SPFILE
CREATE PFILE='/u01/backups/init.ora' FROM SPFILE;

User Managed recovery procedure

-- Connexion à la root database
sqlplus / as sysdba

SHUTDOWN IMMEDIATE;   -- Ou SHUTDOWN ABORT si nécessaire

-- Copier les fichiers manquants depuis la sauvegarde vers leur emplacement original
-- cp /backup/location/datafile /original/location/

STARTUP MOUNT;

-- Activer la récupération automatique (pas de prompts pour chaque archivelog)
SET AUTORECOVERY ON;

RECOVER DATABASE;

ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;

3.12 Demo: Manual recovery without RMAN

Demo script — File 03-08-usermanagedrecovery.txt

-- Processus de sauvegarde
sqlplus sys/oracle@devpdb as sysdba

SELECT NAME FROM V$DATAFILE;
ALTER DATABASE BEGIN BACKUP;

SELECT * FROM v$backup;   -- Data files en status ACTIVE
exit;

-- Préparer les répertoires de sauvegarde
mkdir /u01/backups/datafile
mkdir /u01/backups/archivelogfile

-- Copier les data files
cp /u02/app/oracle/oradata/.../datafile/* /u01/backups/datafile
cd /u01/backups/datafile
ls -al

sqlplus sys/oracle@devpdb as sysdba

ALTER DATABASE END BACKUP;
SELECT * FROM v$backup;   -- Backup mode inactif
exit;

sqlplus / as sysdba

-- Sauvegarder le control file et le SPFILE
ALTER DATABASE BACKUP CONTROLFILE TO '/u01/backups/control01.ctl';
CREATE PFILE='/u01/backups/init.ora' FROM SPFILE;

-- Simuler une activité normale post-sauvegarde
alter session set container=devpdb;
SELECT * FROM demo.demotable;
insert into demo.demotable values ('post backup');
Commit;

alter session set container=cdb$root;

-- Archiver le log courant
ALTER SYSTEM ARCHIVE LOG CURRENT;

SELECT THREAD#, SEQUENCE#, NAME FROM V$ARCHIVED_LOG;
quit;

-- Copier les archive logs
cp /u03/app/oracle/fast_recovery_area/.../archivelog/2025_04_02/* /u01/backups/archivelogfile
cd /u01/backups/archivelogfile
ls -al

-- Simulation de panne : suppression du users datafile
rm <users_datafile>

sqlplus / as sysdba
alter pluggable database devpdb close;
-- Erreur ORA-01110 : data file manquant

alter session set container=devpdb;
ALTER DATABASE DATAFILE 16 OFFLINE;

alter session set container=cdb$root;
alter pluggable database devpdb close;
quit;

-- Processus de restauration
-- Restaurer les data files depuis la sauvegarde
cp /u01/backups/datafile/* /u02/app/oracle/oradata/.../datafile/

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

alter session set container=devpdb;
ALTER DATABASE DATAFILE 16 ONLINE;

alter session set container=cdb$root;
SET AUTORECOVERY ON;
RECOVER DATABASE;

ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;
exit;

-- Vérification — la ligne 'post backup' est présente = récupération complète
sqlplus demo/demo@devpdb
select * from demotable;
exit;

4. Implement Flashback Technology for Error Recovery

4.1 Flashback Technologies

Oracle Flashback Technology is like a time machine for your database. It’s a revolutionary feature set that allows:

• View past states of data
• Fix errors
• Move data forward and backward in time without restoration from backup or long point-in-time media recovery

Overview of Flashback Technologies

4.2 Prerequisites for Flashback

Common prerequisites (Flashback Query, Version Query, Table)

These features use undo data:

-- Vérifier que la gestion undo automatique est activée
SHOW PARAMETER undo_management;    -- Doit être AUTO

-- Vérifier le tablespace undo
SHOW PARAMETER undo_tablespace;

-- Configurer la rétention undo (en secondes)
-- Plus la valeur est élevée, plus on peut flashback loin dans le temps,
-- mais plus l'espace de stockage requis est important
ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=BOTH;   -- 1 heure

Additional prerequisites for Flashback Table

-- Activer le mouvement de lignes sur la table
ALTER TABLE demo ENABLE ROW MOVEMENT;

Prerequisites for Flashback Transaction Query

-- Activer le minimal supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Vérification
SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN,
       SUPPLEMENTAL_LOG_DATA_PK PK,
       SUPPLEMENTAL_LOG_DATA_UI UI,
       SUPPLEMENTAL_LOG_DATA_ALL ALL_LOG
FROM V$DATABASE;
-- MIN doit être YES

Note: If activated at the CDB level, supplemental logging is activated for all PDBs.

Prerequisites for Flashback Drop

-- Vérifier que le recycle bin est activé (activé par défaut)
SHOW PARAMETER recyclebin;   -- Doit être ON

-- Activer si nécessaire
ALTER SESSION SET recyclebin=ON;
-- ou au niveau système
ALTER SYSTEM SET recyclebin=ON SCOPE=BOTH;

Prerequisites for Flashback Database

-- Vérifier la FRA
SHOW PARAMETER db_recovery_file_dest;

-- Vérifier la cible de rétention Flashback (en minutes)
SHOW PARAMETER db_flashback_retention_target;

-- Vérifier le mode ARCHIVELOG
ARCHIVE LOG LIST;

-- Vérifier que Flashback logging est activé
SELECT FLASHBACK_ON FROM V$DATABASE;

-- Si non configuré, procédure d'activation :
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;           -- Si pas encore en archive log mode
ALTER DATABASE FLASHBACK ON;         -- Activer le Flashback logging
ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;

4.3 Demo: Configuring prerequisites Flashback

Demo script — File 04-01-prerequisites.txt

sqlplus / as sysdba

-- Vérifier les paramètres undo
show parameter undo;

-- Configurer la rétention undo
alter system set undo_retention=3600 scope=both;
show parameter undo;

-- Vérifier/activer le supplemental logging
SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN,
       SUPPLEMENTAL_LOG_DATA_PK PK,
       SUPPLEMENTAL_LOG_DATA_UI UI,
       SUPPLEMENTAL_LOG_DATA_ALL ALL_LOG
FROM V$DATABASE;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Vérifier de nouveau
SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN,
       SUPPLEMENTAL_LOG_DATA_PK PK,
       SUPPLEMENTAL_LOG_DATA_UI UI,
       SUPPLEMENTAL_LOG_DATA_ALL ALL_LOG
FROM V$DATABASE;

exit;

-- Vérifier dans la PDB également
sqlplus system/oracle@devpdb
SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN,
       SUPPLEMENTAL_LOG_DATA_PK PK,
       SUPPLEMENTAL_LOG_DATA_UI UI,
       SUPPLEMENTAL_LOG_DATA_ALL ALL_LOG
FROM V$DATABASE;
quit;

-- Vérifier la Recyclebin
sqlplus / as sysdba
show parameter recyclebin;
exit;

-- Vérifier la Recyclebin dans la PDB
sqlplus sys/oracle@devpdb as sysdba
show parameter recyclebin;
exit;

-- Vérifier les prérequis pour Flashback Database
sqlplus / as sysdba
show parameter db_recovery_file_dest;
show parameter db_flashback_retention_target;

ARCHIVE LOG LIST;
SELECT FLASHBACK_ON FROM V$DATABASE;

-- Si nécessaire : activer le Flashback logging
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;

SELECT FLASHBACK_ON FROM V$DATABASE;
quit;

4.4 Demo: Flashback Query

Flashback Query allows you to view data as it existed at a specific point in time or SCN. Returns committed data that was current at that time.

Use cases

• Recovering lost data or undoing incorrectly made changes
• Comparing current data with data at a previous time (daily reports)
• Checking the status of transactional data on a given date
• Self-correcting errors for users

Demo script — File 04-02-flashbackQuery.txt

sqlplus system/oracle@devpdb

-- Créer une table de test
CREATE TABLE demo.employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER
);

INSERT INTO demo.employees VALUES (1, 'Tom', 50000);
INSERT INTO demo.employees VALUES (2, 'Alice', 60000);
COMMIT;

-- Capturer le timestamp actuel
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM DUAL;
-- Exemple : 2025-03-30 00:52:33

-- Effectuer des changements
UPDATE demo.employees SET salary = 55000 WHERE id = 1;
DELETE FROM demo.employees WHERE id = 2;
COMMIT;

-- Voir les valeurs courantes
SELECT * FROM demo.employees;

-- Flashback Query : voir les données AVANT les changements
SELECT * FROM demo.employees
AS OF TIMESTAMP TO_TIMESTAMP('2025-03-30 00:52:33', 'YYYY-MM-DD HH24:MI:SS');
-- Retourne les données telles qu'elles existaient avant l'UPDATE et le DELETE

quit;

4.5 Demo: Flashback Version Query

Flashback Version Query uses the VERSIONS BETWEEN clause in a SELECT to see different versions of specific rows over a time interval.

Key points:

• Can use SCNs or timestamps
• Provides the VERSIONS_XID which can be used with Flashback Transaction Query to revert changes
• Useful for change tracking, auditing and troubleshooting data modifications

Demo script — File 04-03-flashbackVersionQuery.txt

sqlplus system/oracle@devpdb

-- Capturer le timestamp de départ
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM DUAL;
-- Exemple : 2025-04-02 20:34:42

SELECT * FROM demo.employees;

-- Effectuer des changements
UPDATE demo.employees SET SALARY = 60000 WHERE id = 1;
COMMIT;
DELETE FROM demo.employees WHERE id = 1;
COMMIT;

-- Capturer le timestamp de fin
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM DUAL;
-- Exemple : 2025-04-02 20:36:32

-- Formatage de l'affichage
SET LINESIZE 300
COLUMN versions_starttime FORMAT A22 HEADING "Start Time"
COLUMN versions_endtime FORMAT A22 HEADING "End Time"
COLUMN versions_xid FORMAT A18 HEADING "Transaction ID"
COLUMN versions_operation FORMAT A2 HEADING "Operation"
COLUMN id FORMAT 9999 HEADING "ID"
COLUMN name FORMAT A5 HEADING "Name"
COLUMN salary FORMAT $99,999.99 HEADING "Salary"

-- Flashback Version Query sur l'intervalle
SELECT versions_starttime,
       versions_endtime,
       versions_xid,
       versions_operation,
       id,
       name,
       salary
FROM demo.employees
VERSIONS BETWEEN TIMESTAMP
    TO_TIMESTAMP('2025-04-02 20:34:42', 'YYYY-MM-DD HH24:MI:SS') AND
    TO_TIMESTAMP('2025-04-02 20:36:32', 'YYYY-MM-DD HH24:MI:SS');
-- Affiche la valeur initiale (55000), l'UPDATE (60000) et le DELETE

quit;

4.6 Demo: Flashback Drop

Flashback Drop specifically reverses the effect of a DROP TABLE operation.

How the Recyclebin works

When a table is deleted:

• Space is not freed immediately
• The table is renamed and placed (with its associated objects) in the Recyclebin
• Object names in Recyclebin are unique and system-generated
• A Flashback operation retrieves the table from Recyclebin

Objects retrieved with table

• All indexes defined on the table (except bitmap joined indexes)
• All triggers and constraints (except referential integrity constraints to other tables)
• Note: Indexes do not get their original names back — they keep their names generated by Recyclebin

Demo script — File 04-04-flashbackDrop.txt

sqlplus demo/demo@devpdb

SELECT * FROM employees;
INSERT INTO demo.employees VALUES (3, 'John', 60000);
COMMIT;
SELECT * FROM employees;
SELECT index_name FROM user_indexes WHERE table_name='EMPLOYEES';

-- Supprimer la table
drop table employees;

-- La table apparaît dans la Recyclebin
SET LINESIZE 300
COLUMN object_name FORMAT A35 HEADING "Recycle Name"
COLUMN original_name FORMAT A14 HEADING "Original Name"
COLUMN type FORMAT A6 HEADING "Type"
COLUMN droptime FORMAT A22 HEADING "Drop Time"
COLUMN dropscn FORMAT 99999999 HEADING "Drop SCN"

SELECT object_name, original_name, type, droptime, dropscn FROM user_recyclebin;

-- Flashback Drop : récupérer la table par son nom original
FLASHBACK TABLE employees TO BEFORE DROP;

-- Table disparue de la Recyclebin
SELECT object_name, original_name, type, droptime, dropscn FROM user_recyclebin;

SELECT * FROM employees;

-- L'index conserve son nom de la Recyclebin
SELECT index_name FROM user_indexes WHERE table_name='EMPLOYEES';

-- Renommer l'index avec son nom original
ALTER INDEX "BIN$MYXvhdO8s9zgY4IAAApAdw==$0" RENAME TO SYS_C008143;

-- Flashback par nom d'objet Recyclebin
drop table employees;
SELECT object_name, original_name, type, droptime, dropscn FROM user_recyclebin;
FLASHBACK TABLE "<object_name>" TO BEFORE DROP;

-- Gestion de plusieurs tables avec le même nom supprimées successivement
CREATE TABLE items ( id number, name varchar2(10));
INSERT INTO items VALUES (1,'first');
DROP TABLE items;

CREATE TABLE items ( id number, name varchar2(10));
INSERT INTO items VALUES (2,'second');
DROP TABLE items;

CREATE TABLE demo.items ( id number, name varchar2(10));
INSERT INTO demo.items VALUES (3,'third');
DROP TABLE items;

SELECT object_name, original_name, type, droptime, dropscn FROM user_recyclebin;

-- La dernière table supprimée avec ce nom est récupérée en premier
FLASHBACK TABLE items TO BEFORE DROP;
SELECT * FROM items;

-- Récupérer les autres avec renommage
FLASHBACK TABLE items TO BEFORE DROP RENAME TO items2;
SELECT * FROM items2;

FLASHBACK TABLE items TO BEFORE DROP RENAME TO items1;
SELECT * FROM items1;
quit;

4.7 Demo: Flashback Transaction Query

Flashback Transaction Query is a query on the flashback_transaction_query view which allows you to:

• View detailed information on past transactions
• Get transaction ID, operation type, start and commit SCNs
• Get the SQL to undo each change made by the transaction

Particularly useful for diagnosing problems, auditing changes, and potentially reversing unintended changes.

Demo script — File 04-05-flashbackTxnQuery copy.txt

sqlplus system/oracle@devpdb

CREATE TABLE demo.locations (id NUMBER, name VARCHAR2(20));

-- Capturer le timestamp
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM DUAL;
-- Exemple : 2025-03-30 01:21:58

INSERT INTO demo.locations VALUES(1,'Seattle');
COMMIT;

UPDATE demo.locations SET name='Dallas' WHERE id = 1;
COMMIT;

-- Capturer le timestamp de fin
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM DUAL;
-- Exemple : 2025-03-30 01:23:21

-- Formatage
SET LINESIZE 300
COLUMN start_scn FORMAT 99999999 HEADING "Start SCN"
COLUMN commit_scn FORMAT 99999999 HEADING "Commit SCN"
COLUMN operation FORMAT A10 HEADING "Operation"
COLUMN logon_user FORMAT A10 HEADING "Logon User"
COLUMN undo_sql FORMAT A60 HEADING "Undo SQL"

-- Flashback Transaction Query
SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
FROM flashback_transaction_query
WHERE table_owner = 'DEMO'
    AND table_name = 'LOCATIONS'
    AND start_timestamp >= TO_TIMESTAMP('2025-03-30 01:21:58','YYYY-MM-DD HH24:MI:SS');
-- Résultat : INSERT (undo = DELETE via ROWID), UPDATE (undo = UPDATE vers 'Seattle')

-- Obtenir le XID via Flashback Version Query (alternative)
SET LINESIZE 300
COLUMN versions_starttime FORMAT A22
COLUMN versions_endtime FORMAT A22
COLUMN versions_xid FORMAT A18
COLUMN versions_operation FORMAT A2
COLUMN id FORMAT 9999
COLUMN name FORMAT A10

SELECT versions_starttime,
       versions_endtime,
       versions_xid,
       versions_operation,
       id,
       name
FROM demo.locations
VERSIONS BETWEEN TIMESTAMP
    TO_TIMESTAMP('2025-03-30 01:21:58', 'YYYY-MM-DD HH24:MI:SS') AND
    TO_TIMESTAMP('2025-03-30 01:23:21', 'YYYY-MM-DD HH24:MI:SS');

-- Utiliser le XID obtenu pour trouver le SQL d'annulation
SELECT operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('070015007B030000');

quit;

4.8 Demo: Flashback Table

Flashback Table restores a table to its state at a previous point in time without performing a full database recovery.

Important Features:

• Use information in undo tablespace (not backups)
• Can be run while database remains online
• Table must have ROW MOVEMENT enabled
• Does not preserve ROWIDs
• Operation is a single transaction (all tables succeed or all tables fail)
• Table structure must not have changed during the interval

Demo script — File 04-06-flashbackTable.txt

sqlplus demo/demo@devpdb

-- Créer et populer une table de test
CREATE TABLE products (
    product_id NUMBER,
    product_name VARCHAR2(50),
    price NUMBER
);

-- Prérequis : activer ROW MOVEMENT
ALTER TABLE products ENABLE ROW MOVEMENT;

INSERT INTO products VALUES (1, 'Laptop', 1000);
INSERT INTO products VALUES (2, 'Smartphone', 800);
INSERT INTO products VALUES (3, 'Tablet', 600);
COMMIT;

-- Capturer le timestamp avant les changements
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM DUAL;
-- Exemple : 2025-03-30 01:00:31

-- Effectuer des changements
DELETE FROM products WHERE product_id = 2;
COMMIT;
UPDATE products SET price = 1200 WHERE product_id = 1;
COMMIT;

-- État actuel
SELECT * FROM products;
-- PRODUCT_ID  PRODUCT_NAME  PRICE
-- 1           Laptop        1200
-- 3           Tablet        600

-- Flashback Table avec timestamp
FLASHBACK TABLE products TO TIMESTAMP
    TO_TIMESTAMP('2025-03-30 01:00:31', 'YYYY-MM-DD HH24:MI:SS');

-- Vérification : état restauré
SELECT * FROM products;
-- PRODUCT_ID  PRODUCT_NAME  PRICE
-- 1           Laptop        1000
-- 2           Smartphone    800
-- 3           Tablet        600

-- Alternative avec SCN
-- FLASHBACK TABLE products TO SCN <desired_scn>;

quit;

4.9 Demo: Flashback Database

Flashback Database allows rewinding the entire database to a previous state without traditional backup restore. Uses flashback logs for its operation.

Important Limitations

Flashback Database cannot not be used for:

• Repair media failures (lost or damaged data files)
• Recover from accidental deletion of data files
• Cancel a data file shrink operation
• Return to a point before restoring or recreating a control file
• Cancel a compatibility change

Demo script — File 04-07-flashbackDatabase.txt

-- Capturer le SCN courant
sqlplus system/oracle@devpdb
SELECT CURRENT_SCN FROM V$DATABASE;
-- Exemple : 2683727

SELECT * FROM DEMO.EMPLOYEES;

-- Simulation : suppression d'une table et création d'un utilisateur
DROP TABLE DEMO.EMPLOYEES;

CREATE USER testusr IDENTIFIED BY testusr
    DEFAULT TABLESPACE users
    QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO testusr;
quit;

-- Vérifier le nouvel utilisateur
sqlplus testusr/testusr@devpdb
quit;

-- Flashback Database au SCN avant les changements
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN 2683727;
ALTER DATABASE OPEN RESETLOGS;
ALTER PLUGGABLE DATABASE ALL OPEN;
exit;

-- Vérification : l'utilisateur testusr n'existe plus, la table employees est restaurée
sqlplus testusr/testusr@devpdb   -- Erreur attendue (utilisateur n'existe plus)
sqlplus system/oracle@devpdb
SELECT * FROM DEMO.EMPLOYEES;
quit;

4.10 Demo: Restore Points

Restore Points are user-defined names associated with a specific point in time or SCN.

Types of Restore Points

Warning: Guaranteed Restore Points grow in space and can cause the database to stop if the FRA space is exhausted. Delete them as soon as they are no longer needed.

Demo script — File 04-08-RestorePoints.txt

sqlplus system/oracle@devpdb

SELECT * FROM demo.employees;

-- Créer un normal restore point
CREATE RESTORE POINT before_update;

SET LINE 300
COLUMN name FORMAT A20;
COLUMN time FORMAT A50;
SELECT NAME, SCN, TIME FROM V$RESTORE_POINT;

-- Effectuer une mise à jour majeure
UPDATE demo.employees SET salary = salary * 1.2;
COMMIT;
SELECT * FROM demo.employees;

ALTER TABLE demo.employees ENABLE ROW MOVEMENT;

-- Flashback Table vers le restore point
FLASHBACK TABLE demo.employees TO RESTORE POINT before_update;
SELECT * FROM demo.employees;

-- Restore point au niveau PDB
CREATE RESTORE POINT good_state;

SELECT * FROM DEMO.EMPLOYEES;
DROP TABLE DEMO.EMPLOYEES;

sqlplus / as sysdba

-- Flashback de la PDB vers le restore point
alter pluggable database devpdb close immediate;
flashback pluggable database devpdb to restore point good_state;
alter pluggable database devpdb open resetlogs;
quit;

sqlplus system/oracle@devpdb
SELECT * FROM DEMO.EMPLOYEES;   -- Table restaurée
quit;

-- Guaranteed Restore Point au niveau CDB
sqlplus / as sysdba

CREATE RESTORE POINT CDB_RESTORE_POINT GUARANTEE FLASHBACK DATABASE;

SET LINE 300
COLUMN name FORMAT A20;
COLUMN GUARANTEE_FLASHBACK_DATABASE FORMAT A10;
COLUMN time FORMAT A50;

SELECT NAME, GUARANTEE_FLASHBACK_DATABASE, SCN, TIME
FROM V$RESTORE_POINT;

alter session set container=devpdb;
select * from demo.employees;
drop table demo.employees;
alter session set container=cdb$root;

-- Flashback de la CDB vers le guaranteed restore point
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT CDB_RESTORE_POINT;
ALTER DATABASE OPEN RESETLOGS;
ALTER PLUGGABLE DATABASE ALL OPEN;
exit;

sqlplus system/oracle@devpdb
SELECT * FROM DEMO.EMPLOYEES;   -- Table restaurée
quit;

-- Supprimer le guaranteed restore point (IMPORTANT pour libérer l'espace)
sqlplus / as sysdba
DROP RESTORE POINT CDB_RESTORE_POINT;

SELECT NAME, GUARANTEE_FLASHBACK_DATABASE, SCN, TIME
FROM V$RESTORE_POINT;
quit;

4.11 Demo: Monitor Flashback Logs and Storage

Demo script — File 04-09-MonitorFlashbackLogs.txt

sqlplus / as sysdba

-- Informations sur le Fast Recovery Area
SET LINESIZE 120
COLUMN NAME FORMAT A34
COLUMN SPACE_LIMIT FORMAT 999,999,999.99 HEADING "Space Limit (GB)"
COLUMN SPACE_USED FORMAT 999,999,999.99 HEADING "Space Used (GB)"
COLUMN SPACE_RECLAIMABLE FORMAT 999,999,999.99 HEADING "Space Reclaimable (GB)"
COLUMN NUMBER_OF_FILES FORMAT 999 HEADING "Num Files"

SELECT
    NAME,
    SPACE_LIMIT / (1024 * 1024 * 1024) AS SPACE_LIMIT_GB,
    SPACE_USED / (1024 * 1024 * 1024) AS SPACE_USED_GB,
    SPACE_RECLAIMABLE / (1024 * 1024 * 1024) AS SPACE_RECLAIMABLE_GB,
    NUMBER_OF_FILES
FROM V$RECOVERY_FILE_DEST;

-- Utilisation détaillée de la FRA par type de fichier
SELECT FILE_TYPE,
       PERCENT_SPACE_USED AS PCT_USED,
       PERCENT_SPACE_RECLAIMABLE AS PCT_RECLAIM,
       NUMBER_OF_FILES AS NO_FILES
FROM V$RECOVERY_AREA_USAGE;

-- Informations sur les Flashback logs
SELECT FLASHBACK_SIZE / (1024 * 1024 * 1024) AS "Total Flashback Size(GB)",
       ESTIMATED_FLASHBACK_SIZE/(1024 * 1024 * 1024) "Estimated Flashback Size(GB)",
       OLDEST_FLASHBACK_SCN oldest_flashback_scn,
       TO_CHAR(OLDEST_FLASHBACK_TIME,'dd-mon-rr hh24:mi:ss') oldest_flashback_time
FROM V$FLASHBACK_DATABASE_LOG;

Parameter db_flashback_retention_target

• Specifies the maximum period in minutes for which Flashback data is retained
• Sets an upper limit but does not guarantee that the base can be flashbacked to that exact point (depends on available FRA space)
• If the FRA runs out of space, old Flashback logs may be deleted, limiting the flashback window

To guarantee a return to a specific point: Use Guaranteed Restore Points which ensure that the necessary Flashback logs are preserved until the restore point is explicitly deleted.

5. Monitor and troubleshoot backup and recovery operations

5.1 Importance of reporting and monitoring

Monitoring and troubleshooting are essential for tracking RMAN operations and proactively detecting, identifying, and resolving issues.

Key monitoring points

• Know which files have been backed up and which have not recently
• Regularly test backups to ensure they can be restored
• Monitor space usage and free space in backup directories
• Insufficient space can cause performance issues and in the worst case stop database operations
• Regularly reclaim space by deleting obsolete backups
• Ensure backup jobs complete successfully
• Examine the status and execution time of backup jobs

5.2 Use RMAN reports and views to monitor backups

RMAN stores backup metadata in control files or in the recovery catalog if one is used.

LIST commands available

REPORT commands available

Oracle Views for Monitoring

-- Détails des jobs de sauvegarde RMAN
SELECT SESSION_KEY ses,
       INPUT_TYPE,
       STATUS,
       START_TIME,
       END_TIME,
       ELAPSED_SECONDS/3600 HRS,
       AUTOBACKUP_DONE
FROM V$RMAN_BACKUP_JOB_DETAILS;

-- Informations sur les backup sets
SELECT RECID,
       SET_STAMP,
       SET_COUNT,
       BACKUP_TYPE type,
       CONTROLFILE_INCLUDED ctrl_incl,
       INCREMENTAL_LEVEL lvl,
       PIECES
FROM V$BACKUP_SET;

5.3 Demo: RMAN reports and views for monitoring

Demo script — File 05-01-ListAndReport.txt

rman target / catalog rman/oracle@orclpdb

LIST BACKUP;
LIST BACKUP SUMMARY;
LIST BACKUP BY FILE;
LIST DATAFILECOPY ALL;

SHOW ALL;
REPORT NEED BACKUP;
REPORT NEED BACKUP REDUNDANCY 2;
REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 17;
REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS;
REPORT NEED BACKUP TABLESPACE USERS;
REPORT NEED BACKUP INCREMENTAL 2;
REPORT UNRECOVERABLE;

sqlplus / as sysdba

COLUMN ses FORMAT 999
COLUMN STATUS FORMAT A9
COLUMN hrs FORMAT 999.99

SELECT SESSION_KEY ses,
       INPUT_TYPE,
       STATUS,
       START_TIME,
       END_TIME,
       ELAPSED_SECONDS/3600 HRS,
       AUTOBACKUP_DONE
FROM V$RMAN_BACKUP_JOB_DETAILS;

COLUMN ctrl_incl FORMAT A9
COLUMN type FORMAT A4

SELECT RECID,
       SET_STAMP,
       SET_COUNT,
       BACKUP_TYPE type,
       CONTROLFILE_INCLUDED ctrl_incl,
       INCREMENTAL_LEVEL lvl,
       PIECES
FROM V$BACKUP_SET;

5.4 Understanding RMAN Errors

RMAN Error Code Types

Principle of reading RMAN errors

Important: Read errors from bottom to top — the last lines of the stack give the root cause.

Demo script — File 05-02-rmanerror.txt

rman target / catalog rman/oracle@orclpdb

# Exemple 1 : Faute de frappe dans le nom du tablespace
backup uses;   -- Intentionnellement 'uses' au lieu de 'users'
# RMAN-03002 : backup command failed
# Erreur en bas : "No tablespace 'USES' found in the recovery catalog"

# Exemple 2 : Tentative de récupérer un data file en ligne
recover datafile 1;
# Lecture de bas en haut :
# ORA-01110 : problème avec la récupération du data file 1
# Le data file est en cours d'utilisation ou déjà en cours de récupération
# → Conclusion : le data file doit être mis hors ligne d'abord

# Exemple 3 : Chemin de destination inexistant
backup current controlfile format '/u05/backup';
# Additional Information: 9, Linux error 2: No such file or directory

5.5 Test and validate recovery scenarios

Best Testing Practices

1. Test environment: Have a test environment that is a copy of production (or as close as possible)

• Folder/file structure same as production
• Ability to take snapshots (VM or storage snapshots) to easily return to a clean state

2. List of use cases to test:

• Recovery after server crash
• Server hardware failures
• Storage media failures
• Accidental deletion of files
• Point-in-time recovery scenarios

3. Regular validation of backups:

• Use RMAN VALIDATE commands regularly
• Test restore validity with RMAN RESTORE VALIDATE

4. RTO/RPO objectives:

• Keeping Recovery Time Objective (RTO) and Recovery Point Objective (RPO) in mind
• If objectives are not achieved, modify processes or improve infrastructure

5. Documentation: Document and practice recovery procedures regularly

5.6 Data Recovery Advisor — Analysis and interpretation of recommendations

The Data Recovery Advisor is an Oracle Database tool that:

• Automatically diagnoses data failures
• Determines and presents appropriate repair options
• Performs repairs on demand

In this context, a data failure is a corruption or loss of persistent data on disk.

Available interfaces

• Command line interface (RMAN)
• graphical interface in Oracle Enterprise Manager Cloud Control

Benefits of Data Recovery Advisor

• Can detect, analyze and repair data failures before a database process discovers the corruption
• Simplifies the (often complex and error-prone manually) diagnostic process
• Automatically determines the best sequence of repair steps if multiple faults are present

Data Recovery Advisor Workflow

# 1. Détecter une panne (automatiquement ou manuellement)
validate database;
validate check logical database;

# 2. Lister les pannes détectées
LIST FAILURE;
# Chaque panne a un numéro unique et une priorité (HIGH, LOW)

# 3. Obtenir des recommandations de réparation
ADVISE FAILURE;
# Montre : actions manuelles obligatoires/optionnelles + options de réparation automatisées

# 4. Prévisualiser le script de réparation sans l'exécuter
REPAIR FAILURE PREVIEW;

# 5. Exécuter la réparation
REPAIR FAILURE;
# Data Recovery Advisor exécute les étapes du script de réparation

5.7 Demo: Media recovery with Data Recovery Advisor

Scenario: Recovering from the loss of a non-system data file with Data Recovery Advisor.

Demo script — File 05-03-datarecoveryadvisor.txt

sqlplus / as sysdba
alter session set container=devpdb;
select * from demo.demotable;
quit;

rman target / catalog rman/oracle@orclpdb
list backup of database devpdb;
quit;

# Simulation de panne : suppression du users datafile
rm <USERS_DATAFILE_LOCATION>;

sqlplus / as sysdba
alter system flush buffer_cache;
alter session set container=devpdb;
select * from demo.demotable;   -- ORA-01116 attendu

rman target / catalog rman/oracle@orclpdb

set echo off;

# Détecter la panne
validate database;
# RMAN signale : impossible d'accéder au data file 16

# Lister les pannes
List failure;
# Priorité HIGH : "one or more non-system datafiles are missing"

# Obtenir les recommandations
ADVISE FAILURE;
# Actions manuelles obligatoires : none
# Action manuelle optionnelle : si renommé/déplacé accidentellement, le restaurer
# Option de réparation automatisée : RESTORE DATAFILE 16 et RECOVER

# Voir le script sans l'exécuter
REPAIR FAILURE PREVIEW;

# Exécuter la réparation
REPAIR FAILURE;
# Confirmer : yes

exit;

-- Vérification
sqlplus demo/demo@devpdb
SELECT * FROM demotable;   -- Données accessibles
quit;

5.8 Demo: Corrupt block recovery with Data Recovery Advisor

Demo script — File 05-04-BlockCorruption.txt

sqlplus sys/oracle@devpdb as sysdba

-- Identifier la localisation du bloc
SELECT file_id, block_id FROM dba_extents
WHERE segment_name = 'DEMOTABLE'
AND owner = 'DEMO';
-- file_id = 16, block_id = 128

SELECT file_name FROM dba_data_files
WHERE file_id = 16;
exit;

# Corruption manuelle d'un bloc (UNIQUEMENT en démo !)
dd if=/dev/urandom \
   of=<users_datafile_path> \
   bs=8192 count=1 seek=128 conv=notrunc

rman target / catalog rman/oracle@orclpdb

validate database;
# Signale un bloc en échec dans le users data file

SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
# Affiche file 16, block 128 comme corrompu

List failure;
# Data Recovery Advisor : "data file 16 contains one or more corrupt blocks"

ADVISE FAILURE;
# Option automatisée : perform block recovery of block 128 file 16

REPAIR FAILURE PREVIEW;
# Montre : RECOVER DATAFILE 16 BLOCK 128;

REPAIR FAILURE;
# Confirmer : yes — récupération complète

quit;

-- Vérification
sqlplus demo/demo@devpdb
SELECT * FROM demotable;   -- Données accessibles
exit;

5.9 Optimizing backups: Incremental backups and parallelism

Block Change Tracking

Block Change Tracking significantly improves the performance of incremental backups by saving modified blocks in a dedicated file.

• RMAN no longer needs to scan all blocks and data files to identify changes
• Uses the tracking file to quickly identify modified blocks

-- Vérifier le paramètre DB_CREATE_FILE_DEST (requis si pas spécifié manuellement)
SHOW PARAMETER DB_CREATE_FILE_DEST;

-- Si nécessaire, configurer l'emplacement
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata';

-- Activer le Block Change Tracking
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

-- Alternative : spécifier l'emplacement du fichier de tracking
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/mydir/rman_change_track.f' REUSE;

-- Vérifier le statut
SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

RMAN parallelism

Parallelism allows RMAN to use multiple channels simultaneously for backup operations.

Automatic channel allocation:

rman target / catalog rman/oracle@orclpdb

# Configurer le parallélisme automatique
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

# Vérifier la configuration
SHOW DEVICE TYPE;
SHOW ALL;

# Les sauvegardes utiliseront automatiquement 2 canaux (ORA_DISK_1 et ORA_DISK_2)
BACKUP DATAFILE 1, 6, 7;

Manual channel allocation:

RUN {
    ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u01/backups/%U';
    ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/u02/backups/%U';
    BACKUP PLUGGABLE DATABASE devpdb PLUS ARCHIVELOG;
}

Recommended optimization strategy

• Take a Level 0 save once or twice a week
• Take incremental backups for the rest of the business days
• Enable Block Change Tracking to reduce incremental backup time

5.10 Demo: Block Change Tracking and parallelism

Demo script — File 05-05-trackingandparallelism.txt

sqlplus / as sysdba

-- Vérifier et activer le Block Change Tracking
SHOW PARAMETER DB_CREATE_FILE_DEST;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
-- STATUS: ENABLED, FILENAME: chemin du fichier de tracking

# Configurer le parallélisme dans RMAN
rman target / catalog rman/oracle@orclpdb

# Configurer 2 canaux automatiques
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

SHOW DEVICE TYPE;

# Sauvegarde avec allocation automatique de canaux parallèles
BACKUP DATAFILE 1, 6, 7;
# RMAN lance ORA_DISK_1 et ORA_DISK_2 en parallèle

# Allocation manuelle de canaux vers des destinations différentes
RUN {
    ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u01/backups/%U';
    ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/u02/backups/%U';
    BACKUP PLUGGABLE DATABASE devpdb PLUS ARCHIVELOG;
}
# RMAN configure les deux canaux puis les libère à la fin

5.11 Optimization and compression of backups

Backup Optimization

# Activer backup optimization
CONFIGURE BACKUP OPTIMIZATION ON;

This feature makes RMAN skip the backup of data files, archived logs or backup sets if identical copies already exist on the configured device and satisfy the retention policy.

Compressing backups

Compression reduces the size of backups and saves network bandwidth.

# Vérifier l'algorithme de compression actuel
SHOW COMPRESSION ALGORITHM;

# Algorithmes disponibles :
# BASIC   — Par défaut, ne nécessite pas Oracle Advanced Compression
# HIGH    — Meilleur pour bandes passantes lentes, taux de compression le plus élevé
# MEDIUM  — Recommandé pour la plupart des environnements et les sauvegardes cloud
# LOW     — Impact minimal sur le débit, taux de compression le plus faible

# Configurer l'algorithme
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

# Sauvegarder avec compression
BACKUP AS COMPRESSED BACKUPSET DATABASE TAG 'COMPRESSED_BACKUP';

# Sauvegarder un data file spécifique avec compression
BACKUP AS COMPRESSED BACKUPSET DATAFILE 1 TAG 'COMPRESSED_DATAFILE_BACKUP';

General best practices

• Configure a Fast Recovery Area
• Configure a backup retention policy
• Configure a archived redo logs deletion policy
• Database maintains and automatically deletes backups and archived redo logs if necessary

5.12 Demo: Optimization and compression of backups

Demo script — File 05-06-optimizeandcompress.txt

rman target / catalog rman/oracle@orclpdb

# Activer l'optimisation des sauvegardes
Configure backup optimization on;
show all;   -- Vérifier que le paramètre est en vigueur

# Démonstration de l'optimisation
delete noprompt backup of archivelog all;
backup archivelog all;

# Deuxième exécution : RMAN indique les archives déjà sauvegardées
backup archivelog all;
# Message : "skipping archived logs of thread 1 from sequence 1 to 10; already backed up"
# Seule la séquence 11 (nouvelle) est sauvegardée

# Démonstration de la compression
SHOW COMPRESSION ALGORITHM;   -- Affiche BASIC (algorithme par défaut)

# Sauvegarde complète compressée
BACKUP AS COMPRESSED BACKUPSET DATABASE TAG 'COMPRESSED_BACKUP';

LIST BACKUP;

# Comparer les tailles : compressé vs non-compressé
BACKUP AS COMPRESSED BACKUPSET DATAFILE 1 TAG 'COMPRESSED_DATAFILE_BACKUP';
BACKUP DATAFILE 1 TAG 'NOT_COMPRESSED';

LIST BACKUP;
# Résultat observé : sauvegarde compressée = 416.9 MB vs non-compressée = 974.6 MB
# → Économie significative d'espace de stockage

5.13 Optimize performance with V$BACKUP_ASYNC_IO

The V$BACKUP_ASYNC_IO view (for asynchronous I/O) or V$BACKUP_SYNC_IO (otherwise) view displays performance information about current and recently completed RMAN backups and restores.

Phases of an RMAN backup

Important columns of V$BACKUP_ASYNC_IO

Principle: Identify which phase (read or write) is the bottleneck, then adjust the buffer configuration and other parameters.

Structure of lines: For each backup:

• One line per input data file
• One line for the aggregated total performance of all data files
• A line for the output backup piece

5.14 Demo: Optimize performance with V$BACKUP_ASYNC_IO

Demo script — File 05-07-V$BACKUP_ASYNC_IO.txt

rman target / catalog rman/oracle@orclpdb

set echo off;

# Utiliser SET COMMAND ID pour identifier le job dans les vues
RUN {
    SET COMMAND ID TO 'datafile_backup';
    BACKUP datafile 1;
}

exit;

sqlplus / as sysdba

-- Voir le statut du job RMAN par command_id
SELECT OPERATION,
       STATUS,
       MBYTES_PROCESSED,
       START_TIME,
       END_TIME,
       INPUT_BYTES,
       OUTPUT_BYTES
FROM V$RMAN_STATUS
WHERE command_id = 'datafile_backup';
-- 2 opérations : sauvegarde du data file + auto-backup du control file/SPFILE

-- Analyse de performance détaillée
SET LINESIZE 200;
COLUMN "SIZE" FORMAT 99999999999;
COLUMN TYPE FORMAT A9;
COLUMN STATUS FORMAT A10;
COLUMN "ELAPSED_SEC" FORMAT A11;
COLUMN BUFFER_SIZE FORMAT 9999999;
COLUMN BUFFER_COUNT FORMAT 999999;
COLUMN IO_COUNT FORMAT 999999;
COLUMN "B/S" FORMAT 99999999999;
COLUMN long_wait FORMAT 9999;
COLUMN short_wait FORMAT 9999;
COLUMN wait_ratio FORMAT 9999;

-- Jointure entre V$BACKUP_ASYNC_IO et V$RMAN_STATUS
SELECT Filename,
       BYTES "SIZE",
       TYPE,
       AIO.status status,
       ROUND(ELAPSED_TIME / 100) || ' sec ' "ELAPSED_SEC",
       BUFFER_SIZE,
       BUFFER_COUNT,
       IO_COUNT,
       EFFECTIVE_BYTES_PER_SECOND AS "B/S",
       LONG_WAIT_TIME_TOTAL long_wait,
       SHORT_WAIT_TIME_TOTAL short_wait,
       ROUND(LONG_WAITS / IO_COUNT, 2) AS WAIT_RATIO
FROM V$BACKUP_ASYNC_IO AIO,
     V$RMAN_STATUS RS
WHERE RS.RECID = AIO.RMAN_STATUS_RECID
    AND RS.STAMP = AIO.RMAN_STATUS_STAMP
    AND COMMAND_ID = 'datafile_backup';
-- 6 lignes : 3 pour le data file (input, aggregate, output) et 3 pour le control file/SPFILE

Interpretation: The WAIT_RATIO (LONG_WAITS / IO_COUNT) indicates the ratio of operations with long wait times. A high ratio on the read phase suggests a read bottleneck (improving data disks or buffer allocation), while a high ratio on the write suggests a problem on the backup disks.

Recommendation: Create separate disk groups for data files and for backups, and separate physical disks between disk groups, to prevent production I/O from competing with backup I/O.

6. Summary of essential commands

SQL Commands*More common

-- Configuration de la base de données
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER PLUGGABLE DATABASE ALL OPEN;

-- Informations sur les containers (architecture Multitenant)
SELECT name, open_mode FROM V$containers;
SELECT current_scn FROM v$database;
SELECT instance_name, status FROM v$instance;
ARCHIVE LOG LIST;

-- Informations sur les data files
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

-- Gestion du hot backup mode (User Managed)
ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE END BACKUP;
SELECT * FROM v$backup;

-- Flashback
FLASHBACK DATABASE TO SCN <scn>;
FLASHBACK DATABASE TO RESTORE POINT <name>;
FLASHBACK TABLE <table> TO TIMESTAMP TO_TIMESTAMP(<ts>);
FLASHBACK TABLE <table> TO BEFORE DROP;
FLASHBACK TABLE <table> TO RESTORE POINT <name>;
ALTER TABLE <table> ENABLE ROW MOVEMENT;

-- Gestion des restore points
CREATE RESTORE POINT <name>;
CREATE RESTORE POINT <name> GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT <name>;
SELECT NAME, GUARANTEE_FLASHBACK_DATABASE, SCN, TIME FROM V$RESTORE_POINT;

Frequent RMAN commands

# Connexion
rman target /
rman target sys@orclcdb catalog rman/oracle@orclpdb

# Configuration
SHOW ALL;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

# Sauvegardes
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'inc_0' PLUS ARCHIVELOG;
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'inc_1' PLUS ARCHIVELOG;
BACKUP PLUGGABLE DATABASE devpdb PLUS ARCHIVELOG;
BACKUP ARCHIVELOG ALL;
BACKUP CURRENT CONTROLFILE;
BACKUP AS COMPRESSED BACKUPSET DATABASE;

# Validation
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
BACKUP VALIDATE CHECK LOGICAL DATABASE;
RESTORE DATABASE VALIDATE;
VALIDATE DATAFILE 1;
VALIDATE TABLESPACE users;

# Listing et rapports
LIST BACKUP;
LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE devpdb;
LIST ARCHIVELOG ALL;
LIST EXPIRED BACKUP;
LIST FAILURE;
REPORT OBSOLETE;
REPORT SCHEMA;
REPORT NEED BACKUP;
REPORT UNRECOVERABLE;

# Récupération
RESTORE DATABASE;
RECOVER DATABASE;
RESTORE PLUGGABLE DATABASE devpdb;
RECOVER PLUGGABLE DATABASE devpdb;
RESTORE CONTROLFILE FROM AUTOBACKUP;
RECOVER DATAFILE 16 BLOCK 128;  -- Block Media Recovery

# Maintenance
CROSSCHECK BACKUP;
DELETE EXPIRED BACKUP;
DELETE OBSOLETE;
DELETE NOPROMPT BACKUP OF ARCHIVELOG ALL;

# Data Recovery Advisor
LIST FAILURE;
ADVISE FAILURE;
REPAIR FAILURE PREVIEW;
REPAIR FAILURE;

7. Appendix: Useful Oracle Views

Search Terms

perform · backup · recovery · operations · oracle · database · 19c · databases · sql · script · rman · backups · flashback · prerequisites · block · commands · incremental · advisor · catalog · data · query · scripts · views · fra