Installing Oracle Database 11.2.0.3 single-instance using 4k sector size

At the release of Oracle 11g Release 2 support was finally added for 4k sector storage devices. The storage industry has been trying to move away from 512 byte devices towards 4k for some years now, with the adoption of Advanced Format being increasingly commonplace. For more background on this, read my page on 4k sectors.

Unfortunately, as is often the case, Oracle’s implementation of 4k wasn’t completed throughout the software. There is support in the places that matter, in other words ASM diskgroups and database redo logs, but these new features still cannot be used via the Oracle Universal Installer (OUI) or the Database Configuration Assistance (DBCA).

If you are installing Grid Infrastructure and want to use 4k sectors, check out the various installation cookbooks which document the entire process. Once you have Grid Infrastructure installed, it’s time to install the database.

Remember though, that none of this is mandatory. You can install Oracle on Violin using a 512 byte blocksize just using the basic OUI and DBCA. If, however, you choose to attempt a 4k install using OUI or DBCA you will hit error messages. Here’s the sort of message you will see:

ORA-01078: failure in processing system parameters
ORA-15081: failed to submit an I/O operation to a disk
ORA-27091: unable to queue I/O
ORA-17507: I/O request size 512 is not a multiple of logical block size
ORA-06512: at line 4

As with Grid Infrastructure, the solution is to perform a software-only install and then build the database from DBCA using the scripts option – which isn’t that arduous. Here’s a demonstration of installing Oracle 11.2.0.3 (single-instance) on ASM using a 4k sector size – you should be able to adapt this for most versions and combinations of products / operating systems. The main thing to understand is that if your operating system understands 4k sectors (as Linux kernels of 2.6.32 and above do) and you present the LUNs as 4k, you don’t need to bother with the _disk_sector_size_override parameter. If, on the other hand, you are presenting the LUNs as 512 byte for some reason, you need the underscore parameter in both ASM and the database instances. If you aren’t sure, my preference is to add the parameter anyway…

Install Database Software and Create Oracle Database

In this example the Grid Infrastructure home is all setup, so now it is time to install the RDBMS home. I am going to do a software only install and then build the database manually so that I can setup the correct _disk_sector_size_override parameter in the spfile and create the redo logs as having a 4k blocksize. There is also an additional issue in that by default the OUI or DBCA automatic database build will put the spfile into ASM. However, there is a chicken-and-egg situation here because the ASM diskgroups are using a 4k sector size which means the database will not be able to read them without the _disk_sector_size_override parameter being set… and if the parameter is set in the spfile inside ASM then it can never be read.

There are two obvious solutions to this: locate the spfile outside of ASM in a filesystem or create a single non-4k sector size ASM diskgroup just for the spfile. If I was installing a clustered system I would probably want to do the latter so that the spfile is in a shared location, but since this is a standalone server I will opt for just putting the spfile in /u01/app/oracle/admin/<ORACLE_SID>/pfile

At this point the software is already installed using the same “Software Only” option as I used when installing Grid Infrastructure. I could create my database using SQL, or I could use DBCA to create scripts and then edit them. I’m going to go for the latter. DBCA allows me to choose which Database Template I want to use, so for this example I am going to choose the default General Purpose or Transactional Processing template:

I have kept most of the defaults throughout the creation process – the only one I changed was the use of Automatic Memory Management, which for now I have disabled and gone with Manual Memory Management (because I don’t have the correct /dev/shm tmpfs filesystem in place – and because to be honest I still don’t trust it).

When I get to the Creation Options screen I am going to deselect the option to Create Database and instead choose Generate Database Creation Scripts:

Clicking on Finish will create a set of scripts in the location $ORACLE_BASE/admin/$ORACLE_SID/scripts which I can now edit to allow the database to work with 4k sectors:

[oracle@oel57]$ cd /u01/app/oracle/admin/vmem1/scripts/
[oracle@oel57]$ ls -l
total 9580
-rw-r----- 1 oracle oinstall    1903 Mar 19 16:22 cloneDBCreation.sql
-rw-r----- 1 oracle oinstall     828 Mar 19 16:22 CloneRmanRestore.sql
-rw-r----- 1 oracle oinstall    2005 Mar 19 16:22 init.ora
-rw-r----- 1 oracle oinstall    2099 Mar 19 16:22 initvmem1TempOMF.ora
-rw-r----- 1 oracle oinstall    2041 Mar 19 16:22 initvmem1Temp.ora
-rw-r----- 1 oracle oinstall     508 Mar 19 16:22 lockAccount.sql
-rw-r----- 1 oracle oinstall    1347 Mar 19 16:23 postDBCreation.sql
-rw-r----- 1 oracle oinstall     674 Mar 19 16:22 postScripts.sql
-rw-r----- 1 oracle oinstall    1459 Mar 19 16:22 rmanRestoreDatafiles.sql
-rw-r----- 1 oracle oinstall 9748480 Mar 19 16:22 tempControl.ctl
-rwxr-xr-x 1 oracle oinstall     523 Mar 19 16:22 vmem1.sh
-rwxr-xr-x 1 oracle oinstall    1169 Mar 19 16:22 vmem1.sql

The first thing I notice is that there are three different *.ora parameter files. I need to add the _disk_sector_size_override parameter to these so that I do not have any issues accessing the 4k sector-based ASM diskgroups:

[oracle@oel57]$ for file in `ls -1 *.ora`; do
> echo _disk_sector_size_override=TRUE >> $file
> done

Next the statement which creates the database (and therefore the online redo logs) needs to be amended to include the new BLOCKSIZE clause. There are two database creation statements in the file cloneDBCreation.sql so I need to amend both of these three times each, one for each log file, to add BLOCKSIZE 4k:

Create controlfile reuse set database "vmem1"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'&&file0',
'&&file1',
'&&file2',
'&&file3'
LOGFILE GROUP 1 SIZE 51200K BLOCKSIZE 4k,
GROUP 2 SIZE 51200K BLOCKSIZE 4k,
GROUP 3 SIZE 51200K BLOCKSIZE 4k RESETLOGS;

Now finally I need to edit the files postDBCreation.sql and vmem1.sql to change the location of the spfile, because this cannot reside in my ASM diskgroups if they have 4k sectors.

So postDBCreation.sql changes this line from:

create spfile='+DATA/vmem1/spfilevmem1.ora' FROM pfile='/u01/app/oracle/admin/vmem1/scripts/init.ora';

To this:

create spfile='/u01/app/oracle/admin/vmem1/pfile/spfilevmem1.ora' FROM pfile='/u01/app/oracle/admin/vmem1/scripts/init.ora';

Whilst in the file vmem1.sql I need to change the following two lines in the same way, so this:

host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add database -d vmem1 -o /u01/app/oracle/product/11.2.0/dbhome_1 -p +DATA/vmem1/spfilevmem1.ora -n vmem1 -a "DATA,RECO"
host echo "SPFILE='+DATA/vmem1/spfilevmem1.ora'" > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initvmem1.ora

Now becomes this:

host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add database -d vmem1 -o /u01/app/oracle/product/11.2.0/dbhome_1 -p /u01/app/oracle/admin/vmem1/pfile/spfilevmem1.ora -n vmem1 -a "DATA,RECO"
host echo "SPFILE='/u01/app/oracle/admin/vmem1/pfile/spfilevmem1.ora'" > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initvmem1.ora

So the spfile is now going to be created under /u01/app/oracle/admin/vmem1/pfile – and since that doesn’t exist right now I’ll create it:

[oracle@oel57]$ mkdir -p /u01/app/oracle/admin/vmem1/pfile

Everything is now ready so I will create the database by running the script:

[oracle@oel57]$ ./vmem1.sh

At one point there are a couple of warnings about the resource already existing in CRS, which can be ignored:

SQL> host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl enable database -d vmem1;
PRCC-1010 : vmem1 was already enabled
PRCR-1002 : Resource ora.vmem1.db is already enabled

Since I left the option to create a DBConsole instance checked when I was in DBCA, I also get a couple of warnings about the DBSNMP and SYSMAN accounts being locked – again these can be ignored:

Password for DBSNMP user:
Mar 19, 2012 5:18:13 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
WARNING: ORA-28000: the account is locked
Password for SYSMAN user:
Mar 19, 2012 5:18:24 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
WARNING: ORA-28000: the account is locked

That’s it, the database is created and open. If I check the v$log view I can see the 4k block size:

SQL> select group#, bytes, blocksize, members from v$log;

    GROUP#      BYTES  BLOCKSIZE    MEMBERS
---------- ---------- ---------- ----------
         1   52428800       4096          2
         2   52428800       4096          2
         3   52428800       4096          2

This database is now ready to use, although of course it could probably do with more than just three measly 50M logfiles… and some of the default parameters chosen by DBCA are a bit odd. But that’s the end of the installation process. The final thing I would usually do is install SLOB and see what sort of IOPS I can drive from my array. And in fact I’ve been doing precisely that just here:

SLOB using Violin 6616 on Fujitsu Servers