Oracle RAC 09 - Database creation and user environments

Oracle RAC Solution · Previous: Database software, listener and disk groups · Next: Backup Exec agent

Everything so far was preparation: a cluster, a database home, a listener, empty disk groups. This part creates the database clsdb with one instance on each node, checks what the cluster knows about it, and then sets up the shell environments of the two software owners, so that the right sqlplus, srvctl and asmcmd are found without thinking. The notes carry one tip for the database creation, a setuid bit whose absence hides the disk groups from DBCA, and the environment files contain one mistake that does no harm and is still in them.

Creating the database with DBCA

The Database Configuration Assistant was started as oracle on the first node, again with an X server on the workstation.

$ su - oracle
$ dbca

On the first screen DBCA asks what kind of database to work with; in a cluster the choice includes "Oracle Real Application Clusters (RAC) database", and from there it creates instances on every selected node. The answers that define the database:

The whole answer set is a Config document: DBCA answers.

With archiving enabled and the daily disk backup of Database Control switched off, the database produces archived logs into +FRA from the first day, and nothing in the recorded configuration backs them up or removes them. That job was meant for Backup Exec; see Backup Exec agent and Backup Exec server and RAC backup.

When Browse shows no disk groups

On the Database File Locations screen the "Browse" button next to Database Area should list the ASM disk groups. The notes carry a tip for that screen in conditional form: if clicking "Browse" shows no ASM groups, the setuid bit has to be set on the oracle binary of the Grid home. They do not say whether that happened here.

The notes give only the remedy, so the explanation is mine. As I understand it, the cause lies in the separation of the two owners: DBCA runs as oracle, and ASM runs as grid from the Grid home. The oracle executable of the Grid home has to carry the setuid and setgid bits, so that it runs with the identity of its owner and not of the caller. Without them the database owner cannot get at ASM, and DBCA shows an empty list instead of an error. The notes point to My Oracle Support note 1177483.1 and to the fix, run as root in the bin directory of the Grid home.

$ cd <Grid_Home>/bin
$ chmod 6751 oracle
$ ls -l oracle

Mode 6751 is setuid and setgid on top of rwxr-x--x. <Grid_Home> is how the notes write the directory; here it is /data/u01/app/grid11204, and the notes give the full path /data/u01/app/grid11204/bin/oracle in the sentence above the commands. They keep no ls output and name no node (DBCA ran on oradb01; the binary exists on both nodes and I would check both). If the bits were missing here, the notes do not say how that came about. The Grid home had been patched more than once before this point, see Grid patches and the multicast problem, but that is a suspicion, not something the notes establish.

Initialization parameters

DBCA's "Typical" setting takes a percentage of the memory it finds on the node, so 6307 MB at 80 % is the only trace of the size of the virtual machines in the notes; they do not state it anywhere directly. With Automatic Memory Management off, Oracle manages the SGA and the PGA as two separately sized areas. The notes do not give the reason for unticking it. The block size and the character set are the two choices on these tabs that are not changed casually later: the block size is fixed for the life of the database, and a change of character set is a migration.

What the cluster knows about the database

After DBCA finished, the database was a resource of the cluster. The notes check it with srvctl, as oracle on the first node.

$ srvctl config database -d clsdb

Database unique name: clsdb
Database name: clsdb
Oracle home: /data/u01/app/oracle/db11204
Oracle user: oracle
Spfile: +DATA/clsdb/spfileclsdb.ora
Domain: example.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: clsdb
Database instances: clsdb1,clsdb2
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Database is administrator managed

The line to notice is Disk Groups: DATA,FRA. The third disk group, ONTRLG, was created on a LUN of its own that the disk table of the preparation notes describes as online transaction logs, and it is not in this list. DBCA was given +DATA as the database area and +FRA as the recovery area and the storage screen was left at its defaults, so nothing in the recorded answers sends redo logs to +ONTRLG. The notes hold no later step that moves them there either. Whether the group was used after the notes end, I cannot say from them; as far as they go, ONTRLG is a disk group with nothing on it.

The notes stop at this configuration listing. They hold no srvctl status database, no crsctl status resource -t after the database was created, and none of the generated DBCA scripts. Database Control was configured, but its address is not recorded.

One more thing about the name. The database is clsdb in DBCA and in this output. The header of the preparation notes calls it csldb, and the Backup Exec notes work with clsopdb and instances clsopdb1 and clsopdb2 throughout, while a query of v$database in those same notes prints CLSDB. I report the three spellings as they are; Backup Exec server and RAC backup deals with the last one.

The environments of oracle and grid

Two homes on one machine mean two sets of tools with the same names. sqlplus, srvctl, lsnrctl and asmcmd exist in the Grid home, most of them in the database home as well, and which one runs and which instance it talks to is decided by ORACLE_HOME, ORACLE_SID and PATH. The notes set these up last, in the login profiles.

The files were written with vi on the first node and repeated on the second with the node-specific lines changed: ORACLE_HOSTNAME, and ORACLE_SID with the instance number 2.

The profile of oracle does three things beyond the usual exports. It keeps the two homes in variables of its own, GRID_HOME and DB_HOME. It saves the search path in BASE_PATH before any Oracle directory is added, and builds PATH from that. And it defines the aliases grid_env and db_env, which source the two small files into the running shell.

BASE_PATH=/usr/sbin:$PATH:$HOME/bin; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

alias sql='sqlplus / as sysdba'
alias grid_env='. /home/oracle/grid_env'
alias db_env='. /home/oracle/db_env'

Each of the two small files sets ORACLE_SID and ORACLE_HOME and then repeats the PATH, LD_LIBRARY_PATH and CLASSPATH lines, which are evaluated again with the new home. Because PATH is rebuilt from BASE_PATH every time, switching back and forth does not make it longer.

flowchart LR
  login["su - oracle"] --> prof[".bash_profile"]
  prof --> dbs["Database environment"]
  subgraph dbe["After login or db_env"]
    dbs --> d1["ORACLE_HOME = DB_HOME, /data/u01/app/oracle/db11204"]
    dbs --> d2["ORACLE_SID = clsdb1"]
    dbs --> d3["PATH = database home bin + BASE_PATH"]
  end
  subgraph gre["After grid_env"]
    grs["Grid environment"] --> g1["ORACLE_HOME = GRID_HOME, /data/u01/app/grid11204"]
    grs --> g2["ORACLE_SID = +ASM1"]
    grs --> g3["PATH = Grid home bin + BASE_PATH"]
  end
  dbs -- "grid_env" --> grs
  grs -- "db_env" --> dbs

The diagram shows the first node; on oradb02 the instance names end in 2. The user grid needs none of this. Its profile has the Grid home as the only home, +ASM1 or +ASM2 as the instance, and the alias sql as sqlplus / as sysasm.

The test in the notes is as simple as it can be: log in as oracle, print the home, switch, print, switch back, print. The notes do not say on which node it ran.

$ su - oracle
$ echo $ORACLE_HOME
$ grid_env
$ echo $ORACLE_HOME
$ db_env
$ echo $ORACLE_HOME

/data/u01/app/oracle/db11204
/data/u01/app/grid11204
/data/u01/app/oracle/db11204

The ulimit block that never runs

Both profiles begin with a block that is meant to raise the process and file limits of the user.

if [ $USER = “oracle” ]; then
if [ $SHELL = “/bin/ksh” ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Look at the quotes. They are typographic, “ and ”, not the ASCII character the shell understands as a quote. Bash takes them as part of the word, compares oracle with a string that has a curly quote at each end, finds them different and skips the block, silently, at every login of oracle and grid on both nodes. The Config documents keep the characters exactly as they were.

It did no damage, because the limits were already set in two other places during the operating system preparation: in limits.conf, and in a block of the same shape in /etc/profile.local, that one with ASCII quotes and with 131072 where this one says 16384 and 65536. Had the block here worked, it would have asked for lower values than the system-wide file had just set.

What I would do differently

• Delete the ulimit block from both profiles. It is dead, and if someone ever "fixes" the quotes it starts lowering limits that another file sets.
• Record the state, not only the answers. A crsctl status resource -t, an asmcmd lsdg and a lsnrctl status taken after DBCA would have answered the open questions of this part and the previous one: which listener the instances registered with, and what, if anything, went to ONTRLG.
• Decide about ONTRLG before running DBCA. A disk group for online redo logs is only used if the redo logs are created there, and the answers recorded here never mention it.
• Keep the generated DBCA scripts with the notes. They were written to /data/u01/app/oracle/admin/clsdb/scripts on the node and nowhere else.
• Know that this cannot be repeated on current software. RAC is not supported in Standard Edition 2 from 19c, Database Control is gone since 12c, and a database without containers is desupported from 21c. What has held up is the memory answer: with more than 4 GB of physical memory Automatic Memory Management cannot be selected at all today, and it does not go together with HugePages on Linux.
• Type srvctl config database -db clsdb. The single-letter options of srvctl are deprecated since 12c, although they still work.