How to extend the size of a tablespace in Red Hat Satellite database?

Solution Verified - Updated -

Environment

  • Red Hat Satellite 5.5 or earlier with embedded Oracle database
  • For Red Hat Satellite with external database, contact your DBA.

Issue

  • How to extend the size of a tablespace in Red Hat Satellite database?
  • One or more of table space on the satellite becomes full, needs to be extended
  • Emails notifications or messages in the satellite's logs indicating tablespace full
  • Most common tablespace to become full is DATA_TBS
  • Oracle database seems to be continually growing in size how to extend the tablespace ?
  • satellite-sync fails with
'ORA-01654: unable to extend index RHNSAT.RHN_PACKAGE_FILE_CID_PID_IDX by 1024 in tablespace

-or

'ORA-01654: unable to extend index RHNSAT.RHN_PKG_CL_PID_N_TXT_TIME_UQ by 8192 in tablespace DATA_TBS

Resolution

Extending a specific tablespace can be accomplished via the following (all commands are executed as the root user).

  1. Before extending the tablespace, ensure there is a current working backup of the database. Also ensure that there is sufficient space in the filesystem for the extension(s) - see Note 1 below.

  2. Change to the Oracle user and extend the database using the db-control command.

    # su - oracle
$ db-control extend <tablespace name>
$ exit

    • NOTE 1: On each successful execution of the db-control extend command, the tablespace extends by 512 MiB, so increasing the tablespace to a particular size greater than that requires running it multiple times (e.g., to extend the tablespace by 1 GiB, the command would need to be executed twice). The database datafiles are located under the /rhnsat/data directory so ensure that you have sufficient space in the filesystem for the extension(s).

    • NOTE 2: The temporary tablespace - TEMP_TBS - cannot be extended via db-control in Satellite 5.2.x and older. To extend TEMP_TBS in Satellite v 5.2 and earlier refer to the following article: How to extend TEMP_TBS on Red Hat Satellite server?

Root Cause

  • Data tablespaces gradually fill up as part of satellite usage. The longer the history / higher the usage of the satellite, the higher the usage of the tablespace.

  • UNDO_TBS is used as a ring-buffer and the use is expected to increase. The space is automatically recycled by the database. It is possible to lower the value for UNDO_RETENTION which would trigger the 'cleaning' of the ringbuffer more often, however consult Red Hat support before tuning / reconfiguring the database on an embedded-database Satellite.

Diagnostics

  • To see which tablespace is full, run:

    # su - oracle
# db-control report

  • In Satellite versions 5.3.x and below, the report output does not include the temporary tablespace, i.e., TEMP_TBS. Running the following query in the database via sqlplus can get that information, but first, figure out the username, password, & dbinstance. (Assuming default on embedded-database Satellites, that would be "rhnsat" for all three -- replace those in the following sqlplus command if necessary.)

    # sqlplus rhnsat/rhnsat@rhnsat <<\EOF        
SELECT   A.tablespace_name tablespace, D.mb_total, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
    (
    SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
    FROM     v$tablespace B, v$tempfile C
    WHERE    B.ts#= C.ts#
    GROUP BY B.name, C.block_size
    ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
EXIT
EOF

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.

Comments