Friday, December 02, 2011

ORA-00209

I was asked to move a database from a Linux server to a Solaris server today. Nobody knows who created the source database. The last update to the parameter file was made in 2006. The person who did it used one of those sample init.ora files and made the necessary changes for the database in question. However, for some reason he (or she, as we have had a couple of female DBA’s where I work in the past) duplicated a large part of the parameter file so it contained 2 copies of several parameters! To avoid confusion, the parameter file for the target database only has one copy of each parameter. However, the spare parameters in the source database never caused a problem. Oracle simply used the value from the last copy of any given parameter. So , if your parameter file includes the following:
 
open_cursors=100
open_cursors=200
 
When you open the database, it will look like this:
 
SQL> l
  1  select value from v$parameter
  2* where name = 'open_cursors'
SQL> /
 
VALUE
------------------------------
200
 
SQL>
 
It even seems to be able to handle the situation where the first parameter is wrong. So if your parameter file includes the following:
 
db_block_size=4096
db_block_size=8192
 
When you open the database, it will look like this:
 
SQL> select value from v$parameter
  2  where name = 'db_block_size';
 
VALUE
------------------------------
8192
 
SQL>
 
Obviously, if you put the incorrect parameter last, the database will not open. So, if your parameter file includes the following:
 
db_block_size=8192
db_block_size=4096
 
Your database will not open:
 
SQL> startup
ORACLE instance started.
 
Total System Global Area   76518176 bytes
Fixed Size                   733984 bytes
Variable Size              67108864 bytes
Database Buffers            8388608 bytes
Redo Buffers                 286720 bytes
ORA-00209: control file blocksize mismatch, check
alert log for more info
 
SQL>

No comments:

Post a Comment