Results 1 to 2 of 2

Thread: Archiving

  1. #1
    Join Date
    Sep 2002
    Posts
    4

    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
    Raghu

  2. #2
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    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,

    clio_usa
    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
  •