Journals
For an incremental backup of your database, use journals in combination with checkpoints.
While checkpoints provide you with a snapshot of the database, journals keep track of all changes made to the database after the last checkpoint.
Note: When you are journaling a database, you should take regular checkpoints of your database to minimize recovery time.
Note: Journaling is supported at the database level only (not at table- or user-level).
Tools for Performing Journaling
You can perform journaling tasks using system commands ckpdb and alterdb, or by using Actian Director.
Enable Journaling on an Entire Database
To journal an entire database
Issue the following command at the operating system prompt:
ckpdb +j dbname
A checkpoint is created and journaling is initiated.
Journaling and Online/Offline Checkpoints
An explicit journaling option on the ckpdb command causes the checkpoint to be taken offline (that is, when no one is using the database) and with an exclusive lock on the database.
The first time journaling is turned on in a particular database, you must checkpoint the database with journaling enabled (ckpdb +j dbname). Doing so ensures that the checkpoint is taken offline.
Once you have enabled journaling by checkpointing offline with the +j option, you can maintain the “journaling on” status and take checkpoints online (that is, when users are using the database) by not subsequently setting the +j option on ckpdb. Online checkpoints permit users to continue using the database while the checkpoint is being taken.
After you have enabled journaling for the database by checkpointing offline with the +j option, you can take an offline checkpoint to start journaling of tables for which journaling is enabled after the next checkpoint.
Note: A table-level checkpoint is not allowed.
Methods for Stopping Journaling
You can stop journaling all changes in a database with either of the following commands:
• Altering a database using the alterdb command.
• Creating a checkpoint using the ckpdb -j command.
Note: A table-level checkpoint is not allowed.
To re-enable journaling on a database that has had journaling disabled, use the ckpdb command, as described previously.
Disable Journaling When Checkpointing
The following command issued at the operating system prompt stops journaling of all the tables in a database:
ckpdb -j dbname
A checkpoint of the specified database is taken, and then journaling is stopped. After journaling is stopped, you can still take periodic checkpoints of the database.
Disable Journaling When Altering a Database
When you disable journaling using the alterdb command, journaling of a database is halted immediately, regardless of whether users are connected to the database.
This option is provided as a method for recovering from journaling system problems that prevent the archiver from moving transaction log file records to the database journal files, for example, if the disk partition containing the journal files is not periodically purged of obsolete journal files and the partition becomes full. If the logging system is unable to move records from the log file to the journal files, the transaction log file eventually fills up, causing a LOGFULL condition. When this occurs, no database activity can proceed until the LOGFULL state is cleared.
To use alterdb to disable journaling on a database
The following procedure must be run by the DBA of the database. It does not require a database lock and can be run even while the log file is full (LOGFULL).
1. Issue the following command at the operating system prompt:
alterdb dbname -disable_journaling
Journaling of the database is disabled.
Caution! Do not use rollforwarddb on a database that has journaling disabled. Any transactions committed after the alterdb action, or that were still in the transaction log file at the time journaling was disabled, will be lost.
2. Check the database state by using the infodb command at the operating system prompt:
infodb dbname
The infodb listing indicates whether journaling has been disabled.
3. Restart archive processing after disabling journaling by issuing the following command at the operating system prompt:
ingstart -dmfacp
4. Take a new checkpoint to re-enable journaling as soon as possible by using the following command at the operating system prompt:
ckpdb +j dbname
Database Characteristics Affected by Alterdb
The alterdb command lets you disable journaling and change several database characteristics, including:
• Change journal block settings
• Delete oldest checkpoint
• Set verbose mode
To perform this operation, you must be the owner of the database or have the operator privilege.
Backup and Restore Scenario Using Checkpoints and Journals
The ckpdb and rollforwarddb commands are used for backing up and restoring a database, respectively. These operations can also be performed using Actian Director.
Full database backup and recovery operations using the ckpdb and rollforwarddb commands apply to both traditional Ingres tables (if present) and Vector tables.
The process for backing up a database is as follows:
1. Create a database:
createdb dbname
Note: The database is not journaled.
2. Load data into the database.
3. Take a checkpoint of the entire database offline (when no one is using the database) and turn on journaling:
ckpdb +j dbname
This offline checkpoint needs to be done only once.
Subsequently, users update the database (both DML and DDL). The logging facility writes to the transaction log, and Vector writes to the Write-Ahead Log and various data files.
The archiver process regularly wakes and writes to a journal file the changes since the last trigger or full checkpoint.
4. Periodically (for example, nightly) take an online checkpoint:
ckpdb dbname
The commands for restoring a database are as follows. Use the appropriate command for your situation:
Note: The restore operation must be done offline (when no one is using the database).
Restore last full checkpoint and then restore the journals in one operation:
rollforwarddb dbname
or
rollforwarddb +c +j dbname
Restore the last full checkpoint, but not the journals:
rollforwarddb +c -j dbname
Restore the journals after the last full checkpoint has just been restored:
rollforwarddb -c +j dbname
Backup and Restore Using Copydb
The copydb command creates two scripts:
copy.out
Contains query language statements to copy your tables to operating system files. The script contains a COPY statement for each table being copied.
You run the copy.out script (using the sql command) to copy tables out of the database.
copy.in
Contains query language statements to recreate your tables, views, and associated indexes, permissions, and integrities, and copy the table’s data from the operating system files into a database.
You run the copy.in script (using the sql command) to copy the tables into the same or another database.
You can run copydb without shutting down the database; however, copydb needs to acquire locks while reading system catalogs, so it will wait for any users that are running DDL concurrently to finish.
To back up tables with copydb
1. Use operating system commands to create a temporary working directory for the copy.in and copy.out scripts that will be created, and then move to this directory. For example:
mkdir /tmp/mydir.backup
cd /tmp/mydir.backup
2. Back up all the tables and views that you own in the database by issuing the following command at the operating system prompt:
copydb dbname
To back up specified tables, issue this command:
copydb dbname tablename {tablename}
Copy.out and copy.in scripts are created.
3. Copy the data out of the database by running the copy.out script. Issue the following command at the operating system prompt:
sql dbname <copy.out
A copy of the objects copied from the database is created. Store these files on disk or tape.
To restore the data from a backup created by copydb
Run the copy.in script as follows:
sql dbname <copy.in
The tables are copied into the specified database.
Copy Files to a Backup Location Using OS Commands
You can use operating system commands to copy the needed files to a backup location.
To copy files
1. Issue infodb dbname to view all the directories related to the database.
2. Shut down the instance.
Warning: If you move files when Vector is running or without a clean shutdown, the database can become corrupted.
If you are unsure that Vector shut down cleanly, do the following:
a. Start Vector again with the ingstart command.
b. Ensure that you can access the database. (For example: sql dbname then SELECT COUNT(*) from a Vector table, and then \q to quit.)
Note: Simply accessing the database does not start the Vector Server.
c. Stop Vector with the ingstop command.
3. Copy the database file, journal, and dump files to the backup location. Create the work directories in the backup location, but you do not need to copy the work files.
To restore the files
1. Stop Vector using the ingstop command.
2. Copy the files back to their original location.
3. Restart Vector using the ingstart command.