New DBA Series: No More AIxcuses – Part One

Unless it’s a read-only database, no OpenEdge production database should run without after-imaging. Period.

After-Imaging (AI) is an OpenEdge continuous change logging system that stores database changes in specially formatted log files. It allows the DBA to restore a database from a backup then apply all changes from the end point of the backup to the point of the last AI archived change. AI is required to:

A) update a second, identical database, synchronizing both databases to a point in time or transaction (commonly called a warm spare).

-AND/OR-

B) significantly reduce data loss and down time in the case of a data recovery event such as a system crash or human error (happens to the best of us).

It’s almost unbelievable, but even today we still come across production databases with AI disabled. Relying solely on daily backups was never acceptable and today there are simply no AIxcuses!

Recipe for Recovery

One full, valid (tested) backup plus all the AI files generated since the backup was created result in a recovered database with near-zero data loss. Without AI, if your database needs recovering, all transaction creates/updates/deletes since the last backup are lost.

Do yourself a favor – set aside time to do a realistic cost analysis on lost data. Determine how much data and what data would need to be re-created in the database. Determine how much data would be impossible to re-create (ex: orders from a website). Calculate the cost of idle personnel and lost opportunities. It will be more than you expect.

The after imaging subsystem is included in OpenEdge Workgroup and Enterprise Database licenses. There is no extra cost to use it.

After Imaging Basics: the Transaction Process

    • Begin Transaction
    • Data created, updated, deleted
    • Before and after values written to BI file
    • Before and after values written to AI file
    • Repeat
    • Commit or undo

Write-ahead logging (WAL) ensures that database changes are first written to the before image, then the after image, before being written [eventually] to the data files.

What happens to all these BI/AI notes? OpenEdge automatically manages the re-use of BI file space. AI files need archiving before OpenEdge allows re-use of them.


Sounds simple enough:When full, archive each AI file to a safe location. Then mark the file as empty, available for re-use.

What’s the problem?

Excuse Problem #1 – Scripting

The commands are simple enough.

rfutil <dbname> –C aimage extent full

cp <dbname>.a<ext#> /archive/<dbname>.ai.<seq#>

rfutil <dbname> -C aimage empty

Creating the automatic logic around them requires work and scripts to automate. AI file management can be as simple or complex as you like. How “smart” do you want them to be?

Or you can use the AI File Management Daemon included with OpenEdge for the past 15 years. With the AI File Management Daemon, the only scripting you have to do is copying the AI archive from the local archive directory to long-term storage or another server, and purging AI archives older than a couple of days (I like to keep at least 2-3 days on hand).

Excuse Problem #2 – Performance Impact

In Ancient History? … yes, the impact on performance was significant. Now? The effect is minimal unless you made poor hardware decisions. There’s a free check using the syncIO metric in ProTop: values above 10 are potentially problematic.

Resist “If It Ain’t Broke” Mentality

If you’re not running AI, your system is broken. With modern hardware, the performance issue is moot. And again, with the AI File Management Daemon, the scripting issue is history.

The AI File Management Daemon

Available as of v10.1.a

Automates the rotation and re-use of AI files, archiving of full extents, the <ahem> intelligent <ahem> naming of archived AI extents (more on this later).

One of three modes

  1. Time interval – AI file rotation occurs every x seconds. Minimum of 2 minutes, maximum of 24 hours.
  2. Full AI File – Rotation occurs when the currently active AI file fills.  Used with fixed length AI extents.
  3. Both

Two configurable parameters

  1. aiarcdir: Directory where full AI extents are copied. Multiple directories can be specified in case writes to the first directory fail.
  2. aiarcinterval: Time between AI file rotations. If not specified, rotation occurs only when the current AI file fills.

How the AIFMD works

The daemon wakes up, checks for full AI files, or elapsed aiarcinterval. Full AI files are copied to the archive directory and renamed appropriately. Once successfully copied, the daemon marks the AI files as empty and available for re-use.

Enable AI & the AI File Management Daemon

Step 1: Create add.st file with 8 or 16 variable length AI files. Example:

 
a /db/prod
a /db/prod
a /db/prod
a /db/prod
...

Step 2: Add AI files to database

Offline:

prostrct add <dbname> add.st

Online:

prostrct addonline <dbname> add.st

Step 3: Take a backup

Offline:

probkup <dbname> /backup/<dbname>.pbk -com

Online: see below.

Make sure to test this backup.

 

Step 4: Enable after-imaging
Offline:

rfutil <dbname> –C aimage begin

Online: see below.

 

Step 5: Enable AI Management Daemon
Offline:

rfutil <dbname> –C aiarchiver enable

Online: see below.

 

Steps 3-4-5 Online:

probkup online <dbname> /backup/<dbname>.pbk enableai enableaiarchiver -aiarcdir /aiarch,/aiarch2
-aiarcinterval 900 -com

Start your database. Be sure to include the –aiarcdir and, if required,
-aiarcinteval startup parameters

proserve <dbname> –pf <dbname>.pf –aiarcdir /aiarch,/aiarch2 -aiarcinterval 900

Handy AI Archiver Commands

To permanently disable the AI Archiver:

rfutil <dbname> –C aiarchiver disable

To stop the daemon temporarily without disabling the functionality (i.e. for maintenance):

rfutil <dbname> –C aiarchiver end

To restart the AI Archiver:

_mprshut <dbname> -C aimgt –aiarcdir <dir> -aiarcinterval <n seconds>

Notice the similarities with the other database helper processes such as the AIW and BIW:

Changing Parameters Online:

rfutil <dbname> –C aiarchiver setdir /aiarchnew_1,/aiarchnew_2
rfutil <dbname> –C aiarchiver setinterval 1800

The second option is useful in decreasing AI file rotation intervals during peak activity or increasing interval during low activity.

CAREFUL: There is no “rfutil db –C aiarchiver begin”.

Stay Tuned for Part 2

That’s it for Part One. Don’t Touch That Dial, Stay tuned for No More AIxcuses – Part Two where we’ll discuss restoring and rolling forward.

No Comments

Post A Comment

Related post...