2 Responses to “logical standby ORA-1119”

1. lizhenxu Says:
   
   7月 31, 2012 at 上午 8:39

   ORA-18008: DDL Not Applied in Logical Standby [ID 233730.1] The information in this article applies to: 9.2.0.1 and higher versions of oracle with a logical standby setup. Symptom(s) ~~~~~~~~~~ You created the logical standby as per the documentation successfully. Now when a table is created in primary, you noticed it is not getting created in the logical standby. Change(s) ~~~~~~~~~~ While setting your Primary, you dropped some unwanted users which includes OUTLN. Fix ~~~~ Check the alert.log of your logical standby database. You can see in the alert the create table statement accompanied by ora-18008: Example: ======= LSP0 started with pid=15 LOGSTDBY event: ORA-16111: log mining and apply setting up Thu Mar 27 04:05:16 2003 Attempt to start background Logical Standby process Completed: alter database start logical standby apply Thu Mar 27 04:05:20 2003 LOGSTDBY event: ORA-18008: cannot find OUTLN schema LOGSTDBY stmt: create table TEST_CHANDRA ( I INT, J INT) Due to the error ORA-18008, your DDL (create table) is not applied in the logical standby. To resolve this issue, recreate the user OUTLN and then try the log apply statement again.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

   ORA-18008: DDL Not Applied in Logical Standby [ID 233730.1] The information in this article applies to:   9.2.0.1 and higher versions of oracle with a logical standby setup.     Symptom(s) ~~~~~~~~~~   You created the logical standby as per the documentation successfully. Now when a table is created in primary, you noticed it is not getting created in the logical standby.   Change(s) ~~~~~~~~~~   While setting your Primary, you dropped some unwanted users which includes OUTLN.     Fix ~~~~   Check the alert.log of your logical standby database. You can see in the alert the create table statement accompanied by ora-18008:   Example: ======= LSP0 started with pid=15 LOGSTDBY event: ORA-16111: log mining and apply setting up Thu Mar 27 04:05:16 2003 Attempt to start background Logical Standby process Completed: alter database start logical standby apply Thu Mar 27 04:05:20 2003 LOGSTDBY event: ORA-18008: cannot find OUTLN schema LOGSTDBY stmt: create table TEST_CHANDRA ( I INT, J INT)   Due to the error ORA-18008, your DDL (create table) is not applied in the logical standby. To resolve this issue, recreate the user OUTLN and then try the log apply statement again.

2. lizhenxu Says:
   
   7月 31, 2012 at 上午 8:40

   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.