A tablespace uses two different structures to store Tibero data.

Logical Structure of Tablespaces

The following figure shows the logical structure of a tablespace in a database:

[Figure 3.1] Logical Structure of a Tablespace

A tablespace is composed of segments, extents, and blocks, as shown in [Figure 3.1].

Physical Structure of Tablespaces

The following figure shows the physical structure of a tablespace in a database:

[Figure 3.2] Physical Structure of a Tablespace

Physically, a tablespace consists of many data files as shown in [Figure 3.2]. Tibero stores data using the control file and the log file in addition to the data files.

Storing two frequently used tablespaces, such as a table and its index, on physically separate disks is recommended.. If two tablespaces are saved on the same disk, they cannot be accessed simultaneously as the disk's head is fixed on the currently accessed tablespace.

Therefore, saving frequently used tablespaces on separate disks improves a database's performance by allowing simultaneous access to both tablespaces.

Depending on its creation type, a tablespace can be classified as a system tablespace or a non-system tablespace.

The system tablespace is the tablespace that is automatically created with the database, and a non-system tablespace is any tablespace that was created by a user.

This section describes how to create and delete non-system tablespaces.

Creating Tablespaces

To create a tablespace, use the statement CREATE TABLESPACE. The tablespace name, data file, data size, and extent size can be specified.

The following example shows how to create a new tablespace named my_space, which is contained in the data file my_file.dbf.

CREATE TABLESPACE my_space
    DATAFILE '/usr/tibero/dtf/my_file.dtf' SIZE 50M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

The data file my_file.dbf is created when the SQL statement above is executed. If the name is already in use, an error will occur. The size of my_file.dbf is specified as 50 MB, which also specifies the total size of the tablespace as 50 MB.

Tibero keeps all extents within a tablespace in a fixed size. In the example above, one extent is 256 KB. If a data block is 4 KB, an extent will contain 64 data blocks.

Also, a tablespace can be composed of two or more data files.

An example of more than one data file is shown below:

CREATE TABLESPACE my_space2
    DATAFILE '/usr/tibero/dtf/my_file21.dtf' SIZE 20M,   ... (1) ...
             '/usr/tibero/dtf/my_file22.dtf' SIZE 30M    ... (2) ...
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;            ... (3) ...

(1) Specifies a data file named my_file21.dbf with a size of 20 MB.

(2) Specifies a data file named my_file22.dbf with a size of 30 MB. The total size of the tablespace my_space2 is 50 MB.

(3) Specifies the size of an extent as 64 KB.

The number of data files included in a tablespace differs according to the environment of the database and the system. If a large amount of data is to be stored in a tablespace, the tablespace must be created with many data files.

However, the number of data files that can be processed at the same time is dependent on the OS. Consider this fact when specifying the number of files.

The size of data files is dependent on the database size. To prepare for the case where more space is needed than was specified when creating a tablespace, enable the size to be increased automatically.

The following example shows how to enable the data file size to increase by 1 MB each time it is needed using the AUTOEXTEND statement:

CREATE TABLESPACE my_space
    DATAFILE '/usr/tibero/dtf/my_file.dtf' SIZE 50M
    AUTOEXTEND ON NEXT 1M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

Tibero stores data block assignment information in bitmap form within tablespaces. Thus, the maximum number of extents within a tablespace is less than the value of (tablespace size) / (extent size).

Deleting Tablespaces

To delete a tablespace, use the statement DROP TABLESPACE. If a tablespace is deleted, all its schema objects are also deleted. Take care when deleting a tablespace.

Deleting a tablespace is shown below:

DROP TABLESPACE my_space; 

When this SQL statement is executed, the specified tablespace will be deleted, but its data files will remain. To delete the data files, add the INCLUDING clause to the DROP TABLESPACE statement.

DROP TABLESPACE my_space INCLUDING CONTENTS AND DATAFILES;

If more storage space is needed for a tablespace, either enable data files to automatically expand when necessary, or add a new data file to the tablespace by appending the ADD DATAFILE clause to the ALTER DATABASE statement.

The following example adds a new data file called my_file02.dbf to a tablespace called my_space:

ALTER TABLESPACE my_space ADD DATAFILE 'my_file02.dtf' SIZE 20M; 

If an absolute path is not specified when adding a data file as shown above, the data file is created in the default directory. More than one data file can be created in this process, and the size of each data file can be specified to automatically extend itself.

The size of data files can be changed using the ALTER DATABASE statement. The size can be either increased or decreased with ALTER DATABASE. However, when reducing the size, an error will occur if the total size of schema objects stored in the data file is greater than the desired size.

The following example changes the size of the data file my_file01.dbf:

ALTER DATABASE DATAFILE 'my_file01.dtf' RESIZE 100M;

To disallow all read and write access to a tablespace, specify the tablespace status to offline using the OFFLINE clause in the TABLESPACE statement.

A tablespace offline status supports the following two modes:

The following example specifies the status of the tablespace my_space as offline in NORMAL mode, and then specifies it as online again:

ALTER TABLESPACE my_space OFFLINE [NORMAL]; 
ALTER TABLESPACE my_space ONLINE;

The following example specifies the status of the tablespace my_space as offline in IMMEDIATE mode, then specifies it as online again:

ALTER TABLESPACE my_space OFFLINE IMMEDIATE; 
ALTER DATABASE RECOVER AUTOMATIC TABLESPACE my_space;
ALTER TABLESPACE my_space ONLINE;

Tibero provides users with several views, including static views and dynamic views, to efficiently manage tablespaces.

The information includes the number and size of extents within a tablespace, the allocated server, the names, sizes, and types of the data file and segments.

A log member is basically a single log file. When composing Redo logs, different log files must be allocated for each log member in each log group. Saving log files and data files on separate disks is recommended. If log files and data files are saved on the same disk, a database cannot be restored if an error occurs on the disk. If each log group consists of multiple log members, at least one log member must be stored on a disk where data files are not saved.

Log Member Multiplexing

To improve the performance of a database, log members in a log group should be saved on separate disks. All log members in the same log group must save the same log records. If all log members exist on separate disks, all log records can be saved at the same time.

The following figure shows log members in the same log group that are located on separate disks:

[Figure 3.4] Log Member Multiplexing

In [Figure 3.4], Log Member 1-1 is the first log member in log group 1. If more than one log member exists on a single disk, log records for each log member cannot be saved at the same time. This reduces the performance of a database system.

If log archive mode is specified as ARCHIVELOG, an inactivated log group is archived while logs are saved in an activated log group. If the inactivated log group and the activated log group are saved on the same disk, this also reduces the performance of a database. Therefore, to improve performance, each log file in a different log group should be saved on a separate disk.

Log Group Multiplexing

The following figure shows two log groups that consist of two log members that are located on separate disks:

[Figure 3.5] Log Group Multiplexing

In [Figure 3.5], Log Member 1-1 is the first log member in log group 1.

Archive jobs need to be considered when deciding the number and size of log groups. The size of a log group should be specified to be able to quickly transfer the log group to a third storage device and to efficiently use the storage space. The number of log groups should be set to not cause delays due to a log group being archived.

The number and size of log groups should be modified while a database is actually operating. That is, specify parameters that are optimized for the database and increase the number and size of log groups within a range that does not reduce the processing performance of the database.

Saving and Multiplexing an Archive Log

The LOG_ARCHIVE_DEST initialization parameter sets the archive log storage location. It is important to save archive logs because they are required for media recovery.

Archive logs can be stored in multiple places by using multiplexing. When multiplexing archive logs, use the initialization parameters LOG_ARCHIVE_DEST_1, LOG_ARCHIVE_DEST_2, ..., LOG_ARCHIVE_DEST_9. To set the location, set 'location=' in a parameter string. After setting the directory, choose 'mandatory' or 'optional' in case the multiplexed archive logs fail to save. (If 'mandatory' or 'optional' is not specified, 'optional' is used by default.)

If set to 'mandatory', multiplexed archive logs are saved to the specified location. If the logs fail to be saved, redo log group are not reused, which can result in shutting down the entire database. If set to optional and the archive logs fail to save, no reattempt to save is made so other jobs can be processed normally.

LOG_ARCHIVE_DEST =   "/usr/tibero/log/archive"
LOG_ARCHIVE_DEST_1 = "location=/usr/tibero/archive1 mandatory"
LOG_ARCHIVE_DEST_2 = "location=/usr/tibero/archive2 mandatory"
LOG_ARCHIVE_DEST_3 = "location=/usr/tibero/archive3"
LOG_ARCHIVE_DEST_4 = "location=/usr/tibero/archive4 optional" 

To create or delete a log group or log members within a log group, use the ALTER DATABASE statement. The system privilege is needed to use the ALTER DATABASE statement.

Creating Log Files

To create a new log group (log files), use the ADD LOGFILE clause to the ALTER DATABASE statement. When adding log files, it must be performed at the log group level.

The following example adds a log group which has two log members (log files).

The minimum size of a log member is 512KB, the maximum size is 2TB.

The size of the two log members is set to 512 KB, which must not be changed.

ALTER DATABASE ADD LOGFILE (
        '/usr/tibero/log/my_log21.log',
        '/usr/tibero/log/my_log22.log') SIZE 512K

In addition, each log group's number can be set by inserting the GROUP option at the end of the ADD LOGFILE clause. If the log group number is set, it can be used later to indicate the log group in order to add new log members.

The following example specifies the number for a log group:

ALTER DATABASE ADD LOGFILE GROUP 5 (
        '/usr/tibero/log/my_log21.log',
        '/usr/tibero/log/my_log22.log') SIZE 512K 

To add a new log member to an existing log group, use the ADD LOGFILE MEMBER clause.

The following example adds a new log member to log group 5:

ALTER DATABASE ADD LOGFILE MEMBER
        '/usr/tibero/log/my_log25.log' TO GROUP 5

When adding a new log member, the log file's size is not specified because the log file will have the same size as other log members within the log group.

Deleting Log Files

To delete a log group, insert the DROP LOGFILE clause.

The following example deletes log group 5:

ALTER DATABASE DROP LOGFILE GROUP 5; 

There are several things to consider before deleting any log group.

To delete a log member within a log group, use the DROP LOGFILE MEMBER clause. The log group does not need to be specified in this clause.

The following example deletes a log member:

ALTER DATABASE DROP LOGFILE MEMBER '/usr/tibero/log/my_log25.log' 

If no log members remain after the deletion, an error will occur. Log members in active or unarchived log groups will not be deleted.

Tibero provides dynamic views to help users manage the redo log. These views show information such as log files for each group, multiplexing information, and update dates. Both the DBA and general users can use these views.

The DBA can add or delete any copy of the control file. Because the control file contains metadata of the database, it is impossible to modify the control file while the database is in operation. Therefore, there are no statements to add or delete copies of the control file. To modify the control file, the database must be shut down.

There are no SQL statements to modify the control file; standard OS commands must be used to make any modifications. After that, the modified contents are applied to the $TB_SID.tip file.

The following example copies the control file via a UNIX command:

$ cp /usr1/tibero/control01.ctl /usr3/tibero/control03.ctl

In the example above, the usr1 and usr3 directories exist on different disks.

When Tibero restarts the database, it reads the $TB_SID.tip file and, depending on the modified contents, it will update the control file. Note that the control file's name in $TB_SID.tip must include its absolute path. Even when an error occurs to some control files due to disk error, if at least one control file works normally, Tibero also performs normally.

Backing up the control file is only done with the support of logical backup. Therefore, it is necessary to back up the SQL statement that creates the control file.

In particular, when tablespaces, data files, and the redo log are created, modified, or deleted, it is best in terms of database management to back up the control file immediately. When backing up the entire database, the control file itself must also be backed up. For more details, refer to “6.2.2. Performing a Backup”.

The following statement backs up the control file:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS
          '/tibero6/backup/ctrlfile1.sql' REUSE NORESETLOGS;

As shown above, the absolute path must be specified because the copy of the control file to be backed up (ctrlfile1.sql) needs to be stored on a separate disk.

Tibero provides dynamic views to help users manage the control files. The information includes the database creation time and checkpoint. Both the DBA and general users can use these views.