Let us pick up where we left off in our discussion and drill deeper into the technology from Part 1 in the Power of Oracle Database 23ai with Data Guard and Flashback in Part 1 of this series: <Link to the previous article of the series >

Time to play

Now that we have examined Oracle Data Guard and Flashback and the new features and functionalities introduced within Oracle Database 23ai, it is time to see these features in action in a simple yet typical use case. In this scenario, we will investigate whether it is possible to utilize Flashback (Query and Table) after a failover to a standby database. Specifically, as the incarnation changes, can we use Flashback to see what occurred directly before the failover?

First, let us check the database incarnation details within the primary database.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES  STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED CON_ID
------------ ----------------- --------- ----------------------- ---------  ------- ------------ ------------------ -------------------------- ----------  
1            1                 14-FEB-24 0                                  PARENT  1144840863   0                  NO                         0
2            1343420           08-FEB-25 1                        14-FEB-24 CURRENT 1155034180   1                  NO                         0

The primary database’s current incarnation is 1155034180. Next, as you can see below, we will set the undo tablespace for a retention of 86400.

SQL> alter system set undo_retention=86400;

System altered.

We will set the session to use the HR schema, check the current timestamp, execute an update command, and commit the transaction.

SQL> alter session set current_schema=HR;

Session altered
SQL> select systimestamp;

SYSTIMESTAMP

---------------------------------------------------------------------------

12-FEB-25 10.24.56.272744 AM +00:00
SQL> update hr.employees set HIRE_DATE=sysdate where employee_id=100;

1 row updated.
SQL> commit;

Commit complete.

It is now time to use Flashback Query to see the value of hire_date as it was one hour ago and then compare it with its current value.

SQL> select hire_date from hr.employees as of timestamp systimestamp-1/24 where employee_id=100;

HIRE_DATE
---------
17-JUN-03 
SQL> select hire_date from hr.employees where employee_id=100;

HIRE_DATE
---------
12-FEB-25

As we can see above, the previous value for hire_date was 17-JUN-03, and now, after our committed transaction, the new value is 12-FEB-25. We will check for the current timestamp to save this as a reference for when our primary database went down and when the failover started.

SQL> select systimestamp;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-FEB-25 10.37.28.908251 AM +00:00

We will kill the database’s pmon process to force the primary instance’s crash.

[ fmunoza_main ] bash-4.4$ ps -eaf | grep pmon

fmunoza 1112986       1  0 FEB12 ?        00:00:49 ora_pmon_main222
fmunoza 1485907       1  0 10:29 ?        00:00:00 ora_pmon_main22
fmunoza 1486768 1484883  0 10:37 pts/0    00:00:00 grep pmon
[ fmunoza_main ] bash-4.4$ kill -9 1485907

The next step would be to connect to the Data Guard command line interface (DGMGRL) and execute the manual failover.

DGMGRL> connect /

Connected to "dgmain22b"
Connected as SYSDG.
DGMGRL> failover to "dgmain22b";

2025-02-12T10:38:31.179+00:00
Performing failover NOW, please wait...
2025-02-12T10:38:37.728+00:00
Failover succeeded; the new primary is "dgmain22b".
2025-02-12T10:38:37.729+00:00
Failover processing complete, broker ready.

Now that the failover is completed, we will connect to the database (pdb1) and check the status of the database incarnation.

SQL> alter session set container=pdb1;

Session altered.
SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED CON_ID
------------ ----------------- --------- ----------------------- --------- ------- ------------ ------------------ -------------------------- ----------
1            1                 14-FEB-24 0                                 PARENT  1144840863    0                 NO                         0
2            1343420           08-FEB-25 1                       14-FEB-24 PARENT  1155034180    1                 NO                         0
3            2704078           12-FEB-25 1343420                 08-FEB-25 CURRENT 1155465511    2                 NO                         0

The new incarnation (changed after the failover) is 1155465511. So, to return to our initial question, would Flashback work after the failover and the incarnation change? Let’s see if that is possible.

SQL> select hire_date from hr.employees where employee_id=100;

HIRE_DATE
---------
12-FEB-25
SQL> select hire_date from hr.employees as of timestamp systimestamp-1/24 where employee_id=100;

HIRE_DATE
---------
17-JUN-03

This is excellent news. We can see above that the Flashback Query works, but what do you think about the Flashback table? Would it work?

SQL> flashback table hr.employees to timestamp sysdate-1/24;

flashback table hr.employees to timestamp sysdate-1/24
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

So, to run a flashback table operation, we first need to enable row movement. Let’s enable it and try again!

SQL> alter table hr.employees enable row movement;

Table altered.
SQL> flashback table hr.employees to timestamp sysdate-1/24;

Flashback complete
SQL> select hire_date from hr.employees where employee_id=100;

HIRE_DATE
---------
17-JUN-03

The scenario above was a clear and relatively simple example of how we can still easily take advantage of the power of Flashback within an Oracle Database even after a failover event. 

Advancing Disaster Recovery Strategies with Oracle Features

Traditional disaster recovery methods often involve extensive manual processes, reliance on outdated backups, and significant downtime, leading to operational inefficiencies and potential data loss. By contrast, employing Oracle Database features such as Data Guard and Flashback drastically improves recovery time objectives (RTO) and recovery point objectives (RPO). These advanced features ensure businesses can swiftly restore their database systems with minimal disruption in case of a catastrophic failure or data corruption incident. Data Guard’s real-time synchronization provides continuous protection by maintaining standby databases that are almost instantly available for failover operations.

A Complete Solution

Consider the hypothetical case of a significant financial institution facing unexpected software corruption from a cyber-attack. Utilizing Oracle Data Guard, the institution could switch to its standby database within seconds, ensuring that critical banking services remained operational without losing transactional data. The rapid failover capability preserved data integrity and maintained customer trust during an otherwise potentially damaging event. Another compelling hypothetical example involves a global e-commerce platform leveraging Oracle Flashback technology after detecting logical errors caused by an erroneous application deployment. Instead of restoring large backups spanning several hours, the team quickly reverted the affected objects to their previous states within moments, significantly reducing downtime and preserving transaction consistency.

Enhancing overall disaster preparedness strategically requires combining these robust Oracle features into a comprehensive disaster recovery plan. Organizations should thoroughly test their Data Guard configurations to ensure seamless failover transitions between primary and standby databases under various failure scenarios. For Flashback technology integration, it’s recommended to set up frequent automatic restore points (snapshots) and maintain careful monitoring practices to detect anomalies as soon as they occur. Developing clear communication protocols and proactively running drills using this technology across IT departments will empower teams to respond more effectively during critical incidents.

By combining Data Guard’s standby capabilities with Flashback’s pinpoint precision for data correction, organizations create multi-layered defense mechanisms against physical failures and logical errors. This blend drives higher efficiency in disaster recovery and fortifies resilience against an array of unforeseen challenges in today’s volatile digital environment.

Conclusion: Harnessing Competitive Advantages with Oracle Database

Utilizing Oracle Database’s Data Guard and Flashback features offers significant advantages for modern IT operations. These tools enable seamless data management, enhance security protocols, and ensure robust disaster recovery capabilities. IT professionals, database administrators, and technology enthusiasts can create a resilient and secure environment by incorporating these functionalities efficiently data.

The benefits are clear. Ensuring continuous operations through minimal downtime, protecting critical data assets with advanced security measures, and swiftly recovering from potential data loss incidents are just a few of the key advantages. Integrating Data Guard and Flashback into your workflow supports effective risk mitigation and keeps you ahead in the ever-evolving tech landscape. Embrace these powerful features to unlock the full potential of your Oracle Database systems.

References: