Amsterdam DBA notes: IZO-060

I have working through some Oracle 12c ILM examples, I didn't find that many blog articles covering this feature but was happy to follow : http://gavinsoorma.com/2013/09/oracle-12c-new-feature-heat-map-and-automatic-data-optimization/

First of all as SYS enable heat mapping:

SQL> alter system set heat_map=on scope=both;

System altered.

SQL> exec dbms_ilm_admin.set_heat_map_start(start_date => sysdate - 30);

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN FROM dba_heat_map_segment;

no rows selected

The following also didn't trigger any new records in dba_heat_map_segment:

SQL> select count(*) from scott.emp;

COUNT(*)

----------

SQL> select OBJECT_NAME,SEGMENT_WRITE_TIME , SEGMENT_READ_TIME, FULL_SCAN FROM dba_heat_map_segment;

no rows selected

However after adding a policy:

SQL> ALTER TABLE scott.myobjects ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;

Table altered.

and re-runing the the select count(*) from scott.myobjects:

SQL> col OBJECT_NAME form a30

SQL> select object_name, track_time, segment_write, FULL_SCAN, lookup_scan from DBA_HEAT_MAP_SEG_HISTOGRAM where object_name='MYOBJECTS' and owner = 'SCOTT';

OBJECT_NAME TRACK_TIME SEG FUL LOO

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

MYOBJECTS 10-may-15 09:35:28 NO YES NO

I also decide to reduce my policy from 30 days to 1 day:

SQL> alter table scott.myobjects ILM DELETE POLICY P1;

Table altered.

SQL> select policy_name, action_type, scope, compression_level, condition_type, condition_days from dba_ilmdatamovementpolicies order by policy_name;

no rows selected

SQL> ALTER TABLE scott.myobjects ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 2 DAYS OF NO MODIFICATION;

Table altered.

SQL> select policy_name, action_type, scope, compression_level, condition_type, condition_days from dba_ilmdatamovementpolicies order by policy_name;

POLICY_NAME

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

ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE

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

CONDITION_DAYS

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

P41

COMPRESSION SEGMENT ADVANCED LAST MODIFICATION TIME

Next manually kick off dbms_ilm.execute_ilm:

declare

v_executionid number;

begin

dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA, execution_mode => dbms_ilm.ilm_execution_offline, task_id => v_executionid);

end;

SQL>

PL/SQL procedure successfully completed

this ran with task_id=82:

SQL> select task_id, start_time as start_time from user_ilmtasks;

TASK_ID START_TIME

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

82 10-MAY-15 09.54.13.708088 PM

but didn't do anything

set line 150

col POLICY_NAME form a15

col OBJECT_NAME form a30

col SELECTED_FOR_EXECUTION form a10

col JOB_NAME form a10

select task_id, policy_name, object_name, selected_for_execution, job_name from user_ilmevaluationdetails;

SQL>

TASK_ID POLICY_NAME OBJECT_NAME SELECTED_FOR_EXECUTION JOB_NAME

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

82 P41 MYOBJECTS PRECONDITION NOT SATISFIED

Let's give it 24 hours...

Summary

Oracle 12c introduces "unified all" so all the various audit activities :

Ability to Audit Any Role.
Auditing Application Context Values.
Auditing Oracle Database Real Application Security Events.
Auditing Oracle Recovery Manager Events.
Auditing Oracle Database Vault Events.
Auditing Oracle Label Security Events.
Auditing Oracle Data Mining Events.
Auditing Oracle Data Pump Events.
Auditing Oracle SQL*Loader Direct Load Path Events.
Moving Operating System Audit Records into the Unified Audit Trail. https://blogs.oracle.com/imc/entry/oracle_database_12c_new_unified

can be viewed in the single sys.unified_audit_trail table.

As above you can now audit datapump operations, below I show how to set this up and some sample output from the new sys.unified_audit_trail table.

Details - datapump example

I was initially following: http://oracle-base.com/articles/12c/data-pump-enhancements-12cr1.php

So I set up my new audit policy and applied to the scott

SQL> CONN / AS SYSDBA

Connected.

SQL> CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;

Audit policy created.

SQL> AUDIT POLICY audit_dp_all_policy BY scott;

Audit succeeded.

SQL> EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.

and kicked off my export:

[oracle@ora12c66 ~]$ expdp system/qwe123@ora12c66/orcl12c tables=scott.emp directory=TEMP_DIR

Export: Release 12.1.0.1.0 - Production on Wed Mar 18 20:20:52 2015

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

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

Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@ora12c66/orcl12c tables=scott.emp directory=TEMP_DIR

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "SCOTT"."EMP" 8.742 KB 14 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/tmp/expdat.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 18 20:21:13 2015 elapsed 0 00:00:20

and initially this failed to generate a unified_audit_trail record:

SET LINESIZE 200

COLUMN event_timestamp FORMAT A30

COLUMN dp_text_parameters1 FORMAT A30

COLUMN dp_boolean_parameters1 FORMAT A30

SQL> SELECT event_timestamp, dp_text_parameters1, dp_boolean_parameters1 FROM sys.unified_audit_trail WHERE audit_type = 'Datapump';

no rows selected

my problem was that I was only auditing on the user_name scott (i.e. not the scott schema objects)

SQL> col USER_NAME form a30

SQL> col POLICY_NAME form a30

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like '%DP%';

USER_NAME POLICY_NAME ENABLED_ SUC FAI

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

SCOTT AUDIT_DP_ALL_POLICY BY YES YES

i.e. not the user_name system and after

SQL> AUDIT POLICY audit_dp_all_policy BY system;

Audit succeeded.

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like '%DP%';

USER_NAME POLICY_NAME ENABLED_ SUC FAI

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

SCOTT AUDIT_DP_ALL_POLICY BY YES YES

SYSTEM AUDIT_DP_ALL_POLICY BY YES YES

and rerun my expdp:

SQL> SELECT event_timestamp, dp_text_parameters1, dp_boolean_parameters1 FROM sys.unified_audit_trail WHERE audit_type = 'Datapump';

EVENT_TIMESTAMP DP_TEXT_PARAMETERS1 DP_BOOLEAN_PARAMETERS1

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

18-MAR-15 08.28.36.157025 PM MASTER TABLE: "SYSTEM"."SYS_E MASTER_ONLY: FALSE, DATA_ONLY:

XPORT_TABLE_01" , JOB_TYPE: EX FALSE, METADATA_ONLY: FALSE,

PORT, METADATA_JOB_MODE: TABLE DUMPFILE_PRESENT: TRUE, JOB_RE

_EXPORT, JOB VERSION: 12.1.0.0 STARTED: FALSE

.0, ACCESS METHOD: AUTOMATIC,

DATA OPTIONS: 0, DUMPER DIRECT

ORY: NULL REMOTE LINK: NULL,

TABLE EXISTS: NULL, PARTITION

OPTIONS: NONE

manually making this a bit more readable:

EVENT_TIMESTAMP
---------------------------------------------------------------------------
18-MAR-15 08.28.36.157025 PM

DP_TEXT_PARAMETERS1
--------------------------------------------------------------------------------
MASTER TABLE: "SYSTEM"."SYS_EXPORT_TABLE_01" , JOB_TYPE: EXPORT, METADATA_JOB_M
ODE: TABLE_EXPORT, JOB VERSION: 12.1.0.0.0, ACCESS METHOD: AUTOMATIC, DATA OPTIO
NS: 0, DUMPER DIRECTORY: NULL REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITION
OPTIONS: NONE

DP_BOOLEAN_PARAMETERS1
--------------------------------------------------------------------------------

MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TR UE, JOB_RESTARTED: FALSE

alternatively I could have selected all users:

SQL> AUDIT POLICY audit_dp_all_policy;

Audit succeeded.

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like '%DP%';

USER_NAME POLICY_NAME ENABLED_ SUC FAI

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

SCOTT AUDIT_DP_ALL_POLICY BY YES YES

SYSTEM AUDIT_DP_ALL_POLICY BY YES YES

ALL USERS AUDIT_DP_ALL_POLICY BY YES YES

Strangle it appears that my

SQL> select value from v$option where PARAMETER = 'Unified Auditing';

VALUE

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

FALSE

I guess this refers to some more global / default level auditing?

Amsterdam DBA notes

Sunday, May 10, 2015

Automatic Data Optimization (ADO) DBMS_ILM example - part one

Wednesday, March 18, 2015

12c Unified Auditing expdp example

Summary

Details - datapump example