Changing the Database Block size

CHANGING THE DATABASE BLOCK SIZE

We have a chance to change the Database Block size by using the parameter db_block_size. But we can’t change the block size by defining this parameter in init.ora file, because db_block_size parameter is a non-tunable parameter.
This is possible by using traditional EXPORT/IMPORT (or) through DATAPUMP.
Now look for an example, to change the db_block_size of a PROD database from 8k to 4k by using traditional EXPORT/IMPORT.
Follow the below steps.

PREREQUISITES :

1. We need to create an empty database.
2. After creating a Database, 2 situations comes into picture.
If the file system for the prod database and the newly created database is same, its OK.
But if the file system is different for both the databases, then we had to create the Tablespaces in the new database as same as which are existed in PROD database.

STEPS :

We need to export the entire the Database by using EXPORT utility.
[prod@linux ~]$exp system/manager file=full.dmp log=full.log full=y
Now the Database Backup is taken into full.dmp file and the information is logged into the full.log file.
Now create the new Database by changing the db_block_size parameter. Copy the PROD database init.ora file to target location.

[new@linux ~]$vi initprod.ora
db_name=prod
………………………………
db_block_size=4098
Now connect to sqlplus and create new database.
Now copy the dump file from source to target location.
[prod@linux ~]$ cp full.dmp /oraAPP/new/.
Now we need to import the dump file. If the filesystem is same issue below command. Else create the same tablespaces of PROD database in the new database.
[new@linux ~]$imp system/manager file=full.dmp log=full.log full=y

Atlast a new database is created with all existing features of PROD database except with db_block_size.