Oracle 11gR2 - 2k to 8k block size

Hi, need to come up with a best possible and the fastest way/plan/approach to move a db from 2k to 8k block size, looking for the least down time approach, because export is taking almost 10 hours.

Thanks
Mushfique KhanDirector OperationsAsked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Using exp and imp (or expdp and impdp) and creating a new database is the only way to change the database block size.  Yes, Oracle will allow you to create new tablespaces in an existing database with a different block size than the default there, and you can move all user-created objects to the new tablespaces.  But, that still leaves you with the SYSTEM tablespace at the original block size.

We ran our production database for many years with two different block sizes: 2k for a number of small code tables and their indexes; and 8k for the larger tables and indexes.

I would challenge the client though to demonstrate why they actually need a different block size in the Oracle database.  Yes, different block sizes can affect performance and space utilization.  But, usually applications will tolerate whatever block size the database uses, and usually the applications will not have any direct interaction with the database block size.
0
 
slightwv (䄆 Netminder) Commented:
Can I ask the reason behind why you need to do this?

Only way to change block sizes is rebuild the database.

You might look at replication to help:  
Set up a duplicate with the new block size, let the databases replicate data then migrate the apps to the new one gradually.  Eventually you can turn off the old one.

Depending on your version of Oracle, you 'can' build different tablespaces with different block sizes but there isn't a lot of good posts suggesting you EVER do it.
0
 
DavidSenior Oracle Database AdministratorCommented:
Export, instead of datapump, instead of rman?  Consider starting with rebuilding your indexes into a dedicated tablespace with 16k blocks.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
slightwv (䄆 Netminder) Commented:
>>into a dedicated tablespace with 16k blocks.

See my comment above:  Depending on your version, you can mix block sizes between tablespaces BUT no one ever suggests you actually do it.

Just because you can doesn't mean you should.
0
 
johnsoneSenior Oracle DBACommented:
I have mixed tablespace block sizes with some success.  I did it in the only place I ever saw a recommendation for it, which was with LOBs.  Kept the I/O down when accessing the LOBs.

As far as limiting down time to change things, I think that it is the best way to go.  You should be able to create the new tablespaces and then be able to use ALTER TABLE ... MOVE and ALTER INDEX ... MOVE to get things to where they need to go.

However this will only change the database block sizes on user objects.  You cannot change the block size in the SYSTEM tablespace using this methodology.

Not sure exactly why you want to change it though.
0
 
Mushfique KhanDirector OperationsAuthor Commented:
First of all ... thanks a lot to: slightwv, dvz & johnsone ... thank you Gurus

Here is the request, obviously from the client, we have to make it only 8k block size, because of some media or application requirements.

Please suggest, keeping the export timing, was thinking of cloning it to a new server (as they have sufficient resource) using RMAN backup and then just switch to this new 8k db on a different box.

What do you think, is this good/appropriate or going south :-(

Thanks once again :-)
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
You cannot 'switch' block sizes.  The database needs to be created with it.
0
 
Mushfique KhanDirector OperationsAuthor Commented:
Means have only export/import option, can't use RMAN backup? please guide, what other options I've have, to fix this?
0
 
slightwv (䄆 Netminder) Commented:
I don't see how RMAN will help you change a block size.

>> please guide, what other options I've have, to fix this?

I already gave my suggestions.

If your tests with export was the classic, exp, you might try datapump.  It is supposed to be a lot faster.
0
 
Mushfique KhanDirector OperationsAuthor Commented:
I'm talking about expdp & impdp only, not the old export, but this is the only option, if would like to change the db block size, just would like to have confirmation on this.

Then only will work on expdp/impdp, how to improve it's performance and reduce the duration, as much as possible.
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
This may save down time, but definitely not clock time.

Create the new tablespaces in the existing database with the new block sizes.  Then move the objects into the new tablespaces.  Use transportable tablespaces to unplug them from the current database and plug them in to the new one with the new block size.  You would have to handle transferring everything else (users, privs, packages/functions/procedure, etc).

Not sure it is a good solution, just a different one.

If you want to break it up, you could move objects to the new database with exp/imp and have database links and synonyms in the old database to point to them to make it seamless.  But, you will most likely see a performance impact of doing it this way.
0
 
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
RMAN - thrown in to suggest a non-export alternative

What we're suggesting, mkhan, is to set up tablespaces of differing block sizes, a feature of 9i and above.  This solution is viable, although it does require additional memory parameters for the non-default sizes.  One of the Oracle authors has this write-up, but you should be reading the Oracle docs for specifics.

You could also read this doc on performance and compare it against your site.  For example, are you employing parallel streaming?  Are you backing up historical data or partitioned tables that are fully static?  And are you exporting across your LAN to another host?
0
 
Mushfique KhanDirector OperationsAuthor Commented:
You folks rock ... great thanks a lot, I'll wait a bit and then will close & distribute accordingly, but really you guys are amazing ... thanks a lot Thank You ... to all of you :)
0
 
slightwv (䄆 Netminder) Commented:
I agree with markgeer and even asked in the first post:  What is the real reason behind wanting to do this in the first place?

Many times there is a perceived need that actually doesn't mean anything in the real world.

I've been around way too long (so have some of the other Experts participating in this question) and seen too many people make false claims.  Whatever the reason behind the claims, it's hard to argue with reality.

Although, I have lost some debates over the years even with facts!  No matter how hard you try and how many facts you have, some people will just NEVER believe the world is round...
0
 
johnsoneSenior Oracle DBACommented:
Honestly the only valid reason I see for doing it (and it really isn't that valid) is that the SAN is set up with an 8K block size.  It would cut down on logical reads, but they are logical reads as the block is already been physically read and the time involved is negligible (and definitely not worth the effort to rebuild an entire database).
0
 
Mushfique KhanDirector OperationsAuthor Commented:
Give me some time, will close soon, thanks for the patience.
0
 
DavidSenior Oracle Database AdministratorCommented:
At your convenience :)
0
 
Mushfique KhanDirector OperationsAuthor Commented:
Thanks dvz :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.