Most Database Administrators are used to the occasional crisis—it comes with the job. The level of stress involved can vary greatly depending on the frequency of the issue, how well you remember the fix, and/or how quickly can you find a resolution using a Google search. Hopefully, this wasn’t too hard to find on Google.
Below, I provide steps to follow for fixing when the audit tablespace is completely full, designed to lower your stress and save the day!
The environment involved in this scenario is a more complicated configuration as it includes Oracle Real Application Clusters (RAC), a container with multiple pluggable databases, and unified auditing. If you can resolve the audit tablespace being 100% full given a more complex environment, you should have no problem with simpler configurations.
One of the first questions which may come to mind is—how do you know if your audit tablespace is 100% full? Hopefully, you have Oracle Enterprise Manager setup with email notifications set for appropriate warning and critical settings. This will give you a fighting chance to fix the issue before it’s too late. Of course, space issues never seem to happen at a great time, receiving an alert in middle of the night is typical.
If you don’t have OEM or some other equivalent tool or script setup to provide alert notifications, your first sign may be users complaining about system access or the application not responding. One of the first places to check for all potential database issues is the alert.log. If you have audit space issues, you will see something like the below…
ORA-1688: unable to extend table AUDSYS.AUD$UNIFIED partition SYS_P12345 by 128 in tablespace AUDIT_DATA
If that’s the case, just follow the steps provided below…
1. Connect into sqlplus as sysdba for the container database
2. Shut down the pluggable database containing the full audit tablespace
3. Connect to the pluggable database
4. Startup the pluggable database
5. If the tablespace is set to smallfile (32 Gb datafile size limit), add a datafile. For a bigfile tablespace, run a query below to find the file name and then resize to a larger file size (200 Gb in this example)
6. If you previously created a service such as Transparent Application Failover (TAF), please be sure to start this to avoid connection issues
After six simple steps or so, you have restored peace and order back into your production environment. Be sure to save these steps somewhere because you will likely need them at some point again in your career… speaking from experience…
AEM can help your organization implement technologies such as Unified Auditing, Container/Pluggable Databases, and Oracle RAC. Please reach out to learn more about our service offerings.
