Install and configure CMAN 19c in the Oracle Cloud, step by step

Installing and configuring CMAN is a trivial activity, but having the steps in one place is better than reinventing the wheel.

Prepare for the install

Download the Oracle Client 19.3.0.0 in the Oracle Database 19c download page.

Choose this one: LINUX.X64_193000_client.zip (64-bit) (1,134,912,540 bytes) , not the one named “LINUX.X64_193000_client_home.zip” because it is a preinstalled home that does not contain the CMAN tools.

Access the OCI Console and create a new Compute instance. The default  configuration is OK, just make sure that it is Oracle Linux 7 🙂

Do not forget to add your SSH Public Key to access the VM via SSH!

Access the VM using

ssh opc@{public_ip}

Copy the Oracle Client zip in /tmp using your favorite scp program.

Install CMAN

Follow these steps to install CMAN:

# become root
sudo su - root

# install some prereqs (packages, oracle user, kernel params, etc.):
yum install oracle-database-preinstall-19c.x86_64

# prepare the base directory:
mkdir /u01
chown oracle:oinstall /u01

# become oracle
su - oracle

# prepare the Oracle Home dir
mkdir -p /u01/app/oracle/product/cman1930

# unzip the Client install binaries
mkdir -p $HOME/stage
cd $HOME/stage
unzip /tmp/LINUX.X64_193000_client.zip

# prepare the response file:
cat <<EOF > $HOME/cman.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_clientinstall_response_schema_v19.0.0
ORACLE_HOSTNAME=$(hostname)
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/cman1930
ORACLE_BASE=/u01/app/oracle
oracle.install.client.installType=Custom
oracle.install.client.customComponents="oracle.sqlplus:19.0.0.0.0","oracle.network.client:19.0.0.0.0","oracle.network.cman:19.0.0.0.0","oracle.network.listener:19.0.0.0.0"
EOF

# install!
$HOME/stage/client/runInstaller -silent -responseFile $HOME/cman.rsp  ORACLE_HOME_NAME=cman1930

# back as root:
exit

# finish the install
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/cman1930/root.sh

 

Basic configuration

# as oracle:

mkdir -p /u01/app/oracle/network/admin
export TNS_ADMIN=/u01/app/oracle/network/admin

cat <<EOF > $TNS_ADMIN/cman-test.ora
cman-test = (configuration=
  (address=(protocol=tcp)(host=$(hostname))(port=1521))
  (parameter_list =
    (log_level=ADMIN)
    (max_connections=1024)
    (idle_timeout=0)
    (registration_invited_nodes = *)
    (inbound_connect_timeout=0)
    (session_timeout=0)
    (outbound_connect_timeout=0)
    (max_gateway_processes=16)
    (min_gateway_processes=2)
    (remote_admin=on)
    (trace_level=off)
    (max_cmctl_sessions=4)
    (event_group=init_and_term,memory_ops)
  )
  (rule_list=
    (rule=
       (src=*)(dst=*)(srv=*)(act=accept)
       (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
  ) )
)
EOF

echo "IFILE=${TNS_ADMIN}/cman-test.ora" >> $TNS_ADMIN/cman.ora

This will create a CMAN configuration named cman-test. Beware that it is very basic and insecure. Please read the CMAN documentation if you want something more secure or sophisticated.

The advantage of having the TNS_ADMIN outside the Oracle Home is that if you need to patch CMAN, you can do it out-of-place without the need to copy the configuration files somewhere else.

The advantage of using IFILE inside cman.ora, is that you can manage easily different CMAN configurations in the same host without editing directly cman.ora, with the risk of messing it up.

Preparing the start/stop script

Create a file /u01/app/oracle/scripts/cman_service.sh with this content:

#!/bin/bash -l

LOCAL_PARSE_OPTIONS="a:c:o:"

Usage () {
        cat <<EOF

        Purpose   : Start/stop a CMAN configuration

        Usage: `basename $0` -a {start|stop|reload|restart|status} -c <config_name> -o <oracle_home>

        Options:
                -a action           One in start|stop|reload|restart|status
                -c config_name      Name of the cman instance (e.g. ais-prod, gen-prod, etc.)
                -o oracle_home      The ORACLE_HOME path that must be used for the operation (e.g. cman1930)
EOF
}


CENTRAL_CONFIG_DIR=/ORA/dbs01/oracle/network/admin

while getopts ":${LOCAL_PARSE_OPTIONS}" opt ; do
        case $opt in
                a)
                        L_Action=$OPTARG
                        ;;
                c)
                        L_Config=$OPTARG
                        ;;
                o)
                        L_OH=$OPTARG
                        ;;
                \?)
                        eerror "Invalid option: -$OPTARG"
                        exit 1
                        ;;
                :)
                        eerror "Option -$OPTARG requires an argument."
                        exit 1
                        ;;
        esac
done

if [ ! $L_Config ] ; then
    Usage
        eerror "Please specify a configuration name with -c. Possible values are: "
        ls -1 $CENTRAL_CONFIG_DIR | sed -e "s/\.ora//" | grep -v cman
        exit 1
fi


## if the install step was OK, we should have a valid OH installed with this name:
export ORACLE_HOME=$L_OH
if [ ! -f $ORACLE_HOME/bin/cmctl ] ; then
        Usage
        echo "Please set a valid ORACLE_HOME name with -o."
        exit 1
fi


export TNS_ADMIN=$CENTRAL_CONFIG_DIR
case $L_Action in
        start)
                $OH/bin/cmctl startup -c $L_Config
                ;;
        stop)
                $OH/bin/cmctl shutdown -c $L_Config
                ;;
        reload)
                $OH/bin/cmctl reload -c $L_Config
                ;;
        restart)
                $OH/bin/cmctl shutdown -c $L_Config
                sleep 1
                $OH/bin/cmctl startup -c $L_Config
                ;;
        status)
                $OH/bin/cmctl show status -c $L_Config
                # do it again for the exit code
                $OH/bin/cmctl show status -c $L_Config | grep "The command completed successfully." >/dev/null
                ;;
        *)
                echo "Invalid action"
                exit 1
                ;;
esac

This is at the same time ORACLE_HOME agnostic and configuration agnostic.

Make it executable:

chmod +x /u01/app/oracle/scripts/cman_service.sh

and try to start CMAN:

$ /u01/app/oracle/scripts/cman_service.sh -o /u01/app/oracle/product/cman1930 -c cman-test -a start
VERSION         = 19.3.0.0.0
ORACLE_HOME     = /u01/app/oracle/product/cman1930
VERSION         = 19.3.0.0.0
ORACLE_HOME     = /u01/app/oracle/product/cman1930

CMCTL for Linux: Version 19.0.0.0.0 - Production on 12-JUL-2019 09:23:50

Copyright (c) 1996, 2019, Oracle.  All rights reserved.

Current instance cman-test is not yet started
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=ocf-cman-1)(port=1521)))
Starting Oracle Connection Manager instance cman-test. Please wait...
CMAN for Linux: Version 19.0.0.0.0 - Production
Status of the Instance
----------------------
Instance name             cman-test
Version                   CMAN for Linux: Version 19.0.0.0.0 - Production
Start date                12-JUL-2019 09:23:50
Uptime                    0 days 0 hr. 0 min. 9 sec
Num of gateways started   2
Average Load level        0
Log Level                 ADMIN
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/cman1930/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/ocf-cman-1/cman-test/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/ocf-cman-1/cman-test/trace
The command completed successfully.

Stop should work as well:

$ /u01/app/oracle/scripts/cman_service.sh -o /u01/app/oracle/product/cman1930 -c cman-test -a stop
VERSION         = 19.3.0.0.0
ORACLE_HOME     = /u01/app/oracle/product/cman1930
VERSION         = 19.3.0.0.0
ORACLE_HOME     = /u01/app/oracle/product/cman1930

CMCTL for Linux: Version 19.0.0.0.0 - Production on 12-JUL-2019 09:28:34

Copyright (c) 1996, 2019, Oracle.  All rights reserved.

Current instance cman-test is already started
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=ocf-cman-1)(port=1521)))
The command completed successfully.

Add the service in systemctl

# as root:

cat <<EOF > /etc/systemd/system/cman-test.service
[Unit]
Description=CMAN Service for cman-test
Documentation=http://www.ludovicocaldara.net/dba/cman-oci-install
After=network-online.target

[Service]
User=oracle
Group=oinstall
LimitNOFILE=10240
MemoryLimit=8G
RestartSec=30s
StartLimitInterval=1800s
StartLimitBurst=20
ExecStart=/u01/app/oracle/scripts/cman_service.sh -c cman-test -a start -o /u01/app/oracle/product/cman1930
ExecReload=/u01/app/oracle/scripts/cman_service.sh -c cman-test -a reload -o /u01/app/oracle/product/cman1930
ExecStop=/u01/app/oracle/scripts/cman_service.sh -c cman-test -a stop -o /u01/app/oracle/product/cman1930
KillMode=control-group
Restart=on-failure
Type=forking

[Install]
WantedBy=multi-user.target
Alias=service-cman-test.service
EOF

/usr/bin/systemctl enable cman-test.service

# start
/usr/bin/systemctl start cman-test
# stop
/usr/bin/systemctl stop cman-test

Open firewall ports

By default, new OL7 images use firewalld. Just open the port 1521 from the public zone:

# as root:
firewall-cmd --permanent --add-port=1521/tcp
firewall-cmd --reload

 

Bonus: have a smart environment!

# as root:
yum install -y git rlwrap

# Connect as oracle
sudo su - oracle

# Clone this repository
git clone https://github.com/ludovicocaldara/COE.git

# Enable the profile scripts
echo ". ~/COE/profile.sh" >> $HOME/.bash_profile

# set the cman1930 home by default:
echo "setoh cman1930" >> $HOME/.bash_profile
echo "export TNS_ADMIN=/u01/app/oracle/network/admin" >> $HOME/.bash_profile

# Load the new profile
. ~/.bash_profile
[root@ocf-cman-1 tmp]# su - oracle
Last login: Fri Jul 12 09:49:09 GMT 2019 on pts/0
VERSION         = 19.3.0.0.0
ORACLE_HOME     = /u01/app/oracle/product/cman1930

# [ oracle@ocf-cman-1:/home/oracle [09:49:54] [19.3.0.0.0 [CLIENT] SID="not set"] 0 ] #
# # ahhh, that;s satisfying

Ludo

How to install and access Oracle Weblogic 12.2 in the Oracle Cloud Infrastructure

I put here the steps required to install and access Weblogic in the OCI (mostly for me in case I need to do it again 😉 ). The assumptions are:

  • you already have an account for the Oracle Cloud Infrastructure and you can access the OCI console
  • you already have a Compartment with a VCN and a subnet configured (for test purposes, a VCN created with the default values will be just fine)
  • you already have a keypair for your SSH client (id_rsa, id_rsa.pub)
  • you have an X server on your laptop (if you have Windows, I recommend MobaXTerm, but Xming or other servers are just fine)

Create the compute instance

  • Menu -> Core Infrastructure -> Compute -> Instances -> Create Instance
  • Choose a name for the Instance, all the other fields defaults are fine for test (Oracle Linux 7.6, VM.Standard2.1, etc.)
  • Paste your SSH public key
  • Optionally, under advanced/network, specify a different name for the VM
  • Click on Create to complete the creation

At some point you will have an instance “Green” ready to access:

Click on it and get the public address:

Using your SSH keypair, you can now access the instance with:

$ ssh opc@{public_ip}

 

Setup sshd for SSH tunneling and X11 forwarding

Edit as root the sshd_config:

$ sudo vi /etc/ssh/sshd_config

Modify it so that the following lines are present with these values:

AllowTcpForwarding yes
PermitOpen any
X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost no

Those values are required for X11 forwarding (required for the graphical installation) and for SSH tunneling (required to access  the Weblogic ports without exposing them over internet).

Then restart sshd:

$ sudo systemctl restart sshd

Install the packages for X11 

# sudo yum install xorg-x11-xauth.x86_64
# sudo yum install libXtst
# # optional, to test if X11 forwarding works
# sudo yum install xterm

At this point, it should be possible to forward X11. You can test by reconnecting with:

$ ssh -XC opc@{public_ip}

and then:

$ xterm

Create the oracle user

$ sudo su - root
# groupadd -g 54321 oinstall
# useradd -u 54321 -g oinstall oracle
# passwd oracle

At this point, you can reconnect using oracle directly, so X11 forward will work for the oracle user without any additional setup:

$ ssh -XC oracle@{public_ip}

 

Follow the canonical steps to install weblogic

If you do not know how to do that, follow this good tutorial by Tim Hall (oracle-base):

Oracle WebLogic Server (WLS) 12cR2 (12.2.1) Installation on Oracle Linux 6 and 7

 

Access the Weblogic console from outside Oracle Cloud

If you configured correctly sshd, once the Oracle Weblogic instance is configured and started, you can tunnel to the port (it should be 7001):

$ ssh -L 7001:{vm_name}:7001 oracle@{public_ip}

And be able to browse from your laptop using localhost:7001:

HTH

Ludovico

First draft of a Common Oracle Environment… for the Cloud Database (and not only)

I have just published on GitHub a draft of a common Oracle environment scripts that make the shell environment a little bit smarter than what it is by default. It uses some function and aliases that I have published during the past years.

You can start playing with:

# Connect as oracle
sudo su - oracle

# Clone this repository
git clone https://github.com/ludovicocaldara/COE.git

# Enable the profile scripts
echo ". ~/COE/profile.sh" >> $HOME/.bash_profile

# Load the new profile
. ~/.bash_profile

Ideal for the Oracle Cloud Infrastructure

If you are new to the Oracle Cloud, probably you do not have environment scripts that makes it easy to interact with the database.

The environment scripts that I have published work out-of the box in the cloud (just make sure that you have rlwrap installed so that you can have a better CLI experience).

Actually, they work great as well on-premises, but I assume that you already have something automatic there.

Some examples

  • My famous Smart Prompt 😉 (including version, edition, exit code, etc)
# [ oracle@ludodb01:/home/oracle [22:18:59] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
#
  • u : gets the status of the databases
# [ oracle@ludodb01:/home/oracle [22:18:59] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# u
DB_Unique_Name           DB_Name  ludodb01       Oracle_Home
------------------------ -------- -------------- --------------------------------------------------
CDB_fra1cw               CDB      CDB            /u01/app/oracle/product/18.0.0.0/dbhome_1
  • pmon: just displays the running pmon processes
# [ oracle@ludodb01:/home/oracle [22:27:17] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# pmon
grid      8093     1  0 Mar25 ?        00:01:39 asm_pmon_+ASM1
grid     10293     1  0 Mar25 ?        00:01:43 apx_pmon_+APX1
oracle   11077     1  0 Mar25 ?        00:01:47 ora_pmon_CDB
  • db : sets the environment for a specific DB_NAME, DB_UNIQUE_NAME or SID
# [ oracle@ludodb01:/u01/app/oracle/diag/rdbms/cdb_fra1cw/CDB/trace [22:33:53] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# db CDB
DB_UNIQUE_NAME  = CDB_fra1cw
ORACLE_SID      = CDB
ROLE            = PRIMARY
VERSION         = 18.4.0.0.0
ORACLE_HOME     = /u01/app/oracle/product/18.0.0.0/dbhome_1
NLS_LANG        = AMERICAN_AMERICA.AL32UTF8
  • svcstat : shows the running services (and the corresponding pdb, host, etc) as I described in my previous post
# [ oracle@ludodb01:/home/oracle [22:28:03] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# svcstat
DB_Unique_Name           Service_Name                   PDB                            ludodb01
------------------------ ------------------------------ ------------------------------ --------------
cdb_fra1cw               pdb_service_test               PDB1                           ONLINE
  • s_ : smart alias for sqlplus: connects as sysdba/sysasm by default, or with any arguments that you pass:
# [ oracle@ludodb01:/home/oracle [22:29:14] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# s_

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 15 22:30:22 2019
Version 18.4.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> show user
USER is "SYS"
SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

# [ oracle@ludodb01:/home/oracle [22:30:30] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# s_ pippo/pippo

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 15 22:30:34 2019
Version 18.4.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
  • adr_, dg_ rman_, cm_, lsn_ : aliases for common oracle binaries
  • genpasswd : generates random passwords (default length 30)
# [ oracle@ludodb01:/home/oracle [22:32:35] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# genpasswd
+gagDCqVSgqHqsU+-IdeA0nx_-HVZ1

# [ oracle@ludodb01:/home/oracle [22:33:00] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# genpasswd 12
DiU9nHiwPB9y
  • lsoh: lists the Oracle Homes attached to the inventory
# [ oracle@ludodb01:/u01/app/oracle/diag/rdbms/cdb_fra1cw/CDB/trace [22:33:53] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# lsoh

HOME                        LOCATION                                                VERSION      EDITION
--------------------------- ------------------------------------------------------- ------------ ---------
OraGrid180                  /u01/app/18.0.0.0/grid                                  18.4.0.0.0   GRID
OraDB18000_home1            /u01/app/oracle/product/18.0.0.0/dbhome_1               18.4.0.0.0   DBMS EE
  • setoh: sets the Oracle Home given its name in the inventory
# [ oracle@ludodb01:/u01/app/oracle/diag/rdbms/cdb_fra1cw/CDB/trace [22:35:38] [18.4.0.0.0 [DBMS EE] SID=CDB] 0 ] #
# setoh OraGrid180
VERSION         = 18.4.0.0.0
ORACLE_HOME     = /u01/app/18.0.0.0/grid

 

You might want to install the same environment for oracle, grid (if you have role separation, it should be the case for Cloud DB Systems) and (eventually) root.

I am curious to know if it works well for your environment.

Cheers

Ludo

Oracle Clusterware Services Status at a glance, fast!

If you use Oracle Clusterware or you deploy your databases to the Oracle Cloud, you probably have some application services defined with srvctl for your database.

If you have many databases, services and nodes, it might be annoying, when doing maintenance or service relocation, to have a quick overview about how services are distributed across the nodes and what’s their status.

With srvctl (the official tool for that), it is a per-database operation:

$ srvctl status service
PRKO-2082 : Missing mandatory option -db

If you have many databases, you have to run db by db.

It is also slow! For example, this database has 20 services. Getting the status takes 27 seconds:

# [ oracle@server1:/home/oracle/ [15:52:00] [11.2.0.4.0 [DBMS EE] SID=HRDEV1] 1 ] #
$ time srvctl status service -d hrdev_site1
Service SERVICE_NUMBER_01 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_02 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_03 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_04 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_05 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_06 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_07 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_08 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_09 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_10 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_11 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_12 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_13 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_14 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_15 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_16 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_17 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_18 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_19 is running on instance(s) HRDEV4
Service SERVICE_NUMBER_20 is running on instance(s) HRDEV4

real    0m27.858s
user    0m1.365s
sys     0m1.143s

Instead of operating row-by-row (get the status for each service), why not relying on the cluster resources with crsctl and get the big picture once?

$ time crsctl stat res -f -w "(TYPE = ora.service.type)"
...
...

real    0m0.655s
user    0m0.169s
sys     0m0.098s

crsctl stat res -f  returns a list of ATTRIBUTE_NAME=value for each service, eventually more than one if the service is not singleton/single instance  but uniform/multi instance.

By parsing them with some awk code can provide nice results!

STATE, INTERNAL_STATE and TARGET are useful in this case and might be used to display colours as well.

  • Green: Status ONLINE, Target ONLINE, STABLE
  • Black: Status OFFLINE, Target OFFLNE, STABLE
  • Red: Status ONLINE, Target OFFLINE, STABLE
  • Yellow: all other cases

Here’s the code:

if [ -f /etc/oracle/olr.loc ] ; then
        export ORA_CLU_HOME=`cat /etc/oracle/olr.loc 2>/dev/null | grep crs_home | awk -F= '{print $2}'`
        export CRS_EXISTS=1
        export CRSCTL=$ORA_CLU_HOME/bin/crsctl
else
        export CRS_EXISTS=0
fi

svcstat ()
{
    if [ $CRS_EXISTS -eq 1 ]; then
        ${CRSCTL} stat res -f -w "(TYPE = ora.service.type)" | awk -F= '
function print_row() {
        dbbcol="";
        dbecol="";
        instbcol="";
        instecol="";
        instances=res["INSTANCE_COUNT 1"];
        for(i=1;i<=instances;i++) {
                # if at least one of the services is online, the service is online (then I paint it green)
                if (res["STATE " i] == "ONLINE" ) {
                        dbbcol="\033[0;32m";
                        dbecol="\033[0m";
                }
        }
        # db unique name is always the second part of the resource name
        # because it does not change, I can get it once from the resource name
        res["DB_UNIQUE_NAME"]=substr(substr(res["NAME"],5),1,index(substr(res["NAME"],5),".")-1);

        # same for service name
        res["SERVICE_NAME"]=substr(res["NAME"],index(substr(res["NAME"],5),".")+5,length(substr(res["NAME"],index(substr(res["NAME"],5),".")+5))-4);

        #starting printing the first part of the information
        printf ("%s%-24s %-30s%s",dbbcol, res["DB_UNIQUE_NAME"], res["SERVICE_NAME"], dbecol);

        # here, instance need to map to the correct server.
        # the mapping is node by attribute TARGET_SERVER (not last server)
        for ( n in node ) {
                node_name=node[n];
                status[node_name]="";
                for (i=1; i<=instances; i++) {
                        # we are on the instance that matches the server
                        if (node_name == res["TARGET_SERVER " i]) {
                                res["SERVER_NAME " i]=node_name;
                                if (status[node_name] !~ "ONLINE") {
                                        # when a service relocates both instances get the survival target_server
                                        # but just one is ONLINE... so we need to get always the ONLINE one.
                                        #printf("was::%s:", status[node_name]);
                                        status[node_name]=res["STATE " i];
                                }

                                # colors modes
                                if ( res["STATE " i] == "ONLINE" && res["INTERNAL_STATE " i] == "STABLE" ) {
                                        # online and stable: GREEN
                                        status[node_name]=sprintf("\033[0;32m%-14s\033[0m", status[node_name]);
                                }
                                else if ( res["STATE " i] != "ONLINE" && res["INTERNAL_STATE " i] == "STABLE" ) {
                                        # offline and stable
                                        if ( res["TARGET " i] == "OFFLINE" ) {
                                                # offline, stable, target offline: BLACK
                                                status[node_name]=sprintf("%-14s", status[node_name]);
                                        }
                                        else {
                                                # offline, stable, target online: RED
                                                status[node_name]=sprintf("\033[0;31m%-14s\033[0m", status[node_name]);
                                        }
                                }
                                else {
                                        # all other cases: offline and starting, online and stopping, clearning, etc.: YELLOW
                                        status[node_name]=sprintf("\033[0;33m%-14s\033[0m", status[node_name]);
                                }
                                #printf("%s %s %s %s\n", status[node_name], node[n], res["STATE " i], res["INTERNAL_STATE " i]);
                        }
                }
               printf(" %-14s", status[node_name]);
        }
        printf("\n");
}
function pad (string, len, char) {
        ret = string;
        for ( i = length(string); i<len ; i++) {
                ret = sprintf("%s%s",ret,char);
        }
        return ret;
}
BEGIN {
        debug = 0;
        first = 1;
        afterempty=1;
        # this loop should set:
        # node[1]=server1; node[2]=server2; nodes=2;
        nodes=0;
        while ("olsnodes" | getline a) {
                nodes++;
                node[nodes] = a;
        }
        fmt="%-24s %-30s";
        printf (fmt, "DB_Unique_Name", "Service_Name");
        for ( n in node ) {
                printf (" %-14s", node[n]);
        }
        printf ("\n");
        printf (fmt, pad("",24,"-"), pad("",30,"-"));
        for ( n in node ) {
                printf (" %s", pad("",14,"-"));
        }
        printf ("\n");

}
# MAIN awk svcstat
{
        if ( $1 == "NAME" ) {
                if ( first != 1 && res["NAME"] == $2 ) {
                        if ( debug == 1 ) print "Secondary instance";
                        instance++;
                }
                else {
                        if ( first != 1 ) {
                                print_row();
                        }
                        first = 0;
                        instance=1;
                        delete res;
                        res["NAME"] = $2;
                }
        }
        else  {
                res[$1 " " instance] = $2 ;

        }
}
END {
        #if ( debug == 1 ) for (key in res) { print key ": " res[key] }
        print_row();
}
';
    else
        echo "svcstat not available on non-clustered environments";
        false;
    fi
}

Here’s what you can expect, for 92 services distributed on 4 nodes and a dozen of databases (the output is snipped and the names are masked):

$ time svcstat
DB_Unique_Name     Service_Name       server1  server2  server3  server4
------------------ ------------------ -------- -------- -------- --------
hrdev_site1        SERVICE_NUMBER_01                             ONLINE
hrdev_site1        SERVICE_NUMBER_02                             ONLINE
...
hrdev_site1        SERVICE_NUMBER_20                             ONLINE
hrstg_site1        SERVICE_NUMBER_21                    ONLINE  
hrstg_site1        SERVICE_NUMBER_22                    ONLINE  
...
hrstg_site1        SERVICE_NUMBER_41                    ONLINE  
hrtest_site1       SERVICE_NUMBER_42           ONLINE           
hrtest_site1       SERVICE_NUMBER_43           ONLINE           
...
hrtest_site1       SERVICE_NUMBER_62           ONLINE           
hrtest_site1       SERVICE_NUMBER_63           ONLINE           
hrtest_site1       SERVICE_NUMBER_64           ONLINE           
hrtest_site1       SERVICE_NUMBER_65           ONLINE           
hrtest_site1       SERVICE_NUMBER_66           ONLINE           
erpdev_site1       SERVICE_NUMBER_67  ONLINE                    
erptest_site1      SERVICE_NUMBER_68  ONLINE                    
cmsstg_site1       SERVICE_NUMBER_69  ONLINE                    
cmsstg_site1       SERVICE_NUMBER_70  ONLINE                    
...
cmsstg_site1       SERVICE_NUMBER_74  ONLINE                    
cmsstg_site1       SERVICE_NUMBER_75  ONLINE                    
cmstest_site1      SERVICE_NUMBER_76  ONLINE                    
...
cmstest_site1      SERVICE_NUMBER_81  ONLINE                    
kbtest_site1       SERVICE_NUMBER_82                    ONLINE           
...
kbtest_site1       SERVICE_NUMBER_84                    ONLINE           
reporting_site1    SERVICE_NUMBER_85  ONLINE                    
paydev_site1       SERVICE_NUMBER_86           ONLINE           
payrep_site1       SERVICE_NUMBER_87           ONLINE           
...
paytest_site1      SERVICE_NUMBER_90           ONLINE           
paytest_site1      SERVICE_NUMBER_91           ONLINE           
crm_site1          SERVICE_NUMBER_92                             ONLINE

real    0m0.358s
user    0m0.232s
sys     0m0.134s

I’d be curious to know if it works well for your environment, please comment here. 🙂

Thanks

Ludo