SQL/MP Installation and Management Guide

Performing Recovery Operations
HP NonStop SQL/MP Installation and Management Guide—523353-004
11-19
Recovering Tables
Recovering Tables
If the SQL object purged is a table, recovery can be much more complex than one
involving only views and indexes. In some cases (for example if the TMF subsystem is
not configured for file recovery), recovery might not be possible at all. For this reason,
follow the Recovery Precautions on page 11-17.
For the best results in most cases, to recover a table and its dependent objects:
1. Determine what dependent objects (views, indexes, and other tables) might have
been dropped along with the table, using SQLCI.
2. Re-create the table and its dependent objects, using SQLCI. The DDL definition of
the newly created table must exactly match the DDL definition of the purged table.
3. Reset the INVALID and RELEASED attributes of the online dumps for the dropped
objects to OFF, using the TMFCOM ALTER DUMPS command.
4. If any indexes were associated with the file, re-create them.
5. Recover the table and its dependent objects with the TMFCOM RECOVER FILES
command, using the TOFIRSTPURGE option.
6. For the recovered objects, verify that the creation and redefinition timestamps in
the file labels match those in the SQL catalog, using the SQLCI VERIFY
command.
7. For all objects for which VERIFY identifies a mismatch, update the timestamps in
the SQL catalog to match those in the file labels, using a licensed SQLCI2 utility.
8. Update the statistics for the recovered table.
9. SQL compile any SQL programs that access this table.
Partitioned Tables
Recovery of partitioned tables requires special attention. The CREATE statement for a
partitioned table must indicate the number and names of the partitions as they were at
the time the table was dropped. (If you are recovering tables to a different location,
however, their partition names can be different.)
Over time, partitions are dropped, moved, added, and split. To rebuild a CREATE
statement reflecting the partitions at the time of the drop, therefore, you should file a
Caution. Unless performed with great care and precision, SQL table recovery involves risk of
database corruption and loss of data integrity. Recovery should be done only by experienced
users of SQL/MP and TMF users who understand:
•
How objects are defined in the SQL catalog and the results of altering those definitions
•
How to use the licensed SQLCI2 utility
If no one with this expertise is present at your site, contact the Global Customer Support
Center (GCSC) or your service provider before proceeding.