How to run Datapump from a PDB as SYS when ORACLE_PDB_SID can’t work

Intro

In Oracle multitenant architecture, default database container upon connection is CDB$ROOT. But as of 18c, a new environment variable appeared to ease the direct access to a specific PDB (given a defined ORACLE_SID). The variable in question is ORACLE_PDB_SID. 

As explained in Mike Dietrich’s Blog, this variable is checked by a small AFTER EVENT LOGON trigger called DBMS_SET_PDB and runs an alter session set container if ORACLE_PDB_SID is defined.

However, the variable only works in Linux/Unix environments. So if ORACLE_PDB_SID can’t be used in windows, what’s the alternative?  

How do I do it in Windows

DataPump on a PDB as sysdba

• In my case the best alternative was creating a wallet store and put the PDB sys credentials in it

Secure External Password Store (Wallet)

• Both mkstore and orapki tools can create wallets to store credentials & a same wallet can be opened by either one. But what’s the difference then?
• mkstore is older and doesn’t achieves all PKI(Public Key Infrastructure) features required in Fusion middleware for example, that’s why okapi is recommended in middle-tier environments.
• Now let’s create a wallet to store a 19c PDB sys password with mkstore

1- Create a (local) wallet using mkstore and a wallet password which will allow us to manage it later

C:\ORACLE_HOME\admin>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- Create PDB sys Credentials, here MYPDB is a TNS alias and wallet location is set to local dir

C:\ORACLE_HOME\network\admin> mkstore -wrl . -createCredential MYPDB sys 
Enter your secret/Password: === your sys password  
Re-enter your secret/Password: 
Enter wallet password: === Walletpass

Syntax: 
mkstore -wrl <wallet_location> -createCredential <TNS_Alias> <username> <pass>
Default location is local directory

3- Check the files generated after this operation

C:\ORACLE_HOME\network\admin> dir
12/22/2021 06:38 PM 581 cwallet.sso
12/22/2020 06:37 PM 0 cwallet.sso.lck 
12/22/2020 06:38 PM 536 ewallet.p12
12/22/2020 06:37 PM 0 ewallet.p12.lck

• A configured Wallet consists of two files, cwallet.sso and ewallet.p12
• sso refers to the autologin wallet that does not need a password => not really encrypted
• p12 refers to the PKCS12 wallet (Certificate file)=> original encrypted wallet
• lck files are there once the wallet is open

4- Check the credentials created for the PDB . If we had 3 credentails they will all be listed

C:\ORACLE_HOME\network\admin> mkstore -wrl . -listCredential
Enter wallet password:
List credential (index: connect_string username)
1: MYPDB sys

5- Add the Wallet location in SQLNET.ora

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

5- Verify the connection

C:\Oracle\> sqlplus /@MYPDB as sysdba 
sys@MYCDB.MYPDB> show con_name 
CON_NAME 
--------- 
MYPDB

Datapump Import Into the PDB

1- Prepare the impdb par file impdp_nonCDBToPDB.par. In my case I imported a 12c Database into a PDB 

USERID="/ as sysdba"
DUMPFILE=expdp.MyNonCDB12c.122021_%U.dmp
LOGFILE=expdp.MyNonCDB12c.122021.log
DIRECTORY=STAGING_DIR

2- Set the ORACLE_SID to the desired CDB  (MYCDB) and the import into the target PDB as sysdba

C:> set ORACLE_SID=MYCDB
C:> impdp parfile=impdp_nonCDBToPDB.par
Job "SYS"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Fri Dec 24 
02:19:14 2021 elapsed 0 12:27:18

Conclusion

This was an example on how to migrate a non CDB database into PDB using a wallet store and DataPump.
It’s the only way in Windows and if you plan on migrating small DBs to multiple PDBs of the same CDB it’s very useful. you can have as many credentials as you need for each PDB .

Thank you for reading