Having a good backup strategy before performing all kind of upgrades are essential when things are going wrong and you have to revert to a previous stage.
This is not only important when upgrading infrastructure software like Oracle Database, but for all upgrades, especially with application software upgrades.
I’ve been involved in several Oracle Retail upgrades, which is one of the most complex and time consuming upgrades you can find, partly due to many customizations that clients need to implement.
Although upgrade scripts have been written in re-executable fashion, the main problem is with user customization scripts and testing those scripts on dev/test environments.
In this blog I’ll describe well known, but also not often used feature of Oracle database: Flashback Database.
Introduced with 10g version of Oracle RDBMS, Oracle Flashback Database many DBAs avoid to use, partly due to additional write overhead and partly because of duplicating functionality with RMAN backup strategy, especially for point in time recovery (although Flashback Database functionality cannot help in case of medial failure, where good old backup is the only way to help you).
I don’t want to go in detail, but even if you administer Db on busy system, there are circumstances when Oracle Flashback feature is handy.
This is how to setup Flashback Database feature.
First you need to check if Db is in archivelog mode.
select log_mode from v$database;
NOARCHIVELOG
If it is not, then you have to change to archivelog
sql> shutdown immediate
sql> startup mount
sql> alter database archivelog;
sql> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
sql> alter database open;
sql> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
select log_mode from v$database;
NOARCHIVELOG
If it is not, then you have to change to archivelog
sql> shutdown immediate
sql> startup mount
sql> alter database archivelog;
sql> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
sql> alter database open;
sql> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA/mom1p/archlog
Oldest online log sequence 576
Next log sequence to archive 579
Current log sequence 579
Second prerequisite is to use FRA:
sql> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 300G
Last step is to set Db in flashback mode.
sql> alter database flashback on;
Database altered.
You can confirm that your Db is in flashback mode by executing the following query:
sql> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
If you get the following error:
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ora-38708: not enough space for first flashback database log file
you'll need to increase amount of space for FRA:
alter system set db_recovery_file_dest_size = 10g scope = both sid = '*';
sql> shutdown immediate
sql> startup mount
sql> alter database flashback on
sql> alter database open;
To disable flashback mode, you can execute the following statement:
sql> alter database flashback off;
Database altered.
sql> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
You can notice that enabling/disabling Flashback Database feature is online operation.
Now when you are more comfortable with the Flashback Database feature, it’s time to return to the main topic: upgrade of critical business apps (such as Oracle Retail).
I recommend to leave Flashback feature in disabled state until the time for upgrade business apps or Db.
Then simply enable it by executing the following command:
sql> alter database flashback on;
Database altered.
For this demo, I’ve created tflashback_test table:
create table tflashback_test
tablespace users
as
select table_name, tablespace_name from user_tables where rownum < 10;
which has 9 records:
select count(*) from tflashback_test;
9
Then I have to create guarantee restore point, which will allow me to switch back to a state before upgrade begin.
To create guarantee restore point, issue the following command:
sql> create restore point before_upgrade guarantee flashback database;
Restore point created.
To list existing restore points
select * from v$restore_point;
sql> select name from v$restore_point;
NAME
----------------------------------------------------------------------------------------------------
BEFORE_UPGRADE
Let's simulate inserting another 9 records into the tflashback_test table:
sql> insert into tflashback_test select table_name, tablespace_name from user_tables where rownum < 10;
9 rows created.
sql> commit;
Commit complete.
Now table tflashback_test has 18 records.
select count(*) from tflashback_test;
18
To flashback on restore point:
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> flashback database to restore point before_upgrade;
RMAN> alter database open resetlogs;
Let's check if the table tflashback_test really has 9 records as it had before starting the upgrade.
select count(*) from tflashback_test;
9
As you can see, state of the table tflashback_test has been reverted into the state before upgrade (insert in this case).
Flashback affects all database objects like packages, types etc.
To drop restore point:
sql> drop restore point before_upgrade;
Restore point dropped.
There are other variations of Flashback Database features like:
flashback database to scn 961099667;
flashback database to time 'sysdate-2';
flashback database to time "to_date('23.06.2014 13:00:00','dd.mm.rrrr hh24:mi:ss')";
flashback database to before flashback database to restore point before_upgrade;
but the most useful command is definitely:
flashback database to restore point before_upgrade;
as you can control exactly to which scn you'll be revert changes.
Main question is why not to use Flashback Database feature all of the time.
In case when you have batch processing or ETL etc. , Flashback overhead is, according to my measurements, significantly slower (2 times or even more) comparing with running the same load without Flashback enabled.
The main benefit of using Flashback Database feature is very fast point in time recovery (many times faster) comparing with classic rman restore/recover point in time recovery.
By keeping Flashback Database feature in disable state, and enable it only during the upgrades (Oracle Retail upgrades, database upgrades…), you won’t have slower system due to additional logging, and you’ll get very fast point in time recovery if/when something will go wrong during the upgrade process.
Like many other features, Flashback Database is just another example where you have to think about cases which will benefit the most.
For a sake of completeness, changes in Middle Tier (WebLogic, OSB, apps…) can be easily reverted either by using Volume Group snapshots/storage snapshots, or by saving apps state before upgrading on file system.
Comments