5 Replies Latest reply on May 29, 2013 3:09 PM by krishan Jaglan

    Why the sepaete undo is required for separate instance?

    user10745179
      Hi,

      Im new to oracle rac,we are using the oracle 11.2.0.2.0 on AIX 6,

      We have the RAC setup installed. i have one doubt,

      Why the sepaete undo is required for separate instance?

      & why only single temp tablespace for all instances.?

      Thanks
        • 1. Re: Why the sepaete undo is required for separate instance?
          Mahir M. Quluzade
          user10745179 wrote:
          Hi,

          Im new to oracle rac,we are using the oracle 11.2.0.2.0 on AIX 6,

          We have the RAC setup installed. i have one doubt,

          Why the sepaete undo is required for separate instance?

          & why only single temp tablespace for all instances.?

          Thanks
          Hi,

          As you know, on Oracle RAC have 2 or more instance. Every instance is contains SGA and Background processes.
          Every blocks changes on SGA (buffer). Need this changes store UNDO. So need every instance (node) own UNDO tablespace
          and online redo logs.

          Why need TEMP tablespace? Temp tablespace is using for sort operations.
          Sort operations is come from SQL to database. It is not depends with instance.
          Then 1 TEMP table space is enough for database.

          Regards
          Mahir M. Quluzade
          1 person found this helpful
          • 2. Re: Why the sepaete undo is required for separate instance?
            user10745179
            Hi Mahir,

            Thanks for quick response,

            but please clear me, if


            Every blocks changes on SGA (buffer). Need this changes store UNDO. So need every instance (node) own UNDO tablespace
            and online redo logs.

            As undo & redo are stored on shared storage, if one instance fails, Other instance can read the contents from undo & undo the transaction,

            Please correct me,
            Im in big confusion,

            Thanks
            • 3. Re: Why the sepaete undo is required for separate instance?
              rarain
              Hi,


              Excerpt from Oracle documentation.

              Oracle RAC automatically manages undo segments within a specific undo tablespace that is assigned to an instance. Only the instance assigned to the undo tablespace can modify the contents of that tablespace. However, each instance can read the undo data blocks created by any instance. Also, when performing transaction recovery, any instance can update any undo tablespace, if that undo tablespace is not currently being used by another instance for undo generation or transaction recovery.

              Thanks
              1 person found this helpful
              • 4. Re: Why the sepaete undo is required for separate instance?
                Mahir M. Quluzade
                user10745179 wrote:
                Hi Mahir,

                Thanks for quick response,

                but please clear me, if


                Every blocks changes on SGA (buffer). Need this changes store UNDO. So need every instance (node) own UNDO tablespace
                and online redo logs.

                As undo & redo are stored on shared storage, if one instance fails, Other instance can read the contents from undo & undo the transaction,

                Please correct me,
                Im in big confusion,

                Thanks
                Yes,

                1. All nodes available.

                2. One or more RAC instances fail.

                3. Node failure is detected by any one of the remaining instances.

                4. Global Resource Directory(GRD) is reconfigured and distributed among
                surviving nodes.

                5. The instance which first detected the failed instance, reads the failed
                instances redo logs to determine the logs which are needed to be recovered.
                The above task is done by the SMON process of the instance that detected failure.

                6. Until this time database activity is frozen, The SMON issues recovery requests
                for all the blocks that are needed for recovery. Once all the blocks are available,
                the other blocks which are not needed for recovery are available for normal processing.

                7. Oracle performs roll forward operation against the blocks that were modified by the
                failed instance but were not written to disk using redo log recorded transactions.

                8. Once redo logs are applied, uncomitted transactions are rolled back using
                undo tablespace.

                9. Database on the RAC in now fully available.

                Regards
                Mahir M. Quluzade
                1 person found this helpful
                • 5. Re: Why the sepaete undo is required for separate instance?
                  krishan Jaglan
                  Hi user10745179,

                  You got very genuine question and to be frank technically Oracle designer had two options

                  1) Separate undo tablespace for each Instance
                  2) One Big Undo tablespace for all RAC instances

                  and they have choosen option 1. It make sense to me as i see following quick reason over single tablespace.
                  1) make sense as it help to avoid contention ( I/O for each Instance changes goes to different area ) and
                  2) may be some locking issues.
                  3) This also avoid high cache fusion of undo blocks (block transfer between nodes).
                  4) etc.

                  be aware undo tablespace have little rollback segments in its not a single segment. they(Oracle) could always implement these feature's having single tablespace but lets not go there.
                  thanks
                  krishan