Skip to Main Content
  • Questions

  • service issue with pluggable database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 28, 2018 - 12:33 pm UTC

Last updated: August 08, 2019 - 3:26 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I used windows server 2012 with 64 Bit Operating system and I install Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.
When Create pluggable database as per following link.
https://www.oracle.com/technetwork/articles/database/multitenant-part1-pdbs-2193987.html

I face an issue while adding services in PDBs.

e:\app\GROUP1\product\12.2.0\dbhome_1\bin>srvctl add service -db orcl -s SERVICEPDB11 -pdb PDB11
PRCD-1027 : Failed to retrieve database orcl
PRCR-1070 : Failed to check if resource ora.orcl.db is registered
CRS-0184 : Cannot communicate with the CRS daemon.


So I also search the problem on internet but i not find any proper solution.

and Connor said...

That article probably should have some better context. You'll be using SRVCTL when you are adding database services (and other things) to the resources controlled by the Oracle Clusterware.

If you do not have the Clusterware installed/running, and just have the database running, you can add services via the service_names parameter. For example

--
-- Initial state of listener
--
C:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 01-OCT-2018 09:16:46

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date                21-SEP-2018 12:33:52
Uptime                    9 days 20 hr. 42 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   c:\oracle\product\12.2\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\gtx\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gtx)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "db122" has 1 instance(s).
  Instance "db122", status READY, has 1 handler(s) for this service...
Service "db122XDB" has 1 instance(s).
  Instance "db122", status READY, has 1 handler(s) for this service...
The command completed successfully

--
-- then I alter service_names
--
SQL> alter system set service_names = db122, abc;

System altered.

SQL> alter system register;

System altered.

--
-- New status of listener
--
C:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 01-OCT-2018 09:17:06

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date                21-SEP-2018 12:33:52
Uptime                    9 days 20 hr. 43 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   c:\oracle\product\12.2\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\gtx\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gtx)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "ABC" has 1 instance(s).
  Instance "db122", status READY, has 1 handler(s) for this service...
Service "db122" has 1 instance(s).
  Instance "db122", status READY, has 1 handler(s) for this service...
Service "db122XDB" has 1 instance(s).
  Instance "db122", status READY, has 1 handler(s) for this service...
The command completed successfully


But by default, there will be a service name automatically created for each PDB in your container database, so unless you want *additional* service names, you should be good to go.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Refreshable PDB swithover in 18c

Rajeshwaran, Jeyabal, December 13, 2018 - 2:59 pm UTC

Team,

was going through this video https://www.youtube.com/watch?v=dynkNGL-C9Q&list=PLdtXkK5KBY54dp_yOzKo2S34Ney-vPFCs&index=3 on Refreshable PDB swithover in 18c
also have gone through this from documentation
https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-pdbs-with-sql-plus.html#GUID-B505C234-FAF4-4BAB-8B59-59276E0EA128

Lets say i have Containter database CDB1 with one pluggable database PDB1 (source PDB)
another Containter database CDB2 with one pluggable database PDB2 (refreshable clone PDB)

Now applications are connected to PDB1 and have Transactions
due to increased resource usage in CDB1, planning to swich over the refreshable PDB clone - so after this switch PDB2 become source PDB and PDB1 become refreshable clone PDB.

what about the applications? do i need to repoint their connection entires to PDB2 now to proceed their transaction or no changes required in the application ? please confirm.
Connor McDonald
December 17, 2018 - 3:05 am UTC

My understanding is that the listener will take care of this although it is not a transparent failover - connections are terminated and re-initiated on the new node.

The Listener will show a status that it is forwarding connections to the target

oracle>lsnrctl service

Service "pdb2" has 1 instance(s).
Instance "orclcdb2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: orclprd.us.oracle.com, pid: 31177>
(ADDRESS=(PROTOCOL=tcp)(HOST=orclprd.us.oracle.com)(PORT=42003))
"COMMON" established:182 refused:0 state:ready
FORWARD SERVER                                                      <<<<<FORWARD SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcltst.us.oracle.com)(PORT=1521)))

PDB lock down profiles.

Rajeshwaran Jeyabal, August 07, 2019 - 4:06 pm UTC

Team,

not possible to have multiple values in the lock down profile? Kindly advice.

c##sys@ORA12CR2> drop lockdown profile p1;

Lockdown Profile dropped.

c##sys@ORA12CR2> create lockdown profile p1;

Lockdown Profile created.

c##sys@ORA12CR2> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
  2     option=('CURSOR_SHARING')
  3     value=('FORCE','SIMILAR');
alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
                                        *
ERROR at line 1:
ORA-65206: invalid value specified
ORA-00922: missing or invalid option


c##sys@ORA12CR2> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
  2     option=('CURSOR_SHARING')
  3     value=('FORCE');

Lockdown Profile altered.

c##sys@ORA12CR2>

Connor McDonald
August 08, 2019 - 3:26 am UTC

And this is in no way related to the original question

PDB lock down profiles.

Rajeshwaran Jeyabal, August 08, 2019 - 7:36 am UTC

thought it is related to PDB, so posted here. thanks. raised as a new question.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database