How to run Data Pump from a Pluggable Database as SYS when ORACLE_PDB_SID can’t work
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 22.214.171.124.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,
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
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