Overview

This guide provides instructions on capacity management necessary for operating a Tibero Single DB.

Method

5. Capacity Management

5.1. Checking File System Capacity

5.1.1. Checking Tibero Engine File System Usage

If the file system where Tibero is installed, or where Datafiles and Archive Log Files are stored, reaches 100% full, the database may hang because no more data can be written, or errors may occur due to data not being recorded properly.

Therefore, administrators need to periodically check the file system capacity on the OS.

# df –k
Filesystem                               Size             Used              Avail          Use%          Mounted on 
/dev/dsk/d0t0d0s7       12540304      4825027       7589874            39%           /tibero


→ As confirmed by the OS command ‘df –k’, the file system where Tibero is installed is 39% used, indicating approximately 61% free space available.

5.1.2. Checking Archive Location / Capacity

Archive Log Files are offline copies of the Online Redo Log Files and are useful for recovery.
In systems operating in Archive Mode, periodic checks of the file system where Archive Logs are stored are necessary.

SQL> archive log list

NAME                                      VALUE
-----------------------------------   -----------------------------------------------
Database log mode       Archive Mode 
Archive destination        /tibero/tbdata/arch/
Oldest online log sequence     11 
Next log sequence to archive   13        
Current log sequence        13


→ Using the ‘archive log list’ command in tbsql, you can check whether the system is running in Archive Mode and the location where Archive Log Files are stored.


# df -k
Filesystem                            Kbytes               Used               Available             Use%           Mounted on
/dev/rdsk/d9t0d0s3      51643047    14471941    36654676            29%           /tibero/tbdata/arch 

→ The file system “/tibero/tbdata/arch,” where the archive log files are currently stored, is about 29% used.


# cd /tibero/tbdata 
# du -sk arch
1197877 arch  →  approximately 1.18 Gbytes (in Kbytes)


→ After moving to the tbdata file system and checking the size of the ‘arch’ directory, it was found that about 1.18 Gbytes of Archive Log Files are stored.

5.1.3. Checking Tibero Datafile Location / Capacity

SQL> select tablespace_name, file_name from dba_data_files; 


TABLESPACE_NAME     FILE_NAME
----------------------------  --------------------------------------------------
SYSTEM                /tibero/tbdata/data/system001.dtf
UNDO                  /tibero/tbdata/data/undo001.dtf
USR                   /tibero/tbdata/data/usr001.dtf
SYSSUB               /tibero/tbdata/data/syssub001.dtf
TEST01               /tibero/tbdata/data_1/tibero/test01001.dtf
TEST02               /tibero/tbdata/data_1/tibero/test02001.dtf


→ This query shows the tablespace name, the file name belonging to the tablespace, and the file size.
   ‘BYTES/1024/1024’ converts the file size to megabytes for display.


# df –k
Filesystem                     1K-blocks               Used         Available       Use%       Mounted on 
/dev/dsk/d0t1d0s0      35007716     23638505        11019134         69%       /tibero/tbdata/data
/dev/dsk/d0t0d0s7      12540304     11560337      854564   94%       /tibero/tbdata/data_1

→ Checking the file systems under /tibero where the datafiles are stored shows usage of 69% and 94%, respectively. 
   Since /tibero/tbdata/data_1 has low available space, it is recommended to move datafiles to another file system or increase the file system capacity.

5.2. Actions When File System Is Full

If the Tibero engine or Archive log file system becomes 100% full, a DB hang may occur.

$ df –k
Filesystem                         Kbytes               Used         Available         Use%       Mounted on
/dev/dsk/c0t0d0s7      12540304      12540304             0       100%       /tibero
/dev/rdsk/c1t0d0s3     51643047      51643047             0       100%       /tbdata/tbdata/data_1

5.2.1. Check/Delete Unnecessary Files in Tibero Engine File System

There may be unnecessary log files, backup files, or program files in the Tibero engine file system.
After checking, delete or move them to another file system.

5.2.2. Delete Archive Logs

If the disk is full and writing archive log files is impossible, Tibero operations will stop,
and a hang will occur making connections and SQL execution impossible.
Since archive log files are generated continuously and periodically, delete or move unnecessary files.

$ cd /tibero/tbdata/arch 
$ ls –l
-rw------- 1 tibero dba          39936       Mar 27 04:38 log-t0-r0-s10.arc
-rw------- 1 tibero dba          42496       Mar 27 04:38 log-t0-r0-s11.arc
-rw------- 1 tibero dba          43520       Mar 27 05:19 log-t0-r0-s12.arc
-rw------- 1 tibero dba    17548288       Mar 21 23:29 log-t0-r0-s4.arc
-rw------- 1 tibero dba            3584       Mar 21 23:29 log-t0-r0-s5.arc
-rw------- 1 tibero dba        302080       Mar 21 23:41 log-t0-r0-s6.arc
-rw------- 1 tibero dba      8329728       Mar 22 09:49 log-t0-r0-s7.arc
-rw------- 1 tibero dba    10452992       Mar 26 22:32 log-t0-r0-s8.arc
-rw------- 1 tibero dba        246784       Mar 26 23:08 log-t0-r0-s9.arc

$ find . -name "*.arc" -mtime +1 -exec rm -rf {} ;
→ This command finds files with the extension ‘arc’ in the current directory whose modification (creation) date is older than one day 
    (-mtime +1) and deletes them using rm.
    Before deleting, confirm the date of the last backup and delete files older than that backup date.


SQL> select * from v$backup;

FILE#      STATUS                   CHANGE# TIME      
--------  ------------    ---------------------------------
0 NOT     ACTIVE     87773 2023/03/27 15:36:19 
1 NOT   ACTIVE       87775 2023/03/27 15:36:19 
2 NOT     ACTIVE     87777 2023/03/27 15:36:19 
3 NOT     ACTIVE     87779 2023/03/27 15:36:19 
4 NOT     ACTIVE     87781 2023/03/27 15:36:19 
5 NOT     ACTIVE     87783 2023/03/27 15:36:19

6 rows selected.

→ Since the backup date is March 27, 2023, archive log files older than that can be deleted.
→ If Tibero operation stopped due to archive destination disk being full, deleting archive log files will allow the service to resume normally.