Oracle Flashback — A How to Guide (Part 1)

While some of you may be aware of the Oracle Flashback feature and understand what it does. I will discuss its practicality and help you enable it on your Oracle databases connected to JDA WMS instance(s) and hopefully inspire you to use this feature more often, as needed.

There have been far too many times when most of us, as a Functional User or a Developer have accidentally pressed the wrong button or executed an incorrect script or SQL statement causing to change or lose data incorrectly from either using JDA SCE Client, Oracular MOCA Client or LextEdit.

I know the pain, the moment you realize what you have accidentally done and thinking about your options now — I know, we’ve all been there. Sometimes, the accident happening on a Production JDA WMS system can be a show stopper and can cause massive setback unless data is restored immediately. This is where Oracle Flashback comes in. Oracle Flashback has the following benefits:

1. Allows you to restore data from Flashback Query instantly — instead of traditional backups option and mostly without the need of a Database Administrator
2. It’s not complex — it provides the ability to efficiently recover data deleted or lost.
3. Ability to restore selective data and allowing the database to restore to a previous point in time.
4. Allows a user to view previous versions of a table.

Khurram Ahmad (CEO of Smart IS) has spoken about the concept of flashback as far as JDA WMS is concerned.

Using Oracle’s Flashback Query Concepts in JDA/RedPrairie Implementations

However, in this guide, I will show you how it’s done. So, without further ado, let’s begin.

HOW TO ENABLE FLASHBACK in ORACLE

The following steps enable archivelog mode and flashback feature for an Oracle database — it’s OK to test on your practice instance. Please note that archivelog mode consumes a lot of disk space, depending on:

1. The size of the database
2. The amount of activity

So, please ensure that you have enough space to be able to use this feature and have discussed and approved by your Database Administrator. Also please note, that your database version needs to be 10g or greater having EE (Enterprise Edition). This feature is not provided in an SE (Standard Edition) database.

To check if you have the required Edition and options/feature enabled:

SQL> select * from v$version;
SQL> select * from v$option where parameter like 'Flashback%';

Backup Pfile:

It would be best that you, as a Database Administrator take a backup of the pfile.

SQL> create pfile=’backuppfile20181107.ora’ from spfile;

Default directory is $ORACLE_HOME/database/

To Find $ORACLE_HOME:

SQL > sys_context(‘userenv’,’oracle_home’) oracle_home from dual;
 — — OR — — 
SQL > var OH varchar2(200);
SQL > EXEC dbms_system.get_env(‘ORACLE_HOME’, :OH) ;
SQL > PRINT OH

Ensure flashback recovery settings by querying ‘v$database’:

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------
NO

Archive log list: Database is in ‘No Archive Mode’:

SQL> archive log list
Database log mode            No Archive Mode
Automatic archival           Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   2696
Current log sequence         2698

Check existing parameters of db_recovery:

SQL> show parameter db_recovery
NAME                        TYPE           VALUE
---------------------------------------------------------------
db_recovery_file_dest       string        D:\data\fast_recovery_area
db_recovery_file_dest_size  big integer   6795M

Set db_recovery_file_dest system parameter for flashback recovery file destination:

SQL> alter system set db_recovery_file_dest=’F:\app\oracle\recovery_area’ scope=both;
System altered.

Set db_recovery_file_dest_size system parameter for total size limit for flashback recovery:

Please discuss with your Database Administrator before setting this and other parameters discussed here.

SQL> alter system set db_recovery_file_dest_size=100G scope=both;
System altered.

Ensure changes:

SQL> show parameter db_recovery_file_dest
NAME                        TYPE         VALUE
--------------------------------------------------------------
db_recovery_file_dest       string       F:\app\oracle\recovery_area
db_recovery_file_dest_size  big integer  100G

Shutdown database:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup your database in mount state:

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.2046E+10 bytes
Fixed Size 12471040 bytes
Variable Size 3254781184 bytes
Database Buffers 8757706752 bytes
Redo Buffers 21082112 bytes
Database mounted.

Configure your database in archivelog mode by issue the following:

SQL> alter database archivelog;
Database altered.

Alter database in open mode:

SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

The Database has been successfully configured in archive log mode:

SQL> archive log list
Database log mode               Archive Mode
Automatic archival              Enabled
Archive destination             USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence      2697
Next log sequence to archive    2699
Current log sequence            2699

Turn on flashback recovery by issuing following command:

SQL> alter database flashback on;
Database altered.

Ensure changes:

SQL> SELECT NAME, FLASHBACK_ON FROM V$DATABASE;
NAME    FLASHBACK_ON
----------------------
ORCL    YES

Click here to read the 2nd part of this guide.