Home » SQL & PL/SQL » SQL & PL/SQL » Transactional Migration Scripts?
Transactional Migration Scripts? [message #198847] Wed, 18 October 2006 17:40 Go to next message
gaffonso
Messages: 7
Registered: October 2006
Junior Member
The quick-summary of my questions is:

1) Is there a commonly-used best-practice for creating Oracle migration scripts that will roll-back on error?

2) Will the DBMS_JOB technique of getting DDL transactionality to work outside of a trigger?

3) Should I really avoid looking further into autonomous transactions for getting DDL transactionability? Is it really such a dangerous feature?

Here's the back-story regarding the above...

In our environment we use two separate Oracle 10g database:
1) A "prod" database which is used by our live, user-facing webapp
2) A "test" database which is used by our developers during the ongoing development of that webapp

Very often the activities of the developers will require changes to the "developer" database. Sometimes just DML-related changes but sometimes also DDL-related changes.

Currently, our developers script *all* database-related changes as they're made to the dev database. Then, when we're ready to rollout a particular development snapshot to production, we simply collect-up the appropriate scripts into a "migration script" and run that against the "prod" database to bring its schema and data up to data.

This is working relatively well but we'd like a little more help from Oracle to ensure the Migration script runs as an atomic unit. We've had a lot of instances where the migration script errors mid-way and leaves the production database in a partially updated state.

So...

We'd like the migration script to run in a transaction. That way if problems occur during the running of the script, we get a complete rollback and are not left with a partially-applied set of changes.

The problem we've run into (on Oracle) is that the migration script, because it contains DDL statements can't be wrapped in a transaction, each DDL statement forces a commit immediately after that statement.

After much Googling it's clear this is a well-known Oracle limitation. What's less clear is whether there's a commonly-used best-practice for getting around the limitation.

I've read that scheduled jobs (DBMS_JOB) can be used to get DDL-transactionality for triggers, but being relatively new to Oracle, I'm unclear if this same technique will work outside of triggers.

I've also read that autonomous transactions can provide DDL transactionality but there are so many warnings against this technique that I'm hesitant to even look much deeper.

Many thanks!

- Gary
Re: Transactional Migration Scripts? [message #198850 is a reply to message #198847] Wed, 18 October 2006 17:57 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I wouldn't even remotely consider the fact that a DDL statement issues an implicit commit before and afterwards a limitation.

Can you imagine the horrors of DDL changes (or attempted changes) against uncommitted data? Yikes.

Any rollback will go back to the end of the last explicit commit of DDL statement allowing you to fix the cause and continue from that point.

Autonomous transactions are not the answer here - perhaps just some basic script exception handling.

We'll be glad to help with some examples if needed.

[Updated on: Wed, 18 October 2006 18:01]

Report message to a moderator

Re: Transactional Migration Scripts? [message #198851 is a reply to message #198850] Wed, 18 October 2006 18:35 Go to previous messageGo to next message
gaffonso
Messages: 7
Registered: October 2006
Junior Member
>> We'll be glad to help with some examples if needed. <<

Some examples would be great!

Here's a snippet from our latest migration script:

-- Roles by site
create table abhc_data.SITE_ROLE (ID number(19,0) not null, SITE number(19,0) not null, USER_ID number(19,0) not null, ROLE_ID varchar2(255) not null, primary key (ID));
alter table abhc_data.SITE_ROLE add constraint FKSITE_ROLE_SITE foreign key (SITE) references abhc_data.SITE;
alter table abhc_data.SITE_ROLE add constraint FKSITE_ROLE_USER foreign key (USER_ID) references abhc_data."USER";
alter table abhc_data.SITE_ROLE add constraint FKSITE_ROLE_ROLE foreign key (ROLE_ID) references abhc_data."ROLE";
GRANT SELECT, INSERT, UPDATE, DELETE ON ABHC_DATA.SITE_ROLE TO ABHC_APP;

insert into abhc_data.SITE_ROLE (ID, SITE, USER_ID, ROLE_ID) select abhc_data.ID_HI_SEQUENCE.NEXTVAL, 0, USER_ID, ROLE_ID from abhc_data.USER_ROLE;
insert into abhc_data.SITE_ROLE (ID, SITE, USER_ID, ROLE_ID) select abhc_data.ID_HI_SEQUENCE.NEXTVAL, 1, USER_ID, ROLE_ID from abhc_data.USER_ROLE;
insert into abhc_data.SITE_ROLE (ID, SITE, USER_ID, ROLE_ID) select abhc_data.ID_HI_SEQUENCE.NEXTVAL, 2, USER_ID, ROLE_ID from abhc_data.USER_ROLE;


Regardless of the mechanism used (whether it be transactions or not) we need the script to run as an atomic unit: either the whole script runs or the whole script fails.

Right now, if the 3rd alter table statement fails (because we made a spelling mistake in the referenced table-name, for example) we'll still be left with a database that contains the table and two foreign key constraints within that table. In short, we get a database in a partially migrated state.

Now, the above is, perhaps, a bad example. It's not too hard to ensure that the sample script above is error-free, things like spelling-mistakes can be debugged by running the script on a test-database before running on production.

But there are scripts we have where it's much harder to guarantee a trouble-free script in advance and it's not until the script is actually run on the "prod" database that we discover we've got a coding error or some other problem we didn't anticipate.

So we're looking for some code that we can wrap around our migration scripts that:

1) ensures the migration script runs as a single atomic unit
2) automatically rolls-back the entire script if an error occurs anywhere in the script
3) supports scripts that contain both DDL and DML statements

Thanks again!

- Gary
Re: Transactional Migration Scripts? [message #198854 is a reply to message #198851] Wed, 18 October 2006 19:31 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
autonomous transactions can provide a loophole to execute DDL from triggers which is otherwise a restricted operation. Successful DDL doesn't only commit in the autonomous transaction - it's visible across the whole database immediately.

"create schema" is of some use but doesn't seem to support DML or alter statements.
-- No trailing ";" after each DDL
create schema authorization SCOTT
  create table z1 (col1 number primary key)
  --alter table z1 add(col2 varchar2(1))
  create table z2 (col1 number references z1)
  create view v2 as (select * from z2)
  --insert into z2 values (1)
  grant all on z1 to public
  grant all on z2 to public
;


I always opt to create a copy of prod in a QA area and run the upgrade script against that first. This can still go wrong in prod. Data changes, insufficient space, inability to get exclusive locks etc can all cause failures. To cover this - you need a backup of prod to be able to restore to.

One trick in SQL scripts is to include error handling at the top and have a flag file created at the end of the script. Before you run, you clean up any existing flag files and then check for the flagfile after the run. If not found - then it means you exited the script prematurely with and error - so you run a backout script.

whenever sqlerror exit 1
--lots of DML, DDL here

spool script1_ran_ok.flg
spool off

[Updated on: Wed, 18 October 2006 19:37]

Report message to a moderator

Re: Transactional Migration Scripts? [message #198913 is a reply to message #198851] Thu, 19 October 2006 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A big part of your problem is that DDL cannot (by definition) be rolled back. If you get an error inserting the data, then the table will still be there. If you create the table using DBMS_Job, it'll still be there. If you create the table in an autonomous transaction, it'll still be there.

The only way round this I can think of whould be to create an Undo script for each script, which you could run to undo the DDL of each script if it errors during the rollout.
Re: Transactional Migration Scripts? [message #198941 is a reply to message #198913] Thu, 19 October 2006 04:51 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One option might be not to use normal DDL, but Online Table Redefinitions, but that really complicates things.

That offers a way to "Abort and cleanup after errors", but not all things that can be changed with DDL can be changed that way.

For more information have a look here :

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96521/tables.htm#12458

Previous Topic: query order
Next Topic: GROUP BY
Goto Forum:
  


Current Time: Fri Dec 09 17:45:04 CST 2016

Total time taken to generate the page: 0.28341 seconds