Determine and set sizing parameters for database structures

You can determine/set parameter sizes using Enterprise Manager's Server Tab-> Storage section. This is first section of configuration of database structure, and second is parameters form parameter file.

 You can check by SQL Plus.
SQL> show parameter db_block_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
db_block_size                        integer     8192
SQL>
If there is expectation to change the size of database block it would be for new tablespace, and we can do it while creating tablespace. First we need to allocate memory for that block size and we do it with following command:
 It’s good when you use ASM and OMF that command is enough for creating tablespace with default values.

SQL> alter system set db_16K_cache_size=4m;
System altered.
SQL> create tablespace test blocksize 16k;
Tablespace created.
SQL>

Now to modify storage structure, we will first modify size of redo logs. How we do it? We need to drop redo log groups and create new one’s. First to see which size are our redo log groups.

SQL> SELECT bytes/1024/1024 "Size in MB" FROM v$log;
Size in MB
----------
       100
       100
       100

In our case it is 100MB we will recreate redo log groups with size of 150MB. And we now need to check which redo log groups are inactive, because we can drop only inactive redo log groups.

SQL> SELECT group#, status FROM v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

In our case we can drop redolog group  2 and 3. We will drop redolog group 3 and create it with size of 150MB.

 SQL> ALTER DATABASE DROP LOGFILE group 3;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE group 3 size 150M;
Database altered.

SQL>    SELECT bytes/1024/1024 "Size in MB" FROM v$log;
Size in MB
----------
       100
       100
       150
Now select returns size of 150MB for third redolog group and 100MB for other two groups, yes we can have redolog groups with different sizes.
Now we follow same steps for second redolog group
SQL> ALTER DATABASE DROP LOGFILE group 2;
Database altered.


SQL> ALTER DATABASE ADD LOGFILE group 2 size 150M;
Database altered.


And for last redolog group we need to make logswitch to make it inactive. So we first execute and after that check status of redolog group
SQL> alter system switch logfile;
System altered.

SQL> SELECT group#, status FROM v$log;
    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 UNUSED
in our case first redolog group was still active. In that case we need to execute alter system checkpoint to make it inactive

SQL> alter system checkpoint;
System altered.

SQL> SELECT group#, status FROM v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED
Now we can drop and recreate last redolog group with size of 150MB.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 150M;
Database altered.

SQL> select bytes/1024/1024 from v$log;

BYTES/1024/1024
---------------
            150
            150
            150
And we finaly resized all redolog groups.