Friday, April 28, 2017

12c Network isolation to pdbs in Oracle Multitenant architecture

There are Three ways to configure Oracle Container database (CDB) with Pluggable databases (PDBs) for Network isolation.
they are 
1) Using Connection Manager (CMAN) with Access Control rules. ( Oracle Recommended way ) 
2) using 12.2 new feature Access control layer for PDBs.  ( 12.2 New feature ) 
3) One listener per PDB on a distinct port and have firewall white-list for each PDB. ( works in DBaaS in Oracle cloud. But not practical when there are many PDBS.)


1) CMAN : 
Using CMAN is the recommended way. You have a one time configuration of a CMAN server which listens to user connections and forwards to the remote DB listener.
The remote DB listener registers their services when their DBs configure remote_listener parameter in the database.
The Access Control rules in the CMAN configuration file cman.ora  works like a firewall whitelist where you configure ipaddress lists that are allowed to access each service. In 12c, since each PDB is a service, the rules can be used to network-isolate each PDB. This way, each PDB  tennant is completely isolated from the neighboring PDB and cannot access it even if they know username password and connect string.
 Cman lister will listen on a port witn a service_name "cman" registered. CMAN also registers all the PDBs to which connections could be forwarded. This service then forwards the connection to the other registered PDBS if the rule sets are accepted. All DBs that need connection forwarded to them, need to register the services by configuring the remote listeners (remote_listener parameter).

Below is a quick list of steps you need to do to configure a CMAN.

Step a) install CMAN. : you will have to install CMAN from Oracle client Software. Choose custom install and choose Connection manager and sql*net.  You can install CMAN on the same server as CDB or on a remote server.

Step b) configure cman.ora in CMAN_HOME/network/admin directory. 
Below is the example of configuring  cman.ora
cman_test =
(configuration=
    (address=(protocol=tcp)(host=list)(port=1999))
    (parameter_list =
        (aso_authentication_filter=off)
        (connection_statistics=yes)
        (log_directory=/home/oracle/logs)
        (log_level=off)
        (max_connections=256)
        (idle_timeout=0)
        (inbound_connect_timeout=0)
        (session_timeout=0)
        (outbound_connect_timeout=0)
        (max_gateway_processes=16)
        (min_gateway_processes=2)
        (remote_admin=on)
        (trace_directory=/home/oracle/logs)
        (trace_level=admin)
        (trace_timestamp=off)
        (trace_filelen=1000)
        (trace_fileno=1)
        (max_cmctl_sessions=4)
        (event_group=init_and_term,memory_ops)
    )
    (rule_list=
        (rule=
            (src=140.86.39.27)(dst=*)(srv=pdb1)(act=accept)
            (action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
        )
        (rule=(src=list)(dst=127.0.0.1)(srv=cmon)(act=accept))
        (rule=(src=140.86.39.27)(dst=*)(srv=list)(act=accept))
        (rule=(src=140.86.39.63)(dst=*)(srv=pdb2)(act=accept))
    )
)

 This service rule " (rule=(src=<cman_hostname>)(dst=127.0.0.1)(srv=cmon)(act=accept)" is a mandatory rule that will start a cman listener service CMAN on the given port "(address=(protocol=tcp)(host=<cnamHost>)(port=1999)) ". Without this line, you will get errors like 
"(EVENT=CMAN.ORA contains no rule for local CMCTL connection)(Add (rule=(src=list)(dst=127.0.0.1)(srv=cmon)(act=accept)) in rule_list)" 
 Other rules like "(rule=(src=140.86.39.63)(dst=*)(srv=pdb2)(act=accept))" forwards connection requests from ipaddress 140.86.39.63 to pdb2 only, provided that service is registered.

Step c) start CMAN.
Using, CMCTL, connect to configuration profile in cman.ora. The alias in the above example is cman_test.


[oracle@list ~]$ cmctl
CMCTL> ADMINISTER cman_test
CMCTL:cman_test> .

Step d) Register remote PDBs or services 
alter system set remote_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=129.150.66.81)(PORT=1999))';
Note: if you DB listener is on 1521, then the PDBs get autoregistered in local and remote listener.

Step e) connect from client: 
SQL>  conn  system/Saturn_02@//<cman_host>:1999/pdb2
Connected.
or configure tnsname.ora to point to cman host, cman port and service name.
Note, The clients may not have access to the PDBs directly and may not even know the ipaddress. However, as long a the DBs can access the cman host and port, the connection gets forwarded.






2) Database Service Firewall




This option is based new functionality of 12.2 database. It is called Database Service Firewall. 
It is part of Oracle Access Control List. (ACL).   Each policy is represented by an access control list (ACL) containing hosts that are allowed access to a specific database service. Local listeners and server processes validate all inbound client connections against the ACL.

Below are the steps to configure the DB for PDB isolation using  Database service firewall.


Step a)  You need a package  DBMS_SFW_ACL_ADMIN package. This is installed by running as sysdba.  This package is owned by the DBSFWUSER schema. The procedures in this package can be run only by the DBSFWUSER user.

SQL> conn / as sysdba
Connected.

-- Create user and package.
SQL>  @$ORACLE_HOME/rdbms/admin/dbmsaclsrv.sql

Step B)
Configure the listener.ora and set LOCAL_REGISTRATION_ADDRESS_LISTENER and firewall=on. Below is the sample listener.ora with modification in red.


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = CDB4.compute-dbuser19.oraclecloud.internal)(PORT = 1521) (firewall=on))
    )
  )

VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
SSL_VERSION = 1.0

LOCAL_REGISTRATION_ADDRESS_LISTENER= (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

Step C)  Start the listener.
[oracle@CDB4 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 10-MAR-2017 22:35:08

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

Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/CDB4/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CDB4.compute-dbuser19.oraclecloud.internal)(PORT=1521)(FIREWALL=ON)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CDB4.compute-dbuser19.oraclecloud.internal)(PORT=1521)(firewall=on)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                10-MAR-2017 22:35:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/CDB4/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CDB4.compute-dbuser19.oraclecloud.internal)(PORT=1521)(FIREWALL=ON)))
The listener supports no services
The command completed successfully
[oracle@CDB4 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 10 22:36:55 2017

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



Step D) Now setup the local_listener  to point to the listener.  

SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))';

System altered.

SQL> alter system register;

System altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB4PDB1                       READ WRITE NO
         6 MYLOCAL                        READ WRITE NO


Step e)  Note that you will get errors if you do not include the DBSFWUSER. 
Add the ACL service ipaddress for the perticulat PDB and the IP address from which to allow connections.


SQL>  execute   dbms_sfw_acl_admin.ip_add_pdb_ace('CDB4PDB1','140.86.12.219');
BEGIN dbms_sfw_acl_admin.ip_add_pdb_ace('CDB4PDB1','140.86.12.219'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SFW_ACL_ADMIN.IP_ADD_PDB_ACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



SQL> exec DBSFWUSER.DBMS_SFW_ACL_ADMIn.ip_add_pdb_ace('CDB4PDB1','140.86.12.219');

PL/SQL procedure successfully completed.

SQL> exec  DBSFWUSER.dbms_sfw_acl_admin.commit_acl;

PL/SQL procedure successfully completed.


SQL>  exec DBSFWUSER.DBMS_SFW_ACL_ADMIn.ip_add_pdb_ace('CDB4PDB1','140.86.39.63');

PL/SQL procedure successfully completed.

SQL> exec  DBSFWUSER.dbms_sfw_acl_admin.commit_acl;

PL/SQL procedure successfully completed.



SQL> alter system register;

System altered.

SQL> exec DBSFWUSER.DBMS_SFW_ACL_ADMIn.IP_REMOVE_PDB_ACE('CDB4PDB1','140.86.39.63');

PL/SQL procedure successfully completed.

SQL>  alter system register;

System altered.


Step f) Connect from remote client and test. You will either get connected or  access denied error depending on the ACL rule.
select * from DBSFWUSER .ip_acl;

SERVICE_NAME                                 HOST
-------------------------------------------- --------------------------------
"48ADC37AB9052039E0536E99C40AD0FD"           140.86.12.219
"48ADC37AB9052039E0536E99C40AD0FD"           140.86.39.63
"CDB4PDB1"                                   140.86.12.219
"CDB4PDB1"                                   140.86.39.63




SQL> conn system/Saturn_02@//140.86.39.27/CDB4PDB1
Connected.
if the sqlplus is run from a client ip not in ACL, you the the following error.
SQL> conn system/Saturn_02@//140.86.39.27/CDB4PDB1
ERROR:
ORA-46981: Access to service CDB4PDB1 from 140.86.39.63 was denied.


___________________________________________________________________________________________________________________________________________________

3) Multiple Listener 

This is not a Complete Oracle solution, but using a combination of network security and listener configuration, we can achieve network isolation to the PDBs. 
Oracle 12.2 onward, the parameter LOCAL_LISTENERS is modifiable. So  could create one listener per PDB, one listener for CDB on port 1521, and  additional listener per PDB.
All the listeners had each had different names and and ports.
Now you can modify Network firewall rules to allow only certain Ip address per port. It is easier done in Oracle Cloud DBaaS. Here the DB instance access rules can be altered to create ip white lists and accociate the whitelist to a access rule and access rule to the DBaas VM.


While this is not the recommended way, I am only listing this as a option for the sake of documentation and it was the first option i tried out as i had access to DBaaS.


Step 1) create listeners listening for different ports for each PDB.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

LISTENER_PDB1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1531))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1531))
    )
  )


LISTENER_PDB2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1532))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1532))
    )
  )


LISTENER_PDB3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = list.compute-dbuser19.oraclecloud.internal)(PORT = 1533))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1533))
    )
  )


step 2)
    Modify each PDB to listener on a seperate port.

[oracle@list admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 2 22:46:36 2017

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


Connected to:
Oracle Database 12c EE High 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 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 NEWPDBFROMSEED                 READ WRITE NO
         6 PDB3                           READ WRITE NO

SQL> alter session set container=pdb2;

Session altered.

SQL> show parameter local_lis

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
SQL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1532))';

System altered.

SQL> alter system register;

System altered.

SQL> !
[oracle@list admin]$ lsnrctl status listener_pdb2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 02-MAR-2017 22:49:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=list.compute-dbuser19.oraclecloud.internal)(PORT=1532)))
STATUS of the LISTENER
------------------------
Alias                     listener_pdb2
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                02-MAR-2017 22:46:05
Uptime                    0 days 0 hr. 2 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/list/listener_pdb2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=list.compute-dbuser19.oraclecloud.internal)(PORT=1532)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1532)))
Services Summary...
Service "49ada128d2c73b24e053aa6bc40a78d4" has 1 instance(s).
  Instance "list", status READY, has 2 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "list", status READY, has 2 handler(s) for this service...
The command completed successfully
[oracle@list admin]$ exit
exit

Step 3) This is part where i create a separate white-list for each port associated with the DBaaS Vm. I followed the stepts from this site.





Oracle Live SQL .. Test Hard Parse vs ForAll for inserts.

There many be many  times a developer might wonder how something is done in Oracle database or just want to try out some new features.
Also, bloggers would like to provide some sample code to understand coding concept or feature. Now with Oracle liveSQL, we can publish scripts which could be immediately run to see the output.
We can use this site to help Oracle developers be more productive and share scripts and code.
You will need a Oracle account to run scripts.

To kick things off, below is the link to a scripts that inserts 10000 rows one row at a time doing hard parse and then doing bulk bind . The output show that bulk inserts are 10 time faster.



Thursday, November 13, 2014

"CPU :In-memory query" in Oracle 12c

 In 12c EM, we have a  " CPU :in-memory query" displayed in the  graphical report of performance tab. Since Oracle Database In-Memory sessions access only memory, it makes perfect sense to track the CPU utilized for such queries.  I did not see any separate event or  info of how it is derived. 
However, there are new columns called "in_memory_query" and "in_memory_populate" which have values 'Y' and 'N'
I think  "CPU :In-memory query" is derived from the following logic.

 From the v$active_session_history columns session_state, session_type and in_memory_query or in_memory_populate values and its meaning.

      session_state + session_type       + in_inmemory_xxx                                = "Wait-class"
     "ON CPU"        + "FOREGROUND" + in_inmemory_query ='Y'                      =  CPU in-memory query
      "ON CPU"       + "FOREGROUND" + in_inmemory_query ='N'                      =  CPU
      "ON CPU"       + "BACKGROUND" + in_inmemory_populate ='Y'                  =  CPU: in-memory populate
     "ON CPU"        + "BACKGROUND" + in_memory_populate='N'                     =  BCPU /* background CPU */

else
      "WAITING"                                                                                                 = "wait_class value"


the query for the above logic will be something like
 
SELECT sample_time,
DECODE (session_state,'ON CPU',
   DECODE(session_type,'FOREGROUND',
        DECODE( in_inmemory_query,'Y','CPU in-memory query','CPU'),'BACKGROUND',DECODE(in_inmemory_populate,'Y','CPU: in-memory populate','BCPU')),wait_class)
        FROM v$active_session_history order by sample_time desc;

Tweaks for Linux VirtualBox (VBOX) to use SIMD for 12c InMemory.


  Oracle InMemory uses  SSE4.2 extensions to run SIMD.
  However, by default, Virtualbox(VBOX) may not configure this. You can verify if the extensions are enabled
by checking  the flag in cpuinfo as follows in your Linux guest OS

  cat /proc/cpuinfo | grep sse4

If it is not set,  then no rows will be returned.

 you have to do the following tweak to enable it.
1)  Shutdown the guest VM, and the GUI VM manager.
2) cd to VM home directory.
    eg.  cd  D:\software\virtualbox
3) run the following command  Allowing VirtualBox to pass the SSE 4.2 extensions
( supported in VirtualBox starting in Versions 4.3.8)

eg.
D:\software\virtualbox>VBoxManage setextradata "VM name" VBoxInternal/CPUM/SSE4.2 1

Where "VM Name" is the name of your guest Linux VM.

4) start the GUI VManager  and start the guest OS.

5) verify if the  flag is set.
cat /proc/cpuinfo | grep sse4

[oracle@bigdatalite ~]$ cat /proc/cpuinfo | grep sse4
flags        : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl pni ssse3 sse4_2 lahf_lm

To see if the library is linked dynamically , you can run the following 
ps -ef | grep ora_p | awk '{system ( "pmap "$2 " |grep libshpk " ) ; print "-->"$8 }'

[oracle@bigdatalite ~]$  ps -ef | grep ora_p | awk '{system ( "pmap "$2 " |grep libshpk " ) ; print "-->"$8 }'
00007f732897d000   1500K r-x--  /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpksse4212.so
00007f7328af4000   2044K -----  /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpksse4212.so
00007f7328cf3000     72K rw---  /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpksse4212.so
-->ora_pmon_orcl
00007f8e01a71000   1500K r-x--  /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpksse4212.so
00007f8e01be8000   2044K -----  /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpksse4212.so
00007f8e01de7000     72K rw---  /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpksse4212.so

Tuesday, September 30, 2014

12c PDBs and SPFILE

In 12c multi-tenant database, the spfile is for CDB only. A newly created PDB will inherit the initialization values from the container database if they are created from the pdb$seed ( default empty PDB). 
 There are about 184 parameters that can be modified at the PDB level and are stored in the sys table of the PDB. If you altered the parameter in a PDB  and then plugged in to a different Container database, these modified parameters go with the PDB.

The v$system_parameter shows the different configured parameters of all plugged in database. To see all non default parameters, you can run the following query.
select  name,value, con_id from v$system_parameter where con_id!=0;


One interesting fact is, once a parameter has been set, later even if you reset the value equal to container default, the PDB will still maintain the parameter values.

Tuesday, January 21, 2014

Oracle In-database MapReduce in 12c (big data)

There is some interest from the field about what is In-database map-reduce option and why and how it is different than hadoop solution.
I though I will share my thoughts on it.

 In-database map-reduce is an umbrella term that includes two features.
  •             "SQL Map-reduce" or  "SQL pattern matching".
  •              In database container for Hadoop.  to be released in future release. 


  • "SQL MapReduce" : Oracle database 12c introduced a new feature called PATTERN MATCHING using "MATCH_RECOGNIZE" clause in SQL. This is one of the latest ANSI SQL standards proposed and implemented by Oracle. The new sql syntax helps to intuitively solve complex queries that are not easy to implement using 11g analytical functions alone. Some of the use cases are fraud detection, gene sequencing, time series calculation, stock ticker pattern matching . Etc.  I found most of the use case for Hadoop can be done using match_recognize in database on structured data. Since this is just a SQL enhancement , it is there in both Enterprise & Standard Edition database.


  • "In database container for Hadoop  (beta)" : if you have your development team more skilled at Hadoop and not SQL , or want to implement some complex pre-packaged Hadoop algorithms, you could use oracle container for Hadoop (beta). It is a Hadoop prototype APIs  which run within the java virtual machine in the database. It implements Hadoop Java APIs and interfaces with database using parallel table functions to read data in parallel. One interesting fact about parallel table functions is that it can run in parallel across RAC cluster and also can also route data to a specific parallel processes . This functionality is the key in making Hadoop scale across clusters and  this functionality exited in database for over 15 years now.  Advantage of in-database Hadoop  is 
  1.  No need to move data out of database for running Mapreduce functions and hence save time and resources.
  2.  More  real time data could be used.
  3.  Less redundant copies of data and hence better security & less disk space used.
  4.  The servers could be used for not just MapReduce work, but also used to run the database making better resource utilization,
  5. The output of the MapReduce is immediately available for analytic tools and can combine this functionality along with database features like "in-memory option (beta) to get near real time analysis of Big Data. 
  6. Combine db features for security. Backup, auditing, performance with MapReduce. API.
  7. The ability to stream the output of one parallel table function as input to the next parallel table function has an advantage of not needing to maintain any intermediate stages.
  8. Features like graphical, test, spacial and semantic within oracle database can be used for further analysts.
In addition to this, Oracle 12c will support schema less access using JSON protocol. That will help big data use cases of NOSQL to run on data within Oracle database as well.  

Conclusion.
Having these features will help to solve MapReduce challenges when the data is mostly within database and reduce data movement and make better use of available resources.. 
If Most of your data is outside the DB, then sql Connectors for hadoop and Oracle Loader for Hadoop could be used. 


Reference

1) presentation from Kuassi Mensah 

Friday, January 3, 2014

Sql* developer guage sample report

 I had tried to show something like guage in sqlplus using RPAD trick.
Howevere, if you are running the query in sql*developer , using GUAGE will be a good way to display the graph. 

below is the  output in sql*developer and sqlplus.


Sql*Developer OUTPUT
select name,con_id, bufferCache_MB, 'SQLDEV:GAUGE:0:100:0:100:' ||PCT as PCT from
(  SELECT NVL(pdb.name,'CDB') name,b.con_id,
  ROUND(b.subtotal *
  (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size'
  )                        /(1024 *1024)) bufferCache_MB,
  ROUND(b.subtotal         /b.total * 100) PCT,
  rpad('*',ROUND(b.subtotal/b.total * 100),'*') PCT_BARCHART
FROM
  ( SELECT DISTINCT a.*
  FROM
    (SELECT con_id,
      COUNT(*) over (partition BY con_id ) subtotal ,
      COUNT(*) over () total
    FROM sys.gv$bh
    ) a
  )b, v$pdbs pdb
  where b.con_id=pdb.con_id(+)
ORDER BY con_id);
Sqlplus Output 
col PCT_BARCHART format a30
set linesize 200
 col name format a10
 col con_id format 99
 col PCT format 999
    SELECT NVL(pdb.name,'CDB') name,b.con_id,
  ROUND(b.subtotal *  
 (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size'
  )                        /(1024 *1024)) bufferCache_MB,
  ROUND(b.subtotal         /b.total * 100) PCT,
  rpad('*',ROUND(b.subtotal/b.total * 100),'*') PCT_BARCHART
FROM
  ( SELECT DISTINCT a.*
  FROM
    (SELECT con_id,
      COUNT(*) over (partition BY con_id ) subtotal ,
      COUNT(*) over () total
    FROM sys.gv$bh
    ) a
  )b, v$pdbs pdb
  where b.con_id=pdb.con_id(+)
ORDER BY con_id;