|
|
|
For a dynamic backup of your database, use journals in combination with checkpoints.
Journals. keep track of all changes made to journaled tables after the last checkpoint
|
|
|
When you are journaling a database, you should do the following:
|
|
|
You can perform journaling tasks in VDBA or using system commands.
For the detailed steps for performing journaling procedures in VDBA, see the Procedures section of online help.
The system commands for journaling tasks are the ckpdb and alterdb commands. For more information, see the Command Reference Guide.
|
|
|
Journaling can be selected for an entire database or on a table-by-table basis.
|
|
|
The recommended approach is to journal the entire database rather than specifc tables. Tables in journaled databases are created "with journaling" if that is the default_journaling setting of the server class used by the Ingres DBMS Server you are connected to.
Disable journaling on specific tables only if a rollforward recovery of those tables is not important. You must exercise caution when creating non-journaled tables in journaled databases. Non-journaled tables cannot be audited when the database is audited, in addition to their lack of roll forward recovery. Following a roll forward recovery, the relationship between journaled and non-journaled tables can be confusing.
|
|
|
If you choose to journal selected tables, you are responsible for ensuring that all related objects are also journaled (for example, that all tables associated with a view are journaled).
|
|
|
To journal an entire database, use the Enable Journaling option in the Checkpoint dialog in VDBA.
Note: The only tables that are enabled are those whose journaling status is "enabled after next checkpoint." Tables whose journaling status is "disabled" cannot be enabled.
|
|
|
The journaling of new tables begins, as follows:
|
|
|
To start journaling on a database that has not yet been checkpointed, in VDBA invoke the Checkpoint dialog (by choosing the Database Checkpoint menu command) and set the Enable Journaling option.
|
|
|
The first time journaling is turned on in a particular database, you must checkpoint the database by setting the Enable Journaling option in the Checkpoint dialog. This ensures that the checkpoint is taken offline and with an exclusive lock on the database.
Once you have enabled journaling by checkpointing offline with the Enable Journaling option, you can maintain the "journaling on" status and take online checkpoints by not subsequently setting the Enable Journaling option when you take a checkpoint. Online checkpoints permit users to continue using the database while the checkpoint is being taken.
Once you have enabled journaling for the database by checkpointing offline with the Enable Journaling option, you can take an offline checkpoint to start journaling of tables for which journaling is enabled after the next checkpoint.
Any explicit journaling option causes the checkpoint to be taken offline, exclusively locking the database.
|
|
|
To disable journaling, disable the Journaling check box in the Options dialog invoked from the Create Table dialog.
|
|
|
To stop journaling a particular table, issue the following statement from the query language monitor:
set nojournaling on tablename;
|
|
|
You can stop journaling all the tables in a database using either of the following methods in VDBA:
Note: This takes effect immediately; therefore, it must be used only for emergencies. For information, see Disabling Journaling When Checkpointing.
To re-enable journaling on a table or database that has had journaling disabled, use the Checkpoint dialog.
|
|
|
When you choose the Disable Journaling option in the Checkpoint dialog, journaling is stopped for all tables in a database.
This causes a checkpoint of the specified database to be taken and journaling to be stopped. After stopping journaling, you can still take periodic checkpoints of the database.
|
|
|
When you alter a database with the Disable Journaling option (using the Database Characteristics dialog), 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.
Important! Using this option to disable journaling makes the displayed value for the journaling status inconsistent. Tables are "journaling enabled," even though journaling is disabled for the database as a whole and you expect to see "enabled after next checkpoint."
For step-by-step instructions, see Altering Database Characteristics in Procedures of VDBA online help.
|
|
|
To disable journaling on a database by altering the database, follow these steps.
Note: This procedure must be performed 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).
The Database Characteristics dialog appears.
At this point the database is no longer journaled.
Caution! Do not roll forward a database that has journaling disabled. Any transactions committed after the alter database operation, or that were still in the transaction log file at the time journaling was disabled, are lost.
Using the Infodb dialog, you can determine whether journaling has been disabled.
The Checkpoint dialog appears.
|
|
|
The Database Characteristics dialog allows you to disable journaling and to change several database characteristics, including:
To perform this operation, you must be the owner of the database or have the operator privilege.
|
|
|
Journal files are created by the archiver process by the first journal write after a checkpoint takes place. Additional journal files are created as prior files are filled.
By default, journal files are created with:
This results in a target journal file size of 8 MB (16, 384 * 512 bytes). Although most users find these parameters satisfactory, all three can be modified by using the Database Characteristics dialog, using the Block, Size, and Initial edit controls.
|
|
|
The Database Characteristics dialog specifies the target journal size in the Block edit control. The possible values are between 32 to 65536.
A journal file is closed and a new one is created when either a checkpoint is taken (actually, when the first write after a checkpoint is taken) or when the journal file fills.
The Block edit control allows some control over when the logging system declares a journal file full. This parameter is known as the "target journal file size" because the exact size of a journal file cannot be easily predicted. The archiver closes off journal files, if they grow larger than the target number of blocks, only at the completion of an archive cycle. Longer archive cycles imply more variation in journal file sizes.
Upon successful completion of this command, a message is written to the errlog.log. The updated block value can be observed as the infodb parameter "Target journal size".
The command takes effect immediately (or more accurately, the next time the archiver reads the configuration file).
The initial journal size (specified in the Initial edit control) can be affected by this command.
|
|
|
The Database Characteristics dialog allows you to specify the journal block size in the Size edit control. Valid journal block sizes are 4096, 8192, 16384, 32768, and 65536 bytes.
Archiver (dmfacp) performance is affected by the journal file block size. You normally change the block size (Size edit control) in conjunction with the number of target journal blocks (Block edit control). This allows you to target the creation of journal files of a given size. Changing the block size without also changing the number of blocks in a journal file changes the target size of the file.
You typically change the journal block size immediately after the database is created, before the initial checkpoint is taken with the journaling option. Thereafter, changing the journal block size is generally required only for installations with a relatively high volume of journaled data. You can only change the journal block size when journaling is not currently enabled.
To change the journal block size on a database that is currently journaled, perform the following operations:
Upon successful completion of this operation, a message is written to the errlog.log. The updated journal file block size can be observed as the infodb "Journal block size" parameter.
|
|
|
The Database Characteristics dialog allows you to specify the initial journal size in the Initial edit control. Valid journal block sizes are from 0 to the current target journal size (which can be obtained using infodb).
The Initial option allows a measure of control over when journal file disk space allocation takes place, but only for the first journal file created after a checkpoint is taken.
You can change the initial journal size at any time, and it takes effect when the next database journal file is created. In the case of an offline checkpoint, this can be some time after a checkpoint is taken. In the case of an online checkpoint, the file allocation occurs during execution of the checkpoint.
Upon successful completion of this command, a message is written to the errlog.log. The updated block value can be obtained from the "Initial journal size" parameter in the Infodb dialog (invoked by the Database Infodb menu command).
|
|
|
Preallocating space in journal files using the Database Characteristics dialog can reduce the likelihood of running out of journal file disk space.
Filling a journal file causes the archiver to stop, and if left untreated, eventually causes the log file to fill, which brings the system to a halt.
With the alter database operation you can, for example, request creation of journal files of a given size and also request preallocation of the entire file. If the file is sufficiently large, this eliminates the possibility of running out of journal disk space during normal online processing.
This can, however, cause unused journal space to be wasted. If excessive space is allocated during journal file creation, that disk space can be made unavailable when a subsequent checkpoint operation takes place.
If it is necessary to control journal file size more accurately, the archiver must be awakened more frequently. This can be accomplished with smaller consistency point (CP) intervals, allowing more frequent archiver "wake-ups." The consistency point interval can be configured using the Configuration Manager. For details on the Configuration Manager utility, see Using the Configuration Manager in online help. Smaller CP intervals can affect system performance, although the processing involved is for a short interval of time.
|
|
|
On UNIX systems, disk space must be physically written when a journal file is extended. When a journal file is filled, a new one is created. It is undesirable for performance to be affected by file allocation that occurs at unplanned intervals.
You can use the alter database space preallocation features to manage when the allocation takes place, allowing control over when the allocation time delay occurs. A significant amount of journal file I/O can occur when the first journal file is created, with the archiver being unavailable during this time. This can be observed as an online checkpoint taking a long time to complete, or the archiver performing a large amount of work when the first journal write after an offline checkpoint takes place.
|
|
|
In addition to using journals for recovery, you can use journals to produce audit trails of changes to a database. You must be the DBA for the database or have the security privilege to perform an audit on a database.
Audit your database periodically to verify that your journals are correct.
|
|
|
In VDBA, the audit database operation is performed using the Audit Database dialog, invoked by the Operations Audit menu command. For the detailed steps for performing this procedure, see the Procedures section of online help.
To accomplish this task with a system command, use the auditdb command. For more information, see the Command Reference Guide.
|
|
|
The Audit Database dialog in VDBA enables you to produce a listing or file of changes made to journaled tables after the last checkpoint. It is possible that this listing does not include all changes that have been made after the last checkpoint for the following reasons:
The audit database operation scans journal files twice. A prescan is performed to filter out undesired information (for example, aborted transaction data). The second scan outputs journal records of interest. To improve program performance, the Before edit control value terminates both scans when an End Transaction record is found that has a time later than that specified.
When the Inconsistent check box is enabled, you are allowed to view journals that the database has marked as inconsistent.
Note: The audit database operation can still fail if core catalogs are inconsistent.
When the Wait check box is enabled, the audit waits until journals are current. "Current" in this context means either of the following:
Note: If a large amount of unarchived information remains in the log file when this request is initiated, a significant delay in processing can occur.
|
|
|
To make querying the data easier, you can create an audit trail as a file in your current directory and load the file into a table in your database. To do this, follow these steps:
Note: You must have first specified at least one table. Also, you can specify files only if the table you are auditing has fewer than 1940 bytes per row.
For example, the audit database operation extracts a record of the changes to an employee table from the journal for a particular database. The changes in the current directory can be placed in a file named empaudit.trl.
When creating the table, include the audit trail and employee table columns shown below. Enter the audit trail columns before the table's columns, in the order shown. If you do not, the copy operation can fail when you try to copy the audit trail data into the table.
Column Name |
Data Type |
Description |
|---|---|---|
date |
date not null with default |
Date and time of the beginning of the multi-query transaction that contained the operation |
username |
char(32) not null with default |
User name of the user who performed the operation |
operation |
char(8) not null with default |
Insert, update, or delete operation |
tranid1 |
integer not null with default |
Transaction identification number. Concatenated with tranid2. |
tranid2 |
integer not null with default |
Transaction identification number. Concatenated with tranid1. |
table_id1 |
integer not null with default |
Table identification number. Corresponds to value in table_reltid column of iitables system catalog for specified table. |
table_id2 |
integer not null with default |
Table identification number. Corresponds to value in table_reltidx column of iitables system catalog for specified table. |
name |
varchar(20) |
Employee name |
age |
integer |
Employee age |
salary |
money |
Employee salary |
dname |
varchar(10) |
Department name |
manager |
varchar(20) |
Employee manager |
In the following example, the data in the empaudit.trl file is copied to the empaudit table:
Windows:
copy empaudit() from 'C:\users\joe\empaudit.trl';
UNIX:
copy empaudit() from '/usr/joe/empaudit.trl';
VMS:
copy empaudit() from '[usr.joe]empaudit.trl';
The table created from the audit trail (in this example, the empaudit table) contains: