Managing GOLDILOCKS Instance

This chapter describes the basic knowledge required for managing a GOLDILOCKS instance.

Overview

The GOLDILOCKS database system consists of a database and an instance. The database is a collection of various files necessary for operating the database, including dictionary data in memory, user data, data files for dictionary data and user data, and online redo files.

A database instance consists of two parts. one is the memory portions that contain runtime information for operating the GOLDILOCKS database, and the other is the background processes used to operate and manage it. Each database instance is identified by the shared memory key value and the "GOLDILOCKS_DATA" environment variable, both of which are used to configure shared memory.

Property Setting

GOLDILOCKS properties are listed in $GOLDILOCKS _DATA/conf/goldilocks.properties.conf file. A user may install GOLDILOCKS using the basic properties provided. This chapter describes the main properties, including TBS (Tablespace), LOG and CONTROL FILE.

The following sections describe the main property items involved in installing GOLDILOCKS.

A user can modify the text property file ($GOLDILOCKS_DATA/conf/goldilocks.properties.conf), or define a new variable in the form of GOLDILOCKS_<property_name> to change the database or instance settings. In terms of priority, the property file takes precedence over the environment variable.

Background Process

GOLDILOCKS includes a background process (gmaster) for managing instance. The gmaster process consists of multiple internal system threads, described as follows.

Client Process

Client/ Server Model

Applications using the Client/Server (C/S) model connects to a listener (glsnr) that is waiting for access requests. Then, it creates a new database service process (gserver), in dedicated mode to handle the user's request by using TCP communication. All operations are carried out through inter process communication, ensuring that any signals generated in the application process does not affect on the state of the database. Moreover, cleanup thread regularly checks and returns all the resources used by abnormally terminated application.

Direct Access Model

GOLDILOCKS supports a Direct Access (D/A) model as well as a Client/Server (C/S) model. In the D/A model, applications are linked to the server library provided by GOLDILOCKS and directly access the database and instance. Therefore, there are no additional service processes, only the application processes exist.

When a D/A model application process is interrupted abnormally by a signal generated during operation, all resources in use will be cleaned up by the signal handler function set by the library during connection. The function cleans up the resources according to the following two steps.

1. The signal handler marks the session object as having terminated abnormally and then terminates the process.

2. The cleanup thread of gmaster returns resources to the database in a manner similar to the C/S model after a certain period of time.

In the D/A model, application processes access the database area directly. Therefore, adhere to the following precautions.

• When the D/A model application process is terminated by a fatal signal, such as SEGV, the signal handler registered by the library at the time of database connection should cease using shared resources. If a custom signal handler needs to be installed, it should be declared before connecting to the database.

• When forcibly shutting down the application process, avoid using SIGKILL (kill -9), as the process can not detect this signal. Instead, use SIGTERM, SIGQUIT, or SIGUSR2.

Memory Architecture of Instance

The memory size utilized by the database instance is determined by the relevant properties specified in the property file. The shared memory used by the instance can be divided into two main areas: the static area and the tablespace area. The static area includes fundamental information about the public instance, as well as details related to each session, statement, transaction, redo log buffer, dictionary cache, and several other operations. The tablespace area consists of the page frames for each tablespace and the Page Control Header (PCH), which manages the page frames.

The application process memory includes instance memory attached at connection. Additionally, it includes the process-based shared ODBC environment, several ODBC handles, and a heap memory area with bind information.

Startup and Shutdown of the Instance

To start up the GOLDILOCKS instance, set the SHARED_MEMORY_STATIC_KEY property differently from other instances. After that, you can start up the GOLDILOCKS instance by using gsql. Execute the following command to assume the SYSDBA role.

Before starting up or shutting down the GOLDILOCKS instance in dedicated mode of the C/S model, ensure that the listener is running. Note that you cannot start up or shut down the GOLDILOCKS instance in shared mode of the C/S model.

% gsql sys gliese --as sysdba

Connected to GOLDILOCKS Database.

gSQL>

Startup phrase in the GOLDILOCKS instance has several phases as follows.

• NOMOUNT

  • It starts up gmaster process, which is the managing daemon of the GOLDILOCKS instance.

• MOUNT

  • It loads properties and the recovery control file using the $GOLDILOCKS _DATA environment variable.

• OPEN

  • After loading the tablespace contents from data files, it performs recovery using redo logs, rebuilds no-logging indexes, creates the dictionary cache, and then waits for user service connections.

To start up the GOLDILOCKS instance, use the following gsql command.

gSQL> ＼startup nomount
Startup success

gSQL> alter system mount database;
System altered.

gSQL> alter system open database;
System altered.

To directly enter into the OPEN phase, do as follows.

gSQL> ＼startup open
Startup success

If the GOLDILOCKS instance is shut down, the gmaster (the management daemon process) will be terminated. As a result, connections and database operations will no longer be possible.

There are four ways to shut down the GOLDILOCKS instance, as follows.

• NORMAL

  • After blocking access for new sessions and waiting for all connected sessions to end, a user performs a checkpoint and then shuts down the instance.

• TRANSACTIONAL

  • After blocking the start of new transactions and waiting for all running transactions to complete, a user performs a checkpoint and then shuts down the instance.

• IMMEDIATE

  • After blocking the execution of new unit operations (e.g., FETCH or EXECUTE, which are connection units with the GOLDILOCKS database) and waiting for all ongoing unit operations to complete, a user rolls back all transactions, performs a checkpoint, and then shuts down the instance.

• ABORT

  • Regardless of the status of connected sessions, a user terminates the gmaster process and shuts down the instance.

To shutdown an instance, use gsql with the sysdba role and perform the \shutdown command as follows.

% gsql sys gliese --as sysdba

Connected to GOLDILOCKS Database.

gSQL> \shutdown normal

Shutdown success

gSQL>

Start and End of Listener

A user should run the listener to provide service in the client/ server environment.

A user can start the listener as follows.

% glsnr --start
 
Listener is started successfully.

%

A user can stop the listener as follows.

% glsnr --stop
 
Listener is stopped.

%

For more information about listener control, refer to glsnr.
For more information about how to start or end the cluster system, refer to Start and End of Cluster System.

Installing GOLDILOCKS and Creating a Database

This chapter describes how to install the GOLDILOCKS software and create a database.

Overview

GOLDILOCKS  software comes as a compressed file named goldilocks-<version_no>-<os_type>-<cpu_type>.tar.gz. After decompressing the file, the software binaries, various samples and fundamental database directory structures are created at the specified location, completing the installation. Following the installation, a directory named after the package is created.  Within this directory, two additional directories are created, which are goldilocks_home and goldilocks_data are created under it.

• goldilocks_home directory

  • Binary home of GOLDILOCKS product

  • Defined as GOLDILOCKS_HOME environment variable

  • Operation binaries, libraries for the client, header files, licenses, etc. are located.

• goldilocks_data directory

  • Home of the user database (instance) which GOLDILOCKS creates.

  • Defined as GOLDILOCKS _DATA environment variable

  • A default location which has data files, log files, property files

After that, use the utility called gcreatedb in $GOLDILOCKS_HOME/bin directory to create a database in the $GOLDILOCKS _DATA directory.

Release Platform

GOLDILOCKS is available on the following release platforms.

System Requirements

Before installing GOLDILOCKS, ensure that the following requirements are met.

• At least 2 GB of physical memory and disk space must be available.

• A sufficient amount of paging (swap) space is also required.

• Additionally, the correct package version that matches the platform to be installed must be used.

GOLDILOCKS Package Configuration

This chapter describes the directory configuration required for installing GOLDILOCKS.

Package Directory Configuration

Package File List

The following is a description of the files in a directory and their inclusion in the server or client package.

The script created in the admin/standalone directory is used when running GOLDILOCKS in standalone. Conversely, the script created in the admin/cluster directory is used when using GOLDILOCKS by configuring cluster system.

The CDC package includes the following files.

Installing GOLDILOCKS Software

This chapter describes the operating system requirements and environment settings needed before installing GOLDILOCKS.

Kernel Parameters

Shared Memory

Shared memory is a type of Inter-Process Communication (IPC) used for sharing data among multiple programs. GOLDILOCKS utilizes shared memory with user programs through gsql, gloader, and ODBC in a Client/Server (C/S) environment. Since all tablespaces for operations are created in shared memory, precise value settings are required.

The following are the parameters and recommended values required for the shared memory used to install GOLDILOCKS.

The following is an example of how to set shmall when the total size of tablespaces is 32 GB.

kernel.shmmax = 34359738368
kernel.shmmni=4096
kernel.shmall = 8388609

• Assume that the shmmax is 32 GB and PAGE_SIZE is 4096 bytes.

8388609 = (34359738368 / 4096) + 1

• In this case, the value of shmall must be greater than 8388609.

Semaphore

A semaphore is a type of IPC, similar to shared memory, and is a technology to control multiple processes' behavior using the resources provided by the operating system. Depending on the semaphore settings, multiple processes can refer to a relevant resource simultaneously, while any process using the resource will cause other processes to wait until it is available.

GOLDILOCKS uses semaphore to control the access sequence to the shared memory. For example, if multiple GOLDILOCKS client programs request a change to the same data, it should be controlled properly. The semaphore parameter value should be set to an appropriate value according to semaphore operation of GOLDILOCKS. A general Linux value is recommended.

The following are the recommended semaphore values for installing GOLDILOCKS.

In Linux-based systems such as Redhat or Ubuntu, if a user who created IPC resource logs out from the session list is managed by systemd, the corresponding IPC resources are automatically deleted. Therefore, the system must be set as follows to prevent the deletion of semaphores. (for kernel 3.0.0 and higher)

# cp -i /etc/systemd/logind.conf /etc/systemd/logind.conf_prev

# cat /etc/systemd/logind.conf
[Login]
#NAutoVTs=6
#ReserveVT=6
...
RemoveIPC=no

• Execute after the modifications.

# systemctl restart systemd-logind

Network

The backlog refers to the queue length of sockets waiting to be accepted during the TCP socket listen process. Set the glsnr's backlog in GOLDILOCKS to the value specified in the glsnr configuration file under BACKLOG. If the system's maximum backlog value exceeds somaxconn, then set it to somaxconn. In such cases, increasing the somaxconn value.

GOLDILOCKS uses a Unix Domain Socket (UDS) queue when operating in C/S shared mode. The queue length is set to max_dgram_qlen. If this value is too small while clients are accessing the network simultaneously, it can cause a communication bottleneck among glsnr, gbalancer and gdispatcher.

The following are the recommended network values for installing GOLDILOCKS.

Applying Parameters

For a one-time execution, a user can proceed as follows. (It is necessary to reapply the settings when the system is restarted.)

[SHELL]> echo   34359738368   >  /proc/sys/kernel/shmmax
[SHELL]> echo   8388608 >   /proc/sys/kernel/shmall
[SHELL]> echo   4096   >  /proc/sys/kernel/shmmni
[SHELL]> echo   250 32000 100 128  /proc/sys/kernel/sem
[SHELL]> echo   1024   >  /proc/sys/net/core/somaxconn
[SHELL]> echo   256   >  /proc/sys/net/unix/max_dgram_qlen

If a user wants to apply the settings automatically even after a system restart, the user can proceed as follows in /etc/sysctl.conf.

# shared memory
kernel.shmmax = 34359738368
kernel.shmall = 8388608
kernel.shmmni = 4096

# semaphore
kernel.sem = 250 32000 100 128

# network
net.core.somaxconn = 1024
net.unix.max_dgram_qlen = 256

Use the following command to apply the described changes.

[SHELL]> sysctl  -p

Verifying Parameters

The described parameters can be checked by using the following commands.

[SHELL]> ipcs -l

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 33554432
max total shared memory (kbytes) = 33554432
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767

Decompressing GOLDILOCKS

The GOLDILOCKS package is supplied in a compressed form. The basic installation is completed by decompressing it.

The following are simple examples of how to install the GOLDILOCKS package.

##  $GOLDILOCKS_HOME=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/

[SHELL]> gzip –d goldilocks-mercury.2.1.0-linux-x86_64.tar.gz

[SHELL]> tar -xvf goldilocks-server-mercury.2.1.0-linux-x86_64.tar
goldilocks-server-mercury.2.1.0-linux-x86_64/goldilocks_home/include/sqlext.h
goldilocks-server-mercury.2.1.0-linux-x86_64/goldilocks_home/include/goldilocks.h
goldilocks-server-mercury.2.1.0-linux-x86_64/goldilocks_home/include/sqlca.h
…

When the decompression is complete, the user can rename the directory <package_file_name> as desired. Consequently, the user must update the environment variables $GOLDILOCKS _HOME and $GOLDILOCKS _DATA accordingly.

For more information about directory created after decompression, refer to GOLDILOCKS Package Configuration

Setting Enviromment Variables

After decompressing the GOLDILOCKS package, to run the GOLDILOCKS software and develop applications, add the bin and lib directories, created under the $GOLDILOCKS_HOME directory, to the PATH and LD_LIBRARY_PATH environment variables as follows. (When developing GOLDILOCKS client application, a user should always insert $GOLDILOCKS_HOME/include to include file directory of compile option.)

If a shared library exists to call external library functions, the directory set in the EXTLIB_DIR property must be added to LD_LIBRARY_PATH to include it in the search path so that the library can be searched.

export PATH=$GOLDILOCKS_HOME/bin:$PATH
export LD_LIBRARY_PATH=$GOLDILOCKS_HOME/lib:$GOLDILOCKS_DATA/extlib:$LD_LIBRARY_PATH

Environment variables are required to use GOLDILOCKS. The user must set them before installation, as some variables are referenced even during the installation process.

##  $GOLDILOCKS_HOME=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/

[SHELL]> gzip –d goldilocks-mercury.2.1.0-linux-x86_64.tar.gz

[SHELL]> tar -xvf goldilocks-server-mercury.2.1.0-linux-x86_64.tar
goldilocks-server-mercury.2.1.0-linux-x86_64/goldilocks_home/include/sqlext.h
goldilocks-server-mercury.2.1.0-linux-x86_64/goldilocks_home/include/goldilocks.h
goldilocks-server-mercury.2.1.0-linux-x86_64/goldilocks_home/include/sqlca.h
…

Deleting Database

When deleting an existing GOLDILOCKS DATABASE to recreate it, the datafile, control file, redo log file, and archive log file (if using archive logs) must be deleted.

The following is an example of how to delete a GOLDILOCKS DATABASE.

## $GOLDILOCKS_BASE=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
## $GOLDILOCKS_DATA=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
                    goldilocks_data/
## $GOLDILOCKS_HOME=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
                    goldilocks_home/
[SHELL]> rm -rf $GOLDILOCKS_DATA/db/*.dbf
[SHELL]> rm -rf $GOLDILOCKS_DATA/wal/*.ctl
[SHELL]> rm -rf $GOLDILOCKS_DATA/wal/*.log
[SHELL]> rm -rf $GOLDILOCKS_DATA/archive_log/*.log

The location of the data files and archive files may vary depending on user settings.

Deleting

The GOLDILOCKS package is provided in a compressed file format, so a specific deletion rule is not required. To remove the package, delete the installed directory after terminating the DATABASE.

The following is an example of how to delete the GOLDILOCKS package.

## $GOLDILOCKS_BASE=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
## $GOLDILOCKS_DATA=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
                    goldilocks_data/
## $GOLDILOCKS_HOME=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
                    goldilocks_home/
[SHELL]> rm -rf $GOLDILOCKS_DATA
[SHELL]> rm -rf $GOLDILOCKS_HOME
[SHELL]> rm -rf $GOLDILOCKS_BASE



## ~/.bash_profile
$GOLDILOCKS_BASE=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/ 1 Delete
$GOLDILOCKS_DATA=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
                    goldilocks_data/ 2 Delete
$GOLDILOCKS_HOME=/home/GOLDILOCKS/goldilocks-mercury.2.1.0-linux-x86_64/
                    goldilocks_home/ 3 Delete

Creating Database

Create the database after the completing the property creation. A user can create the database using the $GOLDILOCKS_HOME/bin/gcreatedb command. The following are instructions on how to use the gcreatedb commands.

[SHELL]> gcreatedb --help
Usage 

    gcreatedb [options]

Options:

    --cluster        cluster system (if not specified, stand-alone system)
    --db_name        database name
    --db_comment     database comment
    --timezone       timezone ( {+/-}{TZH:TZM} )
    --character_set  character set
                       SQL_ASCII
                       UTF8
                       UHC
                       GB18030
    --char_length_units  char length units
                          OCTETS
                          CHARACTERS
    --member         local cluster member name
    --host           cluster ip address or host name
    --port           cluster port number
    --silent         suppresses the display of the result message
    --help           print help message

examples:

    gcreatedb --db_name="goldilocks" --db_comment="goldilocks database" --timezone="+09:00" --character_set="UTF8" --char_length_units="OCTETS" --silent

The file $GOLDILOCKS_HOME/conf/goldilocks.properties.conf is referenced when creating the database. Tablespace files are created in the SYSTEM_TABLESPACE_DIR path specified in goldilocks.properties.conf with the value of ***_TABLESPACE_SIZE.

The --cluster option must be specified when creating a database that will participate in a cluster system.

The following are the execution arguments for gcreatedb.

A user must consider the following when creating a database.

• Setting kernel parameter shared memory

  • If the size specified in the shared memory setting is smaller than the size described in $GOLDILOCKS _HOME/conf/goldilocks.properties.conf, you can not create the database.

• Tablespace size

  • When creating a database, the tablespace files created will be allocated and used in memory according to their size when GOLDILOCKS is started. Therefore, memory is used even if there is not actual user data in the data tablespace. Therefore, when writing goldilocks.properties.conf, you should consider not only the shared memory but also the available memory on the machine where GOLDILOCKS will be started to ensure that there is sufficient memory for the database.

• Cluster System Usage

  • You must specify whether the database will participate as a member of a cluster system during database creation. Specifically, the --cluster option must be omitted when executing gcreatedb using the database as standalone, but you must include the --cluster option when using the database as a cluster system.

When the database is created successfully, you can check the following tablespace files in the path described in SYSTEM_TABLESPACE_DIR of goldilocks.properties.conf.

[SHELL]> gcreatedb
Database created

[SHELL]> gcreatedb --db_name="TEST_DB"                   \
                   --db_commnet="test database comment"  \
                   --timezone="+09:00"                   \
                   --character_set="UHC"                 \
                   --char_length_units="OCTETS"
Database created

[SHELL]> ls $GOLDILOCKS_DATA/db
system_data.dbf  system_dict.dbf  system_undo.dbf

The following is an example of creating a database for use in a cluster system.

[SHELL]> gcreatedb --cluster
Database created

[SHELL]> gcreatedb --cluster --member=G1N1
Database created

[SHELL]> gcreatedb --cluster --member=G1N1 --host=127.0.0.1 --port 10101
Database created

[SHELL]> gcreatedb --cluster                         \
                   --db_name="TEST_DB"               \
                   --home=$GOLDILOCKS_DATA           \
                   --host=127.0.0.1                  \
                   --port=10101                      \
                   --db_comment="g1n1 db comment"    \
                   --timezone="+09:00"               \
                   --character_set="UHC"             \
                   --char_length_units="OCTETS"
Database created

[SHELL]> ls $GOLDILOCKS_DATA/db
system_data.dbf  system_dict.dbf  system_trans.dbf system_undo.dbf

Building Dictionary Schema Information

Create the following schema to retrieve system and object information.

• DICTIONARY_SCHEMA: This schema consists of views and tables that provide object information, including those prefixed with DBA_, ALL_, and USER_*.

• INFORMATION_SCHEMA: This schema consists of views and tables that are part of the SQL standard INFORMATION_SCHEMA.

• PERFORMANCE_VIEW_SCHEMA: This schema comprises views that aggregate system information by combining data from fixed tables.

Views and tables included in each schema provide convenience for obtaining system information.

After starting the GOLDILOCKS instance in the OPEN phase, the user must execute the following SQL files. This step must be performed at least once after the initial database creation.

The following describes how to build the dictionary schema information using the script for a standalone database.

% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/standalone/DictionarySchema.sql
% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/standalone/InformationSchema.sql
% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/standalone/PerformanceViewSchema.sql

The following describes how to build the dictionary schema information using the script for a cluster system.

% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/cluster/DictionarySchema.sql
% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/cluster/InformationSchema.sql
% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/cluster/PerformanceViewSchema.sql

The following describes how to create a package to use a built-in package.

% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_LOCK.sql
% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_OUTPUT.sql
% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_SQL.sql
% gsql --as sysdba --import $GOLDILOCKS_HOME/admin/packages/DBMS_STANDARD.sql

Managing Database Memory Structure

This chapter describes the database components that compose a GOLDILOCKS instance.

Database Memory Structure

The GOLDILOCKS database is primarily divided into memory and disk areas. The memory area consists of a collection of tablespaces, each made up of one or more shared memory segments, and it does not involve writing to disk during replacement operations.

The disk area consists of data files, control files, property files, and online redo log files. Each data file corresponds to a shared memory segment of a tablespace. The control file contains instance configuration information, while the property file stores instance environment settings. The online redo log files are used for database recovery.

Control File

The control file records information about the physical storage of the database on disk and determines the database's status by being read first at the beginning of instance startup. The control file includes the following information:

• Instance state at the time of the previous checkpoint

• Transaction durability mode (CDS/TDS) at the time of the previous startup

• Online redo log file information

• Data file information for each tablespace

Online Redo Log File

Online redo log files record all changes made to the database by transactions in instances. They are used to recover any unwritten changes to the data files when restarting an instance after an abnormal database termination. By default, four online redo log files are generated with the size specified by the LOG_FILE_SIZE property during database creation. Users can add more log files if needed. The redo log files are reused in a circular manner.

Checkpoints are created when the online redo log file switches to the next file, causing some dirty pages to be written to the appropriate data files. If the checkpoint operation is delayed and updated pages are not written (remaining in the ACTIVE state), all transactions will be suspended until the checkpoint is completed. Therefore, configuring an appropriately sized online redo log file based on the application's characteristics can help improve database system performance.

Undo Segment

Undo segments record images of data before a change operation, allowing transactions to be rolled back partially or fully. Each undo segment is assigned to a single transaction during update operations. Undo segments are stored in the MEM_UNDO_TBS tablespace. It is recommended to allocate sufficient undo tablespace to accommodate multiple update transactions or a single transaction involving a large number of updates (e.g., bulk deletes).

Data File

A data file contains the contents of tables and indexes stored within a tablespace.

A data file consists of the following:

• Page

  • The smallest unit of database I/O operations, with a current size of 8 kilobytes (KB).

• Extent

  • A collection of a certain number of contiguous pages. It is the smallest unit of space allocation for a segment within a tablespace.

  • The extent size can vary between tablespaces.

• Segment

  • A collection of a specific type of data structure. A segment consists of a set of extents.

Exceptionally, a temporary tablespace, such as the MEM_TEMP_TBS tablespace, does not perform redo logging, nor does it create data files.

Tablespace

A database is divided into tablespaces, which are logical structures containing tables and indexes. GOLDILOCKS tablespaces are classified into memory tablespaces and disk tablespaces. In a memory tablespace, shared memory is created for each data file, so there is no separate disk I/O when accessing pages. In contrast, a disk tablespace uses the system’s buffer cache to access pages. Information about the tablespaces currently present in the database can be retrieved by querying the V$TABLESPACE view.

GOLDILOCKS supports the following tablespaces by default.

Tablespace Type

There are five types of tablespaces as follows.

• DICT

  • Tablespace type for storing dictionary tables and indexes

• DATA

  • Tablespace type for storing general schema objects, such as tables and indexes

  • This tablespace is subject to redo logging and page flushing.

• UNDO

  • Tablespace type for storing undo segments

  • This tablespace is also subject to redo logging and page flushing.

• TEMPORARY

  • Tablespace type for storing temporary tables and no-logging indexes created during SELECT queries

  • This tablespace is not subject to redo logging and page flushing.

• TRANSACTION (Cluster only)

  • Tablespace type used to recover global transaction in a cluster system

Checking Database Storage Structure Information

This chapter describes the method for checking information about the various database storage structures mentioned above.

Control File Information

Use the gdump utility to check the contents, as control file is stored in binary format.

[SHELL]> gdump CONTROL control_0.ctl

Online Redo Log File Information

Retrieve the control file by using gdump tool, then the name and current state of each online redo file will be displayed.

Data File Information

Use the gdump tool to retrieve the control file, which will display the data files in each tablespace and their states. Additionally, you can view the V$DATAFILE table to see their current states.

Tablespace Information

Use the gdump tool to retrieve the control file, which will display the name and state of each tablespace in the current database. Additionally, you can also query the V$TABLESPACE table using SQL.

Property Information

Open the text file $GOLDILOCKS_Data/conf/goldilocks.properties.conf to view the property information. For online operations, retrieve the V$PROPERTY and V$DB_PROPERTY tables to display the currently applied property values.

General Operation of Data Storage

A tablespace stores data, and its operations are as follows:

Creating Tablespace

Memory and disk USER DATA tablespaces can be created using the following commands:

gSQL> CREATE TABLESPACE TEST_TBS DATAFILE 'TEST_TBS.dbf' SIZE 10M;

Tablespace created.

gSQL> CREATE MEMORY TABLESPACE TEST_TBS DATAFILE 'TEST_MEM_TBS.dbf' SIZE 10M;

Tablespace created.

gSQL> CREATE DISK TABLESPACE TEST_TBS DATAFILE 'TEST_DISK_TBS.dbf' SIZE 10M;

Tablespace altered.

gSQL> CREATE DISK TABLESPACE DISK_TBS DATAFILE 'TEST_DISK_TBS.dbf' SIZE 10M AUTOEXTEND OFF;

Tablespace altered.

The TEMPORARY tablespace does not include a data file, so it is created as follows:

gSQL> CREATE TEMPORARY TABLESPACE TEST_TEMP_TBS MEMORY 'TEST_TEMP_TBS' SIZE 10M;

Tablespace created.

Retrieving Tablespace Usage Status

Space in a tablespace is allocated or deallocated in units of one extent, which consists of one or more consecutive pages. To retrieve the size of one extent (in bytes) for a specific tablespace, use the following method:

gSQL> SELECT EXTENT_SIZE FROM V$TABLESPACE WHERE TBS_NAME = 'TEST_TBS';

EXTENT_SIZE
-----------
     262144

1 row selected.

A user can view the state of all extents in tablespaces by using D$TABLESPACE_EXTENT table. When an extent is in use, the STATE column is 'U'. When an extent is in free state, the STATE column is 'F'. Therefore, the remaining size of space in the current tablespace (the number of extents) can be calculated as follows.

gSQL> SELECT COUNT(*) FROM D$TABLESPACE_EXTENT('TEST_TBS') WHERE STATE = 'F';

COUNT(*)
--------
      38

1 row selected.

Based on the result above, the empty space in TEST_TBS is calculated as 38×262,144=9,437,18438 \times 262,144 = 9,437,18438×262,144=9,437,184 bytes.

Altering Tablespace

A user can alter tablespaces by using Add/Remove Data File (or Memory for temporary tablespaces), and Online/Offline.

Add/ Drop Data File (or Memory)

If a user wants to add space to tablespaces while the database is operating, the DATA tablespaces can allocate additional space using the following syntax:

gSQL> ALTER TABLESPACE TEST_TBS ADD DATAFILE 'TEST_TBS2.dbf' SIZE 10M;

Tablespace altered.

The TEMPORARY tablespace adds space as follows. Unlike the DATA tablespace, a name must be specified, and it should be a unique memory name within the database.

gSQL> ALTER TABLESPACE TEST_TEMP_TBS ADD MEMORY 'TEST_TEMP_TBS2' SIZE 10M;

Tablespace altered.

A user can drop space in the DATA tablespace using the following syntax. However, if any part of the space is used, it can not be dropped.

gSQL> ALTER TABLESPACE TEST_TBS DROP DATAFILE 'TEST_TBS2.dbf';

Tablespace altered.

Similarly, a user can withdraw space from the TEMPORARY tablespace using the following syntax.

gSQL> ALTER TABLESPACE TEST_TEMP_TBS DROP MEMORY 'TEST_TEMP_TBS2';

Tablespace altered.

Offline Tablespace

Switch the tablespace to offline mode if you want to move the location of a data file within the tablespace. Use the following syntax:

gSQL> ALTER TABLESPACE TEST_TBS OFFLINE;

Tablespace altered.

A user can switch the tablespace back to online mode using the following syntax:

gSQL> ALTER TABLESPACE TEST_TBS ONLINE;

Tablespace altered.

Altering Automatic Data File Expand Property in Disk Tablespace

The data file in a disk tablespace is created with a initial size, and automatically extends to its maximum size as needed. The automatic expansion feature can be enabled or disabled (on/off) as follows. When altering the automatic expansion property, you can also modify the automatic expansion size and the maximum size of the data file.

gSQL> ALTER DATABASE DATAFILE 'TEST_DISK_TBS.dbf' AUTOEXTEND ON;

Database altered.

gSQL> ALTER DATABASE DATAFILE 'TEST_DISK_TBS.dbf' AUTOEXTEND OFF;

Database altered.

gSQL> ALTER DATABASE DATAFILE 'TEST_DISK_TBS.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 20M;

Database altered.

Rename

Rename the tablespace using the following syntax:

gSQL> ALTER TABLESPACE TEST_TBS RENAME TO TEST_TBS2;

Tablespace altered.

Switch the tablespace to offiline mode if you want to change the location of the data file in the tablespace. Then, move the data file using an OS command, rename the datafile of the tablespace using the ALTER TABLESPACE statement, and finally switch the tablespace back to online mode.

gSQL> ALTER TABLESPACE TEST_TBS OFFLINE;

Tablespace altered.

gSQL> ALTER TABLESPACE TEST_TBS RENAME DATAFILE 'TEST_TBS.dbf' TO 'TEST_TBS_1.dbf';

ERR-42000(16164): file does not exist : 
ALTER TABLESPACE TEST_TBS RENAME DATAFILE 'TEST_TBS.dbf' TO 'TEST_TBS_1.dbf'
                                                                         *
ERROR at line 1:

• The following procedure describes how to rename a file using an OS command:

gSQL> ALTER TABLESPACE TEST_TBS RENAME DATAFILE 'TEST_TBS.dbf' TO 'TEST_TBS_1.dbf';

Tablespace altered.

gSQL> ALTER TABLESPACE TEST_TBS ONLINE;

Tablespace altered.

Dropping Tablespace

Drop an unnecessary tablespace using the following syntax. The statement after INCLUDING is optional. If the statement is given, it will delete all content (schema objects) and data files within the tablespace.

gSQL> DROP TABLESPACE TEST_TBS INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Store Mode

GOLDILOCKS uses the store mode as an instance unit to maximize performance under certain circumstances. Store mode defines which aspect of the ACID properties can be sacrificed to improve transaction performance.

GOLDILOCKS supports two types of store modes.

• Transactional Data Store (TDS) mode

  • TDS mode is the default store mode for the GOLDILOCKS database.

  • Transactional Data Store (TDS) mode is a standard DBMS store mode. In this mode, all transactions in the instance write both undo logs and redo logs. As a result, users can roll back transactions and recover data using the data file and online redo log file, even if the instance terminates abnormally, due to periodic checkpoints.

• Concurrent Data Store (CDS) mode

  • In CDS mode, all running transactions in the instance write undo logs but do not write redo logs. This allows transactions to handle run-time errors and roll back if necessary. However, if an instance is terminated abnormally or terminated using the shutdown abort statement, all updated data will be lost. (The mode does not provide recover facilities). CDS mode controls concurrency among transactions ensuring normal operations of transactions even when different transactions access the same object simultaneously.

  • CDC mode is primarily used in runtime information-oriented databases, such as cache server, where frequent query/update occur but durability is not a requirement.

The store mode is configured through property settings when the instance is started. Transactions can not be executed across different store modes. Therefore, users must carefully select the store mode during instance startup, as it cannot be changed while the instance is running in online mode.

Managing Schema Object

Schema Object

A schema object is a set of logical structures created by a user. GOLDILOCKS supports the following schema objects: table, index, synonym, view, sequence, constraint, stored procedure, stored function, package, library, and trigger.

Schema Object Management Privileges

Currently, GOLDILOCKS supports user accounts and their associated privileges. Therefore, not all created objects are shared with all users, and privileges must be granted to allow sharing.

Managing Table

This chapter covers an overview of tables, methods for retrieving table information, procedures for creating, altering tables and loading/ dropping data.

Table

A table is the fundamental unit of storage for user data. It is composed of columns and rows.

Table Type

Currently, GOLDILOCKS supports general heap tables, where the order of data storage is not related to the sort order of any particular column. However, GOLDILOCKS does not support clustered tables or partitioned tables.

• It supports only basic heap tables.

• It supports primary key/unique/not null constraints.

• It supports the following data types:

  • BOOLEAN

  • SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, NUERMIC, FLOAT

  • CHAR (MAX 2000), VARCHAR (MAX 4000), BINARY, VARBINARY, LONG VARCHAR, LONG VARBINARY

  • DATE, TIME, TIMESTAMP, INTERVAL (It supports WITH/WITHOUT TIMEZONE of TIME/TIMESTAMP.)

  • It does not support the BLOB type.

• There is no limit on the number of columns, indexes, or constraints.

• A user can retrieve the entire table information using the query: SELECT * FROM TABLES;.

If a large number of rows are stored in a particular table, resulting in a big table size, even a bulk delete operation may not return the table's empty space to the tablespace. However, TRUNCATE operation can return all the existing space to the tablespace.

Table data can be stored and retrieved across multiple nodes according to the user's desired distribution policy when GOLDILOCKS is configured it as a cluster system. For more information, refer to Managing Table

Managing Index

This chapter covers an overview of indexes and procedures for creating and deleting them.

Overview

An index is a subsidiary schema object linked to tables. It allows users to quickly locate rows that meet specific conditions. Additionally, if the column is a key column of the index, users can directly retrieve the values from that column.

GOLDILOCKS allows the creation of multiple indexes on tables as needed. However, having too many indexes can burden the execution of insert, update, and delete operations, potentially reducing performance.

A primary key or unique constraint automatically creates an index on the associated column.

Index Property

• It supports B-link tree index.

  • GOLDILOCKS provides B-link tree index by default.

• The size of a single index node is 8 KB.

• The maximum number of key columns is 32, and the maximum key length is 2000 bytes.

• Unique indexes are supported..

• Sorting options include Ascending/Descending, NULLS FIRST, and NULLS LAST.

  • Users can define whether to sort index key columns in ascending order (ASC) or descending (DESC) order.

  • Users can define whether to list NULL values of the index key column first (NULLS FIRST) or last (NULLS LAST).

• Users can retrieve the entire index information by using the query: SELECT * FROM INDEXES;.

• The index size can be calculated similarly to a table because an index is implemented as a segment in the same way a table is.

Sequence

A sequence is a schema object that generates unique numbers.

A user can create a sequence as follows:

gSQL> CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE; 

Sequence created.

A user can use the sequence by utilizing NEXTVAL.

gSQL> SELECT customers_seq.NEXTVAL FROM dual;

A user can drop the sequence as follows:

gSQL> DROP SEQUENCE customers_seq;

When a user creates a sequence in GOLDILOCKS configured a cluster system, a global sequence object is automatically created. This object manages the global pool of sequence values, which are shared among all member nodes in the cluster. Each member node is allocated a specified number of sequence values from the global object when calling NEXTVAL and uses them accordingly.
For more information, refer to Global Sequence.

Managing User

Creating User

Only the SYS user and users with the CREATE USER ON DATABASE privilege can create users for the GOLDILOCKS database. The CREATE SESSION ON DATABASE privilege is required to connect using the newly created user.

The following is the syntax to create a user:

<user definition> ::=
    CREATE USER user_identifier IDENTIFIED BY password
    [ DEFAULT TABLESPACE tablespace_name ]
    [ TEMPORARY TABLESPACE tablespace_name ]

The following are the syntax rules and parameters for creating a user:

• user_identifier

  • It specifies the username to be created.

  • Both the username and the role name must be unique.

  • The length of a user_identifier must be less than 128 bytes.

• password

  • The password is stored in an encrypted form.

  • The length of the password must be less than 128 bytes.

  • Passwords are case-sensitive.

• DEFAULT TABLESPACE tablespace_name

  • It specifies the default tablespace where objects created by the user being created, such as tables and indexes (with logging), will be stored.

  • If the DEFAULT TABLESPACE clause is omitted, it defaults to the data tablespace (MEM_DATA_TBS) defined when creating the DATABASE (<database definition>).

• TEMPORARY TABLESPACE tablespace_name

  • It specifies the TABLESPACE for storing user-created temporary tables, indexes (NO LOGGING), and intermediate results generated by queries.

  • If the TEMPORARY TABLESPACE clause is omitted, it defaults to the temporary tablespace (MEM_TEMP_TBS) defined when creating the DATABASE (<database definition>).

• INDEX TABLESPACE tablespace_name

  • It specifies the default tablespace for storing index objects created by the user being created.

  • If the INDEX TABLESPACE clause is omitted, the default value is INDEX TABLESPACE NULL.

Dropping User

It drops a user from the GOLDILOCKS database. The access privileges and user range required to drop a user are the same as those required to create a user.

The following is the syntax for dropping a user:

<drop user statement> ::=
    DROP USER [ IF EXISTS ] user_identifier [ <drop behavior> ]
    ;

<drop behavior> ::=
      RESTRICT
    | CASCADE

The following are the syntax rules and parameters for dropping a user.

• IF EXISTS

  • Even if the user does not exist, an error does not occur.

• user_identifier

  • It is the database username to be dropped.

  • Users cannot drop certain built-in users, such as SYS, which are automatically created when the database is created.

  • It does not drop objects, such as tablespaces, that were created by user_identifier but are not owned by them.

• drop behavior

  • If drop behavior is omitted, the default value is RESTRICT.

Altering User

It alters the definition of a GOLDILOCKS database user. The ALTER USER privilege is required for ordinary users. However, if the user is the user_identifier user, they can alter their own definition without needing this privilege.

The following is the syntax for altering the user definition:

<alter user statement> ::=
      ALTER USER user_identifier <alter user action>
    | ALTER USER PUBLIC <alter schema path>
    ;

<alter user action> ::=
      <alter password>
    | <alter profile>
    | <alter default tablespace>
    | <alter temporary tablespace>
    | <alter index tablespace>
    | <alter schema path>

<alter password> ::=
    IDENTIFIED BY new_password [ REPLACE old_password ]

<alter profile> ::=
    PROFILE { profile_name | DEFAULT | NULL }

<password expire> ::=
    PASSWORD EXPIRE

<account lock> ::=
    ACCOUNT { LOCK | UNLOCK }

<alter default tablespace> ::=
    DEFAULT TABLESPACE tablespace_name

<alter temporary tablespace> ::=
    TEMPORARY TABLESPACE tablespace_name

<alter index tablespace> ::=
    INDEX TABLESPACE { tablespace_name | NULL }

<alter schema path> ::=
    SCHEMA PATH ( { schema_name | CURRENT PATH } [, ...] )

The following are the syntax rules and parameters for altering a user:

• user_identifier

  • It is the username to be altered.

• <alter password>

  • It alters the user's password.

  • IDENTIFIED BY new_password

    • It changes the current password, storing the new password in an encrypted form.

    • The length of the password must be less than 128 bytes.

    • Passwords are case-sensitive.

  • REPLACE old_password

    • If a user has the ALTER USER ON DATABASE privilege, they can omit this clause.

    • If a user does not have the ALTER USER ON DATABASE privilege, they can not omit this clause.

    • The user must match the user_identifier.

• <alter profile>

  • It alters the profile for password management policies.

  • PROFILE profile_name

    • It assigns the profile_name created by a user.

  • PROFILE DEFAULT

    • It assigns the "DEFAULT", which is the default profile.

  • PROFILE NULL

    • It does not assign a profile.

• <password expire>

  • It sets the user's password to expire.

• <account lock>

  • ACCOUNT LOCK

    • It locks the user account.

  • ACCOUNT UNLOCK

    • It unlocks the account.

• <alter default tablespace>

  • It alters the user's default tablespace.

  • tablespace_name must be a data tablespace.

• <alter temporary tablespace>

  • It alters the user's temporary tablespace.

  • tablespace_name must be a temporary tablespace.

• <alter index tablespace>

  • It alters the index tablespace for the user.

  • It assigns INDEX TABLESPACE tablespace_name

    • If assigned a data tablespace, it becomes a LOGGING index.

    • If assigned a temporary tablespace, it becomes a NOLOGGING index.

  • INDEX TABLESPACE NULL

    • It does not assign an index tablespace.

GOLDILOCKS Property

GOLDILOCKS properties are classified into those applied during database creation and those that can be updated while the database is online or offline. A user can change the tablespace path and redo log file only during the MOUNT phase of startup.

Properties Applied During Database Creation

Properties Applied During Database Startup

There are over 100 properties in the GOLDILOCKS database. The following are some of the most frequently used properties:

GOLDILOCKS Utility

gcreatedb

The gcreatedb utility initializes the GOLDILOCKS database and prepares it for the service. It generates data files and log files in the location specified by the GOLDILOCKS_DATA environment variable, based on the provided properties. The following is the syntax:

[SHELL]> gcreatedb --help
Usage 

    gcreatedb [options]

Options:

    --cluster                 cluster system (if not specified, stand-alone system)
    --db_name                 database name
    --db_comment              database comment
    --timezone                timezone ( {+/-}{TZH:TZM} )
    --character_set           character set
                                SQL_ASCII
                                UTF8
                                UHC
                                GB18030
    --char_length_units       char length units
                                OCTETS
                                CHARACTERS
    --member                  local cluster member name
    --host                    cluster ip address or host name
    --port                    cluster port number
    --silent                  suppresses the display of the result message
    --help                    print help message

examples:

    gcreatedb --db_name="goldilocks" --db_comment="goldilocks database" --timezone="+09:00" --character_set="UTF8" --char_length_units="OCTETS" --silent

When creating the database, tablespace files are generated in the SYSTEM_TABLESPACE_DIR path specified in goldilocks.properties.conf, with each file sized according to the ***_TABLESPACE_SIZE values referenced from $GOLDILOCKS_HOME/conf/goldilocks.properties.conf.

The following are the execution arguments for the gcreatedb command:

gsql (GOLDILOCKS Interactive SQL Tool)

gsql is an interactive command line utility used for executing SQL statements to manage the GOLDILOCKS database. It allows database administrators (DBAs) to create initial table schemas and check the current state of the database.

The following is the gsql syntax.

[SHELL]> gsql <userid> <passwd>

gSQL> CREATE TABLE T1 ( COL1 INTEGER );

create success

gSQL> \q

[SHELL]>

gloader (GOLDILOCKS Data Upload/download Tool)

The gloader utility is used to download existing data from a database into a text file, or it uploads an existing data in text format to a new database. The text data file format of gloader is Comma-Separated Value (CSV).

The following is how to use gloader.

gloader [export|import] userid/passwd control='control_file_name' \
data='data_file_name' log='log_file_name' bad='bad_file_name'

The following is an example of a control file.

TABLE  TEST_TBL
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'