Solved

Oracle 11gR2 - 2k to 8k block size

Posted on 2014-02-10
18
2,098 Views
Last Modified: 2014-02-17
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
0
Comment
Question by:mkhandba
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39849047
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
 
LVL 23

Expert Comment

by:David
ID: 39849109
Export, instead of datapump, instead of rman?  Consider starting with rebuilding your indexes into a dedicated tablespace with 16k blocks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39849125
>>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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 34

Expert Comment

by:johnsone
ID: 39849892
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
 

Author Comment

by:mkhandba
ID: 39850555
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 39850569
You cannot 'switch' block sizes.  The database needs to be created with it.
0
 

Author Comment

by:mkhandba
ID: 39850606
Means have only export/import option, can't use RMAN backup? please guide, what other options I've have, to fix this?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39850624
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
 

Author Comment

by:mkhandba
ID: 39850634
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
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 125 total points
ID: 39850685
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
 
LVL 23

Assisted Solution

by:David
David earned 125 total points
ID: 39850796
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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 125 total points
ID: 39850847
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
 

Author Comment

by:mkhandba
ID: 39851868
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39851918
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
 
LVL 34

Expert Comment

by:johnsone
ID: 39853111
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
 

Author Comment

by:mkhandba
ID: 39862080
Give me some time, will close soon, thanks for the patience.
0
 
LVL 23

Expert Comment

by:David
ID: 39862154
At your convenience :)
0
 

Author Comment

by:mkhandba
ID: 39862158
Thanks dvz :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question