ORA-65028 When Creating Pluggable Database Via DBLINK

Symptom:

    We try to take whole data refresh from PROD DB to Stage DB to have more accurate env for developers.  We use dblink to create a pluggable DB from read only PROD DB

SQL>CREATE PLUGGABLE DATABASE TESTPDB FROM NON$CDB@PROD_DBLINK;
ERROR at line 1:
ORA-65028: Unable to open plugin data file at path
ORA-27036: translation error, unable to expand file name
Additional information: 7202
Additional information: 2

In the alert logs, only see
**************************************************************
Undo Create of Pluggable Database TESTPDB with pdb id - 3.
**************************************************************
Deleted Oracle managed file +DATAC1/CASCDBTEST/7000B69622B37FE2E053A27D4598FB7A/DATAFILE/system.552.980410169
ORA-65028 signalled during: CREATE PLUGGABLE DATABASE TESTPDBFROM NON$CDB@PROD_DBLINK...

When you check trace directory of alert log. You will find there are other trace files like diag trc..... which give you more helpful information
There are such error in the diag trc
Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'remote db file read'<='PX Deq: Execute Reply'
     Chain 1 Signature Hash: 0x5c57b818
 [b] Chain 2 Signature: 'PX Deq: Execution Msg'
     Chain 2 Signature Hash: 0x6319d684
 [c] Chain 3 Signature: 'remote db file read'
     Chain 3 Signature Hash: 0x2e6fafb0

So the error is related to DB hang and DB hang is related to parallel execution of creating pdb and remote db file read.

Solution:

     Double check tnsnames.ora are in sync between the rac nodes.
     Shutdown 2nd node and parallel execution as workaround

How to Export/Import Oracle DB Wallet Keys on 12c

Export/Import Keys from non-CDB to PDB

Export keys from Source Database

Connect to Source database
Make sure keystore is open on the Source

select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;

If not Open:    administer key management set keystore open identified by "password";

Make sure WALLET_TYPE is not AUTOLOGIN. If WALLET_TYPE = AUTOLOGIN,

 see steps below to setup an explicit password before export.

NOTE:  This will cause an outage on the source database as you will have to close and re-open the wallet.

If the database was upgraded from 11G, you will need to perform a rekey of the master key first.  

To check if you need to perform a rekey

select creator_dbname, CREATOR_INSTANCE_NAME from v$encryption_keys;

If these columns are NULL, then you will need to rekey

select key_id from v$encryption_keys    (will generally be 1 row)

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP;

select key_id from v$encryption_keys    (you will see an extra row added from when you ran previously)

Export Keystore from Source

ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "password"

TO '/u02/storage/TDE/export2.exp' IDENTIFIED BY password;

Import keys into PDB

Create PDB from non-CDB after the rekey is performed

Copy export file to CDB Host  /u02/storage/PDB

Connect to CDB and make sure wallet is open

select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;

If not Open:    administer key management set keystore open identified by "password";

Connect to PDB and make sure wallet is open

select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;

If not Open:    administer key management set keystore open identified by "password";

Import Keystore

ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "password"

FROM '/u02/storage/PDB/export.exp' IDENTIFIED BY password WITH BACKUP;

select key_id from v$encryption_keys    

Close and Open PDB

Swap Oracle DB Wallet Type from AUTOLOGIN and PASSWORD

Symptom:

     There are 2 wallet types status in oracle DB view v$encryption_wallet.  One is AUTOLOGIN and the other is PASSWORD.

     When you wallet type is AUTOLOGIN, we can't export and import keys in the wallet for migration. So we need to swap wallet to PASSWORD before we export/import keys. 

Solution:

NOTE:  This process will cause an outage on the database as you will have to close and re-open the instances and wallet. (Below are based on 12c DB)

Shutdown Instance 2 of the database if there is one.

SQL> select wrl_parameter, wallet_type from v$encryption_wallet;

WRL_PARAMETER---------------------------------------------
WALLET_TYPE--------------------------------------------------

/etc/oracle/wallets/TDE/TESTDB/

AUTOLOGIN

Shutdown instance 1 of the database

cd /etc/oracle/wallets/TDE/TESTDB/

mv cwallet.sso cwallet.sso.'DATE'

Startup Instance 1 of the database

SQL> select status, wallet_type from v$encyption_wallet;

STATUS------------------------------------------------------------

WALLET_TYPE--------------------------------------------------

CLOSED

UNKNOWN

SQL> administer key management set keystore open identified by "password";

SQL> select status, wallet_type from v$encryption_wallet;

STATUS-------------------------------------------------

WALLET_TYPE--------------------------------------

OPEN

PASSWORD

You can then export/import the Keys on PDB level

To recreate the AUTOLOGIN wallet

administer key management create auto_login keystore from keystore '/etc/oracle/wallets/TDE/TESTDB/' identified by "password";

Restart Instance 2 if there is one

ORA-12154: TNS:could not resolve the connect identifier specified when Exec Rman Duplicate

Symptom:    

The Error details are blow:
We run below on target DB hosts:

RMAN>  connect target sys/****@testdb
connected to target database: testdb (DBID=1516071651)
RMAN>  connect auxiliary sys/*****@testdbaux
connected to auxiliary database: testdbaux(not mounted)
RMAN> DUPLICATE TARGET DATABASE  TO 'testdbaux' FROM ACTIVE DATABASE ;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/30/2018 00:53:16

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/30/2018 00:53:16

ORA-17629: Cannot connect to the remote database server

ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified

ORA-17629: Cannot connect to the remote database server

Diagnosis:

  On Target testdb hosts,   tnsping testdb and testdbaux are both fine.
  sqlplus into each other on both side are working well. tnsnames.ora are set correctly on both side.
  Similar issue recorded on oracle community link

  It turns out rman duplicate will run sessions from testdbaux hosts to connect to testdb target hosts
  It error out as we forgot to add TNS entries in testdbaux DB GRID home tnsnames.ora . The rman duplicate may use Grid home TNS  entries which confuses people

Solution:

   Add same TNS entries for both target and aux  DB GRID home tnsnames.ora

ORA-12170: TNS:Connect timeout occurred When Exec Rman Duplicate

Symptom:

    The Error details are blow:
We run below on target DB hosts:

RMAN>  connect target sys/****@testdb
connected to target database: testdb (DBID=1516071651)
RMAN>  connect auxiliary sys/*****@testdbaux
connected to auxiliary database: testdbaux(not mounted)
RMAN> DUPLICATE TARGET DATABASE  TO 'testdbaux' FROM ACTIVE DATABASE ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/30/2018 01:06:30
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/30/2018 01:06:30
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12170: TNS:Connect timeout occurred
ORA-17629: Cannot connect to the remote database server

Diagnosis:

  On Target testdb hosts,   tnsping testdb and testdbaux are both fine.
  It turns out rman duplicate will run sessions from testdbaux hosts to connect to testdb target hosts
  We open fireports from target hosts to aux hosts only.  But rman duplicate need ports open from aux hosts to targets as well.

Solution:

   Open firewall bi-direction firewall ports between target and aux hosts

Wallet Usage In PDB/CDB of Multitenant Environment

About using TDE in Multitenant environment

• We have a single Keystore (Wallet) owned by the ROOT container (CDB$ROOT) and a separate Master Encryption Key for each of the associated pluggable databases as well as a Master encryption Key for the ROOT (CDB$ROOT) container.
• The keystore in the CDB must be open to allow the keystores in the PDB's to be open.  If you close the keystore in the CDB, all PDB keystores will be closed as well.
• Each PDB must have their own Master Encryption Key
• To import or export the keystore, the wallet must be opened explicitly with a password, and not using auto-login wallet.
• Utilize ADMINISTER KEY MANAGEMENT commands to manage Wallet and Encryption Keys

Create Keystore on CDB

• Create directory /etc/oracle/wallets/TDE/TESTDB
• Update sqlnet.ora
• add ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/etc/test/wallets/TDE/$ORACLE_UNQNAME/)))
• Create keystore
• administer key management create keystore '/etc/test/wallets/TDE/TESTDB/' identified by "password";
• Check status of keystore
• select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;     (status will be CLOSED)
• Open Keystore
• administer key management set keystore open identified by "password";
• Check status of keystore
• select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;    (status will be OPEN_NO_MASTER_KEY)
• Activate the key
• administer key management set key identified by "password" with backup;
• Check status of keystore
• select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;    (status will be OPEN)
• Copy ewallet.p12 to node 2
• Check status of keystore node 2
• select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;    (status should be OPEN.  If not you may need to bounce the CDB)
• 
  

Create Keystore on PDB (no need if you do export/import keys from non-CDB to PDB)

• Connect to PDB
• sqlplus sys@PDB1 as sysdba
• Open Keystore
• administer key management set keystore open identified by "password";
• Check status of keystore
• select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;    (status will be OPEN_NO_MASTER_KEY)
• Activate the key
• administer key management set key identified by "password" with backup;
• Check status of keystore
• select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;    (status will be OPEN)
• Check keystore key status in CDB
• Connect to CDB
• select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
• Copy ewallet.p12 to node 2
• Bounce CDB on node 2 so the PDB's will recognize the new encryption keys
• Connect to CDB on node 2
• select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;