4 Replies Latest reply: Jan 3, 2014 5:46 AM by user1049243 RSS

    DBMS_LOGSTDBY.SKIP_ERROR  doesn't work

    user1049243

      Hi guru's,

      I have e a 11.2.0.3 sql-apply dataguard running (prd1 > prd2) and I want to skip grants and revokes on the scott schema.I have done the following (based on the (http://oracledocs.shu.ac.uk/oracle/B28359_01/appdev.111/b28419/d_lsbydb.htm#i997288 document)

       

      ALTER DATABASE STOP LOGICAL STANDBY APPLY;

       

       

      CREATE OR REPLACE PROCEDURE OSCAR (

         old_stmt    IN  VARCHAR2,

         stmt_type   IN  VARCHAR2,

         schema      IN  VARCHAR2,

         name        IN  VARCHAR2,

         xidusn      IN  NUMBER,

         xidslt      IN  NUMBER,

         xidsqn      IN  NUMBER,

         error       IN  VARCHAR2,

        new_stmt    OUT VARCHAR2

      ) AS

      BEGIN

        -- Default to what we already have

        new_stmt := old_stmt;

        -- Ignore any GRANT errors on SYS or HR schemas

      IF  INSTR(UPPER(old_stmt),'GRANT') > 0

      AND UPPER(schema) = 'SCOTT'

      THEN

         new_stmt := NULL;

      END IF;

      END oscar;

      /

       

       

      EXECUTE DBMS_LOGSTDBY.SKIP_ERROR ( stmt => 'NON_SCHEMA_DDL',schema_name => NULL, object_name => NULL, proc_name => 'SYS.OSCAR')

      --EXECUTE DBMS_LOGSTDBY.UNSKIP_ERROR ( stmt => 'NON_SCHEMA_DDL')

       

       

      ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

       

      But still grants like grant select on scott.emp to dbsnmp is applyed on the prd2 database?

      So what I am donign wrong ?

      thanks for any help.

      regards oScar

        • 1. Re: DBMS_LOGSTDBY.SKIP_ERROR  doesn't work
          mseberg

          Hello;

           

          This:

           

          EXECUTE DBMS_LOGSTDBY.SKIP_ERROR ( stmt => 'NON_SCHEMA_DDL',schema_name => NULL, object_name => NULL, proc_name => 'SYS.OSCAR')

           

          Appears to be correct.

           

          Oscar looks incomplete to me:

           

          What I would try:

           

          CREATE OR REPLACE PROCEDURE OSCAR(

            old_stmt IN VARCHAR2,

            stmt_type IN VARCHAR2,

            schema IN VARCHAR2,

            name IN VARCHAR2,

            xidusn IN NUMBER,

            xidslt IN NUMBER,

            xidsqn IN NUMBER,

            error IN VARCHAR2,

            new_stmt OUT VARCHAR2) AS

           

          BEGIN

            -- Default to what we already have

            new_stmt := old_stmt;

           

            -- Ignore any GRANT errors on SYS or SCOTT schemas

            IF INSTR(UPPER(old_stmt),'GRANT') > 0 THEN

            IF schema IS NULL

            OR (schema IS NOT NULL AND (UPPER(schema) = 'SYS'

            OR UPPER(schema) = 'SCOTT') THEN

            new_stmt := NULL;

            -- record the fact that an error was skipped

            NULL;

            END IF;

            END IF;

          END OSCAR;

          /

           

           

          Best Regards

           

          mseberg

          • 2. Re: DBMS_LOGSTDBY.SKIP_ERROR  doesn't work
            e2e0f59c-7b07-4063-8dc6-a6a6a4cf8ccf

            Thank you for the reply but the statement looks not correct;

             

            SQL> CREATE OR REPLACE PROCEDURE OSCAR(

              2    old_stmt IN VARCHAR2,

              3    stmt_type IN VARCHAR2,

              4    schema IN VARCHAR2,

              5    name IN VARCHAR2,

              6    xidusn IN NUMBER,

              7    xidslt IN NUMBER,

              8    xidsqn IN NUMBER,

              9    error IN VARCHAR2,

            10    new_stmt OUT VARCHAR2) AS

            11

            12  BEGIN

            13    -- Default to what we already have

            14    new_stmt := old_stmt;

            15

            16    -- Ignore any GRANT errors on SYS or SCOTT schemas

            17    IF INSTR(UPPER(old_stmt),'GRANT') > 0

            18    THEN

            19    IF schema IS NULL

            20    OR (schema IS NOT NULL AND (UPPER(schema) = 'SYS'

            21    OR UPPER(schema) = 'SCOTT') THEN

            22    new_stmt := NULL;

            23    -- record the fact that an error was skipped

            24    NULL;

            25    END IF;

            26    END IF;

            27  END OSCAR;

            28  /

             

             

            Warning: Procedure created with compilation errors.

             

             

            SQL> show error

            Errors for PROCEDURE OSCAR:

             

             

            LINE/COL ERROR

            -------- -----------------------------------------------------------------

            21/31    PLS-00103: Encountered the symbol "THEN" when expecting one of

                     the following:

                     ) , * & = - + < / > at in is mod remainder not rem

                     <an exponent (**)> <> or != or ~= >= <= <> and or like like2

                     like4 likec as between overlaps || multiset year day member

                     submultiset

                     The symbol ")" was substituted for "THEN" to continue.

            • 3. Re: DBMS_LOGSTDBY.SKIP_ERROR  doesn't work
              user1049243

              Hi I correct you procedure;

               

              SQL> CREATE OR REPLACE PROCEDURE OSCAR(

                2    old_stmt IN VARCHAR2,

                3    stmt_type IN VARCHAR2,

                4    schema IN VARCHAR2,

                5    name IN VARCHAR2,

                6    xidusn IN NUMBER,

                7    xidslt IN NUMBER,

                8    xidsqn IN NUMBER,

                9    error IN VARCHAR2,

              10    new_stmt OUT VARCHAR2) AS

              11

              12  BEGIN

              13    -- Default to what we already have

              14    new_stmt := old_stmt;

              15

              16    -- Ignore any GRANT errors on SYS or SCOTT schemas

              17    IF INSTR(UPPER(old_stmt),'GRANT') > 0

              18    THEN

              19    IF schema IS NULL

              20    OR (schema IS NOT NULL AND (UPPER(schema) = 'SYS'

              21    OR UPPER(schema) = 'SCOTT'))

              22    THEN

              23    new_stmt := NULL;

              24    -- record the fact that an error was skipped

              25    NULL;

              26    END IF;

              27    END IF;

              28  END OSCAR;

              29  /

               

               

              Procedure created.

               

               

              SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

               

               

              Database altered.

               

               

              SQL> EXECUTE DBMS_LOGSTDBY.SKIP_ERROR ( stmt => 'NON_SCHEMA_DDL',schema_name => NULL, object_name => NULL, proc_name =>

              'SYS.OSCAR')

               

               

              PL/SQL procedure successfully completed.

               

               

              SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

               

               

              Database altered.

               

              I have test it but it still doesn't work.

              • 4. Re: DBMS_LOGSTDBY.SKIP_ERROR  doesn't work
                user1049243

                Thank you for your help I  have solved it now.