How Do I Handle an Upgrade Failure Due to Insufficient Tablespace Caused by Excessive Oracle Audit Logs

Question

During the eSight upgrade, if too many audit logs are stored in the database, an error is reported during the initial database backup, causing an upgrade failure. The detailed error information is as follows:

ORA-39171: Job is experiencing a resumable wait.
ORA001691: unable to extend lob segment

Answer

This problem occurs when the Oracle audit log function is enabled (audit_trail is set to DB or OS). Too many audit logs cause insufficient tablespace. In this case, disable the Oracle audit log function (set audit_trail to FALSE). After the upgrade is complete, enable the audit log function (restore audit_trail to the original value).

In the following example, the value of audit_trail is changed from OS to FALSE:

1. Cancel the upgrade and delete the backup directory.

   The default backup directory is /opt/eSightback.

2. Disable the Oracle audit log function.
   1. Log in to the Oracle server as the root user.
      1. Run the su - oracle command to switch to the oralce user.
      2. Run the sqlplus / as sysdba command to access the Oracle UI.
   2. Ensure that the Oracle service is started normally. If the Oracle service is not started, perform the following steps to start it:
      • In a two-node cluster, run the hares -online Oracle -sys $(hostname) command as the root user to bring Oracle resource online.
      • In a single-node system, run the startup command to start the Oracle service.
   3. Record the original value of audit_trail for future restoration.

      SQL> show parameter audit_trail
       
      NAME                                 TYPE
      ------------------------------------ ---------------------------------
      VALUE
      ------------------------------
      audit_trail                          string
      OS
      SQL>

   4. Change the value of audit_trail to FALSE.

      The value of audit_trail does not take effect immediately after being changed. You need to restart the Oracle service for the modification to take effect.

      SQL> alter system set audit_trail=FALSE scope=spfile;
       
      System altered.
       
      SQL> show parameter audit_trail
       
      NAME                                 TYPE
      ------------------------------------ ---------------------------------
      VALUE
      ------------------------------
      audit_trail                          string
      OS
      SQL>

   5. Restart the Oracle service.
      • In a two-node cluster, run the hares -offline Oracle -sys $(hostname) command as the root user to bring the Oracle resource offline, and then run the hares -online Oracle -sys $(hostname) command to bring the Oracle resource online.
      • In a single-node system, run the shutdown immediate command to stop the Oracle service, and then run the startup command to start the Oracle service.
   6. Verify that the value of audit_trail is changed to FALSE.

      SQL> show parameter audit_trail
       
      NAME                                 TYPE
      ------------------------------------ ---------------------------------
      VALUE
      ------------------------------
      audit_trail                          string
      FALSE
      SQL>

3. Upgrade eSight again until the upgrade succeeds.
4. Enable the Oracle audit log function again.
   1. Log in to the Oracle server as the root user.
      1. Run the su - oracle command to switch to the oralce user.
      2. Run the sqlplus / as sysdba command to access the Oracle UI.
   2. Ensure that the Oracle service is started normally. If the Oracle service is not started, perform the following steps to start it:
      • In a two-node cluster, run the hares -online Oracle -sys $(hostname) command as the root user to bring Oracle resource online.
      • In a single-node system, run the startup command to start the Oracle service.
   3. Run the following commands to restore the value of audit_trail to the original value:

      SQL> alter system set audit_trail=OS scope=spfile;
       
      System altered.

   4. Restart the Oracle service.
      • In a two-node cluster, run the hares -offline Oracle -sys $(hostname) command as the root user to bring the Oracle resource offline, and then run the hares -online Oracle -sys $(hostname) command to bring the Oracle resource online.
      • In a single-node system, run the shutdown immediate command to stop the Oracle service, and then run the startup command to start the Oracle service.
   5. Verify that the value of audit_trail is changed to OS.

      SQL> show parameter audit_trail
       
      NAME                                 TYPE
      ------------------------------------ ---------------------------------
      VALUE
      ------------------------------
      audit_trail                          string
      OS
      SQL>