PDB
Configuring TDE on a PDB with PLUG and UNPLUG Option
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 4 years ago Comments: 0

Configuring TDE on a PDB with PLUG and UNPLUG Option

 
This article describes the process of configure a TDE on CDB and unplugging PDB1 from the CDB1 instance and plugging into the CDB2 instance on the same machine with a new name of PDB2.

 

1 – Configure TDE Source – CDB (cdb1)
A) Create directory

 

[root@vm1 ~]# mkdir -p /etc/ORACLE/WALLETS/cdb1
[root@vm1 ~]# mkdir -p /etc/ORACLE/WALLETS/cdb2
[root@vm1 ~]# chown -R oracle:oinstall /etc/ORACLE
[root@vm1 ~]# chmod -R 755 /etc/ORACLE
B) Edit sqlnet.ora and configure the Wallet

 

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/etc/ORACLE/WALLETS/$ORACLE_SID/)))
C) Configure Database – cdb1

 

[oracle@vm1 ~]$ . oraenv <<< cdb1
ORACLE_SID = [db01] ? The Oracle base remains unchanged with value /u01/app/oracle
[oracle@vm1 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 3 20:58:03 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL>
SQL>
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO


SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/cdb1' IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle container=all;

keystore altered.


SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle WITH BACKUP USING 'emp_key_backup'

keystore altered.


SQL> alter session set container=pdb1;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle WITH BACKUP USING 'emp_key_backup';

keystore altered.

SQL> set linesize 500
SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /etc/ORACLE/WALLETS/cdb1/ OPEN PASSWORD SINGLE NO 0


SQL> create tablespace TESTE
datafile '/u01/app/oracle/oradata/cdb1/pdb1/teste.dbf' size 10m
encryption using 'AES256'
default storage (encrypt);

Tablespace created.

D) Export Key from Source – PDB(pdb1)

 


SQL>ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "oracle" TO '/home/oracle/export.p12' IDENTIFIED BY oracle;

keystore altered.

SQL> !
[oracle@vm1 ~]$ ls -ltr export.p12
-rw-r--r--. 1 oracle oinstall 2612 Sep 3 21:08 export.p12
[oracle@vm1 ~]$ exit
 
E) Unplug and DROP PDB(pdb1)

 

SQL> alter pluggable database close immediate;

Pluggable database altered.

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1 keep datafiles;

Pluggable database dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

2 – Configure TDE Destination CDB(cdb2)

 

[oracle@vm1 ~]$ . oraenv <<< cdb2
ORACLE_SID = [cdb1] ? The Oracle base remains unchanged with value /u01/app/oracle
[oracle@vm1 ~]$ ls /etc/ORACLE/WALLETS/
cdb1 cdb2
[oracle@vm1 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 3 21:13:12 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size 2923872 bytes
Variable Size 452985504 bytes
Database Buffers 771751936 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL>
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/cdb2' IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle WITH BACKUP USING 'emp_key_backup';

keystore altered.

SQL>
SQL>
F) PLUG PDB(pdb2) using Unplugged pdb1

 

SQL> create pluggable database pdb2 as clone using '/home/oracle/pdb1.xml'
2 file_name_convert=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Warning: PDB altered with errors.
G) IMPORT KEY from Source PDB(pdb1) to Destination PDB(pdb2)

 

SQL> alter session set container=pdb2;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "oracle" FROM '/home/oracle/export.p12' IDENTIFIED BY oracle WITH BACKUP;

keystore altered.

SQL> shut immediate;
Pluggable Database closed.
SQL> startup
Pluggable Database opened.

 

H) Validate PDB is fully integrated with CDB2

 

 

SQL> conn / as sysdba
Connected.

SQL> select message,status from pdb_plug_in_violations where status <> 'RESOLVED';
SQL> /

no rows selected

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 READ WRITE NO
SQL>

 

I hope this helps you!!!
 
This article was written by Andre Ontalba e Rodrigo Mufalani
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”


Moving PDB from On-Premise to Cloud
Category: Cloud Author: Carlos Magno de Andrade Junior Date: 5 years ago Comments: 0

Moving PDB from On-Premise to Cloud

Be sure that the BUG Fix is applied on the Source Database:
 
– Be sure that the BUG Fix (18633374) is applied on the Source Database:
– The PSU 12.1.0.2.190416 or higher should be applied also on the source.

 

On-Premise

 

 

1.) Check Status of the Source Database:

SQL> select * from gv$pdbs where con_id=13;

 

   INST_ID     CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

———- ———- ———- ———- ——————————– —————————— ———- — ————————————————————————— ———- ———- ———- ——– ———————-

         1         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ WRITE NO  06-MAY-19 02.03.32.098 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0

         2         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ WRITE NO  06-MAY-19 02.04.32.119 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0

 

2.) GLobal user for DB LInk:

create user c##_link identified by link001#;

grant dba to c##_link container=all;

grant create pluggable database to c##_link container=all;

 

3.) Close the database:

 

SQL> alter pluggable database pdbtest close instances=all;

 

Pluggable database altered.

 

SQL> select * from gv$pdbs where con_id=13;

 

   INST_ID     CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

———- ———- ———- ———- ——————————– —————————— ———- — ————————————————————————— ———- ———- ———- ——– ———————-

         1         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        MOUNTED        06-MAY-19 02.06.27.877 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0

         2         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        MOUNTED        06-MAY-19 02.06.28.166 PM +02:00                                            8042252890  907018240       8192 ENABLED                       0

 

4.) open the Database in Just on Node in READ ONLY MODE:

 

SQL>  alter pluggable database pdbtest open read only;

 

Pluggable database altered.

 

SQL> select * from gv$pdbs where con_id=13;

 

   INST_ID     CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

———- ———- ———- ———- ——————————– —————————— ———- — ————————————————————————— ———- ———- ———- ——– ———————-

         1         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ ONLY  NO  06-MAY-19 02.10.32.513 PM +02:00                                            8042252890  886046720       8192 ENABLED                       0

         2         13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        MOUNTED        06-MAY-19 02.06.28.166 PM +02:00                                            8042252890  886046720       8192 ENABLED                       0

 

CLOUD:

 

5.) Create the TNS Entry on the TNSNAMES.ORA 

 

PDBTEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.165.13)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PDBTEST)

    )

  )

 

[oracle@vits-racdb1 admin]$ tnsping pdbtest

 

TNS Ping Utility for Linux: Version 12.2.0.1.0 – Production on 06-MAY-2019 12:06:29

 

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

 

Used parameter files:

/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.165.13)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDBTEST)))

OK (40 msec)

[oracle@vits-racdb1 admin]$

 

[oracle@vits-racdb1 admin]$ sqlplus c##_link/link001#@pdbtest

 

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 12:07:16 2019

 

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 

Last Successful login time: Tue Apr 02 2019 13:26:37 +00:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

 

SQL>

 

conn / as sysdba

 

6.) Create a database link from CLOUD Database to ON-Premise:

 

create public database link lnk_PDBTEST connect to c##_link identified by link001# using ‘PDBTEST’;

 

SQL> select * from v$pdbs@lnk_PDBTEST;

 

    CON_ID       DBID    CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                                   CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID

———- ———- ———- ——————————– —————————— ———- — ————————————————————————— ———- ———- ———- ——– ———————-

        13  168895093  168895093 8837DFB294B8214DE054020820D8D4F1 PDBTEST                        READ ONLY  NO  06-MAY-19 02.10.32.513 PM +02:00                                            8042252890  886046720       8192 ENABLED                       0

 

7.) Create the new PDB on the Oracle Cloud:

 

SQL> show parameter tables

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

encrypt_new_tablespaces              string      DDL

 

CREATE PLUGGABLE DATABASE PDBTEST FROM PDBTEST@lnk_PDBTEST;

 

SQL> SQL> SQL> SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 CONFLUENCE                     READ WRITE NO

         4 JIRA02                         READ WRITE NO

         5 PITBITBUCKET01                 READ WRITE NO

         6 PDBTEST                        MOUNTED

SQL> alter pluggable database PDBTEST open;

 

Warning: PDB altered with errors.

 

SQL>  show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 CONFLUENCE                     READ WRITE NO

         4 JIRA02                         READ WRITE NO

         5 PITBITBUCKET01                 READ WRITE NO

         6 PDBTEST                        MIGRATE    YES

 

8.) Check the violations

 

*** In case of errors with APEX

cd /u01/oracle/product/12.1.0/dbhome_1/apex

How to Uninstall Oracle HTML DB / Application Express from a 10G/11G Database (Doc ID 558340.1)

Deinstall the APEX from the PDB is is not in use.

 

*** Violations

set lin 1000

set pages 10000

 

select * from  PDB_PLUG_IN_VIOLATIONS where status=’PENDING’ and TYPE=’ERROR’;

 

  1* select * from  PDB_PLUG_IN_VIOLATIONS where status=’PENDING’ and TYPE=’ERROR’

SQL> /

 

TIME                           NAME            CAUSE                          TYPE      ERROR_NUMBER       LINE MESSAGE                                                                                                                  STATUS    ACTION                                                                               CON_ID

—————————— ————— —————————— ——— ———— ———- ———————————————————————————————————————— ——— ——————————————————————————– ———-

06-MAY-19 02.11.16.853559 PM   PDBTEST         VSN not match                  ERROR                0          1 PDB’s version does not match CDB’s version: PDB’s version 12.1.0.2.0. CDB’s version 12.2.0.1.0.                          PENDING   Either upgrade the PDB or reload the components in the PDB.                               6

 

9.) Upgrade the PDB:

 

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -c ‘PDBTEST’ catupgrd.sql

 

sqlplus / as sysdba

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 CONFLUENCE                     READ WRITE NO

         4 JIRA02                         READ WRITE NO

         5 PITBITBUCKET01                 READ WRITE NO

         6 PDBTEST                        MOUNTED

 

alter pluggable database pdbtest open instances=all;

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 CONFLUENCE                     READ WRITE NO

         4 JIRA02                         READ WRITE NO

         5 PITBITBUCKET01                 READ WRITE NO

         6 PDBTEST                        READ WRITE YES

 

exit;

 

10.) Apply Datapatch

 

cd $ORACLE_HOME/OPatch

 

./datapatch -verbose -pdbs PDBTEST

 

sqlplus / as sysdba

 

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 7 14:09:42 2019

 

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 – 64bit Production

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 CONFLUENCE                     READ WRITE NO

         4 JIRA02                         READ WRITE NO

         5 PITBITBUCKET01                 READ WRITE NO

         6 PDBTEST                        READ WRITE YES

 

SQL> alter pluggable database PDBTEST close instances=all;

 

Pluggable database altered.

 

SQL> alter pluggable database PDBTEST open instances=all;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 CONFLUENCE                     READ WRITE NO

         4 JIRA02                         READ WRITE NO

         5 PITBITBUCKET01                 READ WRITE NO

         6 PDBTEST                        READ WRITE NO

 

11.) Update the backup  TDE information for the PDB

 

After create the PDB on the cloud

In order to have the backups running, you must update the TDE Key for the new PDB Created/Attached.

 

DBCS OCI: How to create a new PDB in an OCI CDB? (Doc ID 2438598.1)

 

as root user on the CLoud DB Server:

 

dbcli  list-databases

 

dbcli update-tdekey -i 040e267b-2719-453a-8c86-52844dcd3032 -p -n PDBTEST

 

 

 We appreciate the author Carlos Magno – EzDBA for the contribution.