Multitenant- Create Application pluggable database on Application root Container database

Multitenant- Create Application pluggable database on Application root Container database

• In this Sceanario we will create 2 application pluggable database HR_PDB1 and HR_PDB2

SQL> conn sys/system123@hr_ac as sysdba
Connected.


SQL> CREATE PLUGGABLE DATABASE hr_pdb1 admin user hr_pdb1adm identified by oracle;


Pluggable database created.

SQL> SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 HR_AC                          READ WRITE NO
         8 HR_PDB1                        MOUNTED

SQL> ALTER PLUGGABLE DATABASE hr_pdb1 OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hr_pdb1 SAVE STATE;

Pluggable database altered.

SQL> SELECT STATUS FROM DBA_PDBS WHERE PDB_NAME='HR_PDB1';

STATUS
----------
NORMAL

SQL> ALTER SESSION SET CONTAINER=hr_pdb1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

• We connect to application pdb database and check status of tables that were created at root application container database.  We will share link:::::

SQL> conn hr/oracle@//srv4:1523/hr_pdb1
Connected.

SQL> SELECT TNAME FROM TAB ;

TNAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
WAREHOUSES
EMPLOYEES
PRODUCT_CATEGORIES
PRODUCTS
CUSTOMERS
CONTACTS
ORDERS
ORDER_ITEMS

TNAME
--------------------------------------------------------------------------------
INVENTORIES

12 rows selected.

SQL> SELECT * FROM REGIONS ;

no rows selected

SQL> SELECT SEQUENCE_NAME FROM USER_SEQUENCES ;

SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_73166
ISEQ$$_73171
ISEQ$$_73174
ISEQ$$_73177
ISEQ$$_73180
ISEQ$$_73183
ISEQ$$_73186
ISEQ$$_73189
ISEQ$$_73192

9 rows selected.

• Create another application PDB database hr_pdb2.

SQL> conn sys/system123@hr_ac as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE hr_pdb2 admin user hr_pdb2adm identified by oracle;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE hr_pdb2 OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE hr_pdb2 SAVE STATE;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER=hr_pdb2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION hr_app SYNC;

Pluggable database altered.

SQL> conn hr/oracle@//srv4:1523/hr_pdb2
Connected.
SQL> SELECT TNAME FROM TAB;

TNAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
WAREHOUSES
EMPLOYEES
PRODUCT_CATEGORIES
PRODUCTS
CUSTOMERS
CONTACTS
ORDERS
ORDER_ITEMS

TNAME
--------------------------------------------------------------------------------
INVENTORIES

12 rows selected.

• Check Application container root database 

oracle@srv4:~> sqlplus sys/system123@hr_ac as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 11:46:36 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 HR_AC                          READ WRITE NO
         8 HR_PDB1                        READ WRITE NO
         9 HR_PDB2                        READ WRITE NO

• Check overall status of container database including application root container database with there pdb.

oracle@srv4:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 26 11:46:56 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         4 PLM1                           READ WRITE NO
         5 PDBREF                         READ ONLY  NO
         6 PROXYPDB                       READ WRITE NO
         7 HR_AC                          READ WRITE NO
         8 HR_PDB1                        READ WRITE NO
         9 HR_PDB2                        READ WRITE NO
SQL>