Wednesday, 1 August 2012

Creating Oracle 10g RAC database on 11g Grid Infrastructure

In Oracle 11g r2 grid Infrastructure software the ASM and clusterware has been combined into one Oracle home and ASM is part of  clusterware. Clusterware files such as OCR and Voting disks are created under ASM diskgroups. Unlike, In Oracle 10g where voting disks and OCR must be configured before ASM installation they were either part of OCFS(linux) or shared raw filesystem(Solaris).

Please check Pre-11g database version compatibility with 11g Grid Infrastructure before Installing for more information please check MOS ID: 337737.1.

The following certification matrix is from MOS ID : 337737.1
I will be installing 10rR2 (10.2.0.4) Database in 11g R2 Grid Infrastructure. I am assuming that you have already installed 11gR2 Grid Infrastructure and ASM is up and running.

$srvctl status asm
ASM is running on node1, node2

Go to Oracle Database software location and Install database software(10.2.0.1).

$ /usr/orasw/10g_software/database/runInstaller


Run root.sh on node1, node2 as root

Before creating 10g database I decided to install 10.2.0.4 patch set 3 on top of 10.2.0.1 

login as oracle software owner and install patch set 3

$ /usr/orasw/10g_software/10204_patch/Disk1/runInstaller



Oracle 10g Software with patch set 3 has been installed with no issues. It’s time to create RAC database.

Start DBCA from 10g $ORACLE_HOME/bin

$ cd $ORACLE_HOME/bin
$ dbca









Where is my Configuration Assistant page for Real application clusters?


Humm...not looking right as DB configuration assistant is not showing cluster database selection page. Anyway just to see what happens next  I decided to move forward and selected ASM Storage Management for database files.








It failed at 2% with “ORA-29702” . I have aborted the installation.



Checked cluster nodes status.

$ olsnodes -t
node1        Unpinned
node2        Unpinned

If the cluster is a newly created 11.2 Grid Infrastructure (CRS), and you want to create pre 11.2 RAC db, you need to pin the corresponding nodes. In our case their status are showing as Unpinned. If Grid Infrastructure was upgraded to 11.2, then you no need to pin. Read more Installing Pre 11.2 Database in 11GR2 Grid Infrastructure environment at My Oracle Support Document Id: 948456.1

 $ crsctl pin css -n node1 node2
/usr/lib/hpux64/dld.so: Unable to find library 'libhasgen10.so'.

Make sure to login as root before making any cluster related changes.
$su –

# crsctl pin css -n node1 node2

CRS-4664: Node node1 successfully pinned.
CRS-4664: Node node2 successfully pinned.

#exit

$ olsnodes -t
node1        Pinned
node2        Pinned

Our nodes are showing Pinned status 

now start dbca again from ORACLE_HOME/bin
$ cd $ORACLE_HOME/bin
$ dbca




Now I am able to see RAC database screen.. So far so good.







It failed again with the error that DBCA is unable to communicate to ASM even-though ASM is up and running.

“ORA-15260: permission denied on ASM disk group”

I decided to abort the installation the installation and looked into MOS for possible solutions and this is what I came across.

Due to unpublished bug 8288940, DBCA will fail if database files are located in ASM. Oracle's recommendation is to apply 8288940 patch. 

I download and appled patch 8288940 on top of 10.2.0.4. Please follow instructions in README.txt file for patch installation steps.

started dbca again for 10gR2 ORACLE_HOME

$ cd $ORACLE_HOME/bin
$ dbca 













Now installer is prompting to provide ASM password.  






I can see DATA disk group that I have created during 11gR2 grid Infrastructure installation.
Finally, After selecting next in next couple of screens I am able to create 10g DB on 11Gr2 Grid infrastructure.

Please look into my other blogs on Oracle 11gR2 Grid Infrastructure installation steps and read more on Installing Pre -11.2 Database in 11GR2 Grid Infrastructure environment at  My Oracle Support Document Id: 948456.1

Statspack in 11g

You need Oracle tuning pack/diagnostic pack licences if you want to use Grid control or Database control performance analyzer.

Statspack is free utility for performance monitoring and reporting and it's there since Oracle 8i. Lets start with Installation of Statspack in 11gR2 DB.

- You can create separate tablespace for Statspack objects or use existing tablespsace too.

PERFSTATS is the owner for Statspack repository and this user will be created automatically during Installation.

SQL> connect / as sysdba
Connected.
SQL> @?/rdbms/admin/spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: 

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
SYSAUX PERMANENT *
TOOLS PERMANENT
USERS PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: 
Using tablespace SYSAUX as PERFSTAT default tablespace.
 
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------

Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: 
....
...
No errors.
NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.
SQL> 
 
It's time to connect to PERFSTAT user and take snapshot. You need snapshot before and the end of the period you want to analyze. You can schedule this to run every hour. That's what AWR does automatically for you.
$ sqlplus perfstat/perfstat
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
 
run you load or wait until period you are monitoring is over and take another snapshot.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed
It's time to run Statspack reports. 
SQL> @?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
961341030 ORCL 1 ORCL
 
 
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
961341030 1 ORCL ORCL server1
Using 961341030 for database Id
Using 1 for instance number
 
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
 
 
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL ORCL 1 21 Dec 2011 15:00 5
2 22 Dec 2011 16:00 5Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
 
 
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: test
...
...
End of Report ( test.lst )
SQL>
This will create OS file "test.lst" and time to do analysis.

Installing 11g Grid Infrastructure for Standalone Server on Solaris 10


Download 11g Grid Infrastructure software from oracle's edelivery site edelivery.oracle.com.In this

Verify the system requirements.


  • Memory : minimum 4GB of RAM and 4GB of swap space.
    • # /usr/sbin/prtconf | grep “Memory size”
    • # /user/sbin/swap -s
    • At least 5.5 GB of space for Grid Infrastructure.
  • At least 3.5 GB of space for Oracle software enterprise edition.
  • At least 1.5 GB of tmp space.
    • df –k /tmp 
Create OS users and groups

We will be creating two separating accounts: grid,oracle. The Unix account grid will be used for 11g grid instrastructure( ASM+clusterware) and oracle will be used for 11g Database software.

Group dba for Oracle sysdba: this group is required for Database software. This group use identify OS accounts who have database administrative(SYSDBA) privilege.
SYSASM is new in 11g and enables the separation of ASM storage administration from database administration(SYSDBA). By default members of dba are not granted SYSASM privileges.
Oracle Automatic Storage Storage Management Group(asmadmin) for ASM administrator and separated from SYSDBA(dba) privilege.The members of this group have SYSASM privilege. The SYSASM allows mounting and dismounting of disk groups and ASM storage tasks. The users with SYSASM previliges cannot connect to database.
ASM database Administrator group (asmdba) : members of these groups are granted read and write access to files managed by ASM. The oracle software owner must be the member of this group.

#groupadd –p 1000 oinstall
#groupadd –p 1020 asmadmin
#groupadd –p  1021 asmdba
#groupadd –p  1031 dba
#groupadd –p 1022 asmoper
#useradd –u 1100 –g oinstall –G asmadmin, asmdba, grid
#useradd –u 1101 –g oinstall –G dba, asmdba, oracle
#mkdir –p /u01/app/11.2.0/grid
#mkdir –p /u01/app/grid
#Chown –R grid:oinstall /u01
#mkdir /u01/app/oracle
#chwon –R oracle:oinstall /u01/app/oracle
#chmod 775 /u01

Verify Storage permissions

Login as grid account and make sure grid can write to RAW devices.

$dd if=/dev/zero of=/dev/rdsk/emcpower1g bs=1024 count=1440
dd: /dev/rdsk/emcpower1g: open: Permission denied

login as root and fix permission issues first. 

$ su -

#chown grid:oinstall  /dev/rdsk/emcpower1g

$dd if=/dev/zero of=/dev/rdsk/emcpower1g bs=1024 count=1440
1440+0 records in
1440+0 records out

Package and Patches for Solaris 10.

SUNWarc
SUNWbtool
SUNWcsl
SUNWhea
SUNWi1cs (ISO8859-1)
SUNWi15cs (ISO8859-15)
SUNWi1of
SUNWlibC
SUNWlibm
SUNWlibms
SUNWsprot
SUNWtoo
SUNWxwfnt
119963-14
120753-06
139574-03
141414-02
141444-09 (included with Solaris 5.10 update 8

Package and Patches for Smart Flash Cache


125555-03 
139555-08 
            140796-01       
            140899-01       
141016-01 
141414-10
141736-05 


Check required package before installation.

  # pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibm SUNWlibms SUNWsprot  SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt SUNWcsl

 

Install Grid Infrasture


login as grid account and go to grid infracture directory.
$ id
uid=1100(grid) gid=1000(oinstall)

$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 180 MB.   Actual 511 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 75891 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216   Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-12-03_11-36-45AM. Please wait ...





#sh /u01/app/oraInventory/orainstRoot.sh
     Changing permissions of /u01/app/oraInventory.
     Adding read,write permissions for group.
     Removing read,write,execute permissions for world.
     Changing groupname of /u01/app/oraInventory to oinstall.
     The execution of the script is complete.



#


Checking swap space: 0 MB available, 500 MB required.    Failed <<<<
Some requirement checks failed. You must fulfill these requirements before you continue
# /u01/app/11.2.0/grid/root.sh
........
Errors in file :
ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4
PROT-1: Failed to initialize ocrconfig
In my case there was not enough swap space, fix the problem and before running root.sh run the deconfig
#/u01/grid/11.2.0/crs/install/rootcrs.pl -deconfig -force

Run root.sh again..

# sh /u01/app/11.2.0/grid/root.sh
Running Oracle 11g root.sh script...
The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/11.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Creating /usr/local/bin directory...
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Creating /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2012-04-27 08:27:54: Checking for super user privileges
2012-04-27 08:27:54: User has super user privileges
2012-04-27 08:27:54: Parsing the host name
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
stty: : No such device or address
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
CRS-4664: Node kykewesdrdb1 successfully pinned.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
kykewesdrdb1     2012/04/27 08:28:23     /u01/app/11.2.0/grid/cdata/server1db1/backup_20120427_082823.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
Updating inventory properties for clusterware
Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB.   Actual 91699 MB    Passed
The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.
#
That's it and this will start ohasd.bin( Oracle High Availability Services) which will start following processes
 
 - cssdagent - responsible for I/O fencing.
 - ocssd.bin  - resposible for node membership. (I guess in standalone it required for ASM ?)
 - listener
- orarootagent.bin
 - oraagent

ASM is up and running

asm_gmon_+ASM
asm_smon_+ASM
asm_lgwr_+ASM
asm_ckpt_+ASM
asm_gen0_+ASM
asm_pmon_+ASM
asm_psp0_+ASM
asm_vktm_+ASM
asm_dia0_+ASM
asm_rbal_+ASM
asm_dbw0_+ASM
asm_mman_+ASM
asm_diag_+ASM
asm_mmon_+ASM
asm_mmnl_+ASM

Please look into my other blog http://vkoracle.blogspot.com/2010/11/creating-oracle-10g-rac-database-on-11g.html to see how to install database on top of grid infrastructure.