Monthly Archives: July 2016

Preventing a SPOF with CMAN 12c

I have already written about the use of Connection Manager 11.2 to govern access to a database. While researching this piece I have updated the original post so that it’s relevant for 12c as well.

Although the idea of using Connection Manager is tempting, the obvious first question is about high availability of the process, you don’t want it to become a Single Point of Failure (SPOF). After all, if the gatekeeper to your database environment fails, you are effectively closed for business. One option would be to add another CMAN process that could be used.

Lab Setup

I have created a few VMs to simulate the scenario. I installed CMAN in 2 VMs running Oracle Linux 7 and CMAN 12c. The client homes have been patched up to the April PSU because that’s considered good practice (although I couldn’t find a reference to CMAN in the OPatch logs). The hosts are named oracledev and server4.

I have a 4 node RAC 12c database named NCDB to which the CMAN rules provide access. The SCAN is named rac12pri-scan.

And finally I have an Oracle 12c installation on server3 which will act as the client.

CMAN configuration

The CMAN configuration is quite straight forward. Following my earlier post’s instructions I created the following $ORACLE_HOME/network/cman.ora on oracledev:

[oracle@oracledev ~]$ cat $ORACLE_HOME/network/admin/cman.ora
cman1 =
  (configuration=
    (address=(protocol=tcp)(host=oracledev)(port=1521))
    (rule_list=
      (rule=(src=oracledev)(dst=127.0.0.1)(srv=cmon)(act=accept))
      (rule=(src=*)(dst=rac12pri-scan)(srv=NCDB)(act=accept))
    )
  )

Please note that the configuration file has been kept at its bare minimum to keep the discussion simple. You should review the net services reference guide for a list of available parameters and how to secure the system.

The configuration on host server4 is identical except that the CMAN host has changed. Using that information I can start the CMAN process:

[oracle@server4 ~]$ cmctl startup -c cman1

CMCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUL-2016 13:38:32

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

Current instance cman1 is not yet started
Connecting to (address=(protocol=tcp)(host=server4)(port=1521))
Starting Oracle Connection Manager instance cman1. Please wait...
TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
CMAN for Linux: Version 12.1.0.2.0 - Production
Status of the Instance
----------------------
Instance name             cman1
Version                   CMAN for Linux: Version 12.1.0.2.0 - Production
Start date                06-JUL-2016 13:38:32
Uptime                    0 days 0 hr. 0 min. 9 sec
Num of gateways started   2
Average Load level        0
Log Level                 SUPPORT
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/12.1.0.2/client_1/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/server4/cman1/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/server4/cman1/trace
The command completed successfully.
[oracle@server4 ~]$ 

I can start the second one the same way.

Client configuration

So this is all fair and good, but how can I configure the client to connect to the database? I would like two things to happen:

  • Connect time load balancing. In other words, don’t hammer just one of the two CMAN processes with incoming connection requests while the second one is idling around
  • Ensure that the alternative CMAN is tried in case one of the two systems dies

Things seemed a bit more complicated because I have to use SOURCE_ROUTE to indicate to the client that a connection manager process is involved. For instance, if I have a single CMAN process, I would use the following entry in tnsnames.ora:

NCDB_1CMAN =
 (description=
   (address_list=
     (source_route=yes)
     (address=(protocol=tcp)(host=server4)(port=1521))
     (address=(protocol=tcp)(host=rac12pri-scan)(port=1521))
   )
   (connect_data=
    (service_name=NCDB)
   )
 )

Relying on connection manager on “server4” I connect through to the NCDB database on my RAC system.

[oracle@server3 tns]$ sqlplus martin@NCDB_1CMAN

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 6 13:44:46 2016

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

Enter password: 
Last Successful login time: Wed Jul 06 2016 13:44:34 +01:00

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

SQL> select count(*) from v$active_instances;

  COUNT(*)
----------
         4

But what happens if server4 is not available? Well you guessed right:

[oracle@server3 tns]$ sqlplus martin@NCDB_1CMAN

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 6 13:52:51 2016

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

Enter password: 
ERROR:
ORA-12541: TNS:no listener

Enter user-name: 

This is the single point of failure I would like to prevent.

Using Two CMAN processes in the tnsnames.ora file

After a bit of digging around on My Oracle Support I gave up and used my search engine skills, and that proved more effective. The Oracle Database Net Services References has the correct example in chapter 6, which looks as follows after I applied it to my environment:

NCDB_2CMANS =
  (description =
    (load_balance = on)
    (failover = on)
    (address_list =                   # first CMAN on server4
      (source_route = yes)
      (address = (protocol = tcp)(host = server4)(port = 1521))
      (address=(protocol=tcp)(host=rac12pri-scan)(port=1521))
    )
    (address_list =                   # second CMAN on oracledev
      (source_route = yes)
      (address = (protocol = tcp)(host = oracledev)(port = 1521))
      (address=(protocol=tcp)(host=rac12pri-scan)(port=1521))
    )
    (connect_data =
      (service_name = NCDB)
      (server = dedicated)
    )
  )

You read this as follows:

  • The service name entry starts with the DESCRIPTION tag.
  • On the top level LOAD_BALANCE and FAILOVER are enabled. This allows client side load balancing plus failover to the next CMAN entry
  • Each CMAN host has its own ADDRESS_LIST. Essentially it’s the NCDB_1CMAN entry with the SOURCE_ROUTE, CMAN host and SCAN
  • There is nothing different in the CONNECT_DATA section compared with the previous, single CMAN-entry

The Result

As a result, I can connect to the database now – I haven’t got a CMAN process on server4 but there is one on oracledev:

[oracle@server4 ~]$ cmctl show status -c cman1

CMCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUL-2016 14:17:16

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

Current instance cman1 is not yet started
Connecting to (address=(protocol=tcp)(host=server4)(port=1521))
TNS-04011: Oracle Connection Manager instance not yet started.
[oracle@server4 ~]$ 

[oracle@oracledev ~]$ cmctl show status -c cman1

CMCTL for Linux: Version 12.1.0.2.0 - Production on 06-JUL-2016 14:17:45

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

TNS-04077: WARNING: No password set for the Oracle Connection Manager instance.
Current instance cman1 is already started
Connecting to (address=(protocol=tcp)(host=oracledev)(port=1521))
Status of the Instance
----------------------
Instance name             cman1
Version                   CMAN for Linux: Version 12.1.0.2.0 - Production
Start date                06-JUL-2016 13:18:19
Uptime                    0 days 0 hr. 59 min. 26 sec
Num of gateways started   2
Average Load level        0
Log Level                 SUPPORT
Trace Level               OFF
Instance Config file      /u01/app/oracle/product/12.1.0.2/client_1/network/admin/cman.ora
Instance Log directory    /u01/app/oracle/diag/netcman/oracledev/cman1/alert
Instance Trace directory  /u01/app/oracle/diag/netcman/oracledev/cman1/trace
The command completed successfully.
[oracle@oracledev ~]$ 

A connection can be established:

[oracle@server3 tns]$ sqlplus martin@NCDB_2CMANS

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 6 14:18:13 2016

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

Enter password: 
Last Successful login time: Wed Jul 06 2016 13:44:49 +01:00

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

SQL> select count(*) from v$active_instances;

  COUNT(*)
----------
         4

SQL>

Using an approach like this allows me to connect to the database even if one of the two CMAN systems are down. But what about load balancing? This appears to work as well, tailing the cman1.log file I can see that out of 10 sessions I requested in a bash for-loop they have been evenly spread across both CMAN hosts:

-- server4

06-JUL-2016 13:21:49 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19796)) * establish * NCDB * 0
06-JUL-2016 13:21:49 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19798)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19800)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19801)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=19802)) * establish * NCDB * 0

-- oracledev

06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22637)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22639)) * establish * NCDB * 0
06-JUL-2016 13:21:50 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22643)) * establish * NCDB * 0
06-JUL-2016 13:21:51 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22644)) * establish * NCDB * 0
06-JUL-2016 13:21:51 * (connect_data=(service_name=NCDB)(server=dedicated)(CID=(PROGRAM=sqlplus)
  (HOST=server3.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.53)(PORT=22645)) * establish * NCDB * 0

So both boxes ticked.

Tales from the field: potential reasons for PDB plug-in violations part 2

In the first part of this little series I explained how a missing (default) tablespace in a PDB can have an impact when opening it. In this instalment I’ll add another common cause for PDBs not opening in read-write mode: different patch levels between the PDB to be plugged in and the CDB.

Scenario

Assume for a moment that you upgraded Oracle binaries from 12.1.0.2.5 to 12.1.0.2.160419. In other words, you applied the most current PSU + OJVM patch at the time of writing to your database. Once complete, you would see output similar to this for a single instance Oracle 12c installation without Oracle Restart:

[oracle@server2 ~]$ opatch lspatches
22674709;Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
22291127;Database Patch Set Update : 12.1.0.2.160419 (22291127)

OPatch succeeded.

I am using database CDB1 in this blog post to indicate the patched CDB:

SYS@CDB$ROOT-CDB1> select patch_id,action,status,description
  2  from dba_registry_sqlpatch;

  PATCH_ID ACTION          STATUS
---------- --------------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
  21555660 APPLY           SUCCESS
Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)

  21359755 APPLY           SUCCESS
Database Patch Set Update : 12.1.0.2.5 (21359755)

  21555660 ROLLBACK        SUCCESS
Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)

  22674709 APPLY           SUCCESS
Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)

  22291127 APPLY           SUCCESS
Database Patch Set Update : 12.1.0.2.160419 (22291127)

NB: I double-checked twice and the above output seems correct: there is no rollback of the 12.1.0.2.5 DB PSU part prior to the installation of the new one.

Let’s also assume that you would like to plug a PDB into the newly patched CDB. The PDB to be unplugged from the other CDB (named CDB2) has a lower patch level:

SYS@CDB$ROOT-CDB2> select patch_id,action,status,description
  2  from dba_registry_sqlpatch;

  PATCH_ID ACTION          STATUS
---------- --------------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
  21555660 APPLY           SUCCESS
Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)

  21359755 APPLY           SUCCESS
Database Patch Set Update : 12.1.0.2.5 (21359755)

Plugging in

The steps needed to plug the PDB into its new home have been discussed so many times that I have decided against showing them again. Please refer to the official documentation set (Database Administrator’s Guide) for all the detail. The PDB I would like to plug in to CDB1 is named PLUGINTEST.

SYS@CDB$ROOT-CDB1> create pluggable database plugintest 
  2  using '/home/oracle/plugintest.xml' nocopy;

Pluggable database created.

SYS@CDB$ROOT-CDB1> alter pluggable database plugintest open;

Warning: PDB altered with errors.

With the information I provided previously you can probably guess what’s wrong, but if you are a production DBA who has been tasked to “plug in a PDB” you might not have the background knowledge about patch levels of all the databases in the estate. How can you find out what went wrong? First stop is the alert.log:

alter pluggable database plugintest open
Thu Jul 07 11:02:30 2016
Pluggable database PLUGINTEST dictionary check beginning
Pluggable Database PLUGINTEST Dictionary check complete
Database Characterset for PLUGINTEST is WE8MSWIN1252
***************************************************************
WARNING: Pluggable Database PLUGINTEST with pdb id - 5 is
         altered with errors or warnings. Please look into
         PDB_PLUG_IN_VIOLATIONS view for more details.
***************************************************************
Opening pdb PLUGINTEST (5) with no Resource Manager plan active
Pluggable database PLUGINTEST opened read write
Completed: alter pluggable database plugintest open

This sounds a bit tragic, but not all is lost. The primary issue is that the PDB is opened in restricted mode, which is not terribly useful for ordinary users:

SYS@CDB$ROOT-CDB1> select con_id, name, open_mode, restricted
  2* from v$pdbs where name = 'PLUGINTEST'

    CON_ID NAME                           OPEN_MODE  RES
---------- ------------------------------ ---------- ---
         5 PLUGINTEST                     READ WRITE YES

SYS@CDB$ROOT-CDB1> conn user1/user1@localhost/plugintest
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Warning: You are no longer connected to ORACLE.

The detail is – again – in PDB_PLUG_IN_VIOLATIONS:

SYS@CDB$ROOT-CDB1> select cause, type, message
  2  from PDB_PLUG_IN_VIOLATIONS
  3  where name = 'PLUGINTEST';

CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
MESSAGE
--------------------------------------------------------------------------------
SQL Patch                                                        ERROR
PSU bundle patch 160419 (Database Patch Set Update : 12.1.0.2.160419 (22291127))
: Installed in the CDB but not in the PDB.

SQL Patch                                                        ERROR
PSU bundle patch 5 (Database Patch Set Update : 12.1.0.2.5 (21359755)): Installe
d in the PDB but not in the CDB.

SQL Patch                                                        ERROR
SQL patch ID/UID 21555660/19361790 (Database PSU 12.1.0.2.5, Oracle JavaVM Compo
nent (Oct2015)): Installed in the PDB but not in the CDB.

SQL Patch                                                        ERROR
SQL patch ID/UID 22674709/20057886 (Database PSU 12.1.0.2.160419, Oracle JavaVM
Component (Apr2016)): Installed in the CDB but not in the PDB.

Although the formatting is a bit of a pain to the eye, it should become clear that there are different patch levels preventing the PDB from opening read-write. If the number of options were different between the system a similar error would be thrown. But how does Oracle know about all that? It’s encoded in the XML file that you create when you unplug the PDB. In my case, here are the options used:

    <options>
      <option>APS=12.1.0.2.0</option>
      <option>CATALOG=12.1.0.2.0</option>
      <option>CATJAVA=12.1.0.2.0</option>
      <option>CATPROC=12.1.0.2.0</option>
      <option>CONTEXT=12.1.0.2.0</option>
      <option>DV=12.1.0.2.0</option>
      <option>JAVAVM=12.1.0.2.0</option>
      <option>OLS=12.1.0.2.0</option>
      <option>ORDIM=12.1.0.2.0</option>
      <option>OWM=12.1.0.2.0</option>
      <option>SDO=12.1.0.2.0</option>
      <option>XDB=12.1.0.2.0</option>
      <option>XML=12.1.0.2.0</option>
      <option>XOQ=12.1.0.2.0</option>
    </options>

Likewise, patches are recorded in the XML file, too:

    <sqlpatches>
      <sqlpatch>PSU bundle patch 5 (Database Patch Set Update : 12.1.0.2.5 (21359755)): 
                 APPLY SUCCESS</sqlpatch>
      <sqlpatch>SQL patch ID/UID 21555660/19361790 (Database PSU 12.1.0.2.5, Oracle JavaVM 
                 Component (Oct2015)): APPLY SUCCESS</sqlpatch>
    </sqlpatches>

Corrective Action

The suggested action is conveniently presented as well.

SYS@CDB$ROOT-CDB1> select cause, type, action from pdb_plug_in_violations where name = 'PLUGINTEST';

CAUSE      TYPE      ACTION
---------- --------- ------------------------------------------------------------
SQL Patch  ERROR     Call datapatch to install in the PDB or the CDB
SQL Patch  ERROR     Call datapatch to install in the PDB or the CDB
SQL Patch  ERROR     Call datapatch to install in the PDB or the CDB
SQL Patch  ERROR     Call datapatch to install in the PDB or the CDB

But you don’t follow blindly what you read, do you ;) So I headed over to the official documentation set and My Oracle Support. I found MOS note 1585822.1 most useful; it explains a similar situation (PDB was closed while datapatch ran) to the one I am seeing. So I decided to try running datapatch again.

SYS@CDB$ROOT-CDB1> alter pluggable database plugintest close immediate;

Pluggable database altered.

SYS@CDB$ROOT-CDB1> alter pluggable database plugintest open upgrade;

Pluggable database altered.

SYS@CDB$ROOT-CDB1>  exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@server2 ~]$ cd $ORACLE_HOME/OPatch
[oracle@server2 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Jul  7 11:20:00 2016
Copyright (c) 2015, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_15280_2016_07_07_11_20_00/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)):
  Installed in PLUGINTEST only
Patch 22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)):
  Installed in binary and CDB$ROOT PDB$SEED SWINGBENCH1
Bundle series PSU:
  ID 160419 in the binary registry and ID 160419 in PDB CDB$ROOT, ID 160419 in PDB PDB$SEED, 
  ID 160419 in PDB SWINGBENCH1, ID 5 in PDB PLUGINTEST

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED SWINGBENCH1
    Nothing to roll back
    Nothing to apply
  For the following PDBs: PLUGINTEST
    The following patches will be rolled back:
      21555660 (Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015))
    The following patches will be applied:
      22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016))
      22291127 (Database Patch Set Update : 12.1.0.2.160419 (22291127))

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...
Patch 21555660 rollback (pdb PLUGINTEST): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/21555660_rollback_CDB1_PLUGINTEST_2016Jul07_11_20_35.log (no errors)
Patch 22674709 apply (pdb PLUGINTEST): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22674709/20057886/22674709_apply_CDB1_PLUGINTEST_2016Jul07_11_21_03.log (no errors)
Patch 22291127 apply (pdb PLUGINTEST): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22291127/19694308/22291127_apply_CDB1_PLUGINTEST_2016Jul07_11_21_04.log (no errors)
SQL Patching tool complete on Thu Jul  7 11:21:14 2016

This fixed my problem:

SYS@CDB$ROOT-CDB1> alter pluggable database plugintest close immediate;

Pluggable database altered.

SYS@CDB$ROOT-CDB1> alter pluggable database plugintest open;

Pluggable database altered.

SYS@CDB$ROOT-CDB1> select con_id, name, open_mode, restricted
  2 from v$pdbs where name = 'PLUGINTEST';

    CON_ID NAME                           OPEN_MODE  RES
---------- ------------------------------ ---------- ---
         5 PLUGINTEST                     READ WRITE NO

Interestingly the entries in PDB_PLUG_IN_VIOLATIONS do not disappear, but there is a timestamp in the view that should help you find out of the message is current or not.