Wrong Results From ADG Standby (Doc ID 2467073.1)

Last updated on APRIL 17, 2023

Applies to:

Goal

Wrong results on Active Data Guard (ADG) standby database Query on Partitioned Table with _part_access_version_by_number=FALSE on the Standby.

The issue can be reproduced by the following steps.

1.Create partitioned table on primary.
2. Add data to the table and commit changes.
3. Select from the table on primary and ADG standby. Results are the same.
4. Add data to the table and commit. Data should be inserted in other partition than in step 2.
5. Select from the table on primary and ADG standby. Results are different.

If after step 5 flush shared pool then you will get right results.

Primary:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


SQL> CREATE TABLE TEST_PART_TAB (
    text varchar2(10),
    some_date timestamp
) partition by range (some_date)
(PARTITION P_OLD  VALUES LESS THAN (TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
PARTITION P1001 VALUES LESS THAN (TO_DATE(' 2018-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
PARTITION P1002 VALUES LESS THAN (TO_DATE(' 2018-10-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
);  2    3    4    5    6    7    8

Table created.

SQL> insert into TEST_PART_TAB values ('a', '29.09.18 07:00:00');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM TEST_PART_TAB;

TEXT
----------
SOME_DATE
---------------------------------------------------------------------------
a
29.09.18 07:00:00,000000


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02.10.18 15:40:43,558523 +03:00

SQL>
SQL> insert into TEST_PART_TAB values ('b', '01.10.18 07:00:00');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM TEST_PART_TAB;

TEXT
----------
SOME_DATE
---------------------------------------------------------------------------
a
29.09.18 07:00:00,000000

b
01.10.18 07:00:00,000000


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02.10.18 15:42:49,254163 +03:00

SQL> insert into TEST_PART_TAB values ('c', '02.10.18 07:00:00');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM TEST_PART_TAB;

TEXT
----------
SOME_DATE
---------------------------------------------------------------------------
a
29.09.18 07:00:00,000000

b
01.10.18 07:00:00,000000

c
02.10.18 07:00:00,000000


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02.10.18 15:44:56,003108 +03:00

Standby:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02.10.18 15:41:06,996160 +03:00

SQL> SELECT * FROM TEST_PART_TAB;

TEXT
----------
SOME_DATE
---------------------------------------------------------------------------
a
29.09.18 07:00:00,000000


SQL> SELECT * FROM TEST_PART_TAB;

TEXT
----------
SOME_DATE
---------------------------------------------------------------------------
a
29.09.18 07:00:00,000000


SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02.10.18 15:43:55,042490 +03:00


SQL> SELECT count(*) FROM TEST_PART_TAB PARTITION (P1001);

  COUNT(*)
----------
         0

SQL> SELECT * FROM TEST_PART_TAB;

TEXT
----------
SOME_DATE
---------------------------------------------------------------------------
a
29.09.18 07:00:00,000000


SQL>
SQL> SELECT count(*) FROM TEST_PART_TAB PARTITION (P1002);

  COUNT(*)
----------
         0

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
02.10.18 15:46:37,225992 +03:00

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.