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.
> $ORACLE_HOME/bin/sqlplus /@MYPDB SQL*Plus: Release 22.214.171.124.0 - Production on Wed May 6 18:26:40 2020 Version 126.96.36.199.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 188.8.131.52.0 - Production Version 184.108.40.206.0
> $ORACLE_HOME/bin/expdp /@MYPDB Export: Release 220.127.116.11.0 - Production on Wed May 6 19:21:48 2020 Version 18.104.22.168.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
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
Patch README instructs OPATCH utility to be minimum 22.214.171.124.17. If you are running a lower version, download the latest OPATCH utility from support.oracle.com
> $ORACLE_HOME/OPatch/opatch version OPatch Version: 126.96.36.199.17 OPatch succeeded.
Base Patch and Inventory
Per README, ensure that 19 Release 188.8.131.52.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 : 184.108.40.206.200114 (30557433)"
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
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 220.127.116.11.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 : 18.104.22.168.17 OUI version : 22.214.171.124.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.
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
-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 126.96.36.199.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 : 188.8.131.52.17 OUI version : 184.108.40.206.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, 220.127.116.11.0... Patching component oracle.rdbms.util, 18.104.22.168.0... Patching component oracle.rdbms, 22.214.171.124.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.
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.