Transcript Progress After-Imaging

Progress
After-Imaging
®
Saving your data as you go
Agenda






Introduction
What is AI and why do you need it?
Issues & Gotchas
How it works
Decisions .. Recommendations &
Implementation
Resources
About Me









30+ years experience
Prior Developer
Progress since 1984
DBA since 1996
Syteline & QAD
Dan Foreman 2x
WMPUG, ACATEC, and OAISD
Owner & founder of BBW
Part-time employee at Trendway









Founded 2003
5 employees + contractors
600+ customers; residential, SMB, corporate
Progress Database services
Server Administration
Networking
Repair
Web Design
General Business Support
About you




Using AI?
Issues with AI?
DB Training?
Dan Foreman’s courses?
What is AI?


Transaction Logging
Enhances your existing backups
Protecting your DB



BI file protects against failed transactions
Mirroring protects against hardware failure
AI protects against Human Failure
Why?





Read-only report database
Live spare
24*7 Operation
Diagnostics; to determine active transactions at
the time of a crash
Recovery in the event of BI or DB disk failure
Issues with AI








It changes everything
Increased administrative burden
Cost of additional storage space
Increased recovery time
Works but requires manual intervention on < 91C
If DB crashes during roll forward, you have to start over
If extents are out of sequence, you cannot roll forward
If the database ‘stalls’





The last extent is busy – not full
Client processes do not receive warning
No information in promon or vst’s to indicate the db is stalled
Minimal performance hit
Can’t use it if you touch it
Known Gotchas

You can’t apply more than 65,535 AI files
@ one per hour = 7½ years
 @ 5 minutes = 227 days



Must end and restart AI
Massive processing (archiving) can blow AI…
(This Slide courtesy of Scott Dulecki, Bravepoint)
How it works
BI Files
PROD
DB
SPARE
DB
Transaction
Logs
Special Thanks!
The next few slides were taken from Scott
Dulecki’s “Basic AI” presentation. To obtain a
copy of Scott’s presentation:
Scott M. Dulecki
Bravepoint
616/957-3184
[email protected]
BI Structure
S
ΔS
Δ
Δ Δ
EΔ
Δ
Δ
Δ
Δ
BI Expansion
S
BI Back Out
S
ΔS
Δ
Δ Δ
EΔ
Δ
Δ
Δ
Δ
AI logs the completed
transactions from the BI
file
Decisions … Decisions


How do you plan to use it?
How much data?



(# Checkpoints * BI Cluster Size)
Frequency of backups
Variable or fixed extents?





Without a variable extent, it requires more babysitting
Fixed extents occupy space – even if that space is not used
No way to truncate a variable extent unless you shutdown the
db
Possible 2GB file issue with variable extents
Variable extents work better with time based switches
Recommendations



Separate disk controller and disk (at minimum on it’s
own disk)
Storage space for AI files
Minimum 4 extents


Disable AI when:




Active, Full, Locked (in use), and available
Data Load, data purge, or schema change
Crash Recovery
Offline backup
Test your recovery process to make sure it works

Use no integrity (-i) to reduce roll-forward recovery time
Implementation


DB Structure
Set parameters to manage it
-aibufs (equal to 1.5* bibufs)
 Proaiw
 -aiblocksize (same as –biblocksize)
 -aistall


Turn it on
RFUTIL
rfutil db –C aimage <command>









Begin
End
extent full (checks to see if there is a full extent prior to a
switch)
extent empty (empties an AI extent)
extent list (shows current extents and the status of each)
Mark backedup
Scan
Extract
Roll Forward
Switching Extents
set DLC=c:\progress\83E
set IDLC=c:\progress\83E
set PROCFG=%DLC%\progress.cfg
set PROMSGS=%DLC%\PROMSGS
set IPROMSGS=%DLC%\PROMSGS
set PATH=.,%DLC%,C:\windows
setlocal
set dbdir=g:\symdb\%1
set bkpdir=h:\bkp\%1
set bkpdir2=\\<server>\h$\bkp\%1
set log=%bkpdir%\%1.aiswitch.log
set tmp-file=%1full.txt
Capturing the Date
REM Capture current date variables
for /f "tokens=1,2" %%u in ('date /t') do set d=%%v
for /f "tokens=1" %%u in ('time /t') do set t=%%u
if "%t:~1,1%"==":" set t=0%t%
set cc=%d:~8,2%%
set yy=%d:~6,4%%
set dd=%d:~3,2%%
set mm=%d:~0,2%%
set hh=%t:~0,2%%
set mn=%t:~3,2%%
set ss=%t:~6,2%%
set ts=%yy%%mm%%dd%%hh%%mn%%ss%
echo %date% %time% ==============>> %log%
Checking status
G:
cd %dbdir%
REM Chk DB Status
call %DLC%\bin\proutil -db %1 -C busy
IF errorlevel 6 goto :LOOP
echo %1 is not running >>%log%
exit
:LOOP
REM CHECK FOR FIRST FULL EXTENT
%COMSPEC% /C %DLC%\bin\rfutil %1 -C aimage full 1> %tmp-file% 2>>%log%
if %errorlevel%==2 goto END
Copy & Empty
REM COPY THE FILE AND RENAME IT
for /f "skip=1" %%A in (%tmp-file%) do (if exist %%A (for %%F in (%%A) do copy %%F
%bkpdir%\"%%~nF_%ts%%%~xF") )
for /f "skip=1" %%A in (%tmp-file%) do (if exist %%A (for %%F in (%%A) do copy %%F
%bkpdir2%\"%%~nF_%ts%%%~xF") )
for /f "skip=1" %%B in (%tmp-file%) do (if exist %%B (for %%F in (%%B) do echo Copied %%F
"%%~nF_%ts%%%~xF" to aibackup >> %log%) )
REM EMPTY THE FILE
%COMSPEC% /C %DLC%\bin\rfutil %1 -C aimage empty >> %log%
echo/>nul >> %log%
goto :LOOP
:END
%COMSPEC% /C %DLC%\bin\rfutil %1 -C aimage new >> %log%
echo/>nul >> %log%
echo/>nul >> %log%
del %tmp-file%
Moving Files



To a local backup directory on the server
To a 2nd server (where a live spare resides)
To an external HDD
Roll Forward
%COMSPEC% /C _rfutil.exe %db% -C roll
forward -a %ai%.ai%ai-num% del /q
%ai%.ai%ai-num%



Restore from backup
Roll forward AI extents (must be in sequence!)
Can stop before the end
Resources




PSDN: AI Documentation
OE 10.1B – AI Archiver
PEG Utilities: Adam Backman’s DBA Scripts
Dan Foreman: DBA Resource Kit




AI Checklist
AI (Unix) Shell Scripts
AI Extent Scanner
Dmitri Levin’s Scripts:

http://www.geocities.com/ResearchTriangle/3737/progress.
html
Questions

Q. During the meeting, I was asked why I use DOS
batch files (why not a scripting utility) and why so many
variables?

A. The answer is that I work on several different systems, I
don’t always have the option of installing software on the
customer’s system and quite frequently have to use what they
have to work with. The thing that is always available on a
windows machine is DOS. On Unix, it’s VI. The use of
variables enables me to identify a few things at the start of the
script and not have to make changes to the body of the
script.
? Questions ?
Betty Hardin
Big Blue Water
[email protected]
(269) 561-5517