datablogs: Point In Time Restore

Showing posts with label Point In Time Restore. Show all posts

Tuesday, September 16, 2025

Oracle Standard Edition Log Shipping in Windows

Selva | September 16, 2025 | No comments | Backup, Oracle, Oracle 11g, Point In Time Restore

In today’s always-on world, database downtime can cost businesses a fortune. Setting up a reliable Disaster Recovery (DR) solution ensures your organization can quickly recover from unexpected failures.

This guide walks you through a practical, step-by-step process of implementing Oracle 11g Standard Edition DR, from enabling archive logs to recovering the database at the DR site.

Why This Matters

Business Continuity: Minimize downtime during outages.
Data Protection: Ensure no transaction is lost.
Compliance: Meet RTO/RPO requirements.

Prerequisites

Before starting, ensure:

Oracle 11g Standard Edition is installed on both DC (Primary) and DR (Standby) servers.
Network connectivity & firewall rules allow archive log and backup file transfer between DC and DR.
Adequate storage is provisioned for backups and archive logs.

High-Level DR Workflow


[DC: Primary Database]
   |
   |--- Archive Logs + RMAN Backups ---> [DR: Standby Database]
                                              |
                                              --> Restore + Recover --> Open DB

Step-by-Step Implementation

Step 1: Install Oracle 11g

Install Oracle 11g Standard Edition on both DC and DR servers.

Step 2: Enable Archive Logging (DC)

Archive logs capture every committed change — critical for recovery.


SQL>SELECT LOG_MODE FROM V$DATABASE;

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;

Step 3: Force Logging & Supplemental Logs

Ensure redo logs are always generated and enough data is captured for recovery.


SQL>ALTER DATABASE FORCE LOGGING;
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Step 4: Configure Recovery Parameters

Increase recovery area size if required:


SQL>SHOW PARAMETER db_recovery;
SQL>ALTER SYSTEM SET db_recovery_file_dest_size=10g;

Step 5: Configure RMAN

Enable control file autobackup and retention policy:


RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Step 6: Take Full Backup (DC)


RMAN>BACKUP DATABASE;

This will back up all datafiles and perform an autobackup of control file & SPFILE.

Step 7: Prepare DR Instance

Shutdown instance
Remove old datafiles (oradata)
Start in NOMOUNT mode


STARTUP NOMOUNT;

Step 8: Move Backup Files to DR

Transfer flash_recovery_area folder to the DR server.

Step 9: Restore Control File & Database


RMAN>RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN>ALTER DATABASE MOUNT;
RMAN>RESTORE DATABASE;

Step 10: Recover Database

Update the catalog in RMAN before proceeding Recover Database

RMAN> CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
searching for all files that match the pattern C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG no files found to be unknown to the database

Use RMAN or SQL*Plus:


SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Enter AUTO to apply logs automatically.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 1046243 generated at 09/16/2025 08:25:59 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC
ORA-00280: change 1046243 for thread 1 is in sequence #20
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1048238 generated at 09/16/2025 08:35:03 needed for thread 1
ORA-00289: suggestion : C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_21_NDL89XF1_.ARC
ORA-00280: change 1048238 for thread 1 is in sequence #21
ORA-00278: log file 'C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2025_09_16\O1_MF_1_20_NDL89QG0_.ARC' no longer needed for this recovery

Finally open with resetlogs:


SQL>ALTER DATABASE OPEN RESETLOGS;

Step 11: Validate

Run validation queries:


SQL>SELECT thread#, low_sequence#, high_sequence# FROM v$archive_gap;
SQL>SELECT file#, checkpoint_change# FROM v$datafile_header ORDER BY file#;
SQL>SELECT checkpoint_change# FROM v$database;

Ensure no archive log gaps remain.

RMAN Handy Scripts

Here are some ready-to-use RMAN scripts for ongoing maintenance:

Catalog & Recover


RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  CATALOG START WITH 'C:\app\Administrator\flash_recovery_area\orcl\ARCHIVELOG';
  RESTORE ARCHIVELOG ALL;
  RECOVER DATABASE;
  DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
  RELEASE CHANNEL c1;
}

Point-in-Time Recovery


RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  SET UNTIL SEQUENCE 18 THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
  RELEASE CHANNEL c1;
}

Key Takeaways

Automate archive log shipping from DC to DR to ensure near real-time recovery capability.
Regularly run RMAN crosschecks to keep backup metadata in sync.
Validate DR site periodically by performing trial recovery exercises.

With this approach, you can be confident that your Oracle 11g environment is disaster-ready and downtime can be minimized.

Monday, June 23, 2025

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

Selva | June 23, 2025 | No comments | Amazon RDS, Database Administration, MySQL, Point In Time Restore, Troubleshooting

Had a some conversation with friend he is facing this issue for long back and unable to solve it . But i had an thought why we cant replicate in our test environment and fix this quickly . After a long time had a nice troubleshooting hours

Lets deep dive into the troubleshooting , before enter into the troubleshooting requested few sample output for the same

Outputs for the issues :

First things requested to login without grant tables and asked to repair the tables

root@ip-100-23-45-122:sudo mysqld_safe --skip-grant-tables --skip-networking --skip-plugin-load &

When trying to repair it mysql database most of the tables got corrupted , So corruption happened on the master tables itself ,

root@ip-100-23-45-122:/var/lib/mysql/mysql# mysqlcheck -u root -p --repair --databases mysql
Enter password:
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost
Error : Table 'mysql.engine_cost' doesn't exist
status : Operation failed
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed
Error : Table 'mysql.gtid_executed' doesn't exist
status : Operation failed
mysql.help_category
Error : Table 'mysql.help_category' doesn't exist
status : Operation failed
mysql.help_keyword
Error : Table 'mysql.help_keyword' doesn't exist
status : Operation failed
mysql.help_relation
Error : Table 'mysql.help_relation' doesn't exist
status : Operation failed
mysql.help_topic
Error : Table 'mysql.help_topic' doesn't exist
status : Operation failed
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist
status : Operation failed
mysql.ndb_binlog_index OK
mysql.plugin

So further we need to try something to resolve it . I have replicated same issues with my local environment and followed below steps in the local

Troubleshooting Step 1 :

Installed separate mysql instance with different port and copied all user database directories to new server then tried to start mysql its started also

But when trying to access the same user table got an same error

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

So issue not with master table corruption , something different

Troubleshooting Step 2 :

I have gone through so many links and references tried below things as well ,

Stop the MySQL then Remove ib_logfile0 , ib_logfile1 . Again start the MySQL Server - Its not worked
Move corrupted ibdata1 file in the newly installed MySQL server and then restart MySQL Server - Its not worked
More links requested to restart multiple times its very funny thing but tried it will or not - Its also not worked

Finally complete MySQL troubleshooting reference resolved this issue with replacing tablespace method

Troubleshooting Step 3 :

As per reference link mentioned below Restoring Orphan File-Per-Table ibd Files then its resolved and able to retrive the records from the table

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

Again installed one more MySQL Server and Created dummy database as datablogs and created only structure from the sample

Note : Only if you have structure of the table its possible to recover

mysql> create database datablogs;
Query OK, 1 row affected (0.01 sec)

mysql> use datablogs;
Database changed

mysql> CREATE TABLE tbl_followup (
id int(11) NOT NULL AUTO_INCREMENT,
table_header text,
action varchar(100) DEFAULT NULL,
action_button_text varchar(100) DEFAULT NULL,
parent_template text,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE datablogs.tbl_followup DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

Copied only tbl_followup.ibd file from the corrupted server to new server then imported the tablespace

mysql> ALTER TABLE datablogs.tbl_followup IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select * from datablogs.tbl_followup;
Empty set (0.01 sec)

So overall while reading it very simple but its very hard troubleshooting did in lifetime !!!

Anyway instead of doing multiple solution follow my steps to resolve the issue sortly !!!

Friday, February 17, 2023

Restore and Point in time Restore with Percona Backup of MongoDB on another instance

Selva | February 17, 2023 | No comments | Database Administration, medium, MongoDB, NoSQL, percona, Point In Time Restore

Its easy to recover MongoDB Backup using Percona Backup for MongoDB

https://medium.com/@datablogs/restore-and-point-in-time-restore-with-percona-backup-of-mongodb-on-another-instance-4766f4468443

Thursday, August 11, 2022

SQL Server Point in Time Recovery

Selva | August 11, 2022 | No comments | Automation, Database Administration, Point In Time Restore, SQL Server

Oooooouch !!! I have deleted critical data in the ledger table !!!

When we ran delete or drop Script on any database without double check , Its Really big problem for the end users !!! Also you wont sleep 😁😁😁

If you have GODDBA , you are saved millions dollars of your data also your valuable customers .

Oh Great 😀😀😀 How we can achieve that ?

In SQL Server Multiple Database Backup Methods are available to tackle that ,

If SQL Server database is in full recovery model we can take transaction log backups on every hour/Minute/Seconds into local or remote drives .

So, Yes am taking Log Backup 😕😕😕 How to we restore particular time ?

Using with transaction logs we can bring back the data as much as you want depends on your log backup strategy .

To achieve that here is the automated point in time restore script for your valuable environment ,

Scripts are available in https://github.com/selvackp/SQLServerPointInTimeRestore.git

So finally you have saved your millions dollars worth of data in 10 Minutes !!!

USE master
GO

DECLARE 
        @DatabaseOLDName			sysname  = 'test',										
        @DatabaseNewName			sysname  = 'test1',										
        @PrimaryDataFileName		        sysname  = 'test',									
	@SecDataFileName                        sysname  =  NULL,
        @DatabaseLogFileName		        sysname  = 'test_log',								
	@PrimaryDataFileCreatePath              sysname  = 'D:\MISC\Bkp\testdata.mdf',			
        @SecDataFileCreatePath		        sysname  = NULL,									
        @SecDataFileCreatePath1		        sysname  = NULL,									
        @DatabaseLogFileCreatePath              sysname  = 'D:\MISC\Bkp\test_log.ldf',		
        @PITRDateTime				datetime = '2022-08-11T20:44:11';

DECLARE @command				nvarchar(MAX),
        @OldPhysicalPathName		        nvarchar(MAX),
        @FullBackupDateTime			datetime,
        @DiffBackupDateTime			datetime,
        @LogBackupDateTime			datetime,
        @message				nvarchar(MAX);

SET @command = N'RESTORE DATABASE @DatabaseNewName FROM DISK = @OldPhysicalPathName WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime,
     MOVE N''' + @PrimaryDataFileName + N''' TO N''' + @PrimaryDataFileCreatePath + N''','
           + COALESCE('
     MOVE N''' + @SecDataFileName + ''' TO N''' + @SecDataFileCreatePath + ''',', '')
           + N'
     MOVE N''' + @DatabaseLogFileName + N''' TO N''' + @DatabaseLogFileCreatePath + N''';';

SELECT     TOP (1) @OldPhysicalPathName = bmf.physical_device_name,@FullBackupDateTime = bs.backup_start_date
FROM       msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON  bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName AND  bs.type= 'D' AND  bs.backup_start_date < @PITRDateTime
ORDER BY   bs.backup_start_date DESC;

SET @message = N'Starting restore of full backup file '+ @OldPhysicalPathName + N', taken ' + CONVERT(nvarchar(30), @FullBackupDateTime, 120);

RAISERROR(@message, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;


SET @command = N'RESTORE DATABASE @DatabaseNewName FROM DISK = @OldPhysicalPathName WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime;';

SELECT     TOP (1) @OldPhysicalPathName = bmf.physical_device_name,@DiffBackupDateTime = bs.backup_start_date
FROM       msdb.dbo.backupset   AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON  bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName AND  bs.type  = 'I' AND  bs.backup_start_date >= @FullBackupDateTime AND  bs.backup_start_date< @PITRDateTime
ORDER BY   bs.backup_start_date DESC;

IF @@ROWCOUNT > 0
BEGIN;
    SET @message = N'Starting restore of differential backup file ' + @OldPhysicalPathName + N', taken ' + CONVERT(nvarchar(30), @DiffBackupDateTime, 120);

    RAISERROR(@message, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;
END;

SET @command = N'RESTORE LOG @DatabaseNewName
FROM DISK = @OldPhysicalPathName
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime;';

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY TYPE_WARNING FOR
SELECT     bmf.physical_device_name,
           bs.backup_start_date
FROM       msdb.dbo.backupset         AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
   ON      bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName
AND        bs.type                 = 'L'
AND        bs.backup_start_date    >= COALESCE(@DiffBackupDateTime, @FullBackupDateTime)
ORDER BY   bs.backup_start_date ASC;

OPEN c;

FETCH NEXT FROM c
INTO @OldPhysicalPathName,
     @LogBackupDateTime;

WHILE @@FETCH_STATUS = 0
BEGIN;
    SET @message = N'Starting restore of log backup file '
                   + @OldPhysicalPathName + N', taken '
                   + CONVERT(nvarchar(30), @LogBackupDateTime, 120);
    RAISERROR(@message, 0, 1) WITH NOWAIT;
    EXEC sys.sp_executesql @command,
                           N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                           @DatabaseNewName,
                           @OldPhysicalPathName,
                           @PITRDateTime;

    IF @LogBackupDateTime >= @PITRDateTime
        BREAK;

    FETCH NEXT FROM c
    INTO @OldPhysicalPathName,
         @LogBackupDateTime;
END;

CLOSE c;
DEALLOCATE c;

SET @command = N'RESTORE DATABASE @DatabaseNewName
WITH RECOVERY;';

RAISERROR('Starting recovery', 0, 1) WITH NOWAIT;
EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;
GO

datablogs

Data will talk to you if you're willing to listen !!!

Categories

Tuesday, September 16, 2025

Oracle Standard Edition Log Shipping in Windows

Why This Matters

Prerequisites

High-Level DR Workflow

Step-by-Step Implementation

Step 1: Install Oracle 11g

Step 2: Enable Archive Logging (DC)

Step 3: Force Logging & Supplemental Logs

Step 4: Configure Recovery Parameters

Step 5: Configure RMAN

Step 6: Take Full Backup (DC)

Step 7: Prepare DR Instance

Step 8: Move Backup Files to DR

Step 9: Restore Control File & Database

Step 10: Recover Database

Step 11: Validate

RMAN Handy Scripts

Catalog & Recover

Point-in-Time Recovery

Key Takeaways

Monday, June 23, 2025

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

Friday, February 17, 2023

Restore and Point in time Restore with Percona Backup of MongoDB on another instance

Thursday, August 11, 2022

SQL Server Point in Time Recovery