Re: Exact steps to change DB_BLOCK_SIZE

vasarpota_at_my-deja.com wrote:
>Thank you for the information but do you have a step by step
 breakdown
>of the process of changing block size and recreatig a database
 so that
>I dont make a mistake
>
>
>In article <8l4f71$nv1$1_at_nnrp1.deja.com>,
> oratune_at_aol.com wrote:
>> In article <8l4ddu$mff$1_at_nnrp1.deja.com>,
>> vasarpota_at_my-deja.com wrote:
>> > Does anyone know the exact procedure (if there is a white
 paper) to
>> > resize the Oracle Block Size. And what considerations
 should be
 taken
>> > (e.g. larger data buffer, etc)
>> >
>> > Thanks for your help
>> >
>> The only way to reset the DB_BLOCK_SIZE is to recreate the
 database.
 As
>> the most common db_block_size values are powers of 2 (2K, 4K,
 8K, 16K,
>> 32K, etc.) dividing the db_block_buffers value by (new
 DB_BLOCK_SIZE /
>> old DB_BLOCK_SIZE) is the usual adjustment. For example, if
 you are
>> running with a 2K block size and a db_block_buffers value of
 20000 and
>> you increase the db_block_size to 8k the db_block_buffers
 value would
 be
>> adjusted to 5000 -- 20000/(8/2) == 20000/4 == 5000. Also the
>> db_file_multiblock_read_count value should be adjusted by the
 same
>> factor. If this value is 16 for a 2K block size then the 8k
 block
 size
>> would require this to be reduced to 4 (16/4 == 4). Should
 this be set
>> you will also need to adjust the db_file_direct_io_count
 value.
>>
>> I would have a reliable export of the current instance before
 beginning
>> this task since all datafiles will need to be removed and
 recreated.
>>
>> --
>> David Fitzjarrell
>> Oracle Certified DBA
>>

The Oracle Concepts manual and the Installation Guide both talk about the steps necessary to create a database. You should review them.

Some general guidelines since you are going to re-create an existing instance:

save spool output from each of the following:

select * from sys.dba_tablespaces
select * from sys.dba_data_files
select * form v$logfile

And 'alter database backup control file to trace;' and get the trace file.

Determine whether you are going to manually create the db or use the Oracle installation program. Prepare scripts as necessary (create database with @catalog and @catproc; create tablespaces)

Shutdown the existing db using immediate Start it in restricted mode and make a full export Shutdown normal
Make a cold backup for worst case diaster recovery Remove all existing database files
Recreate the db using the Oracle installation program or your manual script to create the system tablespace Run catalog and catproc
Optional recreate the remaining tablespaces [can be done via imp] Run imp full=y

Verify that your database is small enough to fit in one exp file. On some systems exp has a 2G export file size limitation depending on Oracle and OS versions. See your manual for OS specific limitations. Otherwise you will need to use multiple export files.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 20 2000 - 00:00:00 CDT