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++!
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 :)
In case of issue/question, as usual, feel free to post a comment down below, contact me by email or linkeding chat.
Enjoy !
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 linkeding chat.
Enjoy !
