PPI Backup restore Walk-through

PPI Backup and restore.

Consider the following when archiving selected partitions in PPI tables:

• A restore operation always deletes the selected partitions of the target table before

restoring the rows that are stored in the archive.

• Archiving selected partitions operates on complete partitions within tables, meaning that

the selection of a partial partition implies the entire partition.

• PPI and non-PPI tables are permissible in a single command. Both table types can be

managed in a single database with the EXCLUDE TABLES option.

• Partitioning is based on one or more columns specified in the table definition.

• Partition elimination restricts a query to operating only in the set of partitions that are

Required for the query.

• Incremental archives are possible by using a partition expression that is based on date

fields, which indicate when a row is inserted or updated.

• An archive or restore of selected partitions only places full-table HUT locks. HUT locks on

Individual partitions are not supported.

• Re-collect table statistics after a restore of selected partitions. Statistics are part of the table

dictionary rows, which are not restored during a partition-level restore.

• If a table has a partitioning expression that is different from the partitioning expression

used in the PPI archive, a PPI restore is possible as long as no other significant DDL

changes are made to the table.

• For restore full backup should be restored beforehand, for copy table with same definition should present.

Scripts Used:

Backing of PPI using date range.

archive data table

(test1.tranhist) (partitions where (!transactiondate between date '2001-01-01' and date '2001-05-01'!)),

release lock,

file=arcpart;

Restoing Prartition.

.logon localhost/sysdba,sysdba;

restore data table

(test1.tranhist) (partitions where (!transactiondate between date '2001-01-01' and date '2001-05-01'!)),

release lock,

file=arcpart;

  

Copying Partition from Backupset.

.logon localhost/sysdba,sysdba;

copy data table

(test2.tranhist)(from (test1.tranhist), partitions where (!transactiondate between date '2001-01-01' and date '2001-05-01'!)),

release lock,

file=arcpart;

Walk-through of test conducted:

Created a Partition tables.

CREATE SET TABLE test1.tranhist ,FALLBACK ,

     DUAL BEFORE JOURNAL,

     NO AFTER JOURNAL,

     WITH JOURNAL TABLE = test1.bcd ,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      TransactionID INTEGER,

      TransactionDate DATE FORMAT 'YYYY-MM-DD',

      TransactionParam1 INTEGER)

PRIMARY INDEX ( TransactionID )

PARTITION BY RANGE_N(TransactionDate  BETWEEN DATE '2000-01-01' AND DATE '

2004-12-31' EACH INTERVAL '1' MONTH );

Inserted Record for every partition range.

    

insert into test1.tranhist values (1,'2000/03/04',2);

insert into test1.tranhist values (15,'2002/01/19',2);

insert into test1.tranhist values (21,'2000/08/04',2);

insert into test1.tranhist values (13,'2002/02/04',2);

insert into test1.tranhist values (21,'2000/08/19',2);

insert into test1.tranhist values (13,'2002/02/15',2);

insert into test1.tranhist values (19,'2001/05/04',2);

insert into test1.tranhist values (17,'2003/01/04',2);

insert into test1.tranhist values (19,'2001/05/17',2);

insert into test1.tranhist values (17,'2003/01/19',2);

insert into test1.tranhist values (20,'2001/06/04',2);

insert into test1.tranhist values (16,'2003/01/04',2);

insert into test1.tranhist values (20,'2001/06/17',2);

insert into test1.tranhist values (16,'2003/01/17',2);

insert into test1.tranhist values (7,'2002/03/04',2);

insert into test1.tranhist values (15,'2004/01/04',2);

insert into test1.tranhist values (7,'2002/03/19',2);

insert into test1.tranhist values (14,'2004/01/04',2);

insert into test1.tranhist values (8,'2002/03/04',2);

insert into test1.tranhist values (8,'2002/03/12',2);

insert into test1.tranhist values (6,'2002/03/04',2);

insert into test1.tranhist values (6,'2002/03/01',2);

insert into test1.tranhist values (3,'2002/04/04',2);

insert into test1.tranhist values (18,'2002/05/04',2);

insert into test1.tranhist values (18,'2002/05/19',2);

insert into test1.tranhist values (3,'2002/11/02',2);

insert into test1.tranhist values (5,'2003/04/04',2);

insert into test1.tranhist values (4,'2003/04/04',2);

insert into test1.tranhist values (5,'2003/08/02',2);

insert into test1.tranhist values (4,'2003/10/03',2);

insert into test1.tranhist values (4,'2003/04/04',2);

insert into test1.tranhist values (5,'2003/08/02',2);

insert into test1.tranhist values (4,'2003/10/03',2);

Take a full Backup of database test1.

.logon localhost/sysdba,sysdba;

archive data table

(test1.tranhist),

release lock,

file=arcpart;

Copy to other database (test2).

    .logon localhost/sysdba,sysdba;

copy data table

(test2.tranhist) (from (test1.tranhist)),

release lock,

file=arctran;

Take partition Backup test1.

  .logon localhost/sysdba,sysdba;

archive data table

(test1.tranhist) (partitions where (!transactiondate between date '2001-01-01' and date '2001-05-01'!)),

release lock,

file=arcpart;

Check data in partition of test2.

select * from test2.tranhist where transactiondate between date '2001-01-01' and date '2001-05-01';

*** Query completed. 11 rows found. 3 columns returned.

 *** Total elapsed time was 1 second.

TransactionID  TransactionDate  TransactionParam1

-------------  ---------------  -----------------

            2       2001-03-04                  2

           14       2001-01-17                  2

            2       2001-03-01                  2

           11       2001-02-04                  2

           11       2001-02-24                  2

           10       2001-02-04                  2

           10       2001-02-14                  2

            9       2001-03-04                  2

            9       2001-03-14                  2

           12       2001-03-04                  2

           12       2001-03-24                  2

deleted partition record from test2.

delete from test2.tranhist where transactiondate between date '2001-01-01' and date '2001-05-01';

*** Delete completed. 11 rows removed.

 *** Total elapsed time was 1 second.

Copy Partition data from backup to test2.

copy data table

(test2.tranhist)(from (test1.tranhist), partitions where (!transactiondate between date '2001-01-01' and date '2001-05-01'!)),

release lock,

file=arcpart;

Check data in partition of test2.

*** Query completed. 11 rows found. 3 columns returned.

 *** Total elapsed time was 1 second.

TransactionID  TransactionDate  TransactionParam1

-------------  ---------------  -----------------

            2       2001-03-04                  2

           14       2001-01-17                  2

            2       2001-03-01                  2

           11       2001-02-04                  2

           11       2001-02-24                  2

           10       2001-02-04                  2

           10       2001-02-14                  2

            9       2001-03-04                  2

            9       2001-03-14                  2

           12       2001-03-04                  2

           12       2001-03-24                  2

 

Potential Data Risks When Archiving/Restoring Selected Partitions:

Be careful when archiving partitioned tables: a number of undesirable conditions can occur.

For additional issues that might occur during restore operations.

Caution: The following cases generally do not display an error or give any indication that a problem has

occurred. In most instances, the only indication is that data is incorrect or is missing from a

table.

• Use Correct Specifications—The incorrect use of specifications causes the following

problems:

• An incorrect PARTITIONS WHERE specification during backup can result in an

incomplete archive or difficulties during a restore operation.

• An incorrect PARTITIONS WHERE or ALL PARTITIONS specification during restore

can result in data lost from a table or the restoration of stale data to a table if the

archive being restored contains partial, incomplete, or stale versions of an already

existing partition.

• Restrict Updates to Active Partitions—It is not possible to determine which partitions

have been modified since the last backup. If changed partitions are not re-archived, the

changes are lost when restored.

For example, if the following scenarios exist for a table:

• The backup strategy is to back up only the active (latest) partition of the table.

• A change is made to a non-active partition (to fix an incorrect update).

the only way to archive the change is to archive the changed partitions separately.

The remedy for this situation is to restrict updates to the active partitions only (by using

views to control which rows/partitions are updated) or to re-archive all modified

partitions.

• Do Not Change Values of Functions or Variables—If a built-in SQL function or variable

is used in the PARTITIONS WHERE condition, and the value of the function or variable

changes during the job, a different set of partitions might be archived (or restored) for

some objects in that single archive.

For example, if an archive job uses the CURRENT_DATE built-in function to determine

which is the active partition, and the backup runs past midnight, the date change causes a

different partition to be selected. This means that objects archived after midnight will

archive the new (and probably empty) partition.

The remedy for this situation is to do one of the following:

• Avoid using a changing function or variable in the PARTITIONS WHERE condition.

• Run the backup at a time when the value will not change.

• Modify the PARTITIONS WHERE condition to take the value change into account

when selecting partitions. For example, define a range, such as ‘BETWEEN

CURRENT_DATE – n AND_CURRENT_DATE’ to archive the active partition even if the

date changes.

• Always Specify PARTITIONS WHERE or ALL PARTITIONS—If PARTITIONS WHERE

or ALL PARTITIONS are not specified for a RESTORE or COPY operation, the default

action is to overwrite the entire table with the archived table definition and data.

Essentially, this is the same as a full-table restore.

For example, if PARTITIONS WHERE is omitted when restoring a single-partition

backup, data is dropped from the table and the single partition stored on the archive is

restored.

To solve this problem, always specify PARTITIONS WHERE or ALL PARTITIONS when

restoring partitions into an existing table. Otherwise, the existing table will be overwritten.

• Know What Partitions are Being Deleted—In a RESTORE or COPY operation, all

partitions that match the PARTITIONS WHERE condition are deleted, even if they are not

stored on the archive.

For example, if an archive is restored that:

• Contains the data for April 2007

• Has a PARTITIONS WHERE condition that matches both March and April 2007

the data for March and April 2007 are deleted, and only April 2007 is restored.

Therefore, be careful when using PARTITONS WHERE. If there is any doubt about which

partitions are affected, COPY the selected partition backup to a staging table, and

manually copy the desired partition(s) into the target table using INSERT ... SELECT and/

or DELETE.

• Avoid Restoring From a Previous Partitioning Scheme—When changing the

partitioning expression for a table, changing the boundaries of existing partitions is

feasible. If these partitions are restored, Teradata might drop more data than expected or

restore less data than expected, if the archive does not include data for all of the selected

partitions.

For example, if an archive is done on a table partitioned by month with the archive data

corresponding to March 2004, and the table is re-partitioned by week, then a PPI restore of

the March backup (using ALL PARTITIONS) overwrites the data for all weeks that contain

at least one day in March. As a result, the last few days of February and the first few days of

April might be deleted and not restored.

Therefore, avoid restoring partition backups from a previous partitioning scheme to an

updated table. Or, use LOG WHERE for the weeks that contain days in both March and

February/April, and manually copy the rows into the table.

• Track the Partitions in Each Archive—Manual steps are required to determine which

partitions are archived by a given backup job, or to determine which backup job has the

latest version of a given partition. ANALYZE displays the Teradata-generated bounding

condition that defines the archived partitions. (This differs from a user-entered condition

that might only qualify partial partitions.) In this case, inconsistent or old data might be

restored to the table if the wrong archive is restored for a partition, or if partition-level

archives are restored out-of-order and the archives contain an overlapping set of

partitions.

For example, updated data is lost in the following situation. Assume that a final backup for

a March 2007 partition is performed on April 1, 2007. On April 5, a mistake is found in a

row dated March 16, so the row is updated, and a new backup of the March partition is

done. If, for instance, the table is accidentally deleted a month later, and an attempt is

made to restore the April 1 backup instead of the April 5 backup, the updated data is lost.

To determine the partitions in each archive, keep track of the partition contents of each

archived table, retain the output listing associated with a tape, or run ANALYZE jobs on

archives.