Continuing with the 12.1.0.2 non-RAC MultiTenant environment and two services demonstrated earlier,
I have restarted the environment today :
The two manually created services (NEW_APP1 and NEW_APP2) don't startup automatically.
When I attempted to start the custom service (that, per the previous post, was created in PDB1) when still connected to CDB$ROOT, the command fails. I had to connect to PDB1 to start the service.
The newly created user HEMANT belongs to the Database. The user is not tied to a Service. He can use either Service (whichever is running) to connect to the Database. But the two Services have two different TNSNAMES.ORA entries --- differing by the SERVICE_NAME specification.
Thus, the DBA could configure some application servers to use one service name and other application servers to use another service name.
An example of such a configuration is where the first set of application servers could be for Finance Applications called "FINANCE" and the second set of servers could be for HR Applications (in the *same* database) called "HR". Here I create the two services but start only the FINANCE service.
Now the user should be able to connect to FINANCE, but not to HR.
The user could connect to FINANCE but the request to HR returned ORA-12514.
(Notice how "tnsping HR" is successful but the connection is not ? That is because tnsping only tests if the listener is running, it does not test if the database instance and service are both running).
So, using multiple services, the DBA can "provision" the same database to multiple applications. The entry-point is the SERVICE_NAME, not the USERNAME. Users and Services are independent of each other.
.
.
.
I have restarted the environment today :
[grid@ora12102 ~]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 22:57:17
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 22:57:31 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 1040191008 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database open;
alter pluggable database open
*
ERROR at line 1:
ORA-65000: missing or invalid pluggable database name
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL>
grid@ora12102 ~]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:00:11
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=9213))
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$
The two manually created services (NEW_APP1 and NEW_APP2) don't startup automatically.
SQL> exec dbms_service.start_service('NEW_APP1');
BEGIN dbms_service.start_service('NEW_APP1'); END;
*
ERROR at line 1:
ORA-44773: Cannot perform requested service operation.
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 63
ORA-06512: at "SYS.DBMS_SERVICE", line 395
ORA-06512: at line 1
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ sqlplus sys/oracle@PDB1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:03:05 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> exec dbms_service.start_service('NEW_APP1');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.start_service('NEW_APP2');
PL/SQL procedure successfully completed.
SQL>
[grid@ora12102 ~]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:04:36
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=9213))
Service "NEW_APP1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_APP2" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$
When I attempted to start the custom service (that, per the previous post, was created in PDB1) when still connected to CDB$ROOT, the command fails. I had to connect to PDB1 to start the service.
SQL> create tablespace hemant ; Tablespace created. SQL> create user hemant identified by hemant default tablespace hemant quota unlimited on hemant; User created. SQL> grant create session to hemant; Grant succeeded. SQL> [oracle@ora12102 Desktop]$ tnsping NEW_APP1 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:09:00 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NEW_APP1))) OK (0 msec) [oracle@ora12102 Desktop]$ tnsping NEW_APP2 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:09:05 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NEW_APP2))) OK (0 msec) [oracle@ora12102 Desktop]$ [oracle@ora12102 Desktop]$ sqlplus hemant/hemant@NEW_APP1 SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:09:29 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_id CON_ID ------------------------------ 3 SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12102 Desktop]$ sqlplus hemant/hemant@NEW_APP2 SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:09:48 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Thu Jun 16 2016 23:09:29 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_id CON_ID ------------------------------ 3 SQL> show conn_name SP2-0158: unknown SHOW option "conn_name" SQL> show con_name CON_NAME ------------------------------ PDB1 SQL>
The newly created user HEMANT belongs to the Database. The user is not tied to a Service. He can use either Service (whichever is running) to connect to the Database. But the two Services have two different TNSNAMES.ORA entries --- differing by the SERVICE_NAME specification.
Thus, the DBA could configure some application servers to use one service name and other application servers to use another service name.
An example of such a configuration is where the first set of application servers could be for Finance Applications called "FINANCE" and the second set of servers could be for HR Applications (in the *same* database) called "HR". Here I create the two services but start only the FINANCE service.
[oracle@ora12102 Desktop]$ sqlplus system/oracle@pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:13:55 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> exec dbms_service.create_service('FINANCE','FINANCE');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.start_service('FINANCE');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.create_service('HR','HR');
PL/SQL procedure successfully completed.
SQL>
[grid@ora12102 ~]$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:15:45
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12102)(PORT=9213))
Service "FINANCE" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_APP1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "NEW_APP2" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[grid@ora12102 ~]$
Now the user should be able to connect to FINANCE, but not to HR.
[oracle@ora12102 Desktop]$ tnsping FINANCE TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:18:16 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = FINANCE))) OK (0 msec) [oracle@ora12102 Desktop]$ tnsping HR TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-JUN-2016 23:18:18 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora12102)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = HR))) OK (0 msec) [oracle@ora12102 Desktop]$ [oracle@ora12102 Desktop]$ sqlplus hemant/hemant@FINANCE SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:18:57 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Thu Jun 16 2016 23:09:48 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12102 Desktop]$ [oracle@ora12102 Desktop]$ sqlplus hemant/hemant@HR SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 16 23:19:23 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Enter user-name:
The user could connect to FINANCE but the request to HR returned ORA-12514.
(Notice how "tnsping HR" is successful but the connection is not ? That is because tnsping only tests if the listener is running, it does not test if the database instance and service are both running).
So, using multiple services, the DBA can "provision" the same database to multiple applications. The entry-point is the SERVICE_NAME, not the USERNAME. Users and Services are independent of each other.
.
.
.
2 comments:
Very nicely explained. Small question .. The services that you created via dbms package, can they be automatically started ? Let's say as soon as pdb opens ?
The SERVICE_NAMES parameter allows auto-start of a service when the database instance is started (i.e. database OPENed). However, I've been told that there is a bug in 12c PDBs. I'll probably test that next.
Post a Comment