Unified Audit blocking SYS access

I got reported (by a DBA) that access to a database (as / sysdba) was hanging.

First place I looked was the alert.log of the database – bingo, the following message was there:

ORA-1692: unable to extend lobsegment AUDSYS.SYS_LOB0000077797C00014$$ partition SYS_LOB_P2579 by 1024 in tablespace AUDITTS

2018-04-09 11:35:07.110000 -04:00

When I saw AUDSYS.XXXX the first thing that came to my mind was Unified Audit. You see, the purpose of auditing is to track changes that users make in the database and that includes super user and audit events include connecting to the database.

Since SYS was trying to connect an audit record needed to be created, but as the tablespace dedicated to auditing was full it would cause the database to hung. That didn’t happen before Unified Auditing was introduced – now even SYS user’s actions generate audit records.

Benefits of Unified Audit are huge but won’t bore you with the details as there’s plenty of great blog posts and documentation available at hand and simple google search for it will land you there.

The solution. Since I wasn’t able to connect “/ as sysdba” there was no way for me to increase the size of the AUDITTS tablespace which was the real solution. Amen for “prelim” – connecting with SqlPlus with the prelim option allows you not only to gather info of a hung instance, but also to bypass the Unified Audit  filter.

So, I connected using the prelim option and shutdown the instance

sqlplus -prelim / as sysdba
SQL> shutdown abort
SQL>  exit

Now, I still needed to increase the size of the tablespace. If I had started the database normally I would be again in the same place, hanging due to the SYS connection attempt pending to be written to the audit trail. Amen for “upgrade” mode. Starting the database in “upgrade” mode allows you not only to run upgrade procedures, but also you can take advantage that it permits certain operations (and of course bypass the Unified Audit filter).

So I started the database using the “upgrade” mode and resized my tablespace

sqlplus / as sysdba
SQL> startup migrate
SQL> select tablespace_name, file_name, bytes/1024/1024, maxbytes/1024/1024, autoextensible from dba_Data_files
where tablespace_name in ('AUDITTS') order by tablespace_name, file_name;

TABLESPACE_NAME   FILE_NAME                                  BYTES/1024/1024 MAXBYTES/1024/1024 AUT
----------------- ------------------------------------------ --------------- ------------------ ---
AUDITTS           +DATA/DBFAZ/DATAFILE/auditts.336.960128707 10240           0                  NO


SQL> alter database datafile '+DATAC4/DIS1/DATAFILE/auditts.336.960128707' resize 20G;

The rest is history, actions followed were just to shutdown and startup the database normally and provide the solution to the DBA (and of course write this lovely post)

SQL> shutdown immediate
SQL> startup

Now for some lessons learned

• Keep an eye on your space usage, all this hung/downtime could have been prevented if space were added or if audit records were purged on time
• Be smart and surgeon-minded when configuring/enabling auditing, specially with Unified Audit, as you don’t want to audit it all, but also don’t want to miss actions worth auditing. Take the time to plan for what you want to audit and also plan for what you no longer need (meaning have a purge plan in mind as well)

That’s it for this post – thanks for reading

MyOracleSupport – ORA-600 / ORA-7445 analysis tools

MyOracleSupport – ORA-600 / ORA-7445 analysis tools

It used to be a panic-mode -alert when one would get an ORA-600 / ORA-7445 error. This has slowly transitioned to a rather manageable non-panicking level (even ignorable).

As usual, these kinds of errors point us to MyOracleSupport (MOS), so once we land on support.oracle.com, the nice and quick way to start troubleshooting is to type “600.1” on the search box

This will take us to the well know ORA-600/ORA-7445/ORA-700 Error Look-up Tool.

That would be our first stop to try and troubleshoot the error.

I’m not going to spend time on this as the tool has been around for a good while and most DBAs are quite used to this tool to do a first screening of the error and even do a first MOS search.

The purpose of this post is the specific ORA-7445 / ORA-600 troubleshooting tools

The mechanicals are pretty much the same for either error, so for the sake of this post we’ll use the new ORA-7445 link, so go ahead and click on the link to land on the tool’s main page.

You are presented with two options, to troubleshoot a new issue or to review a troubleshoot (saved) report from a previous session (clicking on the help “?” icon will give you an explanation on the options)

Let’s pick the first option, to troubleshoot a new issue and click next (on the top-right of the page)

Now we’re on the file upload step – depending on your choice of files to upload you have three choices: TFA packages, IPS packages or a trace file + alertlog (optional)

More in depth details on trace file collection are explained on Doc. 2165632.1

A TFA package is the recommended approach – to generate the .zip package you run:

$TFA_HOME/bin/tfactl diagcollect -srdc ora7445

An IPS package works nicely too – to generate the .zip package you run

Connect to “adrci” tool, set the proper home and generate the package:

adrci> set homepath diag/rdbms/mydb/MYDB1
adrci> show incident
INCIDENT_ID          PROBLEM_KEY                   CREATE_TIME
-------------------- ----------------------------- ---------------------------------
12345                ORA 7445 [kfkNotify()+25]     2017-03-05 21:00:21.444000 +00:00
...
adrci> ips pack incident 12345 in /tmp

Alternatively, if there are several similar incidents, you can generate the package for a problem instead

adrci> ips pack problem 12 in /tmp

Upload the generated file by clicking on “Choose File” and then click on the “Upload” button.

In this example we are using an IPS package

Once uploaded click next and let the tool do the analyzing – wait for it to end and you’ll land on the “Review Recommendations” step.

Below an example of the expected output

You may get a good lead or even an acceptable solution to your problem there, however, if the analysis returns no acceptable feedback (or nothing is found) there’s a second section that is quite interesting and helpful – have a look at the “Your Feedback” section, more precisely the “Create SR” button:

This is particularly useful for a quick SR creation, as most of the options will be filled out for you – all you need to reconfirm is the product version, the desired support ID to use and the OS version (and of course the severity of the SR)

You might also want to give it a more meaningful title as the one offered could be too generic.

Note there’s also an option to save the troubleshooting session if you ever need to go back to validate it.

This is what the “Create SR” popup looks like

When I had to use this approach, I found that a sev. 2 was promptly attended to and got very good analysts to help.

Thanks for taking the time to read this – hopefully you’ve found it helpful

Let’s get started

So, here we go again…

After a failed first (and only) attempt I pushed myself (let’s say new year’s resolution) to really start blogging.

This will have mainly technical content, and mostly Oracle related however (like this post) anything might appear here.

So without further delay – I’m publishing my first post of (hopefully) many more to come 🙂