Creating services on standalone database

Creating Services in standalone database:
--------------------------------------------------------------------------

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'account_app',NETWORK_NAME=>'account_app.mydomain.com')

PL/SQL procedure successfully completed.



[oracle@pavanorcl101 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2017 15:25:05

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pavanorcl101.mydomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                01-DEC-2017 15:09:01
Uptime                    0 days 0 hr. 16 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/pavanorcl101/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavanorcl101.mydomain.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=pavanorcl101.mydomain.com)(PORT=8081))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavanorcl101.mydomain.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.mydomain.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.mydomain.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


Start the service:
------------------------------
exec dbms_service.START_SERVICE('account_app')

PL/SQL procedure successfully completed.





[oracle@pavanorcl101 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2017 15:17:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pavanorcl101.mydomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                01-DEC-2017 15:09:01
Uptime                    0 days 0 hr. 8 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/pavanorcl101/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavanorcl101.mydomain.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=pavanorcl101.mydomain.com)(PORT=8081))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pavanorcl101.mydomain.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "account_app.mydomain.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl.mydomain.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.mydomain.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully



Static registration of service with listener:
---------------------------------------------------------------------------
If the service is not register yet, manually Register the services with local_listener by using following command

SQL> alter system set local_listener='account_app' scope=both;




Edit tnsnames.ora

ACCOUNT_APP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVER = DEDICATED)
      (SERVICE_NAME = account_app.mydomain.com)
    )
  )



check the connection from other server:
------------------------------------------------------------------------

D:\app\client\pavan\product\12.1.0\client_1\BIN>sqlplus.exe

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 1 15:21:19 2017

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

Enter user-name: pavan@account_app
Enter password:******
Last Successful login time: Fri Dec 01 2017 15:18:19 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> show user
USER is "PAVAN"
SQL>
SQL> select * from tab;

TNAME
--------------------------------------------------------------------------------
TABTYPE  CLUSTERID
------- ----------
SPM_TEST_TAB
TABLE

ETS_TEST
TABLE

EMPLOYMENT
TABLE

Some other activities:
---------------------------------------

-- Start service
exec DBMS_SERVICE.START_SERVICE('account_app','orcl'); -- with Instance Name  

-- Stop the Service
exec dbms_service.STOP_SERVICE('account_app')

-- Delete Service
exec dbms_service.DELETE_SERVICE(SERVICE_NAME=>'account_app')

-- Disconnect all session related to service
exec DBMS_SERVICE.DISCONNECT_SESSION('account_app')

-- View all services
SELECT name,network_name, creation_date, clb_goal FROM dba_services;

-- View all active services
SELECT service_id, name, network_name FROM gv$active_services;

 -- view all sessions with specific service
select username, service_name from v$session where service_name like 'acc%