Ai File Switch

[Jack@dba]

Hi,

I would like to know abt AI file switch.we will follow below procedure for taking offline backup and start databases

1 ) Shutdown database
2) Ai disable
3) offline backup
4) start AI
5) Start Database

Questions

1) If we take offline backup during last extent is in "busy" state" database is not getting shutdown.
2) If we take offline backup after that once AI enabled all Busy extent getting empty and first extent in "Busy" State.I not able to find where the Ai files has been copied.
3) If last Ai extent reached 2Gb limit only database will be shutdown or whatever space in disk occupies then only Database goes down?

Fyi........... we dont have largefile enabled to our database
Progress Version : 9.1E ( Client is more interested using old version only)
IBM AIX 5.1

And also how to monitor Ai file growth using VST?

JackDBA......

 

[cj_brandt]

1. If you follow your steps, AI would be off when you take an offline backup so option #1 can't happen. If you take a backup with AI enabled, you must have at least 1 empty extent because the backup will switch AI extents. I only took online backups with AI enabled - I am not sure if probkup switches AI extents on an offline backup.
2. When you disable AI, all AI extents are emptied - you have to manually copy the AI extents to your archive location before disable AI - or the data will be lost. You will decide where to copy the data to - Progress ver 9 doesn't have AI archiver to copy extents for you.
3. You can enable -AISTALL command so database stalls until AI extents are emptied, which allows the database to resume. That will prevent the db from shutting down if AI extents are filled.

You should enable largefiles on the Ver 9 database. Low risk and good reward.
If you are going to stay on 9.1E, get the latest Service Pack.

Look at using variable length AI extents, enable AI and consider using probkup online with AI enabled - and don't bother with enabling and disabling AI.

 

[TomBascom]

Why are you disabling after-imaging? That should not be part of your backup process. What benefit do you imagine that has?

Version 9 is of course ancient, obsolete and unsupported. You should upgrade.

 

[Jack@dba]

Why are you disabling after-imaging? That should not be part of your backup process. What benefit do you imagine that has?

Version 9 is of course ancient, obsolete and unsupported. You should upgrade.

Hi Tom,

We are doing this way as per client requested because application is not so critical we need to make sure all application are up and running.we are disabling the After image before backup and enabling After image after backup .
But before taking backup we have Ai archive script which will take copy of all Ai file backup.

Can i know Advantages and disadvantages of doing this way.

And also when database will goes down if Ai file extent reached 2Gb or whatever space in disk occupies then only Database goes down?

JackDBA............

 

[RealHeavyDude]

Any database which contains data of any value should have after image enabled. Not doing so indicates that there is either no value in the data or, maybe, it can easily reproduced.

Suppose after image is switched on:

• Whenever you start the backup ( regardless whether its off- or online ), the first thing that will happen, is an after image switch to the next empty extent. At that point you need to have an empty after extent availble, otherwise the switch and your backup will fail. This switch is absolutely necessary - otherwise you wouldn't be able to roll the archived after image extents forward against a restore of the backup.
• Therefore you need at least 3 after image extents and you need to manage them either manually ( scripted ) or automatically with after image archiving daemon. I prefer to have a few more extents ( 5 ) just in case something goes wrong to give me some additional to fix whatever might be the cause.
• Disabling the after image before starting the backup and enabling it after the backup, IMHO, does have no advantages unless you want to include other DBA maintenance tasks in your offline backup strategy that require after image to be switched off ( for example an index rebuild ). But, such DBA maintenance tasks usually are not performed on a regular basis, rather on demand. You need to manage the full after image extents yourself when the database is shut down and after imaging is disabled.
• Regardless which part of the database it is ( before image, after image or data extents ) that does not have sufficient space to write to, the database will shutdown itself abnormally requiring you to add additional space. In doing so, all clients that are connected to the database will be disconnected too and they will most likely crash.

Therefore, unless you have infinite disk space for your database at your disposal, you need to manage the free space of the file systems on which your database resides too. You wouldn't belive how fast a database can grow when the business discovery new opportunities.

Additionally, why don't you backup your database online? I don't see the advantage of an offline backup other than making the system unavailable for the time the backup takes.

You should also test your disaster recovery strategy too.

Heavy Regards, RealHeavyDude.