Creating and managing Oracle services in single instance Non RAC Systems.

 Creating and managing Oracle services in single instance Non RAC Systems.

#At first lets try to create a service with srvctl :

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

SQL> show parameter uniq

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      orcl

[oracle@node2 ~]$ srvctl status database -d orcl

****Unable to retrieve Oracle Clusterware home.

Start Oracle Clusterware stack and try again.

[oracle@node2 ~]$ srvctl add service -s s1 -d orcl

****Unable to retrieve Oracle Clusterware home.

Start Oracle Clusterware stack and try again.

# This clearly means you cannot manage services in non rac single instance systems with srvctl. The srvctl works with Grid Infrastructure and is the recommended way in Grid Infrastructure. However if you are in non RAC systems then use dbms_service to create and manage services.

Lets create a service now, the version we are using is 21c :

[oracle@node2 dk]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 19 23:31:37 2023

Version 21.3.0.0.0

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

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

    BEGIN

      DBMS_SERVICE.create_service(

        service_name => 'service1',

        network_name => 'service1'

     );

   END;

SQL> /

PL/SQL procedure successfully completed.

# Lets see how to start a service 

SQL> exec dbms_service.start_service('service1');

PL/SQL procedure successfully completed.

# Lets see how to stop a service 

SQL> exec dbms_service.stop_service('service1');

PL/SQL procedure successfully completed.

# Lets see how to delete a service 

SQL> exec dbms_service.delete_service('service1');

BEGIN dbms_service.delete_service('service1'); END;

*

ERROR at line 1:

ORA-44305: service service1 is running

ORA-06512: at "SYS.DBMS_SERVICE", line 68

ORA-06512: at "SYS.DBMS_SERVICE", line 458

ORA-06512: at line 1

# You cannot delete a running service in Oracle.

# So you must stop it first, lets try it

SQL>  exec dbms_service.stop_service('service1');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.delete_service('service1');

PL/SQL procedure successfully completed.

# It works now.

# Lets try to connect now :

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

# I am connected in the CDB$ROOT 

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

# Create a user 

SQL> create user user1 identified by Oracle123mine ;

create user user1 identified by Oracle123mine

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL> create user c##user1 identified by Oracle123mine ;

User created.

SQL> grant connect to c##user1;

Grant succeeded.

[oracle@node2 dk]$ sqlplus c##user1@service1

SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 19 23:36:57 2023

Version 21.3.0.0.0

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

Enter password:

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0