Send Oracle Audit to rsyslog

December 16, 2016 2 Comments

In our database there is turned on auditing on some operations and audit records go to OS.

SYS> show parameter audit_file_dest

NAME                TYPE        VALUE
------------------ ----------- ------------------------------
audit_file_dest  string       /u01_log/audit/orcl

SYS > show parameter audit_trail

NAME        TYPE         VALUE
------------- ----------- -----------
audit_trail string        OS

Our security administrators are using SIEM to monitor suspicious activities and they want database to send audit records to this third party tool.

I thought that I could somehow indicate directory “/u01_log/audit/orcl” from where *.aud files would be uploaded to SIEM, but I was wrong. Some tools may be able to use these *.aud files but not SIEM and let’s configure our database to be able to send audit records to it.

1. Connect to a database instance as sysdba user

SQL> connect / as sysdba

2. Set audit trail to OS

SQL> alter system set audit_trail=OS;

3. Enable auditing for system users if you need to audit activities of sys user(optional)

SQL> alter system set audit_sys_operations=TRUE;

4. Set rsyslog facility and severity(needs database restart)

SQL> alter system set audit_syslog_level=local5.info scope=spfile sid='*';

5.  Restart database

SQL> shutdown immediate;
SQL> startup;

6. Edit rsyslog.conf file

#Saving oracle database audit records
local5.info          /u01_log/audit/RSYSLOG/dbaudit.log
#Send oracle database audit trail to remote rsyslog server
local5.info          @192.168.0.15

7. Restart rsyslog service

# service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

8. It is better to limit the size for audit log, or it may fill the space:

# vi /etc/logrotate.d/oracle.audit

#Created by MariK

/u01_log/audit/RSYSLOG/dbaudit.log {
 rotate 3
 compress
 missingok
 notifempty
 size 40G
 postrotate
 service rsyslog restart
 endscript
}

To check the syntax run :

# logrotate /etc/logrotate.d/oracle.audit

It will say if you have an error. If syntax is ok then output is nothing.

Filed under 12c, audit, DB, RHEL6 Tagged with , , , ,

Configure resource manager to kill sessions automatically after maximum idle time is passed

December 15, 2016 Leave a comment

Problem:

Our applications are opening too many connections and moreover are not closing them at all 🙂 .  Because of this to many sessions stay idle and after IDLE_TIME is passed they become SNIPED.
As you know SNIPED session still holds session counter and it is completely cleaned out just after SNIPED session tries to execute something(it of course errors out). But if SNIPED session never tries to execute anything then the session stays forever in database.  And after a while database throws ORA-00018 maximum number of sessions exceeded.

My old solution: 

Created script file /u01/app/oracle/dba_scripts/kill_sniped.sh, with content:

#!/bin/ksh

#Written by MK

cd /u01/app/oracle/dba_scripts
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_SID=orcl1
export ORACLE_USER=oracle
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF

SET SERVEROUTPUT ON SIZE 1000000;
CALL DBMS_JAVA.SET_OUTPUT(1000000);

DECLARE
snum NUMBER;
BEGIN
FOR i IN (SELECT ‘alter system kill session ”’||a.SID||’,’||a.serial#||’,@’||inst_id||”’ immediate’ killSniped FROM gv\$session a
WHERE (a.status=’SNIPED’ or a.status=’KILLED’)
and a.username is not null
)
LOOP
begin
execute immediate i.killSniped;
exception when others then null;
end;
END LOOP;
END;
/
EOF

You will easily guess what does it do. It finds sessions with status SNIPED and KILLED and executes alter system kill session script for them.

Created crontab entry:

$ crontab -l
*/10 * * * * /u01/app/oracle/dba_scripts/kill_sniped.sh > /u01/app/oracle/dba_scripts/logs/kill_sniped.log 2>&1

Script was working fine about one year, without any problem 🙂 but yesterday my script was not able to handle all of these sessions and it was killing slower than SNIPED sessions were appearing in our database so database raised ORA-00018 error.

New and better solution:

Created consumer group , set plan directive with MAX_IDLE_TIME 900sec for this group and moved problematic app user in this group.

After MAX_IDLE_TIME is passed user session is automatically killed by resource manager and it is the quickest.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => ‘RESTRICTIVE_PLAN’, COMMENT => ”);

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘RSGROUP’, COMMENT =>”);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘RESTRICTIVE_PLAN’
, GROUP_OR_SUBPLAN => ‘RSGROUP’
, COMMENT => ”
, MAX_IDLE_TIME => 900);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME => ‘RSAPP’
, CONSUMER_GROUP => ‘RSGROUP’
, GRANT_OPTION => FALSE);

DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP( ‘RSAPP’, ‘RSGROUP’);

DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER( ‘RSAPP’, ‘RSGROUP’);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=’RESTRICTIVE_PLAN’;

Note: RSAPP user had IDLE_TIME 15min in its profile, that is why I have set MAX_IDLE_TIME to 900sec(15min). Be careful for this decision , you should set this value appropriate to profile IDLE_TIME value. Or first discuss it with developers, they may not want you to kill their app session after 15min.. but after 20min.

To check how many sessions were killed by resource manager check:

SELECT IDLE_SESSIONS_KILLED
FROM V$RSRC_CONSUMER_GROUP
WHERE NAME=’RSGROUP’;

Hope post was useful. 🙂





				


				

					

						Filed under 12c, Real World Scenarios, RESOURCE_MANAGER
						Tagged with , , , , 					

				


			


		
			


				


					
CHANGE SYSMAN PASSWORD ON EM13C


					

						

							December 6, 2016
																						4 Comments
																				

					


					
You will need sys password to reset sysman password.


If you don’t remember sys password you should already know how to change it from repository server… If you still thinking , ok then let’s write this step also:


    

  1. Connect to the database(repository) server via ssh.
    2. 

  2. Describe ORACLE_SID and ORACLE_HOME variables.
    3. 

  3. Connect to the database without password and with sysdba privilege:
    

    sqlplys / as sysdba
    

    4. 

  4. Change sys user password:
    

    alter user sys identified by <sys password>;
    

    5. 



…

1. Stop all OMS processes


cd <oms home>/bin

emctl stop oms


2. Change the SYSMAN password:


cd <oms home>/bin

emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <sys password> -new_pwd <new sysman password>


3. Stop the Admin Server on the primary OMS and restart OMS:


cd <oms home>/bin

emctl stop oms -all

emctl start oms


4. Check that oms is up and running:


cd <oms home>/bin

emctl status oms -details


That’s it.




				


				

					

						Filed under 13C, Grid&Cloud Control
						Tagged with , , , ,