Oracle Database versions like 12c, 18c, 19c.. etc allow you to create many PDBs within a single CDB. In Oracle cloud infrastructure – OCI you can create new PDB, Clone a PDB, unplug a PDB, delete a PDB and can perform many different operations.
There is only one database allowed to be created in virtual machine database in OCI but this is not in case of bare metal DBCS instance. So in this case you can create multiple PDBs in an OCI CDB hosted on VMDB system.
Recently we had a requirement to create new PDB in an already running container database which is having a two node RAC running on OCI virtual machine and activate the encryption master key for the new PDB.
MUST READ: Database Export Using TDE Encryption in Oracle
How to Create User in PostgreSQL
If you are creating new PDB then you must activate an encryption master key for the PDB. After creating or plugging a new pluggable database in 1 or 2 node RAC, use the dbcli update-tdekey to create and activate a master encryption key for that PDB. Otherwis, you might encounter the error ORA-28374: typed master key not found in the wallet when attempting to create tablespace in PDB. In a multi tenant environment, each PDB has its own encryption master key which is stored in a single key store used by all containers.
Steps to Create New PDB in OCI
STEP 1: Login to the database
[root@ctdb2 ~]# ps -ef|grep pmon
grid 21299 1 0 Oct17 ? 00:04:08 asm_pmon_+ASM2
oracle 32524 1 0 Oct17 ? 00:06:17 ora_pmon_CTDB2
root 51435 49291 0 06:53 pts/0 00:00:00 grep --color=auto pmon
[oracle@ctdb2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@ctdb2 ~]$ export PATH=/u01/app/oracle/product/12.1.0.2/dbhome_1/bin:$PATH:.
[oracle@ctdb2 ~]$ export ORACLE_SID=CTDB2
[oracle@ctdb2 ~]$ sqlplus / as sysdba
STEP 2: Check PDBs
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
--------------------------------------------
2 PDB$SEED READ ONLY NO
3 CTPDB READ WRITE NO
STEP 3: Connect to CDB ROOT container using sqlplus and check the wallet.
SQL> show con_name
CON_NAME
---------
CDB$ROOT
NOTE: Wallet Must be OPEN
SQL> set line 2000
SQL> col WRL_PARAMETER for a60
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------------------------------------------------------------------------------
1 FILE /opt/oracle/dcs/commonstore/wallets/tde/CTDB_phx1pw/ OPEN AUTOLOGIN SINGLE NO 0
2 FILE /opt/oracle/dcs/commonstore/wallets/tde/CTDB_phx1pw/ OPEN AUTOLOGIN SINGLE NO 0
STEP 4: Create New PDB and check the Status of PDB
SQL> CREATE PLUGGABLE DATABASE ctdev ADMIN USER pdbadmin IDENTIFIED BY welcome1;
Pluggable database created.
SQL> col PDB_NAME for a15
SQL> col STATUS for a15
SQL> select PDB_ID,PDB_NAME,STATUS from cdb_pdbs;
PDB_ID PDB_NAME STATUS
------------------------
3 CTPDB NORMAL
2 PDB$SEED NORMAL
4 CTDEV NEW
STEP 5: Open Newly Created PDB and Check the Status
SQL> alter pluggable database ctdev open instances=all;
Pluggable database altered.
SQL> select PDB_ID,PDB_NAME,STATUS from cdb_pdbs;
PDB_ID PDB_NAME STATUS
------------------------
3 CTPDB NORMAL
2 PDB$SEED NORMAL
4 CTDEV NORMAL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
------------------------------------
2 PDB$SEED READ ONLY NO
3 CTPDB READ WRITE NO
4 CTDEV READ WRITE NO
STEP 6: Connect to New PDB and Check the Status of Wallet.
SQL> alter session set container=CTDEV;
Session altered.
SQL> show con_name;
CON_NAME
--------
CTDEV
SQL> set line 200;
SQL> col WRL_PARAMETER for a60;
SQL>
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
FILE /opt/oracle/dcs/commonstore/wallets/tde/CTDB_phx1pw/ OPEN_NO_MASTER_ AUTOLOGIN SINGLE UNDEFINED 0
SQL> exit
[oracle@ctdb2 ~]$ exit
STEP 7: Connect as a root user and Update CLI and Server to avoid any further issues.
[root@ctdb2 ~]# cliadm update-dbcli
Job details
ID: 878176b8-af28-472c-bb10-a11ee9e5417a
Description: DcsCli patching
Status: Created
Created: November 27, 2019 7:02:36 AM UTC
Message: Dcs cli will be updated
Task Name Start Time End Time Status
[root@ctdb2 ~]# dbcli update-server
{
"jobId" : "91005375-1fc8-422e-8bbb-6212c62eb14d",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "November 27, 2019 07:05:15 AM UTC",
"resourceList" : [ ],
"description" : "Server Patching",
"updatedTime" : "November 27, 2019 07:05:15 AM UTC"
}
STEP 8: Check the status of above executed command using dbcli list-jobs
[root@ctdb2 ~]# dbcli list-jobs
ID Description Created Status
16d29939-5e31-4cdc-86f2-7c843ef6a7bf Authentication key update for DCS_ADMIN October 17, 2019 9:59:18 PM UTC Success
8f26ab19-f05a-437f-8466-85040ef30b45 Authentication key update for DCS_ADMIN October 17, 2019 10:02:41 PM UTC Success
7aebd502-f8dc-4570-9422-f47a1fb5db10 Provisioning service creation October 17, 2019 10:05:20 PM UTC Success
9ddffae8-dd98-46a9-8c09-972ba0be204a SSH keys update October 17, 2019 11:01:23 PM UTC Success
ccd258a5-2673-41e8-a7a7-10ea0a4c8596 SSH key delete October 17, 2019 11:03:02 PM UTC Success
e507d241-cb99-4b7c-bd0b-8736ca2a94da SSH key delete October 17, 2019 11:03:03 PM UTC Success
878176b8-af28-472c-bb10-a11ee9e5417a DcsCli patching November 27, 2019 7:02:36 AM UTC Success
91005375-1fc8-422e-8bbb-6212c62eb14d Server Patching November 27, 2019 7:05:15 AM UTC Running
Server Patching it will take 45 min to 1 hr to complete
STEP 9: Check ID of the Database in which PDB is created.
[root@ctdb1 ~]# dbcli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
c34b11f9-a86f-4f1b-94ab-0b41ae64d532 CTDB Rac 12.1.0.2.190716 true Oltp ASM Configured 85c875c9-d6f4-4c5a-b60a-b53c4b64d65f
[root@ctdb1 ~]#
STEP 10: Update Master Key for new PDB. You need to provide SYS Password which is used while creating the database.
[root@ctdb1 ~]# dbcli update-tdekey -i c34b11f9-a86f-4f1b-94ab-0b41ae64d532 -n CTDEV -hp 'Hsgbu123#Hsgbu123#'
{
"jobId" : "7095ed01-18be-4c6c-881f-f19ae442bcf6",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "December 03, 2019 07:40:31 AM UTC",
"resourceList" : [ ],
"description" : "TDE update CTDB",
"updatedTime" : "December 03, 2019 07:40:31 AM UTC"
}
[root@ctdb1 ~]#
[root@ctdb1 ~]#
[root@ctdb1 ~]# dbcli list-jobs
ID Description Created Status
16d29939-5e31-4cdc-86f2-7c843ef6a7bf Authentication key update for DCS_ADMIN October 17, 2019 9:59:18 PM UTC Success
8f26ab19-f05a-437f-8466-85040ef30b45 Authentication key update for DCS_ADMIN October 17, 2019 10:02:41 PM UTC Success
7aebd502-f8dc-4570-9422-f47a1fb5db10 Provisioning service creation October 17, 2019 10:05:20 PM UTC Success
9ddffae8-dd98-46a9-8c09-972ba0be204a SSH keys update October 17, 2019 11:01:23 PM UTC Success
ccd258a5-2673-41e8-a7a7-10ea0a4c8596 SSH key delete October 17, 2019 11:03:02 PM UTC Success
e507d241-cb99-4b7c-bd0b-8736ca2a94da SSH key delete October 17, 2019 11:03:03 PM UTC Success
878176b8-af28-472c-bb10-a11ee9e5417a DcsCli patching November 27, 2019 7:02:36 AM UTC Success
91005375-1fc8-422e-8bbb-6212c62eb14d Server Patching November 27, 2019 7:05:15 AM UTC Success
7095ed01-18be-4c6c-881f-f19ae442bcf6 TDE update CTDB December 3, 2019 7:40:31 AM UTC Success
STEP 11: Connect to PDB and Check the Status of wallet
[oracle@ctdb1 ~]$ sqlplus / as sysdba
SQL> set line 2000
col WRL_PARAMETER for a60
select * from v$encryption_wallet;SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
----------------------------------------------------------------------------
FILE /opt/oracle/dcs/commonstore/wallets/tde/CTDB_phx1pw/ OPEN AUTOLOGIN SINGLE NO 0
SQL> select * from gv$encryption_wallet;
INST_ID WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
1 FILE /opt/oracle/dcs/commonstore/wallets/tde/CTDB_phx1pw/ OPEN AUTOLOGIN SINGLE NO 0
2 FILE /opt/oracle/dcs/commonstore/wallets/tde/CTDB_phx1pw/ OPEN AUTOLOGIN SINGLE NO 0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 CTPDB READ WRITE NO
4 CTDEV READ WRITE NO
SQL> alter session set container=CTDEV;
Session altered.
SQL> set line 2000
col WRL_PARAMETER for a60
SQL> select * from v$encryption_wallet;SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-----------------------------------------------------------------------
FILE /opt/oracle/dcs/commonstore/wallets/tde/CTDB_phx1pw/ OPEN AUTOLOGIN SINGLE NO 0
SQL>
STEP 12: Test – Create Tablespace:
SQL> CREATE TABLESPACE USERS DATAFILE '+DATA' SIZE 250M AUTOEXTEND ON SEGMENT SPACE MANAGEMENT AUTO ;
Tablespace created.
SQL> select tablespace_name,status,encrypted from dba_tablespaces;
TABLESPACE_NAME STATUS ENC
SYSTEM ONLINE NO
SYSAUX ONLINE NO
TEMP ONLINE NO
USERS ONLINE YES
Hope above article help you to create new PDB in OCI CDB which is already running in VMDB RAC.
Important Links: CLI Command Reference: