玉面飞龙的BLOG

Logical standby would be more mature and popular in future, i did some test about some feathures behavior in Oracle11g logical standby env.

Here is conclusion.

1) Text index is not FULLY support. It’s only Partially support.  But it can be maintained on logical standby via SQL apply.

2) ROWID datatype are not supported in logical standby, you will see “ORA-16129: unsupported dml encountered” when it’s ignored by logical standby

3) Rows in compressed table can be replicated to logical standby.

4)virtual column  and related function based index is also supported by logical standby

5)Logical standby will replicate “analyze table” statement from primary to standby, not copy statistics in dictionary from primary to standby.

6)Online redefinition is supported

7)Outline is supported. But manually updates in outln schema is not replicated to logical standby.

8)Oracle replicate sequence value very well and make sence.

9)During convert to some character set, we need to do convert on both databases.

10)System trigger like “after logon”or “after startup” is also supported on logical standby side.

i did some test about some feathures behavior in Oracle11g logical standby env.

Here is conclusion.

1) Text index is not FULLY support. It’s only Partially support.  But it can be maintained on logical standby via SQL apply.

2) ROWID datatype are not supported in logical standby, you will see “ORA-16129: unsupported dml encountered” when it’s ignored by logical standby

3) Rows in compressed table can be replicated to logical standby.

4)virtual column  and related function based index is also supported by logical standby

5)Logical standby will replicate “analyze table” statement from primary to standby, not copy statistics in dictionary from primary to standby.

6)Online redefinition is supported

7)Outline is supported. But manually updates in outln schema is not replicated to logical standby.

8)Oracle replicate sequence value very well and make sence.

9)During convert to some character set, we need to do convert on both databases.

10)System trigger like “after logon”or “after startup” is also supported on logical standby side.

————————————see more detail beow————————————————————-

1) Text index is not FULLY support. It’s only Partially support.  But it can be maintained on logical standby via SQL apply.

Per OTN document, Logical standby databases do not support the following datatypes:

BFILE
Collections (including VARRAYS and nested tables)
Multimedia data types (including Spatial, Image, and Oracle Text)
ROWID, UROWID
User-defined types
XMLType stored as Object Relational
Binary XML

below is simple test to show how oracle handle TEXT index.

Step 1) creaet text index on primary

SQL> select * from document;
ID TITLE
———- —————————————-
1 house tax start
2 global economy will twice drop
3 people lost job

SQL> create index document_ctx on document(title) indextype is ctxsys.ctxcat;
Index created.

SQL> select * from tab;
TNAME               TABTYPE
—————————-
DOCUMENT            TABLE
DR$DOCUMENT_CTX$I   TABLE

SQL>  select * from document where catsearch(title,’tax’,”)>0;
ID TITLE
———- —————————————-
1 house tax start

Step 2) Check if index is replicated to logical standby,

on logical standby, we see objects DR$ is created.

SQL> select * from tab;
TNAME                TABTYPE                CLUSTERID
——————– ——————— ———-
DOCUMENT             TABLE
DR$DOCUMENT_CTX$I    TABLE

Text search also works well.

SQL>  select * from document where catsearch(title,’tax’,”)>0;
ID TITLE
———- —————————————-
1 house tax start

However, we see many warnning events on logical standby, the event means it ignores many DML on CTXSYS schema.
CTXSYS schema store many metadata for text index.

STATUS
——————————————————————————–
EVENT
——————————————————————————–
ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR_ID_SEQ”

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$INDEX”

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$INDEX_OBJECT”

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$INDEX_VALUE”

ORA-16227: DDL skipped due to missing object
begin
“SYS”.”DBMS_DDL”.”SET_TRIGGER_FIRING_PROPERTY” (
“TRIG_OWNER” => ‘”BIN

ORA-16129: unsupported dml encountered
DML on “CTXSYS”.”DR$FEATURE_USED”

So to fully have logical standby to support TEXT index, it’s better to re-create it on logical standby side.

2) ROWID datatype are not supported in logical standby, you will see “ORA-16129: unsupported dml encountered” when it’s ignored by logical standby

The test first load some table’s obsolte rows ’s rowid into a ROWID type table, then do purge by rowid.

On primary,

SQL> select count(*) from test;
COUNT(*)
———-
267065

SQL> create table testrowid(id number primary key, rl rowid) ;
Table created.

SQL> insert into testrowid select id,rowid from test where rownum<=100;
100 rows created.

SQL> commit;
Commit complete.

SQL> delete from test where id in (select id from testrowid);
100 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(*) from test;
COUNT(*)
———-
266965

Then on logical standby, ROWID table is created but rows are not replicated.

SQL> select * from testrowid;
no rows selected

We would see warning in DBA_LOGSTDBY_EVENTS.

ORA-16129: unsupported dml encountered
DML on “BINZHANG”.”TESTROWID”

Table test rows is deleted on logical standby by redo analysis & SQL apply

SQL> select count(*) from test;
COUNT(*)
———-
266965

3) Rows in compressed table can be replicated to logical standby.

In Oracle10G, rows in compressed table can NOT be replicated to logical standby. In oracle11G, it changed and oracle11G have 2 compress option: basic or OLTP.

Step 1) create basic compress table on primary.

SQL>create table testcom (id number primary key,name varchar2(63)) compress;
Table created.

SQL>insert /*+ append */into testcom select object_id, ‘compress_content’ from
dba_objects where object_id is not null;
72607 rows inserted.

SQL>analyze table testcom compute statistics;

SQL> select count(*) from TESTCOM;
COUNT(*)
———-
72607

And it uses 118 blocks.

SQL>  select AVG_ROW_LEN,blocks from user_tables where table_name=’TESTCOM’;
AVG_ROW_LEN     BLOCKS
———– ———-
25        118

Step 2) check status on logical standby

On logical standby, replcated table keep compress basic option. Rows can be replated,but uses more blocks.
It’s due to apply concept of logical standby: it ignore the direct load.

SQL> select table_name,compression,compress_for from user_tables;

TABLE_NAME      COMPRESSION              COMPRESS_FOR
————— ———————— ————————————
TESTCOM         ENABLED                  BASIC

SQL> select count(*) from TESTCOM;
COUNT(*)
———-
72607

SQL> select AVG_ROW_LEN,blocks from user_tables where table_name=’TESTCOM’;

AVG_ROW_LEN     BLOCKS
———– ———-
25        244

Seems logical standby use conversional insert that compress basic doesn’t support.
Probabbly compress OLTP would make differece.

————————
insert /*+ restrict_all_ref_cons  */ into “BINZHANG”.”TESTCOM”
p(”ID”,”NAME”)values(:1,:2)
—————————————–
| Id  | Operation                | Name |
—————————————–
|   0 | INSERT STATEMENT         |      |
|   1 |  LOAD TABLE CONVENTIONAL |      |
—————————————–

Step 3) Add a new column on compressed table on primary, it also is added into logical standby.

SQL> desc testcom
Name                                      Null?    Type
—————————————– ——– —————————-
ID                                        NOT NULL NUMBER
NAME                                               VARCHAR2(63)
ANOTHER                                            VARCHAR2(30)

4) Move table on primary, on logical standby it’s moved also.

SQL> alter table testcom move  tablespace users02;
Table altered.

then on logical standby ,it also got moved and data object id got changed.

SEGMENT_NAME         TABLESPACE
——————– ———-
TESTCOM              USERS02

SQL> select object_id,data_object_id from dba_objects where object_name=’TESTCOM’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
76859          76861

Logical standby databases support the following table storage types:

*Cluster tables (including index clusters and heap clusters)
*Index-organized tables (partitioned and nonpartitioned, including overflow segments)
* Heap-organized tables (partitioned and nonpartitioned)
*OLTP table compression (COMPRESS FOR OLTP) and basic table compression (COMPRESS BASIC)

4)virtual column  and related function based index is also supported by logical standby

In OTN document, it lists below Unsupported Table Storage Types:

Logical standby databases do not support the following table storage types:
*Tables containing LOB columns stored as SecureFiles (unless the compatibility level is set to 11.2 or higher)
*Tables with virtual columns
*Tables using hybrid columnar compression(ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage)

Per my test, tables contains  virtual columns can be maintained well by logical standby.

Step 1)create table with virtual column

CREATE TABLE employees (
id          NUMBER ,
first_name  VARCHAR2(10),
last_name   VARCHAR2(10),
salary      NUMBER(9,2),
comm1       NUMBER(3),
comm2       NUMBER(3),
salary1     AS (ROUND(salary*(1+comm1/100),2)),
salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, ‘JOHN’, ‘DOE’, 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, ‘JAYNE’, ‘DOE’, 200, 10, 20);
COMMIT;

SQL> select first_name,salary1,salary2 from employees;

FIRST_NAME     SALARY1    SALARY2
———————- ———-
JOHN               105        110
JAYNE              220        240

Step 2) check table status on logical standby
Per event, table is created.

EVENT
——————————————————————————–
STATUS
——————————————————————————–
CREATE TABLE employees (
id          NUMBER ,
first_name  VARCHAR2(10),
last_name   VARCHAR2(10),
salary      NUMBER(9,2),
comm1       NUMBER(3),
comm2       NUMBER(3),
salary1     AS (ROUND(salary*(1+comm1/100),2)),
salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*
(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
)
ORA-16204: DDL successfully applied

SQL> !oerr ora 16204
16204, 00000, “DDL successfully applied”
// *Cause:  A DDL statement has successfully commited on the logical
//          standby database.
// *Action: No action necessary, this informational statement is provided
//          to record the event for diagnostic purposes.

Rows is also replicated.

SQL>  select first_name,salary1,salary2 from employees;
FIRST_NAME   SALARY1    SALARY2
——————– ———-
JOHN             105        110
JAYNE            220        240

If create a fucntion based index on virtual column, index would also be created on logical standby.

OWNER           U PAR TYPE    Index Name    Column Name
————— - — —— —————————-
BINZHANG        Y NO         EMPLOYEES_PK     ID
BINZHANG        N NO  FUNCTI EMP_FUNIDX       SALARY1

5)Logical standby will replicate “analyze table” statement from primary to standby, not copy statistics in dictionary from primary to standby.

Step 1) Do analyze on primary,

SQL> analyze table employees compute statistics;
Table analyzed.

SQL> select num_rows,LAST_ANALYZED from user_tables where table_name=’EMPLOYEES’;
NUM_ROWS LAST_ANALYZED
———- ——————-
2 2010-06-01 16:04:29

Then on logical standby, table also got analyzed actually. But their “LAST_ANALYZED” is different.
Logical standby apply analyze statement, not analyze result that resides in dict of primary.

SQL>  select num_rows,LAST_ANALYZED from user_tables where table_name=’EMPLOYEES’;

NUM_ROWS LAST_ANALYZED
———- ——————-
2 2010-06-01 10:50:26

6)Online redefinition is supported

Step 1) do online redef on primary, just online switch tablespace.

SQL>  create table large(id number primary key,text varchar(32) )  tablespace users;
Table created.

SQL> insert into large select object_id,object_name from dba_objects where object_id is not null;
72631 rows created.

SQL> commit;
Commit complete.

SQL> exec dbms_redefinition.can_redef_table   ( USER,’LARGE’, DBMS_REDEFINITION.CONS_USE_PK) ;
PL/SQL procedure successfully completed.

create table large_new into tablespace USERS02 with same structure as large

SQL>  exec dbms_redefinition.START_REDEF_TABLE ( USER, ‘LARGE’, ‘LARGE_NEW’, NULL, DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.sync_interim_table( USER, ‘LARGE’,'LARGE_NEW’);
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.finish_redef_table( USER, ‘LARGE’,'LARGE_NEW’);
PL/SQL procedure successfully completed.

On primary,

SQL> select table_name,tablespace_name from user_tables where table_name in ( ‘LARGE’,'LARGE_NEW’);
TABLE_NAME           TABLESPACE_NAME
——————– ——————–
LARGE                USERS02
LARGE_NEW            USERS

Step 2) then do same verify query on logical standby, we can see online-redef is still supported.
Table LARGE ’s  tablespace changed from USERS to USERS02 in dict.

SQL> select table_name,tablespace_name from user_tables where table_name in ( ‘LARGE’,'LARGE_NEW’);
TABLE_NAME           TABLESPACE_NAME
——————– ——————–
LARGE                USERS02
LARGE_NEW            USERS

In oracle documents, its description has confilict on DBMS_REDEFINITION package.

4.  Supported PL/SQL Supplied Packages

Oracle PL/SQL supplied packages that do not modify system metadata or user data leave no footprint in the archived redo log files,
and hence are safe to use on the primary database. Examples of such packages are
DBMS_OUTPUT, DBMS_RANDOM, DBMS_PIPE, DBMS_DESCRIBE, DBMS_OBFUSCATION_TOOLKIT, DBMS_TRACE, DBMS_METADATA, DBMS_CRYPTO.
Oracle PL/SQL supplied packages that do not modify system metadata but may modify user data are supported by SQL Apply,
as long as the modified data belongs to the supported data types listed in Section C.1.1. Examples of such packages are DBMS_LOB,
DBMS_SQL, and DBMS_TRANSACTION.

Data Guard logical standby supports replication of actions performed through the following packages: DBMS_RLS, DBMS_FGA, and DBMS_REDEFINITION.

C.9.2 Unsupported PL/SQL Supplied Packages

Oracle PL/SQL supplied packages that modify system metadata typically are not supported by SQL Apply, and
therefore their effects are not visible on the logical standby database. Examples of such packages are DBMS_JAVA, DBMS_REGISTRY,
DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, and DBMS_AQ.

7)Outline is supported. But manually updates in outln schema is not replicated to logical standby.

Step 1) Create outline on primary.

SQL> create outline ol_temp for category temp_plan on select count(*) from binzhang.test;
Outline created.

SQL> select count(*) from ol$;
COUNT(*)
———-
1

SQL> select count(*) from ol$nodes;
COUNT(*)
———-
1

SQL>  select count(*) from ol$hints;
COUNT(*)
———-
6

Step 2) Then on logical standby, check outln shema also.

SQL> select sql_text from ol$;

SQL_TEXT
——————————————————————————–
select count(*) from binzhang.test

We can see outline schema is replicated and maintained by oracle.

step 3) Then did a manually update on OL$ on primary and check if manually update can be replicated

SQL> update ol$ set sql_text=’select * from dual’;
1 row updated.

SQL> commit;
Commit complete.

SQL> select sql_text from ol$;
SQL_TEXT
——————————————————————————–
select * from dual

Then on logical standby, it doesn’t change; so it doesn’t support manually update on outln schema.

SQL> select sql_text from ol$;
SQL_TEXT
——————————————————————————–
select count(*) from binzhang.test

8)Oracle replicate sequence value very well and make sence.

Sequences are usually cached on primary DB. And oracle only replicated bump sequence operation in seq$.
In cocnept, when session query nextval, it just fetch value from row cache, it doesn’t update seq$,so it doesn’t generated redo, so logical standby capture nothing.

For example ,cache size is 1000, when all cached sequences are used up, and new serial of sequences are generated&cached and seq$ would be updated.
Oracle will replcate redo of seq$ update operation to logical standby and applied into dictionary at logical standby.

And when disable dataguard and query sequenct.nextval on logical standby. In this way, seq$.last_number would be larger than primary.
At this time, as long as primary’s seq$.last_nuber < standby seq$.last_nuber, standby seq$.last_number will not be updated when primary.seq$ got updates.

Oracle will keep logical standy.seq$.last_number >= primary.seq$.last_number. This would make sence for switchover.

9)During convert to some character set, we need to do convert on both databases.

Per OTN document, Configurations are not supported in which the primary database and standby database have different character sets.
how to convert charset?
What i did
1) convert primary to UTF8.
2) logical standby can apply
3) convert logical standby to UTF8
4) Replication still works well.

10)System trigger like “after logon”or “after startup” can be created on logical standby side.

Below type trigger can be created on logical standby and it will not block application session logon as it’s executed recursively by SYS.

CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO binzhang.connection_audit
(login_date, user_name)
VALUES
(SYSDATE, USER);
END logon_audit;