4 Replies Latest reply: Jan 24, 2007 7:59 AM by Cbourbou-Oracle RSS

    Sense of secondary tablespaces?

      Installation of ILMA was easy, good functionality and presentation, I particularly like the cost comparisons.

      However, using the help information, I stumbled upon this sentence:

      "The Secondary tablespaces are additional tablespaces that reside on that logical storage tier and must be specified so that events are not generated to move data that the ILM Assistant believes is on the incorrect logical storage tier."

      I must admit that I cannot make sense of the true nature of 2ndary TS. Is it to group TS which reside on a given logical tear, so they cannot be wrongly included into a another tier? (For example a TS which actually resides on "High Performance" cannot the wrongly added to "Low Cost"?)

      Thanks a lot
        • 1. Re: Sense of secondary tablespaces?
          You have half of it correct. A storage tier must have one primary tablespace. It can also optionally have one or more secondary tablespaces. You are correct: Assigning a tablespace as a secondary tablespace to a storage tier precludes that tablespace being assigned to any other storage tier. But in addition, it also tells the ILM Assistant that partitions that belong on that storage tier may be found not only in the primary tablespace for that storage tier, but also in any of the secondary tablespaces for that storage tier. This allows you to modify the scripts generated by the ILM Assistant and direct specific partitions to any of the secondary tablespaces assigned to that storage tier. As such, subsequent event scans will discover that the partition in question is indeed on the correct storage tier since it resides in a secondary tablespace of that storage tier. Conversely, if you simply edit the storage tier migration script to direct a partition to an arbitrary tablespace that is not associated with the correct storage tier, subsequent event scans will determine that the partition is in the wrong place, and a move partition operation would be generated for it.

          Does that help?

          Thanks, Jack
          • 2. Re: Sense of secondary tablespaces?
            Hi Tino,

            This release of the ILM Assistant only permits a single read-write tablespace and a single read-only tablespace to be assigned to a storage tier. This means that when we detect an event that requires movement onto that particular tier, we have a target tablespace in which to place the data. Well, what we were finding was that existing tables having a partitioning scheme in place immediately caused all kinds of data movement for no other reason than the partitions being in a tablespace other than the preferred tablespace for the tier. The data was technically on the correct tier, but happen to reside in a tablespace unknown to the ILM Assistant. To prevent that, we allow the user to associate existing tablespaces with a tier as secondary tablespaces. The secondary tablespaces won't receive any new partitions on behalf of data migration, but they will be considered valid tablespaces for existing data.

            Why did we do this? Well, for lack of a better excuse, time constraints. This is a short-term solution to a fairly complex problem. Ideally, we would like to allow the user to assign any number of preferred tablespaces to a tier. Then, when the ILM Assistant detects a migration event needing space on a particular tier, we would pick the best preferred tablespace to receive the data. Additionally, there would be no need for secondary tablespaces, since the user would be able indicate all preferred tablespaces. Until we solve the round-robin tablespace assignment problem, we will have to live with the single preferred tablespace and unlimited secondary tablespaces.

            Does that help?

            • 3. Re: Sense of secondary tablespaces?
              Or to put it simply

              The primary tablespace is the one used when a script has to specify the tablespace where a partition will be moved to. Before executing the script you could modify it to reference one of the secondary tablespaces.

              The secondary tablepsaces are all the other tablespaces that reside on that logical storage tier, so that the ILM Assistant knows that the data is located on the correct logical tier

              • 4. Re: Sense of secondary tablespaces?
                Thank you all for your detailed explanations, which cover my question fully....