Short answer: yes.<\/p>\n
Let’s just see it in action.<\/p>\n
First, I have a Data Guard configuration in place. On the primary database, the current incarnation has a single parent (the template from which it has been created):<\/p>\n
SQL> select * from v$database_incarnation;\r\n\r\nINCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES\r\n------------ ----------------- --------- ----------------------- ---------\r\nSTATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED CON_ID\r\n------- ------------ ------------------ -------------------------- ----------\r\n 1 1 14-AUG-23 0\r\nPARENT 1144840863 0 NO 0\r\n\r\n 2 1343420 08-DEC-23 1 14-AUG-23\r\nCURRENT 1155034180 1 NO 0<\/pre>\nJust to make room for some undo, I increase the undo_retention. On a PDB, that requires LOCAL UNDO to be configured (I hope it’s the default everywhere nowadays).<\/p>\n
SQL> alter session set container=PDB1;\r\n\r\nSession altered.\r\n\r\nSQL> alter system set undo_retention=86400;\r\n\r\nSystem altered.\r\n<\/pre>\nThen, I update some data to test flashback query:<\/p>\n
SQL> alter session set current_schema=HR;\r\n\r\nSession altered.\r\n\r\nSQL> update hr.employees set HIRE_DATE=sysdate where employee_id=100;\r\n\r\n1 row updated.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n<\/pre>\nAt this point, I can see the current data, and the data as it was 1 hour ago:<\/p>\n
SQL> select hire_date from hr.employees where employee_id=100;\r\n\r\nHIRE_DATE\r\n---------\r\n13-DEC-23\r\n\r\nSQL> select hire_date from hr.employees as of timestamp systimestamp-1\/24 where employee_id=100;\r\n\r\nHIRE_DATE\r\n---------\r\n17-JUN-03<\/pre>\nNow, I kill the primary database and fail over to the standby database:<\/p>\n
# on the primary:\r\n[ primary ] bash-4.4$ ps -eaf | grep pmon\r\nlcaldara 1485907 1 0 10:29 ? 00:00:00 ora_pmon_orcl\r\nlcaldara 1486768 1484883 0 10:37 pts\/0 00:00:00 grep pmon\r\n[ primary ] bash-4.4$ kill -9 1485907\r\n\r\n# on the standby:\r\nDGMGRL> connect \/\r\nConnected to \"orcl_site2\"\r\nConnected as SYSDG.\r\nDGMGRL> failover to \"orcl_site2\";\r\n2023-12-13T10:38:31.179+00:00\r\nPerforming failover NOW, please wait...\r\n\r\n2023-12-13T10:38:37.728+00:00\r\nFailover succeeded, new primary is \"orcl_site2\".\r\n\r\n2023-12-13T10:38:37.729+00:00\r\nFailover processing complete, broker ready.\r\nDGMGRL><\/pre>\nAfter connecting to the new primary, I can see the new incarnation due to the open resetlogs after the failover.<\/p>\n
SQL> select * from v$database_incarnation;\r\n\r\nINCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES\r\n------------ ----------------- --------- ----------------------- ---------\r\nSTATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED CON_ID\r\n------- ------------ ------------------ -------------------------- ----------\r\n 1 1 14-AUG-23 0\r\nPARENT 1144840863 0 NO 0\r\n\r\n 2 1343420 08-DEC-23 1 14-AUG-23\r\nPARENT 1155034180 1 NO 0\r\n\r\n 3 2704078 13-DEC-23 1343420 08-DEC-23\r\nCURRENT 1155465511 2 NO 0<\/pre>\nAnd I can still query the data as of a previous timestamp:<\/p>\n
SQL> select hire_date from hr.employees where employee_id=100;\r\n\r\nHIRE_DATE\r\n---------\r\n13-DEC-23\r\n\r\nSQL> select hire_date from hr.employees as of timestamp systimestamp-1\/24 where employee_id=100;\r\n\r\nHIRE_DATE\r\n---------\r\n17-JUN-03<\/pre>\nOr flash back the table, if required:<\/p>\n
SQL> flashback table hr.employees to timestamp sysdate-1\/24;\r\nflashback table hr.employees to timestamp sysdate-1\/24\r\n *\r\nERROR at line 1:\r\nORA-08189: cannot flashback the table because row movement is not enabled\r\n\r\n\r\nSQL> alter table hr.employees enable row movement;\r\n\r\nTable altered.\r\n\r\nSQL> flashback table hr.employees to timestamp sysdate-1\/24;\r\n\r\nFlashback complete.\r\n\r\nSQL> select hire_date from hr.employees where employee_id=100;\r\n\r\nHIRE_DATE\r\n---------\r\n17-JUN-03<\/pre>\nSo yes, that works. The caveat is still that you need to retain enough data in the undo tablespace to rebuild the rows in their previous state.<\/p>\n
—<\/p>\n
Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"
Short answer: yes. Let’s just see it in action. First, I have a Data Guard configuration in place. On the primary database, the current incarnation has a single parent (the template from which it has been created): SQL> select * … Continue reading