Flashback Database Operation with Dataguard

In this post, I’ll tell you about Oracle Flashback Technologies. Where and why are they used? We’ll perform Flashback Database on Dataguard.

What is Flashback?

Flashback is returning to the past. We can see it as a security measure that can be taken against user error-based problems in the database. If the database is in the Archivelog mode, Redo records of every transaction are archived for Roll Forward. If Flashback mode is on, Roll Back records of every transaction are produced as flashback log.

In the daily life, flashback technologies are used to compromise user errors such as false tables or records or to see a table anytime. Upon shortly mentioning flashback, we’ll do a daily life operation. First, we’ll activate flashback on Dataguard. Then, we’ll reach the data at that moment by returning to that moment on a critical table.

Why Do We Operate Flashback on Dataguard?

First, it’s not quite possible to return a running database back in time in the production environment other than exceptional situations. Since that operation is generally used to recover a table or the data on it, rolling back all the database in production isn’t much favoured due to interruption and risks. It also should be known that production of flashback logs brings extra load to the database in every transaction. To prevent interruptions and extra loading in production, activate flashback on Dataguard.

NOTE: When Flashback is used on production (primary) database, if you try to return to a moment a NOLOGGING transaction was made, it’s probable that database objects and datafiles will encounter block corruption. For example, if a transaction INSERTING direct-path in NOLOGGING mode between the hours of 9 a.m. and 9:15 a.m. is running and you return to 9:07 a.m. with flashback, you may encounter block corruptions in the database. Be cautious about that if you’ll use Flashback in the primary database without Dataguard. In a system that has Dataguard, there won’t be that risk because FORCE LOGGING will already be running.

Steps to Follow in User Error-Based Data Loss

1. If a false DML transaction is made on a table and not long has passed since then, you can reach the backward data by using Flashback Query along the UNDO_RETENTION time in the production database. While setting the UNDO_RETENTION parameter on the basis of seconds to the requested value, you should be cautious about the size of the UNDO Tablespace and density of the transactions. If you determine the retention time too much, the coming transactions at that time will encounter Undo error and roll back. We’ll examine Flashback Query examples in this writing.

2. If your point of using Dataguard is data security and redundancy, it’ll be good to roll back Dataguard with the DELAY parameter you’ve determined. For example, if we have a Dataguard we’re rolling back from 12 hours ago and an user error deleting happened during those 12 hours, we can see the data on the Dataguard where Read Only is running and transfer it to production with DB Link.

3. If you saw the mistake after the determined delay time in Dataguard is finished or you apply Real Time, roll back the database with Flashback Database which is main topic of this writing. For that, stop MRP, turn on the Mount mode in the database and go to the requested moment with flashback command. Turn on the database with Read Only, upon saving the data start MRP again.

Flashback Database Operation with Dataguard

Stop MRP on Dataguard which is on “Read Only With Apply” mode and turn on the Mount mode in the database.

$ sqlplus / as sysdba

SQL> select open_mode from v$database;
SQL> recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup mount

Be sure to meet the preconditions of Flashback.

• The version should be a minimum of 10g or more.

• The database should be on the Mount mode.

• Archivelog mode should be on (if Flashback is necessary, Archivelogs can be used).

• Fast Recovery Area should be defined (db_recovery_file_dest).

If you installed Dataguard according to our post “Installing Oracle Dataguard (19c)”, that means you meet the preconditions. If there are unmet conditions, meet them and go on.

SQL> archive log list
SQL> sho parameter db_reco
SQL> sho parameter db_flashback
SQL> sho parameter undo_ret

SQL> alter database flashback on;
SQL> alter database open read only;

db_recovery_file_dest => The index where FRA files such as archivelog and flashbacklog are in.

db_recovery_file_dest_size => Size of the Recovery index.

db_flashback_retention_target => Retention period of Flashback logs (second).

undo_retention => Retention period of Undo (second).

Open the database on the Read Only mode after turning on Flashback.

Run query on v$recovery_area_usage view and see that Flashback logs are produced.

SQL> select * from v$recovery_area_usage;

Create a table in the primary (production) database and add record in it.

$ sqlplus / as sysdba

SQL> create table hr.onemli_tablo (id number, bilgi (varchar2(200));
SQL> insert into hr.onemli_tablo values (1, 'bu satiri silme!');
SQL> commit;

Start MRP in Dataguard, run query on the related table and see that it has come there.

SQL> recover managed standby database using current logfile disconnect from session;
SQL> select * from hr.onemli_tablo;

We can apply Flashback Query example here. We just created the table and let’s try running query on that table’s an-hour-ago-state with the “as of timestamp”.

SQL> select * from hr.onemli_tablo;
SQL> select * from hr.onemli_tablo as of timestamp sysdate - 1/24;

We get no result because that table didn’t exist an hour ago. You can query with it as long as the old version of the table exists in Undo. Below, you may see some examples you can query with Flashback Query.

SQL> select * from hr.onemli_tablo as of timestamp {my_date};
SQL> select * from hr.onemli_tablo as of scn {my_scn};

Before adding record on the primary database, save SCN of the system. Then, add a record and commit it.

SQL> select * from hr.onemli_tablo;
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
SQL> insert into hr.onemli_tablo values (2, 'yeni satir.');
SQL> commit;
SQL> select * from hr.onemli_tablo;

See that the added record has come to the Dataguard. Stop MRP to apply Flashback, turn on the Mount mode in the database.

SQL> select * from hr.onemli_tablo;
SQL> recover managed standby database cancel;
SQL> shutdown immediate;
SQL> startup mount

Roll back the database with the Flashback database command to SCN where the second record isn’t added yet. We can use that command both with SCN and timestamp. Open the database with Read Only, query the table at that moment. As you can guess, second record isn’t seen. Then, start MRP again, apply Real Time and you can see the current state of the table.

SQL> flashback database to scn 2421245;
SQL> alter database open read only;
SQL> select * from hr.onemli_tablo;
SQL> recover managed standby database using current logfile disconnect from session;
SQL> select * from hr.onemli_tablo;

Below, you may see flashback database commands you can use as examples.

SQL> flashback database to timestamp {my_date};
SQL> flashback database to before timestamp {my_date};
SQL> flashback database to scn {my_scn};
SQL> flashback database to before scn {my_scn};

This is how the operation ends. In this operation, before adding a record we saved SCN number of it with Flashback Database and after adding the record and applying it on Dataguard, we rolled back to that SCN number with flashback and we rolled back to the state when the record wasn’t added. The same operation can also be used to roll back to a moment before deleting a record or records or updating. It also can be used to roll back to a moment before dropping one or more tables. In short, we learned how to roll back to a moment in the database, how to query the data and recover.

---

Hope to see you in new posts,

Take care.