How to roll forward AI files to a specific point in time

• in cases where it is known that the database became corrupt after a certain point in time and allow recovery up to that point
• recovering a database with a bad block in the AI file. This allows recovery of as much data as possible from the corrupt AI file.
• restoring multiple online backups when these databases all need to be restored to a specifc point in time

• <time> is in the format: yyyy:mm:dd:hh:mm:ss
• <trid> is the transaction id number
• The time can be pulled from the database.lg file in the event of a known failure time, admin, business operation or when the next ai file was switched during an online backup for example, otherwise by using RFUTIL aimage scan
• The transaction id can potentially be found by monitoring software that records transaction-id information, or by using RFUTIL aimage scan verbose
• This feature cannot be used with both endtime and endtrans, nor can it be used to skip transactions or roll back transactions. The only transactions that will be undone are those that are still uncommitted/open when the roll forward operation completes.

The following example demonstrates using the rollforward endtime or endtrans feature:

1) Create a copy of the demo database and enable after-imaging:

$  prodb mydemo demo
$  echo a . > add.st
$  echo a . >> add.st
$  echo a . >> add.st
$  prostrct add mydemo add.st
$  probkup mydemo mydemo.bak
$  rfutil mydemo -C aimage begin
$  proserve mydemo

2.)  Create two procedures to modify the database Customer table's data: 

• updcust.p, that updates all customers

FOR EACH customer:
customer.max-credit = customer.max-credit + 10.
END.

• delcust.p, that deletes all customers with orders

FOR EACH customer:
    FOR EACH order where order.Cust-Num = customer.Cust-Num:
        FOR EACH order-line where order-line.Order-num = order.Order-num:
        DELETE order-line.
        END.
    DELETE order.
    END.
DELETE customer.
END.

3.) Run these procedures then switch the current ai extent

$   mpro mydemo -p updcust.p
$   mpro mydemo -p delcust.p
$   rfutil mydemo -C aimage new
$   rfutil mydemo -C aimage list

4.) Scan the AI extent

In this example, to see where delete operations began, RL_LSTMOD is the separator between updcust.p and delcust.p.
It is advisable to save the output in a file and use an editor to find the entry in which you are interested.

• How to view the contents of after image files?
• What do the codes reported by aimage scan verbose mean?   

...
code = RL_LSTMOD (1637) <-- db open for updcust.p
transaction index = 0 (1638)
dbkey = 32 update counter = 772 (1639)
code = RL_TBGN (1637) <-- start of first update
transaction index = 755 (1638)
dbkey = 0 update counter = 0 (1639)
Trid: 755 Tue Aug 17 08:08:06 2021
...
code = RL_TEND (1637)
..
code = RL_LSTMOD (1637) <-- db open for delcust.p
transaction index = 0 (1638)
dbkey = 32 update counter = 773 (1639)
code = RL_TBGN (1637) <-- start of first delete.
transaction index = 788 (1638)
dbkey = 0 update counter = 0 (1639)
Trid: 788 Tue Aug 17 08:08:39 2021
code = RL_IXREM (1637)
transaction index = 788 (1638)
dbkey = 6560 update counter = 74 (1639)
...

While this delete started at 788 Tue Aug 17 08:08:39 2021, if other transactions need to be included in the roll forward, also begin at this time, you will need to use the Trid to roll forward to endtrans or assure the endtime used is before the transaction end (RL_TEND) for transaction 788

$   cp mydemo.a1 mydemo.ai
$   echo y | prodel mydemo
$   prorest mydemo mydemo.bak

a) To roll forward and stop at time when deletes began:

$  rfutil mydemo -C roll forward endtime 2021:08:17:08:08:39 -a mydemo.ai [ -B n ] [ -r ]

b) To roll forward and stop at transaction where deletes began:

$  rfutil mydemo -C roll forward endtrans 788 -a mydemo.ai [ -B n ] [ -r ]

In practice, there are typically more than one ai file that needs to be rolled forward against the backup baseline. To this end the following parameters are useful:

• -oplock/opunlock : Since 10.2B, where nothing else can touch the db until it's finished with roll forward operations and you're ready and execute opunlock.
• -ailist: To provide a list of aifiles for RFUTIL to process instead of individually. When the database is enabled for AIMGT for example, the list of ai files can be parsed from the dbname.archival.log. If you have ai files that fall outside the 'endtime', it doesn't matter it stops processing ai files when the time is reached. There was a defect pre 11.6: Errors (1028) and (8999), or (4554) occur during rfutil roll forward with endtime or endtrans   

$   rfutil <dbname> -C aimage scan -ailist <ai files to apply in sequence>.txt
$   rfutil <dbname> roll forward oplock endtime 2021:08:17:08:08:39 -ailist alist.txt -MemCheck -DbCheck verbose -B n -r

Database has been locked during roll forward processes. (14072)
Write access to the database will not be allowed until the roll forward operations have completed. (14073)
...
***Beginning processing of After-image extent dbname.a13 (17047)
After-image dates for this after-image file: (1633)
    Last AIMAGE BEGIN Mon Aug 16 14:51:07 2021 (1640)
    Last AIMAGE NEW Tue Aug 17 08:00:05 2021 (1641)
    This is aimage file number 3 since the last AIMAGE BEGIN. (1642)
    This file was last opened for output on Tue Aug 17 08:00:05 2021. (1643)

Rolling forward to Tue Aug 17 08:08:39 2021. (6802)

Roll Forward to Tue Aug 17 08:08:39 2021 has been located, further roll forward operations are invalid. (17923) 
At the end of AI file processing, the number of live transactions is 3 (16845)

$   echo y | rfutil <dbname> -C roll opunlock -crStatus 10 -crTXDisplay

The database will go through crash recovery and no roll forward can be executed after this. (14077)
Are you sure you want to disable the protection on roll forward? (y/n) (14078)