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

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

  1. Gova says:

    Good article. Came in handy at the right time….Thanks.

  2. SURYA says:

    Nice article. saved my time today

  3. vupv says:

    Hi Sir
    I found in file postDBCreation.sql

    ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 51200K ,
    GROUP 4 SIZE 51200K ;

    Need we change to

    ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 51200K BLOCKSIZE 4k,
    GROUP 4 SIZE 51200K BLOCKSIZE 4k;

  4. Scott Zụ says:

    Hi Sir, i have two node in cluster. Need i run ./vmem1.sh in all node ?

  5. Scott Zụ says:

    So , could you please help step config second node . Current I login first node

    [oracle@rac1 ~]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 12 01:25:35 2015

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options

    SQL>

    but second node

    [oracle@rac2 ~]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 12 01:26:01 2015

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL>

    P/S : I see error while run ./vmem1.sh (with me is ./rac1.sh)

    SQL> host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl enable database -d rac;

    SQL> host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl start database -d rac;
    PRCR-1079 : Failed to start resource ora.rac.db
    CRS-5017: The resource action “ora.rac.db start” encountered the following error:
    ORA-01078: failure in processing system parameters
    ORA-01565: error in identifying file ‘/u01/app/oracle/admin/rac/pfile/spfilerac.ora’
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3
    . For details refer to “(:CLSN00107:)” in “/u01/app/11.2.0/grid/log/rac2/agent/crsd/oraagent_oracle/oraagent_oracle.log”.

    CRS-2674: Start of ‘ora.rac.db’ on ‘rac2’ failed
    CRS-2632: There are no more servers to try to place resource ‘ora.rac.db’ on that would satisfy its placement policy

    SQL> connect “SYS”/”&&sysPassword” as SYSDBA
    Connected.
    SQL> spool off
    not spooling currently
    SQL> exit;

    • flashdba says:

      Have you put your SPFILE in 4k storage? That won’t work. See https://flashdba.com/4k-sector-size

      I can’t really help you much right now because I’m walking the streets of Istanbul!

      • Scott Zụ says:

        Dear Sir ,

        In Oracle RAC invironment, if we change fpfile location from diskgroups to local disk :
        create spfile=’+DATA/vmem1/spfilevmem1.ora’ FROM pfile=’/u01/app/oracle/admin/vmem1/scripts/init.ora’;

        Change to

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

        What happen after to do this. Because Oracle RAC require spfile in diskgroup manage by clusterware .

        • flashdba says:

          Let’s rewind a little. Why are you doing this in the first place? This is only necessary if you are trying to use 4k storage in 4kN native mode.

          Almost all Advanced Format storage has the option of 512e emulation mode, in which none of these SPFILE problems occur. Why not use that?

          • Scott Zụ says:

            Dear Sir ,

            First time , thank you for your help.

            In the my Lap, I had got install Oracle RAC with two node and use Microsoft iSCSI Software Target for virtual SAN , all node connect to SAN via iscsi.

            After I install gird and create ASM diskgroup success. I had got install database software to step create database, I had issue “I/O request size 512 is not a multiple of logical block size” . So i find your solution from google and do by your help . But in the my Lap using Oracle RAC not stand alone.

            • flashdba says:

              Oh ok. So your choices are limited. Yiu either need to create some kind of 512 byte LUNs for a separate diskgroup which will store the SPFILEs, OCR and voting disks or you need to put them somewhere else, like on an OCFS volume. If the diskgroups you have now are using 4096 blocksize (you can check in v$asm_diskgroup) then you will not be able to store SPFILEs or voting disks in them.

  6. Scott Zụ says:

    Because spfile in location /u01/app/oracle/admin/rac/pfile/spfilerac.ora not in ASM , so second node present. I had copy /u01/app/oracle/admin/rac/pfile/spfilerac.ora to second node and restart database . So all node work fine. Thank for youre help

  7. den says:

    at 11.2.0.4 this still have problems. After I’ve folowed this article I got
    ERROR at line 1:
    ORA-19624: operation failed, retry possible
    ORA-19870: error while restoring backup piece
    /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/Seed_Database.
    dfb
    ORA-19504: failed to create file “+HX1800B4K/dbtest/system01.dbf”
    ORA-15012: ASM file ‘dbtest/system01.dbf’ does not exist
    ORA-17502: ksfdcre:5 Failed to create file +HX1800B4K/dbtest/system01.dbf
    ORA-15081: failed to submit an I/O operation to a disk
    ORA-27072: File I/O error
    Linux-x86_64 Error: 22: Invalid argument
    Additional information: 4
    Additional information: 2048
    Additional information: -1
    ORA-06512: at “SYS.X$DBMS_BACKUP_RESTORE”, line 5940
    ORA-06512: at line 16

    • flashdba says:

      What are you trying to do? Are you using storage which is configured as 512e or 4kN?

      If so, read this: https://flashdba.com/4k-sector-size/

      If your storage has the option to choose between 512e or 4kN you should choose the former, as the latter is a real pig to get working with Oracle.

      • Den says:

        I’ve read that article. In my case nothing seems to work as described. I have 4kN Ultrastar HUC101818CS4204 disks attached to LSI 9380 MegaRAID SAS controlller which can ONLY represent them as 4kN LUNs.
        On top of Oracle EL7.3 I configured 11.2.0.4 or 12.1.0.2 ASM using ASMlib 2.0.8 as UEK kernel module.
        I’ve tried database version 11.2.0.4/12.1.0.2 with latest PSU. I applied all described in your articles parameters – and all I got when trying to create datafiles on ASM is this error:
        ORA-15081: failed to submit an I/O operation to a disk
        ORA-27072: File I/O error
        Linux-x86_64 Error: 22: Invalid argument
        Additional information: 4
        Additional information: 2048
        Additional information: -1

        Until I installed 12.2 ASM and database. After that things seem to be working for now.
        I suspect that the reason why we have different results is that you used 512e LUNs for both 512e and 4kn tests.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s