Streamlining DataPump Operations in Windows: Bypassing ORACLE_PDB_SID with Secure Wallets

Running DataPump directly from a PDB as SYSDBA is a common task in Oracle multitenant architecture. However, there's a catch: the handy ORACLE_PDB_SID environment variable, introduced in Oracle 18c to ease direct access to specific PDBs, only works on Linux/Unix environments. So, what do you do if you’re stuck on a Windows machine?

 Let’s dive into a fun and effective way to bypass this limitation using a secure wallet store to manage your PDB SYS credentials!

Ever tried running Datapump from a PDB as SYS on a Windows machine and felt like you were wrestling with a stubborn octopus? ORACLE_PDB_SID is supposed to make it easier, but guess what? It doesn't play nice with Windows. Don't worry, I've got a fun and simple workaround for you!

 

The Problem 

In the Oracle multi-tenant architecture, the default connection lands you in CDB$ROOT. Enter ORACLE_PDB_SID, a handy variable introduced in 18c to directly access a specific PDB. It works like a charm on Linux/Unix, but on Windows? Not so much. So, what's a database admin to do?

 

The Solution: Secure External Password Store (Wallet)

Forget about battling with ORACLE_PDB_SID. Instead, let's create a wallet to securely store our PDB SYS credentials. Here's how you can do it, step-by-step, and in a fun way!

Running DataPump as SYSDBA on Windows

In Windows, the best alternative is to create a wallet store to hold the PDB SYS credentials securely. Let’s break down the steps:

Step 1: Create a Wallet

First, we need a wallet to store those precious PDB credentials. You can use either mkstore or orapki tools. I prefer mkstore for its simplicity.

1. Create a Wallet: Fire up mkstore and set up a local wallet with a password.

mkstore -wrl <wallet_location> -create

mkstore -wrl . -create

Oracle Secret Store Tool Release 19.0.0.0.0 - Production 
Version 19.4.0 Copyright(c)2004, 2019, Oracle and/or its affiliates. 
                       All rights reserved. 
Enter password: 
Enter password again:

Syntax:
mkstore -wrl wallet_location -create

Default location is local directory

2. Store PDB SYS Credentials: Add your PDB credentials to the wallet.

mkstore -wrl <wallet_location> -createCredential <TNS_alias> sys

mkstore -wrl . -createCredential DBNAME1 sys 
Enter your secret/Password: ===> your sys password  
Re-enter your secret/Password: 
Enter wallet password: ===> Wallet pass

3. Check Wallet Files: Verify the wallet files (cwallet.sso and ewallet.p12) are created in your specified location 

24/03/2024 09:18 PM 581 cwallet.sso
24/03/2024 09:17 PM 0 cwallet.sso.lck 
24/03/2024 09:18 PM 536 ewallet.p12
24/03/2024 09:17 PM 0 ewallet.p12.lck

When setting up a Wallet, it typically comprises two essential files: cwallet.sso and ewallet.p12. The sso file, known as the autologin wallet, operates without a password, though it offers limited encryption. Conversely, the p12 file represents the PKCS12 wallet, serving as the primary encrypted repository. Upon opening the wallet, you'll notice the presence of lck files, indicating its active state.

Step 2: Update SQLNET.ORA

SYNTAX 

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = <wallet_location>)))
SQLNET.WALLET_OVERRIDE = TRUE

EXAMPLE
WALLET_LOCATION= (SOURCE=(METHOD=file) (METHOD_DATA=(DIRECTORY=C:\Oracle\product\19.0.0\db_1\network\admin))) 
SQLNET.WALLET_OVERRIDE=TRUE

Step 3: Verify the Connection

Test your connection to ensure everything is set up correctly

sqlplus /@<TNS_alias>

Step 4: Import the data using Datapump in PDB. 

Now, the fun part! Let's import your data using Datapump

Prepare the Par File: Create a parameter file for your import job. 

USERID="/ as sysdba"

DIRECTORY=dpump_dir
DUMPFILE=mydumpfile.dmp
LOGFILE=mydumplog.log
TABLES=your_tables

Set ORACLE_SID: Set the ORACLE_SID to your CDB. 

set ORACLE_SID=<CDB_SID>

Run the Import: Kick off the import process as SYSDBA.

impdp sys@<PDB_TNS_alias> parfile=impdp_nonCDB_To_PDB.par

Conclusion

And there you have it! A neat, wallet-based method to run Datapump from a PDB as SYS on a Windows environment. No more struggling with ORACLE_PDB_SID! This approach is not only secure but also flexible, allowing you to manage multiple PDB credentials effortlessly. Happy migrating!

Thank you for reading! If you have any questions or run into any hiccups, drop a comment below. Let's keep the data flowing smoothly