DRCP implementation on oracle 12.1 using python & Django framework

DRCP implementation on oracle 12.1 using python

Read documents:
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/OOW11/python_django/python_django.htm
https://code.tutsplus.com/articles/python-from-scratch-create-a-dynamic-website--net-22787

Step 1)
Setup oracle test schema [we pick pythonhol by copying several tables emp,dept,sal,bonus from scott schema]

Step 2) verify the version of python you have - expected version for this tutorial is 2.x - max 2.7


****************** STEPS below are mainly targetted @ WEB application setup using Django framework and Python (cx_oracle module).

step 3) Download and install Django [do this as privileged user - root who has write access to /usr/]

wget http://www.djangoproject.com/download/1.3.1/tarball/
tar xzvf Django-1.3.1.tar.gz
cd Django-1.3.1
python setup.py install

step 4) Test weather the installed Django FW works fine.

python
from django import get_version
get_version()

####################
[root@host Django-1.3.1]# python
Python 2.6.6 (r266:84292, Dec 20 2012, 15:53:42)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from django import get_version
>>> get_version()
'1.3.1'
>>>
#####################

step 5) download and extract and move the directory to ~ ($HOME) as oracle or app user

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/OOW11/python_django/sample/sample.zip

will extract files directory...

[oracle@host dba]$ cd files
[oracle@host files]$ ls -altr
total 44
-rw-rw-r--.  1 oracle oracle 1286 Jul 22  2011 urls.py
-rw-rw-r--.  1 oracle oracle   70 Jul 22  2011 README.txt
-rw-rw-r--.  1 oracle oracle 2041 Jul 22  2011 python_hol.css
-rw-rw-r--.  1 oracle oracle 1094 Jul 22  2011 employee_list.html
-rw-rw-r--.  1 oracle oracle  622 Jul 22  2011 employee_form.html
-rw-rw-r--.  1 oracle oracle  382 Jul 22  2011 employee_confirm_delete.html
-rw-rw-r--.  1 oracle oracle  390 Jul 22  2011 admin.py.2
-rw-rw-r--.  1 oracle oracle  139 Jul 22  2011 admin.py.1
-rw-rw-r--.  1 oracle oracle 1561 Jul 26  2011 models.py
drwxrwxr-x.  2 oracle oracle 4096 Nov 16  2011 .
drwxrwxr-x. 28 oracle oracle 4096 Jan 20 17:00 ..

step 6) start your project, it should create mysite directory in your PWD.

django-admin.py startproject mysite

drwxrwxr-x.  2 oracle oracle 4096 Jan 20 17:00 mysite

[oracle@host files]$ cd mysite
[oracle@host mysite]$ ls -altr
total 24
drwxrwxr-x. 3 oracle oracle 4096 Jan 20 17:00 ..
-rw-r--r--. 1 oracle oracle  565 Jan 20 17:00 urls.py
-rw-r--r--. 1 oracle oracle  503 Jan 20 17:00 manage.py
-rw-r--r--. 1 oracle oracle    0 Jan 20 17:00 __init__.py
drwxrwxr-x. 2 oracle oracle 4096 Jan 20 17:00 .
-rw-r--r--. 1 oracle oracle 5031 Jan 20 17:00 settings.py


step 7) Run server and try access the webpage from http://127.0.0.1:8000/, this should show "It worked" message.

[oracle@host mysite]$ python manage.py runserver
Validating models...

0 errors found
Django version 1.3.1, using settings 'mysite.settings'
Development server is running at http://127.0.0.1:8000/
Quit the server with CONTROL-C.
[20/Jan/2019 03:03:53] "GET / HTTP/1.1" 200 2051
^C[oracle@host mysite]$ ls -altr
total 36


step 8) preserve settings.py and then modify it by introducing the name,username and password field for the database you acess


[oracle@host mysite]$ cp settings.py settings.py_BKP_20jan19
[oracle@host mysite]$ vi settings.py

'ENGINE':
'NAME':
'USER':
'PASSWORD':

step 8) start the application within the webpage. The command below should create a directory in PWD.

python manage.py startapp python_hol

drwxrwxr-x. 2 oracle oracle 4096 Jan 20 17:50 python_hol

[oracle@host python_hol]$ ls -altr
total 20
-rw-r--r--. 1 oracle oracle  383 Jan 20 17:50 tests.py
drwxrwxr-x. 3 oracle oracle 4096 Jan 20 17:50 ..
-rw-r--r--. 1 oracle oracle   26 Jan 20 17:50 views.py
-rw-r--r--. 1 oracle oracle   57 Jan 20 17:50 models.py
-rw-r--r--. 1 oracle oracle    0 Jan 20 17:50 __init__.py
drwxrwxr-x. 2 oracle oracle 4096 Jan 20 17:50 .

step 10) Verify weather cx_oracle is already there by this simple test ot install cx_oracle module.

[oracle@host yum.repos.d]$ python
Python 2.6.6 (r266:84292, Dec 20 2012, 15:53:42)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> db = cx_Oracle.connect("system/****@localhost/<DB_servicename>"
... )
>>> db.version
'12.1.0.1.0'
>>> quit()

if it didnt work.. we need cx_oracle for python version 2.6, which I belive is cx_oracle 5.3

wget https://files.pythonhosted.org/packages/4b/0c/350c738578358ddbb26bc0b3cbef310b677dcca72eb37cd67e29b4e6427c/cx_Oracle-5.3-11g-py26-2.x86_64.rpm --no-check-certificate
--2019-01-20 18:24:08--  https://files.pythonhosted.org/packages/4b/0c/350c738578358ddbb26bc0b3cbef310b677dcca72eb37cd67e29b4e6427c/cx_Oracle-5.3-11g-py26-2.x86_64.rpm
Resolving files.pythonhosted.org... 2a04:4e42:24::319, 151.101.153.63
Connecting to files.pythonhosted.org|2a04:4e42:24::319|:443... connected.
WARNING: certificate common name “r.ssl.fastly.net” doesn’t match requested host name “files.pythonhosted.org”.
HTTP request sent, awaiting response... 200 OK
Length: 167356 (163K) [application/octet-stream]
Saving to: “cx_Oracle-5.3-11g-py26-2.x86_64.rpm”

100%[================================================================================>] 167,356      479K/s   in 0.3s

2019-01-20 18:24:10 (479 KB/s) - “cx_Oracle-5.3-11g-py26-2.x86_64.rpm” saved [167356/167356]

[root@host Sample_App]# ls -altr
total 180
-rw-r--r--.  1 root root 167356 Mar 11  2017 cx_Oracle-5.3-11g-py26-2.x86_64.rpm
-rw-r--r--.  1 root root   4117 Jan 20 16:43 sample.zip
dr-xr-x---. 24 root root   4096 Jan 20 16:45 ..
drwxr-xr-x.  2 root root   4096 Jan 20 18:24 .
[root@host Sample_App]# rpm -ivh cx_Oracle-5.3-11g-py26-2.x86_64.rpm
Preparing...                ########################################### [100%]
   1:cx_Oracle              ########################################### [100%]

************************** Done here with python setups needed  [step 3 to step 8]

step 9) creating the models...

[oracle@host python_hol]$ cat models.py
from django.db import models

# Create your models here.
[oracle@host python_hol]$

../files/mysite

python manage.py inspectdb > python_hol/models.py

================================
[oracle@host mysite]$ python manage.py inspectdb > python_hol/models.py
[oracle@host mysite]$ cat python_hol/models.py|more
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#     * Rearrange models' order
#     * Make sure each model has one field with primary_key=True
# Feel free to rename the models, but don't rename db_table values or field names.
#
# Also note: You'll have to insert the output of 'django-admin.py sqlcustom [appname]'
# into your database.

from django.db import models

class Emp(models.Model):
    empno = models.IntegerField(null=True, blank=True)
    ename = models.CharField(max_length=30, blank=True)
    job = models.CharField(max_length=27, blank=True)
    mgr = models.IntegerField(null=True, blank=True)
    hiredate = models.DateField(null=True, blank=True)
    sal = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    comm = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    deptno = models.IntegerField(null=True, blank=True)
    class Meta:
        db_table = u'emp'

class Dept(models.Model):
    deptno = models.IntegerField(null=True, blank=True)
    dname = models.CharField(max_length=42, blank=True)
    loc = models.CharField(max_length=39, blank=True)
    class Meta:
        db_table = u'dept'

class Salgrade(models.Model):
    grade = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    losal = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    hisal = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    class Meta:
        db_table = u'salgrade'

class Bonus(models.Model):
    ename = models.CharField(max_length=30, blank=True)
    job = models.CharField(max_length=27, blank=True)
    sal = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    comm = models.DecimalField(null=True, max_digits=0, decimal_places=-127, blank=True)
    class Meta:
        db_table = u'bonus'

======model is build


step 10) modify python_hol/models.py to include only necessary models and also add def __str__(self):, which will come be uselful later.

i) REmoved sal and bonus tables from the models.py, since they were causing errors @ syncdb step.

ii) Also changed: [when you bring admin page up if the changed below listed arent done, will cause ORA-00904 emp.ID invalid identifier error ]

    empno = models.IntegerField(null=True, blank=True)
changed to
    empno = models.IntegerField(primary_key=True)        <<< DB isnt really a primary key


    deptno = models.IntegerField(null=True, blank=True)
changed to
    deptno = models.IntegerField(primary_key=True)        <<< DB isnt really a primary key

iii) Adding self return strings, if this isnt done, you will get emp object and dept object as fields getting listed.. some sort meaniless messages.

emp:
    def __str__(self):
        return self.ename

dept:
    def __str__(self):
        return self.dname

=====Post changes the model looks like below...

[oracle@host python_hol]$ cat models.py
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#     * Rearrange models' order
#     * Make sure each model has one field with primary_key=True
# Feel free to rename the models, but don't rename db_table values or field names.
#
# Also note: You'll have to insert the output of 'django-admin.py sqlcustom [appname]'
# into your database.

from django.db import models

class Emp(models.Model):
    empno = models.IntegerField(primary_key=True)
    ename = models.CharField(max_length=30, blank=True)
    job = models.CharField(max_length=27, blank=True)
    mgr = models.IntegerField(null=True, blank=True)
    hiredate = models.DateField(null=True, blank=True)
    sal = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    comm = models.DecimalField(null=True, max_digits=7, decimal_places=2, blank=True)
    deptno = models.IntegerField(null=True, blank=True)
    class Meta:
        db_table = u'emp'
    def __str__(self):
        return self.ename

class Dept(models.Model):
    deptno = models.IntegerField(primary_key=True)
    dname = models.CharField(max_length=42, blank=True)
    loc = models.CharField(max_length=39, blank=True)
    class Meta:
        db_table = u'dept'
    def __str__(self):
        return self.dname
[oracle@host python_hol]$

=============================================

step 11) modify settings.py to include the new app.

mysite.python_hol

example:

INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    # Uncomment the next line to enable the admin:
    # 'django.contrib.admin',
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
    'mysite.python_hol'            <<< newly added entry
)


step 12) modify settings.py to uncomment the admin app

INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    # Uncomment the next line to enable the admin:
    'django.contrib.admin',                    <<<< uncommented here
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
    'mysite.python_hol'
)

step 13) synchronize with the db [here in case you have bonus and sal table you will face errors. Hence please remove them @ step 10]

dir: files/mysite
python manage.py syncdb

=========
[oracle@host mysite]$ python manage.py syncdb
Creating tables ...
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_user_permissions
Creating table auth_user_groups
Creating table auth_user
Creating table auth_message
Creating table django_content_type
Creating table django_session
Creating table django_site
Creating table django_admin_log

You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (Leave blank to use 'oracle'): pythonhol
E-mail address: pythonhol@example.com
Password:
Password (again):
Superuser created successfully.
Installing custom SQL ...
Installing indexes ...
No fixtures found.
==========


SQL> select table_name from dba_tables where owner='PYTHONHOL';

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
DJANGO_ADMIN_LOG                <<< START HERE - newly created table
DJANGO_SITE
DJANGO_SESSION
DJANGO_CONTENT_TYPE
AUTH_MESSAGE
AUTH_USER
AUTH_USER_GROUPS
AUTH_USER_USER_PERMISSIONS
AUTH_GROUP
AUTH_GROUP_PERMISSIONS
AUTH_PERMISSION                <<< END HERE - newly created table
BONUS
SALGRADE
EMP
DEPT


You can see above all the tables that django framework created on the table, please note this tables didnt exist before.

step 14) Update the URL mappings. Edit ../mysite/urls.py. Uncomment the three lines that reference the admin application. This file maps URLs to application pages.

the file to look like below..


[oracle@host mysite]$ cat urls.py
from django.conf.urls.defaults import patterns, include, url

# Uncomment the next two lines to enable the admin:
from django.contrib import admin                <<uncommented
admin.autodiscover()                        <<uncommented

urlpatterns = patterns('',
    # Examples:
    # url(r'^$', 'mysite.views.home', name='home'),
    # url(r'^mysite/', include('mysite.foo.urls')),

    # Uncomment the admin/doc line below to enable admin documentation:
    # url(r'^admin/doc/', include('django.contrib.admindocs.urls')),

    # Uncomment the next line to enable the admin:
    url(r'^admin/', include(admin.site.urls)),            <<uncommented
)

step 15) To enable admin interface for emp and dept table

create file called admin.py under python_hol directory with the below contents

[oracle@host python_hol]$ cat admin.py
from mysite.python_hol.models import Emp, Dept
from django.contrib import admin
admin.site.register(Emp)
admin.site.register(Dept)

step 16) start the webserver

python manage.py runserver

Validating models...

0 errors found
Django version 1.3.1, using settings 'mysite.settings'
Development server is running at http://127.0.0.1:8000/
Quit the server with CONTROL-C.

..it stays here...


step 17) open web browser and go to http://localhost:8000/admin/

Now we are able to browse the employee by their names and dept by their names as well.

step 18) More enhancement to admin page

class EmpAdmin(admin.ModelAdmin):
    list_display = ('ename', 'job', 'hiredate', 'sal', 'comm')
    list_filter = ['hiredate']
    search_fields = ['ename']
    date_hierarchy = 'hiredate'

admin.site.register(Emp, EmpAdmin)


step 19) run the server and check the admin browser, you will see the changes.

************************** Done here with admin build  [step 9 to step 19]

step 20) build a custom page

the new url looks like below..

[oracle@host files]$ cat mysite/urls.py
from django.conf.urls.defaults import *
from mysite.python_hol.models import Emp

# Uncomment the next two lines to enable the admin:
from django.contrib import admin
admin.autodiscover()

info_dict = {
     'queryset': Emp.objects.all(),
}
employee_info = {'model' : Emp}

urlpatterns = patterns('',
     (r'^employees/$', 'django.views.generic.list_detail.object_list',
          dict(info_dict, template_name='employees/employee_list.html')),
     (r'^employees/create/$', 'django.views.generic.create_update.create_object', dict(employee_info,
          template_name='employees/employee_form.html', post_save_redirect='/employees/')),
     (r'^employees/update/(?P<object_id>\d+)/$', 'django.views.generic.create_update.update_object',
          dict(employee_info, template_name='employees/employee_form.html', post_save_redirect='/employees/')),
     (r'^employees/delete/(?P<object_id>\d+)/$', 'django.views.generic.create_update.delete_object',
     dict(employee_info, template_name='employees/employee_confirm_delete.html',
          post_delete_redirect='/employees/')),

     (r'^admin/', include(admin.site.urls)),

     (r'^site_media/(?P<path>.*)$', 'django.views.static.serve',
          {'document_root': '/home/pythonhol/mysite/python_hol/static'}),
)
[oracle@host files]


step 20.a) setup the templates referenced in the above urls.py

mkdir ../files/mysite/python_hol/templates
mkdir ../files/mysite/python_hol/templates/employees

step 21) introduce the template directory in settings.py file

..
TEMPLATE_DIRS = (
"/home/oracle/dba/files/mysite/python_hol/templates"
)
..

step 22) Create a Django template file that lists all employees:

cp ../files/employee_list.html ./files/mysite/python_hol/templates/employees/

edit the template to match the datamodel.column names as per models.py and also change object search from employee to emp

step 23) setup cascading style sheet...

mkdir ../files/mysite/python_hol/static
mkdir ../files/mysite/python_hol/static/css
cp ../files/python_hol.css ../files/mysite/python_hol/static/css


step 24) start the python server

python manage.py runserver

************************** Done here with custom build [step 20 to step 24]


************************** To create/update/delete records on emp table [step 25 to 26]

step 25) now to create/update record place the next 2 tamplate files.
from ../files/
cp employee_form.html mysite/python_hol/templates/employees

Replace all Employee reference by Emp & then change the edit field as appropriate.

step 26) now to delete record place employee_confirm_delete.html to templates directory

No need to edit this page, since it was having the right config.

************************** To start the webserver on different IP and port. [step 27]

step 27) now to start the web on a different ip and port...

python manage.py runserver 192.168.x.x:8000

Validating models...

0 errors found
Django version 1.3.1, using settings 'mysite.settings'
Development server is running at http://192.168.x.x:8000/
Quit the server with CONTROL-C.
[20/Jan/2019 07:46:46] "GET /employees HTTP/1.1" 301 0
[20/Jan/2019 07:46:46] "GET /employees/ HTTP/1.1" 200 4090
[20/Jan/2019 07:46:46] "GET /site_media/css/python_hol.css HTTP/1.1" 404 1796

====Image references

************************** connection pool config below... [step 29 to step 32]
Step 28) Connection Pool configuration

we need to configure django to accept the drcp connections... so wemake changes to settings.py to include the cclass.

[oracle@host mysite]$ more settings.py
# Django settings for mysite project.
import cx_Oracle                <<< import cx_Oracle
DEBUG = True
TEMPLATE_DEBUG = DEBUG

ADMINS = (
    # ('Your Name', 'your_email@example.com'),
)

MANAGERS = ADMINS

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': '<DBNAME>_drcp',                      # Or path to database file if using sqlite3.
        'USER': 'PYTHONHOL',                      # Not used with sqlite3.
        'PASSWORD': 'XXXXXX',                  # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
        'OPTIONS': {
            'purity': cx_Oracle.ATTR_PURITY_SELF,
            'cclass': 'myappname',
        },
  }
}

---no change rest.

step 29) Start the connection pool @ the DB

SQL> execute dbms_connection_pool.start_pool();

PL/SQL procedure successfully completed.

step 30) create the tne entry rmn01tst_drcp

<DBNAME>_drcp=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = <DBNAME>)
    )
  )


step 31) restart the server

python manage.py runserver 192.168.x.x:8000

step 32) verify the connection pool info.


select * from V$CPOOL_CC_INFO;
select * from V$CPOOL_CC_STATS;
select * from V$CPOOL_STATS;
select server,sid,serial#,username,status,state,sql_id,event from v$session order by server;

SQL> select * from V$CPOOL_CC_INFO;

POOL_NAME                                     CCLASS_NAME                        CON_ID
--------------------------------------------- ------------------------------ ----------
SYS_DEFAULT_CONNECTION_POOL                   PYTHONHOL.myappname                     0
SYS_DEFAULT_CONNECTION_POOL                   SYSTEM.SHARED                           0

SQL> select * from V$CPOOL_CC_STATS;

CCLASS_NAME                    NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS  WAIT_TIME CLIENT_REQ_TIMEOUTS NUM_AUTHENTICATIONS     CON_ID
------------------------------ ------------ ---------- ---------- ---------- ---------- ------------------- ------------------- ----------
PYTHONHOL.myappname                       7          3          4          0          0                   0                   7    0        << 3 browser pages, 3 hits.
SYSTEM.SHARED                             2          0          2          0          0                   0                   2    0

SQL> select * from V$CPOOL_STATS;

POOL_NAME                                     NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_AUTH_SERVERS NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS    WAIT_TIME CLIENT_REQ_TIMEOUTS NUM_AUTHENTICATIONS NUM_PURGED HISTORIC_MAX     CON_ID
--------------------------------------------- ---------------- ---------------- ---------------- ------------ ---------- ---------- ---------- ---------- ------------------- ------------------- ---------- ------------ ----------
SYS_DEFAULT_CONNECTION_POOL                                  4                0                0            9          3   6           0          0                   0                   9          0            4          0

SQL> select server,sid,serial#,username,status,state,sql_id,event from v$session order by server;

SERVER           SID    SERIAL# USERNAME   STATUS   STATE               SQL_ID        EVENT
--------- ---------- ---------- ---------- -------- ------------------- ------------- ----------------------------------------------------------------
...
POOLED           280        389 PYTHONHOL  INACTIVE WAITING                           pool server timer

So we see pooled connections.

Thanks for paying the visit.

configure shared server on oracle 12.1 and see how to verify the shared server connection & manage the connections

Lab: configure shared server on oracle 12.1 and see how to verify the shared server connection & manage the connections

Setup:
2 init parameters are involved:
    1) DISPATCHERS [specifies the #of dispatchers to start with, also it is possible to set very granular setting for protocol, address, description with more options],
      & MAX_DISPATCHERS [used for backward compatibility, used to mention max # of dispatcher at any point it time, but it is overridden by dispatchers]
    1) SHARED_SERVER [the min. # of processes to start, 0 or 1 based on DISPATCHER setting, 0 in case no dispatcher]
       & MAX_SHARED_SERVER [the max # of shared servers allowed, if not set, either 1/8 of process if free slots available or 2 in case ,# of process < 24 is maximum allowed]

So to start with let us set, dispatchers alone, since the max clause or somehow controlled by this parameter and also shared_server will become 1 as soon as dispatchers is set and then we can evaluate it how busy the shared server is and how many more we need to start (even it automatically starts as long as we dont set the max clause).

1) preserve the current pfile
2) Evaluate what is already there

dispatcher params right now:

dispatchers                          string      (PROTOCOL=TCP) (SERVICE=rmn01t
                                                 stXDB)
max_dispatchers                      integer

shared_server setting right now:

max_shared_servers                   integer
shared_servers                       integer     1            <<<< observe, here this parameter isnt set in pfile by default, but the shared_server count increased to 1 as mentioned in oracle param guide.


pfile snipet: [observe no shared_server setting]
*.db_recovery_file_dest_size=4800m
*.db_recovery_file_dest='/oradata_1/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XXXXXDB)'
*.memory_target=1188m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.undo_tablespace='UNDOTBS1'

lsnrctl services:

Service "XXXXXDB" has 1 instance(s).
  Instance "XXXX", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host, pid: 2097>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=48278))
The command completed successfully

3) Remove or clear the existing default configuration

SQL> select server,count(1) from v$session group by server;

SERVER      COUNT(1)
--------- ----------
DEDICATED         32

see here, we dont have any existing shared server session, if there was 1, then we will see SHARED or NONE based on weather the session is actively serviced by the shared server.

SQL> alter system set dispatchers='' scope=both;

System altered.

SQL> select * from v$dispatcher;

no rows selected

SQL>

check listener service after a minute you will see the dispatcher is removed already.

A minute before:
Service "XXXXXDB" has 1 instance(s).
  Instance "XXXX", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host, pid: 2097>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=48278))
The command completed successfully

A minute later:
Service "XXXXXDB" has 1 instance(s).
  Instance "XXXX", status READY, has 0 handler(s) for this service...
The command completed successfully

Since the dispatcher wasnt handling any connection (connected clients) actively, the service died otherwise we will have to use the below command to stop the dispatcher...

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D000'; => to stop accepting new connection and kill the existing connection.

shared server is still alive:
00000000AB97C8C0 2099                     S000  oracle@host (S000)        <<< the shared server is still left asis, I think it wont go off until the instance is restarted or explictly we set the parameter to 0 [which will be another simple excercise]

4) Setup the fresh one as per the need

alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=3)' scope=both;

before this command was fired:

Service "XXXX" has 1 instance(s).
  Instance "XXXX", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

after this command was fired:
Service "XXXX" has 1 instance(s).
  Instance "XXXX", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host, pid: 3605>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=40302))
      "D001" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host, pid: 3607>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=46767))
      "D002" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: host, pid: 3609>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605))

select * from v$dispatcher_config;

 CONF_INDX NETWORK                                          DISPATCHERS CONNECTIONS   SESSIONS MULT LISTENER                  SERVICE         CON_ID
---------- ------------------------------------------------ ----------- ----------- ---------- ---- -------------------------------------------------------------------------------- ---------- ----------
         0 (ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))                      3        1022       1022 OFF  (ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))                         XXXX            0


select * from v$dispatcher;

NAME NETWORK                                          PADDR            STATUS           ACC   MESSAGES      BYTES     BREAKS        OWNED    CREATED       IDLE       BUSY   LISTENER  CONF_INDX     CON_ID
---- ------------------------------------------------ ---------------- ---------------- --- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=40302)) 00000000AB97B760 WAIT             YES          0          0          0            0          0     594042          1          0          0          0
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=46767)) 00000000AB994700 WAIT             YES          0          0          0            0          0     594039          0          0          0          0
D002 (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605)) 00000000AB995860 WAIT             YES          0          0          0            0          0     594035          1          0          0          0



dispatchers are now ready and shared server available as well..

SQL> select addr,spid,pname,program from v$process where pname like 'S%' order by pname;

ADDR             SPID                     PNAME PROGRAM
---------------- ------------------------ ----- ------------------------------------------------
00000000AB97C8C0 2099                     S000  oracle@host (S000)
..




5) let us try doing the operation using shared server and query the necessary views

to examine connection/session:
v$circuit
v$session

to examine the common queue - owner of VC & queue statistics:
v$queue
v$reqdist

to examine weather shared server usage is optimal:
v$shared_server
v$shared_server_monitor

Dispatcher monitoring, as per oracle documentation, the work done @ dispatcher is really swift, hence most of the time it is the shared server which struggles.

a) TNS entry with "SERVER=SHARED"

Service "XXXX" has 1 instance(s).
  Instance "XXXX", status READY, has 4 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
..
      "D002" established:1 refused:0 current:1 max:1022 state:ready    <<<<<<<< look D002 has 1 connection established
         DISPATCHER <machine: host, pid: 3609>
         (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=60605))


this is shared connection...

[oracle@host admin]$ sqlplus scott/****@<DB>_shared

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 18 13:47:25 2019        <<< time of shared server

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


this is dedicated connection...
[oracle@host ~]$ sqlplus scott/****@<DB>

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 18 14:14:39 2019        <<< time of dedicated server

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

Last Successful login time: Fri Jan 18 2019 13:47:25 +08:00



See the dedicated process list below..
[oracle@host lab18Jan2019]$ ps -ef|grep -i local=no
oracle    7898     1  0 14:14 ?        00:00:00 oracleXXXX (LOCAL=NO)        <<< time of dedicated server

so no dedicated process spawned for shared server.

Query 1)
col server for a10
col service_name for a12
col username for a9
col program for a45
col module for a15
set lines 300
set pages 3000
alter session set nls_date_Format='DD/MM/YYYY HH24:MI:SS';
select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;

14:14:06 SQL> select server,service_name,sid,serial#,username,program,module,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:15:59   2  14:15:59   3  14:15:59   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,PROGRAM                                      ,MODULE         ,SQL_ID         ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,---------------------------------------------,---------------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,sqlplus@host (TNS V1-V3)         ,SQL*Plus       ,      ,18/01/2019 14:14:40,            ,7896
NONE      ,XXXX    ,        34,       603,SCOTT    ,sqlplus@host (TNS V1-V3)         ,SQL*Plus       ,      ,18/01/2019 13:47:25,            ,7509

14:15:59 SQL>

select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;

14:15:59 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:16:21   2  14:16:21   3  14:16:21   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896
NONE      ,XXXX    ,        34,       603,SCOTT    ,             ,18/01/2019 13:47:25,                   ,7509

14:16:21 SQL>



Notice above the shared server session 34 (SCOTT) - with NONE under server column. It is good that it shows client process ids.

oracle    7509  2762  0 13:47 pts/1    00:00:00 sqlplus    << shared server sqlplus conn
oracle    7896  2888  0 14:14 pts/2    00:00:00 sqlplus    << dedicated process sqlplus conn

let us check now the shared server views....

Query 2) select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;

14:16:21 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;

CIRCUIT         ,DISPATCHER      ,SERVER    ,SADDR           ,STATUS          ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00        ,00000000ABC66DA8,NORMAL          ,NONE


So there is a session address...

select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;

14:18:22 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:18:37   2  14:18:37   3  14:18:37   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
NONE      ,XXXX    ,        34,       603,SCOTT    ,             ,18/01/2019 13:47:25,                   ,7509

14:18:37 SQL>

Being this session is in idle state, the shared server address is "00" and we get to see the circuit and dispatcher addresses fine.

select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
from v$circuit c
,v$session s
,v$process p1
,v$process p2
where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);

14:36:34 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:36:44   2  from v$circuit c
14:36:44   3  ,v$session s
14:36:44   4  ,v$process p1
14:36:44   5  ,v$process p2
14:36:44   6  where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);
14:36:44   7  14:36:44   8
CIRCUIT         ,DISP_,SS_NAME  ,       SID,   SERIAL#,STATUS          ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,NO_SERVER,        34,       603,NORMAL          ,NONE


So this matches with what we observed.

Query 3) examine queue
SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx
AND
  substr(ksppinm,1,8) like '%_shared_%'
ORDER BY ksppinm
/

14:42:14 SQL> /

KSPPINM                            ,KSPPSTVL
-----------------------------------,----------
..
_shared_server_load_balance        ,0
_shared_server_num_queues          ,2            << notice here 2 common queues vs 1 common queue.

11 rows selected.

14:39:36 SQL> select * from v$queue;

PADDR           ,TYPE      ,    QUEUED,      WAIT,    TOTALQ,    CON_ID
----------------,----------,----------,----------,----------,----------
00              ,COMMON    ,         0,         1,         7,         0
00              ,COMMON    ,         0,         0,         7,         0        << 2 common queue
00000000AB97B760,DISPATCHER,         0,         0,         0,         0
00000000AB994700,DISPATCHER,         0,         0,         0,         0
00000000AB995860,DISPATCHER,         0,         1,        14,         0        << 3 dispatcher queues

select
        p.pname,
        q.type,
        q.totalq,
        q.wait,
        round(q.wait/nullif(q.totalq,0),2)  avg_wait
from
        v$queue q,
        v$process p
where
        p.addr(+) = q.paddr
/

PNAME,TYPE      ,    TOTALQ,      WAIT,  AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER,         0,         0,
D001 ,DISPATCHER,         0,         0,
D002 ,DISPATCHER,        14,         1,       .07
     ,COMMON    ,         7,         0,         0
     ,COMMON    ,         7,         1,       .14

So there are 2 common queue and 3 dispatcher queue. The session is no queue now, staying idle.

Load the server process now...

declare
a varchar2(100);
cursor c1 is
select * from dba_objects;
begin
for i in 1..20
loop
for c1_rec in c1
loop
a:=c1_rec.owner;
end loop;
dbms_output.put_line('exited '||i||' loop');
end loop;
end;
/


With server loaded:

14:52:15 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:52:26   2  14:52:26   3  14:52:26   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896
SHARED    ,XXXX    ,        34,       603,SCOTT    ,7xr6jha03c1wx,18/01/2019 13:47:25,18/01/2019 14:52:25,7509        <<< NONE to SHARED

14:52:27 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;

CIRCUIT         ,DISPATCHER      ,SERVER    ,SADDR           ,STATUS          ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00000000AB,00000000ABC66DA8,NORMAL          ,SERVER            <<< SERVER QUEUE from NONE
                ,                ,97C8C0    ,                ,                ,


14:52:36 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:52:45   2  14:52:45   3  14:52:45   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED    ,XXXX    ,        34,       603,SCOTT    ,7xr6jha03c1wx,18/01/2019 13:47:25,18/01/2019 14:52:41,7509

14:52:45 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:53:01   2  from v$circuit c
14:53:01   3  ,v$session s
14:53:01   4  ,v$process p1
,v$process p2
14:53:01   5  14:53:01   6  where c.saddr=s.saddr
14:53:01   7  and c.dispatcher=p1.addr
14:53:01   8  and c.server=p2.addr(+);

CIRCUIT         ,DISP_,SS_NAME  ,       SID,   SERIAL#,STATUS          ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,S000     ,        34,       603,NORMAL          ,SERVER        <<<<< S000 shared server is processing the request

14:53:01 SQL> select * from v$queue;

PADDR           ,TYPE      ,    QUEUED,      WAIT,    TOTALQ,    CON_ID
----------------,----------,----------,----------,----------,----------
00              ,COMMON    ,         0,         1,         7,         0
00              ,COMMON    ,         0,         0,         8,         0
00000000AB97B760,DISPATCHER,         0,         0,         0,         0
00000000AB994700,DISPATCHER,         0,         0,         0,         0
00000000AB995860,DISPATCHER,         0,         1,        14,         0

14:53:31 SQL> select
        p.pname,
        q.type,
        q.totalq,
        q.wait,
        round(q.wait/nullif(q.totalq,0),2)  avg_wait
from
        v$queue q,
        v$process p
where
        p.addr(+) = q.paddr
/14:53:43   2  14:53:43   3  14:53:43   4  14:53:43   5  14:53:43   6  14:53:43   7  14:53:43   8  14:53:43   9  14:53:43  10  14:53:43  11  14:53:43  12

PNAME,TYPE      ,    TOTALQ,      WAIT,  AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER,         0,         0,
D001 ,DISPATCHER,         0,         0,
D002 ,DISPATCHER,        14,         1,       .07
     ,COMMON    ,         8,         0,         0
     ,COMMON    ,         7,         1,       .14

14:53:43 SQL>

Now let us gracefully exit the session.
-----
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
-----

14:53:43 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;14:56:27   2  14:56:27   3  14:56:27   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896

14:56:27 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;

CIRCUIT         ,DISPATCHER      ,SERVER    ,SADDR           ,STATUS          ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB995860,00        ,00              ,NORMAL          ,NONE                    << CIRCUIT stays but there are no session user connection/session associated

14:56:37 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;14:56:50   2  14:56:50   3  14:56:50   4

no rows selected

14:56:50 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
14:57:01   2  from v$circuit c
14:57:01   3  ,v$session s
14:57:01   4  ,v$process p1
14:57:01   5  ,v$process p2
14:57:01   6  where c.saddr=s.saddr
14:57:01   7  and c.dispatcher=p1.addr
and c.server=p2.addr(+);14:57:01   8

no rows selected

14:57:02 SQL> select * from v$queue;

PADDR           ,TYPE      ,    QUEUED,      WAIT,    TOTALQ,    CON_ID
----------------,----------,----------,----------,----------,----------
00              ,COMMON    ,         0,         1,         8,         0
00              ,COMMON    ,         0,         0,         8,         0
00000000AB97B760,DISPATCHER,         0,         0,         0,         0
00000000AB994700,DISPATCHER,         0,         0,         0,         0
00000000AB995860,DISPATCHER,         0,         1,        16,         0

14:57:09 SQL> select
        p.pname,
        q.type,
        q.totalq,
        q.wait,
        round(q.wait/nullif(q.totalq,0),2)  avg_wait
from
        v$queue q,
        v$process p
where
        p.addr(+) = q.paddr
/
14:57:21   2  14:57:21   3  14:57:21   4  14:57:21   5  14:57:21   6  14:57:21   7  14:57:21   8  14:57:21   9  14:57:21  10  14:57:21  11  14:57:21  12
PNAME,TYPE      ,    TOTALQ,      WAIT,  AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER,         0,         0,
D001 ,DISPATCHER,         0,         0,
D002 ,DISPATCHER,        16,         1,       .06
     ,COMMON    ,         8,         0,         0
     ,COMMON    ,         8,         1,       .13

14:57:21 SQL>
14:57:21 SQL>


Let us try to kill a session...

Load the server process now... to buy more time the routine is increased to 50

declare
a varchar2(100);
cursor c1 is
select * from dba_objects;
begin
for i in 1..50
loop
for c1_rec in c1
loop
a:=c1_rec.owner;
end loop;
dbms_output.put_line('exited '||i||' loop');
end loop;
end;
/


before kill....

14:57:21 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;15:01:27   2  15:01:27   3  15:01:27   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896
SHARED    ,XXXX    ,        30,        77,SCOTT    ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:01:26,8477

15:01:27 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;15:01:37   2  15:01:37   3  15:01:37   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED    ,XXXX    ,        30,        77,SCOTT    ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:01:34,8477

15:01:37 SQL> select CIRCUIT,DISPATCHER,SERVER,SADDR,STATUS,QUEUE from v$circuit;

CIRCUIT         ,DISPATCHER      ,SERVER    ,SADDR           ,STATUS          ,QUEUE
----------------,----------------,----------,----------------,----------------,----------------
00000000A3A5A4F8,00000000AB97B760,00000000AB,00000000ABC6ED48,NORMAL          ,SERVER
                ,                ,97C8C0    ,                ,                ,


15:01:43 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
15:01:50   2  from v$circuit c
15:01:50   3  ,v$session s
15:01:50   4  ,v$process p1
15:01:50   5  ,v$process p2
where c.saddr=s.saddr
15:01:50   6  15:01:50   7  and c.dispatcher=p1.addr
15:01:50   8  and c.server=p2.addr(+);

CIRCUIT         ,DISP_,SS_NAME  ,       SID,   SERIAL#,STATUS          ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D000 ,S000     ,        30,        77,NORMAL          ,SERVER

15:01:51 SQL> select * from v$queue;

PADDR           ,TYPE      ,    QUEUED,      WAIT,    TOTALQ,    CON_ID
----------------,----------,----------,----------,----------,----------
00              ,COMMON    ,         0,         1,        22,         0
00              ,COMMON    ,         0,         0,        24,         0
00000000AB97B760,DISPATCHER,         0,         0,        14,         0
00000000AB994700,DISPATCHER,         0,         0,        15,         0
00000000AB995860,DISPATCHER,         0,         1,        16,         0

15:02:00 SQL> select
        p.pname,
        q.type,
        q.totalq,
        q.wait,
        round(q.wait/nullif(q.totalq,0),2)  avg_wait
from
        v$queue q,
        v$process p
where
        p.addr(+) = q.paddr
/
15:02:20   2  15:02:20   3  15:02:20   4  15:02:20   5  15:02:20   6  15:02:20   7  15:02:20   8  15:02:20   9  15:02:20  10  15:02:20  11  15:02:20  12
PNAME,TYPE      ,    TOTALQ,      WAIT,  AVG_WAIT
-----,----------,----------,----------,----------
D000 ,DISPATCHER,        14,         0,         0
D001 ,DISPATCHER,        15,         0,         0
D002 ,DISPATCHER,        16,         1,       .06
     ,COMMON    ,        24,         0,         0
     ,COMMON    ,        22,         1,       .05


so our target is sid 30

alter system kill session '30,77' immediate;

it took few secs before the session is cleared...

15:04:10 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process
from v$session
where username='SCOTT'
order by server;15:04:40   2  15:04:40   3  15:04:40   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896
SHARED    ,XXXX    ,        30,        77,SCOTT    ,7xr6jha03c1wx,18/01/2019 15:00:27,18/01/2019 15:04:36,8477

15:04:40 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
15:05:06   2  from v$session
where username='SCOTT'
order by server;15:05:07   3  15:05:07   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS          ,STATUS  ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896             ,INACTIVE,WAITING

15:05:07 SQL>


declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2099
Session ID: 30 Serial number: 77

alter system kill session '30,81' immediate;


15:06:09 SQL> alter system kill session '30,81' immediate;

System altered.


15:07:32 SQL> /                <<< SEE @ 15:07 it still doing its job

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS          ,STATUS  ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896             ,INACTIVE,WAITING
SHARED    ,XXXX    ,        30,        81,SCOTT    ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:07:28,8477             ,ACTIVE  ,WAITED SHORT TIME

15:07:33 SQL> select s.server,s.service_name,s.sid,s.serial#,s.username,s.sql_id,s.logon_time,s.sql_exec_start,s.process
from v$session s,v$circuit c
where c.saddr=s.saddr
order by server;15:08:15   2  15:08:15   3  15:08:15   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------
SHARED    ,XXXX    ,        30,        81,SCOTT    ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:08:13,8477

15:08:16 SQL> select c.circuit,p1.pname disp_name, (case utl_raw.length(c.server) when 1 then 'NO_SERVER' else p2.pname end) ss_name,s.sid,s.serial#,c.status,c.queue
15:08:38   2  from v$circuit c
15:08:38   3  ,v$session s
,v$process p1
15:08:38   4  15:08:38   5  ,v$process p2
15:08:38   6  where c.saddr=s.saddr
and c.dispatcher=p1.addr
and c.server=p2.addr(+);15:08:38   7  15:08:38   8

CIRCUIT         ,DISP_,SS_NAME  ,       SID,   SERIAL#,STATUS          ,QUEUE
----------------,-----,---------,----------,----------,----------------,----------------
00000000A3A5A4F8,D002 ,S000     ,        30,        81,EOF             ,SERVER            << the status show EOF - End of File communication, but the shared server is still doing the job!!!!!!!

15:08:38 SQL>

select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
from v$session
where username='SCOTT'
order by server;

15:10:01 SQL> select server,service_name,sid,serial#,username,sql_id,logon_time,sql_exec_start,process,status,state
from v$session
where username='SCOTT'
order by server;15:10:24   2  15:10:24   3  15:10:24   4

SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS          ,STATUS  ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896             ,INACTIVE,WAITING
SHARED    ,XXXX    ,        30,        81,SCOTT    ,7xr6jha03c1wx,18/01/2019 15:05:53,18/01/2019 15:10:21,8477             ,ACTIVE  ,WAITED SHORT TIME

15:10:25 SQL>

Still active and my sqlplus session still alive!!!!!!


SERVER    ,SERVICE_NAME,       SID,   SERIAL#,USERNAME ,SQL_ID       ,LOGON_TIME         ,SQL_EXEC_START     ,PROCESS          ,STATUS  ,STATE
----------,------------,----------,----------,---------,-------------,-------------------,-------------------,------------------------,--------,-------------------
DEDICATED ,XXXX    ,        40,       657,SCOTT    ,             ,18/01/2019 14:14:40,                   ,7896             ,INACTIVE,WAITING

15:11:00 SQL>

declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2099
Session ID: 30 Serial number: 81



Finally @ 15:11 it is removed, it took nearly 5 mins to clear the session.



Thanks - here we close this lab excercise. Next time will see how does shared server impact memory.

Notes to perform the GI Home cloning - Cloning clusterware home (11.2)

1) make a successful CRS home install once [an existing clusterware it can be, actively in use]
2) Apply necessary PSU/oneoffs
3) Shutdown the CRS stack
4) copy the working GI home to a new location using cp -prf
    cp -prf Grid_home copy_path
5) Delete unneccessary files

[root@node1 root]# cd copy_path
[root@node1 grid]# rm -rf crs/install/crsconfig_addparams
[root@node1 grid]# rm -rf host_name
[root@node1 grid]# rm -rf log/host_name
[root@node1 grid]# rm -rf gpnp/host_name
[root@node1 grid]# find gpnp -type f -exec rm -f {} \;
[root@node1 grid]# find cfgtoollogs -type f -exec rm -f {} \;
[root@node1 grid]# rm -rf crs/init/*
[root@node1 grid]# rm -rf cdata/*
[root@node1 grid]# rm -rf crf/*
[root@node1 grid]# rm -rf network/admin/*.ora
[root@node1 grid]# find . -name '*.ouibak' -exec rm {} \;
[root@node1 grid]# find . -name '*.ouibak.1' -exec rm {} \;
[root@node1 grid]# rm -rf root.sh*

(there is an option to exclude the files as well, but that seems missing few files in the oracle doc)

6) create a compressed copy of the previously copied GI home using tar or gzip. Ensure to use right tool to protect the permissons.

cd copy_path
tar -zcvpf /copy_path/gridHome.tgz .

7) Prepare target cluster nodes by following the prereqs.

8) Copy and unzip (from the target grid home location) the compressed binary copy we secured in the previous step using tar command.

[root@node1 root]# mkdir -p location_of_the_copy_of_the_Grid_home
[root@node1 root]# cd location_of_the_copy_of_the_Grid_home
[root@node1 crs]# tar -zxvf /path_name/gridHome.tgz

9) create the oracle inventory directory and change the permisson for the GI Home mountpoint as needed.

[root@node1 crs]# mkdir /u01
[root@node1 crs]# chown oracle:oinstall /u01
[root@node1 crs]# mkdir -p /u01/app/oraInventory
[root@node1 crs]# mkdir -p /u01/app/11.2.0/grid

On the above command being launched the below file's permisson is lost...

Grid_home/bin/extjob
Grid_home/bin/jssu
Grid_home/bin/oradism

Run the following commands to restore the cleared information:

chmod u+s Grid_home/bin/oracle
chmod g+s Grid_home/bin/oracle
chmod u+s Grid_home/bin/extjob
chmod u+s Grid_home/bin/jssu
chmod u+s Grid_home/bin/oradism

10) Remove any unncessary files we didnt remove during the compressed tarball preperation.

11) Launch the clone.pl on each node [from Grid_home/clone/bin] using [variables prepopulated] start.sh script [ensure you only run orainstRoot.sh and avoid running

root.sh]

===

cd Grid_home/clone/bin
>>> start.sh content below..

#!/bin/sh
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/11.2.0/grid
THIS_NODE=&rsquor;hostname -s&rsquor;

E01=ORACLE_BASE=${ORACLE_BASE}
E02=ORACLE_HOME=${GRID_HOME}
E03=ORACLE_HOME_NAME=OraGridHome1
E04=INVENTORY_LOCATION=${ORACLE_BASE}/oraInventory

#C00="-O'-debug'"
C01="-O'\"CLUSTER_NODES={node1,node2}\"'"
C02="-O'\"LOCAL_NODE=${THIS_NODE}\"'"

perl ${GRID_HOME}/clone/bin/clone.pl -silent $E01 $E02 $E03 $E04 $C01 $C02
===

12) Launch the config wizard to
    a) prepare the crsconfig_params file,
    b) prompts you to run the root.sh script (which calls the rootcrs.pl script),
    c) relinks Oracle binaries and
    d) runs cluster verifications.

$Oracle_home/crs/config/config.sh

==================================This ends the oracle GI Home cloning to new cluster procedure.

For add node also oracle has outlined the cloning procedure, but there is a catch with rootcrs.pl

Document refered: https://docs.oracle.com/cd/E11882_01/rac.112/e41959/clonecluster.htm#CWADD92359