10gR2 Logical StdBy Skip_Error

From: japplewhite@xxxxxxxxxxxxx
To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 25 Aug 2011 08:30:47 -0500
We've been running a 64 bit 10.2.0.4 EE Logical Standby, as well as a 
Physical Standby for the same Primary, on RH Linux for over a year - in 
Maximum Performance mod.  Occasionally we have to re-create the Log StdBy 
because either SQL Apply or Auto Delete stops working and we cannot find a 
solution.  We have such a situation now, with SQL Apply hung on an error 
generated in the Primary from a CTAS statement - ""ORA-00955: name is 
already used by an existing object".

I tried creating both a DBMS_LogStdBy.Skip_Error and DBMS_LogStdBy.Skip 
procedure, but neither worked.  After restarting SQL Apply, it processed 
the preceeding ArcLogs, then hung on the same error.

Has anybody successfully used either of the above or another method to 
skip either errors or specific DDL?  We have a successful Skip procedure 
that changes the path for datafiles when they are added to the Primary, so 
that does work.

BTW, has anyone else noticed how screwy the PL/SQL Packages Reference doc 
is wrt DBMS_LogStdBy.Skip_Error specs?  The example doesn't match the 
spec.

Here is what I successfully ran to create the Skip_Error procedure. Please 
tell me if I did it wrong.

CREATE OR REPLACE PROCEDURE sys.handle_error_ddl (
   Statement        IN  VARCHAR2
  ,Statement_Type   IN  VARCHAR2
  ,Schema           IN  VARCHAR2
  ,Name             IN  VARCHAR2
  ,xidusn           IN  NUMBER
  ,xidslt           IN  NUMBER
  ,xidsqn           IN  NUMBER
  ,Error            IN  VARCHAR2
  ,New_Error       OUT  VARCHAR2
) AS

BEGIN
  -- Default to what we already have
  New_Error := Error ;

  -- Ignore any GRANT or other errors in any schema
  If  Instr ( Upper ( Statement ) , 'GRANT'  ) > 0
   Or Instr ( Upper ( Statement ) , 'REVOKE' ) > 0
   Or Instr ( Upper ( Statement ) , 'CREATE' ) > 0
   Or Instr ( Upper ( Statement ) , 'DROP'   ) > 0
   Or Instr ( Upper ( Statement ) , 'ALTER'  ) > 0
   Or Instr ( Upper ( Statement ) , 'TABLE'  ) > 0
  Then
      New_Error := NULL ;
  End If ;

END handle_error_ddl;
/

-- Register the error handler with SQL Apply:

EXECUTE DBMS_LOGSTDBY.SKIP_ERROR ( -
     stmt        => 'NON_SCHEMA_DDL', -
     schema_name =>  NULL, -
     object_name =>  NULL, -
     proc_name   => 'SYS.HANDLE_ERROR_DDL', -
     use_like    =>  Null , -
     esc         =>  Null)
/

Thanks.

Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)  /  512.935.5929 (pager)
References:
- ACID
  - From: Jonathan Lewis
10gR2 Logical StdBy Skip_Error

Other related posts:
