this is the last very short post of this series:

Create your own Oracle Staging Environment, Part 1: The Staging Server
Create your own Oracle Staging Environment, Part 2: Kickstart File(s) and Profiles
Create your own Oracle Staging Environment, Part 3: Setup the lab environment
Create your own Oracle Staging Environment, Part 4: Staging the GI

at the end of the last post the GI was up and running. from this point onwards it would be easy to use dbca or scripts to setup a RAC database. but we can even automate this. updated script:

finish_gi_setup_rac.sh

save this under /var/www/html/finish_gi_setup.sh on the cobbler vm and fire up the vms. the result is:

crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS_DG.dg
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.DATA.dg
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.FRA.dg
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.LISTENER.lsnr
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.LISTENER_ASM.lsnr
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.asm
               ONLINE  ONLINE       mylabp1vm1               Started             
               ONLINE  ONLINE       mylabp1vm2               Started             
ora.gsd
               OFFLINE OFFLINE      mylabp1vm1                                   
               OFFLINE OFFLINE      mylabp1vm2                                   
ora.net1.network
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.ons
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       mylabp1vm2                                   
ora.cvu
      1        ONLINE  ONLINE       mylabp1vm2                                   
ora.mylabp1vm1.vip
      1        ONLINE  ONLINE       mylabp1vm1                                   
ora.mylabp1vm2.vip
      1        ONLINE  ONLINE       mylabp1vm2                                   
ora.oc4j
      1        ONLINE  ONLINE       mylabp1vm2                                   
ora.racdb.db
      1        ONLINE  ONLINE       mylabp1vm2               Open                
      2        ONLINE  ONLINE       mylabp1vm1               Open                
ora.scan1.vip
      1        ONLINE  ONLINE       mylabp1vm2                         

today, on an upgraded asm/gi instance from 11.2.0.4 to 12.1.0.2 on linux x64 we faced the following situation (although the upgrade went smooth and everything was up and running):

srvctl config asm
ASM home: 
PRCA-1057 : Failed to retrieve the password file location used by ASM asm
PRCR-1097 : Resource attribute not found: PWFILE 

in 11.2.0.4 the attribute for the asm password files was not available:

srvctl modify asm -h

Modifies the configuration for ASM.

Usage: srvctl modify asm [-l ] 
    -l            Listener name
    -h                       Print usage

this came in 12.1.0.1 (together with other attributes):

srvctl modify asm -h

Modifies the configuration for ASM.

Usage: srvctl modify asm [-listener ] [-spfile ] [-pwfile ] [-diskstring ]
    -listener           Listener name
    -spfile                Server parameter file path
    -pwfile    Password file path
    -diskstring    ASM diskgroup discovery string
    -help                          Print usage

seems easy to fix:

srvctl modify asm -pwfile [SOME_PATH]/orapw+ASM 
PRCR-1097 : Resource attribute not found: CARDINALITY

no way to get the password file attribute into the GI. the workaround is to stop the complete GI, remove the ASM resource and then add it in again providing all the parameters. makes fun on a production host :)

mos note: 1935891.1

Lets continue this series of posts:

Create your own Oracle Staging Environment, Part 1: The Staging Server
Create your own Oracle Staging Environment, Part 2: Kickstart File(s) and Profiles
Create your own Oracle Staging Environment, Part 3: Setup the lab environment

In Part 3 we had two (or more) VMs up and running with the following specs:

  • eth0 configured as the public network
  • eth1 configured as the private interconnect
  • three shared storage devices for the ASM disks
  • oracle users and groups
  • a sample source file deployed on all Vms

this might be sufficiant for starting a lab if you want to demonstrate the installation of a grid infratructure. but we can even go further and automate the setup of the GI. what else would be required?

  • the source file for the grid infrastructure (stored in /var/www/html/sources). in this case the 11.2.0.4 files downloaded from mos
  • kernel parameters
  • partitions on the disks
  • limits for the oracle users
  • setting the io scheduler
  • the cluster configuration file
  • the gi software installation
  • password-less ssh connectivity for the grid user

here are the updated scripts (as pdfs to reduce the length of this post):

place these on the cobbler server in same locations as in the previous posts, then:

./create_gi_rac_lab.sh mylab 2

fetch one of the vm setup scripts:

ls -la /var/www/html/mylab/setup*.sh
-rw-r--r--. 1 root root 3821 Jan 11 09:32 /var/www/html/mylab/setup_p_1.sh
-rw-r--r--. 1 root root 3821 Jan 11 09:32 /var/www/html/mylab/setup_p_2.sh

… and fire up the VMs on your workstation.

give each vm some minutes before starting the next one. the reason is that the last vm will do the configuration of the GI and therefore the other vms should be up and running when the configuration starts. once the last one completed you have the GI up and running on all the nodes:

[root@mylabp1vm1 ~]# /opt/oracle/product/crs/11.2.0.4/bin/crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS_DG.dg
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.asm
               ONLINE  ONLINE       mylabp1vm1               Started             
               ONLINE  ONLINE       mylabp1vm2               Started             
ora.gsd
               OFFLINE OFFLINE      mylabp1vm1                                   
               OFFLINE OFFLINE      mylabp1vm2                                   
ora.net1.network
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
ora.ons
               ONLINE  ONLINE       mylabp1vm1                                   
               ONLINE  ONLINE       mylabp1vm2                                   
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       mylabp1vm2                                   
ora.cvu
      1        ONLINE  ONLINE       mylabp1vm1                                   
ora.mylabp1vm1.vip
      1        ONLINE  ONLINE       mylabp1vm1                                   
ora.mylabp1vm2.vip
      1        ONLINE  ONLINE       mylabp1vm2                                   
ora.oc4j
      1        ONLINE  ONLINE       mylabp1vm1                                   
ora.scan1.vip
      1        ONLINE  ONLINE       mylabp1vm2                                   

there still is much room for improvement, but for now it at least works … takes around 20 minutes on my workstation until both nodes are up and configured.

recently we faced the above issue in the asm alertlog on a linux box. it turned out that this always was reported when a select againt v$asm_disk or v$asm_diskgroup was executed. although this seemed somewhat nasty ASM always was up and running, no issues at all.

stracing a sqlplus session which executes one of the selects gave the right hints. the issue was this:

SYS@+ASM> show parameter string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskstring			     string	 /dev/*

This forces ASM to scan all devices in /dev for possible asm disks where ASM has access to (inluding all the devices which are not disks at all ). If one of these devices has a size which is not a multiple of the asm sector size this happens. So the fix is easy ( /dev/sdc1 and /dev/sdd1 are the ASM decives in this case ):

alter system set asm_diskstring='/dev/sd*' scope=both;

or

alter system set asm_diskstring='/dev/sd*1' scope=both;

or

alter system set asm_diskstring='/dev/sdc1','/dev/sdd1' scope=both;

Setting the asm_diskstring as close to your asm decives as you can is a best practice anyway and can recude discovery and mount times.

The default value is null in 12cR1 and 11gR2

some time ago I blogged about the catctl.pl script that organizes oracle database upgrades starting in oracle 12. what I didn’t looked at was how the the different phases are build.

when you call the catctl.pl whith the “-y” flag to display the phases some lines at the beginning of the output are:

Analyzing file catupgrd.sql
Log files in /opt/oracle/product/base/12.1.0.2.0/rdbms/admin
23 scripts found in file catupgrd.sql
Next path: catalog.sql
34 scripts found in file catalog.sql
Next path: catproc.sql
38 scripts found in file catproc.sql
Next path: catptabs.sql
63 scripts found in file catptabs.sql
Next path: catpdbms.sql
210 scripts found in file catpdbms.sql
Next path: catpdeps.sql
76 scripts found in file catpdeps.sql
Next path: catpprvt.sql
265 scripts found in file catpprvt.sql
Next path: catpexec.sql
26 scripts found in file catpexec.sql
Next path: cmpupgrd.sql
28 scripts found in file cmpupgrd.sql

so, what is the procedure for determining the order of these scripts? the documentation/header of the catctl.pl script gives some hints:

#      --CATCTL -S
#         Run Sql in Serial Mode one Process
#      --CATCTL -M
#         Run Sql in Multiple Processes
#      --CATFILE -X
#         Sql contains multiprocessing
#      --CATCTL -R
#         Bounce the Sql Process
#      --CATCTL -CS
#         Turns Compile Off (Defaults to on)
#      --CATCTL -CE
#         Turns Compile On
#      --CATCTL -SES
#         Session files for phases
#      --CATCTL -SE
#        Run Sql in Serial Mode only executed in catctl.pl
#      --CATCTL -ME
#        Run Sql in Parallel Mode only executed in catctl.pl

the first step that happens is scanning the catupgrd.sql for the “–CATFILE -X” text. this would look like:

grep "\-\-CATFILE \-X" catupgrd.sql 
@@catalog.sql     --CATFILE -X
@@catproc.sql     --CATFILE -X
@@cmpupgrd.sql    --CATFILE -X

not exactly the same as catctl.pl tells us. the reason is that each of these scripts can contain the “–CATFILE -X” again. so we would need to grep all of these scripts again. a simple script to do this:

!/bin/bash

RDBMS_ADMIN=/opt/oracle/product/base/12.1.0.2.0/rdbms/admin

FILES=`grep "\-\-CATFILE \-X" ${RDBMS_ADMIN}/catupgrd.sql | awk -F " " '{print $1}' | awk -F "@" '{print $3}'` 

for f in ${FILES}
do
  echo ${f}
  FILES2=`grep "\-\-CATFILE \-X" ${RDBMS_ADMIN}/${f} | awk -F " " '{print $1}' | awk -F "@" '{print $3}'`
  for ff in ${FILES2}
  do
    echo ${ff}
  done
done

exactly the same:

./a.sh 
catalog.sql
catproc.sql
catptabs.sql
catpdbms.sql
catpdeps.sql
catpprvt.sql
catpexec.sql
cmpupgrd.sql

be aware that there might be even more recursion in the future and the above little script might not work anymore. the complete logic is in the catctlScanSqlFiles routine which is much more complex.

the rest is pretty straight forward.

the first phase is the first script called in catupgrd.sql (serial execution)

[phase 0] type is 1 with 1 Files
@catupstr.sql         

the second phase:

[phase 1] type is 1 with 5 Files
@cdstrt.sql           @cdfixed.sql          @catcdbviews.sql      @catblock.sql 
@cdcore.sql     

just look at the header of catalog.sql:

--CATCTL -S    Initial scripts single process
@@cdstrt.sql
@@cdfixed.sql
@@catcdbviews.sql
@@catblock.sql
@@cdcore.sql

--CATCTL -R
--CATCTL -M
@@cdplsql.sql
@@cdsqlddl.sql

everything below “–CATCTL -S” is phase 1. phase 2 is “–CATCTL -R” (restart the sqlplus session(s)). phase 3 runs in parallel because of the “–CATCTL -M” flag.

and so on and so on.

btw: by the time I wrote the post about catctl.pl the catctl.pl script was version 12.1.0.1. in the 12.1.0.2 release the documentation inside the scripts is much better especially for the number of processes used for the upgrade:

#      Below is the Basic Flow of upgrade.
#
#      Traditional Database
#
#         Run Database upgrade in parallel (-n option) passing in catupgrd.sql.
#             Minimum SQL process count is 1.
#             Maximum SQL process count is 8.
#             Default SQL process count is 4.
#         Run Post Upgrade Procedure.
#             If there are any errors in the upgrade the post
#             upgrade procedure will not run.
#             If the -x option is specified the post upgrade procedure
#             will not run.
#
#         1)  The database must be first started in upgrade mode
#             by the database adminstrator.
#         2)  After the upgrade completes the database is shutdown.
#         3)  The database is then restarted in restricted normal mode.
#         4)  Run the post upgrade procedure (catuppst.sql).
#         5)  Shutdown the database.
#
#      Multitenant Database (CDB)
#
#         Run Database upgrade in parallel (-n option) passing in catupgrd.sql.
#             Minimum SQL process count is 1.
#             Maximum SQL process count is 64.
#             Default SQL process count is 0.
#                 This is calculated by Number of Cpu's on your system.
#         Run Upgrade on CDB$ROOT.
#             The Maximum SQL process count is 8.
#             If there are any errors in the CDB$ROOT upgrade the
#             entire upgrade process is stopped.
#         Run Upgrades in all the PDB's.
#             The number of PDB's that run together is calculated by
#             dividing the SQL process count by 2.
#             Each individual PDB will use 2 for its SQL process count.
#             For example:
#             If the value of -n is set to 32.  32/2 yields 16
#             PDB's upgrading at the same time.
#             Each of those 16 PDB's use 2 for thier SQL
#             process count for a total of 32 SQL sessions that
#             running concurrently.
#             If the value of -N is set then the PDB's will use
#             this value for thier SQL process count instead of
#             the default 2.

a nice article on the history of grep:

the history of grep

today ( by accident ) I discovered a nice tool that shows socket information on linux:

ss -s
Total: 666 (kernel 0)
TCP:   21 (estab 7, closed 1, orphaned 0, synrecv 0, timewait 1/0), ports 0

Transport Total     IP        IPv6
*	  0         -         -        
RAW	  0         0         0        
UDP	  24        17        7        
TCP	  20        16        4        
INET	  44        33        11       
FRAG	  0         0         0   

there are much more options:

Usage: ss [ OPTIONS ]
       ss [ OPTIONS ] [ FILTER ]
   -h, --help		this message
   -V, --version	output version information
   -n, --numeric	don't resolve service names
   -r, --resolve       resolve host names
   -a, --all		display all sockets
   -l, --listening	display listening sockets
   -o, --options       show timer information
   -e, --extended      show detailed socket information
   -m, --memory        show socket memory usage
   -p, --processes	show process using socket
   -i, --info		show internal TCP information
   -s, --summary	show socket usage summary
   -b, --bpf           show bpf filter socket information

   -4, --ipv4          display only IP version 4 sockets
   -6, --ipv6          display only IP version 6 sockets
   -0, --packet	display PACKET sockets
   -t, --tcp		display only TCP sockets
   -u, --udp		display only UDP sockets
   -d, --dccp		display only DCCP sockets
   -w, --raw		display only RAW sockets
   -x, --unix		display only Unix domain sockets
   -f, --family=FAMILY display sockets of type FAMILY

   -A, --query=QUERY, --socket=QUERY
       QUERY := {all|inet|tcp|udp|raw|unix|packet|netlink}[,QUERY]

   -D, --diag=FILE     Dump raw information about TCP sockets to FILE
   -F, --filter=FILE   read filter information from FILE
       FILTER := [ state TCP-STATE ] [ EXPRESSION ]

have fun with sockets …