Have you ever thought about what you would do if you deleted a user by mistake? The answer is simply that I perform a database recovery from a backup.

This solution is correct, but it is costly in terms of time, especially if the database is large. Recovering large sizes of files must be costly in terms of time.

What is the solution then? The solution is Flashback Database.

Flashback Database is returning the database to a specific point in time in the past, so that all modifications that occurred in the database from that point until now are as if they never happened. We benefit from the Flashback Database in case of logical errors, such as the database administrator accidentally deleting a user or making a Truncate Table, but in case of physical errors such as losing a Data File or other files, we cannot benefit from the Flashback Database and all we can do is restore the files from the backup in the traditional way.

The Flashback Database process is a very fast process to return the database to a period of time in the past compared to the process of restoring from backup files in the traditional way. This is because we do not need to restore the database backup files. The moment Enable Flashback Database is running, a Background Process called Recovery Writer (RVWR) is created.

A portion of the SGA memory called Flashback Buffer is allocated, and a portion of the disk is allocated for Flashback logs. This portion is always in the Flash Recovery Area.

The scenario is such that every Block that has been modified in the database is sent to the Flash Buffer. After that, the RVWR places These Blocks from Flashback Buffer to Flashback Logs.

This scenario is very similar to the Redo Log Buffer and Redo Log Files scenarios, but the difference is that not all modifications are written to the Flashback Buffer as is the case with Redo Log Buffer.

But Complete Block Images, and Flashback Logs are not archived as is the case with Redo Log Files.

As I mentioned, not all modifications are written to the Flashback Buffer. But Complete Block Images, so there are many modifications that happen and are not written directly to the Flashback Buffer.

But they are delayed and other modifications may have happened before they are written to the Flashback Buffer, what I mean is that a Complete Block may be written and it contains a different set of modifications that happened

at different times, so the Flashback Database process for a specific time is very difficult, So Flashback Database needs the database to be in Archive Log Mode

Because the archive contains all the details of the modifications that happened in the database unlike Flashback Logs, so

Flashback Logs with Archive Logs can make the Flashback Database process successful.

To configure Database Flashback, follow these steps:-

1 - Make sure that the database is running in Archive log mode.

In previous chapters, we talked about the database running at least two Redo Log Files, which are files that contain the modifications made to the database.

The LGWR Background Process writes the modifications in the Log Redo Buffer and places them in the Redo Log Files. The writing process is done periodically between the Redo Logs and is rewritten in the Redo Logs, which leads to the loss of the data in it. Therefore, the solution is to run the database in Archive Log Mode. A new Background Process called ARCn is created that makes copies of the Redo Log Files. So that we do not lose the data in these files, which we rely on in the recovery process, and without which the recovery process fails.

To know whether the database is working in Archive Log Mode or not?

SELECT LOG_MODE FROM V$DATABASE;

To prepare the database in Archive Log mode, we perform the following steps:

A- Determine the location where we will place the archive using the LOG_ARCHIVE_DEST variable, taking into account that different directions can be specified using the LOG_ARCHIVE_DEST_n variables.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\ARCHIVE\’ SCOPE=SPFILE;

As I mentioned earlier, other directions can be specified for the archive, and other variables can be configured, but they are not necessary, for example LOG_ARCHIVE_FORMAT This variable is to specify the format in which the archive file is output.

B- Close the database and open it in Mount mode, then execute the command ALTER DATABASE ARCHIVELOG.

ALTER DATABASE ARCHIVELOG;

Now the database is running in ARCHIVELOG MODE.

You can also query the operation of ARCHIVELOG MODE by:

SELECT ARCHIVER FROM V$INSTANCE;

SWITCH LOGFILE is working. The REDO LOG FILE is being copied to the archive.

ALTER SYSTEM SWITCH LOGFILE;

You can also inquire about archive files.

SELECT NAME FROM V$ARCHIVED_LOG;

2-: Initializing the Flash Recovery Area:

I mentioned earlier that Flashback Logs are only managed in the Flash Recovery Area, which is a place allocated by the variable DB_RECOVERY_FILE_DEST, and the size of this allocated place is determined

by this variable DB_RECOVERY_FILE_DEST_SIZE, so to initialize the Flash Recovery Area, we need to initialize the two variables (DB_RECOVERY_FILE_DEST_SIZE & DB_RECOVERY_FILE_DEST).

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’D:\ORACLE\PRODUCT\10.1.0\ FLASH_RECOVERY_AREA;

We have formatted the Flash Recovery Area and we can verify this by reviewing the variables (DB_RECOVERY_FILE_DEST & DB_RECOVERY_FILE_DEST_SIZE).

SHOW PARAMETER DB_RECOVERY_FILE_DEST;

3 - Determine the Flashback Log retention period:

This is before it is used and rewritten again. This is determined by the variable DB_FLASHBACK_RETENTION_TARGET, which is the retention time in minutes. The default value

is to be retained for a day.

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

4 - Close and open the database in Mount mode:

Then format the Flashback using the command ALTER DATABASE FLASHBACK ON

ALTER DATABASE FLASHBACK ON;

This is how we have finished configuring the Flashback.

To make sure that the Flashback is configured, we run the following query:

SELECT FLASHBACK_ON FROM V$DATABASE;

To monitor the flashback

SELECT * FROM V$FLASHBACK_DATABASE_LOG;

To know how to use the Flashback Database, let's follow this scenario together:

1- The database administrator deleted the user TEST by mistake.

DROP USER TEST CASCADE;

2- Close the database and open it in Mount mode, then flashback the database to the time 01-06-08:12-55-54, which is the time before deleting the user TEST.

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('17-06-08:12-55-54','DD-MM-YY:HH٤٢- MI-SS');

3- Open the database in Resetlogs mode.

LTER DATABASE OPEN RESETLOGS;

4- Finally, let's make sure that the user TEST exists.

SELECT USERNAME FROM DBA_USERS WHERE USERNAME='TEST';

But if we do not know, for example, the time of deleting the user TEST, we have no solution other than guessing and then opening the database in Read Only mode and making sure of the feasibility of the recovery. We may resort to closing the database and opening it again and then re-flashing the Flashback Database at another time, and we do not open the database directly in Resetlogs mode because the Restlogs option re-initializes the log. Sequence Number and starts taking the value (001) again.