Applying schema changes to warm spare

[jennid]

Hello-We have a warm spare disaster recovery site. We sync our after image files to it and roll them forward via scripts.

If we make schema changes to the source (production) db, what is the process for applying those changes to the target (DR) database? In the past we've always ran a backup of the production db, copied it to the DR site and rebuilt the DR db. I'm guessing there is a better way to do this.

Thanks.

Jenni

 

[Rob Fitzpatrick]

The schema changes in production are done in the context of transactions, so (assuming AI remains active during the changes) they will be rolled forward to DR. Obviously though, this doesn't apply to structure changes.

 

[jennid]

We do leave AI running during schema changes. For some reason I was under the impression they didn't roll to the DR. Good to know.

What is best practice for applying structure changes?

 

[RealHeavyDude]

Let me rephrase your question: What is the best way to deploy schema changes?

There is one big factor that you need to consider when you deploy schema changes: The CRC - usually you are not deploying source code that needs to be compiled at each site to which you deploy. Instead, you deploy the .r files and therefore the CRC of the database against which you compile your programs must match the CRC of the target database of deployment after the schema changes have been applied there.

To cope with that we implemented a process that involves several databases:

1. Modify the database schema on your local development database located on your local machine.
2. Create a delta data definition file.
3. Use that delta definition file to deploy the schema change to the central development database located on the development server.
4. A new release is extracted from the source code management system.
5. Create a delta data definition file between the central development database and a so-called CRC database, also located on the development server, that solely holds the database schema - no data.
6. Use that delta definition file to deploy the schema change to the CRC database.
7. Compile the release against the CRC database.
8. Deploy the compiled programs and the delta data definition file to the relevant sites.

Following that process we never had any issue on deploying database schema changes together with the compiled programs. I'm not saying it is the only way - but it works for us without a fuzz as long as the developers adhere to it.

Heavy Regards, RealHeavyDude.

 

[cj_brandt]

applying structure changes will depend on if you use fixed length extents or variable length extents.

If your prod db writes to variable length extents, then the new extent is simply added in between adding AI files to the warm spare. Almost all OE databases write to var length extents.

If your prod db writes to fixed length extents and there is a single variable length extent as the last file in an area, then it changes a bit. The DBA normally removes the var length extent in prod and then addes additional fixed length extents and then adds a var length extent at the end. The warm spare can not truncate the bi file without breaking the roll forward process, so it can't remove the variable length extent. The warm spare database just applies fixed extents without removing the variable. So the extents get off a bit in their numbering.

remember to run prostrct list <DBNAME> after updating the db structure.