Why you should read this blog post? You need to take care of the Audit function from OEM CC even if you don’t want to use it. It’s enabled by default and needs to be managed!
Housekeeping for audit
Per default the audit function of oem is configured with very basic audit rules but as example every login or logout creates an audit record inside the database. The audit table is configured quite well as it’s daily range partitioned. But an overhead is always there and the space consumption was more then 10% of the whole sysman schema. Cloud Control has a very conventional way to manage audit settings… emcli. The default configuration looks like:
emcli show_audit_settings User Activity Audit : Disabled (11 out of 171 operations) Externalization Switch : Disabled Directory : File Prefix : em_audit File Size : 5000000 Bytes Data Retention Period : 365 Days *Infrastructure Audit is always enabled .................................................
Before going more in details for the configuration, I’ll summarize the solution provided by Oracle. In the documentation it’s only rarely described the way it works, but the concept can be found there. The OEM writes the audit to a table inside the sysman schema and if configured well it will be unloaded to a database directory in xml format. This happens daily managed by a database job. I will go into more detail below. The view described in the documentation is called SYSMAN.MGMT$AUDIT_LOG. Underlying you find the tables SYSMAN.MGMT_AUDIT_LOGS_E and SYSMAN.MGMT_OPERATIONS_MASTER. I expect it’s called SYSMAN.MGMT_AUDIT_LOGS in versions before 13. I’m sorry but I don’t have a test environment older 13.2 available. To check the oldest entry:
select min(TIME_STAMP) from SYSMAN.MGMT_AUDIT_LOGS_E;
You can and should keep some days available in OEM, but the other audit records should be removed. To have this done automatically each night you can use the export function of OEM. To prepare the export you need to create a directory and grant permissions to sysman:
create directory audit as '/audit'; grant read,write on directory audit to sysman ;
And dont’t forget to create the directory also on the filesystem:
mkdir /audit
To enable the daily export and keep the last 7 days inside the database run the following command on the OMS server or every other emcli installation you have.
emcli update_audit_settings -directory="AUDIT" -data_retention_period="7" -externalization_switch="ENABLE"
If the default size of 5M per file is to small for you, it’s possible to adjust also this parameter with the option -file_size=”50000000″. The value is in Bytes. Now it’s done. But I won’t wait until tomorrow and check if everything is working. So I was searching for the way it’s done. Until here everything is well described in the documentation. The Job, which exports all audit records is named EM_AUDIT_EXT_SCHED_JOB and independent from the file_size parameter the records are separated per day even if the size is not reached.
exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME=>'EM_AUDIT_EXT_SCHED_JOB',USE_CURRENT_SESSION => FALSE); PL/SQL procedure successfully completed. Elapsed: 00:00:00.02
If you list the directory now you find a lot of files, with the naming schema: _YYYY-MM-DD_xxxxx.xml where xxxxx is an increasing number starting with 00001.
The table is still the same size and the audit records are still in the table.
Did we configure something wrong? … NO! There is a second job managing the cleanup and the partitioning: EM_DAILY_MAINTENANCE.
exec DBMS_SCHEDULER.RUN_JOB(JOB_NAME=>'EM_DAILY_MAINTENANCE',USE_CURRENT_SESSION => FALSE);
After this job execution everything is fine, partitions are deleted and space is freed. At the end I want to give you an example how such an audit record looks like.
<?xml version="1.0" encoding="UTF-8"?> <MSG TIME="2017-12-15 01:32:03.847 +01:00" TIME_NORM="2017-12-15 00:32:03.808 +0:00" ORG_ID="ORACLE" COMP_ID="GRID CONTROL" INST_ID="2" LEVEL="1" HOST_ID="<OMS_HOST>" HOST_ADDR="<OMS_IP>" PID="1234" USER="<USER>" UPSTREAM_COMP="Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E;)"> <TXT><USER> Logged on successfully</TXT> <ATTR NAME="INITIATOR_AUTH_TYPE" VALUE="Repository"/> <ATTR NAME="UPSTAREAM_COMPONENT_TYPE" VALUE="Browser"/> <ATTR NAME="INITIATOR_SESSION_ID" VALUE="3C02A811193BAC0E5968109B20DFABE5"/> <ATTR NAME="INITIATOR_NW_ADDRESS" VALUE="<SOURCE_IP>"/> <ATTR NAME="INITIATOR_HOST_NAME" VALUE="<SOURCE_IP>"/> <ATTR NAME="OPERATION_NAME" VALUE="LOGIN"/> <ATTR NAME="OPERATION_STATUS" VALUE="SUCCESS"/> <ATTR NAME="PRIMARY_OBJECT_NAME" VALUE="<USER>"/> </MSG>
Please don’t forget also the housekeeping for the xml files in the database directory. For example with a cron entry:
22 00 * * 0 find /audit -mtime +365 -type f -name "em_audit_????-??-??_?????.xml" -exec rm {} \; 2>&1 1>/dev/null
Add audit policies
If you want to change the audit policies what is audited, you can run
emcli update_audit_settings -operations_to_enable="XXX" emcli update_audit_settings -operations_to_disable="XXX"
Which policies are existing, can be listed with: emcli show_operations_list This command lists all the 171 audit policies.
Be careful: The policy name can include spaces 😉
Finally I want to describe, how to check the audit data with the browser. Navigate to Setup –> Security –> Audit Data.
daniel8192 