ogg对oracle的影响,oracle的Flashback操作 对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;

ID

----------

1

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;

ID

----------

1

此时检查发现，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;

ID

----------

1

此时检查发现，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;

ID

----------

1

2

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)；

2

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需人为干预才能恢复正常。