Home Administration Fixed - EXPDP - OS/ Wallet Authentication does not work, prompts for...

Fixed – EXPDP – OS/ Wallet Authentication does not work, prompts for password

I recently encountered a strange issue on a newly created multitenant database on 19.6 version. Our EXPDP job uses wallet to authenticate to the pluggable database. SQLPLUS could successfully connect/ authenticate to the database using credentials stored in the wallet. But, authentication failed for EXPDP using the same wallet credentials. After sometime found the following issue.

  • EXPDP Authentication fails when using wallet. EXPDP will prompt for password.
  • Any password (correct or incorrect) we enter will proceed with successful authentication.

Our jobs rely on wallet authentication so we couldn’t setup some of our jobs.

SQLPLUS connection

> $ORACLE_HOME/bin/sqlplus /@MYPDB

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 6 18:26:40 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed May 06 2020 18:23:59 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

EXPDP connection

> $ORACLE_HOME/bin/expdp /@MYPDB

Export: Release 19.0.0.0.0 - Production on Wed May 6 19:21:48 2020
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Notice how it prompts for password.

After checking a few times for syntax issues or misconfigurations, the situation led me to believe that this has to be a bug.

Upon checking Metalink, I found the bug – IMPDP/EXPDP Doesn’t work wih OS authentication or External Password Store (Doc ID 2560960.1) – Bug# 28707931

Steps to apply the fix

Download and stage the patch

Download p28707931_196000DBRU_Linux-x86-64.zip and stage it on the server. Unzip it. Follow the steps in the README file to ensure you meet the pre-requisites.

rac1db01 | MYCDB1 | /tmp/patches/one-off
> unzip p28707931_196000DBRU_Linux-x86-64.zip
Archive:  p28707931_196000DBRU_Linux-x86-64.zip
   creating: 28707931/
  inflating: 28707931/README.txt
   creating: 28707931/files/
   creating: 28707931/files/rdbms/
   creating: 28707931/files/rdbms/lib/
   creating: 28707931/files/rdbms/lib/libdbtools19.a/
  inflating: 28707931/files/rdbms/lib/libdbtools19.a/udcutl.o
   creating: 28707931/etc/
   creating: 28707931/etc/config/
  inflating: 28707931/etc/config/actions.xml
  inflating: 28707931/etc/config/inventory.xml
  inflating: PatchSearch.xml

ORACLE_HOME

Ensure that you set (as the home user) the ORACLE_HOME environment variable to the Oracle home.

> . oraenv
ORACLE_SID = [MYCDB1] ? MYCDB1
The Oracle base remains unchanged with value /u01/app/oracle

OPATCH

Patch README instructs OPATCH utility to be minimum 12.2.0.1.17. If you are running a lower version, download the latest OPATCH utility from support.oracle.com

> $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.

Base Patch and Inventory

Per README, ensure that 19 Release 19.6.0.0.200114DBRU Patch Set Update (PSU) 30557433 is already applied on the Oracle Database.

This step also validates ‘opatch lsinventory’ works

> $ORACLE_HOME/OPatch/opatch lsinv | grep -i 30557433
Patch  30557433     : applied on Thu Jan 23 19:54:28 GMT 2020
Patch description:  "Database Release Update : 19.6.0.0.200114 (30557433)"

OS Executables

Ensure the following OS executables are in the $PATH definition.

> which make
/bin/make
rac1db01 | MYCDB1 | /home/oracle
> which ar
/bin/ar
rac1db01 | MYCDB1 | /home/oracle
> which ld
/bin/ld
rac1db01 | MYCDB1 | /home/oracle
> which nm
/bin/nm

Patch Conflicts

Ensure currently installed interim patches do not conflict with the new patch.

> cd /tmp/patches/one-off/28707931
> $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-06_19-32-23PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Shutdown Services

We are ready to install the patch. Shutdown all the services running from the Oracle Home.

> srvctl stop instance -d MYCDB -i MYCDB1 -o immediate

Patch Apply

Use the -local option to install the patch on the local node.

> cd /tmp/patches/one-off/28707931
rac1db01 | MYCDB1 | /tmp/patches/one-off/28707931
> $ORACLE_HOME/OPatch/opatch apply -local
Oracle Interim Patch Installer version 12.2.0.1.17
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.17
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-06_19-49-15PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   28707931

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28707931' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...
Patch 28707931 successfully applied.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-06_19-49-15PM_1.log

OPatch succeeded.

Verify

Check the installed patch.

> $ORACLE_HOME/OPatch/opatch lsinv 
Patch  28707931     : applied on Wed May 06 19:49:48 GMT 2020
Unique Patch ID:  23287344
Patch description:  "RUNNING IMPDP WITH OS AUTHENTICATION PROMPTS FOR A PASSWORD"
   Created on 21 Jan 2020, 01:39:29 hrs PST8PDT
   Bugs fixed:
     28707931
   This patch overlays patches:
     30557433, 29517242, 30557433, 29517242, 30557433
   This patch needs patches:
     30557433, 29517242, 30557433, 29517242, 30557433
   as prerequisites

Start the database instance.

> srvctl start instance -d MYCDB -i MYCDB1

Verify the database instance. It should be open ‘Read Write’ and should not be in ‘Restricted’ mode.

SQL> set lines 200
SQL> select inst_id, name, open_mode, restricted from gv$MYPDBs;

   INST_ID NAME 	  OPEN_MODE  RES
---------- -------------- ---------- ---
	 1 PDB$SEED	  READ ONLY  NO
	 1 MYPDB 	  READ WRITE NO
	 2 PDB$SEED	  READ ONLY  NO
	 2 MYPDB 	  READ WRITE NO

Repeat the steps on each node where 19c binaries are installed.

With patch 28707931 in place, EXPDP now successfully authenticates using wallet credentials.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest posts

Steps to remove OEM Agent

Recently I migrated all targets (Cluster, Databases, Instances, ASM, etc) and their jobs to a 13c OEM...

DBCA fails with ORA-27125 on 19c

Issue I recently encountered an issue where DBCA failed with ORA-27125 when creating a...

How to rename a PDB in 19c

Let's look at how to rename a PDB in 19c. The steps are the same as 12c....

How to change the ethernet switch password on Exadata

Let's look at how to log in and change the ethernet switch (adm) password on an Exadata...

You might also likeRELATED
Recommended to you