IBM Support

How to use OnTape to perform an Informix® Point-In-Log restore

Troubleshooting


Problem

This document explains how to perform a Point-In-Log restore using OnTape. This operation used to be possible only through OnBar, but starting with Informix Dynamic Server version 10.00, it is possible to restore an archive until the wanted logical log file.

Symptom

Occasionally, it is necessary to perform a physical restore of an Informix instance followed by a logical restore until a single logical log file to avoid undesired transactions replay.

Cause

Usually undesired user transactions such as a DROP TABLE, DELETE or UPDATE statements, or a DROP DATABASE that cause unpredictable results, need to be removed from the logical restore phase.

Environment

LTAPEDEV onconfig parameter must be set to a directory path.

Diagnosing The Problem

1) Check for the logical log file with the undesired transaction, that is. a DROP TABLE statement.

2) Assuming that LTAPEDEV is set to /opt/IBM/informix/backup/LogsPoolDir, locate the logical log files:


$ ls -ltr /opt/IBM/informix/backup/LogsPoolDir
-rw-rw----    1 informix informix   10321920 Dec 19 14:30 db1_1_Log0000000067
-rw-rw----    1 informix informix    8421376 Dec 19 14:30 db1_1_Log0000000070
-rw-rw----    1 informix informix   10321920 Dec 19 14:30 db1_1_Log0000000069
-rw-rw----    1 informix informix   10321920 Dec 19 14:30 db1_1_Log0000000068
-rw-rw----    1 informix informix      98304 Dec 19 14:30 db1_1_Log0000000071
-rw-rw----    1 informix informix      98304 Dec 19 14:31 db1_1_Log0000000072
-rw-rw----    1 informix informix      98304 Dec 19 14:31 db1_1_Log0000000077
-rw-rw----    1 informix informix      98304 Dec 19 14:31 db1_1_Log0000000076
-rw-rw----    1 informix informix      98304 Dec 19 14:31 db1_1_Log0000000075
-rw-rw----    1 informix informix      98304 Dec 19 14:31 db1_1_Log0000000074
-rw-rw----    1 informix informix      98304 Dec 19 14:31 db1_1_Log0000000073
-rw-rw----    1 informix informix      98304 Dec 19 14:31 db1_1_Log0000000078

This path holds the logical log files from 67 through 78.

Resolving The Problem

Assuming that the undesired transaction (DROP TABLE) happened when logical log 76 was the Current log file, the Point-In-Log restore should stop the transaction replay before reaching log 76, therefore, logical log file number 75.

1) Remove or rename the file db1_1_Log000000076 to stop the Logical Recovery.

2) Issue the ontape -r command. The restore process should print messages like the following:

$ ontape -r


Restore will use level 0 archive file /opt/IBM/informix/backup/db1_1_L0. Press Return to continue ...

Archive Tape Information

Tape type:      Archive Backup Tape
Online version: IBM Informix Dynamic Server Version 11.50.FC7

...

Continue restore? (y/n)y
Do you want to back up the logs? (y/n)n

...

Restore a level 1 archive (y/n) n
Do you want to restore log tapes? (y/n)y

...

Rollforward should start with log number 70
Restore will use log backup file /opt/IBM/informix/backup/LogsPoolDir/db1_1_Log0000000070. Press Return to continue ...

Rollforward log file /opt/IBM/informix/backup/LogsPoolDir/db1_1_Log0000000070 ...
Rollforward log file /opt/IBM/informix/backup/LogsPoolDir/db1_1_Log0000000071 ...
Rollforward log file /opt/IBM/informix/backup/LogsPoolDir/db1_1_Log0000000072 ...
Rollforward log file /opt/IBM/informix/backup/LogsPoolDir/db1_1_Log0000000073 ...
Rollforward log file /opt/IBM/informix/backup/LogsPoolDir/db1_1_Log0000000074 ...
Rollforward log file /opt/IBM/informix/backup/LogsPoolDir/db1_1_Log0000000075 ...

Program over.
$

The online.log file will record the restore process without any error messages:

14:35:03  IBM Informix Dynamic Server Started.

...

14:35:12  Restartable Restore has been ENABLED
14:35:12  Recovery Mode
14:35:13  Physical Restore of rootdbs, datadbs, cdrspace, sbspace started.

...



14:35:24  Physical Restore of rootdbs, datadbs, cdrspace, sbspace Completed.
...

14:35:40  Logical Recovery Started.
14:35:40  10 recovery worker threads will be started.
...

14:35:40  Start Logical Recovery - Start Log 70, End Log?
14:35:40  Starting Log Position - 70 0x7ec018
14:35:40  Clearing the physical and logical logs has started
14:35:43  Cleared 99 MB of the physical and logical logs in 3 seconds
14:36:01  Logical Log 70 Complete, timestamp: 0x11c83fc.
14:36:02  Logical Log 71 Complete, timestamp: 0x11c83fc.

...

14:36:06  Logical Log 75 Complete, timestamp: 0x11c840a.
14:36:06  Logical Recovery has reached the transaction cleanup phase.
14:36:06  Logical Recovery Complete.
          5 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks

14:36:06  Logical Recovery Complete.

3) Issue the onstat - command. The engine is now in Quiescent mode:


$ onstat -

IBM Informix Dynamic Server Version 11.50.FC7     -- Quiescent -- Up 00:05:49 -- 54896 Kbytes


Note: All data transactions that occur after logical log 75 will be lost.

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"10.0;11.1;11.5;11.7","Edition":"Enterprise;Workgroup;Ultimate;Growth","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21575948