Logical Standby 10gR2 – Open Issues

 

What is it? 

It is version of STANDBY database that is replicated from the primary in a different manner than a physical standby.  Instead of the exact copy that a physical standby is…a LOGICAL STANDBY is different in both the method of data transfer and different (most likely) in that there will be some data divergence.  In some cases it may be used for failover and disaster recovery situations (with little data divergence) but most often it is used for REPORTING and/or a DATAWAREHOUSE.

What can it do?

 It is close to real time with the data in the PRIMARY database especially if you are using the REAL-TIME APPLY capabilities of 10g+.

What can’t it do?

• Not all datatypes are supported

10.2.x Logical standby databases support the following data types:
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CHAR
CLOB and NCLOB
DATE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
LONG (even those these are supported, there are several which I skip during SQLAPPLY due to hanging, since they are needed for reporting no problem)
LONG RAW
NCHAR
NUMBER
NVARCHAR2
RAW
TIMESTAMP
TIMESTAMP WITH LOCAL TIMEZONE
TIMESTAMP WITH TIMEZONE
VARCHAR2 and VARCHAR

• Some DDL is skipped during SQL APPLY process

Certain SYS packages aren’t supported

DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH, DBMS_REDEFINITION, DBMS_SCHEDULER, and DBMS_AQ.  Well since the scheduler won’t run (10.2.x.) which in turn doesn’t allow certain things to occur such as automatic tuning tasks. Use command-line API’s instead for tuning.

• Unsupported Tables, Views and Sequences

SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
ORDER BY OWNER,TABLE_NAME;

• DDL Items that are SKIPPED during SQL APPLY process:

ALTER DATABASE
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW LOG
ALTER SESSION
ALTER SYSTEM
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
CREATE SCHEMA AUTHORIZATION
CREATE SPFILE FROM PFILE
DROP DATABASE LINK
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW LOG
EXPLAIN
LOCK TABLE
SET CONSTRAINTS
SET ROLE
SET TRANSACTION

What to do about Materialized Views?

These most often have to be rewritten for the LOGICAL STANDBY.

For example, the following materialized view in the primary database will not propogate to the LOGICAL STANDBY.

CREATE MATERIALIZED VIEW schema.mv_name
TABLESPACE TABLESPACE_NAME
CACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH start with sysdate next trunc(sysdate)+1
AS……..

In the LOGICAL STANDBY the MV is recreated  (see step 3 below running DDL on logical).

CREATE MATERIALIZED VIEW schema.mv_name
TABLESPACE TABLESPACE_NAME
CACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
as

 Added a cron job to rebuild the MV nightly. The ? stands for Force.

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
EXECUTE DBMS_MVIEW.REFRESH ( ‘SCHEMA.MV_NAME’, ‘?’);
ALTER DATABASE START LOGICAL STANDBY APPLY;
 

 

Consistent, Repeatable Issues and Problems:

1. APPLIED_SCN not in synch with NEWEST_SCN

This has occurred since 10.2.0.3 patchset – consistent and repeatable.
Numbers between applied_scn and newest_scn are always slightly off,
they start out exactly the same and diverge within a couple of days
after recreation of database.

SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME, NEWEST_SCN, NEWEST_TIME
FROM DBA_LOGSTDBY_PROGRESS;
678901696 28-APR-08 678900866 28-APR-08 678901709 28-APR-08

2. ORA-16116 during heavy transactions …..

……such as a sqlload, updates
on a single large table. The logical standby database is recreated because it takes longer to create an SR, escalate and pursue the issue. Oracle support doesn’t consider a logical standby to be a high severity issue so escalation takes more time. The following note doesn’t help.

Subject: Determining if SQL Apply Engine is Performing Full Table
Scans
Doc ID: Note:255958.1

TYPE STATUS    HIGH_SCN
———– ———————–
COORDINATOR ORA-16116: no work available 599833773
READER ORA-16116: no work available 599833773
BUILDER ORA-16116: no work available 599833773
PREPARER ORA-16116: no work available 599833697
PREPARER ORA-16116: no work available 599833770
PREPARER ORA-16116: no work available 599833772
PREPARER ORA-16116: no work available 599833687
ANALYZER ORA-16116: no work available 599833773
APPLIER ORA-16116: no work available 599833773

 3.  10.2.0.3 – Occasionally DDL is not migrated from PRIMARY to LOGICAL.

Example:  Created programmer account on PRIMARY, usually migrates to LOGICAL standby so they can logon there as well.  That step didn’t happen so I relied on the following means to recreate his account on the logical.  More data divergence!!! See documentation for list of DDL commands that are not skipped.  See excerpt from docs on what to do if this happens and you need to fix things.

Performing DDL on a Logical Standby Database from documentation.
This section describes how to add a constraint to a table maintained through SQL Apply.

By default, only accounts with SYS privileges can modify the database while the database guard is set to ALL or STANDBY. If you are logged in as SYSTEM or another privileged account, you will not be able to issue DDL statements on the logical standby database without first bypassing the database guard for the session.

The following example shows how to stop SQL Apply, bypass the database guard, execute SQL statements on the logical standby database, and then reenable the guard:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.

SQL> ALTER SESSION DISABLE GUARD;
PL/SQL procedure successfully completed.

SQL> ALTER TABLE SCOTT.EMP ADD CONSTRAINT EMPID UNIQUE (EMPNO);
Table altered.

SQL> ALTER SESSION ENABLE GUARD;
PL/SQL procedure successfully completed.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Database altered.

4.  Changes to LOGICAL APPLY settings are not preserved during database cycling

So…..I rerun the following commands after the database comes backup.  Note: These may not apply to your environment.  Be sure and understand the implications of each line before using, in other words read the docs.

alter database stop logical standby apply;
–This is set so that the enduser gets a consistent view of the data, important to be set if you are using logical standby for reporting.
exec DBMS_LOGSTDBY.APPLY_SET(‘PRESERVE_COMMIT_ORDER’,’TRUE’);

execute dbms_logstdby.apply_set(‘MAX_SERVERS’,28);

–Increases the LCR cache memory
execute dbms_logstdby.apply_set(‘MAX_SGA’,2400);

–Changes the number of apply processes, I have changed this parameter up to 10 and then back down after performance problems.
exec dbms_logstdby.apply_set(‘APPLY_SERVERS’,1);

–If you only want to keep the tables that come from the primary protected against updates. If you don’t want any object modified then alter database guard all;
alter database guard standby;
alter database start logical standby apply immediate;