10gR2 Logical StdBy Skip_Error

From: <japplewhite_at_austinisd.org>
Date: Thu, 25 Aug 2011 08:30:47 -0500
Message-ID: <OF9B5EACE5.95AF44C2-ON862578F7.0048E237-862578F7.004A47AE_at_austinisd.org>

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)

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 25 2011 - 08:30:47 CDT