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;

Advertisements

11 Responses to Logical Standby 10gR2 – Open Issues

  1. aprilcsims says:

    I have seen this a few times in the past…on an older version of Oracle. I would recommend 10.2.0.4+ as the most stable version of the Logical Standby.

  2. graeme says:

    hi april

    i really like your blog. much higher quality than so many oracle blogs out there.

    do you have any recommendations for refreshing a 20Gb schema in a primary (and therefore logical standby) database? I would normally use IMPDP but i suspect the replication may not be able to handle the volume of data. i am using 10.2.0.4

    thanks
    graeme

    • April C Sims says:

      My suggestion is to skip the schema on the logical standby…the following skips everything. Logical Apply has to be stopped, it only takes a few seconds to run these two commands. You can restart Logical Apply.

      exec dbms_logstdby.skip(‘DML’,’SCHEMANAME’,’%’);
      exec dbms_logstdby.skip(‘SCHEMA_DDL’,’SCHEMANAME,’%’);

      Once the schema is skipped, do the import on the primary. When that is finished, run the following instantiate table commands for every table involved in the 20GB.

      exec dbms_logstdby.instantiate_table(‘SCHEMANAME’,’TABLE_NAME’,’DBLINK’);

      Once all of the tables are moved over, then do the unskip commands.

      exec dbms_logstdby.unskip(‘DML’,’SCHEMANAME’,’%’);
      exec dbms_logstdby.unskip(‘SCHEMA_DDL’,’SCHEMANAME,’%’);

      You may have to recreate items like triggers, sequences, indexes (items that aren’t tables) on the primary which would then be regenerated on the logical.
      I have found the instantiate table process to be much more efficient that Logical Apply.

  3. graeme says:

    thanks april. i think i will try that. i didn’t actually think that instantiate_table would be a better method. i might try and skip the schema then drop and recreate the schema, import only the tables first then unskip and rerun the import (excluding tables) as to get all the other changes to objects etc.
    i really appreciate your assistance.
    thanks
    graeme

  4. graeme says:

    quick question What if there are new tables created in the primary? Will instantiate_table be enough to populate the logminer dictionary (and avoid the need to run DBMS_LOGSTDBY.BUILD)

    • April C Sims says:

      Not a problem as long as you run the UNSKIP command, any new tables (DDL) will be applied to the Logical Standby.

  5. graeme says:

    actually i spoke to soon. i meant new tables created as part of the import (while we are skipping the schema). if you then go the the standby and instantiate_table will that work since according to the instantiate_table documentation it states…

    “This procedure assumes that the metadata has been maintained correctly”

    • April C Sims says:

      You mentioned this….sounds like the best plan to me. If you import only the tables all of the DDL is applied:
      i might try and skip the schema then drop and recreate the schema, import only the tables first then unskip and rerun the import (excluding tables) as to get all the other changes to objects etc.

      • graeme says:

        thanks april. i will give it a try and let you know. i really appreciate your assistance.

  6. Amin Adatia says:

    When creating a Logical Standby, is it required to first create a Physical Standby? Does this not negate using a different OS for the Standby? Or are different OS not supported by Data Guard? My Primary database is on AIX and I would like to move to Linux (and upgrade the Logical to 11gR2)

    • April C Sims says:

      Yes, you are required to create a Physical Standby first. See the following document for OS configurations that are supported across certain OS. It doesn’t appear that AIX to Linux is directly upgradeable. Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]
      You may have to recreate the logical on the new OS or perhaps try using the Transportable Tablespace Method for Upgrading the Logical to 11gR2. How to Move Tablespaces Across Platforms Using Transportable Tablespaces With RMAN [ID 371556.1]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s