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.

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 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;
System altered.

Ensure changes:

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

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 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;
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.

--

--

--

I am an experienced IT Consultant who has a knack to solve complex problems. Want to connect? Reach out to me on: http://www.linkedin.com/in/ali-ahmed-jdawms

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Aggregating results asynchronously with Redis cache

Shaping Engineering Culture In A Chaotic Environment — Part 1

The all-in-one app for notes, tasks, project management (not only) for Developers

Ownership in the Dongle Community

An Introduction to Monkey D. App

AWS CodeBuild Vs GitHub Actions Vs CircleCI

Looping: do-while loop In C

Reverse proxy using Netflix’s Zuul in Spring Boot

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ali Ahmed

Ali Ahmed

I am an experienced IT Consultant who has a knack to solve complex problems. Want to connect? Reach out to me on: http://www.linkedin.com/in/ali-ahmed-jdawms

More from Medium

Google Data Analyst Capstone Project: Bike-share Cyclistic Case Study.

What’s in store for the future of healthcare data?

Google Analytics Customer Revenue Prediction

Google Analytics Customer Revenue Prediction

Scraping Data and from Spotify’s API