Relocating all indexes to premium storage

It is a common practice for DBAs to name index files with some common extensions. For example, all index files are named with ".inx" extensions. If your Tier-0 storage has enough capacity, you can relocate all indexes of the database to Tier-0 storage. You can also make sure all index containers created with this special extension are automatically created on Tier-0 storage by using the CREATE and RELOCATE clause of policy definition.

To relocate all indexes to Tier-0 storage

  1. Create a policy such as the following example:
    # cat index_policy.xml
    <?xml version="1.0"?>
    <!DOCTYPE PLACEMENT_POLICY SYSTEM "/opt/VRTSvxfs/etc/\
    placement_policy.dtd">
    <PLACEMENT_POLICY Version="5.0" Name="selected files">
        <RULE Flags="data" Name="Key-Files-Rule">
            <COMMENT>
                This rule deals with key important files.
            </COMMENT>
            <SELECT Flags="Data">
                <PATTERN> *.inx  </PATTERN>
            </SELECT>
            <CREATE>
                <COMMENT>
                    Note that there  are two DESTINATION.
                </COMMENT>
                <ON>
                    <DESTINATION>
                        <CLASS> tier0 </CLASS>
                    </DESTINATION>                                
                    <DESTINATION>
                        <CLASS> tier1</CLASS>
                    </DESTINATION>
                </ON>
            </CREATE>
                 <RELOCATE>
                     <COMMENT>
                         Note that there is no WHEN clause.
                     </COMMENT>
                     <TO>
                         <DESTINATION>
                             <CLASS> tier0 </CLASS>
                         </DESTINATION>
                     </TO>
                 </RELOCATE>
        </RULE>
    </PLACEMENT_POLICY>
  2. To make sure file creation succeeds even if Tier-0 runs out of space, add two ON clauses as in the example policy in 1.
  3. Assign and enforce the policy.
    # fsppadm validate /DBdata index_policy.xml
    # fsppadm assign /DBdata index_policy.xml
    # fsppadm enforce /DBdata