Loading....

There is an Oracle Metalink Note (Does Goldengate Support The New Oracle 10g Flashback Feature? [ID 966212.1]) which discusses this aspect, but I thought that I would like to see for myself how does the Extract and Replicat processes behave if we issue any FLASHBACK command on the source database.

The Metalink note states that GoldenGate supports the following Flashback operations:

Flashback Table to SCN
Flashback Table to timestamp
Flashback Table to restore point

and does not support operations like:

Flashback Table to before drop
Flashback database

I have tested both the Flashback Table To Timestamp as well as the Flashback Table To Before Drop operations and find that both seem to be supported by GoldenGate and replication continues inspite of these commands being issued on the source database.

Let us see the examples of the same.

I have an Oracle 11g Goldengate (11.1.1) environment configured with the source and target databases of version 11.2.0.2.

Extract and Replicat processes are running and I am not using Data Pump in this case. The table configured for replication is called MYOBJECTS.

To test the Flashback, I am deleting all the rows from the table and then check on the target database if this delete operation has been propagated.

We will then flashback to a point in time when all the rows were present in the table and then check both the source and target database to verify if the delete operation has been flashed back on both databases.

Source

SQL> select count(*) from myobjects;

COUNT(*)
———-
56687

SQL> select to_char(sysdate,’dd-mon-yyyy hh24:mi:ss’) from dual;

TO_CHAR(SYSDATE,’DD-
——————–
17-aug-2011 10:23:07

SQL> delete myobjects;

56687 rows deleted.

SQL> commit;

Commit complete.

Target

SQL> select count(*) from myobjects;

COUNT(*)
———-
0

Source

SQL> conn / as sysdba
Connected.
SQL> alter table ggs_owner.myobjects enable row movement;

Table altered.

SQL> flashback table ggs_owner.myobjects to timestamp to_timestamp (’17-aug-2011 10:23:07′,’dd-mon-yyyy hh24:mi:ss’);

Flashback complete.

SQL> select count(*) from ggs_owner.myobjects;

COUNT(*)
———-
56687

Target

SQL> select count(*) from myobjects;

COUNT(*)
———-
56687

If we check the Extract and Replicat process reports, we can see that around the time we have performed the database flashback operation, there has been both extract and replicat activity

Source

2011-08-17 10:23:58 INFO OGG-01026 Rolling over remote file ./dirdat/yy000004.
2011-08-17 10:25:53 INFO OGG-01026 Rolling over remote file ./dirdat/yy000005.
2011-08-17 10:25:58 INFO OGG-01026 Rolling over remote file ./dirdat/yy000006.

Target

Switching to next trail file ./dirdat/yy000004 at 2011-08-17 10:23:59 due to EOF, with current RBA 9999940
Opened trail file ./dirdat/yy000004 at 2011-08-17 10:23:59

Switching to next trail file ./dirdat/yy000005 at 2011-08-17 10:25:54 due to EOF, with current RBA 9999930
Opened trail file ./dirdat/yy000005 at 2011-08-17 10:25:54

Switching to next trail file ./dirdat/yy000006 at 2011-08-17 10:25:58 due to EOF, with current RBA 9999882
Opened trail file ./dirdat/yy000006 at 2011-08-17 10:25:58

Let us now look at the Flashback Table To Before Drop operation.

The Metalink note states that this operation is NOT supported, but I found that in my case, GoldenGate was still peforming the online change synchronization even after we dropped the table and flashed it back.

Would like others in the community to try this out themselves and provide some feedback to me on how it went for them.

Here is the example.

We will use the same MYOBJECTS table and drop it and then flash it back from the recycle bin. Note – if you are using DDL replication as well, then recycle bin has to be turned off and in that case you will not be able to flashback the table from the recycle bin using the Flashback Table To Before Drop command.

Source

SQL> drop table myobjects;

Table dropped.

SQL> flashback table myobjects to before drop;

Flashback complete.

SQL> update myobjects set owner=’GAVIN’;

56687 rows updated.

SQL> commit;

Commit complete.

SQL> select distinct owner from myobjects;

OWNER
——————————
GAVIN

Target

SQL> select distinct owner from myobjects;

OWNER
——————————
GAVIN

Changes have been replicated!

Please follow and like us:

Last Update: August 17, 2011  

August 17, 2011 60 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?