| DB Moving [message #481066] |
Fri, 29 October 2010 20:41  |
Tr0cken
Messages: 22
Registered: August 2010
|
Junior Member |
|
|
Hi, we are currently working with a 10.2.0.1 DB running on CentOs 4.7 that has grown too big (400GB), so what we want to do is:
- Move it to a new server running CentOs 5.5 (with more RAM, disks and processor).
- Upgrade it 10.2.0.4
- Make use of table partitioning
- Make use of ASM instead of filesystem (would you recommend this?)
Can you tell me what would be the order in which we must do this upgrades/mods and what to take into account?
Thank you!
|
|
|
|
|
|
| Re: DB Moving [message #481073 is a reply to message #481069] |
Fri, 29 October 2010 23:35  |
Tr0cken
Messages: 22
Registered: August 2010
|
Junior Member |
|
|
BlackSwan wrote on Fri, 29 October 2010 21:39>Can you tell me what would be the order in which we must do this upgrades/mods and what to take into account?
How much down time can occur during this effort?
I realize *ZERO* is the most preferred, but is it a challenge to change a race car's tire while it continues around the track.
Max downtime can be 8 hours.
|
|
|
|
| Re: DB Moving [message #481080 is a reply to message #481066] |
Sat, 30 October 2010 04:01 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
An eight hour window should mean that you can do it with a network mode Data Pump job. That means you can accomplish everything in one operation.
Furthermore, you can test as often as you want: this is particularly important with an implementation of partitioning. You'll need to do many tests to determine the appropriate partitioning strategy: get partitioning right, and the results can be excellent; get it wrong, and it may be disastrous. With regard to ASM, I would say "definitely", and of course use 11.2 ASM, it is far superior to 10g.
That's the approach I would try - other people may have different ideas. I'm sure I'm not the only person who would be interested to see your final project plan and the results.
|
|
|
|
| Re: DB Moving [message #481113 is a reply to message #481080] |
Sun, 31 October 2010 01:39 |
Tr0cken
Messages: 22
Registered: August 2010
|
Junior Member |
|
|
So far the plan is this:
1) Install DB in new server with ASM (If upgrade to 11g is not free, then I'll have to stick with 10g. I'll ask our sales representative on monday).
2) Import full DB with impdp network_link
3) Upgrade to 10.2.0.4
4) Partition tables
Any objections or suggestions?
Thanks!
|
|
|
|
|
|
| Re: DB Moving [message #481125 is a reply to message #481113] |
Sun, 31 October 2010 10:30 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - your steps 1-2-3 are in the wrong order, it should be 1-3-2: you should apply the latest patch set (which is 10.2.0.5, I think?) immediately after installing the new Oracle home and before importing the database.
Also, how do you intend to implement partitioning? If you do it after the migration to the new environment, you will have massive downtime. Which is why I suggested importing into partitioned structures, so that everything is done in one operation.
Of course, you might find that you don't need to partition any tables after the move. Why do you think you need partitioning, anyway?
|
|
|
|
| Re: DB Moving [message #481221 is a reply to message #481125] |
Mon, 01 November 2010 22:19 |
Tr0cken
Messages: 22
Registered: August 2010
|
Junior Member |
|
|
Quote:Hi - your steps 1-2-3 are in the wrong order, it should be 1-3-2: you should apply the latest patch set (which is 10.2.0.5, I think?) immediately after installing the new Oracle home and before importing the database.
I wasn't aware of this patch. I've downloaded it and I'll be testing it shortly, thanks!
Quote:Also, how do you intend to implement partitioning? If you do it after the migration to the new environment, you will have massive downtime. Which is why I suggested importing into partitioned structures, so that everything is done in one operation.
How should I do this if I want to make a tablespace for each partition? Is using the QUERY clause the correct way?
Quote:Of course, you might find that you don't need to partition any tables after the move. Why do you think you need partitioning, anyway?
I have a table that stores transactions and currently has 360 millions of rows. Reports are taken mostly from this table, so I would like to partition it by range (4 quarters). Do you think it would help the performance?
Thanks for your help!
|
|
|
|
| Re: DB Moving [message #481249 is a reply to message #481221] |
Tue, 02 November 2010 03:43 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:How should I do this if I want to make a tablespace for each partition? Is using the QUERY clause the correct way? No. If you import into partitioned structures, Oracle will sort it out.
Quote:I would like to partition it by range (4 quarters) Have you thought this through? Why 4 partitions, and not 400? What about the indexes?
Quote:Do you think it would help the performance? You tell me. What are you looking for? Partitionwise joins, for instance? Or partition pruning? Or better parallelism? It is not something to decide without being clear on what your problem is, what you want to achieve, and why a partitioning strategy would help.
That's all I can say on this without any information.
|
|
|
|
| Re: DB Moving [message #481322 is a reply to message #481249] |
Tue, 02 November 2010 07:49 |
Tr0cken
Messages: 22
Registered: August 2010
|
Junior Member |
|
|
John Watson wrote on Tue, 02 November 2010 03:43
You tell me. What are you looking for? Partitionwise joins, for instance? Or partition pruning? Or better parallelism? It is not something to decide without being clear on what your problem is, what you want to achieve, and why a partitioning strategy would help.
Well the table that I want to partition contains lots of data (4 years) but only the current month of the year gets updated (and updatable) data. All the other months' data are left read only for ever.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
