3 Replies Latest reply: Apr 30, 2014 1:36 PM by Marcelo Marques - ESRI RSS

    12c Cloned PDB always open in restricted mode

    Marcelo Marques - ESRI

      OS: Windows Server 2012 R2 Standard

      DB: Oracle Database 12.1.0.5 Enterprise Edition

       

      SQL> ALTER SESSION SET CONTAINER = cdb$root;

      Session altered.

       

      SQL> ALTER PLUGGABLE DATABASE pmpdb CLOSE IMMEDIATE;

      Pluggable database altered.

       

      SQL> ALTER PLUGGABLE DATABASE pmpdb OPEN READ ONLY;

      Pluggable database altered.

       

      SQL> CREATE PLUGGABLE DATABASE pm2pdb FROM pmpdb;

      Pluggable database created.

       

      SQL> ALTER PLUGGABLE DATABASE pm2pdb 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

      PM2PDB                         READ WRITE YES

      PMPDB                          READ ONLY  NO

      REVPDB                         READ WRITE NO

      WMXPDB                         READ WRITE NO

      6 rows selected.

       

      SQL> ALTER PLUGGABLE DATABASE pm2pdb CLOSE IMMEDIATE;

      Pluggable database altered.

       

      SQL> ALTER PLUGGABLE DATABASE pm2pdb OPEN;

      Warning: PDB altered with errors.

       

      SQL> SHOW CON_NAME

      CON_NAME

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

      CDB$ROOT

       

      SQL> ALTER SESSION SET CONTAINER = pmpdb;

      Session altered.

       

      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

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

      AW8mqVJU90/kv8tLJWR0ySYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

      SOFTWARE KEYSTORE SYS                            mcs

      PMPDB

       

      SQL> ALTER SESSION SET CONTAINER = pm2pdb;

      Session altered.

       

      SQL> show con_name

      CON_NAME

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

      PM2PDB

       

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

      no rows selected

       

      SQL> SELECT name,status from v$tempfile where status != 'ONLINE' order by name;

      no rows selected

       

      SQL> SELECT name,status from v$datafile where status NOT IN ('ONLINE','SYSTEM') order by name;

      no rows selected

       

      SQL> ALTER SESSION SET CONTAINER = cdb$root;

      Session altered.

       

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

      WRL_PARAMETER

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

      STATUS                         WALLET_TYPE

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

      C:\ORACLE\ADMIN\MCS\WALLET

      OPEN                           AUTOLOGIN

       

      SQL>

       

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

      alert_mcs.log

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

      ....

      Tue Apr 29 16:32:43 2014

      ALTER PLUGGABLE DATABASE pmpdb CLOSE IMMEDIATE

      Tue Apr 29 16:32:44 2014

      ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local

      Pluggable database PMPDB closed

      Completed: ALTER PLUGGABLE DATABASE pmpdb CLOSE IMMEDIATE

      ALTER PLUGGABLE DATABASE pmpdb OPEN READ ONLY

      Tue Apr 29 16:33:20 2014

      Opening pdb PMPDB (3) with no Resource Manager plan active

      Pluggable database PMPDB opened read only

      Completed: ALTER PLUGGABLE DATABASE pmpdb OPEN READ ONLY

      CREATE PLUGGABLE DATABASE pm2pdb FROM pmpdb

      Tue Apr 29 16:34:41 2014

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

      Pluggable Database PM2PDB with pdb id - 7 is created as UNUSABLE.

      If any errors are encountered before the pdb is marked as NEW,

      then the pdb must be dropped

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

      ....

      Successfully created internal service pm2pdb.esri.com at open

      WARNING: Detected that PDB needs to import keys from source. PDB can only open in restricted mode until import.

      ALTER SYSTEM: Flushing buffer cache inst=0 container=7 local

      Tue Apr 29 16:34:52 2014

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

      Post plug operations are now complete.

      Pluggable database PM2PDB with pdb id - 7 is now marked as NEW.

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

      Completed: CREATE PLUGGABLE DATABASE pm2pdb FROM pmpdb

      ALTER PLUGGABLE DATABASE pm2pdb OPEN

      Tue Apr 29 16:36:05 2014

      Pluggable database PM2PDB dictionary check beginning

      Pluggable Database PM2PDB Dictionary check complete

      Tue Apr 29 16:36:16 2014

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

      XDB installed.

      XDB initialized.

      Tue Apr 29 16:36:31 2014

      Warning: Pluggable database PM2PDB altered with errors

      Pluggable database PM2PDB opened read write

      Completed: ALTER PLUGGABLE DATABASE pm2pdb OPEN

      ALTER PLUGGABLE DATABASE pm2pdb CLOSE IMMEDIATE

      Tue Apr 29 16:37:10 2014

      ALTER SYSTEM: Flushing buffer cache inst=0 container=7 local

      Pluggable database PM2PDB closed

      Completed: ALTER PLUGGABLE DATABASE pm2pdb CLOSE IMMEDIATE

      ALTER PLUGGABLE DATABASE pm2pdb OPEN

      Tue Apr 29 16:37:39 2014

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

      Warning: Pluggable database PM2PDB altered with errors

      Pluggable database PM2PDB opened read write

      Completed: ALTER PLUGGABLE DATABASE pm2pdb OPEN

       

       

      Have you seen this before? Any idea how to fix?

       

      Thanks,

      Marcelo Marques

      Esri Technical Manager, OCP

        • 1. Re: 12c Cloned PDB always open in restricted mode
          rp0428
          Have you seen this before? Any idea how to fix?

          1. No - not that familiar with that exception

          2. Maybe

           

          Some info in the posts suggests that the source PDB uses TDE (transparent data encryption).

           

          Is that true?

           

          Can you post the specifics about that source PDB and the configuration of TDE?

           

          Info in the Advanced security guide shows some prerequisites for exporting/importing data in a TDE system.

          http://docs.oracle.com/cd/E16655_01/network.121/e17729/asotrans_other.htm

           

          I'm wondering if those same prerequisites might apply to clonning TDE based PDBs?

           

          For example, this one:

          Exporting and Importing Tables with Encrypted Columns

          You can export and import tables with encrypted columns using the ENCRYPTION=ENCRYPTED_COLUMNS_ONLY setting.

          1.   Ensure that the keystore is open before you attempt to export tables containing encrypted columns.

           

          In a multitenant environment, if you are exporting data in a pluggable database (PDB), then ensure that the wallet is open in the PDB. If you are exporting into the root, then ensure that the wallet is open in the root.

          To find if the keystore is open, query the STATUS column of the V$ENCRYPTION_WALLET view. If you must open the keystore, then run the following SQL statement:

          ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password [CONTAINER = ALL | CURRENT];  

          Your post showed a query including the wallet type but it didn't show if the keystore itself was open or not.,

           

          Just something to consider.

           

          I also found one 'possibly relevant' mention in another section of that document:

          http://docs.oracle.com/cd/E16655_01/network.121/e17729/asotrans_other.htm#BABJCGDA

          About Exporting and Importing TDE Master Encryption Keys for a PDB

          You can export and import any TDE master encryption key from the root in the same way that you export and import the TDE master encryption key for a non-CDB database.

          You can also export and import all of the TDE master encryption keys that belong to the PDB by exporting and importing the TDE master encryption keys from within a PDB. Export and import of TDE master encryption keys in a PDB supports the PDB unplug and plug operations. During a PDB unplug and plug, all of the TDE master encryption keys that belong to a PDB, as well as the metadata, are involved. Therefore, the WITH IDENTIFIER clause of the ADMINISTER KEY MANAGEMENT EXPORT statement is not allowed when you export keys from within a PDB. The WITH IDENTIFIER clause is only permitted in the root.

          Suppose you want to export TDE master encryption keys in a PDB. You must run the ADMINISTER KEY MANAGEMENT EXPORT statement without the WITH IDENTIFIER clause in the source database, for example:

          ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "my_secret"  TO '/etc/TDE/export.exp'  IDENTIFIED BY password

          This ADMINISTER KEY MANAGEMENT EXPORT operation exports not only the keys but creates metadata that is necessary for PDB environments (as well as for cloning operations).

          I'm not sure what to make of that last sentence 'as well as for cloning operations'.

          • 2. Re: 12c Cloned PDB always open in restricted mode
            Marcelo Marques - ESRI

            I opened an SR with Oracle.

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

            " It sounds similar to bug 15984241.

            Description: After cloning the PDB, the PDB is in opened in the RESTRICTED mode. To allow the PDB to be open in READ WRITE mode the keys have to be

            imported in the PDB which is fails because the source and clone PDBs have different GUIDs.

            Although, this should be fixed."

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

            Now I confirmed that I have to import the master key into the cloned pdb.

             

            -Marcelo

            • 3. Re: 12c Cloned PDB always open in restricted mode
              Marcelo Marques - ESRI

              Fails to import the key

               

              SQL> ALTER SESSION SET CONTAINER = pm2pdb;

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

              ORA-46631: keystore needs to be backed up

               

              I did backup the keystore but it continues to fail with the same error.

              I am working with Oracle Support for resolve the problem.

               

              -Marcelo