Managing GOLDILOCKS Instance

This chapter describes the basic knowledge of managing the GOLDILOCKS instance.

Overview

GOLDILOCKS database system consists of database and instance. Database is a collection of various files which are necessary for driving database such as dictionary data on memory, user data, data file for dictionary data and user data, online redo files.

Database instance consists of two parts. One is the memory portions containing the run-time information for operating GOLDILOCKS database, and the other is the background process which is used to operate and manage it. Each database instance is identified as shared memory key value and "GOLDILOCKS _DATA" environment variable value, both 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. This chapter describes the main properties such as TBS (Tablespace), LOG, CONTROL FILE.

The followings describe main property items of when installing GOLDILOCKS.

A user can change 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 settings or instance settings. In the priority, the property file takes precedence over the environment variable.

Background Process

GOLDILOCKS has a background process (gmaster) for managing instance. gmaster consists of multiple system thread internally, and the contents are as follows.

Client Process

Client/ Server Model

The Client/ Server (C/S) model application is connected to listener (glsnr) which is waiting for access request. Then it creates a new database service process (gserver), in dedicated mode, and it handles user's request by using TCP communication. All these operations are carried out through inter process communication, so any signal 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 Client/ Server (C/S) model. All applications using D/A model are linked to the server library supported by GOLDILOCKS, and then directly access database and instance. Therefore, no other special service process exists but only the application processes does exist.

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

1. The signal handler marks an abnormal termination on the session object and terminates the process.

2. The cleanup thread of gmaster return resources to the database in the same way as C/S model after a certain period of time.

Application processes directly access the database area in D/A model. Therefore, comply with the following precautions.

• When the D/A model application process is terminated by a fatal signal such as SEGV, the signal handler which was registered by the library at connection to the database should stop using shared resources. If a specific signal handler should be installed in the application it should be declared before connecting to the database.

• When forcibly shut down the application process, do not use SIGKILL (kill-9) because the process can not detect the generation of the signal. A user should use SIGTERM, SIGQUIT or SIGUSR2.

Memory Architecture of Instance

The memory size used by the database instance is determined by the relevant properties in the property file. Shared memory used by instance can be divided into static area and tablespace area. Static area includes basic information about public instance, each session, statement, transaction, redo log buffer, dictionary cache and several other operation. Tablespace area includes page frame of each tablespace and Page Control Header (PCH) for controlling them.

The application process memory includes instance memory attached at connection. Additionally, it includes process basis sharing ODBC environment, several ODBC handles, heap memory area with bind information.

Startup and Shutdown Instance

To startup the GOLDILOCKS instance, set the SHARED_MEMORY_STATIC_KEY property differently from other instances. After that, a user can startup the GOLDILOCKS instance by using gsql. Execute it to take sysdba role as follows.

A user should run listener before startup or shutdown the GOLDILOCKS instance in dedicated mode of C/S model.
A user can not startup or shutdown the GOLDILOCKS instance in shared mode of 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 a managing daemon of the GOLDILOCKS instance.

• MOUNT

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

• OPEN

  • After loading the tablespace contents from data file, it recovers by using redo log, rebuilds no-logging indexes, creates dictionary cache, and then waits for the user's service connection.

A user can start up the GOLDILOCKS instance by using gsql as follows.

gSQL> ＼startup nomount
Startup success

gSQL> alter system mount database;
System altered.

gSQL> alter system open database;
System altered.

To directly enter into OPEN phase, do as follows.

gSQL> ＼startup open
Startup success

If GOLDILOCKS instance is shut down, gmaster (the daemon process for management) would be terminated. Then, connection and database operation is no longer possible.

There are four ways to shutdown the GOLDILOCKS instance as follows.

• NORMAL

  • After blocking the access of a new session and waiting until the end of all connected sessions, a user perform a checkpoint and shuts down the instance.

• TRANSACTIONAL

  • After blocking the start of a new transaction and waiting until the end of all running transactions, a user performs a checkpoint and shuts down the instance.

• IMMEDIATE

  • After blocking the execution of a new unit operation (Connection unit with GOLDILOCKS database. e.g. FETCH or EXECUTE, etc.) and waiting until the end of all unit operations, a user rolls back all transactions, performs a checkpoint and shuts down the instance.

• ABORT

  • Regardless of any connected session's status, a user terminates gmaster and shuts down the instance.

To shutdown an instance, use gsql with sysdba role and perform \shutdown, 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 the service in the client/ server environment.

A user can start the listener as follows.

% glsnr --start
 
Listener is started successfully.

%

A user can end 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 cluster system refer to Start and End of Cluster System.

Installing GOLDILOCKS and Creating Database

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

Overview

GOLDILOCKS  software is a compressed file with the name such as goldilocks-<version_no>-<os_type>-<cpu_type>.tar.gz. After decompressing the file, software binaries, various samples, fundamental database directory structures are created at the corresponding location, and then installation is completed. After the installation, the directory is created, and the directory is named after the package.  Then directories whose names 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 a utility called gcreatedb in $GOLDILOCKS_HOME/bin directory to create a database in $GOLDILOCKS _DATA directory.

Release Platform

GOLDILOCKS is available in the following release platform.

[1]The user should install libnsl separately in CentOS 8, RHEL 8 or higher.

System Requirements

Check the following requirements before installing GOLDILOCKS.

• At least 2 G should be secured at the physical memory and disk space.

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

• The correct package version which fits into the platform to be installed is required.

GOLDILOCKS Package Configuration

This chapter describes the directory configuration when installing GOLDILOCKS.

Package Directory Configuration

Package File List

The followings are description of files in a directory, and whether it is included in server package or client package.

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

Installing GOLDILOCKS Software

This chapter describes the operating system and the environment setting before installing GOLDILOCKS.

Kernel Parameters

Shared Memory

Shared memory is a type of Inter Process Communication (IPC). It is a memory which is used for sharing data in multiple programs. GOLDILOCKS uses shared memory with user programs using gsql, gloader, ODBC for Client/ Server (C/S) environment. Because all tablespaces for operation are created in shared memory, the precise parameter setting is required.

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

The following is an example of setting shmall when the total size of tablespaces is 32 GB.

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

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

8388609 = (34359738368 / 4096) + 1

• In this case, the value of shmall should be bigger than 8388609.

Semaphore

Semaphore is a kind of IPC, like as shared memory, and it is a technology to control multiple processes' behavior using the resources from the operating system. Depending on semaphore setting, multiple processes can simultaneously refer to a relevant resource, and when any process is in use, the other process may wait until it stops using the resource.

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 followings are recommended semaphore values to install GOLDILOCKS.

In Linux based system such as Redhat, Ubuntu, if a user creating IPC resource logs out the session list managed by systemd, then the corresponding IPC resource is automatically deleted. Therefore, the system should be set as follows to prevent deleting the semaphore. (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

• Modify it, then execute it.

# systemctl restart systemd-logind

Network

The backlog means the sockets' queue length waiting to be accepted during the TCP socket listen. Set glsnr's backlog in GOLDILOCKS as glsnr config file's BACKLOG. If the backlog's maximum value in system is bigger than somaxconn, then it sets to somaxconn. In this case, somaxconn should be extended.

GOLDILOCKS uses Unix Domain Socket (UDS) queue when it operates in C/S shared mode. The queue length is set to max_dgram_qlen. If the value is small when clients access the network simultaneously, then it leads to bottleneck state of communication among glsnr, gbalancer and gdispatcher.

The followings are recommended network values to install GOLDILOCKS.

Applying Parameters

For a one-time execution, a user can do as follows. (It is required to reapply when the user restarts the system).

[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 it automatically even when the user restarts the system, the user can do 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 changes given above.

[SHELL]> sysctl  -p

Checking 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

GOLDILOCKS package is supplied in a compressed form. The basic installation completes by decompression.

The followings 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 completes, a user can change the directory name <package_file_name> on the user's taste, and accordingly the user should change the environment variables of $GOLDILOCKS _HOME and $GOLDILOCKS _DATA.

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

Setting Enviromment Variables

After decompression of GOLDILOCKS package, bin and lib path will be created under $GOLDILOCKS _HOME directory. Then as given below, a user should add bin and lib path under PATH and LD__LIBRARY_PATH to execute GOLDILOCKS software and develop applications. (When developing GOLDILOCKS client application, a user should always insert $GOLDILOCKS_HOME/include to include file directory of compile option.)

export PATH=$GOLDILOCKS _HOME/bin:$PATH
export LD_LIBRARY_PATH=$GOLDILOCKS _HOME/lib:$LD_LIBRARY_PATH

Environment variables are required to use GOLDILOCKS. A user should set them prior to installation because some variables are referenced to even during installation.

##  $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

Delete datafile, control file, redo log file and archive log file (when using the archive log file) should be deleted when deleting the existing database to recreate the GOLDILOCKS DATABASE.

The following is an example of deleting 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 file and archive file may vary depending on the settings by a user.

Deletion

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

The following is an example of deleting 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 database after the completion of property creation. A user can create database by using $GOLDILOCKS_HOME/bin/gcreatedb. The followings are 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
    --home           home directory
    --member         local member name
    --host           host address
    --port           host port
    --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

$GOLDILOCKS_HOME/conf/goldilocks.properties.conf is referenced when creating database. Then, tablespace files are created in SYSTEM_TABLESPACE_DIR path in goldilocks.properties.conf with the value of ***_TABLESPACE_SIZE.

--cluster option should be specified when creating the database which is to participate in cluster system.

The followings are execution arguments of gcreatedb.

A user should consider the followings when creating database.

• Setting kernel parameter shared memory

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

• Tablespace size

  • Tablespace files are created when the database is created, and the tablespace is used after being allocated to the memory as big as the tablespace size when driving GOLDILOCKS. Therefore, it uses memory even when the actual user data does not exist in the data tablespace. Therefore, a user should create database with the sufficient memory considering the actually available memory as well as shared memory on GOLDILOCKS startup machine when writing goldilocks.properties.conf.

• Whether to use cluster system

  • It should be specified that whether the database will participate as a member of cluster system when creating the database. In other words, --cluster option should be omitted when executing gcreatedb using the database as standalone, but --cluster option should be specified when using the database as cluster system.

When database is created successfully, a user can check the following tablespace file 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 database to be used in 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 get the system and object information.

• DICTIONARY_SCHEMA: It consists of views and tables to get object information such as DBA_*, ALL_*, USER_* .

• INFORMATION_SCHEMA: It consists of views and tables included in the SQL standard INFORMATION_SCHEMA.

• PERFORMANCE_VIEW_SCHEMA: It consists of views to get system information by combining the fixed tables' information.

Views and tables included in each schemas provides convenience to get system information.

After driving GOLDILOCKS instance on OPEN phase, a user should execute the sql files as follows. It should be done at least once after the first database creation.

The following describes how to build the information by using the script for 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 information by using the script for 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

Managing Database Memory Structure

This chapter describes the database components which compose GOLDILOCKS instance.

Database Memory Structure

GOLDILOCKS database is divided into memory area and disk area. Memory area is a collection of tablespace consisting of one or more shared memories. Owe to its in-memory database, GOLDILOCKS database never goes down to the disk by replace operation.

Disk area consists of data files, control file, property file, online redo log files. Data file exists one per shared memory of each tablespace, and control file contains instance configuration information. property file stores instance environment settings, and online redo log file is used to recover database.

Control File

Control file records the physically stored information on the disk of database, and determines the status of database by firstly reading at the beginning of Instance startup. The control file records 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 of each tablespace

Online Redo Log File

Online redo log files store all changes made to the database by transactions in instances. It is used to recover unwritten changes on the data file when restarting instance after database's abnormal termination. Four online redo log files are generated by default in the size specified in LOG_FILE_SIZE property when creating database. A user can add more if the user need. The redo log files are reused in circulation manner.

Checkpoint are generated when online redo log file switches to the next file, and then some dirty pages move down to an appropriate data file. If the checkpoint operation is delayed and the updated page does not move down (ACTIVE state), all transactions will be suspended until checkpoint completion. Therefore, creating a suitable size online redo log file according to the application's characteristic is helpful to improve the database system performance.

Undo Segment

Undo segments records images in advance of changing operation to use when transactions partially or totally rollback. A single undo segment is assigned to a single transaction during update operation. Undo segments are stored in MEM_UNDO_TBS tablespace. It is recommended to secure enough undo tablespace, in preparation for multiple update transactions or a single transaction with large amount of update operation (bulk delete).

Data File

Data file includes the contents of tables/indexes stored in tablespace.

Data file consists of the followings.

• Page

  • The minimum unit of database I/O. The current size is 8 Kbytes.

• Extent

  • A certain number of continuous pages' collection. The minimum unit of which the segment is allocated space from the tablespace.

  • Extent size of each tablespace could be different.

• Segment

  • A specific type of data structure's collection. A segment consists of extent sets.

Exceptionally, a temporary tablespace, such as MEM_TEMP_TBS tablespace, does not execute redo logging, nor does data file create.

Tablespace

Database is divided into tablespaces which is a logical structure containing tables and indexes. GOLDILOCKS tablespace is classified into the memory tablespace and the disk tablespace. In the memory tablespace, a separate disk I/O does not occur when the shared memory is created per each data file in the tablespace and accesses to the page. However, in the disk tablespace, the buffer cache of the system is used to access the page. The information about the tablespace existing in the current database can be output by retrieving V$TABLESPACE table.

GOLDILOCKS supports the following tablespaces by default.

Tablespace Types

There are five types of tablespace as follows.

• DICT

  • Tablespace type for storing dictionary tables and indexes.

• DATA

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

  • It becomes the subject of redo logging and page flushing.

• UNDO

  • Tablespace type for storing undo segments.

  • It becomes the subject of redo logging and page flushing.

• TEMPORARY

  • Tablespace type for storing the temporary tables and no-logging indexes which are created during SELECT query.

  • It is not the subject of redo logging and page flushing.

• TRANSACTION (Cluster only)

  • Tablespace type which is used to recover global transaction in cluster system

Checking Information of Database Storage Structure

This chapter describes a method to check the information about multiple database storage structure which are mentioned above.

Control File Information

Use gdump utility to check the contents because 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

Retrieve the control file by using gdump tool, then the data files in each tablespaces and its states will be displayed. Also, viewing the V$DATAFILE table, their current states will be displayed.

Tablespace Information

Retrieve the control file by using gdump tool, then the name and state of each tablespace in the current database will be displayed. Also, a user can enquire the V$TABLESPACE table by using SQL.

Property Information

Open the text file $GOLDILOCKS_Data/conf/goldilocks.properties.conf, then the property information will be displayed. When working online, retrieve V$PROPERTY table, then the information about currently applied property values will be displayed.

General Operation of Data Storage

A tablespace stores data, and its operation is as follows.

Creating Tablespace

The memory and the disk USER DATA tablespaces are created as follows.

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.

TEMPORARY tablespace does not include 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 State

A tablespaces space is allocated or deallocated in the unit of one extent consisting of one or more consecutive pages. A user can retrieve the size of one extent (BYTE) in a specific tablespace as follows.

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.

Seeing the result above, the empty space in TEST_TBS is 38 * 262144 = 9437184 Byte.

Altering Tablespace

A user can alter the tablespaces by using Add/Remove Data File (Memory in case of temporary tablespaces), and Online/Offline.

Add/ Drop Data File (or Memory)

If a user wants to add spaces to tablespaces while operating the database, the DATA tablespaces allocate the additional space by using the following syntax.

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

Tablespace altered.

TEMPORARY tablespace adds spaces as follows. Unlike DATA tablespace, a name should be given, and the name should be a unique memory name in database.

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

Tablespace altered.

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

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

Tablespace altered.

Similarly, a user can withdraw the space in TEMPORARY tablespace by 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 a user wants to move the location of data file in the tablespace. Use the following syntax.

gSQL> ALTER TABLESPACE TEST_TBS OFFLINE;

Tablespace altered.

A user can switch the tablespace to online mode again by 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 the disk tablespace is created in the initial size, and it is automatically extended to the maximum size when it is needed. The automatic expand property can be on or off as follows. The automatic expand size and the maximum size of the data file can be altered when altering the automatic expand property.

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 by using the following syntax.

gSQL> ALTER TABLESPACE TEST_TBS RENAME TO TEST_TBS2;

Tablespace altered.

Switch the tablespace to offiline mode, if a user wants to change the location of data file in the tablespace. Then, move the data file by using OS command, rename it by using ALTER TABLESPACE statement, then switch the tablespace to online state.

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 the file by using 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 by using the following syntax. The statement after INCLUDING is optional, but if the statement is given then it will delete all the content (schema object) and data file in the tablespaces.

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 circumstance. Store mode defines which part of ACID property to give up to improve the operation performance in the transaction.

GOLDILOCKS supports two types of store modes.

• Transactional Data Store (TDS) mode

  • TDS mode is default store mode of GOLDILOCKS database.

  • Transactional Data Store (TDS) mode is general DBMS store mode. In this mode, all transactions in the instance write both undo log and redo log. Therefore, a user can rollback the transactions and recover using data file and online redo log file even when an instance is terminated abnormally, because periodical checkpoint are performed.

• Concurrent Data Store (CDS) mode

  • All running transactions in the instance write undo logs, but do not write redo logs. Therefore, the transactions can deal with all run-time errors, and can rollback. But if an instance is terminated abnormally or terminated using shutdown abort statement, all updated data would be lost. (It does not provide recover facility). It is because checkpoint action does not occur. CDS Mode controls concurrency among transactions, so it ensures normal operations of transactions even when different transactions access the same object at the same time.

  • CDC Mode is mainly used in run-time information oriented database in which query/update operations are frequently performed but does not require durability such as cache server.

Store mode is set through the property setting when a user starts up the instance. Transactions can not be executed in different store modes. The user should carefully set the store mode when the user starts up the instance because the user can not change the instance in online mode.

Managing Schema Object

Schema Object

Schema object is a set of logical structure created by a user. GOLDILOCKS supports the following schema objects, which are table, index, synonym, view, sequence, constraint and stored procedure.

Schema Object Management Privileges

Currently, GOLDILOCKS supports user and his privilege. Therefore, not all the users share all the created objects, so the privilege should be given to a user.

Managing Table

This chapter describes table overview, methods of retrieving the table information, creating/ altering table and loading/ dropping data.

Table

A table is the most basic unit of storage containing user data. A table consists of columns and rows.

Table Type

Currently, GOLDILOCKS supports general heap table whose data saving order is irrelevant to sort order of a particular column. However, GOLDILOCKS does not support clustered table, partitioned table.

• It supports basic heap table only.

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

• It supports the datatypes as follows.

  • 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 BLOB type.

• The number of columns, indexes and constraints are not limited.

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

If a huge number of rows are stored in a particular table, and then the table size becomes big, even bulk delete operation does not return the table's empty space to the tablespace. But TRUNCATE operation can return all existing space to the tablespace.

Table data can be stored and retrieved being distributed to multiple nodes according to the user's desiring distribution policy when using GOLDILOCKS configuring it as cluster system. For more information, refer to Managing Table

Managing Index

This chapter describes index overview and creating/deleting index.

Overview

Index is a subsidiary schema object which is linked to tables. A user can easily find the location of specifically conditioned row using the index. A user can also retrieve the row's column value if the column is the key column of the index.

GOLDILOCKS can create as many indexes in need to tables. However, too many indexes burden the execution of inserting/ changing/ deleting operation of the table, then it may lower the performance.

Primary key or unique constraint automatically creates an index on that column.

Index Property

• It supports B-link tree form index.

  • GOLDILOCKS provides B-link tree form index by default.

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

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

• It supports unique index.

• It supports Ascending/Descending, NULLS FIRST and NULLS LAST.

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

  • A user can define whether to list NULL value of the index key column at first (NULLS FIRST) or at last (NULLS LAST).

• A user can retrieve the whole index information by using the query SELECT * FROM INDEXES;.

• A user can calculate the index size in the similar way of calculating table because index is implemented by segment in the same way of implementing table.

Sequence

Sequence is a schema object which generates a unique number.

A user can generate the 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 using NEXTVAL.

gSQL> SELECT customers_seq.NEXTVAL FROM dual;

A user can drop the sequence as follows.

gSQL> DROP SEQUENCE customers_seq;

Global sequence object is automatically created when user creates sequence by using GOLDILOCKS configuring cluster system. This object manages the global pool of the sequence value which is used being shared by all member nodes in cluster system. Each member node is allocated the sequence value as many as specified from the global object when calling NEXTVAL and uses them.
For more information, refer to Global Sequence.

Managing User

Creating User

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

The followings are the syntax to create a user.

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

The followings are the syntax rules and parameters to create a user.

• user_identifier

  • It is a username to be created.

  • The username and the role name should be unique.

  • The length of a user_identifier should be smaller than 128 bytes.

• password

  • The password is stored encrypted.

  • The length of a password should be smaller than 128 bytes.

  • Password is case-sensitive.

• DEFAULT TABLESPACE tablespace_name

  • It specifies the default tablespace which stores the objects such as tables, indexes (LOGGING) created by a user which is to be created.

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

• TEMPORARY TABLESPACE tablespace_name

  • It specifies the TABLESPACE to store user-created temporary tables, index (NO LOGGING), and query processing-generated intermediate results.

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

• INDEX TABLESPACE tablespace_name

  • It specifies the default tablespaces to store an index object created by a user to be created.

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

Dropping User

It drops the created user of GOLDILOCKS database. An access privilege and user range for dropping user are as same as those for creating user.

The following is the syntax to drop a user.

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

<drop behavior> ::=
      RESTRICT
    | CASCADE

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

• IF EXISTS

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

• user_identifier

  • It is a database username to be dropped.

  • A user can not drop a user, such as SYS, which is automatically generated when the database is created.

  • It does not drop objects, such as tablespace, which is not the owner but created by user_identifier.

• drop behavior

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

Altering User

It alters the definition of the GOLDILOCKS database user. ALTER USER privilege is required for an ordinary user. However, if a user is the user_identifier user, then the user can alter the definition without the privilege.

The followings are the syntax to alter 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 followings are the syntax rule and parameters for altering user.

• user_identifier

  • It is a username to be altered.

• <alter password>

  • It alters user password.

  • IDENTIFIED BY new_password

    • It changes the current password, and the new password is stored encrypted.

    • The length of a password should be smaller than 128 bytes.

    • Password is case-sensitive.

  • REPLACE old_password

    • If a user have ALTER USER ON DATABASE privilege, the user can omit it.

    • If a user do not have ALTER USER ON DATABASE privilege, the user can not omit it.

    • The user and user_identifier should be same.

• <alter profile>

  • It alters the profile for password management policies.

  • PROFILE profile_name

    • It allocates profile_name created by a user.

  • PROFILE DEFAULT

    • It allocates "DEFAULT" which is the default profile.

  • PROFILE NULL

    • It does not allocate the profile.

• <password expire>

  • It expires the user password.

• <account lock>

  • ACCOUNT LOCK

    • It locks the user account.

  • ACCOUNT UNLOCK

    • It unlocks the account lock.

• <alter default tablespace>

  • It alters user's default tablespace.

  • tablespace_name should be data tablespace.

• <alter temporary tablespace>

  • It alters user's temporary tablespace.

  • tablespace_name should be temporary tablespace.

• <alter index tablespace>

  • It alters the index tablespace of a user.

  • It assigns INDEX TABLESPACE tablespace_name

    • When assigning the data tablespace, it becomes the LOGGING index.

    • When assigning the temporary tablespace, it becomes the NOLOGGING index.

  • INDEX TABLESPACE NULL

    • It does not assign the index tablespace.

GOLDILOCKS Property

GOLDILOCKS property is classified as the property which is applied when creating database and the property which can be updated at online/offline. A user can change the tablespace path and the redo log file only at MOUNT phase of startup.

Properties When Creating Database

Properties When Driving Database

There are more than 100 properties in GOLDILOCKS database. The followings are frequently used properties among them.

GOLDILOCKS Utility

gcreatedb

The gcreatedb utility initializes GOLDILOCKS database and gets ready for the service. The gcreatedb generates data files and log files in the GOLDILOCKS_DATA environment variable location according to given properties. The following is a 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
    --home           home directory
    --member         local member name
    --host           host address
    --port           host port
    --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

Tablespace files are created in SYSTEM_TABLESPACE_DIR of goldilocks.properties.conf as each value of ***_TABLESPACE_SIZE by referring to $GOLDILOCKS_HOME/conf/goldilocks.properties.conf when creating the database.

The followings are execution arguments of gcreatedb command.

gsql (GOLDILOCKS Interactive SQL Tool)

gsql is an interactive command line utility to execute SQL statements for managing GOLDILOCKS database. DBA creates initial table schema by using gsql, or checks the current database state.

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 downloads existing data in database to a file in text format, 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 '"'