How to Perform Point-in-Time Recovery in Oracle Database

Introduction

Point-in-Time Recovery (PITR) in Oracle Database is a crucial process that allows administrators to restore data to a specific moment, preventing data loss in case of accidental deletions or corruption. This guide will walk you through restoring an Oracle database using RMAN (Recovery Manager) with incremental backups and archive logs.

Step 1: Taking a Level-0 Backup

Before performing PITR, we first take a Level-0 (Full) Backup of the database using RMAN.

Checking the Initial Row Count

We start by checking the total number of records in the U1.T3 table:

SELECT COUNT(*) FROM U1.T3;

Total records: 6

Executing Level-0 Backup

The following RMAN script performs the Level-0 backup:

RUN {

  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 20G;

  ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 20G;

  ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 20G;

  BACKUP FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p' INCREMENTAL LEVEL 0 DATABASE

    CURRENT CONTROLFILE FORMAT '/data/rman/%d_C_%T_%u'

    SPFILE FORMAT '/data/rman/%d_S_%T_%u'

    PLUS ARCHIVELOG FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';

  RELEASE CHANNEL ch11;

  RELEASE CHANNEL ch12;

  RELEASE CHANNEL ch13;

}

















✅ Level-0 backup completed successfully.

Step 2: Making Changes and Taking Level-1 Backup

After completing the Level-0 backup, we insert more records into the table:

insert into U1.T3 select * from U1.T3

Total records: 12

Executing Level-1 Backup

RUN {
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 20G;
  ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 20G;
  ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 20G;
  BACKUP FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p' INCREMENTAL LEVEL 1 DATABASE
    CURRENT CONTROLFILE FORMAT '/data/rman/%d_C_%T_%u'
    SPFILE FORMAT '/data/rman/%d_S_%T_%u'
    PLUS ARCHIVELOG FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
  RELEASE CHANNEL ch12;
  RELEASE CHANNEL ch13;
}

















✅ Level-1 backup completed successfully.

We now insert 12 more records, increasing the total to 24 records.

⚠️ Important: No new backup was taken after inserting these records.

Step 3: Switching Logs and Shutting Down the Database

Now, we switch log files:

ALTER SYSTEM SWITCH LOGFILE;

Shutdown and Mount Database in Restricted Mode

SHUTDOWN IMMEDIATE;
STARTUP MOUNT RESTRICT;





Drop and Recreate Database

DROP DATABASE;
STARTUP NOMOUNT;






Step 4: Restoring the Database

Restore SPFile

RESTORE SPFILE FROM '/data/rman/PRIM_S_20250325_9b3l832u'

✅ SPFile restored successfully.




Restore Control File

RESTORE CONTROLFILE FROM '/data/rman/PRIM_C_20250325_9a3l832u';

✅ Control file restored successfully.






Now, mount the database:

ALTER DATABASE MOUNT;

Step 5: Restoring and Recovering the Database

Catalog and Restore Database

CATALOG START WITH '/data/rman/';

Why Do We Need to Use the CATALOG Command in RMAN?

The CATALOG command in RMAN (Recovery Manager) is used to register backup files, archive logs, or other recovery-related files that exist on disk but are not yet known to the RMAN repository. This is necessary in several scenarios, including:

1️⃣ When Restoring Backups Copied from Another Location

If you have manually moved backup files from one server to another (e.g., from production to a DR site), RMAN will not automatically recognize these backups. You need to catalog them to make RMAN aware of their existence.

but in this scenario source and destination same.

RESTORE DATABASE;

✅ Database restored successfully.






Restore Archive Logs and Perform Recovery

RECOVER DATABASE;

✅ Database recovered successfully.








Reset Logs and Open Database

ALTER DATABASE OPEN RESETLOGS;

✅ Database opened successfully.

Step 6: Verifying Point-in-Time Recovery

We now check if our 24 records exist, despite only having backups for 6 and 12 records:

SELECT COUNT(*) FROM U1.T3;

✅ Total records: 24 🎉






Key Takeaways:

  • Point-in-Time Recovery (PITR) is possible only with archived log files.

  • Without archive logs, full recovery cannot be achieved.

  • RMAN incremental backups (Level-0 and Level-1) help minimize backup size and optimize restoration time.

Conclusion

Oracle Point-in-Time Recovery (PITR) is a powerful feature for database restoration. By leveraging RMAN backups and archived logs, you can recover a database to a precise moment, ensuring minimal data loss.

🚀 Optimize your backup strategy today to safeguard your critical data!


💬 Related Topics You Might Like






Comments

Post a Comment

Popular posts from this blog

Image
Oracle Export Metadata only And Import the Metadata only Guide – Step-by-Step Tutorial Scenario : Client is wanted to export and import the metadata In this guide, we will learn how to use expdp (Data Pump Export) and impdp (Data Pump Import) in Oracle Database to back up metadata and restore the same only. To create a backup, first we have to set up a physical backup location. Creating a Backup location: On Linux server: mkdir -p /data/backup On Oracle Database: Command: create a directory TEST_DIR2 as '/data/backup'; Here, TEST_DIR2 is a logical directory pointing to the physical backup location. To verify the directory: SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2'; Exporting a Metadata using expdp: To backup the Metadata for Schema U2,  expdp schemas=U2 directory=TEST_DIR2 dumpfile=schdata_only2.dmp logfile=schdata_only.log content=METADATA_ONLY Note:   Dump File (.dump): A binary file generated by expdp that contains exported schema objects, data...
Read more
Image
How to Enable Archive Log Mode in Oracle (Switch to ARCHIVELOG Mode) If you want to disable archive log mode (switch to NOARCHIVELOG mode), follow these steps: 1. Check Current Archive Log Mode Before making changes, check if your database is in NO ARCHIVELOG mode: command -> select log_mode from v$database; The database in noarchivelog mode   If the output is NO ARCHIVELOG , it means archive logging is disabled. 2. Shutdown the Database You cannot switch to ARCHIVELOG mode while the database is running, so first shut it down: commnd -> shut immediate; 3. Start the Database in MOUNT Mode the database should be in mount stage to disable or enable it 4. Enable Archive Log Mode (Switch to ARCHIVELOG Mode) Run the following command: ALTER DATABASE ARCHIVELOG; 5. Open the Database Normally ALTER DATABASE OPEN; 6. Verify the Change Run this command again to confirm the mode: SELECT log_mode FROM v$database; Impact of Enabling Archive Log Mode ✅ Point-in-Time Recovery: With...
Read more
Image
Oracle Export Previous SCN Data & Import into an Existing Table – Step-by-Step Guide Scenario: In this guide, we will explore how to use Oracle Data Pump Export (expdp) and Import (impdp) to back up historical data from a previous SCN (System Change Number) and restore it into an existing table. This approach is useful when you need to retrieve older data while preserving the table structure. Step 1: Setting Up a Backup Location Before exporting data, we need to create a physical backup location where the dump files will be stored. On Linux server: mkdir -p /data/backup On Oracle Database: We create a logical directory pointing to the physical backup location: Command: create a directory TEST_DIR2 as '/data/backup'; TEST_DIR2 -->  Logical directory pointing to the physical backup location. To verify the directory: SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2'; Step 2: Checking Current Table Data Inserted few more records Before exporting, ...
Read more