4 Replies Latest reply on Apr 21, 2017 7:39 AM by Marco Mischke

    Purge OS-Auditfiles on Standby Site

    Peter_L_

      Hi,

      for a customer I am currently enabling audit on a 12c database under OL 7.3.

      I've also configured a purging auditdata mechanism as follows:

      - setting a archive timestamp on DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS

      - creating purging jobs for these 3 types

      All jobs works fine on primary site. Due redo-apply the auditdata in DB-tables are purged on both sites. But the OS-Audit files are purged only on primary.

      Maybe because the standby DB is in read only there is no scheduler available, which can run the purge job.

      If I run manually

      DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

      audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

      container => DBMS_AUDIT_MGMT.CONTAINER_ALL

      );

      on standby, all OS audit files will purged there, but it doesn't work via DBMS_SCHEDULER.

      Is there another recommended way to implement purging jobs in a DataGuard env on stanby site?

      Should I create a job at OS-Level (cron...) on standby site? Or what is my mistake here?

       

      Best regards Peter

        • 2. Re: Purge OS-Auditfiles on Standby Site
          Peter_L_

          Many thanks,

          but the "DBMS_AUDIT_MGMT.CREATE_PURGE_JOB" has no "Role"-Option. O.K. this is no problem, I can use the DBMS_SCHEDULER package with role option, as you described.

          Regards Peter

          Edit:

          Sorry the database_role Option in DBMS_SCHEDULER allows only  'PRIMARY or 'LOGICAL STANDBY'. So I think it's not possible to create a DBMS_SCHEDULER Job on the Standby site to clear OS-Audit files there.

          I think I have to do it with an OS Job.

          • 3. Re: Purge OS-Auditfiles on Standby Site
            MsJ

            For physical standby database any changes made to Scheduler objects or any database changes made by Scheduler jobs on the primary database are applied to the physical standby like any other database changes. For the primary database and logical standby databases, there is additional functionality that enables you to specify that a job can run only when the database is in the role of the primary database, or can run only when the database is in a logical standby role.

            • 4. Re: Purge OS-Auditfiles on Standby Site
              Marco Mischke

              Hi,

               

              since a physical standby is typically in MOUNT state, there is no chance to execute DBMS_AUDIT_MGMT procedures.

               

              SQL> select database_role from v$database;

               

               

              DATABASE_ROLE

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

              PHYSICAL STANDBY

               

               

              SQL> desc dbms_audit_mgmt

              ERROR:

              ORA-04043: object dbms_audit_mgmt does not exist

               

              The package is available only when the database is OPEN:

               

              SQL> select database_role from v$database;

               

               

              DATABASE_ROLE

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

              PRIMARY

               

               

              SQL> desc dbms_audit_mgmt;

              PROCEDURE CLEAN_AUDIT_TRAIL

              Argument Name                  Type                    In/Out Default?

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

              AUDIT_TRAIL_TYPE               BINARY_INTEGER          IN

              USE_LAST_ARCH_TIMESTAMP        BOOLEAN                 IN     DEFAULT

              [...]

               

              So the only chance is to clean those records via cron or similar. That's the way I do it for my systems.

               

              HTH

              Marco