love wife & love life —Roger的Oracle&MySQL技术博客

Phone:18180207355 提供专业Oracle&MySQL数据恢复、性能优化、迁移升级、紧急救援等服务

logical standby ORA-1119

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger的Oracle&MySQL技术博客

本文链接地址: logical standby ORA-1119

群中一位网友的问题,logical standby 同步create tablespace操作,遇到如下问题:

我们知道,create tablespace属于ddl操作,可能有人会问,是不是logical standby不支持ddl?
其实是支持的,只是针对这种情况,不应该这样操作而已。

下面是mos提供的一个solution:

另外还有几篇相关文档,大家也可以看看参考下,如下:
ORA-18008: DDL Not Applied in Logical Standby [ID 233730.1]
SKIPPING PARTITION DDL ON YOUR LOGICAL STANDBY DATABASE [ID 417597.1]

2 Responses to “logical standby ORA-1119”

  1. lizhenxu Says:

  2. lizhenxu Says:

    SKIPPING PARTITION DDL ON YOUR LOGICAL STANDBY DATABASE [ID 417597.1]
    Skipping Partition DDL on your Logical Standby Database
    This note addresses the following use case: Your application only needs to maintain 3 months of data on line, partitioned monthly, but you also have a requirement to keep the data for 7 years. You can use your Logical standby database to keep this data longer so that the Primary database does not have to maintain the data.
    To do this you will need to manually maintain the physical structure changes to the partitioned table on both the primary database and the standby database so when you add next months partition and remove last months partition on the primary, the partitions on the Logical standby database will not be changed automatically (data in the table will continue to be maintained by SQL Apply). To do this you need to‘SKIP’ the “ALTER TABLE” command on the standby database so the delete partition will not be executed. You will then need to add next month’s partition on to the standby database. The ‘SKIP’ is done using the DBMS_LOGSTDBY.SKIP[1] procedure on the Logical Standby database. Consider the following simple table on the primary database.
    create table orders
    (order_date date not null
    , order_number number not null
    , customer_code varchar2(7) not null
    ) partition by range ( order_date )
    ( partition p200611 values less than (to_date(’01-Dec-2006′) )
    , partition p200612 values less than (to_date(’01-Jan-2007′) )
    , partition p200701 values less than (to_date(’01-Feb-2007′) )
    , partition p200702 values less than (to_date(’01-Mar-2007′) )
    , partition pnext values less than (maxvalue));
    The normal process each month is to prepare next months partition and remove the oldest partition from this table on the 15th of each month. This would be performed using the “ALTER TABLE” command as follows.
    alter table orders drop partition p2000611 update indexes;
    alter table orders split partition pnext at (to_date(’01-Apr-2007′))
    into ( partition p200703 , partition pnext ) update indexes;
    By default these two DDL commands will be executed on the Logical standby database. To prevent that (and keep the data longer) you first must skip these commands by using the DBMS_LOGSTDBY.SKIP procedure, once, to setup the skip rule.
    On the standby, define the skip as follows:
    alter database stop logical standby apply;
    execute dbms_logstdby.skip(‘alter table’,’scott’,’orders’);
    alter database start logical standby apply;
    Now when the “alter table drop partition” command is executed on the primary, the command will be skipped on the standby, preserving the data. However, this will also skip the “alter table split partition” command on the Logical standby database, something you do not want to happen. You address this on the standby database by manually executing the “alter table split partition” command after it has completed on the primary.
    alter database stop logical standby apply;
    alter session disable guard;
    alter table scott.orders split partition pnext at (to_date(’01-Apr-2007′))
    into ( partition p200703 , partition pnext ) update indexes;
    alter session enable guard;
    alter database start logical standby apply;
    Note that you must specify the schema as well as the table (scott.orders) as you must be logged into the Logical standby database as SYS to disable the guard.
    If you want to allow all other “ALTER TABLE” commands to be executed on this table and only skip the drop partition command, you can define a PL/SQL Procedure that you want to have executed whenever the “ALTER TABLE” command is received. Now, rather than skipping all “ALTER TABLE” commands for the “scott.orders” table, you can specify what commands should be skipped and which ones should execute normally.
    The following PL/SQL Procedure is an example of an extremely simplified procedure that could be used.
    create or replace procedure sys.sql_apply_partition_handler
    (statement IN VARCHAR2
    ,statement_type IN VARCHAR2
    ,schema IN VARCHAR2
    ,name IN VARCHAR2
    ,lxidusn IN NUMBER
    ,lxidslt IN NUMBER
    ,lxidsqn IN NUMBER
    ,skip_action OUT NUMBER
    ,new_statement OUT VARCHAR2) AS
    begin
    IF upper(statement) like ‘%DROP%PARTITION%’
    THEN
    skip_action := sys.dbms_logstdby.SKIP_ACTION_SKIP;
    new_statement := null;
    ELSE –upper(statement) = ‘%DROP%PARTITION%’
    skip_action := sys.dbms_logstdby.SKIP_ACTION_APPLY;
    new_statement := null;
    END IF; –Schema Check
    end sql_apply_partition_handler;
    /
    This procedure will tell SQL Apply to skip all “DROP PARTITION”commands and allow all other “ALTER TABLE” commands to be applied normally.
    To direct SQL Apply to call your procedure whenever it receives an“ALTER TABLE” command, you must change the skip definition changed so that the PL/SQL procedure is called as follows:
    alter database stop logical standby apply;execute dbms_logstdby.skip(‘alter table’,’scott’,’orders’,’SYS.SQL_APPLY_PARTITION_HANDLER’);
    alter database start logical standby apply;References:1. DBMS_LOGSTDBY.SKIP – Data Guard Concepts and Administrationhttp://download-west.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ls.htm- CHDDIHHI

Leave a Reply

You must be logged in to post a comment.