ALTER DATABASE
ALTER DATABASE#
Syntax#
alter_database ::=#
startup_clause ::=, rename_datafile_clause ::=, create_datafile_clause ::=, create_checkpoint_image_clause ::= session_clause ::=, archivelog_option ::=, backup_clause ::=, incremental_backup_clause ::=, recover_clause ::=, restore_clause ::=, change_backup_directory_clause ::=, move_backup_clause ::=, delete_backup_clause ::=, change_tracking_clause ::=
startup_clause ::=#
rename_datafile_clause ::=#
create_datafile_clause ::=#
create_checkpoint_image_clause ::=#
session_clause ::=#
archivelog_option ::=#
backup_clause ::=#
incremental_backup_clause ::=#
incremental_level_clause ::=#
with_tag_clause ::=#
recover_clause ::=#
from_tag_clause ::=#
until_option ::=#
restore_clause ::=#
restore_database_clause ::=#
restore_tablespace_clause ::=#
change_backup_directory_clause ::=#
move_backup_clause ::=#
delete_backup_clause ::=#
change_tracking_clause ::=#
snapshot_clause ::=#
Prerequisites#
The ALTER DATABASE statement can only be executed in a startup phase preceding the SERVICE phase by the SYS user after connecting in SYSDBA mode. The exception is when using the SESSION CLOSE option, in which case it is not necessary to connect in SYSDBA mode in order to execute this statement.
Description#
The ALTER DATABASE statement modifies, maintains, or restores an existing database.
database_name#
This element is used to specify the name of the database to be managed.
startup_clauses#
This element is used to specify the name of the startup phase in which to start up Altibase.
CONTROL#
This option is used to start the database in the CONTROL phase. When the database is started in this phase, database media recovery can be performed. Tablespaces can also be discarded in this phase. For more detailed information about the various Altibase startup phases, please refer to the Administrator's Manual.
To proceed to the phase immediately following the CONTROL phase, the META phase, execute this statement as follows:
ALTER DATABASE dababase_name META;
META#
This option is used to start the database in the META phase. While proceeding to this phase from the previous phase, the CONTROL phase, the database meta data are loaded. To proceed to the next phase, execute the following statement:
ALTER DATABASE dababase_name SERVICE;
SERVICE#
This option is used to start the database in the SERVICE phase. When the database is started in this phase, all memory and disk tables are loaded, and extended services such as replication and SNMP can be started. If the database can be successfully started in this phase, it means that any required recovery has been performed, and that the system is in a state in which service is being provided normally.
UPGRADE#
This option is used to start the database in the META UPGRADE phase. When the database is started up to this phase, the meta data are upgraded, meaning that all recovery-related tasks have been completed.
To proceed to the next phase, execute the following statement:
ALTER DATABASE dababase_name SERVICE;
RESETLOGS#
This task is required to ensure normal startup of the server after incomplete recovery has been performed in the CONTROL phase. Logs that are no longer necessary once incomplete recovery has been performed are deleted while proceeding to this phase.
To proceed to the next phase, execute the following statement:
ALTER DATABASE dababase_name SERVICE;
META RESETUNDO#
In this phase, the SYS_TBS_DISK_UNDO tablespace is initialized, but the size of the tablespace file is not changed. Before executing this statement, check the integrity of the database, ensure that disk garbage collection has been performed, and shut down the database normally
SHUTDOWN NORMAL#
The server waits until all client connections have been disconnected normally before shutting down normally.
SHUTDOWN IMMEDIATE#
The server forcibly disconnects all clients and then shuts down normally.
SHUTDOWN EXIT#
This option is used to kill the Altibase server forcibly. When Altibase is shut down in this way, the contents of the database will likely be invalid, and thus the next time the server is executed, recovery tasks will have to be performed.
RENAME DATAFILE#
This command is used to change a reference to a data file within Altibase so that it points to a data file that has a different name or is located in a different directory. The data file specified in TO 'datafile_path' must exist. This command can only be executed in the CONTROL phase. datafile_path must be an absolute path.
For reference, the ALTER TABLESPACE statement is used to move memory tablespace checkpoint image files.
CREATE DATAFILE#
When a disk data file has been lost, this command is used to create a data file with reference to the log anchor data. After this statement is executed, complete media recovery is performed to restore the data file.
This statement is available only during the CONTROL phase.
datafile_path, which is where the data file will be created, must be an absolute path. For reference, the ALTER TABLESPACE statement is used to create memory tablespace checkpoint image files.
CREATE CHECKPOINT IMAGE#
When a memory checkpoint image file has been lost, this command is used to create a checkpoint image file with reference to the log anchor data. After this statement is executed, complete media recovery can be performed in order to restore the memory checkpoint image file.
Because the checkpoint image file is created in the checkpoint path specified for memory tablespaces, it is not necessary to specify the path; only the name of the file to be created need be provided.
This statement is available only during the CONTROL phase.
<Query> Recreate the lost checkpoint image file MEM-TBS-1.
iSQL> ALTER DATABASE CREATE CHECKPOINT IMAGE 'MEM-TBS-1';
SESSION CLOSE#
This statement is used to forcibly terminate a session.
This statement can specify the session ID and user to terminate a session, and all sessions can be terminated with ALL statement. However, the session accessed by the current user cannot be terminated. If this statement is executed, a transaction of the session is rolled back.
Note: It is impossible to terminate a session immediately if the session is waiting to obtain a lock.
archivelog_option#
The database can be switched between archivelog mode and noarchivelog mode in the CONTROL phase.
BACKUP LOGANCHOR#
When the database is operating in archivelog mode, this statement is used to back up log anchor files online without interrupting service.
BACKUP TABLESPACE#
When the database is operating in archivelog mode, this statement is used to back up the specified tablespace to the backup directory without interrupting service.
BACKUP DATABASE#
When the database is operating in archivelog mode, this statement is used to back up all memory and disk tablespaces and log anchor files without interrupting service.
incremental_backup_clause#
This incrementally backs up the whole database or certain tablespaces.
incremental_level_clause#
This specifies the incremental backup level.
WITH TAG tag_name#
This specifies a tag name for a backup.
RECOVER DATABASE#
This is used to perform complete media recovery. The log files in the archive log directory are read for use in recovering the data files that were affected by media errors to the current point in time.
FROM TAG tag_name#
This specifies a database restoration or recovery from the backup with the tag name tag_name.
RECOVER DATABASE UNTIL TIME#
This is used to perform incomplete media recovery to a specified point in time. The log files in the archive log directory are read for use in recovering the data files that were affected by media errors to the specified point in time.
RECOVER DATABASE UNTIL CANCEL#
This is used to perform incomplete media recovery to the most recent point in time at which the logs in archive log files are valid. The log files in the archive log directory are read for use in recovering the data files that were affected by media errors to that point in time.
restore_database_clause#
This performs a complete media restoration on a database, or an incomplete restoration to a specific point in time or a specific tag name.
restore_tablespace_clause#
This performs a complete restoration on a tablespace.
change_backup_directory_clause#
This specifies the location of backup files generated by incremental backups.
move_backup_clause [WITH CONTENTS]#
This changes the incremental backup directory. If the WITH CONTENTS option is specified, previous backup files are transferred to the new directory.
delete_backup_clause#
This deletes incremental backup files which have expired.
change_tracking_clause#
This enables or disables page change tracking for incremental backups.
snapshot_clauses#
This option configures SNAPSHOT SCN on the basis of the time when BEGIN SNAPSHOT is executed, and exports data with iLoader based upon the configured SCN.
Examples#
<Query> Start up a database called mydb to provide normal service.
iSQL> ALTER DATABASE mydb SERVICE;
<Query> Switch the database to archievelog mode.
iSQL> ALTER DATABASE ARCHIVELOG;
<Query> Start up the database normally after the incomplete recovery.
iSQL> ALTER DATABASE mydb META RESETLOGS;
<Query> Back up the SYS_TBS_DISK_DATA tablespace to the /altibase backup directory.
iSQL> ALTER DATABASE TABLESPACE SYS_TBS_DISK_DATA TO '/altibase_backup/';
<Query> Restore the database to Feb. 16, 2009, 12:00 PM from a previous backup.
iSQL> ALTER DATABASE RECOVER DATABASE UNTIL TIME '2008-02-16:12:00:00';
<Query> Restore the database from a previous backup to reflect the entire contents of the log file #20000, which immediately precedes the log file #20001, which was lost.
iSQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;