Stopped sessions due to SYSAUX tablespace 100% full

Asset ID:	1-72-1937986.1
Update Date:	2018-05-03
Keywords:

Solution Type Problem Resolution Sure

Solution 1937986.1 : Stopped sessions due to SYSAUX tablespace 100% full

Applies to:

Oracle Communications Performance Intelligence Center (PIC) Software - Version 4.1 and later
Information in this document applies to any platform.

Symptoms

All sessions running on the same subsystem are stopped.

Changes

Cause

SYSAUX tablespace of the unique xDR storage (DWS) in the impacted subsystem is full at 100%.

The following error is continuously raising in alert_IXP.log file of the concerned xDR server (DWS):

ORA-1654: unable to extend index SYS.SQLLOG$_PKEY by 64 in tablespace SYSAUX
ORA-1654: unable to extend index SYS.SQLLOG$_PKEY by 4096 in tablespace SYSAUX
Mon Sep 29 04:02:15 2014
ORA-1654: unable to extend index SYS.SQLLOG$_PKEY by 64 in tablespace SYSAUX
ORA-1654: unable to extend index SYS.SQLLOG$_PKEY by 4096 in tablespace SYSAUX
Mon Sep 29 04:02:15 2014
ORA-1654: unable to extend index SYS.SQLLOG$_PKEY by 64 in tablespace SYSAUX
ORA-1654: unable to extend index SYS.SQLLOG$_PKEY by 4096 in tablespace SYSAUX

Tablespaces occupancy can be checked using the "view" appropriate script. As root on the impacted xDR storage server (DWS):

# cd_oracle_utils

# ./ViewTbspaceUsage.sh <connection string>
Tablespace                         Used (Mb)      Max (Mb)     Used %
------------------------------ ------------- ------------- ----------
DATA_CDR                              484663       2254208         22
DATA_CONF                                 44          2048          2
DATA_IND                              993247       2169072         46
DATA_LOG                                  15          2048          1
SYSAUX                                 81832         81920         99
SYSTEM                                  3943         16384         24
UNDO                                    1406         32768          4

Solution

The solution consists on extending the SYSAUX tablespace. Depending on the installed ORACLE release, the following procedures need to be executed.

Oracle 10

Log on to concerned IXP xDR server (DWS) as oracle user.
Verify if there is space left in /opt/oracle/oradata/ partition (you can use df -h), we are going to add 16GB so 32GB free is minimum.
Connect to the ORACLE database:
$ sqlplus / as sysdba
Extend the SYSAUX tablespace using the following commands. Verify that the chosen dbf file names (saux03.dbf and saux04.dbf) are not already used by the system (ls -l /usr/TKLC/oracle/oradata/IXP/saux*)

ALTER TABLESPACE SYSAUX ADD DATAFILE '/usr/TKLC/oracle/oradata/IXP/saux03.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 8G;
ALTER TABLESPACE SYSAUX ADD DATAFILE '/usr/TKLC/oracle/oradata/IXP/saux04.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 8G;
Exit sqlplus with "exit" command and switch to root user.
Verify SYSAUX occupancy percentage using "ViewTbspaceUsage.sh" script.

Oracle 11

Log on to concerned IXP xDR server (DWS) as oracle user.
Verify if there is space left in the DATA diskgroup. We are going to add 16GB so 32GB free is minimum.

$ cd /opt/TKLCixp/prod/db/utils/cmd
$ ./AsmUsage.sh <connection string>
"Total Size" and "Used Size" columns should be checked.

Disk Group       Sector   Block   Allocation
Name               Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
--------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
DATA                512   4,096    1,048,576 MOUNTED     EXTERN      10,282,818      7,325,485     71.24
If "AsmUsage" script is not available, the following commands can be used:

$ sqlplus / as sysdba
SQL> select name, total_mb,free_mb from v$asm_diskgroup;
Result looks like below:

NAME                              TOTAL_MB    FREE_MB
------------------------------ ------------- ------------
DATA                             100418145   689485645
Connect to the ORACLE database:
sqlplus / as sysdba
Extend the SYSAUX tablespace using the following commands:

ALTER TABLESPACE SYSAUX ADD DATAFILE '+DATA' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 8G;
ALTER TABLESPACE SYSAUX ADD DATAFILE '+DATA' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 8G;
Exit sqlplus with "exit" command and switch to root user.
Verify SYSAUX occupancy percentage using "ViewTbspaceUsage.sh" script.

Attachments

This solution has no attachment