Skip to main content

Posts

Sharding VS Horizontal Partitioning

Sharding Oracle Database

Sharding Oracle DB Source: https://www.oracle.com/technetwork/database/availability/sharding-faq-3610620.pdf • Sharding is for OLTP apps • Shards are distributed (but not necessarily replicated –unless you use Data Guard or GoldenGate for some Oracle instances) partitions (horizontal partitioning). • Shards reside on different Oracle instances, that may be of different releases (as long as the app is backward compatible with orlder Oracle DB versions) • The application believes there is only one Oracle instance: a sharded DB (SDB); but for better performances, it has to specify a shard-key (which is pretty much a partitioning key -e.g. customer ID, account nb , etc.) in the connection-string of the JDBC driver. The JDBC driver or connection pool will use this shard-key to provide a connection object for the relevant shard which contains the data pertinent to the required transaction [1] (i.e. not the whole Oracle instance). Note that there is a shard-routing-cache at the conn...

ORA-00018.Maximum number of sessions exceed

ORA-00018.Maximum number of sessions exceed Initiate the environment variables cat /etc/oratab . oraenv ISTCLOUD Control the number of connections in use loggin as PDBADMIN in your PDB sqlplus PDBADMIN/welcome1@129.152.132.249:1521/SALESPDB4XOK68WUOMMXBE1WPHN.ieoracle12645.oraclecloud.internal Check the number of connections Run: SELECT 'Currently, ' || (SELECT COUNT(*) FROM V$SESSION) || ' out of ' || VP.VALUE || ' connections are used.' AS USAGE_MESSAGE FROM V$PARAMETER VP WHERE VP.NAME = 'sessions' It displays “Currently n out of m connections are used.” Here n > m. It should be the opposite. Change the max number of sessions Loggin as sys in your PDB quit sqlplus / as sysdba alter session set container = SALESPDB4XOK68WUOMMXBE1WPHN; Change the number of sessions alter system set sessions=100 scope=both sid='*'; Then bounce the database Turn it off and on again SQL> shutdown immediate SQL> startup Open all the PDBs (closed by the boun...

Oracle Database commands

Notes Leo Oracle DB Start sqlplus Look for orale_home and SID cat /etc/oratab # # A colon, ':', is used as the field terminator.  A new line terminates # the entry.  Lines beginning with a pound sign, '#', are comments. # # Entries are of the form: #   $ORACLE_SID:$ORACLE_HOME:<N|Y>: # ORCL : /u01/app/oracle /product/12.1.0/dbhome_1:Y Set the env variables: [oracle@LeoFareco opc]$ . oraenv ORACLE_SID = [ ORC L] ? The Oracle base remains unchanged with value /u01/app/oracle Start sqlplus [oracle@LeoFareco opc]$ sqlplus / as SYSDBA Avoid the wrapping of the text SQL> set lines 999; SQL> set pages 999; PDB Create a new PDB from the Seed SQL> CREATE PLUGGABLE DATABASE PDB2 ADMIN USER oracle IDENTIFIED BY oracle; Pluggable database created. List the available PDBs SQL> show pdbs OR SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 READ WRITE PDB2 MOUNTED Open a PDB in read-write mode...