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:
- Allows you to restore data from Flashback Query instantly — instead of traditional backups option and mostly without the need of a Database Administrator
- It’s not complex — it provides the ability to efficiently recover data deleted or lost.
- Ability to restore selective data and allowing the database to restore to a previous point in time.
- Allows a user to view previous versions of a table.
Using Oracle’s Flashback Query Concepts in JDA/RedPrairie Implementations
Oracle offers a very powerful concept through its Flashback Technology…
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:
- The size of the database
- 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%';
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
Archive log list: Database is in ‘No Archive Mode’:
SQL> archive log listDatabase 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_recoveryNAME 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;
SQL> show parameter db_recovery_file_destNAME TYPE VALUE
db_recovery_file_dest string F:\app\oracle\recovery_area
db_recovery_file_dest_size big integer 100G
SQL> shutdown immediate;Database closed.
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
Configure your database in archivelog mode by issue the following:
SQL> alter database archivelog;
Alter database in open mode:
SQL> alter database open;
Database altered.SQL> select open_mode from v$database;
The Database has been successfully configured in archive log mode:
SQL> archive log listDatabase 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;
SQL> SELECT NAME, FLASHBACK_ON FROM V$DATABASE;NAME FLASHBACK_ON
Click here to read the 2nd part of this guide.