Category Archives: Bug

Memory Leak in Network Checksum with new SHA-2 Functions

I’ve just stumbled over an issue with the new checksum algorithm introduced with Oracle 12c. It seams that in certain situation the new SHA-2 function cause a memory leak. A search on My Oracle Support revealed that there is a Bug on AIX. See Bug 19451972 MEMEORY LEAKS WITH SHA512, SHA384, SHA256 ENTRIES IN SQLNET.CRYPTO_CHECKSUM and the corresponding Note 1919000.1 SQLPlus 12c Memory usage Grows (Leaks) While Running Long Query.

Test Case

Nevertheless I have similar issues on a Exadata Machine and my Oracle VM. To verify my issue I’ve used a simple test case, where I start a SQL*Plus script which does:

  1. connect as SCOTT
  2. query some views eg. v$session_connect_info
  3. wait a few seconds
  4. query some views eg. v$session_connect_info
  5. start over with step 1

Since SQL*Plus does not support any loop I just use cat to generate a script with a bunch of connect and SELECT. For this I used the following Template (connect_scott_template.sql):

CONNECT scott/tiger@TDB12A
ALTER SESSION SET nls_date_format='DD.MM.YYYY HH24:MI:SS';
SELECT sysdate FROM dual;
SELECT sid, osuser, authentication_type, network_service_banner
FROM v$session_connect_info
WHERE sid=(SELECT sys_context('userenv','sid') FROM dual);
EXEC DBMS_LOCK.SLEEP(10);

Based on this template I’ve created my SQL script with a for loop.

for i in {1..720}; do cat connect_scott_template.sql >>connect_scott.sql ; done

If the script runs for a couple of minutes / hour you will see that RSS (real memory size / resident set size) does increase when network integrity check is enabled with SHA512. It remains on the same level for the same test without network integrity check. Below you see the output of my bash session history (with minor optimization for the web 😉 ):

cd /u00/app/oracle/admin/TDB12A/adhoc/nocksum
export TNS_ADMIN=$PWD

nohup sqlplus /nolog @connect_scott.sql &

PID=17185

while [[ $(ps $PID|wc -l) > 1 ]]
do
echo $(date "+%Y.%m.%d %H:%M:%S $(ps u $PID|tail -1)") >>connect_scott_nocksum_`date '+%Y%m%d'`.log
sleep 30
done

I’ve started sqlplus and the script with nohup. To collect the rss information I’ve just created a while loop and pipe the output of ps to a log file. For the test with checksum typ SHA512 used an alternative TNS_ADMIN directory with a different sqlnet.ora. My Test did run for about two hours. I’ve but the collected data in an Excel sheet to create the following chart. You see, that both SQL*Plus process require more real memory over time. Nevertheless the required memory for SQL*Plus with SHA512 is definitely higher.

MemoryLeak

Files and References

Below you find the scripts mentioned above as well some MOS references:

Conclusion

It seems that this Bug is a bit more generic than expected. Since the new SHA function would anyway just work for pure 12c environment, it is acceptable to use the old SHA1 Hash until this Bug is fixed.

AVCLI doubles audit trails, bug or feature?

I’ve start using the AV command line interface to administer AVDF. I use the tool fairly often to start, stop and monitor the audit trails. But recently I ran in a small issue after a typo. I just want to start the audit trail on the ADUMP directory of a database.

AVCLI> LIST TRAIL FOR SECURED TARGET TDB11A;
----------------------------------------------------------------------------------------------------------------------------------
| AUDIT_TRAIL_TYPE | HOST   | LOCATION                            | STATUS  | REQUEST_STATUS | ERROR_MESSAGE                     |
==================================================================================================================================
| DIRECTORY        | urania | /u00/app/oracle/admin/TDB11A/adump  | STOPPED |                |                                   |
| TABLE            | urania | SYS.AUD$                            | STOPPED |                |                                   |
| TRANSACTION LOG  | urania |                                     | STOPPED |                |                                   |
----------------------------------------------------------------------------------------------------------------------------------

AVCLI> START COLLECTION FOR SECURED TARGET TDB11A USING HOST urania FROM DIRECTORY '/u00/app/oracle/admin/TDB11A/adump/';

Request submitted successfully.

After submitting the start command I’ve checked the status of the audit trails. As expected the audit trail has been started and is now waiting in IDLE state on audit files. But wait there are two audit trails on the same directory?! One of them does have a backslash.

AVCLI> LIST TRAIL FOR SECURED TARGET TDB11A;
--------------------------------------------------------------------------------------------------------------
| AUDIT_TRAIL_TYPE | HOST   | LOCATION                            | STATUS  | REQUEST_STATUS | ERROR_MESSAGE |
==============================================================================================================
| DIRECTORY        | urania | /u00/app/oracle/admin/TDB11A/adump  | STOPPED |                |               |
| DIRECTORY        | urania | /u00/app/oracle/admin/TDB11A/adump/ | IDLE    |                |               |
| TABLE            | urania | SYS.AUD$                            | STOPPED |                |               |
| TRANSACTION LOG  | urania |                                     | STOPPED |                |               |
--------------------------------------------------------------------------------------------------------------

It is also possible to start both of them.

AVCLI> START COLLECTION FOR SECURED TARGET TDB11A USING HOST urania FROM DIRECTORY '/u00/app/oracle/admin/TDB11A/adump';

Request submitted successfully.

AVCLI> LIST TRAIL FOR SECURED TARGET TDB11A;
---------------------------------------------------------------------------------------------------------------
| AUDIT_TRAIL_TYPE | HOST   | LOCATION                            | STATUS  | REQUEST_STATUS  | ERROR_MESSAGE |
===============================================================================================================
| DIRECTORY        | urania | /u00/app/oracle/admin/TDB11A/adump  | STOPPED | START REQUESTED |               |
| DIRECTORY        | urania | /u00/app/oracle/admin/TDB11A/adump/ | IDLE    |                 |               |
| TABLE            | urania | SYS.AUD$                            | STOPPED |                 |               |
| TRANSACTION LOG  | urania |                                     | STOPPED |                 |               |
---------------------------------------------------------------------------------------------------------------

4 ROW(s) selected.

The command completed successfully.

Since it does not make sense to have two audit trails on the same directory I tried to drop the second audit trail.

AVCLI> STOP COLLECTION FOR SECURED TARGET TDB11A USING HOST urania FROM DIRECTORY '/u00/app/oracle/admin/TDB11A/adump/';

Request submitted successfully.

AVCLI> DROP TRAIL FOR SECURED TARGET TDB11A USING HOST urania FROM DIRECTORY '/u00/app/oracle/admin/TDB11A/adump/';
ERROR:
OAV-3025: Audit DATA has been gathered FOR trail /u00/app/oracle/admin/TDB11A/adump/ OF TYPE DIRECTORY FOR secured target TDB11A. cannot DROP trail.
  • Why the heck I have a second audit trail?
  • Why it is not possible to remove it?

The answer to the second question is simple. Since both audit trails point to the same directory they also point somehow to the same audit data. In the current release 12.1.1 of Oracle Audit Vault and Database Firewall it is not possible to remove an audit trail if there were already collected audit data. Ok but why do I have a second audit trail to the same directory? The reason is not obvious but simply. Oracle did not implement a command to create new audit trails. Instead, they use the start command. If you execute

START COLLECTION FOR SECURED TARGET

and the requested audit trail does not yet exist, it will be created. Unfortunately this behavior is not mentioned in the AVCLI documentation. I could test this successfully for other trail types. In the case of directories, Oracle checks whether the directory exists and is accessible, but they do not normalize the path name. Which is why I end up with two similar audit trails.

Solution

For now there are only two possibilities. We either have to live with the second audit trail or we could try to manually drop the audit data related to this audit trail. But dropping means losing audit data, which is in most cases not feasible for production systems. I’ll provide a possible solution to drop trail data later on this blog. Oracle itself addressed this issue in a Bug 17544636 ONE CAN EASILY DUPLICATE AUDIT TRAILS WHEN USING AVCLI.

Conclusion

It is a nice feature to easily create audit trails. But I except to better workaround simple user errors / typos 🙂

References

Some links related to this post.

OAV-46599 when trying to add new secure target on AVDV 12.1.1.1

It is the second time that I run into this problem. Therefore, it is time to write a quick note before I struggle a third time. At some point adding a new secure targets no longer works and breaks with an OAV-46599.

OAV 46599

Initially I was a bit confused about the error. Because there haven’t been any changes on the system since the last secure target has been added. But reading the whole error message above gives the correct indication of the root cause. It’s nothing else than an ORA-28001 the password has expired. Lets see which user has an expired password.

SQL> ALTER SESSION SET nls_date_format='DD.MM.YYYY HH24:MI:SS';

SESSION altered.

SQL> SET linesize 160 pagesize 200
SQL> SELECT username,account_status,expiry_date FROM dba_users WHERE account_status='EXPIRED';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ -------------------------------- -------------------
ANONYMOUS                      EXPIRED                          17.09.2011 10:21:08
AVREPORTUSER                   EXPIRED                          17.07.2013 21:25:55

It looks like the account AVREPORTUSER is expired. Because I do not know the password yet know whether it was stored somewhere, I’ll just reset the old password. For this I need both password hash’s.

SQL> col name FOR a15
SQL> col password FOR a17
SQL> col spare4 FOR a65
SQL> SELECT name,password,spare4 FROM USER$ WHERE name='AVREPORTUSER';

NAME            PASSWORD          SPARE4
--------------- ----------------- -----------------------------------------------------------------
AVREPORTUSER    F315BBCEBB3F78E7  S:14155D035FEBAB05790EAB47CCC4ACDBD8B728C373EECDABE6EB5FAA9D03

With alter user identified by values I’m able to specify both the 10g and the 11g password hash to reset the password to the same value.

 ALTER USER AVREPORTUSER IDENTIFIED BY VALUES 'S:14155D035FEBAB05790EAB47CCC4ACDBD8B728C373EECDABE6EB5FAA9D03;F315BBCEBB3F78E7';

As you can see in DBA_USERS the account has now status open again. Adding secure targets does work again.

SQL> SELECT username,account_status,expiry_date,password_versions FROM dba_users WHERE username='AVREPORTUSER';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE         PASSWORD
------------------------------ -------------------------------- ------------------- --------
AVREPORTUSER                   OPEN                             11.02.2014 06:39:02 10G 11G

But how has this account become expired? The reason is quite obviously. All AV user do have the Oracle DEFAULT profile which has a limited password life time of 180 days. Therefore, the accounts expire after 180 days. And yes my AVDF test system was set up about 180 days ago. 🙂

SQL> SELECT username,profile FROM dba_users WHERE username='AVREPORTUSER';

USERNAME                       PROFILE
------------------------------ ------------------------------
AVREPORTUSER                   DEFAULT

SQL> SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

An Oracle Bug has been opened for this issue. BUG 17078860 Relax The profile settings in the AV server’s database