Passwordless Data Pump 19c

That’s a very light bug with a very simple workaround, but it may require a little change in scripts. If you use passwordless authentication (external password file or OS authentication) with Data Pump in 19c it will ask for the password. The solution is just to answer whatever you want because the external authentication will be used anyway.

Example

I create the wallet

mkstore -wrl $ORACLE_HOME/network/admin -create <<CREATE
w4ll3t-P455w0rd
w4ll3t-P455w0rd
CREATE

I create a tnsnames.ora entry that I’ll use to connect:

cat >> $ORACLE_HOME/network/admin/tnsnames.ora  <<CAT
CDB1A_SYSTEM=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=CDB1A))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
CAT

I add a credential for this entry — here SYSTEM user and its password:

mkstore -wrl $ORACLE_HOME/network/admin -createCredential \
 CDB1A_SYSTEM SYSTEM <<CREATE
user-P455w0rd
user-P455w0rd
w4ll3t-P455w0rd
CREATE

I also add a credential for Eazy Connect Plus (see this previous post for the dummy parameter):

mkstore -wrl $ORACLE_HOME/network/admin -createCredential \
 //localhost/PDB1?_user=system SYSTEM <<CREATE
user-P455w0rd
user-P455w0rd
w4ll3t-P455w0rd
CREATE

Finally, I set the sqlnet.ora for this:

cat >> $ORACLE_HOME/network/admin/sqlnet.ora <<'CAT'
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="$ORACLE_HOME/network/admin")))
SQLNET.WALLET_OVERRIDE=TRUE
CAT

Then, I can connect passwordless

connect /@CDB1A_SYSTEM
show user
show con_name
connect /@//localhost/PDB1?_user=system
show user
show con_name

Nothing new here. It is an old feature and very simple to setup. In 2019 we do not see any password in clear text in scripts or command line, right?

Data Pump expdp/impdp

But let’s try to use the same with Data Pump

expdp /@CDB1A_SYSTEM

I want to connect passwordless because the password is in the wallet, but Data Pump asks me for the password. Don’t worry: it asks but doesn’t care. The connection will use the wallet one. Then the solution is to send /dev/null as the stdin:

‘“/ as sysdba”’

There’s the same when using OS authentication like running expdp or impdp connected SYSDBA. Note that this is not a recommendation: running Data Pump as SYSDBA is not a good idea. But if you don’t want to show the password and you are too lazy to setup a wallet, this was working until 19c. The only thing was to double-quote it so that Data Pump takes it as one parameter, and to single-quote around it so that the shell does not interpret your double quotes:

expdp '"/ as sysdba"'

Of course, another reason not to use this is that OS authentication connects you to the CDB$ROOT where you should not have a lot to export…

Fixed in 20.1

After writing this I realize that there’s now a MOS note about it, a patch and a solution (Upgrade to 20.1 when available): https://support.oracle.com/epmos/faces/DocContentDisplay?id=2556152.1