Solved

Oracle 11gR2 - 2k to 8k block size

Posted on 2014-02-10
18
2,266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 77

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 77

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

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 77

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 77

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 35

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 77

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 35

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

724 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