Secure and Automating Oracle Data Pump with Wallet-Stored Credentials

Step 1: Create the Oracle Wallet

Create a secure directory:

mkdir -p /u01/app/oracle/wallets/datapump

Example:
C:\test1\wallets>mkdir datapump
C:\test1\wallets>cd datapump
C:\test1\wallets\datapump>

Create the wallet:

mkstore -wrl /u01/app/oracle/wallets/datapump -create

Example:
mkstore -wrl C:\test1\wallets\datapump -create

C:\test1\wallets\datapump>mkstore -wrl C:\test1\wallets\datapump -create
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:

C:\test1\wallets\datapump>dir
 Volume in drive C is Windows
 Volume Serial Number is D21E-AF9B

 Directory of C:\test1\wallets\datapump

10/08/2025  11:04 AM    <DIR>          .
10/08/2025  11:04 AM    <DIR>          ..
10/08/2025  11:04 AM               194 cwallet.sso
10/08/2025  11:04 AM                 0 cwallet.sso.lck
10/08/2025  11:04 AM               149 ewallet.p12
10/08/2025  11:04 AM                 0 ewallet.p12.lck
               4 File(s)            343 bytes
               2 Dir(s)  186,633,195,520 bytes free

Step 2: Store Database Credentials

Add credentials to the wallet:
mydb_alias: TNS alias defined in tnsnames.ora
db_user: Oracle username
db_password: Oracle password

mkstore -wrl /u01/app/oracle/wallets/datapump -createCredential mydb_alias db_user db_password

Example:
mkstore -wrl C:\test1\wallets\datapump -createCredential mydb_alias db_user db_password

mkstore -wrl C:\test1\wallets\datapump -createCredential orcl hr hr

C:\test1\wallets\datapump>mkstore -wrl C:\test1\wallets\datapump -createCredential orcl hr hr
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

C:\test1\wallets\datapump>

Step 3: Configure SQLNET.ORA

Edit sqlnet.ora to enable wallet-based authentication:

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
   (METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallets/datapump)))

Example: add the entires in SQLNET file
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
   (METHOD_DATA = (DIRECTORY = C:\test1\wallets\datapump)))

Step 4: Create a TNS Alias

Ensure tnsnames.ora includes an alias for your target database:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = orcl)))

Step 5: Run Data Pump Export Using Wallet

Now you can run expdp without specifying credentials:

Note: No username or password needed—Oracle Wallet handles authentication.

expdp /@mydb_alias DIRECTORY=dp_dir DUMPFILE=export.dmp LOGFILE=export.log SCHEMAS=HR

Step 6: Run Data Pump Import Using Wallet

impdp /@mydb_alias DIRECTORY=dp_dir DUMPFILE=export.dmp LOGFILE=import.log REMAP_SCHEMA=HR:HR_NEW

Step 7: Verify Wallet Usage

Check wallet status:

SELECT * FROM V$ENCRYPTION_WALLET;

Check credential usage:

mkstore -wrl /u01/app/oracle/wallets/datapump -listCredential

Example:
mkstore -wrl C:\test1\wallets\datapump -listCredential

C:\test1\wallets\datapump>mkstore -wrl C:\test1\wallets\datapump -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: orcl hr