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
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 ??
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.