修改controlfile block size

我们知道，缺省情况下，controlfile 的block size为16k
SQL> select * from v$version where rownum <=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

[oracle@localhost goolen]$ dbfsize control01.ctl 
Database file: control01.ctl
Database file type: file system
Database file size: 596 16384 byte blocks

SQL> col name for a55
SQL> set lines 120
SQL> select name,block_size from v$controlfile;
NAME                                                    BLOCK_SIZE
------------------------------------------------------- ----------
/opt/app/oracle/oradata/goolen/control01.ctl                 16384
/opt/app/oracle/oradata/goolen/control02.ctl                 16384

现在有个问题，controlfile的block size也能像datablock 一样可以更改吗？
下面我们来测试一下
参数_controlfile_block_size可以修改控制文件的块大小
SQL> alter system set "_controlfile_block_size"=32768 scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2213696 bytes
Variable Size            1073744064 bytes
Database Buffers          486539264 bytes
Redo Buffers                7512064 bytes

[oracle@localhost goolen]$ rm -rf control0*

改完后重启实例，需要重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "GOOLEN" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/app/oracle/oradata/goolen/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/opt/app/oracle/oradata/goolen/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/opt/app/oracle/oradata/goolen/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    '/opt/app/oracle/oradata/goolen/system01.dbf',
 13    '/opt/app/oracle/oradata/goolen/sysaux01.dbf',
 14    '/opt/app/oracle/oradata/goolen/undotbs01.dbf',
 15    '/opt/app/oracle/oradata/goolen/goolen01.dbf'
 16  CHARACTER SET AL32UTF8
 17  ;

Control file created.

数据库能正常open

SQL> alter database open;

Database altered.

SQL> select name,block_size from v$controlfile;

NAME                                                    BLOCK_SIZE
------------------------------------------------------- ----------
/opt/app/oracle/oradata/goolen/control01.ctl                 32768
/opt/app/oracle/oradata/goolen/control02.ctl                 32768

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.
这个只是出于好奇，才做的测试，生产中不要轻易去修改，没有太大的意义