I am working on Oracle 11g and facing an issue. One of my applications is throwing an error message saying “unable to create Flashback”. I am trying to create flashback of a database table but it is not working. The only change that I did was to change a data type of a column from nvarchar to varchar, and after this change it stopped working correctly. I have also searched on internet, but not able to find a solution. Kindly help.
How to create Flashback of a database in Oracle?
Oracle restore points and Flashback Database are associated data protection features that let you wind back or reverse data back in time to fix any issues caused by user errors or logical data corruption within a specified time window.
In Oracle database, Flashback technology is an array of features that allows you to easily view previous or past states of your data or to move or transfer your database objects to a previous state without using point in time media recovery. To enable Flashback in Oracle database 11G R1 and earlier versions, follow these. First, your database has to be in ARCHIVELOG mode.
To do this, you need to check first the log mode in Oracle database. See image below.
In the image above, it shows that the database is in NOARCHIVELOG mode. There are 2 types of logging modes in Oracle database: ARCHIVELOG and NOARCHIVELOG. To change to ARCHIVELOG mode, do these. First, if you require, you can set the archive log destination to where you want to save the archive logs whether to multiple locations or to a single location.
If this is not set, Oracle will save the archive log files in DB_RECOVERY_FILE_DEST location if it is set. But if you haven’t set yet your DB_RECOVERY_FILE_DEST location, you must set your archive location before changing your database to ARCHIVELOG mode. See image.
Take note, “to change this parameter while database is open, your database has to run with SPFILE, if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE”, according to amit7oracledba.blogspot.in. Once this is done, you must shut down your database and restart in MOUNT mode. See image.
Once your database has been changed to ARCHIVELOG mode, you need to configure Flash Recovery Area.
In the image, Flashback is currently disabled. To enable, set first db_recovery_file_dest_size initialization parameter.
Once db_recovery_file_dest_size parameter has been set, create a location in your operating system where FLASHBACK logs will be stored.
After that, set db_recovery_file_dest initialization parameter.
Once it is done, create an Undo Tablespace that has enough space to keep data for Flashback operations. Usually, the more frequent users update the database, the more space is required. Next, check if automatic Undo Management is enabled or disabled. Automatic Undo Management is enabled by default. In case it is not, you must enable it.
In Oracle database 10G R2 and later, the default value for Undo Management is Auto. If you are using an earlier release, do these. See image.
Next, shut down your database.
Next, restart your database in MOUNT mode.
Finally, change the Flashback mode of the database.
And you are done. Flashback is now enabled in your database.
