Search <book_title>...

Veritas InfoScale™ 8.0.2 Storage and Availability Management for Oracle Databases - AIX, Linux, Solaris

Last Published: 2023-06-12

Product(s): InfoScale & Storage Foundation (8.0.2)

Platform: AIX,Linux,Solaris

Section I. Storage Foundation High Availability (SFHA) management solutions for Oracle databases
1. Overview of Storage Foundation for Databases
Section II. Deploying Oracle with Veritas InfoScale products
Section III. Configuring Storage Foundation for Database (SFDB) tools
1. Configuring and managing the Storage Foundation for Databases repository database
2. Configuring authentication for Storage Foundation for Databases (SFDB) tools
Section IV. Improving Oracle database performance
Section V. Using point-in-time copies
Section VI. Optimizing storage costs for Oracle
Section VII. Managing Oracle disaster recovery
1. Using Storage Foundation for Databases (SFDB) tools in a replicated environment
2. Using volume snapshots in a replicated environment
Section VIII. Storage Foundation for Databases administrative reference
Section IX. Reference

Migrating partitioned data and tablespaces

Perhaps the simplest application of multi-tier storage to databases is relocation of individual table partitions between different placement classes as usage requirements change. If exact relocation times are unpredictable, or if relocation is infrequent, administrators may wish to relocate table partitions when necessary rather than defining strict periodic relocation schedules.

Ad hoc relocation of table partitions can be useful, for example, with databases that track sales and inventory for seasonal businesses such as sports equipment or outdoor furniture retailing. As the selling season for one type of inventory (for example, summer equipment or furniture) approaches, database table partitions that represent in-season goods can be relocated to high-performance storage, since they will be accessed frequently during the coming months. Similarly, partitions that represent out-of-season goods can be relocated to lower-cost storage, since activity against them is likely to be infrequent.

For example, sales are mostly catalog-driven for a large retailer specializing in sports equipment. Product details are saved in a large database and the product table is partitioned based on type of activity. Some of the products are seasonal and do not sell well at other times. For example, very few snow skis are sold during the summer. To achieve season-based migration, see the following example. Assume the table product_tab has two partitions, summer and winter. Each of these partitions is mapped to a separate data file.

First, you must set up your system to use SmartTier for Oracle.

To add the fast_storage and slow_storage storage classes

Use the dbdst_admin command as follows:

$ /opt/VRTS/bin/dbdst_admin -S PROD -o addclass=\
fast_storage:"Fast Storage for Production DB"

$ /opt/VRTS/bin/dbdst_admin -S PROD -o addclass=\
slow_storage:"Slow Storage for Production DB"

To convert the database's file system and add volumes for use with SmartTier for Oracle

Use the dbdst_convert command as follows:

$ /opt/VRTS/bin/dbdst_convert -S PROD \
-M /dev/vx/dsk/oradg/oradata -v new_vol1,new_vol2,new_vol3

To classify volumes into storage classes

Use the dbdst_classify command as follows:

$ /opt/VRTS/bin/dbdst_classify -S PROD \
-M /dev/vx/dsk/oradg/oradata -v new_vol1:fast_storage

$ /opt/VRTS/bin/dbdst_classify -S PROD \
-M /dev/vx/dsk/oradg/oradata -v new_vol2:slow_storage,\
new_vol3:slow_storage

Once the volumes are configured, an administrator can define file placement policy rules that specify seasonal relocation of selected tablespaces and partitions and assign them to the database's file system.

To move summer data to slower storage and winter data to faster storage at the beginning of winter

Use the dbdst_partition_move command as follows:

$ /opt/VRTS/bin/dbdst_partition_move -S PROD -T product_tab \
-p winter -c fast_storage

$ /opt/VRTS/bin/dbdst_partition_move -S PROD -T product_tab \
-p summer -c slow_storage

These commands relocate the files that comprise the winter partition of the product_tab table to placement class fast_storage, and the files that comprise the summer partition to placement class slow_storage. SmartTier for Oracle determines which files comprise the winter and summer partitions of product_tab, and uses underlying SmartTier services to immediately relocate those files to the fast_storage and slow_storage placement classes respectively.

To move winter data to slower storage and summer data to faster storage at the beginning of summer

Use the dbdst_partition_move command as follows:

$ /opt/VRTS/bin/dbdst_partition_move -S PROD -T product_tab \
-p summer -c fast_storage

$ /opt/VRTS/bin/dbdst_partition_move -S PROD -T product_tab \
-p winter -c slow_storage

SmartTier for Oracle formulates SmartTier policy rules that unconditionally relocate the files containing the target partitions to the destination placement classes. It merges these rules into the database file system's active policy, assigns the resulting composite policy to the file system, and enforces it immediately to relocate the subject files. Because the added policy rules precede any other rules in the active policy, the subject files remain in place until the dbdst_partition_move command is next executed, at which time the rules are removed and replaced with others.