Maintaining the Master Index Database

The database requires periodic maintenance tasks, such as backing up information or archiving certain tables. Perform backups regularly, and use the standards and policies of your organization to determine the best methods for backing up data. The following topics provide information about tasks you should perform for standard database maintenance.

• Backing up the Master Index Database

• Restoring the Master Index Database

• Archiving Master Index Data

Backing up the Master Index Database

The master index database must be backed up on a regular basis. Typically, the database should be backed up once a month or once a quarter, depending on the size of the database and the volume of data being processed. The frequency of your database backups depends on your organization’s internal policies and practices. Use your normal procedures for backing up a high availability database (this procedure should be determined by a database administrator).

Online Backups

The best practice for backing up the master index database is an online backup during which the database is not shut down. (Note that this does require an offline backup as a starting point to which any online changes can be applied in the event the database must be restored). An online backup will always take a consistent snapshot, though it might not backup all transactions in progress.

Each transaction in the master index application is saved under one commit command, so the state of the database is always consistent when a backup is performed. The history tables always match the transactions in the current tables and no partial transactions are committed. Even if a transaction is underway at the time of the backup, the database is consistent.

For the most reliable backups for Oracle databases, Oracle recommends running the Oracle database in ARCHIVELOG mode. ARCHIVE mode ensures that your database is protected from both instance and media failure and, because all changes made to the database are saved in a redo log, all database updates are available for recovery rather than just the most recent changes. For SQL Server, Microsoft recommends running the SQL Server database using the full recovery model, which allows a database to be recovered to the point of failure. Online backups are available for Oracle and SQL Server databases running in these modes.

Offline Backups

If needed, you can perform offline backups of the master index database. In this case, you must queue any incoming messages using the JMS IQ Manager and undeploy the master index application before beginning the backup. Once the backup is complete, restart the database, redeploy the master index application, and then process the messages queued by the JMS IQ Manager.

Restoring the Master Index Database

In the unlikely event that you need to restore the master index database to a previously archived version, you must undeploy the master index application prior to performing the restoration to ensure that the application retrieves the correct sequence numbers from the database once it is restored. Any new transactions that occurred after the archived version was created will be lost, but they can be resent if the JMS IQ Manager is configured to journal all messages.

Archiving Master Index Data

In addition to regular database backups, some of the master index database tables can grow very large. For performance reasons, you might want to archive the information in the sbyn_assumedmatch and the sbyn_audit tables.