Managing Control File

To use the GOLDILOCKS database, a database instance must be created, which also involves creating a control file. When the GOLDILOCKS multilevel startup process transitions from the NOMOUNT phase to the MOUNT phase, the information recorded in the control file is used to determine details such as the absolute paths and sizes of the files to be used by the database. The control file is a binary file that stores the following database information.

Control File Contents

The database information includes details about the database’s operation, information on all in-use tablespaces, and data. The operational information stored in the control file includes the following.

Tablespace information stored in the control file includes the following.

Data file information stored in the control file includes the following.

It stores information about each incremental backup performed in the database. The GOLDILOCKS database supports incremental backups for both the database and individual tablespaces. This incremental backup information is recorded in the control file as follows.

Multiplexing Control File

The control file stores the physical structure of the GOLDILOCKS database and information on data consistency. If the control file becomes corrupted or is accidentally deleted, the database cannot operate.

Therefore, the GOLDILOCKS database recommends creating at least two control files and storing them on physically separate disks. GOLDILOCKS supports multiplexing up to 8 control files. To add control files when creating a database, specify the number of multiplexing control files and their paths in the property file. To add control files during database operation, increase the control file multiplexing property value and add the paths of the new control files.

Restoring Corrupted Control File

If a control file is corrupted due to an abnormal database termination, the corrupted control file is restored using one of the intact control files from the multiplexed set, and then the database is restarted.

If all multiplexed control files are corrupted, the backup control file is restored. An incomplete media recovery is then performed using archive redo log files and redo log files, followed by a restart of the database. For more information about performing incomplete recovery with the backup control file, refer to When all multiplexed control files are corrupted in the recovery examples.

Control File Information

A user can use the performance view V$CONTROLFILE to query information about the location and name of the control file. The following is an example of how to retrieve the control file names using V$CONTROLFILE.

gSQL> SELECT CONTROLFILE_NAME FROM V$CONTROLFILE;

CONTROLFILE_NAME                                         
---------------------------------------------------------
/goldilocks_data/wal/control_0.ctl
/goldilocks_data/wal/control_1.ctl

2 rows selected.

A user can retrieve accurate information from the control file using gdump, the dump tool provided by GOLDILOCKS.

Managing Redo Log File

GOLDILOCKS uses redo log files to ensure database persistency. In other words, if the GOLDILOCKS database is abnormally terminated for any reason, it can be restored to its state prior to shutdown using the redo log files and data files.

To achieve this, the GOLDILOCKS database uses the Write Ahead Logging (WAL) policy to log all database update operations.

The updated data from an update operation is not recorded directly in the data file; instead, the log for the update operation is recorded in the redo log file. This approach is more efficient in terms of database performance. Recording each update operation directly in the data file would require random access and could lead to excessive disk I/O due to multiple updates to the same file. In contrast, the update log is smaller than the updated data and is continuously appended to the end of the log file, which allows for more efficient disk I/O.

Additionally, the GOLDILOCKS database uses a log buffer in shared memory to record update logs and then writes the log buffer to the log file in batches, resulting in more efficient disk I/O.

Redo Log File Structure

The redo log buffer and log files in GOLDILOCKS have a circular structure. A predefined number of log files are created to record logs, and when one log file becomes full, the system uses the next log file. Once all log files are used, the previously used log files are reused. The log files consist of a circular structure with a single log group containing multiple members, and GOLDILOCKS performs logging using a minimum of four log groups.

Redo Log Group and Its Member

The GOLDILOCKS database uses log groups and members to record logs to the disk log file during database operation. Each redo log file is a member of a log group, and a log group consists of several log members. Having multiple members in a log group ensures high availability, as other log members can be used if a particular disk fails or if a particular log member becomes corrupted.

The system uses a circular log group structure, recording logs to one log group at a time. When that log group becomes full, the system moves to the next log group. The transition of the system from the current log group to the next one is referred to as log switching.

The number of log groups and members, as well as their positions, are set by properties during database creation. They can also be modified using ADD or DROP statements while the database is in operation.

Log Group State

A log group is initialized to the UNUSED state when created and changes to CURRENT, ACTIVE, or INACTIVE states during operation as managed by the system.

An ACTIVE log group can not be reused in the system, but it can be reused once it is changed to INACTIVE by the archive log thread. The archive log thread is triggered by an event that occurs before the checkpoint is completed, and it changes ACTIVE log groups to INACTIVE. To do this, the log archiving thread first archives the log file in the ACTIVE log group, and then changes its status to INACTIVE if the system is operating in ARCHIVELOG mode. If the system is operating in NOARCHIVELOG mode, the ACTIVE log group becomes immediately reusable once its status is changed to INACTIVE.

Adding Log Group and Log Member

Adding Log Group

Adding a log group is allowed only during the mount phase of the GOLDILOCKS database multilevel startup. The new log group is added next to the CURRENT log group that is currently being used.
The following is an example to add a new log group with the file name 'abc.log', and a file size of 20 Mbytes to group ID 10.

ALTER DATABASE ADD LOGFILE GROUP 10 ('abc.log') SIZE 20M;

Adding Log Member

A new log member can be added to enhance the stability of a log group in use. This can be done during the mount phase of the GOLDILOCKS database multilevel startup, similar to the process for adding a log group. The following is an example to add a log file named 'test log' to group ID 10. The log file size is not specified because all log members within a log group have the same size.

ALTER DATABASE ADD LOGFILE MEMBER 'test.log' TO GROUP 10;

Altering Log Member Name

The position and file name of a log member in use can be altered by executing the RENAME command during the mount phase. RENAME is executed when a log member's disk has physically failed or when the log member needs to be transferred to another disk for performance reasons.
The following is an example to RENAME the log file from '/disk1/goldilocks_data/wal/redo_0_0.log' to '/disk2/goldilocks_data/wal/redo_0_0.log'.

ALTER DATABASE RENAME LOGFILE '/disk1/GOLDILOCKS_data/wal/redo_0_0.log' TO '/disk2/GOLDILOCKS_data/wal/redo_0_0.log';

Dropping Log Group or Log Member

A log member or log group in use can be dropped during the mount phase if a user wants to reduce log groups or log members or if a log file's disk has failed.
The following is an example to drop all members of log group ID 10.

ALTER DATABASE DROP LOGFILE GROUP 10;

Dropping a log member is only allowed when at least two members exist in the log group.
The following is an example to drop the log member '/disk1/goldilocks_data/wal/redo_0_0.log'.

ALTER DATABASE DROP LOGFILE MEMBER '/disk1/GOLDILOCKS_data/wal/redo_0_0.log';

Restoring Corrupted Redo Log File

If the system fails and the redo log files become corrupted, restart recovery will fail, and the system can not be operated. However, if there are normal log members present in the corrupted log group, you can recover the system by copying the log file from a normal log member to replace the corrupted log files. This allows restart recovery and system operation to proceed.

At this time, the log file that contains the log recorded at the ON_DISK_LSN in V$CONTROLFILE of the control file must exist in order to complete the recovery. If the log file does not exist, the recovery cannot be completed and the database can only be restarted after performing incomplete recovery.

If all log files in a log group are corrupted or if there is only one log member, the system can be restarted by performing incomplete media recovery. The incomplete media recovery is limited to normal log files.
For more information about incomplete media recovery, refer to Incomplete Recovery.

Redo Log File Information

A user can query the V$LOGFILE view, which is a performance view to retrieve the location and name of the redo log files. When querying V$LOGFILE, information such as the log file name, its log group ID, its state, and the file size is retrieved as follows.

gSQL> SELECT FILE_NAME, GROUP_ID, GROUP_STATE, FILE_SIZE FROM V$LOGFILE;

FILE_NAME                          GROUP_ID GROUP_STATE FILE_SIZE
---------------------------------- -------- ----------- ---------
/disk1/goldilocks_data/wal/redo_0_0.log        0 INACTIVE    104857600
/disk1/goldilocks_data/wal/redo_1_0.log        1 CURRENT     104857600
/disk1/goldilocks_data/wal/redo_2_0.log        2 UNUSED      104857600
/disk1/goldilocks_data/wal/redo_3_0.log        3 UNUSED      104857600

4 rows selected.

Managing Archive Redo Log File

GOLDILOCKS redo log files reuse log groups that have been used, employing a circular log group structure. Therefore, for media recovery using backups, the database must be operated in archive log mode. In this mode, completed redo log files are copied to archive redo log files.
For more information about the archive log mode in the GOLDILOCKS database, refer to ARCHIVELOG Mode.

Creating Archive Redo Log File

The redo log file is copied to the archive redo log file directory by the log archiving thread, which is a system thread in the GOLDILOCKS database. The log archiving thread is activated by the checkpoint thread during a checkpoint, and it archives the appropriate redo log files.

The archive redo log file is created in the directory specified by the ARCHIVELOG_DIR_ 1 property. The name of the archive redo log file is composed of a prefix set by the ARCHIVELOG_FILE property and a sequence number corresponding to each redo log file.

Maintaining and Dropping Archive Redo Log File

Media recovery using backups can fail if archive redo log files are arbitrarily dropped, just as with redo log files. Therefore, archive redo log files must be preserved along with backup files. When backup files are no longer needed, the corresponding archive redo log files required for media recovery using the backup can also be dropped.

A backup involves copying the data file that was downloaded to disk by the most recent checkpoint. Therefore, to recover media using a backup, you need the archive log files starting from the oldest LSN and later. A checkpoint occurs whenever a redo log file is switched, so one or more checkpoint logs are present in every log file except for the redo log file in the CURRENT state.

The following describes how to obtain the archive redo log files required for media recovery using a backup file:

1. Retrieve the checkpoint LSN recorded in the file header of the backup data files.

2. Identify the archive redo log files and locate the file that includes the checkpoint LSN.

3. The archive redo log files required for media recovery using the backup start from just before the archive redo log file identified in step 2.

Dump the control file and get the checkpoint LSN to get the archive redo log file needed for incremental backup. Subsequent process is the same as the entire backup process.

If backup files are no longer needed, the corresponding archive redo log files required for media recovery using those backups can also be deleted.

Multiplexing Archive Redo Log File Directory

If an archive redo log file is moved from the directory specified by ARCHIVELOG_DIR_1 to another directory or media for preservation, media recovery may fail because the system will not be able to locate the required archive redo log file.

In this case, you can either move the relocated archive redo log file back to the directory specified by ARCHIVELOG_DIR_1 to proceed, or configure additional directories for the archive redo log files by setting ARCHIVELOG_DIR_2 through ARCHIVELOG_DIR_10. Ensure that READABLE_ARCHIVELOG_DIR_COUNT is set to the number of directories used (ARCHIVELOG_DIR_2 through ARCHIVELOG_DIR_10) to properly support media recovery.

Managing Tablespace

All data used by the database is stored in physical disk files, with the database's logical structure employed for efficient data management and performance. GOLDILOCKS efficiently manages disk space using logical structures such as tablespaces, segments, extents, and pages.

A tablespace can include multiple data files, and each tablespace can be set to online or offline to support high data availability. Additionally, distributing the disks that store data files enhances I/O performance and reduces contention for physical disk I/O.

Tablespace Type

In GOLDILOCKS, tablespaces are categorized into SYSTEM tablespaces and non-SYSTEM tablespaces. The SYSTEM tablespace is created during database creation and is used and managed exclusively by the GOLDILOCKS system. Non-SYSTEM tablespaces, on the other hand, are created and utilized by users.

SYSTEM Tablespace

The SYSTEM tablespace is created when the GOLDILOCKS database is initialized and is essential for database operation. It includes the dictionary tablespace, undo tablespace, and system temporary tablespace.

Non-SYSTEM Tablespace

Non-SYSTEM tablespaces are used to store tables and indexes for data storage, and users can create or delete these tablespaces as needed.

Managing Tablespace and Data File

Managing Tablespace

Managing Tablespace State

The GOLDILOCKS database tablespace can be in either an online or offline state. An offline tablespace is not accessible. A user can set the tablespace to offline arbitrarily, or the system sets an abnormal tablespace to offline. However, the system tablespace must not be set to offline.

• Offline tablespace

The GOLDILOCKS database flushes all data files in the tablespace to disk before the tablespace is set to offline. Since flushing the data files requires all related logs to be flushed to disk as well, there is no need for special recovery when the tablespace later transitions back to online. However, any DDL operations that occurred while the tablespace was offline are applied when the tablespace state is changed to online.

A tablespace can be set to offline immediately using the IMMEDIATE mode, without flushing the data files. In this case, the tablespace state will be changed to online only after performing media recovery when the tablespace is set back to online.

GOLDILOCKS can set a tablespace to offline during the mount phase. To do this, the service must be normally terminated, or the server must be operated in ARCHIVELOG mode. This method provides high availability by excluding unrecoverable tablespaces during database startup.

gSQL> ALTER TABLESPACE TEST_TBS OFFLINE;
 
Tablespace altered.
 
gSQL> ALTER TABLESPACE TEST_TBS ONLINE;
 
Tablespace altered.

gSQL> ALTER TABLESPACE TEST_TBS OFFLINE IMMEDIATE;
 
Tablespace altered.
 
gSQL> ALTER TABLESPACE TEST_TBS ONLINE;
 
ERR-42000(14051): media recovery required - 'TEST_TBS'
 
gSQL> ALTER DATABASE RECOVER TABLESPACE TEST_TBS;
 
Database altered.
 
gSQL> ALTER TABLESPACE TEST_TBS ONLINE;
 
Tablespace altered.

Attributes of Tablespace

The following are attributes of a tablespace in the GOLDILOCKS database. PERSISTENT or TEMPORARY indicates whether the persistence of the tablespace is ensured. DATA or UNDO specifies the type of tablespace and the kind of data it stores.

Managing Tablespace

• Creating user tablespace

It creates a new user tablespace. The name of the tablespace used in the database instance must be unique when creating the tablespace. A tablespace can contain up to 1024 data files. In a memory tablespace, each data file can store up to 30 Gbyte, while in a disk tablespace, the data file can store as much data as the physical disk allows. The database can create up to 65,535 tablespaces, including the system tablespace.

The name of each data file, including its absolute path, must be unique. Use the 'REUSE' option to reuse an existing data file that is not currently in use by the database. The extent size of a tablespace can be selected from 64 Kbyte, 128 Kbyte, 256 Kbyte, 512 Kbyte, or 1 Mbyte, with the default extent size being 256 Kbyte.

gSQL> CREATE TABLESPACE TEST_TBS DATAFILE
     '/goldilocks1/db/TEST_TBS1.dbf' SIZE 20M,
     '/goldilocks2/db/TEST_TBS2.dbf' SIZE 50M,
     '/goldilocks3/db/TEST_TBS3.dbf' SIZE 100M REUSE;

Tablespace created.

The following is an example of creating a disk tablespace.

gSQL> CREATE DISK TABLESPACE TEST_TBS DATAFILE
     '/goldilocks1/db/TEST_DISK_TBS1.dbf' AUTOEXTEND OFF MAXSIZE 20M,
     '/goldilocks2/db/TEST_DISK_TBS2.dbf' AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED REUSE;

Tablespace created.

When creating a tablespace, you can set its state to ONLINE or OFFLINE, and also specify the LOGGING or NOLOGGING property.

• Dropping tablespace

A tablespace and its data files can be dropped when they are no longer needed. Unused tablespaces should be dropped to avoid wasting resources. This is because once a tablespace is created, the added disk data files and memory allocations are created and remain in use.

gSQL> DROP TABLESPACE TEST_TBS;

Tablespace dropped.

Dropping a tablespace does not, by default, drop the table indexes in use. Therefore, the INCLUDING CONTENTS option must be used together when dropping a tablespace that contains tables or indexes in use.

gSQL> DROP TABLESPACE TEST_TBS;

ERR-42000(16148): tablespace not empty, use INCLUDING CONTENTS option : 
drop tablespace TEST_TBS
                *
ERROR at line 1:

gSQL> DROP TABLESPACE TEST_TBS INCLUDING CONTENTS;

Tablespace dropped.

Use the AND DATAFILES option to also drop the data files added to the tablespace.

gSQL> DROP TABLESPACE TEST_TBS INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

• Altering tablespace size

To alter the size of a tablespace, you can add data files to or drop data files from the tablespace. Add new data files to the tablespace when additional storage space is needed during database operations. Conversely, drop unused data files from the tablespace to avoid wasting space.

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

Tablespace altered.

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

Tablespace altered.

A data file can only be dropped from a tablespace if it has never been used since its creation. Once a data file has been used, it cannot be dropped, even if all the data has been removed.

ALTER TABLESPACE TEST_TBS DROP DATAFILE 'TEST_TBS2.dbf';

ERR-42000(14044): datafile not empty

• Managing temporary tablespace

A temporary tablespace does not store data files, but allocates memory of a specified size. Create a temporary tablespace as follows.

gSQL> CREATE TEMPORARY TABLESPACE TEST_TBS MEMORY 'TEST_TEMP_TBS' SIZE 10M EXTSIZE 256K;

Tablespace created.

Add memory to the temporary tablespace as follows.

gSQL> ALTER TABLESPACE TEST_TBS ADD MEMORY 'TEST_TBS2' SIZE 10M;

Tablespace altered.

Drop unused memory from the temporary tablespace as follows.

gSQL> ALTER TABLESPACE TEST_TBS DROP MEMORY 'TEST_TBS2';

Tablespace altered.

Drop the temporary tablespace as follows.

gSQL> DROP TABLESPACE TEST_TBS INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Transferring Data File

The data file storage path stored in the database must be modified when altering the datafile's storage disk or directory.

The following is an example to describe how to modify the path when transferring the datafile '/goldilocks1/db/TEST_TBS1.dbf' in tablespace TEST_TBS to '/goldilocks4/db/TEST_TBS1.dbf'.

gSQL> ALTER TABLESPACE TEST_TBS RENAME DATAFILE
    '/goldilocks1/db/TEST_TBS1.dbf' TO '/goldilocks4/db/TEST_TBS1.dbf';

Tablespace altered.

Tablespace Information

For more information about the tablespaces created in the database, refer to V$TABLESPACE.

gSQL> \DESC V$TABLESPACE

COLUMN_NAME   TYPE                   IS_NULLABLE
------------- ---------------------- -----------
TBS_NAME      CHARACTER VARYING(128) FALSE      
TBS_ID        NUMBER                 FALSE      
TBS_ATTR      CHARACTER VARYING(128) FALSE      
IS_LOGGING    BOOLEAN                FALSE      
IS_ONLINE     BOOLEAN                FALSE      
OFFLINE_STATE CHARACTER VARYING(32)  FALSE      
EXTENT_SIZE   NUMBER                 FALSE      
PAGE_SIZE     NUMBER                 FALSE

Managing Data File

Data File Matching

A data file can become corrupted due to disk failure, database defects, or human error. If a corrupted data file is used in the database, it can lead to serious problems.

The GOLDILOCKS database ensures data file integrity by using a checksum for each page of the data file. The page checksum is generated using LSN and CRC value, and is stored on each page. Users can set the page checksum type using the value in PAGE_CHECKSUM_TYPE, with LSN being the default value.

The page checksum is verified when loading the data file into memory at database startup. If an error occurs with the checksum value, the database service can not start.

The following is an example to describe a database startup failure when the datafile TEST_TBS.dbf in the user-created tablespace TEST_TBS does not match.

gSQL> \STARTUP MOUNT

Startup success

gSQL> ALTER SYSTEM OPEN DATABASE;

ERR-HY000(14094): datafile recovery required - datafile(/goldilocks/db/TEST_TBS.dbf) of tablespace(TEST_TBS) corrupted

If the data file does not match, set the tablespace containing the data file to OFFLINE, or recover the data file to restart the database.

The following describes how to start up the database after setting the tablespace to OFFLINE.

gSQL> \STARTUP MOUNT

Startup success

gSQL> ALTER SYSTEM OPEN DATABASE;

ERR-HY000(14094): datafile recovery required - datafile(/goldilocks/db/TEST_TBS.dbf) of tablespace(TEST_TBS) corrupted

gSQL> ALTER TABLESPACE TEST_TBS OFFLINE IMMEDIATE;

Tablespace altered.

gSQL> ALTER SYSTEM OPEN DATABASE;

System altered.

A full backup or incremental backup of the data file must exist when recovering the data file. The following describes how to recover the data file when a backup is available.

gSQL> \STARTUP MOUNT

Startup success

gSQL> ALTER SYSTEM OPEN DATABASE;

ERR-HY000(14094): datafile recovery required - datafile(/goldilocks/db/TEST_TBS.dbf) of tablespace(TEST_TBS) corrupted

gSQL> ALTER DATABASE RECOVER DATAFILE 'TEST_TBS.dbf' CORRUPTION;

Database altered.

gSQL> ALTER SYSTEM OPEN DATABASE;

System altered.

Data File Information

For more information about the data files being used in the database, refer to V$DATAFILE.

gSQL> \DESC V$DATAFILE

COLUMN_NAME    TYPE                           IS_NULLABLE
-------------- ------------------------------ -----------
TBS_NAME       CHARACTER VARYING(128)         FALSE      
DATAFILE_NAME  CHARACTER VARYING(1024)        FALSE      
CHECKPOINT_LSN NUMBER                         FALSE      
CREATION_TIME  TIMESTAMP(6) WITHOUT TIME ZONE FALSE      
FILE_SIZE      NUMBER                         FALSE

Buffer Cache

Structure of GOLDILOCKS Buffer Cache

It caches pages required from the disk data file to access index pages and tables stored in the disk tablespace into the buffer. GOLDILOCKS allocates the buffer cache based on the size set in the BUFFER_CACHE_SIZE property and retrieves requested pages from the page cached in the buffer cache using a hash table sized according to the BUFFER_HASH_BUCKETS property. The touch count is incremented each time a page is accessed. If there is no available space in the buffer cache, the LRU strategy is employed, which replaces the page with the smallest touch count value.

Buffer Cache List

The following lists are used for the buffer cache in GOLDILOCKS:

Page frames that have not been used since system startup are linked to the buffer free list, while used page frames are linked to the buffer LRU list. Page frames that are updated in the buffer cache are linked to the buffer checkpoint list, but they are not dropped from the buffer LRU list.

When all page frames in the buffer free list are used and there is no available page frame to cache a new page, the system looks for an available page frame by retrieving from the buffer LRU list. At this point, any page frames that have been updated but are not in use are moved to the buffer flush list.

A page frame cannot be redundantly linked to the buffer free, LRU, or flush lists, except for the buffer checkpoint list. In other words, a page frame in the buffer free list cannot simultaneously be in the buffer LRU or flush lists, and a page frame in the buffer LRU list cannot exist in the buffer free or flush lists.

The buffer LRU list is divided into hot/ cold areas. If the touch count of a page frame in the cold area meets or exceeds the specified value (BUFFER_HOT_REGION_CRITERIA), it is transferred to the hot area. The touch count of page frames in the buffer LRU list increases with each access. If all page frames belong to the hot area, no page frame is available for replacement. Therefore, the size of the hot area is limited by the BUFFER_HOT_REGION_PERCENT property value.

Page Frame Status

All page frames in the buffer cache have the following statuses.