Maintain Illustra Databases

Illustra databases require two kinds of regular maintenance. First, each database must be vacuumed to purge deleted rows from the database and update statistics to help the optimizer speed access to your data. Second, each database must be dumped regularly to ensure against file system failure.

It is also possible to enable transaction level recovery using the Illustra transaction log. This will allow you to recover a database up to the latest commited transaction if a disk fails. See the Illustra System Administrator's Guide for detailed information on enabling transaction level logging.

Vacuum a Database

Illustra implements an advanced no-overwrite storage mechanism by writing new records on update instead of updating the row in place. In addition, it marks records as invalid instead of physically reclaiming their storage space when deleting them. These methods provide important features:

Because transactions are implemented by first writing the new rows and then validating them when the transaction is completed, the database does not have to maintain complex before image and after image recovery logs. This allows an Illustra database to come back online instantaneously after a failure, instead of after a lengthy recovery procedure.
Because old rows are stored in the database and stamped with their commit and deletion times, Illustra supports time travel back to older versions of rows in a table. AOLserver uses this feature to implement the page archive versioning.

However, the copies of deleted and updated rows in the database continue to use disk space. Database vacuuming is the process by which Illustra purges these deleted rows. In addition, while purging deleted rows, Illustra updates various statistics about the tables in the database to help the query optimizer determine the fastest path to access data.

To vacuum a database, connect to the database using the nsql command and execute the vacuum SQL statement. The nsql command is a command-line program used to send SQL statements directly to the database. The vacuum statement is described in detail in Chapter 3 of the Illustra System Administrator's Guide.

Vacuum all deleted rows from the database:

Run the nsql command from a terminal window on Unix or a Command Prompt (MS-DOS) window on NT. To vacuum the database, you must connect as the Illustra miadmin user using the -U flag before the database name. By default, the AOLserver uses the nsdb database. To start nsql:
```
    \>	nsql -U miadmin nsdb
```
When nsql connects to the database, it responds with a * prompt, indicating it is ready to send SQL statements to the database.
Enter the vacuum SQL command as described in Chapter 3 of the Illustra System Administrator's Guide. Normally you want to vacuum the entire database and update statistics using the syntax:
```
    vacuum all with(statistics);
```
This command removes all deleted rows from the database, including any deleted rows in the ns_archives_` table. It removes all but the latest archived version of pages stored in the database. If archiving is not enabled in your installation or allowing recovery of only the latest archived page is appropriate for your users, vacuuming the entire database is appropriate.

Back Up the Database

The Illustra database stores tables in individual files in the file system. However, because Illustra caches important data and implements a complex transaction control system above the file system, it is unsafe to simply back up the database files with ordinary file system backup tools. Instead, you must use the Illustra dump SQL statement to copy a consistent snapshot of the database to ordinary file system files, which can then be backed up by your file system backup procedures.

You can also use the Illustra SQL dump statement to back up directly to a tape drive configured for use by Illustra. See the Ilustra System Administrator's Guide for details. The instructions provided below only describe dumping a database to an ordinary file that can be backed up by your normal file system backup tools.

To dump an Illustra database, connect to the database using the nsql command and execute the dump SQL statement. The nsql command is a command-line program used to send SQL statements directly to the database. The dump statement uses the following syntax:

    dump level device 'file_path_spec'

The level is an integer from 0 to 3, similar to the dump levels of the Unix dump utility:

Level 0 dumps the entire database.
Level 1 dumps everything since the last level 0 dump.
Level 2 dumps everything since the last level 1 dump.
Level 3 dumps everything since the last level 2 dump.

The device is the name of a device configured to be used by Illustra for backups. The simplest case, used in the instructions below, is the device named disk, so the database is backed up to an ordinary disk file. Other possibilities include tape_8mm and tape_qic150; see the Illustra Systems Administrator's Guide for more information on available devices.

The frequency of dumps depends on the requirements of the particular installation. A site with few changes to the database may implement a simple once-a-week level 0 dump strategy. A site with many daily updates to the database may instead need a nightly backup that uses the dump levels to avoid copying previously backed up data.

Back up the entire database to a file:

Run the nsql command from within a terminal window on Unix or a Command Prompt (MS-DOS) window on Windows NT. To dump the database, you must connect as the Illustra miadmin user using the -U flag before the database name, for example:
```
    \>	nsql -U miadmin <database>
```
When nsql connects to the database, it responds with a * prompt indicating it is ready to send SQL statements to the database.
Enter the dump SQL command as described in Chapter 3 of the Illustra System Administrator's Guide. To dump the entire database to a Unix file use the following syntax:
```
    dump 0 disk `/backupdisk/illustradumps/mydb.0';
```

Be sure to dump your database to a different physical disk or to immediately back up the file, using your ordinary file system backup utility, to another backup medium.

Back up incremental database changes to a file:

Run the nsql command from within a terminal window on Unix or a Command Prompt (MS-DOS) window on NT. To dump the database, you must connect as the Illustra miadmin user using the -U flag before the database name, for example:
```
    \>	nsql -U miadmin <database>
```
When nsql connects to the database, it responds with a * prompt indicating it is ready to send SQL statements to the database.
Enter the dump SQL command as described in Chapter 3 of the Illustra System Administrator's Guide. To incrementally perform a level 1 dump to copy updated data in the database since the last level 0 dump, use the following syntax:
```
    dump 1 disk `/backupdisk/illustradumps/mydb.1';
```

Be sure to dump your database to a different physical disk or to immediately back up the file, using your ordinary file system backup utility, to another backup medium.

Restore the Database

To recover the database after a file system failure, you must first reinstall Illustra, and then recover the individual database in use by your site from database dump files restored from your file system backup software.

To recover an Illustra database from a database dump file, use the Illustra SQL restore command. It uses the following syntax:

    restore database_name {level | level to level} device 
file_path_spec

You can restore multiple levels of a dump in a single restore. To restore multiple levels, specify the start level and the end level. The different levels can reside on different devices and in different directories. Specify the device and path name from the lowest level of the dump (level 0) in the restore statement. You will be prompted for the devices and path names of subsequent levels during the restore process.

If a database having the specified database name already exists on the server when restoring a level 0 dump, the restore process destroys the old database, creates a new one, and loads the data into it. If no database with the specified database name exists on the server, the restore process creates a new database.

Possible devices are the same as those for the dump procedure described above. The example below assumes you perform regular dumps to disk files and restore the disk dump files using your ordinary file system backup software. Because you cannot connect to the database that you intend to restore, you must connect to the special template1 database to execute the SQL restore command.

Restore a database:

Run the nsql command from within a terminal window on Unix or a Command Prompt (MS-DOS) window on NT. To dump the database, you must connect as the Illustra miadmin user using the -U flag before the template1 database name, for example:
```
    \>	nsql -U miadmin template1
```
When nsql connects to the database, it responds with a * prompt indicating it is ready to send SQL statements to the database.
Enter the restore SQL command as described in Chapter 3 of the Illustra System Administrator's Guide. Use a single restore command to restore from a level 0 and any incremental levels you have. For example, to restore the mydb database base using level 0, 1, and 2 disk file dumps, use the following syntax:
```
    restore mydb 0 to 2 disk 
    `/backupdisk/illustradumps/mydb.0';
```

You will be prompted for the location of the level 1 and 2 dump files. In addition, if the mydb database already exists, it will be removed before it is restored from the dump files.

Top of Page
Copyright © 1996 America Online, Inc.