Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

12c clone pdb import tde key still open restricted

Marcelo MarquesAug 11 2017 — edited Oct 30 2017

------------------------------------------------------------------------------------

-- PLATFORM

-- OS: Windows Server 2012 R2 Standard

-- DB: Oracle Database Enterprise Edition 12.1.0.2.170531(64bit):25872779 (JUL-2017)

------------------------------------------------------------------------------------

-- see below for more details and opatch lsinventory

-- 1. clone pdb configured with tde in the same cdb

-- 2. cloned pdb opens in restricted mode

-- 3. export source pdb tde key

-- 4. attempt to import tde key into cloned pdb works

-- 5. close and open cloned pdb, still cannot see the key

-- 6. cloned pdb is still restricted

-- 7. cloned pdb retricted cannot be accessed

Have anyone seen this problem? It looks like a bug to me. I'll also create a SR on this. But wanted to check with the community first.

------------------------------------------------------------------------------------

-- TRY TO CLONE THE PDB USING AN AUTO-LOGIN WALLET

-- CHANGE THE CLONE PDB NAME TO PM7PDB

-- TRY TO IMPORT THE KEY IN THE CLONED PDB

------------------------------------------------------------------------------------

ORA>cd c:\temp

ORA>SET ORACLE_SID=MCS

ORA>SQLPLUS /NOLOG

SQL> connect /as sysdba

Connected.

SQL> select instance_name,status,database_status from v$instance;

INSTANCE_NAME STATUS DATABASE_STATUS

---------------- ------------ -----------------

mcs OPEN ACTIVE

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

OPEN AUTOLOGIN

SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;

NAME OPEN_MODE RES

------------------------------ ---------- ---

PDB$SEED READ ONLY NO

PLPDB READ WRITE NO

PMPDB READ WRITE NO

REVPDB READ WRITE NO

WMXPDB READ WRITE NO

SQL> SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;

KEY_ID KEYSTORE_TYPE CREATOR CREATOR_INSTANCE_NAME CREATOR_PDBNAME

------------------------------------------------------------------------------ ----------------- ------------------------------ ------------------------------ ------------------------------

AZ3I0efDkU8XvxNC0b8zvPQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs CDB$ROOT

AW8mqVJU90/kv8tLJWR0ySYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs PMPDB

AUzo+Hz7EU9hv4UPnA5K8PAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs REVPDB

AWogKQrcv09nvxoS2GCbhOMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs PLPDB

ARTBUgggxE+Pv49SeRNEVecAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs WMXPDB

--------------------------------------------------------------------------------

--LET'S CLONE THE PDB: PMPDB -> PM7PDB

--------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> ALTER PLUGGABLE DATABASE pmpdb CLOSE IMMEDIATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pmpdb OPEN READ ONLY;

Pluggable database altered.

SQL> SHOW PARAMETER db_create_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest string +DATA

SQL>

--If need to clone again

--SQL> ALTER PLUGGABLE DATABASE pm7pdb CLOSE;

--SQL> DROP PLUGGABLE DATABASE pm7pdb INCLUDING DATAFILES;

--Clone PDB

SQL> CREATE PLUGGABLE DATABASE pm7pdb FROM pmpdb;

Pluggable database created.

SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;

NAME OPEN_MODE RES

------------------------------ ---------- ---

PDB$SEED READ ONLY NO

PLPDB READ WRITE NO

PM7PDB MOUNTED

PMPDB READ ONLY NO

REVPDB READ WRITE NO

WMXPDB READ WRITE NO

6 rows selected.

SQL> ALTER PLUGGABLE DATABASE pm7pdb OPEN;

Warning: PDB altered with errors.

--------------------------------------------------------------------------------

--alert_mcs.log

--------------------------------------------------------------------------------

ALTER PLUGGABLE DATABASE pm7pdb OPEN

Wed Aug 09 13:52:17 2017

Pluggable database PM7PDB dictionary check beginning

Pluggable Database PM7PDB Dictionary check complete

Database Characterset for PM7PDB is AL32UTF8

Wed Aug 09 13:52:29 2017

***************************************************************

WARNING: Pluggable Database PM7PDB with pdb id - 7 is

altered with errors or warnings. Please look into

PDB_PLUG_IN_VIOLATIONS view for more details.

***************************************************************

Wed Aug 09 13:53:00 2017

Opening pdb PM7PDB (7) with no Resource Manager plan active

Pluggable database PM7PDB opened read write

Completed: ALTER PLUGGABLE DATABASE pm7pdb OPEN

--------------------------------------------------------------------------------

--PDB_PLUG_IN_VIOLATIONS pm7pdb

--------------------------------------------------------------------------------

ALTER SESSION SET CONTAINER = cdb$root;

SELECT *

FROM PDB_PLUG_IN_VIOLATIONS

WHERE NAME = 'PM7PDB'

ORDER BY TYPE;

09-AUG-17 01.52.29.008000000 PM PM7PDB Wallet Key Needed ERROR 0 1 PDB needs to import keys from source. PENDING Import keys from source.

09-AUG-17 01.52.31.508000000 PM PM7PDB Sync Failure ERROR 0 1 "Sync PDB failed with ORA-1918 during 'DROP USER c##alertusr CASCADE'

" PENDING

09-AUG-17 01.52.29.117000000 PM PM7PDB Service Name Conflict WARNING 0 1 Service name or network name of service pmpdbdesk.esri.com in the PDB is invalid or conflicts with an existing service name or network name in the CDB. PENDING Drop the service and recreate it with an appropriate name.

09-AUG-17 01.52.29.133000000 PM PM7PDB Service Name Conflict WARNING 0 2 Service name or network name of service pmpdbags.esri.com in the PDB is invalid or conflicts with an existing service name or network name in the CDB. PENDING Drop the service and recreate it with an appropriate name.

--Service Name

ALTER SESSION SET CONTAINER = pm7pdb;

SHOW CON_NAME

SELECT * FROM ALL_SERVICES;

BEGIN

DBMS_SERVICE.DELETE_SERVICE(

service_name => 'pmpdbdesk.esri.com');

END;

BEGIN

DBMS_SERVICE.DELETE_SERVICE(

service_name => 'pmpdbags.esri.com');

END;

SELECT * FROM ALL_SERVICES;

--------------------------------------------------------------------------------

-- PM7PDB OPENED READ-WRITE BUT RESTRICTED

--------------------------------------------------------------------------------

SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;

NAME OPEN_MODE RES

------------------------------ ---------- ---

PDB$SEED READ ONLY NO

PLPDB READ WRITE NO

PM7PDB READ WRITE YES

PMPDB READ ONLY NO

REVPDB READ WRITE NO

WMXPDB READ WRITE NO

6 rows selected.

--------------------------------------------------------------------------------

-- EXPORT PMPDB KEY

--------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL> ALTER PLUGGABLE DATABASE pmpdb CLOSE IMMEDIATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pmpdb OPEN;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER = pmpdb;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

PMPDB

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=CURRENT;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=CURRENT

ERROR at line 1:

ORA-28417: password-based keystore is not open

SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mcs.exp$admin" TO 'C:\oracle\admin\mcs\wallet\masterkey_pmpdb_exp.bkp' IDENTIFIED BY "mcs$admin";

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mcs.exp$admin" TO 'C:\oracle\admin\mcs\wallet\masterkey_pmpdb_exp.bkp' IDENTIFIED BY "mcs$admin"

ERROR at line 1:

ORA-46658: keystore not open in the container

--------------------------------------------------------------------------------

--SWITCH TO PASSWORD WALLET

--------------------------------------------------------------------------------

--don't need to shutdown/start the database to switch to password wallet

SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL> host dir /b C:\oracle\admin\mcs\wallet\*

cwallet.sso

ewallet.p12

ewallet_2014032716380569_keystore_bkp.p12

ewallet_2014032820112909_masterkey_cdb_backup.p12

ewallet_2014043018012198_keystore_bkp.p12

ewallet_2014050123414576.p12

ewallet_2014050517333329.p12

masterkey_cdb_exp.bkp

masterkey_plpdb_exp.bkp

masterkey_pmpdb_exp.bkp

masterkey_pmpdb_exp.bkp.old

masterkey_revpdb_exp.bkp

masterkey_wmxpdb_exp.bkp

--RENAME FILE C:\oracle\admin\mcs\wallet\cwallet.sso TO cwallet.sso.old

SQL> host rename C:\oracle\admin\mcs\wallet\cwallet.sso cwallet.sso.OLD

SQL> host dir /b C:\oracle\admin\mcs\wallet\*

cwallet.sso.OLD

ewallet.p12

ewallet_2014032716380569_keystore_bkp.p12

ewallet_2014032820112909_masterkey_cdb_backup.p12

ewallet_2014043018012198_keystore_bkp.p12

ewallet_2014050123414576.p12

ewallet_2014050517333329.p12

ewallet_2014050717430565.p12

masterkey_cdb_exp.bkp

masterkey_plpdb_exp.bkp

masterkey_pmpdb_exp.bkp

masterkey_pmpdb_exp.bkp.old

masterkey_revpdb_exp.bkp

masterkey_wmxpdb_exp.bkp

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

OPEN AUTOLOGIN

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE CONTAINER=ALL;

keystore altered.

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

CLOSED UNKNOWN

--OPEN PASSWORD WALLET

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=ALL;

keystore altered.

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

OPEN PASSWORD

--------------------------------------------------------------------------------

-- TRY TO EXPORT THE KEY AGAIN

--------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER = pmpdb;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

PMPDB

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=CURRENT;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=CURRENT

ERROR at line 1:

ORA-28354: Encryption wallet, auto login wallet, or HSM is already open

SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mcs.exp$admin" TO 'C:\oracle\admin\mcs\wallet\masterkey_pmpdb_exp.bkp' IDENTIFIED BY "mcs$admin";

keystore altered.

--------------------------------------------------------------------------------

--KEEP THE PASSWOD WALLET

--TO IMPORT THE KEY IN THE CLONED PDB PM7PDB

--REQUIRES A PASSWORD WALLET ALSO

--------------------------------------------------------------------------------

-- IMPORT THE KEY

--------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER = pm7pdb;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

PM7PDB

SQL> ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "mcs.exp$admin" FROM 'C:\oracle\admin\mcs\wallet\masterkey_pmpdb_exp.bkp' IDENTIFIED BY "mcs$admin" with backup;

keystore altered.

--This works again on this release Aug/09/2017

Oracle Database Enterprise Edition 12.1.0.2.170531(64bit):25872779

--This used to work fine in previou versions of 12.1.0.2 but now it fails, see below!

/* April/19/2017 -

Error report -

SQL Error: ORA-46655: no valid keys in the file from which keys are to be imported

46655. 00000 - "no valid keys in the file from which keys are to be imported"

*Cause: The specified keystore, from which keys are to be imported, did not

contain any valid keys.

*Action: None.

--MOS: ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET fails with ORA-46655: no valid keys in the file from which keys are to be imported (Doc ID 2156693.1)

ALTER SESSION SET CONTAINER = cdb$root;

SHOW CON_NAME;

select * from V$ENCRYPTION_KEYS;

SQL> SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;

no rows selected

--WEIRD THE KEY DOES NOT SHOW FOR PM7PDB, IGNORE AND MOVE FORWARD, THIS MIGHT BE A BUG

--------------------------------------------------------------------------------

-- PM7PDB RESTART

-------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

SQL> ALTER PLUGGABLE DATABASE pm7pdb CLOSE IMMEDIATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pm7pdb OPEN;

--Aug/08/2017 - Warning: PDB altered with errors.

--April/19/2017 - Pluggable database altered.

--SEE THIS, CAN OPEN THE PM7PDB READ-WRITE AND IS NOT RESTRICTED ANY MORE

--WEIRD BECAUSE THE KEY DOES NOT SHOW FOR PM7PDB

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

OPEN PASSWORD

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=ALL;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=ALL

ERROR at line 1:

ORA-28354: Encryption wallet, auto login wallet, or HSM is already open

--Aug/08/2017 -

SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;

NAME OPEN_MODE RES

------------------------------ ---------- ---

PDB$SEED READ ONLY NO

PLPDB READ WRITE NO

PM7PDB READ WRITE YES

PMPDB READ WRITE NO

REVPDB READ WRITE NO

WMXPDB READ WRITE NO

6 rows selected.

--PM7PDB IS READ WRITE BUT RESTRICTED

--April/19/2017 -

SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;

NAME OPEN_MODE RES

------------------------------ ---------- ---

PDB$SEED READ ONLY NO

PLPDB READ WRITE NO

PM7PDB READ WRITE NO

PMPDB READ WRITE NO

REVPDB READ WRITE NO

WMXPDB READ WRITE NO

6 rows selected.

--THIS CONFIRMS, PM7PDB IS READ WRITE AND IS NOT RESTRICTED ANYMORE

--------------------------------------------------------------------------------

--SWITCH TO AUTOLOGIN WALLET

--------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "mcs$admin" CONTAINER=ALL;

keystore altered.

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

CLOSED UNKNOWN

SQL> host dir /b C:\oracle\admin\mcs\wallet\*

cwallet.sso.OLD

ewallet.p12

ewallet_2014032716380569_keystore_bkp.p12

ewallet_2014032820112909_masterkey_cdb_backup.p12

ewallet_2014043018012198_keystore_bkp.p12

ewallet_2014050123414576.p12

ewallet_2014050517333329.p12

masterkey_cdb_exp.bkp

masterkey_plpdb_exp.bkp

masterkey_pmpdb_exp.bkp

masterkey_pmpdb_exp.bkp.old

masterkey_revpdb_exp.bkp

masterkey_wmxpdb_exp.bkp

--RENAME FILE C:\oracle\admin\mcs\wallet\cwallet.sso.OLD TO cwallet.sso

SQL> host rename C:\oracle\admin\mcs\wallet\cwallet.sso.OLD cwallet.sso

SQL> host dir /b C:\oracle\admin\mcs\wallet\*

cwallet.sso.OLD

ewallet.p12

ewallet_2014032716380569_keystore_bkp.p12

ewallet_2014032820112909_masterkey_cdb_backup.p12

ewallet_2014043018012198_keystore_bkp.p12

ewallet_2014050123414576.p12

ewallet_2014050517333329.p12

ewallet_2014050717430565.p12

masterkey_cdb_exp.bkp

masterkey_plpdb_exp.bkp

masterkey_pmpdb_exp.bkp

masterkey_pmpdb_exp.bkp.old

masterkey_revpdb_exp.bkp

masterkey_wmxpdb_exp.bkp

--OPEN AUTOLOGIN WALLET

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "mcs$admin" CONTAINER=ALL;

keystore altered.

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

OPEN PASSWORD <== STILL PASSWORD !!!!!

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "mcs$admin" CONTAINER=ALL;

--ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "mcs1$admin" CONTAINER=ALL;

keystore altered.

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

OPEN AUTOLOGIN <== NOW IS AUTOLOGIN

-- REPEATED THIS A COUPLE TIMES AND ALWAYS SEE THE SAME BEHAVIOR

-- ONLY WHEN TRIED TO CLOSE IT CHANGES BACK TO AUTO-LOGIN

--------------------------------------------------------------------------------

-- CHECK PM7PDB DATA

-------------------------------------------------------------------------------

SQL> ALTER SESSION SET CONTAINER = cdb$root;

Session altered.

SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;

NAME OPEN_MODE RES

------------------------------ ---------- ---

PDB$SEED READ ONLY NO

PLPDB READ WRITE NO

PM7PDB READ WRITE YES

PMPDB READ WRITE NO

REVPDB READ WRITE NO

WMXPDB READ WRITE NO

6 rows selected.

--CHECK THE KEY FOR PM7PDB AGAIN

SQL> ALTER SESSION SET CONTAINER = pm7pdb;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

PM7PDB

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE

------------------------------ --------------------

C:\ORACLE\ADMIN\MCS\WALLET

OPEN_NO_MASTER_KEY AUTOLOGIN

SQL> SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;

no rows selected

---STILL NO KEY !!!!

--VIOLATIONS ARE RESOLVED

ALTER SESSION SET CONTAINER = cdb$root;

SELECT *

FROM PDB_PLUG_IN_VIOLATIONS

WHERE NAME = 'PM7PDB'

ORDER BY TYPE;

09-AUG-17 02.19.26.705000000 PM PM7PDB Wallet Key Needed ERROR 0 1 PDB needs to import keys from source. RESOLVED Import keys from source.

09-AUG-17 02.19.28.033000000 PM PM7PDB Sync Failure ERROR 0 1 "Sync PDB failed with ORA-1918 during 'DROP USER c##alertusr CASCADE'

" PENDING

09-AUG-17 02.19.26.705000000 PM PM7PDB Service Name Conflict WARNING 0 1 Service name or network name of service pmpdbdesk.esri.com in the PDB is invalid or conflicts with an existing service name or network name in the CDB. RESOLVED Drop the service and recreate it with an appropriate name.

09-AUG-17 02.19.26.705000000 PM PM7PDB Service Name Conflict WARNING 0 2 Service name or network name of service pmpdbags.esri.com in the PDB is invalid or conflicts with an existing service name or network name in the CDB. RESOLVED Drop the service and recreate it with an appropriate name.

--TRY TO ACCESS THE DATA

--PMPDB (SOURCE)

SQL> host tnsping mcsdbora2.esri.com:1574/pmpdb.esri.com

SQL> connect sde/sdeadmin@pmcsdbora2.esri.com:1574/pmpdb.esri.com

Connected.

SQL> show user

USER is "SDE"

SQL> show con_name

CON_NAME

------------------------------

PM7PDB

SQL> select * from sde.version;

MAJOR MINOR BUGFIX

---------- ---------- ----------

DESCRIPTION

--------------------------------------------------------------------------------

RELEASE SDESVR_REL_LOW

---------- --------------

10 3 0

10.3 geodatabase

103004 93001

--PM7PDB (CLONE)

SQL> host tnsping mcsdbora2.esri.com:1574/pm7pdb.esri.com

SQL> connect sde/sde.7dmin@mcsdbora2.esri.com:1574/pm7pdb.esri.com

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL> show user

USER is ""

SQL> select * from sde.version;

SP2-0640: Not connected

SQL>

--FAILS TO ACCCESS THE DATA THE PM7PDB IS RESTRICTED

SQL> ALTER PLUGGABLE DATABASE pm7pdb CLOSE IMMEDIATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pm7pdb OPEN;

Warning: PDB altered with errors.

SQL> SELECT name, open_mode, restricted FROM v$pdbs ORDER BY name;

NAME OPEN_MODE RES

------------------------------ ---------- ---

PDB$SEED READ ONLY NO

PLPDB READ WRITE NO

PM7PDB READ WRITE YES

PMPDB READ WRITE NO

REVPDB READ WRITE NO

WMXPDB READ WRITE NO

6 rows selected.

--PM7PDB STILL RESTRICTED

--VIOLATIONS SHOW AS RESOLVED

ALTER SESSION SET CONTAINER = cdb$root;

SELECT *

FROM PDB_PLUG_IN_VIOLATIONS

WHERE NAME = 'PM7PDB'

ORDER BY TYPE;

09-AUG-17 02.19.26.705000000 PM PM7PDB Wallet Key Needed ERROR 0 1 PDB needs to import keys from source. RESOLVED Import keys from source.

09-AUG-17 02.45.11.358000000 PM PM7PDB Sync Failure ERROR 0 1 "Sync PDB failed with ORA-1918 during 'DROP USER c##alertusr CASCADE'

" PENDING

ALTER SESSION SET CONTAINER = cdb$root;

SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;

AZ3I0efDkU8XvxNC0b8zvPQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs CDB$ROOT

AW8mqVJU90/kv8tLJWR0ySYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs PMPDB

AUzo+Hz7EU9hv4UPnA5K8PAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs REVPDB

AWogKQrcv09nvxoS2GCbhOMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs PLPDB

ARTBUgggxE+Pv49SeRNEVecAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE SYS mcs WMXPDB

-- NO KEY SHOWS UP FOR PM7PDB

This post has been answered by Marcelo Marques on Oct 30 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 27 2017

Added on Aug 11 2017

#general-database-discussions

1 comment

4,846 views