Thursday, August 2, 2012

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.

5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I guess I have selected a mind blowing and interesting blog.
    PPI Claims

    ReplyDelete
  3. I was getting bore since morning but as soon as I got this link & reached at this blog, I turned into fresh and also joyful too. PPI Claims

    ReplyDelete
  4. I believe this is actually the most useful blog I've been through this entire day. CPP CLAIMS

    ReplyDelete
  5. I believe this is actually the most useful blog I've been through this entire day. here

    ReplyDelete