Friday, May 29, 2020

How to install SQLdeveloper on Macbook Pro Mojave

  1. Download and install Java 11. You can choose SDK or JDK version. I opted for JDK
  2. check your java version
    java -version
  3. Download latest sqldeveloper from here
  4. change to Applications folder
    cd /Applications
  5. Copy the dowloaded zip to the Application folder. we will have backup zip file in downloads.
    We will delete the zip file when done.
    cp -p $HOME/Downloads/sqldeveloper-19.2.1.247.2212-macosx.app.zip .
  6. unzip sqldeveloper-19.2.1.247.2212-macosx.app.zip .
  7. Look for the SQLDevelopers.app file
    ls -alt SQLdeveloper.app
  8. Open finder and look for SQLdeveloper.app ( below )
  9. Click SQLDeveloper from launchpad
  10. create database connections & voila done!
  11. Delete the zip file from /Applications folder
    rm -f sqldeveloper-19.2.1.247.2212-macosx.app.zip

Wednesday, January 24, 2018

LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo


Problem:- While running opatch lsinventory, command fails with the below error:


Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1
Central Inventory : /u01/app/oraInventory
   from           : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.4.0
Log file location : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/cfgtoollogs/opatch/opatch2018-01-24_01-10-13AM_1.log

List of Homes on this system:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Solution:- 

cd $ORACLE_HOME/oui/bin
./attachHome.sh

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 21183 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /mnt/san01ch/vd001_v001/u01/app/oraInventory
'AttachHome' was successful.
[oracle@amo02ch:/mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/oui/bin ORA2CH] $ opatch lsinventory 
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1
Central Inventory : /mnt/san01ch/vd001_v001/u01/app/oraInventory
   from           : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.4.0
Log file location : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/cfgtoollogs/opatch/opatch2018-01-24_01-12-40AM_1.log

Lsinventory Output file location : /mnt/san01ch/vd001_v001/u01/app/oracle/product/112040/db_1/cfgtoollogs/opatch/lsinv/lsinventory2018-01-24_01-12-40AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (1) :

Patch  18522509     : applied on Wed Mar 11 16:34:12 PDT 2015
Unique Patch ID:  17604597
Patch description:  "Database Patch Set Update : 11.2.0.4.3 (18522509)"
   Created on 30 Jun 2014, 08:14:42 hrs PST8PDT
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
   Bugs fixed:
     17752995, 17288409, 16392068, 17205719, 17811429, 17767676, 17614227
     17040764, 17381384, 17754782, 17726838, 13364795, 17311728, 17389192
     17006570, 17612828, 17284817, 17441661, 13853126, 17721717, 13645875
     18203837, 17390431, 16542886, 16992075, 16043574, 17446237, 16863422
     14565184, 17071721, 17610798, 17468141, 17786518, 17375354, 17397545
     18203838, 16956380, 17478145, 16360112, 17235750, 17394950, 13866822
     17478514, 17027426, 12905058, 14338435, 16268425, 13944971, 18247991
     14458214, 16929165, 17265217, 13498382, 17786278, 17227277, 17546973
     14054676, 17088068, 16314254, 17016369, 14602788, 17443671, 16228604
     16837842, 17332800, 17393683, 13951456, 16315398, 18744139, 17186905
     16850630, 17437634, 19049453, 17883081, 15861775, 17296856, 18277454
     16399083, 16855292, 18018515, 10136473, 16472716, 17050888, 17865671
     17325413, 14010183, 18554871, 17080436, 16613964, 17761775, 16721594
     17588480, 17551709, 17344412, 18681862, 15979965, 13609098, 18139690
     17501491, 17239687, 17752121, 17602269, 18203835, 17297939, 17313525
     16731148, 17811456, 14133975, 17600719, 17385178, 17571306, 16450169
     17655634, 18094246, 17892268, 17165204, 17011832, 17648596, 16785708
     17477958, 16180763, 16220077, 17465741, 17174582, 18522509, 16069901
     16285691, 17323222, 18180390, 17393915, 16875449, 18096714, 17238511
     17596908, 17811438, 17811447, 18031668, 16912439, 18061914, 17622427
     17545847, 16943711, 17082359, 17346671, 18996843, 14852021, 17783588
     16618694, 17672719, 17614134, 17341326, 17546761, 17716305



--------------------------------------------------------------------------------

OPatch succeeded.

Saturday, January 13, 2018

Database Design thoughts : A personal non-academic perspective.

The benefits of any database model, be it relational or non-relational is
  1. Data storage is efficient.
  2. Data retrival is efficient.
  3. Reporting is efficient
  4. Data transformation into Information is easy and self documenting the business / process. 
The first 3 benefits get a lot of focus while designing a database. In today's scenarios, implementations to achieve these benefits , are mainly managed by IT departments. The 4th benefit remains unspoken &  expected to be outcome of the first three.

4th benefit requires equal , if not more focus during the design phase. It should be the starting point of designing. Product managers and DBAs need to sit down and dream a little bit about current and future scenarios. After they have a rough draft of things to come, security & legal teams' input comes next in line. There are many academic papers and thoughts. 
         However after seeing difficult and messy designing of databases, The designing considerations boil down to :-
  1. What legal rules apply to the data being stored.
  2. What is data entry point.
  3. What is data exit point.
  4. At what stage or condition should the data be removed completely from the database.
  5. How to categorize data segments for security and privacy.
  6. What personnel and roles should have direct access to data.
  7. What will be data policy of the company and who will define the policy.
Each of the above , including legal, requires workflow with clear cut process in place. 
These aspects of designing used to be DBA's responsibility but in changing times when the role of DBA's is diminishing to implementing data storage, retrieval and reporting efficiency , Product managers and Security teams' involvement increases considerably.

Implementation of what comes out of the 4th Benefit falls on the shoulders of IT departments.  IT departments are at their best when it comes to choice of correct technology , machines and storage. They would also be able to analyze what type of database technology is best for the organization. Database technologies dictate datatypes, tables, columns, rows, roles, privileges and so on. Modularly designed database are low-maintenance. They also increase efficiency of developers who create in-house applications. Examples of modularity are:-
  1. Authentication module
  2. Authorization module
  3. Sales module 
  4. Marketing module
  5. Billing module 
  6. Decision making module (data warehouse module)
  7. and so on...

another module 'Link module' would be needed to to implement efficient storage and retrieval of data.  DFD should be also created and available as and when needed.


















Thursday, January 11, 2018

Preventive tasks : Avoid errors & issues while installing oracle 11gR2 on Red Hat Enterprise Linux 7 server(x86-64):-

Error/Issue No:-1 Packages "elfutils-libelf-devel-0.97" And "pdksh-5.2.14" Are Missing (PRVF-7532) (Doc ID 1454982.1)


Workaround:-
Once the software is copied/extracted under  <path>/database, do the following:
1
 Change directory to <path>/database/stage/cvu/cv/admin
2
Backup cvu_config

% cp cvu_config backup_cvu_config
3
Edit cvu_config and change the following line:

CV_ASSUME_DISTID=OEL4
TO
CV_ASSUME_DISTID=OEL6
4
Save the updated cvu_config file
5
Install the 11.2.0.3 or 11.2.0.4 software using <path>/database/runInstaller

cd <path>/database
6
./runInstaller

Error/Issue No:-2 error in invoking target 'agent nmhs' of make file ins_emagent.mk while installing Oracle 11.2.0.4 on Linux (Doc ID 2299494.1)



workaround:-
1
vi $ORACLE_HOME/sysman/lib/ins_emagent.mk
2
search for the line

$(MK_EMAGENT_NMECTL)
3
Then replace the line with

$(MK_EMAGENT_NMECTL) -lnnz11
and save the file
4
Then click “Retry” button to continue.


Tuesday, August 11, 2015

Oracle and BASH : How to calculate how many minutes have elapsed between two timestamps

There are many ways to calculate minutes passed between two timestamps. The script below is to calculate how many minutes passed between two time stamps using oracle database.

Script : test.sh
====================
#!/bin/bash

# Create a function to accept two timestamps. The order of the timestapms (FROM_TIME, TO_TIME) does not affect the result.
fn_howmany_mins()
{
FROM_TIME=$1
TO_TIME=$2
MINS=`sqlplus -S "/ as sysdba " <<EOF
set echo off
set feedback off
set linesize 10;
set pagesize 0
set trimspool on
set space 0
set truncate on
select abs(round((to_date('$TO_TIME','YYYYMMDD_HH24MISS')-to_date('$FROM_TIME','YYYYMMDD_HH24MISS'))*1440)) from dual;
exit;
EOF`
MINS=`echo $MINS`
echo $MINS
}

#SET up test time stapms as below.
FROM_TIME=20150811_110601
TO_TIME=`date +"%Y%m%d_%H%M%S"`
echo "FROM_TIME=$FROM_TIME TO_TIME=$TO_TIME"

#Call the function to calculate the minutes.
fn_howmany_mins $FROM_TIME $TO_TIME

Sample output sh ./test.sh
==================
FROM_TIME=20150811_111458 TO_TIME=20150811_110601
9

In an actual usage remove the echo statement to get pure number . e.g. number 9 above


Monday, August 10, 2015

Bash : How to get last character in a string

Last character in a string can be extracted using sed. example:-

$cat test.sh
#!/bin/bash
STRING="Humpty Dumpty"
STRING=`echo "$STRING"|sed -e 's/\(^.*\)\(.$\)/\2/'`
echo "STRING=$STRING"
exit 0

Output:-
STRING=y

Thursday, August 6, 2015

How to send select rows into a while loop (bash)

Sample File:-  test.txt
2015-08-01  WHY=Beacuse of testing
DATE=2015-08-01  Beacuse of testing
DATE=2015-08-01  WHY=Beacuse of testing
2015-08-01  Beacuse of testing

Sample script:script.sh
head -3 test.txt|while read LINE
do
echo  "LINE=$LINE"
STR1=`echo $LINE|awk '{print $1}'`
STR2=`echo $LINE|awk '{first=$1; $1=""; print $0}'`

VALU1=`echo $STR1|awk -F\= '{print $2}'`
VALU1="${VALU1:-$STR1}"
LABL1=`echo $STR1|awk -F\= '{print $1}'` 
if [ "$VALU1" == "$STR1" ]
   then
   LABL1=''
   export STR1
fi

VALU2=`echo $STR2|awk -F\= '{first=$1; $1="";print $0}'`
VALU2="${VALU2:-$STR2}"
LABL2=`echo $STR2|awk -F\= '{print $1}'`
if [ "$VALU2" == "$STR2" ]
   then
   LABL2=''
   export STR1
fi
echo "STR1=$STR1 "
echo "LABL1=$LABL1"
echo "VALU1=$VALU1"
echo -e "\t\tSTR2=$STR2 "
echo -e "\t\tLABL2=$LABL2"
echo -e "\t\tVALU2=$VALU2"
echo -e "\n\n"
done

Sample Output:-
LINE=2015-08-01  WHY=Beacuse of testing
STR1=2015-08-01 
LABL1=
VALU1=2015-08-01
STR2= WHY=Beacuse of testing 
LABL2=WHY
VALU2= Beacuse of testing



LINE=DATE=2015-08-01  Beacuse of testing
STR1=DATE=2015-08-01 
LABL1=DATE
VALU1=2015-08-01
STR2= Beacuse of testing 
LABL2=
VALU2= Beacuse of testing



LINE=DATE=2015-08-01  WHY=Beacuse of testing
STR1=DATE=2015-08-01 
LABL1=DATE
VALU1=2015-08-01
STR2= WHY=Beacuse of testing 
LABL2=WHY
VALU2= Beacuse of testing