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…

CREATE TABLE evil_schemes (
    scheme_name VARCHAR2(100) PRIMARY KEY,
    description VARCHAR2(4000))

CREATE TABLE evil_scheme_steps(
    step_no NUMBER,
    es_scheme_name VARCHAR2(30) REFERENCES evil_schemes(scheme_name),
    step_name VARCHAR2(30) NOT NULL,
    description VARCHAR2(4000),
    complete_flag VARCHAR2(1),
    CONSTRAINT ess_pk PRIMARY KEY( step_no, es_scheme_name))

And now for some data :

INSERT INTO evil_schemes( scheme_name, description)
VALUES('WORLD DOMINATION', 'Today WordPress. Tomorrow...the world!')

INSERT INTO evil_scheme_steps(
    step_no, es_scheme_name, step_name,
VALUES( 1, 'WORLD DOMINATION', 'Recruit Minions', 
    'Ask Deb if I can borrow the cat')

INSERT INTO evil_scheme_steps(
    step_no, es_scheme_name, step_name,
VALUES(2, 'WORLD DOMINATION', 'Volcano shopping',
    'Contact Estate Agents about available hollowed out volcanos')

INSERT INTO evil_scheme_steps(
    step_no, es_scheme_name, step_name,
    'Check Amazon for a copy of 1001 Amusing Ways to Kill a Spy')

INSERT INTO evil_scheme_steps(
    step_no, es_scheme_name, step_name,
    'Speak to the bank about getting a small business loan')

…now add a program that we can test…

    i_scheme_name evil_scheme_steps.es_scheme_name%TYPE,
    i_step_no evil_scheme_steps.step_no%TYPE,
    i_complete evil_scheme_steps.complete_flag%TYPE) IS

    UPDATE evil_scheme_steps
    SET complete_flag = i_complete
    WHERE es_scheme_name = i_scheme_name
    AND step_no = i_step_no;

Finally we need an automated test script…

set serveroutput on size 1000000
    l_count PLS_INTEGER;
    l_scheme evil_scheme_steps.es_scheme_name%TYPE := 'WORLD DOMINATION';
    l_complete evil_scheme_steps.complete_flag%TYPE := 'Y';
    l_msg VARCHAR2(500);
    SELECT COUNT(*) INTO l_count
    FROM evil_scheme_steps
    WHERE es_scheme_name = l_scheme;
    FOR i IN 1..l_count LOOP
        IF i != 4 THEN
                i_scheme_name => l_scheme, i_step_no => i, 
                i_complete => l_complete);
            l_msg := 'Step '||i||' successful.';
           l_msg := 'Step '||i||q'[ failed. Bank didn't like the business plan.]'; 
        END IF;

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 :

ORA-12612 TNS: net service name is incorrectly specified

The database I’m using is running on a Linux Server so….

export ORACLE_SID=db
sqlplus sys as sysdba

SQL*Plus: Release Production on Thu May 26 11:23:47 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


…where db is the SID you want to connect to.
Now to do a shutdown and then mount the database

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  816054272 bytes
Fixed Size		    1339600 bytes
Variable Size		  562040624 bytes
Database Buffers	  247463936 bytes
Redo Buffers		    5210112 bytes

Database mounted.


If you need to put the database into archivelog mode …


…and to enable Flashback …


Once you’ve made the changes, you can bring the database back up with


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…

SQL> @upd_ess_ut.sql
Step 1 successful.
Step 2 successful.
Step 3 successful.
Step 4 failed. Bank didn't like the business plan.

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.

SQL> SELECT step_name, complete_flag
  2  FROM evil_scheme_steps
  3  WHERE es_scheme_name = 'WORLD DOMINATION' 
  4  /

-------------------- ----------
Recruit Minions	     Y
Volcano shopping     Y
Research	     Y


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…


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 ?)


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

SQL> SELECT step_name, complete_flag
  2  FROM evil_scheme_steps
  3  WHERE es_scheme_name = 'WORLD DOMINATION' 
  4  /

-------------------- ----------
Recruit Minions	     
Volcano shopping     


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

ORA-38774: cannot disable media recovery - flashback database is enabled

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…


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.