crsctl stat res: syntax and optimization

crsctl is the Oracle Clusterware Control utility which is used to manage your Oracle CRS/GI/Restart configuration and crsctl stat res is most likely the most used/known mix of options used with the crsctl command which provides information about all the resources registered in your cluster. Unfortunately, crsctl stat res is not really humanly readable and is restricted in term of what information is shown and you then need to dig more into crsctl stat res to have more interesting information which is what uses rac-status.sh for example.

For example, if you want to have all the information about the services, you need to use the below syntax (-p is for static configuration and it would be -v for the runtime configuration):

# crsctl stat res -p -w "TYPE = ora.service.type"

crsctl stat res also has some nice syntax which can be used in the filer operators (it is not regexp level but it is pretty cool):

Filter Operators: =, >, <, !=, co, st, en, nc, nci, coi, eqi
  where
    co  --> contains
    st  --> starts with
    en  --> ends with
    nc  --> does not contain
    coi --> contains, case-insensitive
    nci --> does not contain, case-insensitive
    eqi --> equals, case-insensitive

For example, if you want the information about the listeners, and knowing that there are many types of listeners in an Oracle cluster like ora.listener.type, ora.scan_listener.type, ora.leaf_listener.type, ora.asm_listener.type, you could write the easy:

# crsctl stat res -v -w "TYPE co listener"

which will list all the listener types.

As these crsctl stat res commands return many information you may not be interested in, you can grep for what you want from this output. The issue I started to face is that more and more systems have more and more resources (hundreds of services, databases, etc ...) and crsctl stat res (and then my scripts rac-status.sh and oraenv++) was becoming slow which is not what we want. Hopefully, there is an easy way of greatly optimising these performances by using the -attr option allowing you to select only the columns you want:

[root@exa01db01]# time crsctl stat res -p -w "TYPE = ora.service.type" > /dev/null
real    0m33.047s
user    0m31.989s
sys     0m0.390s
[root@exa01db01]# time crsctl stat res -p -w "TYPE = ora.service.type" -attr "NAME,TYPE,ACL,ENABLED,ENABLED@SERVERNAME(exa01db01),ENABLED@SERVERNAME(exa01db02),ROLE,PLUGGABLE_DATABASE" > /dev/null
real    0m2.249s   <== 16 times faster !
user    0m0.882s
sys     0m0.164s
[root@exa01db01]#

Wow, 16 times faster in specifying the columns you want ! this crsctl -attr is a rocket !

This is how I recently greatly improved the performances of rac-status.sh and oraenv++. I just found one weird behavior when using the -attr option, see below:

[root@exa01db01]# crsctl stat res -v -w "NAME co LISTENER_SCAN2"
NAME=ora.LISTENER_SCAN2.lsnr
LAST_RESTART=11/09/2021 19:38:10
LAST_STATE_CHANGE=11/09/2021 19:38:10
[root@exa01db01]# crsctl stat res -v -w "NAME co LISTENER_SCAN2" -attr "NAME,LAST_RESTART,LAST_STATE_CHANGE"
NAME=ora.LISTENER_SCAN2.lsnr
LAST_RESTART=1636447090
LAST_STATE_CHANGE=1636447090
[root@exa01db01]# 

See these date formats ? they are different whether you use the -attr option or not. The date format is a human readable one without -attr and is an epoch sec if you use the -attr option. No big deal but it is good to know if you work on the restart dates:

[root@exa01db01]# date -d @1636447090
Tue Nov  9 19:38:10 AEDT 2021
[root@exa01db01]#

Bye for now !

Grid Infrastructure Out of Place Patching (aka GI OOP)

Out of Place patching has become the standard for database patching for years now (I have described it precisely here) but for any reason, people restrain themselves for doing Out of Place patching for Grid Infrastructure and usually do In Place GI patching and Out of Place GI upgrade (you cannot do In Place upgrade :)). I will describe below how to easily perform GI OOP.

To start on the right foot, a quick reminder of the concept and the required steps of an Out of Place patching:

1. Your system is running on a source version home let's say /u01/app/19.0.0.0/grid
2. You prepare the future alread patched target home let's say /u01/app/19.11.0.0/grid
3. The day of the maintenance, you stop what is running on the source home and restart on the target home
4. If, for any reason, something goes wrong, you just have to restart everything on the source home

This can be represented with the below image:

For the purpose of this blog, I will use the below homes in the examples:

• The source GI home: /u01/app/19.0.0.0/grid
• The target GI Home: /u01/app/19.11.0.0/grid

1. Prepare your target home

Preparing the target home is to prepare a GI home with the patches you will want to use; here, I will go with GI 19.11 with the latest opatch, the latest GI JDK and patch 31602782. To achieve this, you can clone a source GI Home or, what I prefer and recommend, to create a gold image of your target home. Oracle has/had a note with a list of already prepared gold image per version but this note has kind of disappeared recently so I gave up on that one. Also, building your own gold image is easy and very good to know how all of that works. To build my target GI 19.11 gold image, you first need to get:

• The base GI 19c version which is 19.3: GI_gold_193_V982068-01.zip -- from edelivery.oracle.com
• The GI 19.11 patch: GI_1911_p32545008_190000_Linux-x86-64.zip
• The latest opatch: opatch_p6880880_122010_Linux-x86-64.zip
• The latest GIJDK: GIJDK_April2021_p32490416_190000_Linux-x86-64.zip (this one is no more the latest but it was the latest when I did this gold image)
• The patch 31602782: p31602782_1911000DBRU_Linux-x86-64.zip

Note: you may want to have a look at this blog to get the notes where to download GI, the critical patches, GI JDK, etc... Note 2: you do not have to apply the latest GI JDK, it is to show that you can apply any one-off patch on top of the RUR in your target gold image -- GI tends to have many critical issues and then patches so better to be know how to deal with it. Here is what it looks like once you have the files on your server:

[root@target gioop]# pwd
/u01/stage/gioop
[root@target gioop]# ls -ltr
GI_1911_p32545008_190000_Linux-x86-64.zip               <= GI 19.11 patch
GIJDK_April2021_p32490416_190000_Linux-x86-64.zip       <= April JDK
opatch_p6880880_122010_Linux-x86-64.zip                 <= Latest opatch
GI_gold_193_V982068-01.zip                              <= GI gold image
p31602782_1911000DBRU_Linux-x86-64.zip                  <= Patch 31602782
[root@target gioop]#

Unzip the 19.3 gold image:

[root@target gioop]# mkdir temp
[root@target gioop]# unzip -q GI_gold_193_V982068-01.zip -d temp/.
[root@target gioop]#

Unzip the GI JDK and the 31602782 patch (any number of one-off patches):

[root@target gioop]# unzip -o -q GIJDK_April2021_p32490416_190000_Linux-x86-64.zip
[root@target gioop]# unzip -o -q p31602782_1911000DBRU_Linux-x86-64.zip
[root@target gioop]#

Very importantly, all needs to be done as the oracle (grid owner) user (not root) so give the correct permissions and you should have the below situation:

[root@target gioop]# chown -R oracle:oinstall /u01/stage/gioop
[root@target gioop]# ls -ltr
oracle oinstall       4096 Apr 20 07:17 32545008                                           <= GI 19.11 patch
oracle oinstall       2477 Apr 22 16:16 PatchSearch.xml
oracle oinstall 2523672126 May  7 11:28 GI_1911_p32545008_190000_Linux-x86-64.zip
oracle oinstall  125203135 May  7 11:28 GIJDK_April2021_p32490416_190000_Linux-x86-64.zip
oracle oinstall  120761121 May  7 11:28 opatch_p6880880_122010_Linux-x86-64.zip
oracle oinstall 2889184573 May  7 12:26 GI_gold_193_V982068-01.zip
oracle oinstall       4096 May  7 12:28 temp                                               <= GI gold image 
oracle oinstall       4096 May  7 12:33 32490416                                           <= GI JDK
oracle oinstall       4096 Apr 25 21:04 31602782                                           <= Patch 31602782
[root@target gioop]#

Start by upgrading opatch to the latest version:

[root@target gioop]# su - oracle
[oracle@target:]/home/oracle => cd /u01/stage/gioop/temp
[oracle@target:]/u01/stage/gioop/temp => ./OPatch/opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
[oracle@target:]/u01/stage/gioop/temp => unzip -o -q ../opatch_p6880880_122010_Linux-x86-64.zip
[oracle@target:]/u01/stage/gioop/temp =>./OPatch/opatch version
OPatch Version: 12.2.0.1.24
OPatch succeeded.
[oracle@target:]/u01/stage/gioop/temp =>

We can now patch the gold image with GI 19.11, the GI JDK and the patch 31602782; we can do all of this in a single command line:

[oracle@target:]/u01/stage/gioop/temp => ./gridSetup.sh -silent -printtime -waitForCompletion -noCopy -applyRU /u01/stage/gioop/32545008 -applyOneOffs /u01/stage/gioop/31602782,/u01/stage/gioop/32490416
Preparing the home to patch...
Applying the patch /u01/stage/gioop/32545008...
Successfully applied the patch.
Applying the patch /u01/stage/gioop/31602782...
Successfully applied the patch.
Applying the patch /u01/stage/gioop/32490416...
Successfully applied the patch.
The log can be found at: /u01/app/oraInventory/logs/GridSetupActions2021-05-07_01-12-59PM/installerPatchActions_2021-05-07_01-12-59PM.log
Launching Oracle Grid Infrastructure Setup Wizard...
[FATAL] [INS-40426] Grid installation option has not been specified.       <== you can ignore this error
   ACTION: Specify the valid installation option.
[oracle@target:]/u01/stage/gioop/temp =>

Before continuing, we need to temporarely attach the home to the system:

[oracle@target:]/home/oracle => /u01/app/19.0.0.0/grid/oui/bin/runInstaller -attachHome ORACLE_HOME=/u01/stage/gioop/temp ORACLE_HOME_NAME=gold_gi1911
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 24575 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
You can find the log of this install session at:
 /u01/app/oraInventory/logs/AttachHome2021-05-07_01-53-14PM.log
'AttachHome' was successful.
[oracle@target:]/home/oracle =>

Note that you have to attach the home to be able to use opatch and create a gold image but you cannot apply the RU nor the one-off patches if the home is attached:

[oracle@target:]/u01/stage/gioop/temp => ./gridSetup.sh -silent -printtime -waitForCompletion -noCopy -applyRU /u01/stage/gioop/32545008 -applyOneOffs /u01/stage/gioop/31602782,/u01/stage/gioop/32490416
[INS-32826] The software home (/u01/stage/gioop/temp) is already registered in the central inventory. Refer to patch readme instructions on how to apply.
[oracle@target:]/u01/stage/gioop/temp =>

We now have a prepared target home with our target version located in a temporary directory. We can verify the list of patch of our home:

[oracle@target:]/u01/stage/gioop/temp => ./OPatch/opatch lspatches -oh /u01/stage/gioop/temp
31602782;SAME INSTANCE SLAVE PARSE FAILURE FLOOD CONTROL
32490416;JDK BUNDLE PATCH 19.0.0.0.210420
32585572;DBWLM RELEASE UPDATE 19.0.0.0.0 (32585572)
32584670;TOMCAT RELEASE UPDATE 19.0.0.0.0 (32584670)
32579761;OCW RELEASE UPDATE 19.11.0.0.0 (32579761)
32576499;ACFS RELEASE UPDATE 19.11.0.0.0 (32576499)
32545013;Database Release Update : 19.11.0.0.210420 (32545013)

OPatch succeeded.
[oracle@target:]/u01/stage/gioop/temp =>

We will now create our own gold image which we could easily copy and deploy on all the other systems (dev, qa, dr, prod, etc ...):

[oracle@target:]/u01/stage/gioop/temp =>  ./gridSetup.sh -silent -createGoldImage -destinationLocation /u01/stage/gioop/
Launching Oracle Grid Infrastructure Setup Wizard...
Successfully Setup Software.
Gold Image location: /u01/stage/gioop/grid_home_2021-05-07_01-59-02PM.zip
[oracle@target:]/u01/stage/gioop/temp =>

You can now save the prepared gold image /u01/stage/gioop/grid_home_2021-05-07_01-59-02PM.zip on a central repository server as this is the image you will be usng on all your systems -- this is your future GI !

To keep your systems clean, let's detach the temporary home:

[oracle@target:]/u01/stage/gioop/temp => /u01/app/19.0.0.0/grid/oui/bin/runInstaller -detachHome ORACLE_HOME=/u01/stage/gioop/temp ORACLE_HOME_NAME=gold_gi1911
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 24575 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
[oracle@target:]/u01/stage/gioop/temp =>

2.Switch the home

Create the target GI directory on all the servers

[root@exadb01 ~]# cat ~/dbs_group
exadb01
exadb02
. . .
exadb08
[root@exadb01 ~]# dcli -g ~/dbs_group -l root "df -h /u01"
Filesystem                    Size  Used Avail Use% Mounted on
exad01: /dev/mapper/VGExaDb-LVDbOra1  250G  125G  125G  50% /u01          <= check that you have enough disk space on each node
. . .
[root@exadb01 ~]# dcli -g ~/dbs_group -l root "mkdir -p /u01/app/19.11.0.0/grid; chown -R oracle:oinstall /u01/app/19.11.0.0/grid"
[root@exadb01 ~]# 

Unzip the previously prepared goldimage (only on one node !)

[oracle@exadb01:]/home/oracle => unzip -q /u01/stage/gioop/GI_gold_1911_2021-05-07_01-59-02PM.zip -d /u01/app/19.11.0.0/grid
[oracle@exadb01:]/home/oracle => dcli -g ~/dbs_group -l oracle "du -sh /u01/app/19.11.0.0/grid"
exadb01: 9.9G      /u01/app/19.11.0.0/grid      <== your gold image unzipped here 
exadb02: 4.0K      /u01/app/19.11.0.0/grid      <== empty directory here
. . .
exadb08: 4.0K      /u01/app/19.11.0.0/grid<     <== empty directory here
[oracle@exadb01:]/home/oracle =>

Something important here to aboid issues during the patch process; verify that the ASM passwordfile and the ASM spfile is located under ASM (if not, you'll find a quick procedure here on how to move them to ASM):

[root@exadb01 ~]# . oraenv <<< +ASM1
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
[root@exadb01 ~]# asmcmd spget
+DATA/mycluster/ASMPARAMETERFILE/registry.253.1045914043
[root@exadb01 ~]# asmcmd pwget --asm
+DATA/orapwASM
[root@exadb01 ~]#

Prepare a responsefile such as this one:

[oracle@exadb01:+ASM1]/home/oracle => cat /u01/stage/gioop/1911oop_response.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0
oracle.install.option=CRS_SWONLY
ORACLE_BASE=/u01/app/oracle
oracle.install.asm.OSDBA=oinstall
oracle.install.asm.OSOPER=oinstall
oracle.install.asm.OSASM=oinstall
oracle.install.crs.config.ClusterConfiguration=STANDALONE
[oracle@exadb01:+ASM1]/home/oracle =>

Gridsetup, this will only copy the software across all the nodes, this will NOT modify anything else

[oracle@exadb01:]/u01/app/19.11.0.0/grid => ./gridSetup.sh -silent -responseFile /u01/stage/gioop/1911oop_response.rsp -waitForCompletion
Launching Oracle Grid Infrastructure Setup Wizard...

[WARNING] [INS-41813] OSDBA for ASM, OSOPER for ASM, and OSASM are the same OS group.
   CAUSE: The group you selected for granting the OSDBA for ASM group for database access, and the OSOPER for ASM group for startup and shutdown of Oracle ASM, is the same group as the OSASM group, whose members have SYSASM privileges on Oracle ASM.
   ACTION: Choose different groups as the OSASM, OSDBA for ASM, and OSOPER for ASM groups.
[WARNING] [INS-41874] Oracle ASM Administrator (OSASM) Group specified is same as the inventory group.
   CAUSE: Operating system group oinstall specified for OSASM Group is same as the inventory group.
   ACTION: It is not recommended to have OSASM group same as inventory group. Select any of the group other than the inventory group to avoid incorrect configuration.
The response file for this session can be found at:
 /u01/app/19.11.0.0/grid/install/response/grid_2021-05-10_10-54-29AM.rsp

You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2021-05-10_10-54-29AM/gridSetupActions2021-05-10_10-54-29AM.log

As a root user, execute the following script(s):
        1. /u01/app/19.11.0.0/grid/root.sh

Execute /u01/app/19.11.0.0/grid/root.sh on the following nodes:
[exadb01]

As instructed, run this root.sh script:

[root@exadb01 ~]# /u01/app/19.11.0.0/grid/root.sh
Check /u01/app/19.11.0.0/grid/install/root_exadb01.domain.com_2021-05-10_11-03-09-927603750.log for the output of root script
[root@exadb01 ~]# cat /u01/app/19.11.0.0/grid/install/root_exadb01.domain.com_2021-05-10_11-03-09-927603750.log
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/19.11.0.0/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Cluster or Grid Infrastructure for a Stand-Alone Server execute the following command as oracle user:
/u01/app/19.11.0.0/grid/gridSetup.sh
This command launches the Grid Infrastructure Setup Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that is available in the installation media.

[root@exadb01 ~]#

OK, this was the last step to be done before the real maintenance, the next steps are do be done under a window maintenance only as the GI will be switched to the new home node by node stopping all the resources running on the old GI home and restarting all the resources on the new GI home. I will recommend using the rac-status.sh script to check the status of all the resources of the cluster before switching the home -- and do the same after the home switching to ensure that your maintenance is idempotent:

[oracle@exadb01:]/home/oracle => /u01/app/19.11.0.0/grid/gridSetup.sh -silent -switchGridHome
Launching Oracle Grid Infrastructure Setup Wizard...

You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2021-05-10_11-05-43AM.log

As a root user, execute the following script(s):
        1. /u01/app/19.11.0.0/grid/root.sh

Execute /u01/app/19.11.0.0/grid/root.sh on the following nodes:
[exadb01, exadb02, exadb03, exadb04, exadb05, exadb06, exadb07, exadb08]

Run the scripts on the local node first. After successful completion, run the scripts in sequence on all other nodes.

Successfully Setup Software.
[oracle@exadb01:]/home/oracle =>

Now, strictly follow the instructions and run the root.sh scripts as instructed; do NOT run them concurrently on multiple nodes; note that they will take time to run:

[root@exadb01 ~]# /u01/app/19.11.0.0/grid/root.sh
Check /u01/app/19.11.0.0/grid/install/root_exadb01.domain.com_2021-05-10_11-11-21-158300990.log for the output of root script
[root@exadb01 ~]#

And so on on all the nodes one by one ... and you are done ! hmm not exactly you need to update your /etc/oratab (on each node) as the ASM entry will be removed by the patching:

[root@exadb01 ~]# grep ASM /etc/oratab
+ASM1:/u01/app/19.11.0.0/grid:N

You can have a look at the inventory and you could see the old and new GI Home as below:

[root@exadb01 ~]# dcli -g ~/dbs_group -l root "grep -i grid /u01/app/oraInventory/ContentsXML/inventory.xml"
exadb01: <HOME NAME="OraGI19Home1" LOC="/u01/app/19.0.0.0/grid" TYPE="O" IDX="1">                     <== old
exadb01: <HOME NAME="OraGI19Home2" LOC="/u01/app/19.11.0.0/grid" TYPE="O" IDX="19" CRS="true"/>       <== new
. . .
exadb08: <HOME NAME="OraGI19Home1" LOC="/u01/app/19.0.0.0/grid" TYPE="O" IDX="1">                     <== old
exadb08: <HOME NAME="OraGI19Home2" LOC="/u01/app/19.11.0.0/grid" TYPE="O" IDX="14" CRS="true"/>       <== new
[root@exadb01 ~]#

Now you are all done ! a last check with rac-status.sh to ensure that everything is running as expected and you can use the same gold image and procedure to all your GIs !

3. The Rollback procedure

In case of something goes wrong during or after you have switched to your new home, you need to have a tested rollback procedure and the beauty of Out of Place patching is that the old home is still on the system, untouched, as it was before. You then just have to switch back to the old home.
Note that the below chown -R oracle:oinstall (or to the grid owner) is mandatory; indeed, the switch is ran as root and root.sh will later on put the correct privileges back in place.

[root@exadb01 ~]# dcli -g ~/dbs_group -l root "chown -R oracle:oinstall /u01/app/19.0.0.0/grid"           <== this is mandatory
[root@exadb01 ~]# su - oracle
[oracle@exadb01:]/home/oracle => /u01/app/19.0.0.0/grid/gridSetup.sh -silent -switchGridHome
Launching Oracle Grid Infrastructure Setup Wizard...

You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2021-05-10_12-25-25PM.log

As a root user, execute the following script(s):
        1. /u01/app/19.0.0.0/grid/root.sh

Execute /u01/app/19.0.0.0/grid/root.sh on the following nodes:
[exadb01, exadb02, exadb03, exadb04, exadb05, exadb06, exadb07, exadb08]

Run the scripts on the local node first. After successful completion, run the scripts in sequence on all other nodes.

Successfully Setup Software.
[oracle@exadb01:]/home/oracle =>

Same as before, now run the root.sh script on each node one by one, do not run them concurrently.

[root@exadb01 ~]# /u01/app/19.0.0.0/grid/root.sh
Check /u01/app/19.0.0.0/grid/install/root_exadb01.domain.com_2021-05-10_12-36-17-716165992.log for the output of root script
[root@exadb01 ~]#
. . .
[root@exadb01 ~]# ssh exadb08
Last login: Mon May 10 11:44:34 2021 from exadb01.domain.com
[root@exadb08 ~]# /u01/app/19.0.0.0/grid/root.sh
Check /u01/app/19.0.0.0/grid/install/root_exadb08.domain.com_2021-05-10_12-49-56-584075655.log for the output of root script
[root@exadb08 ~]#

No, refix back your oratab, execute rac-status.sh to check that everything is back up and running as expected and you are all done !

I finally found my top Orace 12c-19c database feature !

Every Oracle database version comes with tons of new features (or renamed features to kind of re release a feature which was not really well implemented in a previous version :)) which are advertised a lot but when you think back about these features, which ones were really the top new feature of each version ?

I can easily name what I consider (this is indeed very subjective) to be the best feature of each version up to 11g but honnestly, for the 12c family (12, 18 and 19), I had nothing in mind (also may be because I have been doing less database work for years now). Let's start by my top feature for each version:

• Oracle 7: CBO -- I haven't worked that much with Oracle 7 though but well, CBO is a huge feature


• Oracle 8: RMAN -- We finally had something more integrated and more efficient than BEGIN/END BACKUP which was kind of a hassle. I also remind funny stories when interviewing to recruit people: "Me: do you know RMAN ?", "Candidate: Hermann Maier ? yes he is very good at ski racing", "Me: OK, we'll call you" :D


• Oracle 8i: if 8i is a different version than 8 (some were saying that at that time), I would say Java in the database ! haha joking obviously; not sure this was the best idea ever as we still cannot really patch it online 20 years later and it is still full of vulnerabilities we have to fix every single month :) I would then say Partitionning -- 8 had partitionning but it was very basic, it started to be really usable in 8i; if 8i is not a different version than 8 then I go with RMAN for 8/8i


• Oracle 9i: dbms_metadata.get_ddl -- how awesome it was when get_ddl was released ! what a pain it was to generate a DDL in the previous versions ! query tons of different data dict views, doing export and clean the DDL from the export file, using some graphical tools, ... there was no easy and 100% reliable way, get_ddl was clearly a relief, long live get_ddl ! :)


• Oracle 10g: AWR -- no need to argue here, there was clearly a before and an after in term of performance investigations and tuning thanks to AWR


• Oracle 11g: Snapshot Standby -- we could already do this in 10g with restore points, I remember I had scripted this in the past but Snapshot Standbys made it easy (and official !) to open read write a copy of a production to developper and resync it at night, excellent feature; I could also have said Exadata ! but at the time Exadata was released, it was still kind of confidential, it took time for people to use / trust Exadata so for me, Snapshot Standby stays the top 11g feature

Then 12c came and I couldn't find anything really appealling to me. I was thinking about the online datafiles move which is indeed a very cool feature but Oracle has mainly addressed these kind of datafiles placement issues in previous versions with OMF, db_file_create_dest, etc ... so even if this is a very cool feature, it would have had a bigger impact if implemeted in 8 than in 12. One would say CDB/PDB; it is indeed the major architecture change but I personnaly don't really like it and making it default makes everything more complicated for 99% for may be 1% who would really need this feature so for me, it is not really the best 12c family feature. Also, it is not totally integrated with CRS which is why I cannot show the PDBs in rac-status.sh. It should be coming with GI 21c (as GI 20c has not been released) -- we'll see.

And suddenly, with 19c, came my top 12c family feature:

• Services can now automatically failback to the preferred node using -failback yes !!

Oh boy, I have always been waiting for this feature ! For everyone who have done patching / upgrades (everyone ?), you know how messy it is with the services: they restart on the available nodes when you patch the preferred node, application teams want to run on a specific node because they application is still not RAC-friendly or whatever reason, etc... You can easily know what services have moved during a maintenance using rac-status but when it comes to rebalance everything to the preferred node(s), this is another story. Indeed, CRS does not show the preferred / available nodes information with crsctl and you then have to svrctl config service to get this information knowing that you need to use the srvctl command from the database home version (and not CRS !) for this as a wrong srvctl version used for this would fail, .. in short, it is a big mess for something which should be easy in my opinion.

But this was before -failback yes feature ! you just have to set it up on a service like this:

srvctl config service -d DB_NAME -s SERVICE_NAME -failback yes

Then at next restart of the server, GI or instance, the service will automatically been rebalanced to the preferred node -- gracefully, without disconnecting anyone -- very cool, right ?!

To help setting that up, I have then wrote 2 scripts to be able to easily take advantage of this feature:

• svc-show-config.sh to show the current services configuration
• svc-set-failback-yes.sh to to set the failback option to your services

Enjoy !

svc-set-failback-yes.sh: Make your Orace services to automatically failback to the preferred nodes

A very cool feature has been released with Oracle 19c: the -failback yes option to make your services to automatically failback to the preferred node ! I then wrote this script to easily set that up:

[root@exadb01 ~]# ./svc-set-failback-yes.sh -d PROD
2021-02-18_084727 [QUESTION] You are about to modify your system (failback = yes to your databases services), are you sure you want to proceed ? [y/n] (yes/exit)
y
2021-02-18_084728 [INFO] It may be slow if you have many services as srvctl is slow when a database has many services.
2021-02-18_084729 [INFO] Database: PROD
2021-02-18_084736 [INFO] Succesfully set the service PROD_APP1 in failback mode.
2021-02-18_084736 [INFO] Succesfully set the service PROD_APP2 in failback mode.
2021-02-18_084736 [INFO] Service PROD_REPORTING is already in failback mode.
2021-02-18_084736 [INFO] Service PROD_BATCH is already in failback mode.
[root@exadb01 ~]#

You will first note that I ask for a confirmation before setting that up as it will modify your settings so better being sure in case of you start this script by mistake. You can suppress this message by using the -y option.
Then a warning that it can be slow as srvctl is slow when managing many services so it can take some time.
With no option, it will set the services with failback yes of all the databases defined in /etc/oratab (ASM, agent and MGMTDB are ignored).
With -d option, you can choose a specific database:

$ ./svc-set-failback-yes.sh -d PROD

You can also grep a pattern in /etc/oratab like this:

$ ./svc-set-failback-yes.sh -g PROD

This will set the services configuration on all the *PROD* databases. The default greps "19" in oratab as it is a 19c feature.
Use -h option for help and example as usual.
Used in coordination with svc-show-config.sh, you can easily check your config, set up -failback yes and check again to verify:

$./svc-show-config.sh
$./svc-set-failback-yes.sh
$./svc-show-config.sh

Also note that i works as root and as oracle. You can download it here, enjoy !

srvctl add oraclehome

Recently I was installing some new databases homes on some Exadatas and I was wondering how the Homes were "known" by the cluster (indeed, when rac-status.sh shows the Homes, I got them as a characteristic of a database and not from a list of registered homes) and I then found this srvctl add oraclehome option. This was on a 12.2 GI and honestly I never noticed it before.

The command help looks like this:

[root@exadatadb01]# srvctl add oraclehome -h
Adds the Oracle home resource to the Oracle Clusterware.
Usage: srvctl add oraclehome -name  -path  [-type {ADMIN|POLICY}] [-node ]
    -name               Identifier for the Oracle home resource
    -path               Oracle home path
    -type               {ADMIN|POLICY}      Type of the specified Oracle home. Default is POLICY.
    -node               Comma-separated list of nodes in which the Oracle home will be available
    -help               Print usage
[root@exadatadb01]#

I then tried to add my new home in my cluster and could check it:

[oracle@exadatadb01]$ srvctl add oraclehome -name TEST -path /u01/app/oracle/product/12.2.0.1/dbhome_2 -type admin -node exadatadb01,exadatadb02
[oracle@exadatadb01]$ srvctl config oraclehome -name TEST
Oracle home path: /u01/app/oracle/product/12.2.0.1/dbhome_2
Oracle home name: test
Oracle home type: ADMIN
Node list: exadatadb01,exadatadb02
Shared: FALSE
Oracle base: /u01/app/oracle
Databases configured:
Listeners configured:
Oracle home test is enabled.
Oracle home resource is individually enabled on nodes:
Oracle home resource is individually disabled on nodes:
[oracle@exadatadb01]$

And we can have information about this home using crsctl as well:

[root@exadatadb01]# crsctl stat res -p -w "TYPE = ora.home.type"
NAME=ora.test.home
TYPE=ora.home.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
. . .

Cool, huh ? now the question is "what is the use of that ?" indeed, I have checked on the other RAC/GI systems I have handy and no ORACLE_HOME is registered in the clusters, not in any configuration. We could also wonder what would be the use of having an ORACLE_HOME registered in the cluster ? an ORACLE_HOME is just some files and directories in a filesystem, what could be the value of CRS knowing about the ORACLE_HOME ? I then tried to google and MOS about this srvctl add oraclehome command and I absolutely found nothing !

As I do not like to keep things I do not understand their use, I decided to unregister my TEST ORACLE_HOME to keep a clean cluster configuration:

[oracle@exadatadb01]$ srvctl remove oraclehome -name TEST

Which worked fine. Then I thought I could do more testing on this feature and registered my home again:

[oracle@exadatadb01]$ srvctl add oraclehome -name TEST -path /u01/app/oracle/product/12.2.0.1/dbhome_2 -type admin -node exadatadb01,exadata02
PRCS-1085 : Server category test already exists 
PRCR-1086 : server category test is already registered

Which didn't work as it seems that the srvctl remove oraclehome left some categories behind which I found no way to remove (yet). A MOS SR gave me the information that I was hitting this bug:

Bug 25978527 - LNX64-12.2-SRVCTL:SRVCTL REMOVE ORACLEHOME SHOULD ALSO REMOVE CATEGORY CASCADELY 

. . . which I can acces no information about it so I don't know if there is a patch or a workaround for this.

To sum that up, I am not sure about the use of registering an ORACLE_HOME in the cluster, I found no information about this nowhere (google, MOS) and ... the remove oracle_home does not work properly so ... let's say that this feature is good to know and let's see if it becomes a real new feature in the future ! Let me know in the comments if you have a clue about a potential use of this.

An interesting point here though is that CRS not being aware of the ORACLE_HOMEs, GI is then also not aware of the versions of the ORACLE_HOMEs running there and then the pre-requisite saying that GI version has to be >= DB version cannot be enforced by GI -- which I tested and I can assure that it is indeed the case and you can run a DB Home version > GI version with no issue -- well except that it is not supported but it works with no problem as GI is not aware about the DB Home version :)

Upgrade Grid Infrastructure to 18c

Now that GI 19c has been released, it is time to upgrade your GI to 18c !
I will be presenting here a well tested procedure to upgrade your Grid Infrastructure to 18c; it has been designed and applied on many Exadatas and the procedure can also be applied on non-Exadata systems. You will notice that it is very close to upgrade your Grid Infrastructure to 12c.

0/ Preparation

Please find few things that are good to know and read before starting upgrading a GI to 18c:

• Download the GI 18c image from edelivery: V978971-01.zip
• Download the RU you want to apply like Patch 28828717: GI RELEASE UPDATE 18.5.0.0.0; As I use to do this kind of maintenance when I patch the Exadata stack, I usually use the GI RU provided in the Exadata bundle like 28980183/Database/18.5.0.0.0/18.5.0.0.190115GIRU/28828717 from the Jan 19 Bundle; you can do either way, it is the same RU.
• GI 18c will be installed on /u01/app/18.1.0.0/grid
• GI 12.1 is running from /u01/app/12.1.0.2/grid on the systems I work on
• This procedure has been successfully applied on many Exadatas half rack and full rack; it also applies to non-Exadata GI system
• I use the rac-status.sh script to check the status of al the resources of my cluster before and after the maintenance to avoid any unpleasantness
• Check your oratab entries to avoid having them deleted during the upgrade as explained in this post

Versions naming:

It is not a secret to anyone, Oracle's version naming has always been changed in a not really consistent manner across the years making it not really easy to follow and then this new style of yearly release also came with its new version numbering. Indeed, we used to name our directories with the version number like 12.1.0.2 and then create a new directory like 12.2.0.1 when we would perform an out of place upgrade to a new major version.
Nowadays, even if 18c is actually version 12.2.0.2, the database came with a 18.1 version and GI came with a 18.0 version and each RU will increment the second number of the version, January 19 RU (patch 28828717) being GI version 18.5, and so on.
I had a discussion with some friends about how to name the GI directory knowing that and also that nowadays, Oracle promotes the Rapid Home Provisionning tool for an "always out of place" patch strategy.
We decided that we wouldn't go with RHP nor an out of place patching for each RU but for every major release as we were doing before. It would then have made no sense to name our directory /u01/app/18.5.0.0/grid for GI upgrade to 18c + Jan 19 Bundle as April 19 Bundle would upgrade the version to 18.6; we then have decided to name our GI 18c directory with the original version number /u01/app/18.1.0.0/grid which makes it clear to everyone (and 18.0 looked weird to me, indeed, a "0" version makes not much sense to me even if /u01/app/18.0.0.0/grid is the path used in Oracle Cloud). Also, we kept 4 numbers in the naming to stay consistent with what we were doing before and with the databases Homes.
And the day you need to double check what exact patch is installed here, we would rely on something like lspatches which you would also do in any case as one off patches may have been installed on your Homes and you would not have renamed the directory.
Having said that, you can name it as you wish :)

1/ Install GI 18c from the gold image

Let's enjoy this super new feature and quickly install GI 18c from a gold image:

-- Create the target directories for GI 18c
sudo su -
dcli -g ~/dbs_group -l root "ls -ltr /u01/app/18.1.0.0/grid"
dcli -g ~/dbs_group -l root mkdir -p /u01/app/18.1.0.0/grid
dcli -g ~/dbs_group -l root chown grid:oinstall /u01/app/18.1.0.0/grid
dcli -g ~/dbs_group -l root "ls -altr /u01/app/18.1.0.0/grid"

-- Install GI using this gold image : /patches/V978971-01.zip
sudo su - grid
unzip -q /patches/V978971-01.zip -d /u01/app/18.1.0./grid

2/ Pre requisites

2.1/ Upgrade opatch

As usual, it is recommended to patch opatch before starting any patching activity. If you work with Exadata, you may have a look at this post where I show how to quickly upgrade opatch with dcli.

2.2/ ASM spfile and password file

Check that the ASM passwordfile and the ASM spfile are located under ASM to avoid issues during the upgrade:

[grid@exadb01]$ asmcmd spget
+DATA/mycluster/ASMPARAMETERFILE/registry.253.909449003
[grid@exadb01]$ asmcmd pwget --asm
+DATA/orapwASM
[grid@exadb01]$

If you don't, you may face the below error and ASM won't restart after being upgraded:

Verifying Verify that the ASM instance was configured using an existing ASM
parameter file. ...FAILED
PRCT-1011 : Failed to run "asmcmd". Detailed error:
ASMCMD-8001: diskgroup 'u01' does not exist or is not mounted

Please find a quick procedure to move the ASM passwordfile from a FileSystem to ASM:

[grid@exadb01]$ asmcmd pwcopy /u01/app/12.1.0.2/grid/dbs/orapw+ASM +DBFS_DG/orapwASM
[grid@exadb01]$ asmcmd pwset --asm +DBFS_DG/orapwASM
[grid@exadb01]$ asmcmd pwget --asm

2.3/ Prepare a responsefile such as this one

[grid@exadb01]$ egrep -v "^#|^$" /tmp/giresponse.rsp | head -10
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v18.1.0
INVENTORY_LOCATION=
oracle.install.option=UPGRADE
ORACLE_BASE=/u01/app/oracle
oracle.install.asm.OSDBA=dba
oracle.install.asm.OSOPER=dba
oracle.install.asm.OSASM=dba
oracle.install.crs.config.gpnp.scanName=
oracle.install.crs.config.gpnp.scanPort=
oracle.install.crs.config.ClusterConfiguration=
[grid@exadb01]$

2.4/ System pre requisites

Check these system pre requisites:

-- a 10240 limits for the "soft stack" (if not, set it, log off and log on)
[root@exadatadb01]# dcli -g ~/dbs_group -l root grep stack /etc/security/limits.conf | grep soft
exadatadb01: * soft stack 10240
exadatadb02: * soft stack 10240
exadatadb03: * soft stack 10240
exadatadb04: * soft stack 10240
[root@exadatadb01]#

-- at least 1500 huge pages free
[root@exadatadb01]# dcli -g ~/dbs_group -l root grep -i huge /proc/meminfo
....
AnonHugePages: 0 kB
HugePages_Total: 200000
HugePages_Free: 132171
HugePages_Rsvd: 38338
HugePages_Surp: 0
Hugepagesize: 2048 kB
....
[root@exadatadb01]#

2.5/ Run the pre requisites

This step is very important and the logs need to be checked closely for any error:

[grid@exadb01]$ cd /u01/app/18.1.0.0/grid
[grid@exadb01]$ ./runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u01/app/12.1.0.2/grid -dest_crshome /u01/app/18.1.0.0/grid -dest_version 18.1.0.0 -fixup -verbose

Below an output which requires a fixup:

CVU operation performed:      stage -pre crsinst
Date:                         Jun 18, 2019 9:47:15 PM
CVU home:                     /u01/app/18.1.0.0/grid/
User:                         grid
******************************************************************************************
Following is the list of fixable prerequisites selected to fix in this session
******************************************************************************************
--------------                ---------------     -------------  ------------- 
Check failed.                 Failed on nodes     Reboot         Re-Login       
                                                  required?      required?      
--------------                ---------------     -------------  -------------  
Group Membership: asmoper     exadb01,exadb02      no             no             
Execute "/tmp/CVU_18.0.0.0.0_grid/runfixup.sh" as root user on nodes "exadb01,exadb02" to perform the fix up operations manually
Press ENTER key to continue after execution of "/tmp/CVU_18.0.0.0.0_grid/runfixup.sh" has completed on nodes "exadb01,exadb02"

Which I executed on both nodes (do not do that if your a fixup requires a reboot !):

[root@exadb01 ~]# /tmp/CVU_18.0.0.0.0_grid/runfixup.sh
All Fix-up operations were completed successfully.
[root@exadb01 ~]# ssh exadb02
[root@exadb02 ~]# /tmp/CVU_18.0.0.0.0_grid/runfixup.sh
All Fix-up operations were completed successfully.
[root@exadb02 ~]# 

Once the fixup script has been executed, restart the pre-requisites to be sure that everything is now good:

[grid@exadb01]$ ./runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u01/app/12.1.0.2/grid -dest_crshome /u01/app/18.1.0.0/grid -dest_version 18.1.0.0 -fixup -verbose
. . .
Pre-check for cluster services setup was successful. 
CVU operation performed:      stage -pre crsinst
Date:                         Jun 18, 2019 9:53:40 PM
CVU home:                     /u01/app/18.1.0.0/grid/
User:                         grid
[grid@exadb01]$

3/ Upgrade to GI 18c

Now that all the pre requisites are successful, we can upgrade GI to 18c.

3.0/ A status before starting the upgrade

I strongly recommend to keep a status of all the resources across your cluster before starting the maintenance to avoid any unpleasantness after the maintenance.

[grid@exadb01]$ ./rac-status.sh -a -w0 | tee status_before_GI_upgrade_to_18

                Cluster exadata is a  X5-2 Elastic Rack HC 8TB

    Listener   |      Port     |      db01      |      db02      |      db03      |      db04      |     Type     |
-------------------------------------------------------------------------------------------------------------------
    LISTENER   | TCP:1551      |     Online     |     Online     |     Online     |     Online     |   Listener   |
 LISTENER_ABCD | TCP:1561      |     Online     |     Online     |     Online     |     Online     |   Listener   |
 LISTENER_SCAN1| TCP:1551,1561 |        -       |        -       |     Online     |        -       |     SCAN     |
 LISTENER_SCAN2| TCP:1551,1561 |        -       |     Online     |        -       |        -       |     SCAN     |
 LISTENER_SCAN3| TCP:1551,1561 |     Online     |        -       |        -       |        -       |     SCAN     |
-------------------------------------------------------------------------------------------------------------------

       DB      |    Service    |      db01      |      db02      |      db03      |      db04      |
----------------------------------------------------------------------------------------------------
  db01         | proddb_1_bkup |     Online     |        -       |        -       |        -       |
               | proddb_2_bkup |        -       |     Online     |        -       |        -       |
               | proddb_3_bkup |        -       |        -       |     Online     |        -       |
               | proddb_4_bkup |        -       |        -       |        -       |     Online     |
  db02         | db02svc1_bkup |        -       |        -       |     Online     |        -       |
               | db02svc2_bkup |        -       |        -       |     Online     |        -       |
  db03         | db03svc1_bkup |     Online     |        -       |        -       |        -       |
               | db03svc2_bkup |     Online     |        -       |        -       |        -       |
  db04         | db04svc1_bkup |     Online     |        -       |        -       |        -       |
               | db04svc2_bkup |        -       |     Online     |        -       |        -       |
               | db04svc3_bkup |        -       |        -       |     Online     |        -       |
               | db04svc4_bkup |        -       |        -       |        -       |     Online     |
----------------------------------------------------------------------------------------------------

       DB      |    Version    |      db01      |      db02      |      db03      |      db04      |    DB Type   |
-------------------------------------------------------------------------------------------------------------------
  db01         | 12.1.0.2  (1) |    Readonly    |    Readonly    |    Readonly    |    Readonly    |    RAC (S)   |
  db02         | 12.1.0.2  (1) |        -       |        -       |      Open      |      Open      |    RAC (P)   |
  db03         | 12.1.0.2  (1) |      Open      |      Open      |        -       |        -       |    RAC (P)   |
  db04         | 12.1.0.2  (1) |    Readonly    |    Readonly    |    Readonly    |    Readonly    |    RAC (S)   |
-------------------------------------------------------------------------------------------------------------------

        ORACLE_HOME references listed in the Version column :                                   Primary : White and (P)
                                                                                                Standby : Red   and (S)
                1 : /u01/app/oracle/product/12.1.0.2/dbhome_1
[grid@exadb01]$

3.1/ ASM memory setting

Some recommended memory settings have to be set at ASM instance level:

[grid@exadb01]$ sqlplus / as sysasm
SQL> alter system set sga_max_size = 3G scope=spfile sid='*';
SQL> alter system set sga_target = 3G scope=spfile sid='*';
SQL> alter system set memory_target=0 sid='*' scope=spfile;
SQL> alter system set memory_max_target=0 sid='*' scope=spfile /* required workaround */;
SQL> alter system reset memory_max_target sid='*' scope=spfile;
SQL> alter system set use_large_pages=true sid='*' scope=spfile /* 11.2.0.2 and later(Linux only) */;

3.2/ Reset miscount to default

The miscount parameter is the maximum time, in seconds, that a network heartbeat can be missed before a node eviction occurs. It needs to be reset to default before upgrading. It has to be done as the GI owner.

[grid@exadb01]$ . oraenv <<< +ASM1
[grid@exadb01]$ crsctl unset css misscount

3.3/ gridSetup.sh

We will be using a script named gridSetup.sh to initiate the GI upgrade to 18c
Please find below a whole output:

[grid@exadb01]$ cd /u01/app/18.1.0.0/grid
[grid@exadb01]$ ./gridSetup.sh -silent -responseFile /tmp/giresponse.rsp -J-Doracle.install.mgmtDB=false -J-Doracle.install.crs.enableRemoteGIMR=false -applyRU /patches/28980183/Database/18.5.0.0.0/18.5.0.0.190115GIRU/28828717
Preparing the home to patch...
Applying the patch /patches/28980183/Database/18.5.0.0.0/18.5.0.0.190115GIRU/28828717...
Successfully applied the patch.
The log can be found at: /u01/app/oraInventory/logs/GridSetupActions2018-11-18_05-11-43PM/installerPatchActions_2018-11-18_05-11-43PM.log
Launching Oracle Grid Infrastructure Setup Wizard...
. . .
You can find the log of this install session at:
 /u01/app/oraInventory/logs/GridSetupActions2018-11-18_05-11-43PM/gridSetupActions2018-11-18_05-11-43PM.log

As a root user, execute the following script(s):
        1. /u01/app/18.1.0.0/grid/rootupgrade.sh

Execute /u01/app/18.1.0.0/grid/rootupgrade.sh on the following nodes:
[exadb01, exadb02, exadb04, exadb03]

Run the script on the local node first. After successful completion, you can start the script in parallel on all other nodes, except a node you designate as the last node. When all the nodes except the last node are done successfully, run the script on the last node.

Successfully Setup Software.
As install user, execute the following command to complete the configuration.
        /u01/app/18.1.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /tmp/giresponse.rsp [-silent]

[grid@exadb01]$

Above are some ignorable warnings about OS groups. Is also described the next step which is to start rootupgrade.sh on each node.

3.4/ rootupgrade.sh

As specified by gridSetup.sh in the previous step, we now need to run rootupgrade.sh on each node knowing that you can start rootupgrade.sh in parallel except for the first and the last node; below an example with a half rack (4 nodes):

1. Start rootupgrade.sh on the node 1
2. Start rootupgrade.sh in parallel on the nodes 2 and 3
3. Start rootupgrade.sh on the node 4

Note that CRS will be stopped on the node you apply rootupgrade.sh on so your instances will suffer an outage during this operation. Think about rebalancing your services accordingly to avoid any application downtime.

Here is a sample output; note that rootupgrade.sh is very silent, all logs go to the log file specified:

[root@exadb01]# /u01/app/18.1.0.0/grid/rootupgrade.sh
Check /u01/app/18.1.0.0/grid/install/root_exadb01_2019-06-29_11-14-09-408446489.log for the output of root script
[root@exadb01]#

An interesting thing to note here after a node is patched is that the softwareversion is now the target one (18.5 and not that here CRS shows 18.0.0.0.0) but the activeversion is still the old one (12.1); indeed, the activeversion will be changed to 18.5 when applying rootupgrade.sh on the last node.

[root@exadb01]# . oraenv <<< +ASM1
[root@exadb01]# crsctl query crs softwareversion
Oracle Clusterware version on node [exadb01] is [18.0.0.0.0]
[root@exadb01]# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.1.0.2.0]
[root@exadatadb01]#

3.5/ gridSetup.sh -executeConfigTools

Run the gridSetup.sh -executeConfigTools command:

[grid@exadb01]$ /u01/app/18.1.0.0/grid/gridSetup.sh -executeConfigTools -responseFile /tmp/giresponse.rsp -silent
Launching Oracle Grid Infrastructure Setup Wizard...

You can find the logs of this session at:
/u01/app/oraInventory/logs/GridSetupActions2018-11-18_07-11-22PM

Successfully Configured Software.
[grid@exadb01]$

3.6/ Check that GI is relinked with RDS:

It is worth double checking that the new GI Home is properly relinked with RDS to avoid future performance issues (you may want to read this pdf for more information on what RDS is):

[grid@exadb01]$ dcli -g ~/dbs_group -l oracle /u01/app/18.1.0.0/grid/bin/skgxpinfo
exadatadb01: rds
exadatadb02: rds
exadatadb03: rds
exadatadb04: rds
[grid@exadb01]$

If not, relink the GI Home with RDS:

dcli -g ~/dbs_group -l oracle "ORACLE_HOME=/u01/app/18.1.0.0/grid; make -C /u01/app/18.1.0.0/grid/rdbms/lib -f ins_rdbms.mk ipc_rds ioracle"

3.7/ Check the status of the cluster

Let's have a look at the status of the cluster and the activeversion:

[grid@exadb01]$ /u01/app/18.1.0.1/grid/bin/crsctl check cluster -all
**************************************************************
exadatadb01:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
exadatadb02:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
exadatadb03:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
exadatadb04:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[grid@exadb01]$ dcli -g ~/dbs_group -l oracle /u01/app/18.1.0.0/grid/bin/crsctl query crs activeversion
Oracle Clusterware version on node [exadb01] is [18.0.0.0.0]
Oracle Clusterware version on node [exadb02] is [18.0.0.0.0]
Oracle Clusterware version on node [exadb03] is [18.0.0.0.0]
Oracle Clusterware version on node [exadb04] is [18.0.0.0.0]
[grid@exadb01]$

Let's check the status of all the resources like we did in paragraph 3.0:

[grid@exadb01]$ ./rac-status.sh -a -w0 | tee status_after_GI_upgrade_to_18

                Cluster exadata is a  X5-2 Elastic Rack HC 8TB

    Listener   |      Port     |      db01      |      db02      |      db03      |      db04      |     Type     |
-------------------------------------------------------------------------------------------------------------------
    LISTENER   | TCP:1551      |     Online     |     Online     |     Online     |     Online     |   Listener   |
 LISTENER_ABCD | TCP:1561      |     Online     |     Online     |     Online     |     Online     |   Listener   |
 LISTENER_SCAN1| TCP:1551,1561 |        -       |     Online     |        -       |        -       |     SCAN     |
 LISTENER_SCAN2| TCP:1551,1561 |     Online     |        -       |        -       |        -       |     SCAN     |
 LISTENER_SCAN3| TCP:1551,1561 |        -       |        -       |     Online     |        -       |     SCAN     |
-------------------------------------------------------------------------------------------------------------------

       DB      |    Service    |      db01      |      db02      |      db03      |      db04      |
----------------------------------------------------------------------------------------------------
  db01         | proddb_1_bkup |     Online     |        -       |        -       |        -       |
               | proddb_2_bkup |        -       |     Online     |        -       |        -       |
               | proddb_3_bkup |        -       |        -       |     Online     |        -       |
               | proddb_4_bkup |        -       |        -       |        -       |     Online     |
  db02         | db02svc1_bkup |        -       |        -       |     Online     |        -       |
               | db02svc2_bkup |        -       |        -       |     Online     |        -       |
  db03         | db03svc1_bkup |     Online     |        -       |        -       |        -       |
               | db03svc2_bkup |     Online     |        -       |        -       |        -       |
  db04         | db04svc1_bkup |     Online     |        -       |        -       |        -       |
               | db04svc2_bkup |        -       |     Online     |        -       |        -       |
               | db04svc3_bkup |        -       |        -       |     Online     |        -       |
               | db04svc4_bkup |        -       |        -       |        -       |     Online     |
----------------------------------------------------------------------------------------------------

       DB      |    Version    |      db01      |      db02      |      db03      |      db04      |    DB Type   |
-------------------------------------------------------------------------------------------------------------------
  db01         | 12.1.0.2  (1) |    Readonly    |    Readonly    |    Readonly    |    Readonly    |    RAC (S)   |
  db02         | 12.1.0.2  (1) |        -       |        -       |      Open      |      Open      |    RAC (P)   |
  db03         | 12.1.0.2  (1) |      Open      |      Open      |        -       |        -       |    RAC (P)   |
  db04         | 12.1.0.2  (1) |    Readonly    |    Readonly    |    Readonly    |    Readonly    |    RAC (S)   |
-------------------------------------------------------------------------------------------------------------------

        ORACLE_HOME references listed in the Version column :                                   Primary : White and (P)
                                                                                                Standby : Red   and (S)
                1 : /u01/app/oracle/product/12.1.0.2/dbhome_1
[grid@exadb01]$

And check for differences:

[grid@exadb01]$ diff status_before_GI_upgrade_to_12.2 status_after_GI_upgrade_to_12.2
8,10c8,10
<  LISTENER_SCAN1| TCP:1551,1561 |        -       |        -       |     Online     |        -       |     SCAN     |
<  LISTENER_SCAN2| TCP:1551,1561 |        -       |     Online     |        -       |        -       |     SCAN     |
<  LISTENER_SCAN3| TCP:1551,1561 |     Online     |        -       |        -       |        -       |     SCAN     |
---
>  LISTENER_SCAN1| TCP:1551,1561 |        -       |     Online     |        -       |        -       |     SCAN     |
>  LISTENER_SCAN2| TCP:1551,1561 |     Online     |        -       |        -       |        -       |     SCAN     |
>  LISTENER_SCAN3| TCP:1551,1561 |        -       |        -       |     Online     |        -       |     SCAN     |
[oracle@exadb01]$

We can see here than only the SCAN listeners have been re shuffled by the maintenance which does not matter. You can relocate them but it has no impact whatsoever. It also means that all our instances and services are back as they were before the maintenance. We are then idempotent.

3.8/ Set Flex ASM Cardinality to "ALL"

Starting release 12.2 ASM will be configured as "Flex ASM". By default Flex ASM cardinality is set to 3. This means configurations with four or more database nodes in the cluster might only see ASM instances on three nodes. Nodes without an ASM instance running on it will use an ASM instance on a remote node within the cluster. Only when the cardinality is set to “ALL”, ASM will bring up the additional instances required to fulfill the cardinality setting.

[oracle@exadb01]$ srvctl modify asm -count ALL
[oracle@exadb01]$

Note that this command provides no output.

3.9/ Update compatible.asm to 18.1

Now that ASM 18c is running, it is recommended to update the compatible.asm to 18.1 to be able to enjoy the 18c new features.

-- Set env and connect
[oracle@exadb01]$ . oraenv <<< +ASM1
[oracle@exadb01]$ sqlplus / as sysasm

-- List the diskgroups
SQL> select name, COMPATIBILITY from v$asm_diskgroup ;

-- Set compatible to 12.2 (examples here with some usual DGs)
SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.asm' = '18.1.0.0.0';
SQL> ALTER DISKGROUP DBFS_DG SET ATTRIBUTE 'compatible.asm' = '18.1.0.0.0';
SQL> ALTER DISKGROUP RECO SET ATTRIBUTE 'compatible.asm' = '18.1.0.0.0';

-- Verify the new settings
SQL> select name, COMPATIBILITY from v$asm_diskgroup ;

3.10/ Update the Inventory

To wrap this up, let's update the Inventory

[grid@exadb01]$ . oraenv <<< +ASM1
[grid@exadb01]$ /u01/app/12.2.0.1/grid/oui/bin/runInstaller -ignoreSysPrereqs -updateNodeList ORACLE_HOME=/u01/app/18.1.0.0/grid "CLUSTER_NODES={exadb01,exadb02,exadb03,exadb04}" CRS=true LOCAL_NODE=exadb01

Note: you may also want to update the new GI Home patch in OEM or any other monitoring tool that would require it.

3.11/ /etc/oratab entries

If you have oratab entries that have disappeared after the upgrade, you may have missed the warning in the 0/ Preparation paragraph of this post. You may want to have a look at this post for an explanation of this behavior.

And you're all done ! enjoy !

rac-status.sh : an overview of your RAC / GI 11g,12c, 18c ,19c, 21c, 23c resources in a glimpse

Consolidation has been a fancy word and concept for years in IT resulting for us DBAs with more and more databases and instances running on more and more powerful / bigger clusters running RAC (Real Application Cluster) before and now GI (Grid Infrastructure) from 12c version.

It is kind of nice to have to manage all these big clusters / appliances but sometimes what was easy before become less easy like quickly answering these questions :

• what is up ?
• what is supposed to be up ? (which is fairly different from the previous one)
• where is it up ?
• have we missed a problem ?

And quickly answering these questions on a Full Exadata Rack with 100 databases running on is not that easy with the common tools.

I then made a tool to be able to answer these questions and have a nice result in a glimpse (please note that all the below screenshots come from real implementations, I have just anonymized the outputs for obvious reasons.

Feel free to also check the the rac-status.sh: FAQ and the rac-status.sh what's new post. You can download the script here, there are download links for each script in the "Scripts" menu on top of each page.