OCI CLI to list all the database and its host lifecycle - Enahanced 

This shell script can be used, though tenancy ocid id should be updated with the actual one. 

ociCompartmentList=$(oci iam compartment list --compartment-id ocid1.tenancy.oc1..aaaa1111222334)
for c in $(echo "$ociCompartmentList" | jq '.data | keys | .[]')
do
  compartment_ocid=$(echo "$ociCompartmentList" | jq -r ".data[$c].\"id\"")
  ocidbList=$(oci db database list -c $compartment_ocid)
  for i in $(echo "$ocidbList" | jq '.data | keys | .[]')
  do
        dbname=$(echo "$ocidbList" | jq -r ".data[$i].\"db-name\"")
        dbsystemid=$(echo "$ocidbList" | jq -r ".data[$i].\"db-system-id\"")
        lifecycle=$(echo "$ocidbList" | jq -r ".data[$i].\"lifecycle-state\"")
        dbNodeList=$(oci db node list -c $compartment_ocid --db-system-id $dbsystemid)
        for n in $(echo "$dbNodeList" | jq '.data | keys | .[]')
        do
            hostname=$(echo "$dbNodeList" | jq -r ".data[$n].\"hostname\"")
            host_ocid=$(echo "$dbNodeList" | jq -r ".data[$n].\"id\"")
            host_lifcycle=$(echo "$dbNodeList" | jq -r ".data[$n].\"lifecycle-state\"")
            vnic=$(echo "$dbNodeList" | jq -r ".data[$n].\"vnic-id\"")
            public_ip=$(oci network vnic get --vnic-id ${vnic} | jq -r ".data[\"public-ip\"]")
            echo "Hostname: ${hostname}(${public_ip}) | HostLifeCycle: ${host_lifcycle} | Database: $dbname | DBLifeCycle: $lifecycle"
        done
  done
done

This script list for a specific compartment, only. In this script, please update compartment OCID with the actual one.:

 compartment_ocid="ocid1.compartment.oc1..aaaaaa..8dfh8ea"

 ocidbList=$(oci db database list -c $compartment_ocid)

 for i in $(echo "$ocidbList" | jq '.data | keys | .[]')

 do

        dbname=$(echo "$ocidbList" | jq -r ".data[$i].\"db-name\"")

        dbsystemid=$(echo "$ocidbList" | jq -r ".data[$i].\"db-system-id\"")

        lifecycle=$(echo "$ocidbList" | jq -r ".data[$i].\"lifecycle-state\"")

        dbNodeList=$(oci db node list -c $compartment_ocid --db-system-id $dbsystemid)

        for n in $(echo "$dbNodeList" | jq '.data | keys | .[]')

        do

            hostname=$(echo "$dbNodeList" | jq -r ".data[$n].\"hostname\"")

            host_ocid=$(echo "$dbNodeList" | jq -r ".data[$n].\"id\"")

            host_lifcycle=$(echo "$dbNodeList" | jq -r ".data[$n].\"lifecycle-state\"")

            vnic=$(echo "$dbNodeList" | jq -r ".data[$n].\"vnic-id\"")

            public_ip=$(oci network vnic get --vnic-id ${vnic} | jq -r ".data[\"public-ip\"]")

            echo "Hostname: ${hostname}(${public_ip}) | HostLifeCycle: ${host_lifcycle} | Database: $dbname | DBLifeCycle: $lifecycle"

        done

 done

Reference: Original script used from Senthil Rajendra's Blog and enhanced to my needs to list all RAC nodes and list public IP associated.

OCI Cli

These steps are performed on OEL VMBox    Install python3: yum install -y python3*   [root@localhost bin]# ls -l python* lrwxrwxrwx. 1 root root 7 May 31 2019 python -> python2 lrwxrwxrwx. 1 root root 9 May 31 2019 python2 -> python2.7 -rwxr-xr-x. 1 root root 7216 Apr 9 2019 python2.7 lrwxrwxrwx. 1 root root 9 Mar 26 18:00 python3 -> python3.6 -rwxr-xr-x. 2 root root 11336 Aug 7 2019 python3.6 -rwxr-xr-x. 2 root root 11336 Aug 7 2019 python3.6m  [root@localhost bin]# ln -s -f python3 python [root@localhost bin]# ls -l python* lrwxrwxrwx. 1 root root 7 Mar 26 18:01 python -> python3 lrwxrwxrwx. 1 root root 9 May 31 2019 python2 -> python2.7 -rwxr-xr-x. 1 root root 7216 Apr 9 2019 python2.7 lrwxrwxrwx. 1 root root 9 Mar 26 18:00 python3 -> python3.6 -rwxr-xr-x. 2 root root 11336 Aug 7 2019 python3.6 -rwxr-xr-x. 2 root root 11336 Aug 7 2019 python3.6m [root@localhost bin]# python -V Python 3.6.8    This broke my "yum" so I had to revert back:   [root@localhost bin]# ls -l python* lrwxrwxrwx. 1 root root 7 Mar 26 18:01 python -> python3 lrwxrwxrwx. 1 root root 9 May 31 2019 python2 -> python2.7 -rwxr-xr-x. 1 root root 7216 Apr 9 2019 python2.7 lrwxrwxrwx. 1 root root 9 Mar 26 18:00 python3 -> python3.6 -rwxr-xr-x. 2 root root 11336 Aug 7 2019 python3.6 -rwxr-xr-x. 2 root root 11336 Aug 7 2019 python3.6m [root@localhost bin]# ln -s -f python2 python [root@localhost bin]# python -V Python 2.7.5    [oracle@localhost ~]$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 14620 100 14620 0 0 39270 0 --:--:-- --:--:-- --:--:-- 39407 ****************************************************************************** You have started the OCI CLI Installer in interactive mode. If you do not wish to run this in interactive mode, please include the --accept-all-defaults option. If you have the script locally and would like to know more about input options for this script, then you can run: ./install.sh -h If you would like to know more about input options for this script, refer to: https://github.com/oracle/oci-cli/blob/master/scripts/install/README.rst ****************************************************************************** Downloading Oracle Cloud Infrastructure CLI install script from https://raw.githubusercontent.com/oracle/oci-cli/v2.9.3/scripts/install/install.py to /tmp/oci_cli_install_tmp_ipBj. ######################################################################## 100.0% Running install script. python3 /tmp/oci_cli_install_tmp_ipBj -- Verifying Python version. -- Python version 3.6.8 okay. ===> In what directory would you like to place the install? (leave blank to use '/home/oracle/lib/oracle-cli'): -- Creating directory '/u01/userhome/oracle/lib/oracle-cli'. -- We will install at '/u01/userhome/oracle/lib/oracle-cli'. ===> In what directory would you like to place the 'oci' executable? (leave blank to use '/home/oracle/bin'): -- The executable will be in '/u01/userhome/oracle/bin'. ===> In what directory would you like to place the OCI scripts? (leave blank to use '/home/oracle/bin/oci-cli-scripts'): -- Creating directory '/u01/userhome/oracle/bin/oci-cli-scripts'. -- The scripts will be in '/u01/userhome/oracle/bin/oci-cli-scripts'. ===> Currently supported optional packages are: ['db (will install cx_Oracle)'] What optional CLI packages would you like to be installed (comma separated names; press enter if you don't need any optional packages)?: db -- The optional packages installed will be 'db'. -- Trying to use python3 venv. -- Executing: ['/usr/bin/python3', '-m', 'venv', '/u01/userhome/oracle/lib/oracle-cli'] -- Executing: ['/u01/userhome/oracle/lib/oracle-cli/bin/pip', 'install', '--upgrade', 'pip'] Collecting pip Downloading https://files.pythonhosted.org/packages/54/0c/d01aa759fdc501a58f431eb594a17495f15b88da142ce14b5845662c13f3/pip-20.0.2-py2.py3-none-any.whl (1.4MB) 100% |████████████████████████████████| 1.4MB 1.4MB/s Installing collected packages: pip Found existing installation: pip 9.0.3 Uninstalling pip-9.0.3: Successfully uninstalled pip-9.0.3 Successfully installed pip-20.0.2 -- Executing: ['/u01/userhome/oracle/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpm_j8_sv_', 'wheel', '--upgrade'] Collecting wheel Downloading wheel-0.34.2-py2.py3-none-any.whl (26 kB) Installing collected packages: wheel Successfully installed wheel-0.34.2 -- Executing: ['/u01/userhome/oracle/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpm_j8_sv_', 'oci_cli[db]', '--upgrade'] Collecting oci_cli[db] Downloading oci_cli-2.9.8-py2.py3-none-any.whl (7.5 MB) |████████████████████████████████| 7.5 MB 2.4 MB/s Collecting cryptography==2.8 Downloading cryptography-2.8-cp34-abi3-manylinux2010_x86_64.whl (2.3 MB) |████████████████████████████████| 2.3 MB 27.4 MB/s Collecting configparser>=4.0.2 Downloading configparser-5.0.0-py3-none-any.whl (22 kB) Collecting python-dateutil<3.0.0,>=2.5.3 Downloading python_dateutil-2.8.1-py2.py3-none-any.whl (227 kB) |████████████████████████████████| 227 kB 44.6 MB/s Collecting pytz>=2016.10 Downloading pytz-2019.3-py2.py3-none-any.whl (509 kB) |████████████████████████████████| 509 kB 42.4 MB/s Collecting idna<2.7,>=2.5 Downloading idna-2.6-py2.py3-none-any.whl (56 kB) |████████████████████████████████| 56 kB 2.3 MB/s Collecting PyYAML==5.1.2 Downloading PyYAML-5.1.2.tar.gz (265 kB) |████████████████████████████████| 265 kB 36.8 MB/s Collecting jmespath==0.9.4 Downloading jmespath-0.9.4-py2.py3-none-any.whl (24 kB) Collecting oci==2.12.1 Downloading oci-2.12.1-py2.py3-none-any.whl (3.7 MB) |████████████████████████████████| 3.7 MB 41.7 MB/s Collecting arrow==0.14.7 Downloading arrow-0.14.7-py2.py3-none-any.whl (39 kB) Collecting certifi Downloading certifi-2019.11.28-py2.py3-none-any.whl (156 kB) |████████████████████████████████| 156 kB 62.2 MB/s Collecting retrying==1.3.3 Downloading retrying-1.3.3.tar.gz (10 kB) Collecting click==6.7 Downloading click-6.7-py2.py3-none-any.whl (71 kB) |████████████████████████████████| 71 kB 6.4 MB/s Collecting pyOpenSSL==18.0.0 Downloading pyOpenSSL-18.0.0-py2.py3-none-any.whl (53 kB) |████████████████████████████████| 53 kB 3.7 MB/s Collecting six==1.14.0 Downloading six-1.14.0-py2.py3-none-any.whl (10 kB) Collecting terminaltables==3.1.0 Downloading terminaltables-3.1.0.tar.gz (12 kB) Collecting cx-Oracle==7.0; extra == "db" Downloading cx_Oracle-7.0.0-cp36-cp36m-manylinux1_x86_64.whl (675 kB) |████████████████████████████████| 675 kB 54.6 MB/s Collecting cffi!=1.11.3,>=1.8 Downloading cffi-1.14.0-cp36-cp36m-manylinux1_x86_64.whl (399 kB) |████████████████████████████████| 399 kB 36.9 MB/s Collecting pycparser Downloading pycparser-2.20-py2.py3-none-any.whl (112 kB) |████████████████████████████████| 112 kB 41.2 MB/s Building wheels for collected packages: PyYAML, retrying, terminaltables Building wheel for PyYAML (setup.py) ... done Created wheel for PyYAML: filename=PyYAML-5.1.2-cp36-cp36m-linux_x86_64.whl size=44104 sha256=8eadcc33892c992b1c071e8f0e7623945acebe5eece35299f4c448571ba05366 Stored in directory: /tmp/tmpm_j8_sv_/wheels/d8/9b/e7/75af463b873c119dd444151fc54a8e190c87993593e1fa194a Building wheel for retrying (setup.py) ... done Created wheel for retrying: filename=retrying-1.3.3-py3-none-any.whl size=11430 sha256=72069b956abf995176390aa9a39d594c55523244a38da0942e45a9a787118746 Stored in directory: /tmp/tmpm_j8_sv_/wheels/ac/cb/8a/b27bf6323e2f4c462dcbf77d70b7c5e7868a7fbe12871770cf Building wheel for terminaltables (setup.py) ... done Created wheel for terminaltables: filename=terminaltables-3.1.0-py3-none-any.whl size=15354 sha256=3b6260fbdb92de3b45d625b05bca4eace5de000e9b55f36badabb9d90c3a0a8f Stored in directory: /tmp/tmpm_j8_sv_/wheels/86/1b/58/c23af2fe683acd8edc15d5a1268f0242be1ff2cf827fe34737 Successfully built PyYAML retrying terminaltables ERROR: oci 2.12.1 has requirement configparser==4.0.2, but you'll have configparser 5.0.0 which is incompatible. Installing collected packages: six, pycparser, cffi, cryptography, configparser, python-dateutil, pytz, idna, PyYAML, jmespath, pyOpenSSL, certifi, oci, arrow, retrying, click, terminaltables, cx-Oracle, oci-cli Successfully installed PyYAML-5.1.2 arrow-0.14.7 certifi-2019.11.28 cffi-1.14.0 click-6.7 configparser-5.0.0 cryptography-2.8 cx-Oracle-7.0.0 idna-2.6 jmespath-0.9.4 oci-2.12.1 oci-cli-2.9.8 pyOpenSSL-18.0.0 pycparser-2.20 python-dateutil-2.8.1 pytz-2019.3 retrying-1.3.3 six-1.14.0 terminaltables-3.1.0 ===> Modify profile to update your $PATH and enable shell/tab completion now? (Y/n): Y ===> Enter a path to an rc file to update (leave blank to use '/home/oracle/.bashrc'): -- Backed up '/u01/userhome/oracle/.bashrc' to '/u01/userhome/oracle/.bashrc.backup' -- Tab completion set up complete. -- If tab completion is not activated, verify that '/u01/userhome/oracle/.bashrc' is sourced by your shell. -- -- ** WARNING: Other 'oci' executables are on your $PATH. ** -- Conflicting paths: /home/oracle/bin/oci, /home/oracle/bin/oci, /home/oracle/bin/oci, /home/oracle/bin/oci -- You can run this installation of the CLI with '/u01/userhome/oracle/bin/oci'. -- -- ** Run `exec -l $SHELL` to restart your shell. ** -- -- Installation successful. -- Run the CLI with /u01/userhome/oracle/bin/oci --help [oracle@localhost ~]$  

Oracle Cloud Infrastructure 2019 Architect Associate

Oracle Cloud Infrastructure Architect Associate

Oracle Cloud Infrastructure 2019 Architect Associate

Powerful Excel Utility for DBAs

Excel Utility for DBAs

This Excel utility has been very useful to all my colleagues at different places as well to my other DBA friends. Based on popular demand I am pleased to share this utility with a broader community of DBA team by sharing this to everyone.

This utility is pretty self explanatory. Due to privacy and security reason I have included sample database names. To begin validating this utility, you need to replace sample database names with your database name(s), which you can do by running a macro(ctrl+shift+T) which will read a TNSNames.ora file you provide as input.

Here is the usage included in the Excel file in the "Version" tab.

Usage: This WorkBook is useful for collecting information from multiple  Oracle  or  SQL Server databases in Excel WorkSheet. Initialization of macros  is very dependent on your current cursor (selected cell) position.  Query you enter in C2 and other cell should  NOT contain ";" at the end of the query string. Enhancement has been done to trim the trailing ";", if present. You should always validate that query you are going to execute against multiple database is syntactically correct.

There are couple macros in this WorkBook , most of them have shortcuts too. (Ref: List of Macros)

For collectData Macro:

You can enter multiple queries , one query in its own cell. Starting from C2, C3, C4, etc.

You can specify work sheet name for the  query results, if you omit it will autogenerate one for you. WorkSheetName must be specified in B2, B3, B4, etc. corresponding cells for the queries.

Queries must be followed by header line and then followed by Sr.No and Database Aliases (from your local TNSNAMES.ora file). Please refer to the "Sample" WorkSheet in this WorkBook.

Once ready with this setup press "Ctl+Shift+C" and will display Login Window for the common user name and password across all the databases. If you have separate username and/or password, use  column E and F to specify  username and password respectively. Select environment type; N => for Non Production, P => For production, A => For all environment. Once entered all this information this macro executes all the listed queries in the current WorkSheet for all the databases listed in the current WorkSheet only. All database must be consecutoive without any Blank lies and/or Sr.No.

WorkSheet containing result contains following additional information:

Date and Time in Cell A1.  Row 2 contains header name and Column A contains database name. Query executed is being stored as Comments for Cell A2.

Also, there will be text file generated in the directory where this Excel file with Macro is being stored, (e.g. DB2Excel4DP.xlsm.txt)  which can be used to watch the progress of the execution. It updates this file once it  completes execution for the databse (success/failure).

List of Macros:
MacroName		ShortCut	Description
collectData		Ctl+Shift+C	Execute queries and stores result in WorkSheets
compareResults		Ctl+Shift+R	Execute queries and stores result SideBySide
DeleteCNValidWrkSheet		Ctl+Shift+D	Delete CN Valid Worksheets (Autogenerated after each execution)
readTNSNamesAndList		Ctl+Shift+T	Reads TNSNames.ora file from the specified location and list all aliases in a new WorkSheet.

In order to get this file, as of now please feel free to send me email (patel.RiDham at GMail.com) I will share this file with you at the earliest feasible time.

Here is the permanent link to this Excel utility.

If you like this utility please feel free to leave your comments here as well as feel free to leave any suggestions/enhancements.

Cleanup /boot partition on Ubuntu

First check your kernel version, so you won't delete the in-use kernel image, running:

uname -r

Now run this command for a list of installed kernels:

dpkg --list 'linux-image*' | grep ^ii

and delete the kernels you don't want/need anymore by running this:

sudo apt-get remove linux-image-VERSION

Replace VERSION with the version of the kernel you want to remove.

When you're done removing the older kernels, you can run this to remove ever packages you won't need anymore:

sudo apt-get autoremove

And finally you can run this to update grub kernel list:

sudo update-grub

Following command is derived from the above post and customized for my own needs:

dpkg --list 'linux-image*' | grep ^ii | awk '{print "apt-get remove " $2}' | grep -v extra|sort| egrep "[0-9]-generic"|head -n -3 




Reference: https://askubuntu.com/questions/345588/what-is-the-safest-way-to-clean-up-boot-partition

Change max value for Oracle Sequence

After data refresh/copy from one environment to another, usually developer realizes that they are using sequence and need to change the sequence's max value. To perform this I have developed this handy script:

This script assumes that the executor of this script has DBA (or DBA like privileges). Ability to select from DBA views and alter any sequence.

set serveroutput on

declare
   seq_owner  varchar2(30) := upper('&1');
   seq_name   varchar2(30) := upper('&2');
   desiredVal number := &3;
   oldIncrVal number;
   diff       number;
   newDiff    number;
   sqlStmt    varchar2(4000);
   currSeqVal number;
begin
   select INCREMENT_BY into oldIncrVal from dba_sequences where SEQUENCE_OWNER = seq_owner and SEQUENCE_NAME = seq_name;
   sqlStmt := 'select ' || seq_owner || '.' || seq_name || '.nextval from dual';
   dbms_output.put_line(sqlStmt);
   execute immediate sqlStmt into currSeqVal;
   diff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff;
   execute immediate sqlStmt into currSeqVal;
   newDiff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal;
end;
/

DB_Unique_Name vs. DB_Name

I have used DB_Unique_Name when I create a standby databases. This morning one of my colleague, when duplicating database using the production backup on non-production cluster,  asked me the question why does files in the ASM are being created in folder with the old database name and not with the new database name. I reviewed and found out that DB_Unique_Name parameter was left unchanged which caused this issue. This prompted me to believe that DB_Unique_Name is being used for creating the OMF file structure.  This is not documented clearly in Oracle Documentation, but I found this nice article which explains the difference between DB_Name and DB_Unique_Name.

http://ora12c.blogspot.com/2012/08/difference-between-dbname-dbuniquename.html