Retrieving the Public Key for Key Pair on Linux or Mac OS

ssh-keygen command can be used on Linux or Mac OS to retrieve the public key from the private SSH key:

$ ssh-keygen -y -f MyKeyPair.pem 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQChauLwkBK/vIiFY/t7uY6lzxESqZkZNvCAA3L42OH2fWzKptqGF+N32zjmLLSPFpYjoEHoHpi5e7yypTmiljtHcKUTwJTs3xclQrApCQvR+LneOi/5P5WaYl61G76osJesXiunLTa+RVr3LDR96LjPcql7JDnuh1RFhDqZ87nDfcGmXGV8iG7w3bk3R/2LuzzMYTgEVdv91S1OF1roH1baPXSV8MaYbOKhMUqV61+eP6/F5ZhT5Gk0BKX1KnQ3/gbgMqjMWRMZzYUeVjUbC52lYwrrBTQX5tHphAJtOTNJ/CpyuEuZ7ED+XYhX9Q1DNOZ47K51xbg5lsnyOBYSUqHz

-y – This option will read a private OpenSSH format file and print its public key.
-f – Specifies the filename of the key file.

Find Oracle patch description

Some patches contain several sub-patches inside. For example, when we download GI RU and unzip it, the unzipped folder contains several patch folders. To find out information about these patches you can do any of the following:

1. OPen README.html and find the section “Patch Numbers Getting Installed as Part of this Bundle Patch

2. Each patch sub-folder contains etc/config/inventory.xml, that contains description about patch:

[oracle@primrac1 29708769]$ pwd
 /home/oracle/Downloads/29708769

[oracle@primrac1 29708769]$ cat ./29834717/etc/config/inventory.xml|grep patch_description
     Database Release Update : 19.4.0.0.190716 (29834717)

[oracle@primrac1 29708769]$ cat ./29850993/etc/config/inventory.xml |grep patch_description
     OCW RELEASE UPDATE 19.4.0.0.0 (29850993)

Or we can see inventory.xml in all folders:

[oracle@primrac1 29708769]$ cat ./*/etc/config/inventory.xml |grep patch_description
     TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)
     Database Release Update : 19.4.0.0.190716 (29834717)
     OCW RELEASE UPDATE 19.4.0.0.0 (29850993)
     ACFS RELEASE UPDATE 19.4.0.0.0 (29851014)

3. Another option is to use opatch lspatches or opatch query -all and query/list patch information:

[oracle@primrac1 29708769]$ pwd
 /home/oracle/Downloads/29708769

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch lspatches ./29850993|grep patch_description
 patch_description:OCW RELEASE UPDATE 19.4.0.0.0 (29850993)

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch lspatches ./29834717|grep patch_description
 patch_description:Database Release Update : 19.4.0.0.190716 (29834717)

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch query -all 29850993|grep description
  Patch description: "OCW RELEASE UPDATE 19.4.0.0.0 (29850993)"

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch query -all 29834717|grep description
  Patch description: "Database Release Update : 19.4.0.0.190716 (29834717)"

Patch 28729234 requires component(s) that are not installed in OracleHome. These not-installed components are oracle.crs:11.2.0.4.0

Problem:

Applying OCW patch 28729234 on RDBMS home failed with the following error:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME

Oracle Interim Patch Installer version 11.2.0.3.20
...
UtilSession failed: Patch 28729234 requires component(s) that are not installed in OracleHome. These not-installed components are oracle.crs:11.2.0.4.0

Reason:

The error message is confusing, because the real reason is in the patch itself. There is a duplication of folders and files. Custom/server directory under 28729234, also contains 28729234 directory.

Solution:

As a workaround run opatch from inner 28729234 directory:

[oracle@rac1 ~]$ cd /home/oracle/28813878/28729234/custom/server/28729234

Generate emocmrsp file:

[oracle@rac1 28729234]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/emocmrsp

OCM Installation Response Generator 10.3.7.0.0 - Production
Copyright (c) 2005, 2012, Oracle and/or its affiliates.  All rights reserved.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (ocm.rsp) was successfully created

Apply patch using the following command:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -ocmrf ./ocm.rsp 
...
Start OOP by Prereq process.
Launch OOP…
Oracle Interim Patch Installer version 11.2.0.3.20
Copyright (c) 2019, Oracle Corporation.  All rights reserved.
...
Applying interim patch '28729234' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Patching component oracle.rdbms, 11.2.0.4.0…
Patch 28729234 successfully applied.

Checking that OCW is applied:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch lspatches
28729234;OCW Patch Set Update : 11.2.0.4.190115 (28729234)
28729262;Database Patch Set Update : 11.2.0.4.190115 (28729262)

Platform IDs supported by patch are: 46. Platform ID needed is : 226

Problem:

Opatch apply failed with the following error:

Platform IDs supported by patch are: 46 Patch ( 24006111 ) is not applicable on current platform.
Platform ID needed is : 226

Reason:

You may not be familiar with Platform IDs, so the above error actually means “Downloaded patch is for Linux x86 and Linux x86-64 is required”.

Solution:

Download and apply patch for Linux x86-64.

Additional information:

Full list of Platform IDs can be found here

PlatformARU_ID
Apple Mac OS X (PowerPC)421
HP Tru64 UNIX87
HP-UX Itanium197
HP-UX Itanium (32-bit)278
HP-UX PA-RISC (32-bit)2
HP-UX PA-RISC (64-bit)59
IBM AIX on POWER Systems (32-bit)319
IBM AIX on POWER Systems (64-bit)212
IBM i on POWER Systems43
IBM S/390-based Linux (31-bit)211
IBM z/OS on System z30
IBM: Linux on POWER Systems227
IBM: Linux on System z209
Linux Itanium214
Linux x8646
Linux x86-64226
Microsoft Windows (32-bit)912
Microsoft Windows Itanium (64-bit)208
Microsoft Windows x64 (64-bit)233
Sun Solaris SPARC (32-bit)453
Sun Solaris SPARC (64-bit)23
Sun Solaris x86 (32-bit)173
Sun Solaris x86-64 (64-bit)267

ACFS Input/output error on OS kernel 3.10.0-957

Problem:

My ACFS volume has intermittent problems, when I run ls command I get ls: cannot access sm: Input/output error and user/group ownership contains question marks.

[root@primrac1 GG_HOME]# ll
ls: cannot access ma: Input/output error
ls: cannot access sm: Input/output error
ls: cannot access deploy: Input/output error
total 64
d????????? ? ? ? ? ? deploy
drwx------ 2 oracle oinstall 65536 Sep 28 21:05 lost+found
d????????? ? ? ? ? ? ma
d????????? ? ? ? ? ? sm

Reason:

Doc ID 2561145.1 mentions that kernel 3.10.0-957 changed behaviour of d_splice_alias interface which is used by ACFS driver.

My kernel is also the same:

[root@primrac1 GG_HOME]# uname -r
3.10.0-957.21.3.el7.x86_64

Solution:

Download and apply patch 29963428 on GI home.

[root@primrac1 29963428]# /u01/app/19.3.0/grid/OPatch/opatchauto apply -oh /u01/app/19.3.0/grid

JDBC 11g: SQLException(“Io exception: Connection reset”)

Problem:

Connection using 11g ojdbc was very slow and most of the time was failing with Connection reset error after 60s (default inbound connection timeout). Database alert log contained WARNING: inbound connection timed out (ORA-3136) errors.

Reason:

Oracle 11g JDBC drivers use random numbers during authentication. Those random numbers are generated by OS using /dev/random and if there is faulty/slow hardware or not too much activity on the system this generation can be slow, which causes slowness during jdbc connection.

Solution:

Instead of /dev/random indicate non-blocking /dev/urandom as java command line argument:

# java -Djava.security.egd=file:/dev/urandom -cp ojdbc8.jar:. JDBCTest "stbyrac-scan.example.com"

Simple java code to test connection to Oracle database

Assuming that client computer does not have Oracle client installed.

1. Download necessary version of ojdbc jar file from Oracle. The latest version for now is ojdbc8.jar

2. Install java development tools:

# yum install java-devel * -y

3. Create a sample java code, which:
– connects to the database
– selects 1 from dual
– disconnects

# cat JDBCTest.java
import java.sql.*;
 class JDBCTest{
     public static void main(String args[]) throws SQLException {
         Connection con = null;
         try{
             Class.forName("oracle.jdbc.driver.OracleDriver");
             String dbURL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + args[0] + ")(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=orclgg)))";
             System.out.println("jdbcurl=" + dbURL);
             String strUserID = "system";
             String strPassword = "Oracle123";
             con=DriverManager.getConnection(dbURL,strUserID,strPassword);
             System.out.println("Connected to the database.");
             Statement stmt=con.createStatement();
             System.out.println("Executing query");
             ResultSet rs=stmt.executeQuery("SELECT 1 FROM DUAL");
             while(rs.next())
                 System.out.println(rs.getInt("1"));
             con.close();
         }catch(Exception e){ System.out.println(e);}
         finally {
             con.close();
         }
 }
 }

4. Compile java code and check that *.class file was generated:

# javac JDBCTest.java

# ll  JDBCTest.*
 -rw-r--r-- 1 root root 1836 Sep 27 11:45 JDBCTest.class
 -rw-r--r-- 1 root root  925 Sep 27 11:45 JDBCTest.java

5. Run code:

# java -Djava.security.egd=file:/dev/../dev/urandom -cp ojdbc8.jar:. JDBCTest "stbyrac-scan.example.com"

jdbcurl=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyrac-scan.example.com)(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=orclgg)))

Connected to the database.
Executing query…
1

Please note that -Djava.security.egd=file:/dev/../dev/urandom parameter is required to have a stable connection. I will discuss the importance of this parameter in the next post.

OPATCHAUTO-72115: Please complete the previous apply session across all nodes to perform apply session

Problem:

My opatchauto outofplace patching failed on GI home. I was able to cleanup cloned GI home and information about it in inventory.xml, but after running opatchauto again I was getting the following error:

[root@rac1 29708703]# $ORACLE_HOME/OPatch/opatchauto apply -oh $ORACLE_HOME -outofplace
OPatchauto session is initiated at Sun Aug 18 20:40:43 2019
System initialization log file is /u01/app/18.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2019-08-18_08-40-46PM.log.
Session log file is /u01/app/18.3.0/grid/cfgtoollogs/opatchauto/opatchauto2019-08-18_08-42-20PM.log
The id for this session is Z1CP
OPATCHAUTO-72115: Out of place patching apply session cannot be performed.
OPATCHAUTO-72115: Previous apply session  is not completed on node rac1.
OPATCHAUTO-72115: Please complete the previous apply  session across all nodes to perform apply session.
OPatchAuto failed.

Solution:

Clear checkpoint files from the previous session :

[root@rac1 29708703]# cd /u01/app/18.3.0/grid/.opatchauto_storage/rac1
[root@rac1 rac1]# ls
oopsessioninfo.ser
[root@rac1 rac1]# rm -rf oopsessioninfo.ser 

Rerun opatchauto apply again.

sqlplus backspace – ^H, delete – [[D^, add command history

Problem:

In sqlplus, pressing backspace writes ^H and delete – [[D^. Your terminal settings affects keyboard behaviour in sqlplus.

Let’s improve our sqlplus – make backspace and delete keys work as expected and in addition to this let’s add a new feature such as maintaining command history.

Solution:

Install a readline wrapper (rlwrap) – it maintains a separate input history for each command.

[root@rac1 ~]# yum install rlwrap -y 

Create alias for sqlplus in /etc/profile:

alias sqlplus='rlwrap sqlplus' 

Reconnect to the terminal and check that alias is created:

[oracle@rac1 ~]$ alias
alias egrep='egrep --color=auto'
alias fgrep='fgrep --color=auto'
alias grep='grep --color=auto'
alias l.='ls -d .* --color=auto'
alias ll='ls -l --color=auto'
alias ls='ls --color=auto'
alias sqlplus='rlwrap sqlplus'
alias vi='vim'
alias which='alias | /usr/bin/which --tty-only --read-alias --show-dot --show-tilde'

Connect to sqlplus:

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> 

And test your new sqlplus 🙂 :

Use backspace, delete, execute some command and then press arrow up to see previous command.

Patch Planner to check and request conflict patches

Problem:

Recently, I was applying p29963428_194000ACFSRU_Linux-x86-64.zip on top of 19.4 GI home and got the following error:

==Following patches FAILED in analysis for apply:
 Patch: /u01/swtmp/29963428/29963428
 Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-08-07_10-07-56AM_1.log
 Reason: Failed during Analysis: CheckConflictAgainstOracleHome Failed, [ Prerequisite Status: FAILED, Prerequisite output: 
 Summary of Conflict Analysis:
 There are no patches that can be applied now.
 Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches : 
 29851014, 29963428
 Conflicts/Supersets for each patch are:
 Patch : 29963428
 Bug Conflict with 29851014 Conflicting bugs are: 29039918, 27494830, 29338628, 29031452, 29264772, 29760083, 28855761 ... 
 After fixing the cause of failure Run opatchauto resume

Solution:

Oracle MOS note ID 1317012.1 describes steps how to check such conflicts and request conflict/merged patches in previous:

1. Run lsinventory from the target home:

[grid@rac1 ~]$ /u01/app/19.3.0/grid/OPatch/opatch lsinventory > GI_lsinventory.txt

2. Logon to support.oracle.com -> Click the “Patch and Updates” tab -> Enter the patch number you want to apply:

2. Click Analyze with OPatch…

3. Attach GI_lsinventory.txt file created in the first step and click “Analyze for Conflict”:

4. Wait for a while and you will see the result. According to it, patch 29963428 conflicts with my current patches:

From the same screen I can “Request Patch”.

5. After clicking “Request Patch” button I got the following error:

Click “See Details”:

The message actually means that fix for the same bug is already included in currently installed 19.4.0.0.190716ACFSRU.

So I don’t have to apply 29963428 patch. I wanted to share the steps with you , because the mentioned tool is really useful.