Results 1 to 2 of 2

Thread: Archiving

  1. #1
    Join Date
    Sep 2002

    Unanswered: Archiving

    I am presently working on an OLTP application that will add
    about 2G of data a day.
    Most of the insert/update activity will be on current data and enquiry on
    history data.
    We are planning on partitioning the data by date and making the history partitions(-10days) Read-only.
    After 6 months we will have about 20 active partitions and 120 readonly.
    Which is better from a recovery/operations perspective.
    a. Having everything in one instance
    Making the current 10 partitions insert/updateable
    Making the reas of them readonly.
    Periodically marking old partitions readonly
    b. have 2 instances.
    Let the active instance have
    the partition with insert/update activity
    Let the history instance have the inquiry only partitions.
    Manually move partitions from the Active instance to
    the history partition.

    I am concerned that the single instance approach
    will make recovery/manageability tougher.
    Any thoughts ??

    Thanks in advance

  2. #2
    Join Date
    Apr 2002
    California, USA


    Well, as you already said two servers would be better since you can spread your load across two servers, and have the historic queries use only the one server.

    You can also tune (larger block size, sort_area, rollback segments, etc) for the historic data db, and optimize your OLTP for better performance.
    You should also place each partition from the OLTP into separate tablespace, so you can do transportable tablespaces to the historic db very quickly.

    Go for the two databases and you will have better performance and easy administration down on the road.

    Hope that helps,

    OCP - DBA


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts