Blogs

How to run Data Pump from a Pluggable Database as SYS when ORACLE_PDB_SID can’t work

Intro

In Oracle multitenant architecture, the 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 to create 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 and the same wallet can be opened by either one. But what’s the difference then?
• Mkstore is older and doesn’t achieve 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 that 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
• The p12 refers to the PKCS12 wallet (Certificate file)=> original encrypted wallet
• The LCK files are there once the wallet is open

4- Check the credentials created for the PDB. If we had 3 credentials 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

6- 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 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 of how to migrate a non-CDB database into PDB using a wallet store and Data Pump.
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!

More from this Author