oracle的Flashback操作对goldengate的影响

最新推荐文章于 2022-06-02 19:53:43 发布

转载最新推荐文章于 2022-06-02 19:53:43 发布 · 149 阅读

本文探讨了在特定环境下，Oracle GoldenGate在面对数据库闪回操作时的数据复制一致性问题。通过一系列实验，分析了闪回表、闪回数据库等场景下GoldenGate的运行状态和数据一致性，揭示了在闪回数据库后GoldenGate需额外干预以恢复正常运行的原因。

本实验主要为测试数据库的flashback操作对goldengate的影响。

测试环境为rehl 5.5 64bit，oracle 11.2.0.3版本，ogg为11.2.1.0.1版本，ogg未开启ddl复制（之后还需要测试在aix5.3下，oracle10g中flashback操作对ogg的影响）。

使用schema 为scott ，表为object1，object1表如下：

SQL> desc object1

Name Null? Type

----------------------------------------- -------- -------------- ID NOT NULL NUMBER(38)

测试如下：

1.在object1中插入数据，确定数据已经复制到目标端后，flashback到插入数据前，检查ogg进程的运行情况和数据的一致性。

Connected.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1078432

SQL> select * from object1;

----------

SQL> insert into object1 values(2);

1 row created.

SQL> commit;

Commit complete.

此时检查确定目标端和源端数据一致。

SQL> flashback table object1 to scn 1078432;

Flashback complete.

SQL> select * from object1;

----------

此时检查发现，ogg进程运行正常，目标端和源端数据一致。

2.删除object1 ，检查ogg进程情况，flashback表object1，检查ogg进程的运行情况和数据的一致性。

SQL> drop table object1;

Table dropped.

SQL> flashback table object1 to before drop;

Flashback complete.

SQL> select * from object1;

----------

此时检查发现，ogg进程运行正常，目标端和源端数据一致。但ogg检查附加日志发现object1附加日志已失效。

GGSCI (primary.localdomain) 12> info trandata scott.object1

Logging of supplemental redo log data is disabled for table SCOTT.OBJECT1.

不添加object1 的附加日志，继续插入数据，ogg数据复制正常。

3.truncate表object1，检查ogg进程的运行情况和数据的一致性；flashback表object1，检查ogg进程的运行情况和数据的一致性。

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1080108

SQL> select * from object1;

----------

SQL> truncate table object1;

Table truncated.

SQL> flashback table object1 to scn 1080108;

flashback table object1 to scn 1080108

ERROR at line 1:

ORA-01466: unable to read data - table definition has changed

经测试，不能闪回truncate的表，ogg抽取和复制进程中未添加gettruncates参数，此时源端和目标端的数据不一致。

4.flashback数据库，检查ogg进程的运行情况和数据一致性。

数据库默认没有开启数据库闪回，开启数据库闪回功能，功能开启需要重启数据库，会造成抽取进程中断。

操作步骤如下：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 630501376 bytes

Fixed Size 2230992 bytes

Variable Size 398460208 bytes

Database Buffers 222298112 bytes

Redo Buffers 7512064 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

确定当前scn号并插入数据，然后flashback 数据库。检查ogg进程运行情况和数据一致性。

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1083379

SQL> insert into scott.object1 values (1）；

SQL> insert into scott.object1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 630501376 bytes

Fixed Size 2230992 bytes

Variable Size 398460208 bytes

Database Buffers 222298112 bytes

Redo Buffers 7512064 bytes

Database mounted.

SQL> flashback database to scn 1083379;

Flashback complete.

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;

Database altered.

SQL> select * from scott.object1;

no rows selected

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

1083723

SQL> insert into scott.object1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.object1 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

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

STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

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

NEXT_TIME

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

1 1 1 52428800 512 1 NO

CURRENT 1083381 2014-05-25 19:33:18 2.8147E+14

2 1 0 52428800 512 1 YES

UNUSED 0 0

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

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

STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#

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

NEXT_TIME

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

3 1 0 52428800 512 1 YES

UNUSED 0 0

此操作完成后，因resetlogs操作，造成抽取进程运行正常，但是不能正常抽取到数据，需重新指定抽取进程的抽取起始时间点。

SQL> select sysdate from dual;

SYSDATE

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

2014-05-25 19:42:18

ogg操作：

GGSCI (primary.localdomain) 39> alter extya,begin 2014-05-25 19:42:18

EXTRACT altered.

GGSCI (primary.localdomain) 41> start extya

Sending START request to MANAGER ...

EXTRACT EXTYA starting

SQL> insert into scott.object1 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.object1 values(1);

1 row created.

SQL> commit;

Commit complete.

此时源端和目标端数据已经不一致，重新插入闪回数据库期间的数据后，ogg复制进程中断，报错如下：

2014-05-25 19:43:40 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, repya.prm: OCI Error ORA-00001: unique constraint (SCOTT.SYS_C0010985) violated (status = 1). INSERT INTO "SCOTT"."OBJECT1" ("ID") VALUES (:a0).

2014-05-25 19:43:40 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, repya.prm: Aborted grouped transaction on 'SCOTT.OBJECT1', Database error 1 (OCI Error ORA-00001: unique constraint (SCOTT.SYS_C0010985) violated (status = 1). INSERT INTO "SCOTT"."OBJECT1" ("ID") VALUES (:a0)).

2014-05-25 19:43:40 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repya.prm: Repositioning to rba 1701 in seqno 2.

2014-05-25 19:43:40 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, repya.prm: SQL error 1 mapping SCOTT.OBJECT1 to SCOTT.OBJECT1 OCI Error ORA-00001: unique constraint (SCOTT.SYS_C0010985) violated (status = 1). INSERT INTO "SCOTT"."OBJECT1" ("ID") VALUES (:a0).

2014-05-25 19:43:40 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, repya.prm: Repositioning to rba 1701 in seqno 2.

2014-05-25 19:43:40 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repya.prm: Error mapping from SCOTT.OBJECT1 to SCOTT.OBJECT1.

2014-05-25 19:43:40 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repya.prm: PROCESS ABENDING.

至此实验结束，结论如下：

1.在此实验环境下，闪回表不影响ogg复制的数据一致性。

2.闪回数据库会造成ogg复制的数据不一致，而且闪回数据库后，ogg需人为干预才能恢复正常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8441448/viewspace-1169517/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8441448/viewspace-1169517/