Oracle – for when it was like that when you got there

Main menu

Post navigation

Using Flashback for Automated Testing – a stepping-stone to World Domination

As I’m between jobs at the moment, I’ve had time to sit and reflect on possible future career directions.
With Deb’s help and inspiration (“if that washing isn’t done by the time I get back, there’ll be trouble!”) I think I may have come up with something.
Yep, I’m looking into the possibility of becoming an Evil Genius and Megalomaniac. After all, I’ve already got the alter-ego – I mean, “The Anti-Kyte”, that’s never going to be a super hero name, is it ?
Apart from that, there’s also the costume to consider. Let’s face facts here, mine is just not a figure that would be flattered by brightly coloured spandex.

If I’m going to run an efficient evil empire, I’ll need a properly designed and efficient data-driven application. Everything must be thoroughly tested. The temperature control on the piranha tank failing or some meddling super-spy escaping the cunningly planned trap at a crucial moment could do untold damage to the share price.
A suite of automated tests that I can run repeatedly to verify both functionality and performance under load is a must-have.
In that case, I’m going to need a way of running my test suite, then quickly rolling back the resultant database changes, fixing any problems and running it all over again.
It’s time to take a fresh look at the Flashback functionality available in the Oracle Database.

The Evil Masterplan

At this point I should mention that Oracle XE does not support Flashback. This is an Enterprise Edition feature apparently. Bizarrely, XE does allow you to create a restore point but when you try to issue the FLASHBACK DATABASE command you get :

ORA-00439 : feature not enabled : Flashback Database

Fear not, I’ve had the database liquidated for it’s failure and have moved on to 11g R2 Enterprise Edition.
What I’m going to do is :

make sure that the database is running in archivelog mode

make sure that the flashback recovery area is set up

setup the database structures and data for the test

turn flashback on

create a guaranteed restore point

run the tests

flashback the database to the restore point

I know, I should really get my minions to do it, but the cat is being particularly un-cooperative at the moment.

Setting up Flashback

First things first, you need to make sure that the database is running in archivelog mode. Whilst this
is de rigueur for production instances, you may well find that testing/development environments are running in noarchivelog mode so that the DBA’s don’t have to worry about managing the space that the archived redo logs take up. As you probably know, if the log_archive_dest directory runs out of space, you’ll get a message in the alert.log telling you that the archiver is stuck and the instance will, to all intents and purposes, just freeze.
Anyway, to check this, just run the following query :

select log_mode
from v$database
/

If the result returned is ARCHIVELOG then you’re good to go. If however, it’s NOARCHIVELOG, well you’re going to have to do a bit more work.
To enable archiving, we’re going to have to shutdown and mount the database. This can get a bit fiddly. As we also need to do this to turn flashback on, we’ll come onto it a bit later. In the meantime we need to…

Check the Flashback Recovery Area

The Flashback Recovery area is configured by means of two initialization parameters – db_recovery_file_dest (the location of the recovery files) and db_recovery_file_dest_size ( the size of the directory in bytes). So let’s have a look and see whether these are set :

SELECT name, value
FROM v$parameter
WHERE name like 'db_recovery_file%'
/

It looks as if both of these parameters are set by default in 11g R2 ( and XE, come to that).
We’re almost ready to go. All we need now is an application to test …

Masterplan 0.1

Here it is then, the raw database behind the evil masterplan. Just promise you won’t tell anyone…

Yes, I know that artificially generating a failure in the script is “cheating”. EVIL genius, remember ?
Anyway, we’re finally ready to enable archiving (if necessary) and to turn on flashback…

Fiddling with a mounted database

Remember I mentioned that things get a little fiddly at this point ? Well, we need to shutdown the database and then mount it. Once the database is shut down, it stops talking to the TNS Listener so any TNS connections get bounced back with :

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Therefore, you need to have a session on the machine that the database is running on. You also need to have your ORACLE_SID environment variable set to the name of the database you want to connect to.
You then need to connect without specifying a SID in the connect string. If you don’t, or your environment variable is not set correctly you will get the illuminating and helpful message :

There are a couple of things that I should really point out here ( see, I’m not all bad).
Firstly, if you change the archivelog mode, Oracle recommend you take a backup immediately as any old backups become unusable.
Secondly, the ARCHIVELOG setting persists across startup/shutdown of your database. If you want to change it, you’ll have to do so explicitly by mounting the database and issuing the appropriate command ( i.e. ALTER DATABASE ARCHIVELOG / NOARCHIVELOG depending on whether you want to enable or disable archiving).

Create a Guaranteed Restore Point

“Why not just use a common or garden Restore Point ?” I hear you ask.
Good question ( would you like a job as a minion ?)
A normal restore point will only retain any changes up to the value set in the db_flashback_retention_target initialisation parameter.
By contrast, a guaranteed restore point will hold a before-image of any blocks changed after the restore point is created. It ignores the retention target parameter.
A restore point can have any name you like ( up to 128 characters) . So, connect as sysdba and..

If you want to check the restore points currently on your database you can run the following query :

SELECT name, guarantee_flashback_database, scn
FROM v$restore_point
/

Time Travel

Now to implement our fiendish new invention. We’re going to convert the database into tachyons and then send it backwards through time…or we could just run the test suite then Flashback the database to the state it was in before we started.
First, let’s run the test…

We can now see that some data has changed. Incidentally, flashback will work for any changes to database objects, not just data. As long as you don’t hack around with the actual structure of the database ( e.g. adding a tablespace), you’ll be fine.

Now we’ve run our automated test suite, we need to get the database ready for the next run – after we’ve fixed the bugs.
So, shutdown and then mount the database again then…

FLASHBACK DATABASE TO RESTORE POINT planning_and_megalomania
/

When we open the database this time, we need to specify resetlogs to stop it getting confused by changes that we’ve just…anihilated…Mwah hah hah hah hah( look, do you have any idea how hard it is to write an evil laugh ?)

ALTER DATABASE OPEN RESETLOGS
/

And finally, we can check to see that the database is now back as it was before we ran the tests

Cleaning up after yourself

Behind every evil mastermind is a wonderful, lovely (Welsh) woman. Yes, Deb insisted that I included this bit.
The first point to bear in mind is that you need to manage the guaranteed restore points you create. As soon as you no longer need one, you should remove it. Otherwise, you’ll just fill up your flashback recovery area with block images you no longer need. So, once Masterplan 0.1 is stable and we want to be ready to test the next version :

DROP RESTORE POINT planning_and_megalomania
/

The second point is that, if you’ve only turned archiving on for the purposes of this exercise, then it’s quite possible that there’s not a whole lot of space allocated for archiving redo logs. If this is the case, and you don’t want you’re database to stop unexpectedly, then you need to turn archiving off again. NOTE – you’ll need to turn off Flashback first or you’ll get

Once again, you may want to consider taking a backup after doing this.
Well, I’d love to stay and chat but I’m on my way to the butchers to buy some fish food. Then again, I think the cat may be expendable after all…