datapump and unified audit



So the new year is upon us and I've not written anything for a couple of weeks - thought it was about time i blogged something.
I've been looking further into some of the new 12c stuff in preparation for a possible large scale upgrade project this year. I got to the audit part, which to be honest is one of the least glamorous parts of DBA work - but it has to be done.
I thought i'd take the chance to learn how the new audit stuff works and combine it with the supposed new feature of being able to audit datapump commands. I was thinking that maybe there is a way to track when someone does an export using the compression=all option where the advanced compression option is required.

I did my testing on 12.1.0.2 (but it shouldn't be any different in 12.1.0.1 i think).

The testing i did in a new style PDB/CDB combo rather than a traditional style db - but again there is essentially no difference in how it would work.

I'm doing the testing in a CDB called Rich with a PDB in it called MARKER

First up here is a quick overview of the setup


SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MARKER                         READ WRITE NO
         4 REMOTEDB                       READ WRITE NO

Now lets switch to the marker pdb (and i do wish they'd not made the command container= - it's more logical for it to be plug=/pluggable= - something like that?) - anyway we switch and create a new user to do the export as

SQL> alter session set container=marker;

Session altered.

SQL> create user testuser identified by testuser;

User created.

SQL> grant dba to testuser;

Grant succeeded.

Now lets create some of this new audit setup - we can do this as the new user i created as i was lazy and granted it DBA.

So we login

[oracle@server-name]:RICH:[~]# sqlplus testuser/testuser@marker

And create a policy to audit all datapump activities

SQL>  create audit policy dp_usage actions component=datapump ALL;

Audit policy created.

Now we set this as an active policy

SQL> audit policy dp_usage;

Audit succeeded.

Now we do an export

[oracle@server-name]:RICH:[~]# expdp testuser/testuser@marker directory=tmp

Export: Release 12.1.0.2.0 - Production on Wed Jan 7 10:18:07 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights
reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit  Production
With the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
Starting "testuser"."SYS_EXPORT_SCHEMA_01":  testuser/********@marker

directory=tmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type

SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "testuser"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
***************************************************************************

***
Dump file set for testuser.SYS_EXPORT_SCHEMA_01 is:
  /tmp/expdat.dmp
Job "testuser"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 7

10:18:40 2015 elapsed 0 00:00:32

SO that ran OK - lets see if the audit record got created.

[oracle@server-name]:RICH:[~]# sqlplus testuser/testuser@marker

Lets flush any entries still in memory to the table (as the new method to be more efficient by default logs to memory and asynchronously writes to a table)

SQL> exec sys.dbms_audit_mgmt.flush_unified_audit_trail;
BEGIN sys.dbms_audit_mgmt.flush_unified_audit_trail; END;

*
ERROR at line 1:
ORA-46276: DBMS_AUDIT_MGMT operation on unified audit trail failed
ORA-55906: Secure file log [id: 0 name: ORA$AUDIT_NEXTGEN_LOG] does not

exist
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1746
ORA-06512: at line 1

And this is where the stuff that i'd read didn't tally up with reality (and to be honest when i read it it didn't sound right)

Let's reconnect to the CDB and try it there maybe that's the problem?

SQL> exec sys.dbms_audit_mgmt.flush_unified_audit_trail;
BEGIN sys.dbms_audit_mgmt.flush_unified_audit_trail; END;

*
ERROR at line 1:
ORA-46276: DBMS_AUDIT_MGMT operation on unified audit trail failed
ORA-55906: Secure file log [id: 0 name: ORA$AUDIT_NEXTGEN_LOG] does not

exist
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1746
ORA-06512: at line 1

So - still fails, lets just see if there is anything in the log

SQL> select count(*) from unified_audit_trail;

  COUNT(*)
----------
         0

No - as i thought. Then i discovered a slightly different option to flush the audit trail - lets try that

SQL> BEGIN
 DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(
  CONTAINER  => DBMS_AUDIT_MGMT.CONTAINER_ALL);
END;
/  2    3    4    5
BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1746
ORA-06512: at line 2

But still no...

Hmm - let's go back and look at some of the basics - other stuff i read said it had to actually manually be enabled before you do anything - so lets check the status of the option

SQL> select * from v$option where parameter like 'Uni%'
  2  /

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
Unified Auditing
FALSE                                                                     0

Sure enough it's switched off.....

So lets actually make sure this is enabled - to do this we have to recompile oracle with the option on - this isn't actually available in chopt yet - you have to do it the old way - see the chopt output below

[oracle@server-name]:RICH:[/oracle/12.1.0.2/bin]# chopt

usage:

chopt <enable|disable> <option>

options:
                  dm = Oracle Data Mining RDBMS Files
                olap = Oracle OLAP
        partitioning = Oracle Partitioning
                 rat = Oracle Real Application Testing

e.g. chopt enable rat

So lets do it the old style way.....

[oracle@server-name]:RICH:[/oracle/12.1.0.2/bin]# cd $ORACLE_HOME/rdbms/lib

[oracle@server-name]:RICH:[/oracle/12.1.0.2/rdbms/lib]# make -f ins_rdbms.mk uniaud_on ioracle
/usr/bin/ar d /oracle/12.1.0.2/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /oracle/12.1.0.2/rdbms/lib/libknlopt.a

/oracle/12.1.0.2/rdbms/lib/kzaiang.o
chmod 755 /oracle/12.1.0.2/bin

 - Linking Oracle
rm -f /oracle/12.1.0.2/rdbms/lib/oracle
/oracle/12.1.0.2/bin/orald  -o /oracle/12.1.0.2/rdbms/lib/oracle -m64 -z

noexecstack -Wl,--disable-new-dtags -L/oracle/12.1.0.2/rdbms/lib/ -

L/oracle/12.1.0.2/lib/ -L/oracle/12.1.0.2/lib/stubs/   -Wl,-E

/oracle/12.1.0.2/rdbms/lib/opimai.o /oracle/12.1.0.2/rdbms/lib/ssoraed.o

/oracle/12.1.0.2/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--

no-whole-archive /oracle/12.1.0.2/lib/nautab.o /oracle/12.1.0.2/lib/naeet.o

/oracle/12.1.0.2/lib/naect.o /oracle/12.1.0.2/lib/naedhs.o

/oracle/12.1.0.2/rdbms/lib/config.o  -lserver12 -lodm12 -lcell12 -lnnet12

-lskgxp12 -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -

lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -

lclient12  -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv

/oracle/12.1.0.2/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ;

then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12  -lrt -lplp12 -

lserver12 -lclient12  -lvsn12 -lcommon12 -lgeneric12 `if [ -f

/oracle/12.1.0.2/lib/libavserver12.a ] ; then echo "-lavserver12" ; else

echo "-lavstub12"; fi` `if [ -f /oracle/12.1.0.2/lib/libavclient12.a ] ;

then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12  -lrt -lplp12 -

ljavavm12 -lserver12  -lwwg  `cat /oracle/12.1.0.2/lib/ldflags`    -

lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat

/oracle/12.1.0.2/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12

-lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12

-lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -

lcore12 -lnls12 -lztkg12 `cat /oracle/12.1.0.2/lib/ldflags`    -lncrypt12

-lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /oracle/12.1.0.2/lib/ldflags`  

 -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12   -

lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -

lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar

tv /oracle/12.1.0.2/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null

2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -

L/oracle/12.1.0.2/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -

lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12

-lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -

lxml12  -lgeneric12 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -

lippdcemerged -lippsemerged -lippdcmerged  -lippsmerged -lippcore  -

lippcpemerged -lippcpmerged  -lsnls12 -lnls12  -lcore12 -lsnls12 -lnls12 -

lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -

lcore12 -lnls12 -lsnls12 -lunls12  -lsnls12 -lnls12  -lcore12 -lsnls12 -

lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12

-lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12  -laio -lons    `cat

/oracle/12.1.0.2/lib/sysliblist` -Wl,-rpath,/oracle/12.1.0.2/lib -lm   

`cat /oracle/12.1.0.2/lib/sysliblist` -ldl -lm   -L/oracle/12.1.0.2/lib
test ! -f /oracle/12.1.0.2/bin/oracle ||\
           mv -f /oracle/12.1.0.2/bin/oracle /oracle/12.1.0.2/bin/oracleO
mv /oracle/12.1.0.2/rdbms/lib/oracle /oracle/12.1.0.2/bin/oracle
chmod 6751 /oracle/12.1.0.2/bin/oracle


So that's recompiled with the option on

[oracle@server-name]:RICH:[/oracle/12.1.0.2/rdbms/lib]# s

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 7 10:56:59 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options


SQL>

And we can see already the banner changed to say it's enabled

Now lets bounce the DB to make sure we really did pick this up (to be honest the db should have been down when i recompiled.......)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORA-00600: internal error code, arguments: [krsh_fsga_sgaq.ds_not_found],

[], [], [], [], [], [], [], [], [], [], []

:-) - i think that's a direct result of the recompile without the db being down - so i'm ignoring it....

So i'll do a quick cycle through to make sure it's all clean now

[oracle@server-name]:RICH:[/oracle/12.1.0.2/rdbms/lib]# s

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 7 10:58:10 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1409286144 bytes
Fixed Size                  3710736 bytes
Variable Size            1342177520 bytes
Database Buffers           50331648 bytes
Redo Buffers               13066240 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1409286144 bytes
Fixed Size                  3710736 bytes
Variable Size            1342177520 bytes
Database Buffers           50331648 bytes
Redo Buffers               13066240 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

- 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

So all looks fine

Let's try a flush now

SQL> exec sys.dbms_audit_mgmt.flush_unified_audit_trail;
BEGIN sys.dbms_audit_mgmt.flush_unified_audit_trail; END;

*
ERROR at line 1:
ORA-46276: DBMS_AUDIT_MGMT operation on unified audit trail failed
ORA-55906: Secure file log [id: 0 name: ORA$AUDIT_NEXTGEN_LOG] does not

exist
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1746
ORA-06512: at line 1

Hmm not in the script..... - lets try it just for this pdb

SQL> BEGIN
 DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(
  CONTAINER  => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

That's better, out of interest lets trying doing all of them

SQL> BEGIN
 DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(
  CONTAINER  => DBMS_AUDIT_MGMT.CONTAINER_ALL);
END;
/  2    3    4    5
BEGIN
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1746
ORA-06512: at line 2

So that's not possible - lets try it in the container

SQL> BEGIN
 DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(
  CONTAINER  => DBMS_AUDIT_MGMT.CONTAINER_ALL);
END;
/  2    3    4    5
BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1746
ORA-06512: at line 2

Hmm so one of the PDB's is playing up - i'm ignoring this for now.....

Let's see what's in the audit trail now

SQL> select count(*) from unified_audit_trail;

  COUNT(*)
----------
         4

hooray progress - something is being logged

Lets do a few exports

[oracle@server-name]:RICH:[/oracle/12.1.0.2/rdbms/lib]# expdp testuser/testuser@marker directory=tmp
[oracle@server-name]:RICH:[/oracle/12.1.0.2/rdbms/lib]# expdp testuser/testuser@marker directory=tmp compresion=all
[oracle@server-name]:RICH:[/oracle/12.1.0.2/rdbms/lib]# echo "directory=tmp reuse_dumpfiles=y compression=all" > temp.par
[oracle@server-name]:RICH:[/oracle/12.1.0.2/rdbms/lib]# expdp testuser/testuser@marker parfile=temp.par

OK - 3 different variations on the same thing - lets see what is logged

Lets flush  to be sure they are in the table.

SQL> BEGIN
 DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(
  CONTAINER  => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/  2    3    4    5

PL/SQL procedure successfully completed.

Now we check the log

Not the easiest format to read how I've displayed it - but here you go

SQL> select DP_TEXT_PARAMETERS1,DP_BOOLEAN_PARAMETERS1

,CLIENT_PROGRAM_NAME,event_timestamp from unified_audit_trail
  2   where DP_TEXT_PARAMETERS1 is not null;

MASTER TABLE:  "testuser"."SYS_EXPORT_SCHEMA_01" , JOB_TYPE: EXPORT,

METADATA_JOB_MODE: SCHEMA_EXPORT, JOB VERSION: 12.0.0, ACCESS METHOD:

AUTOMATIC, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL,

TABLE EXISTS: NULL, PARTITION OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE,

DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE                               

                                                             oracle@server-name

(DW00)                             07-JAN-15 11.06.59.281884 AM

MASTER TABLE:  "testuser"."SYS_EXPORT_SCHEMA_01" , JOB_TYPE: EXPORT,

METADATA_JOB_MODE: SCHEMA_EXPORT, JOB VERSION: 12.0.0, ACCESS METHOD:

AUTOMATIC, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL,

TABLE EXISTS: NULL, PARTITION OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE,

DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE                               

                                                             oracle@server-name

(DW00)                             07-JAN-15 11.07.34.239826 AM

MASTER TABLE:  "testuser"."SYS_EXPORT_SCHEMA_01" , JOB_TYPE: EXPORT,

METADATA_JOB_MODE: SCHEMA_EXPORT, JOB VERSION: 12.0.0, ACCESS METHOD:

AUTOMATIC, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL,

TABLE EXISTS: NULL, PARTITION OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE,

DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE                               

                                                             oracle@server-name

(DW00)                             07-JAN-15 11.08.34.156892 AM


SQL> set long 32000
SQL> /
MASTER TABLE:  "testuser"."SYS_EXPORT_SCHEMA_01" , JOB_TYPE: EXPORT,

METADATA_JOB_MODE: SCHEMA_EXPORT, JOB VERSION: 12.0.0, ACCESS METHOD:

AUTOMATIC, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL,

TABLE EXISTS: NULL, PARTITION OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE,

DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE                               

                                                             oracle@server-name

(DW00)                             07-JAN-15 11.06.59.281884 AM

MASTER TABLE:  "testuser"."SYS_EXPORT_SCHEMA_01" , JOB_TYPE: EXPORT,

METADATA_JOB_MODE: SCHEMA_EXPORT, JOB VERSION: 12.0.0, ACCESS METHOD:

AUTOMATIC, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL,

TABLE EXISTS: NULL, PARTITION OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE,

DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE                               

                                                             oracle@server-name

(DW00)                             07-JAN-15 11.07.34.239826 AM

MASTER TABLE:  "testuser"."SYS_EXPORT_SCHEMA_01" , JOB_TYPE: EXPORT,

METADATA_JOB_MODE: SCHEMA_EXPORT, JOB VERSION: 12.0.0, ACCESS METHOD:

AUTOMATIC, DATA OPTIONS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL,

TABLE EXISTS: NULL, PARTITION OPTIONS: NONE
MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE,

DUMPFILE_PRESENT: TRUE, JOB_RESTARTED: FALSE                               

                                                             oracle@server-name

(DW00)                             07-JAN-15 11.08.34.156892 AM

So - it's worked OK that's the good news..... and the new auditing stuff looks quite neat.

The bad news is half the parameter aren't logged - including the compression one i wanted.....

Oh well i learnt something and i assume the other stuff will get added in later patches/upgrades

Comments