Oracle RAC + ASM + NFS home lab

Many people look for ways to emulate enterprise hardware in their home labs in order to get good hands on experience with Oracle products.  One of the harder ones to get working is Oracle RAC due to the need of multiple networks and shared storage.

With the plethora of virtualization software out now, this has gotten much easier.  For my job I needed a two node RAC setup to do some testing of DBCA (Database Creation Assistant) scripts.  I've done 100's of RAC installs, so other than the time, I didn't foresee any issues.  Well, as usual, what can go wrong will go wrong.

Basic RAC Setup

So lets jump to the basics.  For RAC I needed a public network, private network, and shared storage.  For the network pieces I also needed additional virtual IP's for each node as well as a set of three SCAN IP address.

I setup a new virtual private network for my servers.  I added all the new IP's to my DNS server.  Things were going well.  I then did the trick to create shared ASM disks on a shared NFS mount:

dd if=/dev/zero of=/u03/oradata/asm_dsk1 bs=1k count=10000000
dd if=/dev/zero of=/u03/oradata/asm_dsk2 bs=1k count=10000000
dd if=/dev/zero of=/u03/oradata/asm_dsk3 bs=1k count=10000000
dd if=/dev/zero of=/u03/oradata/asm_dsk4 bs=1k count=10000000
dd if=/dev/zero of=/u03/oradata/asm_dsk5 bs=1k count=10000000


I then changed the ownership:

chown grid:asmdba /u03/oradata/asm-dsk?
chmod 660 /u03/oradata/asm-dsk?

That second command, to change the permissions is what I missed.  This was somewhat critical.  It also speaks to a problem I've seen many times over.  How to deal with Oracle access issue when you have role separation setup.  The is particularly common when running Oracle E-Business suite due to the number of assumptions made by the Oracle procedures for cloning an EBS instance.

Troubleshooting Role separation issues.

As mentioned I've done this many times, and the only way to effectively troubleshoot these issues is to methodically go step by step through the setup of Oracle and Grid and ASM.  There are a few notes for help on this in MOS, but not a lot:

• UNIX: Diagnostic C program for ORA-1031 from CONNECT INTERNAL / AS SYSDBA (Doc ID 67984.1)
• ORA­15183 Unable to Create Database on Server using 11.2 ASM and Grid Infrastructure (Doc ID 1054033.1)
• Database Creation on 11.2 Grid Infrastructure with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 ) (Doc ID 1084186.1)
• How To Recompile config.c / Relink Executables Of A Grid Infrastructure Home (Cluster) (Doc ID 1637766.1)
• Connect as SYSDBA on 11.2 Cloned Home Gives "ORA-1031: Insufficient Privileges" Error (Doc ID 1061788.1)

These all sound misleading, but lets go through the issue I faced.

The Issue

I had setup all my hardware and ASM raw disks.  After completing the GRID INFRASTRUCTURE installation, things looked good.  ASM was up and running, and the ASM diskgroup (DATA) looked fine.

I then went on to install the database home, and try to create my RAC database with DBCA.  That is when the trouble started.  During the initial create database statement I would get:
ORA-00200: control file could not be created
ORA-00202: control file: '+DATA'
ORA-15045: ASM file name '+DATA' is not in reference form
ORA-17502: ksfdcre:5 Failed to create file +DATA
ORA-27091: unable to queue I/O
ORA-27041: unable to open file


I jumped into the database alert log and DBCA logs, and garnered the following additional error:

Linux-x86_64 Error: 13: Permission denied

Additional information: 3

ORA-1501 signalled during: CREATE DATABASE "mydb2"

Ok, so time to go back and check everything. For role separation to work there is a OS group that is used to allow for the oracle and grid users to have permissions on the ASM disks.  In my case that group was supposed to be asmadmin.  Somewhere during the install I must have picked the wrong item in a pulldown box, or didn't pay attention to a fixup script that ran.  Either way I ended up with a difference.  So here is a list of steps to check that everything is right for role separation.  You need to check every item.

Role Separation Checklist

You should shutdown CRS before making any changes / updating any of these items.  As the root user run on all nodes: 

$GRID_HOME/bin/crsctl stop crs

1. Check the user id and group membership for the grid user:
   id grid
   (note the id number and group names, numbers the user is in)
2. Do the same check for the oracle user:
   id oracle
3. Make a list of the groups you are using for each role in oracle (ASM access, ASM super user, DB DBA, DB OPER, etc..)
4. As the grid user, check the configuration for the Grid infrastructure ($GRID_HOME/rdbms/lib/conf.c)
   #define SS_DBA_GRP "asmdba"
#define SS_OPER_GRP "asmoper"
#define SS_ASM_GRP "asmadmin"
   If changes are needed, update the config.c and re-link the oracle binary
   cd $GRID_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle
5. As the oracle user do the same check in the oracle database home:
   ($ORACLE_HOME/rdbms/lib/conf.c)
   #define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP "asmadmin"
   (note that the SS_ASM_GRP has to match the grid home setting)
   If you need to make changes, again re-link the oracle binary
   cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle
6. Check that the oracle binary in the grid home has the right permissions and ownership. The user, group ownership are critical.  Also the sitcky bit has to be set for execution:
   ls -l $GRID_HOME/bin/oracle
-rwsr-s--x. 1 grid asmadmin 291225032 Jun  6 10:15 /u01/app/12.1.0/grid/bin/oracle
   If this is not correct then as the root user run:
   $GRID_HOME/bin/setasmgidwrap $GRID_HOME/bin/oracle
7. Do the same check for the database home oracle binary (again pay close attention to the user and group ownership as well as the sticky bit on the execution flag):
   ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 323613264 Jun  7 16:08 /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
   If this is not correct, then as the root user run:
   $GRID_HOME/bin/setasmgidwrap $ORACLE_HOME/bin/oracle
8. Check the ownership / permissions on the ASM source disks (in my case this was the raw files I had created on the shared NFS mount):
   ls -l /u03/oradata/asm*
-rw-rw----. 1 grid asmadmin 10240000000 Jun 11  2018 /u03/oradata/asm_dsk1
   (Again note the user and group ownership, as well as file permissions.  I was missing the write option on the group)
9. Check NFS mount settings.  Since I'm on a home lab using NFS, this is also important.  If your in a SAN or Exadata situation, you shouldn't have to check this. 
   I use the following settings, which are based on Oracle's recommended settings with the addition of "_netdev" which tells the startup scripts to wait until the network is started before trying to mount this specific mountpoint:
   rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0,_netdev

You can now reboot or restart CRS (as the root user run $GRID_HOME/bin/crsctl start crs).

That should take care of permission issues when working with role separation.  It's important to go through every step, and verify everything.  I spent a few hours repeating these steps over a few times, having missed one thing or another.  So even with years of experience it's easy to miss a step.  What should have taken me about 30 minutes to resolve, ended up taking about 3 hours due to this.

Not the end of the world, but really should have been caught sooner.
Gary

DBA, cloud enabled - COLLABORATE 18 IOUG - #C18LV

2018 has entered like a lion, and is rushing by for myself.  COLLABORATE 18 is just around the corner. For the third year I’m again acting as the IOUG conference chair for COLLABORATE.  This is also the 25th anniversary of IOUG.  April 22nd through April 26th will be some of the most packed days of Oracle technical training available in North America!

To start with there are three user groups that come together to bring the best user presentations.  The Oracle Applications User Group (OAUG), Quest user group, and the Independent Oracle Users Group (IOUG).  As I represent the IOUG side of this triangle, lets go through some of the great things we have lined up for this event.

IOUG has 201 education sessions scheduled from Monday to Thursday covering many topics for the modern data professional.  Thats over 196 hours of in person sessions with some of the brightest minds with real world experiences.  Looking to move to the cloud?  We have sessions on that.  Trying to decide which cloud is right for you?  Looking to use new in memory database features, or NOSQL?  Yep we have those also.  Thinking you need more database security, need to catch up on patching, or upgrade?  Those topics are covered as well.

One of the topics that IOUG is always proud to bring to COLLABORATE is professional development.  Again we have 13 sessions covering everything from how to improve your presentation skills, making your role more visible, and even what it takes to become a CEO / CTO.

Is BIG DATA / BI your thing?  Well we have that covered as well.  Learn all the new features in modern SQL, or maybe see how you can use NOSQL platforms to get the most out of your data.  Perhaps your more of a developer, well we have a number of sessions on DEVOPS, using docker with Oracle projects, and of course sessions on performance tuning your databases as well as data model design.

So you might be thinking this is all talking and not much doing.  Well that’s not true, on Sunday the 22nd we have 5 hands on labs being put on by Oracle and AWS.  This is your opportunity to try Oracle cloud and AWS cloud yourself.  Leave with real hands on in person experience with these technologies.  Not into the cloud?  How about a hands on training of Python for Oracle DBA’s?  Or Oracle 18c upgrade hands on lab?  Even a lab on javascript notation for Oracle 12c and above.  Finally a lab on Hadoop and Kafka in conjunction with Oracle Database allows us to cover just about anything you would want to know about modern database technology.

Beyond that we are also excited to have Oracle Education working with us this year.  They will have a prominent spot in hall by the education sessions on the 3rd floor.  They will be there to talk to you about Oracle Education opportunities outside of COLLABORATE, as well as certification and testing opportunities.  If you’re trying to advance your Oracle career, we are pleased to have them on site to help.

Don’t forget all the networking opportunities, not just in the hallways and in sessions.  We have a welcome reception on Sunday at 6pm for all IOUG members.  There will be drinks and appetizers, as well as some ice breaker games.  For first time attendees we have a sessions right before at 5pm to introduce you to the conference, the user group, and meet some of the people that help bring this all together.

Which reminds me, I would like to extend a huge thanks to the team from IOUG that helps put the conference on.  There is only a few paid staff members that coordinate all the venue and on-site items.  Everything else is done by a team of 51 volunteers that do everything else from reviewing proposals, working with new speakers, setting up on-site events, coordinating with vendors, and much, much more.  Really, this yearly event would not exist if it wasn’t for the hard work of the team.  If you see someone at the conference wearing a conference committee hat, please say “HI”.  Feel free to ask them your questions, they should be intimately informed on all things COLLABORATE.

Also a big thanks to our 168 speakers.  Thank you for taking the time to work hard and hone your skills, and then going the extra mile to want to train others and explain the gory details.  The committee might bring the skeleton of the conference together, bu the speakers put all the meat on the bones.

Don’t forget the vendor floor reception on Monday after the educational sessions end.  Be sure to stop by the IOUG booth.  Finally we have the thank you party on Wednesday night, for everyone to gather, meet, and have fun!

Really, this is one of the most jam packed COLLABORATEs we have hosted in years.  I’m proud to be a part of it, and extra proud of the entire team that brought this all together.  I hope to see you in Las Vegas, at Mandalay Bay hotel from April 22nd to April 26th 2018!  Please feel free to say Hi if you see me.
Gary