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
Post a Comment