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

Share on:

Intro

datapump

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 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
  • 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

Share on:
Back to Top