oraenv++: an easy and powerful tool to set up your Oracle environment

I have never found a really good and portable way of setting up an Oracle/ASM environment. For sure, there is oraenv but it is based on oratab which is a static hardcoded text file and oraenv does not work well (at all ?) with RAC. Some tries to set up aliases to set up their environment but same, it is hardcoded, Oracle uses hardcoded env files in OCI, etc ... All of this works more and less well but it has always seemed a bit clumsy to me and during a recent email discussion I had with Osman, I decided to jump into that challenge to create something better, easier and more efficient than any of these tools and a fews days later was born oraenv++!

Key features

• oraenv++ reads the environmental information from GI/CRS/Oracle Restart so no hardcoded configuration file is needed; sorry I cannot do anything if you do not have GI/CRS/Oracle Restart
• oraenv++ sets the ORACLE_SID, ORACLE_HOME, ORACLE_BASE, PATH and can also set the ORACLE_PDB_SID environment variable to be able to directly connect to a PDB (from 18.8)
• if you do not know the name of the database you want to set the env up, oraenv++ will show you a menu with a list of databases from your configuration to choose from
• you can also grep and grep -v in the database list of your environment (regexp are supported)
• GI storing the database names in lowercase regardless of their case, oraenv++ is not key sensitive regarding the database name nor the pattern(s) to grep/ungrep
• oraenv++ has to be sourced (. oraenv++) and not executed (./oraenv++); I'll eventually write about the differences in my bash tips
• I have tested it with GI 19c and I see no reason why it would not work from any GI 12c+ (including 21c+); not sure for GI 11g which should not be much around any more these days
• I have been using oraenv++ for a little while now and I honestly find it

Examples

A first example to simply set up a database environment:

$ . oraenv++ prod12a
Database            : prod12a
ORACLE_HOME         : /u01/app/oracle/product/12.1.0.2/db_1
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : PROD12A1
ORACLE_PDB_SID      :
sqlplus is          : /u01/app/oracle/product/12.1.0.2/db_1/bin/sqlplus
$

Here, we have set up the prod12a environememnt with the correct instance name running on the host you are connected to. If you do not want to see this output, you can make it silent:

$ . oraenv++ prod12a --silent
$

or

$ OPP_SILENT=True
$ . oraenv++ prod12a
$

If you do not specify a database name, oraenv++ will list all the databases which have an instance on this host from your GI configuration and show you a menu to choose from (let's go with 6 below):

$ . oraenv++
    Database    SID          ORACLE_HOME
-----------------------------------------------------------------
  1/ asm        +ASM1      /u01/app/19.11.0.0/grid
  2/ dev12a     DEV12A1    /u01/app/oracle/product/12.1.0.2/db_1
  3/ dev12b     DEV12B1    /u01/app/oracle/product/12.1.0.2/db_1
  4/ dev19a     DEV19A1    /u01/app/oracle/product/19.0.0.0/db_1
  5/ dev19b     DEV19B1    /u01/app/oracle/product/19.0.0.0/db_1
  6/ misc1      MISC11     /u01/app/oracle/product/12.1.0.2/db_1
  7/ misc2      MISC21     /u01/app/oracle/product/19.0.0.0/db_1
  8/ test       TEST1      /u01/app/oracle/product/11.2.0.4/db_1
  9/ prod12a    PROD12A1   /u01/app/oracle/product/12.1.0.2/db_1
 10/ prod12b    PROD12B1   /u01/app/oracle/product/12.1.0.2/db_1
 11/ prod19a    PROD19A1   /u01/app/oracle/product/19.0.0.0/db_1
 12/ prod19b    PROD19B1   /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)
6
Database            : misc1
ORACLE_HOME         : /u01/app/oracle/product/12.1.0.2/db_1
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : MISC11
ORACLE_PDB_SID      :
sqlplus is          : /u01/app/oracle/product/12.1.0.2/db_1/bin/sqlplus
$

If you have many databases, you can narrow the selection down using --grep or --ungrep (acts as a grep -v); note that these 2 options allow mutiple comma separated pattern to be grepped/ungrepped; they also supports regexp -- this is far overkill but it is fun :)

$. oraenv++ --grep prod,dev --ungrep 12
     Database    SID          ORACLE_HOME
-----------------------------------------------------------------
  1/ dev19a     DEV19A1    /u01/app/oracle/product/19.0.0.0/db_1
  2/ dev19b     DEV19B1    /u01/app/oracle/product/19.0.0.0/db_1
  3/ prod19a    PROD19A1   /u01/app/oracle/product/19.0.0.0/db_1
  4/ prod19b    PROD19B1   /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)

This could also have been written (just to showcase a regexp :)):

$. oraenv++ --grep 19[ab]$
     Database    SID          ORACLE_HOME
-----------------------------------------------------------------
  1/ dev19a     DEV19A1    /u01/app/oracle/product/19.0.0.0/db_1
  2/ dev19b     DEV19B1    /u01/app/oracle/product/19.0.0.0/db_1
  3/ prod19a    PROD19A1   /u01/app/oracle/product/19.0.0.0/db_1
  4/ prod19b    PROD19B1   /u01/app/oracle/product/19.0.0.0/db_1
-----------------------------------------------------------------
Which environment you want to set up ? (CTRL+C for exit)

It indeed also works with ASM:

# . oraenv++ asm
Database            : asm
ORACLE_HOME         : /u01/app/19.11.0.0/grid
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : +ASM1
ORACLE_PDB_SID      :
sqlplus is          : /u01/app/19.11.0.0/grid/bin/sqlplus
#

If you want to direct connect to a PDB, you can also specify one with the --pdb option which will set the ORACLE_PDB_SID variable and you could then directly connect to this PDB (this works from 18.8):

$ . oraenv++ dev19a --pdb MY_PDB
Database            : dev19a
ORACLE_HOME         : /u01/app/oracle/product/19.0.0.0/db_1
ORACLE_BASE         : /u01/app/oracle
ORACLE_SID          : DEV19A1
ORACLE_PDB_SID      : MYPDB
sqlplus is          : /u01/app/oracle/product/19.0.0.0/db_1/bin/sqlplus
$ sqlplus / as sysdba
SQL> show con_name
CON_NAME
----------------
MY_PDB

TODO / other features / Ideas

First of all, feel free to post down below or contact me if you have ideas or remarks.

On my end, one cool feature to implement in a next future would be to list the PDBs per CDB showing a menu to be able to choose from as we have hundreds of PDBs per CDB and we cannot really remember of all them then choosing from a list would be far easier. The thing here is that the PDBs are real GI resource from 21c only, they are listed in the services before 21c which I already use to show them in rac-status.sh. The thing is that crsctl is slow to query all the services when there are a lot of them so this would slow oraenv++
. Having said that, the direct connection to a PDB feature takes few seconds to get connected and ssh in any Cloud I have experienced are also slow verifying your credentials, billing you, etc ... and no one complains about it :)

Where to download ?

You can download oraenv++ from my public git repo or using the direct link to the source code from the Scripts menu on top of this page.

In case of issue/question, as usual, feel free to post a comment down below, contact me by email or linkedin chat.

Enjoy !