Category Archives: Oracle

Oracle (Database and Middleware) related posts

RBAL (ospid: nnn): terminating the instance due to error 27625 after patching Oracle Restart

I have come across an odd behaviour trying to patch an Oracle Restart environment to 12.1.0.2 January 2019. Based on a twitter conversation this isn’t necessarily limited to my patch combination, there might be others as well. I have used opatchauto to apply patch 28813884 to both RDBMS and GRID homes plus its corresponding OJVM (Java) patch. Before diving into details, this is the environment I have been working with:

  • Oracle Restart 12.1.0.2 with an earlier PSU/OJVM combo applied
  • Separation of duties with oracle as the RDBMS owner, and grid owning the GRID infrastructure installation. This is a key point!
  • Patches to be applied
    • Patch 28813884 (GI PSU)
    • Patch 28790654 (corresponding OJVM patch)
  • OPatch version as installed in the GRID|RDBMS home
    • opatchauto: 13.9.4.2.0
    • opatch: 12.2.0.1.17

Following the instructions in the readme file I upgraded OPatch to the required version. The file I downloaded was named p6880880_121010_Linux-x86-64.zip. I double-checked the readme, and to me this is the correct file. After upgrading OPatch in the RDBMS and GRID homes, I started patching.

After this process completed, I wanted to start the database in UPGRADE mode as required by the OJVM patch. This needs to be done via sqlplus since srvctl does not support an “upgrade” option in “start database”.

And that’s where it hit me: whenever I tried to open the database in UPGRADE mode, it threw an error:

 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 16 12:15:34 2019

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

 Connected to an idle instance.
 
 SQL> startup upgrade
 
 ORACLE instance started.
 
 Total System Global Area 1073741824 bytes
 Fixed Size                  2932632 bytes
 Variable Size             713031784 bytes
 Database Buffers          352321536 bytes
 Redo Buffers                5455872 bytes
 ORA-03113: end-of-file on communication channel
 Process ID: 22354
 Session ID: 14 Serial number: 41958

Looking at the alert log I can see that RBAL terminated the instance:

ALTER DATABASE   MOUNT
 ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.113)(PORT=1521))' SCOPE=MEMORY SID='ORCL';
 2019-07-16 12:15:43.404000 -04:00
 NOTE: ASMB mounting group 1 (DATA)
 WARNING: cellinit.ora is missing. RBAL is terminating the instance.
 RBAL (ospid: 22332): terminating the instance due to error 27625
 System state dump requested by (instance=1, osid=22332 (RBAL)), summary=[abnormal instance termination].
 System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_diag_22306_20190716121543.trc
 Dumping diagnostic data in directory=[cdmp_20190716121543], requested by (instance=1, osid=22332 (RBAL)), summary=[abnormal instance termination].
 Instance terminated by RBAL, pid = 22332
 2019-07-16 12:15:49.164000 -04:00

But since this is Oracle Restart, Clusterware will simply restart the database. Unless of course you’ve been changing the default behaviour. And funny enough, this works (see further down in the article as to why). Although I’d appreciate this in most cases, the automatic restart isn’t appropriate in my situation: when started by Clusterware, the database is not in upgrade mode:

 SQL> select status from v$instance;

 STATUS
 ------------
 OPEN

Which is a problem for me. A MOS search about error 27625 didn’t reveal anything too useful, and it took me quite a while to realise the problem has to do with permissions. An Internet search finally gave me the right answer, a fellow blogger has pointed it out a little while ago …

So what exactly is the problem? The RDBMS “oracle” binary needs a specific set of permissions and ownership/group membership for Oracle Restart with separation of duties enabled. This is what it looked like before applying the patch:

$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 324518992 Jul 16 12:15 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle

The file permissions are ‘6751’ with oracle owning the file but it belongs to the asmadmin group. Remember, I am using a separate user for Grid Infrastructure with its own specific operating system groups. After running opatch, this changed to:

$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle oinstall 324518992 Jul 16 12:29 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle

Permissions remained the same, however the group changed from asmadmin to oinstall. Not quite what I had in mind, and it reproducibly causes instance crashes. There is a simple solution: make sure permissions are set correctly! Using “Database Creation on 11.2/12.1/12.2 Grid Infrastructure with Role Separation ( ORA-15025, KFSG-00312, ORA-15081 ) (Doc ID 1084186.1)” got me on the right track.

 $ ls -l $ORACLE_HOME/bin/oracle
 -rwsr-s--x. 1 oracle asmadmin 324518992 Jul 16 12:29 /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle

 $ sqlplus / as sysdba

 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 16 12:34:45 2019

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

 Connected to an idle instance.

 SQL> startup upgrade
 ORACLE instance started.

 Total System Global Area 1073741824 bytes
 Fixed Size                  2932632 bytes
 Variable Size             713031784 bytes
 Database Buffers          352321536 bytes
 Redo Buffers                5455872 bytes
 Database mounted.
 Database opened.

 SQL> select status from v$instance;

 STATUS
 ------------
 OPEN MIGRATE

 SQL> 

With the database in upgrade mode (I believe the “migrate” is a left over from the 9i days) I am reassured that running datapatch (the OJVM part actually) works as advertised.

By the way Clusterware corrects the group permissions when you issue a “srvctl start database -db …” command as documented in “Starting the database using srvctl changes the group setting for oracle binary (Doc ID 1508027.1)”. Which didn’t solve my problem as I can’t start the database in upgrade mode using srvctl.

Hope this helps you one day!

Advertisements

JSON_TABLE() and date/time columns in Oracle 19c

While researching the use of JSON in Oracle 19c I came some interesting behaviour that wasn’t immediately obvious (to me). With this post I am hoping to save you a couple of minutes scratching your head when working with JSON_TABLE(). This is Oracle 19.3.0 on Linux and I’m connecting to it using SQLcl 19.1.

Some background

As part of my JSON-support-in-Oracle research I had a good look at JSON_TABLE. Although complex at first sight, it is a lot less intimidating if you know how to use XMLTABLE :) My goal for this post is to convert a JSON document to a relational structure.

To convert JSON to (relational) rows and columns, I believe I need to use the JSON_value_column from the list of available options in JSON_TABLE‘s JSON_column_definition. Interestingly, and different from the syntax diagram for XMLTABLE(), I’m allowed to specify a JSON_value_return_type. There are quite a a few of them, and I guess the most commonly used ones are going to be varchar2, date and timestamp. This post is about the latter two, since parsing out a text string is pretty simple.

The Test Case

I created a very simple table to test the functionality:

SQL> create table t1 (
   2   id number not null,
   3   json_txt clob not null,
   4   constraint pk_t1 primary key (id),
   5   constraint c_is_json check (json_txt is json)
   6  );

 Table created.

Essentially a table to hold a JSON column plus a check constraint to make sure that I’m not inserting data that isn’t JSON. Here’s the initial JSON document I started with:

SQL> select json_query(json_txt, '$' pretty error on error) 
  2   from t1 where id = 1;

JSON_QUERY(JSON_TXT,'$'PRETTYERRORONERROR)                  
------------------------------------------------------------
{                                                           
  "results" :                                               
  [                                                         
    {                                                       
      "resultID" : 1,                                       
      "resultDate" : "13-JUN-19 08:10:00",                  
      "details" : "none"                                    
    }                                                       
  ]                                                         
}                 

Thanks to my check constraint I know this is valid JSON. I specified the pretty keyword to make the output more readable. When developing using new(-ish) features I prefer Oracle to raise an error so I’m going with ERROR ON ERROR.

These are my NLS settings by the way, this is a system installed with the default locale set to en_US. I have added these settings for reference and they will play a role later on.

SQL> select * from nls_session_parameters
  2* where regexp_like(parameter,'(DATE|TIME)');
PARAMETER                      VALUE                         
------------------------------ ------------------------------
NLS_DATE_FORMAT                DD-MON-RR                     
NLS_DATE_LANGUAGE              AMERICAN                      
NLS_TIME_FORMAT                HH24.MI.SSXFF                 
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH24.MI.SSXFF       
NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZR             
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH24.MI.SSXFF TZR   

6 rows selected. 

I am now ready to start testing.

First Test

So let’s get started with the testing. My query is shown here:

SELECT
    jt.*
FROM
    t1,
    JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
            COLUMNS (
                resID   NUMBER   PATH '$.resultID',
                resDATE DATE     PATH '$.resultDate'
            )
        )
    AS jt
WHERE
    t1.id = 1;

Not too dissimilar to using XMLTABLE, I need to pass the column containing my JSON document to the function, followed by the path to the data within the JSON document I want to parse. I could have stored multiple results in the array (and I did initially, but considered that a little too confusing), hence the reference to all of them.

The columns() clause (of type JSON_value_column) allows me to define columns, their respective data type and the path relative to the second argument to JSON_TABLE. In my case that’s $.results.resultID and $.results.resultDate. These two are mapped to resID and resDATE in the output. I am limiting the query to my first row in t1.

When I execute this query against the first record in the table, I get an error:

SQL> SELECT
  2      jt.*
  3  FROM
  4      t1,
  5      JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
  6              COLUMNS (
  7                  resID   NUMBER   PATH '$.resultID',
  8                  resDATE DATE     PATH '$.resultDate'
  9              )
 10          )
 11      AS jt
 12  WHERE
 13      t1.id = 1;

Error starting at line : 1 in command -
SELECT
    jt.*
FROM
    t1,
    JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
            COLUMNS (
                resid   NUMBER   PATH '$.resultID',
                resdate DATE     PATH '$.resultDate'
            )
        )
    AS jt
WHERE
    t1.id = 1
Error report -
ORA-01861: literal does not match format string

This is kind of surprising, my suspicion at the time was that something is wrong with the NLS_DATE_FORMAT settings. But whatever I tried, I always got the same error. Using MOS and other resources on the net turned out blank, which might well be down to my inadequate search efforts. Tracing didn’t really help either.

Second Test

I tried a few other things until, with the help of an educated guess, I got lucky: I removed the time portion from the resultDate and formatted it to the output you get from selecting sysdate from dual, as shown here:

SQL> select json_query(json_txt, '$.results' pretty error on error) 
  2  from t1 where id = 2; 

JSON_QUERY(JSON_TXT,'$.RESULTS'PRETTYERRORONERROR)          
------------------------------------------------------------
[                                                           
  {                                                         
    "resultID" : 2,                                         
    "resultDate" : "13-JUN-19",                             
    "details" : "none"                                      
  }                                                         
]                                                           

This time around, the error message was different:

SQL> SELECT
  2      jt.*
  3  FROM
  4      t1,
  5      JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
  6              COLUMNS (
  7                  resID   NUMBER    PATH '$.resultID',
  8                  resDATE DATE      PATH '$.resultDate'
  9              )
 10          )
 11      AS jt
 12  WHERE
 13      t1.id = 2;

Error starting at line : 1 in command -
SELECT
    jt.*
FROM
    t1,
    JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
            COLUMNS (
                resID   NUMBER    PATH  '$.resultID',
                resDATE DATE      PATH '$.resultDate'
            )
        )
    AS jt
WHERE
    t1.id = 2
Error report -
ORA-40688: date or time stamp string value does not conform
to ISO 8601 format

Aha! That’s much better than ORA-01861: literal does not match format string. So it would appear as if the date the system expected to find in the JSON document must be ISO 8601 compliant. Ummm, what exactly is an ISO 8601 date format? The Internet knew!

Third Test

So maybe using an ISO 8601 time format in the JSON document is the answer? Worth a try, the JSON document now looks like this:

SQL> select json_query(json_txt, '$.results' pretty error on error) 
  2  from t1 where id = 3;

JSON_QUERY(JSON_TXT,'$.RESULTS'PRETTYERRORONERROR)          
------------------------------------------------------------
[                                                           
  {                                                         
    "resultID" : 3,                                         
    "resultDate" : "2019-06-13T16:39:00+00:00",             
    "details" : "none"                                      
  }                                                         
]     

It seems that ISO 8601 requires a slightly more complex time expression, much more like a timestamp in Oracle. Let’s see if this works:

SQL> SELECT
  2      jt.*
  3  FROM
  4      t1,
  5      JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
  6              COLUMNS (
  7                  resID   NUMBER    PATH  '$.resultID',
  8                  resDATE DATE      PATH '$.resultDate'
  9              )
 10          )
 11      AS jt
 12  WHERE
 13      t1.id = 3;

     RESID RESDATE  
---------- ---------
         3 13-JUN-19

Hurray, no errors, but result data for a change!

Caveat

But wait, that’s not the whole story. I have stored a time component in the input document, so how do I get that? I’d normally use to_char() for that purpose, so let’s give that a go:

SQL> SELECT
  2      to_char(jt.resDATE, 'dd.mm.yyyy hh24:mi:ss') as formattedDate
  3  FROM
  4      t1,
  5      JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
  6              COLUMNS (
  7                  resID   NUMBER    PATH  '$.resultID',
  8                  resDATE DATE      PATH '$.resultDate'
  9              )
 10          )
 11      AS jt
 12  WHERE
 13      t1.id = 3;

FORMATTEDDATE      
-------------------
13.06.2019 00:00:00

Hmmm, that’s midnight, and not 16:39:00+00:00 …. Unless I have missed something important it seems as if the DATE column type in the columns() clause stripped the input of its time component. Not to worry though, there’s a timestamp type. Maybe that can help?

SQL> SELECT
  2      jt.*
  3  FROM
  4      t1,
  5      JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
  6              COLUMNS (
  7                  resID   NUMBER    PATH '$.resultID',
  8                  resDATE TIMESTAMP PATH '$.resultDate'
  9              )
 10          )
 11      AS jt
 12  WHERE
 13      t1.id = 3;

     RESID RESDATE                     
---------- ----------------------------
         3 13-JUN-19 16.39.00.000000000

Voila! I’ve got a date and time.

Summary

From my very limited testing it appears as if input date in the JSON document has to be provided in ISO 8601 format, or otherwise Oracle raises an error. Unlike in SQLLDR’s control file there appears to be no option to tell Oracle about the formatting. Changing the NLS_DATE_FORMAT (not shown here) did affect the output of the query only. It didn’t appear to be applied when parsing the input.

Why does my REST Services menu not show up in SQL Developer?

Oracle SQL Developer has excellent support for Oracle Restful Data Services (ORDS). A lot of the functionality is just a mouse click away. With so many people speaking about RESTful APIs I wanted to see what they are like. However, when I first tried to use SQL Developer to administer ORDS in the database I was surprised at first to not find the menu item to do so. This post might be stating the (insert colourful adjective) obvious, but it took me a little time to work it out and I’m hoping this post saves you 5 minutes.

What’s the problem?

When right-clicking my connection node in the Connections tree I should be shown a menu named “REST Services”. Which I wasn’t, as shown in the figure below.

And no, I did of course not read the documentation beforehand.

And how do you solve it?

After a little research it turns out that said menu appears only after ORDS has been installed. As part of the installation ORDS connects to the database and deploys code, triggering the appearance of “REST Services” menu in SQL Developer when you connect next. Here’s what I think is the relevant output from the installation:

$ java -jar ords.war install simple

...

Installing Oracle REST Data Services version 19.1.0.r0921545
... Log file written to /home/oracle/ords_install_core_2019-06-13_092412_00708.log
... Verified database prerequisites
... Created Oracle REST Data Services proxy user
... Created Oracle REST Data Services schema
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /home/oracle/ords_install_datamodel_2019-06-13_092420_00287.log
... Log file written to /home/oracle/ords_install_apex_2019-06-13_092421_00372.log
Completed installation for Oracle REST Data Services version 19.1.0.r0921545. Elapsed time: 00:00:09.825

...

Once the deployment is complete, the “REST Services” menu appears (you might have to re-connect), as you can see in the following figure:

Hope this helps!

Using the Secure External Password store with sqlcl

Sometimes it is necessary to invoke a SQL script in bash or otherwise in an unattended way. SQLcl has become my tool of choice because it’s really lightweight and can do a lot. If you haven’t worked with it yet, you really should give it a go.

So how does one go about invoking SQL scripts from the command line these days? There’s an age-old problem with unattended execution: how do you authenticate against the database? There are many ways to do so, some better than others. This post shows how to use the Secure External Password Store with SQLcl. As always, there is more than one way to do this, @FranckPachot recently wrote about a different approach on Medium which you might want to check out as well.

Please don’t store passwords in scripts

I have seen passwords embedded in shell scripts far too often, and that’s something I really don’t like for many, many reasons. Thankfully Oracle offers an alternative to storing clear text passwords in the form of the Secure External Password Store (SEPS).This post explains one of many ways to use a wallet to use sqlcl to connect to a database. It assumes that a Secure External Password store is set up with the necessary credentials. Components referenced in this post are:

  • sqlcl 19.1
  • Instant Client Basic 18.5
  • Oracle XE 18.4

The SEPS wallet is found in /home/oracle/seps with its corresponding tnsnames.ora and sqlnet.ora in /home/oracle/seps/tns. I have set TNS_ADMIN to /home/oracle/seps/tns and ensured that sqlnet.ora points to the correct wallet location.

First attempt

The first attempt at using sqlcl with the wallet resulted in the following error:

$ /home/oracle/sqlcl/bin/sql -L /@xepdb1

SQLcl: Release 19.1 Production on Fri May 17 05:56:56 2019

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

  USER          = 
  URL           = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclexe)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1)))
  Error Message = ORA-01017: invalid username/password; logon denied

I provided the -L flag to prevent sqlcl from asking me for different credentials after a failed login attempt. Using the -verbose flag in the next attempt I confirmed that sqlcl was indeed using my tnsnames.ora file in the directory specified by $TNS_ADMIN.

Investigating

So I started investigating … The first place to go to is the documentation, however I didn’t find anything relevant in the command line reference or FAQ shown on the product’s landing page. I then cast my net wider and found a few things on My Oracle Support (they didn’t apply to my version of sqlcl) and the Oracle forums.

I tried various things to get the thin client to cooperate with using the wallet but didn’t pursue that route further after learning about the option to use the OCI JDBC driver. After experimenting a little more I got on the right track.

Second attempt

The consensus in the Oracle forum posts I found seems to be to use the OCI flag when invoking the tool. So I tried that next:

$ /home/oracle/sqlcl/bin/sql -L -oci /@xepdb1

SQLcl: Release 19.1 Production on Fri May 17 06:09:29 2019

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

  USER          = 
  URL           = jdbc:oracle:oci8:@xepdb1
  Error Message = no ocijdbc18 in java.library.path
  USER          = 
  URL           = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclexe)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1)))
  Error Message = ORA-01017: invalid username/password; logon denied

No success yet, but there’s an important clue in the output the first URL indicates that indeed an OCI connection was tried, except that a shared library was missing from Java’s library path. I guessed correctly that ocijdbc18 is part of the instant client 18 basic installation. After installing the RPM for the latest 18c instant client I confirmed libocijdb18.so was part of the package.

From what I understand java doesn’t pick up the configuration created by ldconfig and you either have to set java.library.path manually (as in java -Djava.library.path=…) or set LD_LIBRARY_PATH. The latter is easier, and it gave me the desired result:

$ export LD_LIBRARY_PATH=/usr/lib/oracle/18.5/client64/lib:$LD_LIBRARY_PATH
$ echo "select user from dual" | /home/oracle/sqlcl/bin/sql -L -oci /@xepdb1

SQLcl: Release 19.1 Production on Fri May 17 06:15:29 2019

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

Last Successful login time: Fri May 17 2019 06:15:32 -04:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

USER                                                                                                                            
------------------------------------------------------------------------
MARTIN                                                                                                                    

Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Result! I can use sqlcl to connect to a database using a wallet.

Learning about Kubernetes: JDBC database connectivity to an Oracle database

In the past few months I have spent some time trying to better understand Kubernetes and how application developers can make use of it in the context of the Oracle database. In this post I’m sharing what I learned along that way. Please be careful: this is very much a moving target, and I wouldn’t call myself an expert in the field. If you find anything in this post that could be done differently/better, please let me know!

By the way, I am going to put something similar together where Oracle Restful Data Services (ORDS) will provide a different, more popular yet potentially more difficult-to-get-right connection method.

My Motivation for this post

The question I wanted to answer to myself was: can I deploy an application into Kubernetes that uses JDBC to connect to Oracle? The key design decision I made was to have a database outside the Kubernetes cluster. From what I read on blogs and social media it appears to be the consensus at the time of writing to keep state out of Kubernetes. I am curious to see if this is going to change in the future…

My environment

As this whole subject of container-based deployment and orchestration is developing at an incredible pace, here’s the specification of my lab environment used for this little experiment so I remember which components I had in use in case something breaks later…

  • Ubuntu 18.04 LTS serves as my host operating environment
  • Local installation of kubectl v1.14.1
  • I am using minikube version: v1.0.1 and it pulled the following default components:
    • Container runtime: docker 18.06.3-ce
    • kubernetes 1.14.1 (kubeadm v1.14.1, kubelet v1.14.1)
  • The database service is provided by Oracle XE (oracle-database-xe-18c-1.0-1.x86_64)
    • Oracle XE 18.4.0 to provide data persistance in a VM (“oraclexe.example.com”)
    • The Pluggable Database (PDB) name is XEPDB1
    • I am using an unprivileged user to connect

I would like to take the opportunity again to point out that the information you see in this post is my transcript of how I built this proof-of-concept, can-I-even-do-it application. It’s by no means meant to be anything other than that. There are a lot more bells and whistles to be attached to this in order to make it remotely ready for more serious use. But it was super fun putting this all together which is why I’m sharing this anyway.

Preparing Minikube

Since I’m only getting started with Kubernetes I didn’t want to set up a multi-node Kubernetes cluster in the cloud, and instead went with Minikube just to see if my ideas pan out ok or not. As I understand it, Minikube is a development/playground Kubernetes environment and it’s explicitly stated not to be suitable for production. It does seem to give me all that I need to work out my ideas. Since I’ve been using Virtualbox for a long time I went with the Minikube Virtualbox provider and all the defaults. It isn’t too hard to get this to work, I followed the documentation (see earlier link) on how to install kubectl (the main Kubernetes command line tool) and Minikube.

Once kubectl and Minikube were installed, I started the playground environment. On a modern terminal this prints some very fancy characters in the output as you can see:

$ minikube start
πŸ˜„  minikube v1.0.1 on linux (amd64)
πŸ’Ώ  Downloading Minikube ISO ...
 142.88 MB / 142.88 MB [============================================] 100.00% 0s
🀹  Downloading Kubernetes v1.14.1 images in the background ...
πŸ”₯  Creating virtualbox VM (CPUs=2, Memory=2048MB, Disk=20000MB) ...
πŸ“Ά  "minikube" IP address is 192.168.99.100
🐳  Configuring Docker as the container runtime ...
🐳  Version of container runtime is 18.06.3-ce
βŒ›  Waiting for image downloads to complete ...
✨  Preparing Kubernetes environment ...
πŸ’Ύ  Downloading kubeadm v1.14.1
πŸ’Ύ  Downloading kubelet v1.14.1
🚜  Pulling images required by Kubernetes v1.14.1 ...
πŸš€  Launching Kubernetes v1.14.1 using kubeadm ... 
βŒ›  Waiting for pods: apiserver proxy etcd scheduler controller dns
πŸ”‘  Configuring cluster permissions ...
πŸ€”  Verifying component health .....
πŸ’—  kubectl is now configured to use "minikube"
πŸ„  Done! Thank you for using minikube!

This gives me a fully working Kubernetes lab environment. Next I need an application! My plan is to write the world’s most basic JDBC application connecting to the database and return success or failure. The application will be deployed into Kubernetes, the database will remain outside the cluster as you just read.

Let’s start with the datbase

I needed a database to connect to, obviously. This was super easy: create a VM, install the Oracle XE RPM for version 18c, and create a database. I started the database and listener to enable external connectivity. The external IP address to which the listener is bound is 192.168.99.113. This is unusual for Virtualbox as its internal network defaults to 192.168.56.0/24. Minikube started on 192.168.99.0/24 though and I had to make an adjustment to allow both VMs to communicate without having to mess with advanced routing rules.

Within XEPDB1 (which appears to be the default PDB in XE) I created a user named martin with a super secret password, granted him the connect role and left at at that. This account will later be used by my application.

Developing a super simple Java app to test connectivity

Modern applications are most often based on some sort of web technology, so I decided to go with that, too. I decided to write a very simple Java Servlet in which I connect to the database and print some status messages along the way. It’s a bit old-fashioned but makes it easy to get the job done.

I ended up using Tomcat 8.5 as the runtime environment, primarily because I have a working demo based on it and I wanted to save a little bit of time. I am implementing a Universal Connection Pool (UCP) as a JNDI resource in Tomcat for my application. The Servlet implements doGet(), and that’s where most of the action is going to take place. For anything but this playground environment I would write factory methods to handle all interactions with UCP and a bunch of other convenience methods. Since this is going to be a simple demo I decided to keep it short without confusing readers with 42 different Java classes. The important part of the Servlet code is shown here:

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
          throws ServletException, IOException {

		PrintWriter out = response.getWriter();
		response.setContentType("text/html");
		
		out.println("<html&gt;<head&gt;<title&gt;JDBC connection test</title&gt;</head&gt;");
		
		out.println("<body&gt;");
		
		out.println("<h1&gt;Progress report</h1&gt;");
		out.println("<ul&gt;");
		
		out.println("<li&gt;Trying to look up the JNDI data source ... ");
		PoolDataSource pds = null;
		Connection con = null;
		DataSource ds = null;
		PreparedStatement pstmt = null;
		
		// JNDI resource look-up
		Context envContext = null;
		Context ctx;
		try {
			ctx = new InitialContext();
			envContext = (Context) ctx.lookup("java:/comp/env");

			ds = (javax.sql.DataSource) envContext.lookup("jdbc/UCPTest");
		} catch (NamingException e) {
			
			out.println("error looking up jdbc/UCPTest: " + e.getMessage());
			e.printStackTrace();
			return;
		}
		
		pds = ((PoolDataSource) ds);
		
		out.println("Data Source successfully acquired</li&gt;");
		out.println("<li&gt;Trying to get a connection to the database ... ");
		
		try {
			con = pds.getConnection();
			
			// double-checking the connection although that shouldn't be necessary
			// due to the "validateConnectionOnBorrow flag in context.xml
			if (con == null || !((ValidConnection)con).isValid()) {
				out.println("Error: connection is either null or otherwise invalid</li&gt;");
			}
			
			out.println(" successfully grabbed a connection from the pool</li&gt;");
			out.println("<li&gt;Trying run a query against the database ... ");
		
			pstmt = con.prepareStatement("select sys_context('userenv','db_name') from dual");
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) {
				out.println(" query against " + rs.getString(1) + " completed successfully</li&gt;");
			} 
			
			// clean up
			rs.close();
			pstmt.close();
			con.close();
			con = null;
			
			out.println("<li&gt;No errors encountered, we are done</li&gt;");
			out.println("</ul&gt;");
			
		} catch (SQLException e) {
			out.println("</ul&gt;");
			out.println("<h2&gt;A generic error was encountered. </h2&gt;");
			out.println("<p&gt;Error message:</p&gt; <pre&gt; " + e.getMessage() + "</pre&gt;");
		}

		out.println("</body&gt;</html&gt;");
	}

The context.xml file describing my JNDI data source goes into META-INF/context.xml in the WAR file.

<?xml version="1.0" encoding="UTF-8"?&gt;

<Context&gt;
	<Resource name="jdbc/UCPTest" 
		auth="Container"
		factory="oracle.ucp.jdbc.PoolDataSourceImpl"
		type="oracle.ucp.jdbc.PoolDataSource"
		description="UCP JNDI Connection Pool"
		connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
		initialPoolSize="10" 
		minPoolSize="10" 
		maxPoolSize="10"
		inactiveConnectionTimeout="20" 
		user="martin" 
		password="..."
		url="jdbc:oracle:thin:@//oracle-dev/XEPDB1"
		connectionPoolName="UCPTest" 
		validateConnectionOnBorrow="true"
		fastConnectionFailoverEnabled="false" /&gt;
</Context&gt;

Note the URL: it references oracle-dev as part of the JDBC connection string. That’s not the DNS name of my VM, and I’ll get back to that later.

As always, I debugged/compiled/tested the code until I was confident it worked. Once it did, I created a WAR file named servletTest.war for use with Tomcat.

Services

In a microservice-driven world it is very important NOT to hard-code IP addresses or machine names. In the age of on-premises deployments it was very common to hard code machine names and/o IP addresses into applications. Needless to say that … wasn’t ideal.

In Kubernetes you expose your deployments as services to the outside world to enable connectivity. With the database outside of Kubernetes in its own VM – which appears to be the preferred way of doing this at the time of writing – I need to expose it as a headless service. Using an external name appears to be the right choice. The YAML file defining the service is shown here:

$ cat service.yml 
---
kind: Service
apiVersion: v1
metadata:
  name: oracle-dev
spec:
  type: ExternalName
  externalName: oraclexe.example.com

If you scroll up you will notice that this service’s name matches the JDBC URL in the context.xml file shown earlier. The name is totally arbitrary, I went with oracle-dev to indicate this is a playground environment. The identifier listed as external name must of course resolve to the appropriate machine:

$ ping -c 1 oraclexe.example.com 
PING oraclexe.example.com (192.168.99.113) 56(84) bytes of data.
64 bytes from oraclexe.example.com (192.168.99.113): icmp_seq=1 ttl=64 time=0.290 ms

--- oraclexe.example.com ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.290/0.290/0.290/0.000 ms

A quick kubectl apply later the service is defined in Minikube:

$ kubectl get service/oracle-dev -o wide
NAME         TYPE           CLUSTER-IP   EXTERNAL-IP            PORT(S)   AGE   SELECTOR
oracle-dev   ExternalName   <none&gt;       oraclexe.example.com   <none&gt;    14m   <none&gt;

That should be sorted! Whenever applications in the Kubernetes cluster refer to oracle-dev, they are pointed to oraclexe.example.com, my database VM.

Docker all the things, eh application

With the WAR file ready and a service I could use for the JDBC connection pool ready I could start thinking about deploying the application.

Create the Docker image

The first step is to package the application in Docker. To do so I used the official Tomcat Docker image. The Dockerfile is trivial: specify tomcat:8.5-slim and copy the WAR file into /usr/local/tomcat/webapps. That’s it! The CMD directive invokes “catalina.sh run” to start the servlet engine. This is the output created by “docker build”:

$ docker build -t servlettest:v1 .
Sending build context to Docker daemon  10.77MB
Step 1/3 : FROM tomcat:8.5-slim
8.5-slim: Pulling from library/tomcat
27833a3ba0a5: Pull complete 
16d944e3d00d: Pull complete 
9019de9fce5f: Pull complete 
9b053055f644: Pull complete 
110ab0e6e34d: Pull complete 
54976ba77289: Pull complete 
0afe340b9ec5: Pull complete 
6ddc5164be39: Pull complete 
c622a1870b10: Pull complete 
Digest: sha256:7c1ed9c730a6b537334fbe1081500474ca54e340174661b8e5c0251057bc4895
Status: Downloaded newer image for tomcat:8.5-slim
 ---> 78e1f34f235b
Step 2/3 : COPY servletTest.war /usr/local/tomcat/webapps/
 ---> c5b9afe06265
Step 3/3 : CMD ["catalina.sh", "run"]
 ---> Running in 2cd82f7358d3
Removing intermediate container 2cd82f7358d3
 ---> 342a8a2a31d3
Successfully built 342a8a2a31d3
Successfully tagged servlettest:v1

Once the docker image is built (remember to use eval $(minikube docker-env) before you run docker build to make the image available to Minikube) you can reference it in Kubernetes.

Deploying the application

The next step is to run the application. I have previously used “kubectl run” but since that’s deprecated I switched to creating deployments as YAML files. I dumped the deployment generated by “kubectl run” into a YML file and used the documentation to adjust it to what I need. The result is shown here:

$ cat deployment.yml 
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  labels:
    app: servlettest
  name: servlettest
spec:
  replicas: 1
  selector:
    matchLabels:
      app: servlettest
  template:
    metadata:
      labels:
        app: servlettest
    spec:
      containers:
      - image: servlettest:v1
        imagePullPolicy: IfNotPresent
        name: servlettest
        ports:
        - containerPort: 8080
          protocol: TCP
      restartPolicy: Always

The application is deployed into Kubernetes using “kubectl apply -f deployment.yml”. A few moments later I can see it appearing:

$ kubectl get deploy/servlettest -o wide
NAME          READY   UP-TO-DATE   AVAILABLE   AGE     CONTAINERS    IMAGES           SELECTOR
servlettest   1/1     1            1           7m23s   servlettest   servlettest:v1   app=servlettest

I’m delighted to see 1/1 pods ready to serve the application! It looks everything went to plan. I can see the pod as well:

$ kubectl get pods -o wide
NAME                           READY   STATUS    RESTARTS   AGE     IP           NODE       NOMINATED NODE   READINESS GATES
servlettest-6c6598b487-wssp6   1/1     Running   0          8m12s   172.17.0.4   minikube   <none&gt;           <none&gt;

Accessing the application

I now have an application running in Kubernetes! Umm, but what next? I haven’t been able to get access to the application yet. To do so, I need to expose another service. The Minikube quick start suggests exposing the application service via a NodePort, and that seems to be the quickest way. In a “proper Kubernetes environment” in the cloud you’d most likely go for a service of type LoadBalancer to expose applications to the public.

So far I have only shown you YAML files to configure Kubernetes resources, many things can be done via kubectl directly. For example, it’s possible to expose the deployment I just created as a service on the command line:

$ kubectl expose deployment servlettest --type NodePort
service/servlettest exposed

$ kubectl get service/servlettest -o wide
NAME          TYPE       CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE   SELECTOR
servlettest   NodePort   10.100.249.181   <none&gt;        8080:31030/TCP   71s   app=servlettest

One of the neat things with Minikube is that you don’t need to worry too much about how to actually access that service: the minikube service command provides the URL for the service, and it even opens it in the default browser if you so want. I’ll see if I can get the ingress add-on to work as well, but that should probably go into its own post…

In my case I’m only interested in the URL because I want a textual representation of the output:

$ minikube service servlettest --url
http://192.168.99.100:31030

$ lynx --dump http://192.168.99.100:31030/servletTest/JDBCTestServlet
                                Progress report

     * Trying to look up the JNDI data source ... Data Source successfully
       acquired
     * Trying to get a connection to the database ... successfully grabbed
       a connection from the pool
     * Trying run a query against the database ... query against XE
       completed successfully
     * No errors encountered, we are done

The URL I am passing to lynx is made up of the IP and node port (192.168.99.100:31030) as well as the servlet path based on the WAR file’s name.

So that’s it! I have a working application deployed into Minikube, using a service of type “ExternalName” to query my database. Although these steps have been created using Minikube, they shouldn’t be different on a “real” Kubernetes environment.

What about the database?

The JNDI resource definition for my connection pool requested 10 sessions, and they have been dutifully created:

SQL> select username, machine from v$session where username = 'MARTIN';

USERNAME                       MACHINE
------------------------------ ----------------------------------------
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6
MARTIN                         servlettest-6c6598b487-wssp6

10 rows selected.

The “machine” matches the pod name as shown in kubectl output:

$ kubectl get pods -l app=servlettest -o wide
NAME                           READY   STATUS    RESTARTS   AGE   IP           NODE       NOMINATED NODE   READINESS GATES
servlettest-6c6598b487-wssp6   1/1     Running   0          30m   172.17.0.4   minikube   <none&gt;           <none&gt;

Well I guess you can actually deploy the world’s most basic JDBC application into Kubernetes. Whether this works for you is an entirely different matter. As many much cleverer people than me have been pointing out for some time: just because a technology is “hot” doesn’t mean it’s the best tool for the job. It’s always worthwhile weighing up pros and cons for your solution carefully, and just because something can be done (technically speaking) doesn’t mean it should be done.

Ansible tips’n’tricks: provision multiple machines in parallel with Vagrant and Ansible

Vagrant is a great tool that I’m regularly using for building playground environments on my laptop. I recently came across a slight inconvenience with Vagrant’s Virtualbox provider: occasionally I would like to spin up a Data Guard environment and provision both VMs in parallel to save time. Sadly you can’t bring up multiple machines in parallel using the VirtualBox provisioner according to the documentation . This was true as of April 11 2019 and might change in the future, so keep an eye out on the reference.

I very much prefer to save time by doing things in parallel, and so I started digging around how I could achieve this goal.

The official documentation mentions something that looks like a for loop to wait for all machines to be up. This isn’t really an option, I wanted more control over machine names and IP addresses. So I came up with this approach, it may not be the best, but it falls into the “good enough for me” category.

Vagrantfile

The Vagrantfile is actually quite simple and might remind you of a previous article:

  1 Vagrant.configure("2") do |config|
  2   config.ssh.private_key_path = "/path/to/key"
  3 
  4   config.vm.define "server1" do |server1|
  5     server1.vm.box = "ansibletestbase"
  6     server1.vm.hostname = "server1"
  7     server1.vm.network "private_network", ip: "192.168.56.11"
  8     server1.vm.synced_folder "/path/to/stuff", "/mnt",
  9       mount_options: ["uid=54321", "gid=54321"]
 10 
 11     config.vm.provider "virtualbox" do |vb|
 12       vb.memory = 2048
 13       vb.cpus = 2
 14     end
 15   end
 16 
 17   config.vm.define "server2" do |server2|
 18     server2.vm.box = "ansibletestbase"
 19     server2.vm.hostname = "server2"
 20     server2.vm.network "private_network", ip: "192.168.56.12"
 21     server2.vm.synced_folder "/path/to/stuff", "/mnt",
 22       mount_options: ["uid=54321", "gid=54321"]
 23 
 24     config.vm.provider "virtualbox" do |vb|
 25       vb.memory = 2048
 26       vb.cpus = 2
 27     end
 28   end
 29 
 30   config.vm.provision "ansible" do |ansible|
 31     ansible.playbook = "hello.yml"
 32     ansible.groups = {
 33       "oracle_si" => ["server[1:2]"],
 34       "oracle_si:vars" => { 
 35         "install_rdbms" => "true",
 36         "patch_rdbms" => "true",
 37       }
 38     }
 39   end
 40 
 41 end

Ansibletestbase is my custom Oracle Linux 7 image that I keep updated for personal use. I define a couple of machines, server1 and server2 and from line 30 onwards let Ansible provision them.

A little bit of an inconvenience

Now here is the inconvenient bit: if I provided an elaborate playbook to provision Oracle in line 31 of the Vagrantfile, it would be run serially. First for server1, and only after it completed (or failed…) server2 will be created and provisioned. This is the reason for a rather plain playbook, hello.yml:

$ cat hello.yml 
---
- hosts: oracle_si
  tasks:
  - name: say hello
    debug: var=ansible_hostname

This literally takes no time to execute at all, so no harm is done running it serially once per VM. Not only is no harm done, quite the contrary: Vagrant discovered an Ansible provider in the Vagrantfile and created a suitable inventory file for me. I’ll gladly use it later.

How does this work out?

Enough talking, time to put this to test and to bring up both machines. As you will see in the captured output, they start one-by-one, run their provisioning tool and proceed to the next system.

$ vagrant up 
Bringing machine 'server1' up with 'virtualbox' provider...
Bringing machine 'server2' up with 'virtualbox' provider...
==> server1: Importing base box 'ansibletestbase'...
==> server1: Matching MAC address for NAT networking...

[...]

==> server1: Running provisioner: ansible...

[...]

    server1: Running ansible-playbook...

PLAY [oracle_si] ***************************************************************

TASK [Gathering Facts] *********************************************************
ok: [server1]

TASK [say hello] ***************************************************************
ok: [server1] => {
    "ansible_hostname": "server1"
}

PLAY RECAP *********************************************************************
server1                    : ok=2    changed=0    unreachable=0    failed=0   

==> server2: Importing base box 'ansibletestbase'...
==> server2: Matching MAC address for NAT networking...

[...]

==> server2: Running provisioner: ansible...

[...]
    server2: Running ansible-playbook...

PLAY [oracle_si] ***************************************************************

TASK [Gathering Facts] *********************************************************
ok: [server2]

TASK [say hello] ***************************************************************
ok: [server2] => {
    "ansible_hostname": "server2"
}

PLAY RECAP *********************************************************************
server2                    : ok=2    changed=0    unreachable=0    failed=0

As always the Ansible provisioner created an inventory file I can use in ./.vagrant/provisioners/ansible/inventory/vagrant_ansible_inventory. The inventory looks exactly as described in the |ansible| block, and it has the all important global variables as well.

$cat ./.vagrant/provisioners/ansible/inventory/vagrant_ansible_inventory
# Generated by Vagrant
server2 ansible_host=127.0.0.1 ansible_port=2201 ansible_user='vagrant' ansible_ssh_private_key_file='/path/to/key'
server1 ansible_host=127.0.0.1 ansible_port=2200 ansible_user='vagrant' ansible_ssh_private_key_file='/path/to/key'

[oracle_si]
server[1:2]

[oracle_si:vars]
install_rdbms=true
patch_rdbms=true

After ensuring both that machines are up using the “ping” module, I can run the actual playbook. You might have to confirm the servers’ ssh keys the first time you run this:

$ ansible -i ./.vagrant/provisioners/ansible/inventory/vagrant_ansible_inventory -m ping oracle_si
server1 | SUCCESS => {
"changed": false,
"ping": "pong"
}
server2 | SUCCESS => {
"changed": false,
"ping": "pong"
}

All good to go! Let’s call the actual playbook to provision my machines.

$ ansible-playbook -i ./.vagrant/provisioners/ansible/inventory/vagrant_ansible_inventory provisioning/oracle.yml 

TASK [Gathering Facts] ***************************************************************
ok: [server1]
ok: [server2]

TASK [test for Oracle Linux 7] *******************************************************
skipping: [server1]
skipping: [server2]

And we’re off to the races. Happy automating!

Using dbca to create a physical standby database

While investigating new options I discovered with dbca for the previous article I noticed that it’s now possible to use Oracle’s Database Creation Assistant (dbca) to create a physical standby database using the -createDuplicateDB flag.

I wanted to know how easily this can be done on my Oracle 18.5.0 system. I have 2 VMs, server3 and server4 running Oracle Linux 7.6. I created the primary database on server3. The database name is NCDB with the db_unique_name set to SITEA. I also use SITEA as the database SID to prevent confusion. My physical standby database with db_unique_name SITEB will reside on server4.

The setup is kept as simple as possible to avoid any unintended complications. In other words, this is a lab setup, not something you’d entertain using for any other purpose. As always, this is a tech post and not a piece about licencing. It is up to you to ensure you aren’t performing any tasks that you aren’t licensed for.

Creating the primary database

The primary database can be created using dbca as always. Unlike the last post, this one uses ASM, not a filesystem to show how easy it is to do both.

The primary database is a non-Container Database (NCDB). I intend to create the primary instance as SITEA (Site A) with the standby named SITEB. This way no one gets confused when the database runs in primary role in the designated disaster recovery location. I haven’t seen gdbName <> sid/db_unique_name in many single instance deployments and appreciate that might be unfamiliar but bear with me. I’ll try to make this as transparent as possible.

Invoking dbca

Enough talking, let’s create the database:

[oracle@server3 ~]$ dbca -silent -createDatabase \
> -gdbName NCDB \
> -templateName lab_template.dbc \
> -sid SITEA \
> -enableArchive true -archiveLogMode auto \
> -memoryMgmtType auto_sga \
> -createAsContainerDatabase false \
> -recoveryAreaDestination +RECO \
> -totalMemory 2048 \
> -datafileDestination +DATA \
> -initParams db_unique_name=SITEA,dg_broker_start=true \
> -customScripts add_srls_dbca.sql

[...]

Prepare for db operation
10% complete
Registering database with Oracle Restart
14% complete
Copying database files
43% complete
Creating and starting Oracle instance
45% complete
49% complete
53% complete
56% complete
62% complete
Completing Database Creation
68% complete
70% complete
71% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/SITEA.
Database Information:
Global Database Name:SITEA
System Identifier(SID):SITEA
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SITEA/SITEA1.log" for further details.

A few words of explanation: As I said I use SITEA as db_unique_name and the database’s SID. In Oracle Managed Files (OMF) deployments a file name references the db_unique name, and I wanted my data files, temp files and log files all to be in +DATA/SITEA/. db_unique_name defaults to db_name, and not changing it would have resulted in the database being created in +DATA/NCDB and that’s not what I wanted.

I decided to include starting the Data Guard broker as part of the initialisation parameters. Archiving is enabled right from the start as well.

The custom script you see referenced creates the necessary number of standby redo logs. This will save me a bit of work, because RMAN is smart. When duplicating the database for use as a standby it creates standby redo logs (SRL) for me if if finds SRLs in the target database. Yet another thing less for me to worry about.

Implications of using gdbname and sid (+ db_unique_name) in dbca

If you haven’t used a SID <> gDBName combination before, you might feel about unsure about how Oracle creates things. First of all, it created the database with the db_name you passed to dbca as gdbname. In this post that’s NCDB. Normally, if you grep’d the operating system’s process table for NCDB you would find all the instance’s background processes. With my dbca command however (where an Oracle SID <> gdbname is provided) you won’t find anything though. Grep for the ORACLE_SID “SITEA” instead. Likewise, if you want to create a bequeath connection to the database, set your ORACLE_SID to SITEA.

Since this is Oracle Restart the database is registered with Grid Infrastructure under its db_unique_name/ORACLE_SID combination in the RDBMS home:

[oracle@server3 ~]$ srvctl config database
SITEA
[oracle@server3 ~]$ srvctl config database -db SITEA
Database unique name: SITEA
Database name: NCDB
Oracle home: /u01/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/SITEA/PARAMETERFILE/spfile.273.1003226559
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,RECO
Services:
OSDBA group:
OSOPER group:
Database instance: SITEA
[oracle@server3 ~]$

Interacting with the database via srvctl requires you to use the db_unique_name SITEA as shown.

This is my preferred way to set things up these days. I’d like to invite you to critically reflect about the approach I took. I prefer it over other designations such as “PROD” and “STDBY”.

Creating the standby database

With the primary database created, archivelog mode enabled plus a working Fast Recovery Area I can move on to creating the physical standby database.

Before I do this a few small changes have to be made to my network configuration. I always add TNS entries for both primary and standby database in the “primary” host’s $ORACLE_HOME/network/admin/tnsnames.ora file and ship this across to the “standby” host. They are needed later. With the preparation done, I proceed to calling dbca:

[oracle@server4 ~]$ dbca -silent -createDuplicateDB \
> -gdbName NCDB \
> -sid SITEB \
> -initParams db_create_file_dest=+DATA,db_recovery_file_dest=+RECO,dg_broker_start=true \
> -primaryDBConnectionString server3:1521/SITEA \
> -createAsStandby \
> -dbUniqueName SITEB

[ ... ]

Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SITEB/SITEB1.log" for further details.

So far so good. What did dbca do? And more importantly, how did it create the standby database? I didn’t have a backup of NCDB/SITEA at the time I called dbca on server4 so it must have performed a duplicate … from active database. Looking at the dbca trace in $ORACLE_BASE/cfgtoollogs/dbca/SITEB I can confirm this. After wading through a lot of messages, here’s the relevant bit:

INFO: Mar 11, 2019 12:14:04 PM oracle.assistants.dbca.driver.backend.steps.RmanDuplicateStep executeImpl
INFO: Running rman duplicate command:run {
set newname for datafile 1 to new;
set newname for datafile 3 to new;
set newname for datafile 4 to new;
set newname for datafile 7 to new;
duplicate target database
for standby
from active database
dorecover
nofilenamecheck;
}

This might be an issue for some users because it requires a direct connection to the source database. I don’t know if RMAN might use the “from service … using backupset …” clause if you provided an offline backup.

The files I care about are all in the right place:

SQL> select name from v$datafile union all
2 select name from v$tempfile union all
3 select member from v$logfile union all
4 select value from v$parameter2 where name = 'control_files';

NAME
-------------------------------------------------------
+DATA/SITEB/DATAFILE/system.268.1003228125
+DATA/SITEB/DATAFILE/sysaux.273.1003228127
+DATA/SITEB/DATAFILE/undotbs1.272.1003228129
+DATA/SITEB/DATAFILE/users.271.1003228129
+DATA/SITEB/TEMPFILE/temp.262.1003228149
+DATA/SITEB/ONLINELOG/group_3.266.1003228137
+RECO/SITEB/ONLINELOG/group_3.310.1003228137
+DATA/SITEB/ONLINELOG/group_2.267.1003228137
+RECO/SITEB/ONLINELOG/group_2.258.1003228137
+DATA/SITEB/ONLINELOG/group_1.270.1003228135
+RECO/SITEB/ONLINELOG/group_1.259.1003228135
+DATA/SITEB/ONLINELOG/group_4.265.1003228137
+RECO/SITEB/ONLINELOG/group_4.311.1003228137
+DATA/SITEB/ONLINELOG/group_5.264.1003228139
+RECO/SITEB/ONLINELOG/group_5.312.1003228139
+DATA/SITEB/ONLINELOG/group_6.274.1003228139
+RECO/SITEB/ONLINELOG/group_6.313.1003228141
+DATA/SITEB/ONLINELOG/group_7.263.1003228147
+RECO/SITEB/ONLINELOG/group_7.314.1003228147
+DATA/SITEB/CONTROLFILE/current.260.1003228117
+RECO/SITEB/CONTROLFILE/current.260.1003228117

21 rows selected.

As reported by other bloggers, dbca creates a “temporary” listener that it doesn’t remove after the database creation process is completed. I specifically wanted to see if this is the case with Oracle Restart as well and yes, it is:

[oracle@server4 SITEB]$ ps -ef | grep tnslsnr
oracle 1125 1 0 10:27 ? 00:00:00 /u01/app/oracle/product/18.0.0/dbhome_1/bin/tnslsnr LISTENER20190318102721 -inherit
grid 5837 1 0 09:01 ? 00:00:00 /u01/app/grid/product/18.0.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 7588 31809 0 10:44 pts/1 00:00:00 grep --color=auto tnslsnr

The extra listener didn’t survive a reboot though and the processes should disappear eventually. dbca persists the configuration into $ORACLE_HOME/network/admin/listener.ora though, and those entries should probably be removed. This is especially true with Oracle Restart (and RAC for that matter) because I want all listeners to be controlled from the GRID home, not the RDBMS home.

There are other leftover files in $ORACLE_HOME/dbs as well for which I’d hope dbca removes them one day.

On the plus side dbca registers the database in Oracle Restart for me as part of the work it did:

[oracle@server4 ~]$ srvctl config database -db SITEB
Database unique name: SITEB
Database name: NCDB
Oracle home: /u01/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/SITEB/PARAMETERFILE/spfile.275.1003228201
Password file: +DATA/SITEB/orapwsiteb
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,RECO
Services:
OSDBA group:
OSOPER group:
Database instance: SITEB

It’s good to see that I don’t need to worry about storing password file and spfile in ASM myself, so that job is already taken care off. A few other things need to be changed though. The database is still considered a primary (of which I know it isn’t) and the start option therefore is wrong, too. There are licensing implications opening a standby read only rather than just mounting it when turning on redo apply. Refer to the documentation for more details.

Out of habit I changed the role to PHYSICAL_STANDBY and the start option to MOUNT. This is what I ended up with in my lab, YMMV and depends on how you are licensed.

[oracle@server4 ~]$ srvctl config database -db SITEB
Database unique name: SITEB
Database name: NCDB
Oracle home: /u01/app/oracle/product/18.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/SITEB/PARAMETERFILE/spfile.275.1003228201
Password file: +DATA/SITEB/orapwsiteb
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: DATA,RECO
Services:
OSDBA group:
OSOPER group:
Database instance: SITEB

Creating the Broker configuration

The standby redo logs have been created by RMAN and with dg_broker_start set to true by dbca for both databases I can now create a Data Guard Broker configuration. With Oracle Restart I don’t have to statically register the databases with the listener anymore. Clusterware takes care of starting/stopping them during role changes.

[oracle@server3 ~]$ dgmgrl
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Mar 18 11:11:55 2019 Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@SITEA
Password:
Connected to "SITEA"
Connected as SYSDBA.
DGMGRL> create configuration autoconf
> primary database is 'SITEA'
> connect identifier is 'SITEA';
Configuration "autoconf" created with primary database "SITEA"
DGMGRL> add database 'SITEB' as connect identifier is 'SITEB';
Database "SITEB" added
DGMGRL> edit database 'SITEA' set property standbyfilemanagement=auto;
Property "standbyfilemanagement" updated
DGMGRL> edit database 'SITEB' set property standbyfilemanagement=auto;
Property "standbyfilemanagement" updated
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - autoconf

Protection Mode: MaxPerformance
Members:
SITEA - Primary database
SITEB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 17 seconds ago)
DGMGRL>

You might want to increase the protection level from MaxPerformance to something that better suits your requirements. Three might be additional changes you want to do within the broker as well. The code output you just saw is the bare minimum I put into place.

Summary

All in all this has been pretty straight forward without too many hiccups along the way. If you want to you can put a lot of those steps into your favourite automation toolkit and you’ll end up with a standby database reasonably quickly provided your environment supports this kind of setup.