Backup and Recovery Improvements in Oracle Database 23ai

Oracle Database 23ai represents decades of continuous improvement in backup and recovery capabilities, transforming what were once manual, error-prone operations into highly automated, intelligent systems. This lesson examines key improvements across logical backups, block corruption repair, transaction analysis, and modern automation features that simplify backup operations while improving recovery speed and reliability.

Understanding these improvements enables DBAs to leverage modern Oracle capabilities that reduce backup windows, minimize recovery time, automate complex tasks, and provide granular recovery options unavailable in earlier Oracle versions. These enhancements span logical backup tools, corruption detection and repair, transaction history analysis, cloud integration, and AI-driven automation.

Data Pump replaced the legacy Export/Import utilities starting in Oracle 10g, providing superior performance, flexibility, and functionality for logical database backups. Data Pump operates at the database server level rather than the client level, enabling dramatic performance improvements and advanced capabilities unavailable in the original Export/Import tools.

Key Data Pump Advantages

Data Pump provides comprehensive improvements over legacy utilities:

• Server-side processing: Operations execute on database server using multiple parallel processes, dramatically faster than client-based Export/Import
• Parallel operations: Multiple worker processes simultaneously export/import data, scalable to available CPU cores
• Network mode: Direct database-to-database transfers without intermediate dump files
• Fine-grained object selection: Advanced filtering by schema, table, tablespace, or custom queries
• Compression: Built-in compression reduces dump file sizes by 50-90%
• Encryption: Secure sensitive data in dump files with transparent encryption
• Resumable operations: Jobs can pause and resume after space or quota issues are resolved
• Progress monitoring: Real-time job status and progress tracking

Data Pump Export Examples

Full database export with compression:

expdp system/password \
  FULL=Y \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=fulldb_%U.dmp \
  PARALLEL=4 \
  COMPRESSION=ALL \
  LOGFILE=fulldb_export.log

Schema export with query filter:

expdp system/password \
  SCHEMAS=HR,SALES \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=hr_sales.dmp \
  QUERY='HR.EMPLOYEES:"WHERE hire_date > SYSDATE - 365"' \
  COMPRESSION=METADATA_ONLY

Table export excluding specific data:

expdp system/password \
  TABLES=orders,order_items \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=orders_structure.dmp \
  CONTENT=METADATA_ONLY \
  EXCLUDE=STATISTICS

Data Pump Import Capabilities

Table remapping during import:

impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=hr_sales.dmp \
  REMAP_SCHEMA=HR:HR_DEV \
  REMAP_TABLESPACE=USERS:USERS_DEV \
  TABLE_EXISTS_ACTION=REPLACE

Oracle 23ai Data Pump Enhancements

• JSON Relational Duality support: Export and import JSON duality views with relational backing tables
• Blockchain table support: Export immutable blockchain tables while preserving chain integrity
• Enhanced multitenant operations: Improved PDB cloning and data movement between container databases
• Cloud integration: Direct export to OCI Object Storage without local staging
• Performance improvements: Optimized parallel processing and compression algorithms

When to Use Data Pump

• Schema-level recovery: Restore individual schemas without full database recovery
• Object migration: Move tables, procedures, or entire schemas between databases
• Data subsetting: Export filtered datasets for development, testing, or analytics
• Database upgrades: Migrate data to new Oracle versions or platforms
• Logical corruption recovery: Extract clean data from corrupt tables
• Compliance and archival: Create long-term archives of historical data

Block corruption—though rare in modern Oracle databases—can occur due to hardware failures, storage system bugs, or memory errors. Oracle 23ai provides multiple layers of detection and automated repair capabilities that minimize downtime and data loss when corruption occurs.

Data Recovery Advisor

Data Recovery Advisor (introduced in Oracle 11g, enhanced in 23ai) automatically diagnoses data failures and recommends optimal recovery strategies:

• Automatic failure detection: Continuously monitors for corruption, missing files, and control file issues
• Impact assessment: Analyzes failure severity and affected database components
• Repair recommendations: Generates optimal recovery plans based on available backups and current database state
• Automated execution: Can execute recovery scripts automatically with DBA approval

Using Data Recovery Advisor:

RMAN> LIST FAILURE;
-- Displays all detected failures with severity and impact

RMAN> ADVISE FAILURE;
-- Generates recommended repair strategy

RMAN> REPAIR FAILURE;
-- Executes recommended repair automatically

RMAN Block Media Recovery

Block Media Recovery restores individual corrupted blocks without restoring entire datafiles, dramatically reducing recovery time:

• Granular recovery: Restore only corrupted blocks, not entire files
• Online operation: Database remains available during block recovery
• Minimal downtime: Users accessing non-corrupt blocks continue working
• Automatic block selection: RMAN identifies corrupt blocks and recovers them automatically

Example block recovery:

-- Recover all known corrupt blocks
RMAN> RECOVER CORRUPTION LIST;

-- Recover specific datafile blocks
RMAN> RECOVER DATAFILE 5 BLOCK 23, 45, 67;

Active Data Guard Automatic Block Repair

In Data Guard configurations, corrupted blocks are automatically repaired using standby database copies:

• Primary detects block corruption during read
• Automatically fetches clean block from physical standby
• Repairs corrupted block in primary database
• Updates standby if standby block was also corrupt
• Entire process occurs transparently without DBA intervention

DBMS_REPAIR Package

For complex corruption scenarios, DBMS_REPAIR provides granular control over corruption detection and repair:

-- Detect corruption in a table
BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    repair_table_name => 'REPAIR_TABLE',
    corrupt_count => :corrupt_blocks
  );
END;
/

-- Mark corrupt blocks as software corrupt (skip during reads)
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    object_type => DBMS_REPAIR.TABLE_OBJECT,
    repair_table_name => 'REPAIR_TABLE',
    fix_count => :fixed_blocks
  );
END;
/

Oracle 23ai Corruption Handling Improvements

• Enhanced detection: More aggressive corruption checking during backups and normal operations
• Faster block recovery: Optimized block recovery algorithms reduce recovery time
• Cloud integration: Block recovery leverages cloud-based backups automatically
• AI-driven prediction: Machine learning identifies storage systems likely to experience corruption

LogMiner enables SQL-based analysis of Oracle redo logs, providing detailed transaction history for auditing, troubleshooting, and targeted recovery operations. LogMiner transforms binary redo log files into queryable relational data, enabling precise identification of data changes.

LogMiner Capabilities

• Transaction reconstruction: View complete transaction history including SQL statements
• User activity tracking: Identify who made specific changes and when
• Data recovery analysis: Determine exact changes to reverse or reapply
• Compliance and auditing: Satisfy regulatory requirements for change tracking
• Performance analysis: Identify heavy transaction patterns and hotspots
• Logical corruption investigation: Trace how data became corrupted

LogMiner Usage Example

Basic LogMiner session:

-- Add redo log files to analyze
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/u01/archive/arch_1_100.arc',
    OPTIONS => DBMS_LOGMNR.NEW
  );
  DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/u01/archive/arch_1_101.arc',
    OPTIONS => DBMS_LOGMNR.ADDFILE
  );
END;
/

-- Start LogMiner analysis
BEGIN
  DBMS_LOGMNR.START_LOGMNR(
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
               DBMS_LOGMNR.COMMITTED_DATA_ONLY
  );
END;
/

-- Query transaction details
SELECT username, sql_redo, sql_undo, timestamp
FROM v$logmnr_contents
WHERE seg_owner = 'HR' 
  AND seg_name = 'EMPLOYEES'
  AND operation = 'UPDATE'
ORDER BY timestamp;

-- End LogMiner session
EXEC DBMS_LOGMNR.END_LOGMNR;

Practical LogMiner Use Cases

Find who deleted specific records:

SELECT username, sql_redo, timestamp
FROM v$logmnr_contents
WHERE operation = 'DELETE'
  AND seg_name = 'ORDERS'
  AND sql_redo LIKE '%order_id = 12345%'
ORDER BY timestamp DESC;

Generate undo SQL for batch job rollback:

-- Find all changes from specific transaction
SELECT sql_undo
FROM v$logmnr_contents
WHERE xid = '0A00120003E80000'  -- Transaction ID
ORDER BY scn;

Oracle 23ai LogMiner Enhancements

• JSON data support: Track changes to JSON documents and JSON Relational Duality Views
• Blockchain table auditing: Analyze immutable table transaction history
• Improved performance: Faster redo log parsing and dictionary loading
• Cloud integration: Analyze redo logs stored in OCI Object Storage
• Extended retention: Mine archived logs stored in long-term cloud storage

Oracle 23ai introduces AI-driven automation and intelligent defaults that reduce manual configuration and improve backup reliability:

Automatic Backup Policies

• Smart scheduling: Machine learning analyzes database workload patterns and automatically schedules backups during low-activity periods
• Adaptive compression: Dynamically selects compression algorithms based on data characteristics and backup window constraints
• Intelligent retention: Automatically adjusts retention policies based on recovery window requirements and storage availability
• Predictive validation: Proactively validates backups likely to be needed for recovery

Recovery Performance Enhancements

• Faster Media Recovery: Optimized redo apply algorithms reduce recovery time by 30-50%
• Parallel recovery: Enhanced parallelization for faster restore and recovery operations
• Smart restore: RMAN automatically selects fastest restore path (image copies vs. backup sets)
• Section-based datafile restore: Large datafiles restored in parallel sections

Cloud-Native Improvements

• Direct cloud backup: Backup directly to OCI Object Storage without intermediate staging
• Cross-region replication: Automatic backup replication across geographic regions
• Backup to multiple clouds: Simultaneously backup to OCI, AWS, or Azure
• Cloud cost optimization: Automatic tiering to archival storage for older backups

Oracle 23ai provides sophisticated backup capabilities for multitenant container database (CDB) and pluggable database (PDB) architectures:

PDB-Level Backup Operations

• Individual PDB backups: Back up single PDBs without backing up entire CDB
• PDB point-in-time recovery: Restore individual PDBs to different points in time
• PDB relocation: Move PDBs between CDBs using backup and restore
• PDB cloning from backups: Create PDB clones directly from backup sets

Example PDB backup:

-- Backup single PDB
RMAN> BACKUP PLUGGABLE DATABASE pdb1;

-- Backup multiple PDBs
RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2, pdb3;

-- PDB point-in-time recovery
RMAN> RECOVER PLUGGABLE DATABASE pdb1 
      UNTIL TIME "TO_DATE('2026-01-28 14:00:00', 'YYYY-MM-DD HH24:MI:SS')";

Application Container Backup

• Backup application containers and application PDBs independently
• Clone application PDBs from backups maintaining application version
• Upgrade application PDBs while maintaining backup consistency

Oracle 23ai enhances backup validation capabilities ensuring recoverability before disasters occur:

Comprehensive Validation

-- Validate database recoverability without restoration
RMAN> RESTORE DATABASE VALIDATE;

-- Validate specific recovery scenario
RMAN> RESTORE DATABASE PREVIEW;

-- Validate backup integrity with block checking
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

-- Test recovery to specific point in time
RMAN> RESTORE DATABASE UNTIL TIME 'SYSDATE-7' VALIDATE;

Automated Validation Scheduling

• Scheduled validation jobs run automatically without manual intervention
• Validation failures trigger alerts to DBAs and management
• Comprehensive validation reports for compliance audits
• Integration with Oracle Enterprise Manager for centralized monitoring

Oracle 23ai includes significant Data Guard improvements for disaster recovery:

Fast-Start Failover Improvements

• Faster failover: Typical failover time reduced to 10-30 seconds
• Automatic standby selection: Intelligently selects best standby candidate for failover
• Multi-standby support: Coordinate failover across multiple standby databases
• Application continuity: Seamlessly replay in-flight transactions after failover

Far Sync Improvements

• Zero data loss protection for geographically distant standbys
• Reduced network bandwidth requirements
• Improved redo transport compression

Active Data Guard Enhancements

• Automatic block repair: Corrupt blocks repaired automatically from standby
• DML redirection: Limited DML operations on standby redirected to primary automatically
• Global indexes: Maintain global indexes during rolling upgrades

Leverage Oracle 23ai improvements effectively with these operational practices:

1. Adopt Data Pump for logical backups: Retire legacy Export/Import; use Data Pump exclusively for logical backup operations
2. Enable automatic backup validation: Configure RMAN to validate backups automatically after creation
3. Implement Data Recovery Advisor: Leverage automated failure detection and repair recommendations
4. Use LogMiner for audit trails: Maintain transaction history for compliance and troubleshooting
5. Configure cloud backups: Leverage OCI Object Storage for off-site backup protection
6. Implement PDB-level backups: In multitenant environments, backup PDBs independently for granular recovery
7. Test recovery regularly: Quarterly recovery tests validate backup procedures and identify issues proactively
8. Monitor backup jobs: Use Enterprise Manager Cloud Control for centralized backup monitoring
9. Document procedures: Maintain runbooks for Data Pump restores, block recovery, and LogMiner analysis

This lesson examined modern improvements in Oracle 23ai backup and recovery operations, from Data Pump's logical backup capabilities to intelligent block corruption repair, transaction analysis with LogMiner, and AI-driven automation features. Understanding these improvements enables DBAs to implement more reliable, efficient, and comprehensive backup solutions than were possible in earlier Oracle versions.

The next lesson explores Oracle Enterprise Manager Cloud Control features for centralized backup management, covering how to configure backup policies, monitor backup jobs across database fleets, perform guided recovery operations, and generate compliance reports demonstrating backup policy adherence across your Oracle estate.