Solved

Oracle 11gR2 - 2k to 8k block size

Posted on 2014-02-10
18
1,992 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
 
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
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 
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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now