Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 1 hour 16 min ago

An awk filter to truncate or wrap around tabular output

Thu, 2018-05-31 04:47

In my previous blog “idql and its column output”, see link here, I provided a small awk filter to reflow the idql output’s columns by wrapping their content around. Later I came of thinking that it could be useful to be able to truncate the columns instead, in order to have an even compacter output. A shorter, denser table can certainly help is some cases, such as when a quick overview of a query’s result is enough to get an idea of its correctness or its performance.
Of course, in case of truncation, a user-definable trailing string, defaulting to the ellipsis, would be appended to show that a truncation has been applied; this is to avoid to present incomplete and therefore incorrect data to an unsuspecting or distracted reader.
So, here is the script:

# Usage:
#    gawk [-v maxw=nn] [-v truncate=0|1] [-v ellipsis=...] [-v wrap=1|0] -f compact_wwa.awk file
# or:
#    cmd | gawk [-v maxw=nn] [-v truncate=0|1] [-v ellipsis=...] [-v wrap=1|0] -f compact_wwa.awk
# where:
#     missing numeric parameters assume the value 0;
#     maxw is the maximum column width and defaults to 32;
#         characters outside this limit are either wrapped around in their own column or truncated, depending on the parameters truncate and wrap;
#     wrapping is the default and has priority over truncating;
#     truncate=0 wrap=0 --> wrapping around;
#     truncate=0 wrap=1 --> wrapping around;
#     truncate=1 wrap=0 --> truncate;
#     truncate=1 wrap=1 --> wrapping around;
#     truncating is therefore only done when explicitly and unambiguously requested; this is to preserve data integrity whenever possible;
#     ellipsis string is only considered when truncating and defaults to '...'; thus, there is always one to warn of truncation;
# example:
#     gawk -v maxw=50 -f compact_wwa.awk tmp_file  | less -S
#     C. Cervini, dbi-services;
BEGIN {
   if (!maxw) maxw = 32
   if (!truncate)
      if (!wrap)
         wrap = 1
      else;
   else if (!wrap);
   else
      truncate = 0
   if (!ellipsis)
      ellipsis = "..."
   while (getline && !match($0, /^([0-9]+> )+/));
   header = substr($0, RLENGTH + 1)
   getline
   nbFields = NF
   fs[0] = 0; fw[0] = -1 # just so that fs[1] = 1, see below;
   headerLine = ""; sepLine = ""
   for (i = 1; i <= NF; i++) {
      fs[i] = fs[i - 1] + fw[i - 1] + 2
      fw[i] = length($i)
      Min = min(fw[i], maxw)
      if (1 == truncate)
         Min = max(Min, length(ellipsis))
      sepLine = sepLine sprintf("%s  ", substr($0, fs[i], Min))
   }
   printWithWA(header)
   printf("%s\n", sepLine)
}
{
   if (match($0, /^\([0-9]+ rows? affected\)/)) {
      print
      exit
   }
   printWithWA($0)
}
function printWithWA(S) {
   do {
      left_over = ""
      for (i = 1; i <= nbFields; i++) {
         Min = min(fw[i], maxw)
         if (1 == truncate)
            Min = max(Min, length(ellipsis))
         columnS = substr(S, fs[i], Min)
         if (1 == truncate) {
            restColumn = substr(S, fs[i] + Min, fw[i] - Min); gsub(/ +$/, "", restColumn)
            if (restColumn)
               columnS = substr(columnS, 1, length(columnS) - length(ellipsis)) ellipsis
         }
         printf("%s  ", columnS)
         restS = substr(S, fs[i] + Min, fw[i] - Min)
         if (length(restS) > 0) {
            left_over = left_over sprintf("%-*s  ", fw[i], restS)
         }
         else
            left_over = left_over sprintf("%*s  ", fw[i], "")
      }
      printf "\n"
      gsub(/ +$/, "", left_over)
      S = left_over
   } while (left_over && wrap)
}
function min(x, y) {
   return(x <= y ? x : y)
}
function max(x, y) {
   return(x >= y ? x : y)
}

Example of execution

idql dmtest -Udmadmin -Pdmadmin -w500 <<EoQ | gawk -v maxw=25 -v truncate=1 -v ellipsis="(...)" -f compact_wwa.awk | less -S
select r_object_id, user_name, user_os_name, user_address, user_group_name, user_privileges, owner_def_permit, world_def_permit, group_def_permit, default_folder, user_db_name, description,
acl_domain, acl_name, user_os_domain, home_docbase, user_state, client_capability, globally_managed, user_delegation, workflow_disabled, alias_set_id, user_source, user_ldap_dn, user_xprivileges,
failed_auth_attempt, user_admin, user_global_unique_id, user_login_name, user_login_domain, user_initials, USER_PASSWORD, user_web_page, first_failed_auth_utc_time, last_login_utc_time,
deactivated_utc_time, deactivated_ip_addr, root_log_dir
from
dm_user
go
exit
EoQ

Result:
blog10
blog11
Here the ellipsis string has been set to “(…)”.
One could wonder why there are 2 apparently contradictory options to specify the wrapping, truncate and wrap. Wouldn’t one be enough since truncate = non wrap ? And it would also remove the 7 lines of validation and prioritization at the beginning. One of the reason is comfort as it is often easier to think in positive rather than in negative logic. The other is that both options are not exactly opposite; the opposite of truncate can be “do nothing” as well as “wrap around”, and vice-versa. It is better to be explicit rather than implicit so to avoid any ambiguity.
What if a do-nothing filter is needed while still calling compact_wwa.awk (have I said that wwa stands for “with wrap-around” ?), e.g. from another script that passes the command-line options to it ? Just specify some large value for maxw and that’s it. As its name suggests it, compact_wwa.awk only attempts to shrink columns if larger than maxw; narrower columns are not modified.

Possible emprovements

There are still lots of ways to enhance this script. For instance, one of them is to provide a maxw per column. Here, the parameter maxw is applied to all the columns but it would be nice to compress (whether by truncating or by wrapping around) only the uninteresting columns so to have more space for the relevant ones.
Another way would be to make the script work both ways, enlarging columns as well as shrinking them, which could sometimes simplify their parsing since they’ll all have the same width. Maybe the next time…

Wrapping up

I think this script can prove useful to those who need to work with idql and have to deal with its limits. Countless times in the past, I had to open a DQL extraction’s output in UltraEdit to manually shrink all those columns. Too bad that I didn’t have this little utility before, it could have spared me tons of time doing all this tedious work.

 

Cet article An awk filter to truncate or wrap around tabular output est apparu en premier sur Blog dbi services.

Adding a Documentum Extension to gawk, part II

Thu, 2018-05-31 04:41

This is part II of the article “Adding a Documentum Extension to gawk”. You can find Part I here
Before we can test the extension, we need a test program and some helper functions for comfort. Let’s prepare them.
o  Move back to the dmgawk directory and edit DctmAPI.awk, the wrapper functions;

$ cd ../../..
$ pwd
/home/dmadmin/gawk
$ vi DctmAPI.awk

o  Cut and paste the lines below:

@load "dctm"

# provides high-level function to do Documentum stuff;
# set environment variable $DOCUMENTUM to where the dmcl.ini file is and $LD_LIBRARY_PATH to the location of libdmcl40.so;
# this library file is made available to gawk scripts by @include-ing in it;
# the environment variable AWKPATH can be set to the path containing the included awk sources;
# Cesare Cervini
# dbi-services.com
# 5/2018

dmLogLevel = 1

function dmShow(mesg) {
# displays the message msg if allowed
   if (dmLogLevel > 0)
      print mesg
}

function dmConnect(docbase, user_name, password) {
# connects to given docbase as user_name/password;
# returns a session id if OK, an empty string otherwise
   dmShow("in connect(), docbase = " docbase ", user_name = " user_name ", password = " password)
   session = dmAPIGet("connect," docbase "," user_name "," password)
   if (!session) {
      print "unsuccessful connection to docbase " docbase " as user " user_name
      return "" 
   }
   else {
      dmShow("successful session " session)
      dmShow(dmAPIGet("getmessage," session))
   }
   dmShow("exiting connect()")
   return session
}

function dmExecute(session, dql_stmt) {
# execute non-SELECT DQL statements;
# returns 1 if OK, 0 otherwise;
   dmShow("in dmExecute(), dql_stmt=" dql_stmt)
   query_id = dmAPIGet("query," session "," dql_stmt)
   if (!query_id) {
      dmShow(dmAPIGet("getmessage," session))
      return 0
   }
   if (!dmAPIExec("close," session "," query_id)) {
      dmShow(dmAPIGet("getmessage," session))
      return 0
   }
   dmAPIGet("getmessage," session)
   return 1
}

function dmSelect(session, dql_stmt, attribute_names) {
# execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
# attributes_names is a list of attributes to extract from the result set;
# return 1 if OK, 0 otherwise;
   dmShow("in dmSelect(), dql_stmt=" dql_stmt)
   query_id = dmAPIGet("query," session "," dql_stmt)
   if (!query_id) {
      dmShow(dmAPIGet("getmessage," session))
      return 0
   }

   s = ""
   nb_attrs = split(attribute_names, attributes_tab, " ")
   for (attr = 1; attr <= nb_attrs; attr++)
      s = s "[" attributes_tab[attr] "]\t"
   print s
   resp_cntr = 0
   while (dmAPIExec("next," session "," query_id) > 0) {
      s = ""
      for (attr = 1; attr <= nb_attrs; attr++) {
         value = dmAPIGet("get," session "," query_id "," attributes_tab[attr])
         if ("r_object_id" == attributes_tab[attr] && !value) {
            dmShow(dmAPIGet("getmessage," session))
            return 0
         }
         s= s "[" (value ? value : "NULL") "]\t"
      }
      resp_cntr += 1
      dmShow(sprintf("%d: %s", resp_cntr, s))
   }
   dmShow(sprintf("%d rows iterated", resp_cntr))

   if (!dmAPIExec("close," session "," query_id)) {
      dmShow(dmAPIGet("getmessage," session))
      return 0
   }

   return 1
}

function dmDisconnect(session) {
# closes the given session;
# returns 1 if no error, 0 otherwise;
   dmShow("in dmDisconnect()")
   status = dmAPIExec("disconnect," session)
   if (!status)
      dmShow("Exception in dmDisconnect():")
   else
      dmShow("exiting disconnect()")
   return status
}

o  Ditto for the test program tdctm.awk;
$ vi tdctm.awk

# test program for DctmAPI.awk and the interface dctm.c;
# Cesare Cervini
# dbi-services.com
# 5/2018

@include "DctmAPI.awk"

BEGIN {
   dmLogLevel = 1

   status = dmAPIInit()
   printf("dmAPIInit(): %d\n", status)
   if (status)
      print("dmAPIInit() was successful")
   else
      print("dmAPIInit() was not successful")
 
   printf "\n"
   session = dmConnect("dmtest", "dmadmin" , "dmadmin")
   printf("dmConnect: session=%s\n", session)
   if (!session) {
      print("no session opened, exiting ...")
      exit(1)
   }

   printf "\n"
   dump = dmAPIGet("dump," session ",0900c35080008107")
   print("object 0900c35080008107 dumped:\n" dump)

   printf "\n"
   stmt = "update dm_document object set language_code = 'FR' where r_object_id = '0900c35080008107'"
   status = dmExecute(session, stmt)
   if (status)
      print("dmExecute [" stmt "] was successful")
   else
      print("dmExecute [" stmt "] was not successful")

   printf "\n"
   stmt = "select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document"
   status = dmSelect(session, stmt, "r_object_id object_name owner_name acl_domain acl_name")
   if (status)
      print("dmSelect [" stmt "] was successful")
   else
      print("dmSelect [" stmt "] was not successful")

   printf "\n"
   stmt = "select count(*) from dm_document"
   status = dmSelect(session, stmt,  "count(*)")
   if (status)
      print("dmSelect [" stmt "] was successful")
   else
      print("dmSelect [" stmt "] was not successful")

   printf "\n"
   status = dmDisconnect(session)
   if (status)
      print("successfully disconnected")
   else
      print("error while  disconnecting")

   printf "\n"
   status = dmAPIDeInit()
   if (status)
      print("successfully deInited")
   else
      print("error while  deInited")

   exit(0)
}

o  Let’s test now !
first, set the needed variables for the Documentum environment, if still not done;
$ export DOCUMENTUM=/home/dmadmin/documentum
$ export LD_LIBRARY_PATH=$DOCUMENTUM/product/7.3/bin

o  Then, we need to tell the new gawk where to find the dynamic extension and the awk wrapper;
the environment variables are AWKLIBPATH and AWKPATH respectively;
let’s define those variables on-the-fly for a change:

$ AWKLIBPATH=gawk-4.2.1/extension/.libs AWKPATH=./ gawk-4.2.1/gawk -f ./tdctm.awk

o  Produced output:

dmAPIInit(): 1
dmAPIInit() was successful
 
in connect(), docbase = dmtest, user_name = dmadmin, password = dmadmin
successful session s0
[DM_SESSION_I_SESSION_START]info: "Session 0100c3508000d6d6 started for user dmadmin."
exiting connect()
dmConnect: session=s0
 
object 0900c35080008107 dumped:
USER ATTRIBUTES
 
object_name : 4/10/2018 20:09:28 dm_DBWarning
title : Result of dm_method(dm_DBWarning) with status code (0)
subject : Result of dm_method(dm_DBWarning) with command line: ./dmbasic -f../install/admin/mthd4.ebs -eDBWarning -- -docbase_name dmtest.dmtest -user_name dmadmin -job_id 0800c3508000035f -method_....
authors []:
keywords []:
resolution_label :
owner_name : dmadmin
owner_permit : 7
group_name : docu
group_permit : 5
world_permit : 3
log_entry :
acl_domain : dmadmin
acl_name : dm_4500c35080000101
language_code : FR
 
SYSTEM ATTRIBUTES
 
r_object_type : dm_document
r_creation_date : 4/10/2018 20:09:41
r_modify_date : 5/13/2018 22:09:06
r_modifier : dmadmin
r_access_date : nulldate
r_composite_id []:
r_composite_label []:
r_component_label []:
r_order_no []:
...
 
i_is_replica : F
i_vstamp : 200
 
in dmExecute(), dql_stmt=update dm_document object set language_code = 'FR' where r_object_id = '0900c35080008107'
dmExecute [update dm_document object set language_code = 'FR' where r_object_id = '0900c35080008107'] was successful
 
in dmSelect(), dql_stmt=select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document
[r_object_id] [object_name] [owner_name] [acl_domain] [acl_name] 1: [0900c350800001d0] [Default Signature Page Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 2: [6700c35080000100] [CSEC Plugin] [dmadmin] [dmadmin] [dm_4500c35080000101] 3: [6700c35080000101] [Snaplock Connector] [dmadmin] [dmadmin] [dm_4500c35080000101] 4: [0900c350800001ff] [Blank Word 2007 / 2010 Document] [dmadmin] [dmadmin] [dm_4500c35080000101] 5: [0900c35080000200] [Blank Word 2007 / 2010 Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 6: [0900c35080000201] [Blank Word 2007 / 2010 Macro-enabled Document] [dmadmin] [dmadmin] [dm_4500c35080000101] 7: [0900c35080000202] [Blank Word 2007 / 2010 Macro-enabled Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 8: [0900c35080000203] [Blank Excel 2007 / 2010 Workbook] [dmadmin] [dmadmin] [dm_4500c35080000101] 9: [0900c35080000204] [Blank Excel 2007 / 2010 Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 10: [0900c350800001da] [11/21/2017 16:31:10 dm_PostUpgradeAction] [dmadmin] [dmadmin] [dm_4500c35080000101] ...
885: [0900c35080005509] [11/30/2017 20:11:05 dm_DataDictionaryPublisher] [dmadmin] [dmadmin] [dm_4500c35080000101] 886: [0900c3508000611d] [12/11/2017 19:40:05 dm_DataDictionaryPublisher] [dmadmin] [dmadmin] [dm_4500c35080000101] 887: [0900c35080006123] [12/11/2017 20:10:08 dm_DataDictionaryPublisher] [dmadmin] [dmadmin] [dm_4500c35080000101] 888: [0900c3508000612d] [12/11/2017 20:30:07 dm_UpdateStats] [dmadmin] [dmadmin] [dm_4500c35080000101] 888 rows iterated
dmSelect [select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document] was successful
 
in dmSelect(), dql_stmt=select count(*) from dm_document
[count(*)] 1: [888] 1 rows iterated
dmSelect [select count(*) from dm_document] was successful
 
in dmDisconnect()
exiting disconnect()
successfully disconnected
 
successfully deInited

That’s it, enjoy your new dmgawk!

Next steps

Now that the extension has proved to be usable, it could be interesting to deploy it system-wide. This can be done by the generated Makefile at the root directory of the package as follows:

$ sudo make install

The loadable extensions are stored in /usr/local/lib/gawk:

$ ll /usr/local/lib/gawk
total 504
-rwxr-xr-x 1 root root 124064 May 13 16:38 filefuncs.so
...
-rwxr-xr-x 1 root root 26920 May 13 16:38 time.so
-rwxr-xr-x 1 root root 31072 May 13 16:38 dctm.so

As this is the standard place to look for them, there no need to specify the AWKLIBPATH anymore.
There is also a standard place for awk utility scripts, /usr/local/share/awk. If DctmAPI.awk is installed there, it becomes available to anybody from anywhere on the system and setting AWKPATH is not needed any more. In addition, if /usr/local/bin is in the righteous $PATH, the newly extended gawk is accessible directly, along with its extensions and its ancillary scripts, e.g.:
$ gawk -f dmgawk/tdctm.awk
You may also want to define an alias dmawk pointing to the extended gawk:
$ alias dmawk=/usr/local/bin/gawk
or you may want to name it dmgawk so you still can access the original dmawk:
$ alias dmgawk=/usr/local/bin/gawk

I’m not finished yet!

I started this mini-project on a 32-bit Ubuntu VM with the Documentum v5.3 libdmcl40.so library. Once it proved working, I replayed all the steps on a 64-bit Ubuntu VM with the Documentum v7.3 binaries. There, as already noticed in the past, the library libdmcl40.so kept crashing the executable that loads it at run-time, gawk here, so I successfully resorted to libdmcl.so, the one that calls java code behind the scenes through JNI. It is a bit slower at start up but at least does work as expected. It is very likely that the ancient libdmcl40.so is going to be retired in some future release of the content server, so be prepared to switch.
You may remember that I edited the automake Makefile.am to have the extension compiled, and that I had to relink said extension manually with the libdmcl.so library. A better way would be to provide it with its own makefile to automate those steps. This way, no original gawk source file would be altered.

Conclusion, finally

This test program and wrapper are actually the gawk counterparts of the Documentum extension for python I wrote earlier for this blog (check it here). With these 2 extensions, an administrator does not have any longer to be stuck with a limited and, even worse, stagnant tool. The awk language is easy and well suited to the kind of work administrators do with Documentum. Its gawk implementation is powerful, open, very well maintained and, as shown, easily extensible so there is no reason not to use it.
For licensing reasons, the interface cannot be distributed by Documentum but anybody can install it privately without restriction.

 

Cet article Adding a Documentum Extension to gawk, part II est apparu en premier sur Blog dbi services.

Adding a Documentum Extension to gawk, part I

Thu, 2018-05-31 04:41

Recently, I was searching my NAS for some files which, to end this intolerable suspense, I did not find but on the other hand I did stumbled across a couple of interesting dmawk scripts I wrote for on customer more than 20 years ago. Some statements looked a bit odd, e.g. access to elements from multi-dimensional arrays such as “a[i1][i2]”, or “delete A” to empty an array (instead of the well-known awk idiom split(“”, A)). After a while, I understood what was going on here: the dmawk that came with content server v3.x was formerly based on the GNU dialect of awk named gawk. It was already a more powerful interpreter than the standard AT&T awk available on the Unix I was using, namely HP-UX, and nowadays it has become even better, as you can see by yourself by checking the official manual here.

At that time, it already allowed to be extended, which Documentum took profit of by turning gawk into a DMCL client, dmawk. However, it was quite a tedious task because it required hacking deeply into the source code. When years later I was playing with this and trying to add Oracle connectivity to gawk (and turn it into an Oracle OCI-based client, oragawk ;-), I remember for instance one step that required the symbol table to be edited in order to add the new functions, and possibly their implementation code inserted in the bison file; finally, the whole gawk source had to be recompiled and relinked. Tedious yet bearable as it didn’t prevent passionate people from forking custom versions with useful new functionalities such as xml processing in xgawk.

Over the years, starting with v4.1.1, gawk has evolved a new, much easier mechanism for adding extensions (aka plugins). It is named dynamic extension (see documentation here); it lets one load shared libraries at run-time and invoke their functions from within a gawk script through a minimum interface to be provided; the other way around, i.e. callbacks, is also possible for the brave, or really motivated, ones. Several powerful extensions have been developed through this system such as: json serialization from/to an associative array (useful e.g. to prepare parameters to pass to javascript functions such as the ones in HighChart or flot libraries), arbitrary-precision integer and floating point support, postgresql database access, etc. (for a list of current extensions, see here). If you have a recent gawk compiled with the MPFR extension, try for example the command below:
gawk -M -v prec=16000 'BEGIN{PREC = prec; printf("a big fp number:\n%f\n\n", 3.0**10**4); n = 3**10**4; print "a large integer number:\n" n; print "it has " length(n) " digits"; exit}'
Impressive, isn’t ?
To know if your local gawk has the option, ask it so:
gawk --version
GNU Awk 4.1.3, API: 1.1 (GNU MPFR 3.1.4, GNU MP 6.1.0)
Copyright (C) 1989, 1991-2015 Free Software Foundation.

This one has it.

So why hasn’t Documentum kept up with gawk and gone for the standard, lesser awk instead ? Maybe because of a licensing issue ? Gawk is protected by the GNU GPL (see its full text in the gawk manual above) which mandates not only its modified source code but also all extension code statically or dynamically linked with it to be provided also in source code form and included in the delivered package, and maybe this was disturbing at the time. Now that open source is omnipresent, from O/Ses to RDBMS, from the network software to WEB development frameworks, from ERP to CRM software, it is so common that even a restrictive license such as the GPL does not shock anyone today. Linux itself, or GNU/Linux as some like to call it, contains a massive amount of GPL-ed software, linux per se being limited to the kernel, and is under the GPL itself. Thus, in the new extension mechanism, the GPL requires to publish the source code of not only the interface to be linked with gawk but also of the proprietary shared library libdmcl40.so (see the definition of Corresponding Source) that is loaded at run-time; but wait: since libdmcl40.so tightly uses other proprietary Documentum libraries, their source code would also need to be published, and so on, transitively. Obviously, this licensing is by design not favorable to closed source, which is exactly the reason d’être of the FSF (aka, the Free Software Foundation). If Documentum chose not to go the GPL way at that time with the simpler EDMS, it is very unlikely that it will in the future unless it drastically changes its business model and become, say, the Red Hat of document management!

Fortunately, there are no reasons to hold one’s breath until that day for I propose here a simple, straightforward interface between gawk and the Documentum libdmcl40.so/libdmcl.so run-time libraries which will not violate the GPL as nothing closed source is distributed. Its usage is exactly the same as documented in the API reference manual. I’ll show you hereafter how to prepare and use it. Since this article will be quite long, I’ll split it in two parts. Part I, you’re reading it, presents the interface and explains how to compile it as a gawk extension. Part II here will deal with a wrapper around that interface providing a few helper functions and a test program to show how to use the extension.

Installation

o  Check the currently installed version of gawk;

$ gawk --version
GNU Awk 4.1.3, API: 1.1 (GNU MPFR 3.1.4, GNU MP 6.1.0)
Copyright (C) 1989, 1991-2015 Free Software Foundation.

o  This is an old release; let’s take this opportunity to upgrade to the current latest, the 4.2.1;
    prepare a working directory; the name “dmgawk” fits it well;
$ mkdir ~/dmgawk; cd ~/dmgawk

o  Let’s get the latest source code of gawk, it’s the release 4.2.1;
$ wget http://ftp.gnu.org/gnu/gawk/gawk-4.2.1.tar.gz
$ tar -xpvzf gawk-4.2.1.tar.gz
$ cd gawk-4.2.1

o  compile it;
$ ./configure
$ make

o  Check the new version;
$ ./gawk --version
GNU Awk 4.2.1, API: 2.0
Copyright (C) 1989, 1991-2018 Free Software Foundation.

o  Note that the MPFR and GMP extensions were not linked in; that’s because those libraries were not present on my system;
    to have them, just download, compile and install them, and then run gawk’s ./configure and make again;
    fine so far; we’ll extend this local version; whether it will or can be installed system-wide is up to you;
    move to the extensions directory;
    edit the automake Makefile.am and add the highlighted references below to the new interface, let’s call it dctm.c;
$ cd extension
$ vi Makefile.am
pkgextension_LTLIBRARIES = \
filefuncs.la \
...
time.la \
dctm.la
...
time_la_SOURCES = time.c
time_la_LDFLAGS = $(MY_MODULE_FLAGS)
time_la_LIBADD = $(MY_LIBS)
 
dctm_la_SOURCES = dctm.c
dctm_la_LDFLAGS = $(MY_MODULE_FLAGS)
dctm_la_LIBADD = $(MY_LIBS)


o  save and quit; that’s all for the make file;
    let’s edit the interface dctm.c now and insert the code below;
$ vi dctm.c

/*
 * dctm.c - Builtin functions that provide an interface to Documentum dmapp.h;
 * see dmapp.h for description of functions; 
 *
 * Cesare Cervini
 * dbi-services.com
 * 5/2018
 * go to .libs and and link dmcl.o it with the Documentum library with: gcc -o dctm.so -shared dctm.o path-to-the-shared-library/libdmcl40.so;
 */
#ifdef HAVE_CONFIG_H
#include <config.h>
#endif

#include <stdio.h>
#include <assert.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>

#include <sys/types.h>
#include <sys/stat.h>

#include "gawkapi.h"

#include "gettext.h"
#define _(msgid)  gettext(msgid)
#define N_(msgid) msgid

/* make it point to the Documentum dmapp.h on your system */
#include "/home/dmadmin/documentum/share/sdk/include/dmapp.h"

static const gawk_api_t *api;	/* for convenience macros to work */
static awk_ext_id_t ext_id;
static const char *ext_version = "dctm extension: version 1.0";
static awk_bool_t (*init_func)(void) = NULL;

int plugin_is_GPL_compatible;

/*  do_dmAPIInit */
static awk_value_t *
do_dmAPIInit(int nargs, awk_value_t *result, struct awk_ext_func *unused) {
   unsigned int ret = 0;

   assert(result != NULL);

   ret = dmAPIInit();
   ret &= 0xff;

   return make_number(ret, result);
}

/*  do_dmAPIDeInit */
static awk_value_t *
do_dmAPIDeInit(int nargs, awk_value_t *result, struct awk_ext_func *unused) {
   unsigned int ret = 0;

   assert(result != NULL);

   ret = dmAPIDeInit();
   ret &= 0xff;

   return make_number(ret, result);
}

/*  do_dmAPIExec */
static awk_value_t *
do_dmAPIExec(int nargs, awk_value_t *result, struct awk_ext_func *unused) {
   awk_value_t str;
   unsigned int ret = 0;

   assert(result != NULL);

   if (get_argument(0, AWK_STRING, & str)) {
      ret = dmAPIExec(str.str_value.str);
      ret &= 0xff;
   } else if (do_lint)
      lintwarn(ext_id, _("dmAPIExec: called with inappropriate argument(s)"));

   return make_number(ret, result);
}

/*  do_dmAPIGet */
static awk_value_t *
do_dmAPIGet(int nargs, awk_value_t *result, struct awk_ext_func *unused) {
   awk_value_t str;
   char *got_value;

   assert(result != NULL);

   if (get_argument(0, AWK_STRING, & str)) {
      got_value = dmAPIGet(str.str_value.str);
   } else if (do_lint)
      lintwarn(ext_id, _("dmAPIGet: called with inappropriate argument(s)"));

   make_const_string(got_value == NULL ? "" : got_value, strlen(got_value), result);
   return result;
}

/*  do_dmAPISet */
static awk_value_t *
do_dmAPISet(int nargs, awk_value_t *result, struct awk_ext_func *unused) {
   awk_value_t str1;
   awk_value_t str2;
   unsigned int ret = 0;

   assert(result != NULL);

   if (get_argument(0, AWK_STRING, & str1) && get_argument(0, AWK_STRING, & str2)) {
      ret = dmAPISet(str1.str_value.str, str2.str_value.str);
      ret &= 0xff;
   } else if (do_lint)
      lintwarn(ext_id, _("dmAPISet: called with inappropriate argument(s)"));

   return make_number(ret, result);
}

/* these are the exported functions along with their min and max arities; */
static awk_ext_func_t func_table[] = {
	{ "dmAPIInit",   do_dmAPIInit, 0, 0, awk_false, NULL },
	{ "dmAPIDeInit", do_dmAPIDeInit, 0, 0, awk_false, NULL },
	{ "dmAPIExec",   do_dmAPIExec, 1, 1, awk_false, NULL },
	{ "dmAPIGet",    do_dmAPIGet, 1, 1, awk_false, NULL },
	{ "dmAPISet",    do_dmAPISet, 2, 2, awk_false, NULL },
};

/* define the dl_load function using the boilerplate macro */

dl_load_func(func_table, dctm, "")

o  Again, run configure and build the extensions;
$ pwd
/home/dmadmin/dmgawk/gawk-4.2.1/extension
$ ./configure
$ make

o  The extensions’ object files and shared libraries are actually contained in the hidden .libs directory below extension; so move there and check the generated dctm.so library;

$ cd .libs
$ ldd dctm.so
linux-vdso.so.1 => (0x00007ffc1d7e5000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f3452930000)
/lib64/ld-linux-x86-64.so.2 (0x00007f3452efd000)

o  The library has still no reference to libdmcl.so; let’s link it with it and check again;
    find that Documentum library on your system; on mine, it’s in /home/dmadmin/documentum/product/7.3/bin;
$ gcc -o dctm.so -shared dctm.o /home/dmadmin/documentum/product/7.3/bin/libdmcl.so
$ ldd dctm.so
linux-vdso.so.1 => (0x00007fff55dbf000)
/home/dmadmin/documentum/product/7.3/bin/libdmcl.so (0x00007fece91bb000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fece8df1000)
libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007fece8bb9000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fece899c000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fece861a000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fece8311000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fece80fb000)
/lib64/ld-linux-x86-64.so.2 (0x00007fece95ca000)

Good, so far dctm.so has been linked with libdmcl.so and thus is able to access the dmAPI*() functions. gawk let us load dynamic extensions through two ways:
1. the statement @load “name_of_extension” inserted in the client awk script;
2. the -l | –load “name_of_extension” command-line options
Please, turn now to part II here for the rest of this article.

 

Cet article Adding a Documentum Extension to gawk, part I est apparu en premier sur Blog dbi services.

OpenShift on my Windows 10 laptop with MiniShift

Thu, 2018-05-31 01:23

If you want to play with OpenShift on your laptop, you can, in a Virtual Machine. I have VirtualBox installed on my laptop. I’ll install Minishift here, which will create the VM to run OpenShift with few simple commands only. On Linux you can refer to Daniel’s post. Here is the Windows version. Oh, and Daniel did that to run Postgres but my goal is to run an Oracle container of course. Or MySQL maybe.

I’ve downloaded minishift-1.18.0-windows-amd64.zip and unzipped it in D:\Downloads\minishift-1.18.0-windows-amd64 where I have minishift.exe

Minishift

I configure to use VirtualBox

minishift config set vm-driver virtualbox

It is installed in my Windows profile:

C:\Users\fpa\.minishift

Be careful, minishift do not know that we have multiple drives in Windows. When I was running minishift.exe from the D: disk is was not able to find the virtual machine’s files that were on C:

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>minishift start
-- Starting profile 'minishift'
-- Checking if https://github.com is reachable ... OK
-- Checking if requested OpenShift version 'v3.9.0' is valid ... OK
-- Checking if requested OpenShift version 'v3.9.0' is supported ... OK
-- Checking if requested hypervisor 'virtualbox' is supported on this platform ... OK
-- Checking if VirtualBox is installed ... OK
-- Checking the ISO URL ... OK
-- Downloading OpenShift binary 'oc' version 'v3.9.0'
40.81 MiB / 40.81 MiB [===================================================================================] 100.00% 0s-- Downloading OpenShift v3.9.0 checksums ... OK
-- Checking if provided oc flags are supported ... OK
-- Starting local OpenShift cluster using 'virtualbox' hypervisor ...
-- Minishift VM will be configured with ...
Memory: 2 GB
vCPUs : 2
Disk size: 20 GB
-- Starting Minishift VM ..... FAIL E0529 17:08:31.056327 1448 start.go:391] Error starting the VM: Error creating the VM. Error creating machine: Error in driver during machine creation: open /Users/fpa/.minishift/cache/iso/b2d/v1.3.0/minishift-b2d.iso: The system cannot find the path specified.. Retrying.
Error starting the VM: Error creating the VM. Error creating machine: Error in driver during machine creation: open /Users/fpa/.minishift/cache/iso/b2d/v1.3.0/minishift-b2d.iso: The system cannot find the path specified.

Then, I changed to the C: drive

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>C:
 
C:\Users\fpa>dir \Users\fpa\.minishift\cache\iso\b2d\v1.3.0\
Volume in drive C is OS
Volume Serial Number is 26AE-33F7
 
Directory of C:\Users\fpa\.minishift\cache\iso\b2d\v1.3.0
 
29-May-18 15:22 .
29-May-18 15:22 ..
29-May-18 15:22 41,943,040 minishift-b2d.iso
1 File(s) 41,943,040 bytes
2 Dir(s) 30,652,370,944 bytes free

And I run minishift from there:

C:\Users\fpa>D:minishift start
-- Starting profile 'minishift'
-- Checking if https://github.com is reachable ... OK
-- Checking if requested OpenShift version 'v3.9.0' is valid ... OK
-- Checking if requested OpenShift version 'v3.9.0' is supported ... OK
-- Checking if requested hypervisor 'virtualbox' is supported on this platform ... OK
-- Checking if VirtualBox is installed ... OK
-- Checking the ISO URL ... OK
-- Checking if provided oc flags are supported ... OK
-- Starting local OpenShift cluster using 'virtualbox' hypervisor ...
-- Minishift VM will be configured with ...
Memory: 2 GB
vCPUs : 2
Disk size: 20 GB
-- Starting Minishift VM .............................. OK
-- Checking for IP address ... OK
-- Checking for nameservers ... OK
-- Checking if external host is reachable from the Minishift VM ...
Pinging 8.8.8.8 ... OK
-- Checking HTTP connectivity from the VM ...
Retrieving http://minishift.io/index.html ... OK
-- Checking if persistent storage volume is mounted ... OK
-- Checking available disk space ... 0% used OK
Importing 'openshift/origin:v3.9.0' . CACHE MISS
Importing 'openshift/origin-docker-registry:v3.9.0' . CACHE MISS
Importing 'openshift/origin-haproxy-router:v3.9.0' . CACHE MISS
-- OpenShift cluster will be configured with ...
Version: v3.9.0
-- Copying oc binary from the OpenShift container image to VM ...................................... OK
-- Starting OpenShift cluster ........................
Using Docker shared volumes for OpenShift volumes
Using public hostname IP 192.168.99.102 as the host IP
Using 192.168.99.102 as the server IP
Starting OpenShift using openshift/origin:v3.9.0 ...
OpenShift server started.
 
The server is accessible via web console at:
  https://192.168.99.102:8443
 
You are logged in as:
User: developer
Password:
 
To login as administrator:
oc login -u system:admin

Docker

That’s all. I have a new VM in VirtualBox whith its main files in C:/Users/fpa/.minishift

C:/Users/fpa/.minishift/machines/minishift/boot2docker.iso
C:/Users/fpa/.minishift/machines/minishift/disk.vmdk

The VM boots on the Boot2Docker iso, which is the way to run Docker on Windows without enabling HyperV. The first network interface is NAT for internet access. The second one has a DHCP IP from 192.168.99.1

You can control the VM with minishift (start, stop, configure, ssh,…):

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>d:minishift ssh
## .
## ## ## ==
## ## ## ## ## ===
/"""""""""""""""""\___/ ===
~~~ {~~ ~~~~ ~~~ ~~~~ ~~~ ~ / ===- ~~~
\______ o __/
\ \ __/
\____\_______/
_ _ ____ _ _
| |__ ___ ___ | |_|___ \ __| | ___ ___| | _____ _ __
| '_ \ / _ \ / _ \| __| __) / _` |/ _ \ / __| |/ / _ \ '__|
| |_) | (_) | (_) | |_ / __/ (_| | (_) | (__| < __/ |
|_.__/ \___/ \___/ \__|_____\__,_|\___/ \___|_|\_\___|_|
Boot2Docker version 1.12.6, build HEAD : 5ab2289 - Wed Jan 11 03:20:40 UTC 2017
Docker version 1.12.6, build 78d1802

We have everything running in containers here:

docker@minishift:/mnt/sda1$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
77c0ef5a80d7 50c7bffa0653 "/usr/bin/openshift-r" 8 minutes ago Up 8 minutes k8s_router_router-1-tsmw7_default_7a1be0e2-635b-11e8-843d-f2c6a11ee2db_1
10fb6a2a6b70 9b472363b07a "/bin/sh -c '/usr/bin" 8 minutes ago Up 8 minutes k8s_registry_docker-registry-1-zfxm5_default_7865ac33-635b-11e8-843d-f2c6a11ee2db_1
2f6b90fb0bb4 openshift/origin-pod:v3.9.0 "/usr/bin/pod" 8 minutes ago Up 8 minutes k8s_POD_router-1-tsmw7_default_7a1be0e2-635b-11e8-843d-f2c6a11ee2db_1
3c720d166989 fae77002371b "/usr/bin/origin-web-" 8 minutes ago Up 8 minutes k8s_webconsole_webconsole-7dfbffd44d-48b9h_openshift-web-console_62b66c66-635b-11e8-843d-f2c6a11ee2db_1
bb5870fc0b7e openshift/origin-pod:v3.9.0 "/usr/bin/pod" 8 minutes ago Up 8 minutes k8s_POD_docker-registry-1-zfxm5_default_7865ac33-635b-11e8-843d-f2c6a11ee2db_1
95663bf29487 openshift/origin-pod:v3.9.0 "/usr/bin/pod" 8 minutes ago Up 8 minutes k8s_POD_webconsole-7dfbffd44d-48b9h_openshift-web-console_62b66c66-635b-11e8-843d-f2c6a11ee2db_1
5fc022dbe112 openshift/origin:v3.9.0 "/usr/bin/openshift s" 8 minutes ago Up 8 minutes origin

But we should not have to connect to this machine.

The minishift executable can be used to control anything. As I have docker client installed on my laptop (the Docker Toolbox) I can get the environment variables:

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>d:minishift docker-env
SET DOCKER_TLS_VERIFY=1
SET DOCKER_HOST=tcp://192.168.99.102:2376
SET DOCKER_CERT_PATH=C:\Users\fpa\.minishift\certs
REM Run this command to configure your shell:
REM @FOR /f "tokens=*" %i IN ('minishift docker-env') DO @call %i

and see, from Windows, the docker images that are in the VM:

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>where docker
C:\Program Files\Docker Toolbox\docker.exe
 
SET DOCKER_TLS_VERIFY=1
SET DOCKER_HOST=tcp://192.168.99.102:2376
SET DOCKER_CERT_PATH=C:\Users\fpa\.minishift\certs
 
D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
openshift/origin-web-console v3.9.0 fae77002371b 12 days ago 495.1 MB
openshift/origin-docker-registry v3.9.0 9b472363b07a 12 days ago 464.9 MB
openshift/origin-haproxy-router v3.9.0 50c7bffa0653 12 days ago 1.283 GB
openshift/origin-deployer v3.9.0 e4de3cb64af9 12 days ago 1.261 GB
openshift/origin v3.9.0 83ec0170e887 12 days ago 1.261 GB
openshift/origin-pod v3.9.0 b6d2be1df9c0 12 days ago 220.1 MB

While I’m there, I can run whatever I want as a docker container. Let’s try with Oracle.

I need to login to the Docker store (where I have accepted the license conditions)

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>docker login
Login with your Docker ID to push and pull images from Docker Hub. If you don't have a Docker ID, head over to https://hub.docker.com to create one.
Username: pachot
Password:
Login Succeeded

Let’s pull the Oracle ‘slim’ image:

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>docker pull store/oracle/database-enterprise:12.2.0.1-slim
12.2.0.1: Pulling from store/oracle/database-enterprise
4ce27fe12c04: Downloading [> ] 1.076 MB/83.31 MB
9d3556e8e792: Downloading [> ] 535.3 kB/151 MB
fc60a1a28025: Download complete
0c32e4ed872e: Download complete
b465d9b6e399: Waiting

And run it:

D:\Downloads\minishift-1.18.0-windows-amd64\minishift-1.18.0-windows-amd64>docker run -it --rm --name orcl store/oracle/database-enterprise:12.2.0.1-slim
Setup Oracle Database
Oracle Database 12.2.0.1 Setup
Wed May 30 20:16:56 UTC 2018
 
Check parameters ......
log file is : /home/oracle/setup/log/paramChk.log
paramChk.sh is done at 0 sec
 
untar DB bits ......
log file is : /home/oracle/setup/log/untarDB.log
untarDB.sh is done at 38 sec
...

You may find that funny, but the Oracle images in the Docker store contains only a tarball of Oracle Home and a pre-created database. Just the time to untar those and run the instance and after 2 minutes I have my database ready. All is untar-ed to the volume, including the software.

Here are the ports that are redirected to:

C:\Users\fpa>SET DOCKER_TLS_VERIFY=1
C:\Users\fpa>SET DOCKER_HOST=tcp://192.168.99.102:2376
C:\Users\fpa>SET DOCKER_CERT_PATH=C:\Users\fpa\.minishift\certs
C:\Users\fpa>docker port orcl
1521/tcp -> 0.0.0.0:32771
5500/tcp -> 0.0.0.0:32770

Then, easy to connect with SQL*Net with the credentials provided (see the setup instructions)

C:\Users\fpa>sqlcl sys/Oradoc_db1@//192.168.99.102:32771/orclpdb1.localdomain as sysdba
SQLcl: Release 18.1.1 Production on Wed May 30 22:41:10 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0
SQL> select instance_name,host_name from v$instance;
 
INSTANCE_NAME HOST_NAME
------------- ------------
ORCLCDB 254f96463883

OpenShift

So, that’s an easy way to run Oracle on Docker when you have VirtualBox. One download and 5 commands and I’m ready to connect. But that’s not the goal. Here we have OpenShift here to manage multiple Docker containers.

According to the ‘minishift start’ output I have a Web server on https://192.168.99.102:8443 (user: system, password: admin)

It already contains a lot of databases:
CaptureMiniShift001

They are really easy to use. In two clicks I’ve run a MySQL container:
CaptureMiniShift002

If you don’t like the GUI, there’s the command line interface of OpenShift within the minishift ‘cache:

C:\Users\fpa>C:\Users\fpa\.minishift\cache\oc\v3.9.0\windows\oc.exe login https://192.168.99.102:8443
Authentication required for https://192.168.99.102:8443 (openshift)
Username: system
Password:
Login successful.
 
You have one project on this server: "test"
 
Using project "test".
 
C:\Users\fpa>C:\Users\fpa\.minishift\cache\oc\v3.9.0\windows\oc.exe status
In project test on server https://192.168.99.102:8443
 
svc/mysql - 172.30.224.246:3306
dc/mysql deploys openshift/mysql:5.7
deployment #1 deployed 7 minutes ago - 1 pod

Now that I have OpenShift running and installed, I’ll be able to run Oracle and manage containers from there. That’s for the next post on this subject.

 

Cet article OpenShift on my Windows 10 laptop with MiniShift est apparu en premier sur Blog dbi services.

EDB Failover Manager in EDB containers in Minishift/OpenShift – Failovers

Tue, 2018-05-29 13:42

In the last post we had a look at the basic configuration of EDB EFM and confirmed that we can do manual switchovers for maintenance operations. Being able to do a switchover and switchback is fine but what really is important are automatic failovers. Without an automatic failover this setup would be somehow useless as a) EDB EFM is supposed to do exactly that and b) without that we would need to implement something on our own. So lets have a look if that works.

For this little I scaled the PostgreSQL instances to three, meaning one master and two replica instances. Three is the minimum required as EFM requires at least three agents so at least two can decide what to do. In a traditional setup this is usually done by making a non database host the EDB EFM witness host. In this OpenShift/MiniShift setup we do not have a witness node so the minimum amount of database hosts (where EFM runs on) is three. This is how it currently looks like:

dwe@dwe:~$ oc get pods -o wide -L role
NAME                 READY     STATUS    RESTARTS   AGE       IP            NODE        ROLE
edb-as10-0-1-dzcj6   0/1       Running   2          3h        172.17.0.9    localhost   standbydb
edb-as10-0-1-jmfjk   0/1       Running   2          4h        172.17.0.3    localhost   masterdb
edb-as10-0-1-rlzdb   0/1       Running   2          3h        172.17.0.6    localhost   standbydb
edb-pgpool-1-jtwg2   0/1       Running   2          4h        172.17.0.4    localhost   queryrouter
edb-pgpool-1-pjxzs   0/1       Running   2          4h        172.17.0.5    localhost   queryrouter

What I will do is to kill the master pod (edb-as10-0-1-jmfjk) to see what happens to my current setup. Before I do that, lets open a second session into one of the standby pods and check the current EFM status:

dwe@dwe:~$ oc rsh edb-as10-0-1-dzcj6
sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Master      172.17.0.3           UP     UP        
	Standby     172.17.0.6           UP     UP        
	Standby     172.17.0.9           UP     UP        

Allowed node host list:
	172.17.0.3

Membership coordinator: 172.17.0.3

Standby priority host list:
	172.17.0.9 172.17.0.6

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.3           0/F000060        
	Standby     172.17.0.9           0/F000060        
	Standby     172.17.0.6           0/F000060        

	Standby database(s) in sync with master. It is safe to promote.

Looks fine. So from the first session using the oc command line utility lets kill the master pod. What I at least expect is that one of the standby instances get promoted and the remaining standby is reconfigured to connect to the new master.

dwe@dwe:~$ oc delete pod edb-as10-0-1-jmfjk
pod "edb-as10-0-1-jmfjk" deleted
dwe@dwe:~$ oc get pods
NAME                 READY     STATUS        RESTARTS   AGE
edb-as10-0-1-dzcj6   1/1       Running       2          3h
edb-as10-0-1-jmfjk   1/1       Terminating   2          4h
edb-as10-0-1-rlzdb   1/1       Running       2          4h
edb-as10-0-1-snnxc   0/1       Running       0          2s
edb-bart-1-s2fgj     1/1       Running       2          4h
edb-pgpool-1-jtwg2   1/1       Running       2          4h
edb-pgpool-1-pjxzs   1/1       Running       2          4h

The master pod is terminating. What does the second session in the standby pod tell us when we ask EFM for the cluster status?

sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     172.17.0.6           UP     UP        
	Promoting   172.17.0.9           UP     UP        

Allowed node host list:
	172.17.0.3

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.6

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.9           0/F000250        
	Standby     172.17.0.6           0/F000140        

	One or more standby databases are not in sync with the master database.

The master is gone and one of the standby instances gets promoted. Lets wait a few seconds and check again:

sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Idle        172.17.0.11          UP     UNKNOWN   
	Standby     172.17.0.6           UP     UP        
	Master      172.17.0.9           UP     UP        

Allowed node host list:
	172.17.0.3

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.6

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.9           0/F000288        
	Standby     172.17.0.6           0/F000288        

	Standby database(s) in sync with master. It is safe to promote.

Idle Node Status (idle nodes ignored in XLog location comparisons):

	Address              XLog Loc         Info
	--------------------------------------------------------------
	172.17.0.11          0/F000288        DB is in recovery.

The new master is ready, the remaining standby is reconfigured to connect to the new master. Even better a few seconds later the old master is back as a new standby:

sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     172.17.0.11          UP     UP        
	Standby     172.17.0.6           UP     UP        
	Master      172.17.0.9           UP     UP        

Allowed node host list:
	172.17.0.3

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.6 172.17.0.11

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.9           0/F000288        
	Standby     172.17.0.6           0/F000288        
	Standby     172.17.0.11          0/F000288        

	Standby database(s) in sync with master. It is safe to promote.

Works fine and is what you must have in a good setup. When we kill one of the standby pods the expected result is that the pod restarts and the instance will come back as a standby, lets check:

20:32:47 dwe@dwe:~$ oc delete pod edb-as10-0-1-hf27d
pod "edb-as10-0-1-hf27d" deleted
dwe@dwe:~$ oc get pods -o wide -L role
NAME                 READY     STATUS        RESTARTS   AGE       IP            NODE        ROLE
edb-as10-0-1-8p5rd   0/1       Running       0          3s        172.17.0.6    localhost   
edb-as10-0-1-dzcj6   1/1       Running       2          4h        172.17.0.9    localhost   masterdb
edb-as10-0-1-hf27d   0/1       Terminating   1          12m       172.17.0.3    localhost   
edb-as10-0-1-snnxc   1/1       Running       0          20m       172.17.0.11   localhost   standbydb
edb-pgpool-1-jtwg2   1/1       Running       2          5h        172.17.0.4    localhost   queryrouter
edb-pgpool-1-pjxzs   1/1       Running       2          5h        172.17.0.5    localhost   queryrouter

A few moments later we have the previous status:

dwe@dwe:~$ oc get pods -o wide -L role
NAME                 READY     STATUS    RESTARTS   AGE       IP            NODE        ROLE
edb-as10-0-1-8p5rd   1/1       Running   0          4m        172.17.0.6    localhost   standbydb
edb-as10-0-1-dzcj6   1/1       Running   2          4h        172.17.0.9    localhost   masterdb
edb-as10-0-1-snnxc   1/1       Running   0          24m       172.17.0.11   localhost   standbydb
edb-pgpool-1-jtwg2   1/1       Running   2          5h        172.17.0.4    localhost   queryrouter
edb-pgpool-1-pjxzs   1/1       Running   2          5h        172.17.0.5    localhost   queryrouter

Fine as well. To complete this little series about EDB containers in MiniShift/OpenShift we will have a look at how we can add an EDB BART container to the setup, because backup and recovery is still missing :)

 

Cet article EDB Failover Manager in EDB containers in Minishift/OpenShift – Failovers est apparu en premier sur Blog dbi services.

SQL Saturday Paris 2018 – Pré-conférence SQL Server Linux, Docker et Azure

Mon, 2018-05-28 14:41

The next SQL Saturday in Paris is coming soon and the agenda has been published by the @GUSS on twitter. Le prochain SQL Saturday Paris arrive bientôt et l’agenda a été publié sur Twitter par le @GUSS

blog 135 - 0 - SQL Sat Paris 2018

Cette année, un savant mélange de speakers francophones et internationaux, un plus pour les participants …

Un rapide coup d’œil à l’agenda – si on se focalise sur la partie moteur SQL Server – montre également qu’une bonne partie du stream rouge concerne Docker et l’automatisation. Intéressant :)

Bien entendu les sujets sur la performance et l’architecture auront toujours leur popularité et vous pourrez continuer à y assister :) Ce n’est pas moi qui vais m’en plaindre :)

 

Pré-conférence SQL Server au delà de Windows : installation, déploiement et gestion

sqlsatparis2018

Comme vous le savez déjà, Microsoft s’est ouvert à l’Open Source depuis SQL Server 2017 et a également renforcé son offre Azure avec notamment les instances managées. Par conséquent l’écho-système autour de SQL Server s’est considérablement agrandi. Cette année j’aurai l’opportunité de partager quelques expériences à ce sujet lors d’une pré- conférence qui se déroulera le vendredi 6 juin 2017.

Nous aborderons ensemble des thématiques bien connus par tout administrateur de données comme l’installation, la configuration, le monitoring, la performance ou encore la haute disponibilité dans des environnements autre que Windows.

Pour vous inscrire c’est par ici.

Au plaisir de vous y retrouver!

 

 

 

Cet article SQL Saturday Paris 2018 – Pré-conférence SQL Server Linux, Docker et Azure est apparu en premier sur Blog dbi services.

EDB Failover Manager in EDB containers in Minishift/OpenShift

Mon, 2018-05-28 12:47

In the last three posts we deployed an EDB database container and two pgpool instances, scaled that up to include a read only replica and finally customized the PostgreSQL instance with ConfigMaps. In this post will we look at how the EDB Failover Manager is configured in the database containers.

This are the pods currently running in my environment, two pgpool containers and two PostgreSQL containers:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-gk8dt   1/1       Running   1          9d
edb-as10-0-1-n5z4w   1/1       Running   0          3m
edb-pgpool-1-h5psk   1/1       Running   1          9d
edb-pgpool-1-tq95s   1/1       Running   1          9d

The first one (edb-as10-0-1-gk8dt) is the primary instance and EFM should be running there as well:

dwe@dwe:~$ oc rsh edb-as10-0-1-gk8dt
sh-4.2$ psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 f
(1 row)

sh-4.2$ ps -ef | grep efm
edbuser    202     1  0 08:45 ?        00:00:04 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-7.b10.el7.x86_64/jre/bin/java -cp /usr/edb/efm-3.0/lib/EFM-3.0.0.jar -Xmx32m com.enterprisedb.efm.main.ServiceCommand __int_start /etc/edb/efm-3.0/edb.properties
sh-4.2$ 

Looking at the configuration there are some interesting points:

sh-4.2$ cat /etc/edb/efm-3.0/edb.properties | egrep -v "^$|^#"
db.user=edbuser
db.password.encrypted=ca78865e0f85d15edc6c51b2e5c0a58f
db.port=5444
db.database=edb
db.service.owner=edbuser
db.service.name=
db.bin=/usr/edb/as10/bin
db.recovery.conf.dir=/edbvolume/edb/edb-as10-0-1-gk8dt/pgdata
jdbc.sslmode=disable
user.email=none@none.com
script.notification=
bind.address=172.17.0.6:5430
admin.port=5431
is.witness=false
local.period=10
local.timeout=60
local.timeout.final=10
remote.timeout=10
node.timeout=10
stop.isolated.master=false
pingServerIp=8.8.8.8
pingServerCommand=/bin/ping -q -c3 -w5
auto.allow.hosts=false
db.reuse.connection.count=0
auto.failover=true
auto.reconfigure=true
promotable=true
minimum.standbys=0
recovery.check.period=2
auto.resume.period=0
virtualIp=
virtualIp.interface=
virtualIp.prefix=
script.fence=
script.post.promotion=/var/efm/post_promotion_steps.sh %f
script.resumed=
script.db.failure=/var/efm/stopEFM
script.master.isolated=
script.remote.pre.promotion=
script.remote.post.promotion=
script.custom.monitor=
custom.monitor.interval=
custom.monitor.timeout=
custom.monitor.safe.mode=
sudo.command=sudo
sudo.user.command=sudo -u %u
log.dir=/var/log/efm-3.0
jgroups.loglevel=
efm.loglevel=
jvm.options=-Xmx32m
kubernetes.port.range=1
kubernetes.namespace=myproject
kubernetes.pod.labels=cluster=edb
kubernetes.master.host=172.30.0.1
kubernetes.master.httpsPort=443
create.database.master=/var/lib/edb/bin/createmasterdb.sh
create.database.standby=/var/lib/edb/bin/createstandbydb.sh
kubernetes.is.init.master=true

The last 8 lines are not there when you do a manual EFM installation so this is something specific in the container deployment. Apparently it is EFM that creates the master and the replica instance(s). The rest is more or less the default setup. The cluster status should be fine then:

sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Master      172.17.0.6           UP     UP        
	Standby     172.17.0.8           UP     UP        

Allowed node host list:
	172.17.0.6

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.8

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.6           0/5000140        
	Standby     172.17.0.8           0/5000140        

	Standby database(s) in sync with master. It is safe to promote.

We should be able to do a switchover:

sh-4.2$ /usr/edb/efm-3.0/bin/efm promote edb -switchover    
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.
sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     172.17.0.6           UP     UP        
	Master      172.17.0.8           UP     UP        

Allowed node host list:
	172.17.0.6

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.6

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.8           0/60001A8        
	Standby     172.17.0.6           0/60001A8        

	Standby database(s) in sync with master. It is safe to promote.

Seems it worked so the instances should have switched the roles and the current instance must be in recovery:

sh-4.2$ psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 t
(1 row)

Fine. This works as expected. So far for the first look at EFM inside the containers. It is not the same setup you’ll find when you install EFM on your own and EFM is doing more here than it does usually. A lot of stuff happens in the scripts provided by EDB here:

sh-4.2$ ls -la /var/lib/edb/bin/
total 72
drwxrwx---  2 enterprisedb root  4096 May 11 20:40 .
drwxrwx--- 24 enterprisedb root  4096 May 28 18:03 ..
-rwxrwx---  1 enterprisedb root  1907 Feb 17 17:14 cleanup.sh
-rwxrwx---  1 enterprisedb root  4219 May 10 22:11 createmasterdb.sh
-rwxrwx---  1 enterprisedb root  2582 May 11 03:30 createstandbydb.sh
-rwxrwx---  1 enterprisedb root  1491 May 10 22:12 dbcommon.sh
-rwxrwx---  1 enterprisedb root 10187 May 10 22:28 dbfunctions.sh
-rwxrwx---  1 enterprisedb root   621 May 10 22:15 dbsettings.sh
-rwxrwx---  1 enterprisedb root  5778 Apr 26 22:55 helperfunctions.sh
-rwxrwx---  1 enterprisedb root    33 Feb 18 03:43 killPgAgent
-rwxrwx---  1 enterprisedb root  5431 May 10 22:29 launcher.sh
-rwxrwx---  1 enterprisedb root   179 May 10 22:12 startPgAgent
-rwxrwx---  1 enterprisedb root   504 May 11 12:32 startPgPool

These scripts are referenced in the EFM configuration in several places and contain all the logic for initializing the cluster, starting it up, stopping and restarting the cluster, setting up replication and so on. To understand what really is going on one needs to understand the scripts (which is out of scope of this post).

 

Cet article EDB Failover Manager in EDB containers in Minishift/OpenShift est apparu en premier sur Blog dbi services.

Which Bitnami service to choose in the Oracle Cloud Infrastructure?

Fri, 2018-05-25 15:40

In the Oracle Cloud PaaS you have a marketplace where you can choose your service. Some are packaged from Bitnami and available on multiple OS. My first idea is that in PaaS you do not care about the OS. But Oracle Cloud has this very nice feature where you still have full access to the OS, as root, even in PaaS. Then, you choose the Linux distribution of your preference. Except if performance is different. They run on different Linux kernels. Is Oracle Linux Unbreakable Kernel more efficient?

We need to compare with relevant and reliable measures. And for that I’ve created a Postgres service and used Kevin Closson SLOB method, as I’m a lucky beta tester for pgio. I’ll post later about the creation of the Postgres service in the Oracle Cloud.

Cached IO

First, I’ve run cached IO to measure CPU performance.

I’ve run with the following settings in pgio.conf:

UPDATE_PCT=0
RUN_TIME=300
NUM_SCHEMAS=4
NUM_THREADS=1
WORK_UNIT=255
SCALE=1G

This is 4 schemas with 1GB of data. This fit in my 30GB host. Actually, here is a vmstat sample during the run showing 5GB cached and large free memory:

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
4 0 0 15508168 12444 5495652 0 0 0 0 1530 1631 28 22 50 0 0
4 0 0 15508292 12452 5495648 0 0 0 4 1506 1564 27 23 50 0 0

There’s no I/O to block device here, which is my goal.

I’ve run 10 times the runit.sh and here is the summary of main result with IOPS:


$ for i in allopc@144.21.82.244.out allopc@144.21.82.255.out allopc@144.21.82.252.out ; do grep os-release $i | grep PRETTY | tail -1 ; grep x86 $i | tail -1 ; grep -E "runit.out.*DBNAME" $i | tail -10 | nl ; done
 
Fri May 25 11:56:11 UTC 2018 /etc/os-release : PRETTY_NAME="Ubuntu 16.04.4 LTS"
Linux ubuntu 4.4.0-127-generic #153-Ubuntu SMP Sat May 19 10:58:46 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 11:11:05 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >393232< CACHE_HITS/s >15711<
2 Fri May 25 11:16:06 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398179< CACHE_HITS/s >16000<
3 Fri May 25 11:21:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >402080< CACHE_HITS/s >16019<
4 Fri May 25 11:26:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >404682< CACHE_HITS/s >16086<
5 Fri May 25 11:31:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >408524< CACHE_HITS/s >16327<
6 Fri May 25 11:36:08 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >409209< CACHE_HITS/s >16390<
7 Fri May 25 11:41:08 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >403647< CACHE_HITS/s >16327<
8 Fri May 25 11:46:09 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397440< CACHE_HITS/s >15894<
9 Fri May 25 11:51:09 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397273< CACHE_HITS/s >15956<
10 Fri May 25 11:56:11 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >396906< CACHE_HITS/s >15904<
 
Fri May 25 11:54:56 UTC 2018 /etc/os-release : PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
Linux debian 4.9.0-6-amd64 #1 SMP Debian 4.9.88-1+deb9u1 (2018-05-07) x86_64 GNU/Linux
1 Fri May 25 11:09:53 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395672< CACHE_HITS/s >15882<
2 Fri May 25 11:14:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >401400< CACHE_HITS/s >16188<
3 Fri May 25 11:19:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >391551< CACHE_HITS/s >15764<
4 Fri May 25 11:24:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >393827< CACHE_HITS/s >15802<
5 Fri May 25 11:29:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >404462< CACHE_HITS/s >16198<
6 Fri May 25 11:34:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >392712< CACHE_HITS/s >15732<
7 Fri May 25 11:39:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >399389< CACHE_HITS/s >16063<
8 Fri May 25 11:44:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >390283< CACHE_HITS/s >15567<
9 Fri May 25 11:49:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >391426< CACHE_HITS/s >15771<
10 Fri May 25 11:54:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >392760< CACHE_HITS/s >15874<
 
Fri May 25 11:53:58 UTC 2018 /etc/os-release : PRETTY_NAME="Oracle Linux Server 7.5"
Linux b5e501 4.1.12-124.15.2.el7uek.x86_64 #2 SMP Tue May 22 11:52:31 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 11:08:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395815< CACHE_HITS/s >15759<
2 Fri May 25 11:13:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395346< CACHE_HITS/s >16009<
3 Fri May 25 11:18:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395340< CACHE_HITS/s >15898<
4 Fri May 25 11:23:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >402556< CACHE_HITS/s >16200<
5 Fri May 25 11:28:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397847< CACHE_HITS/s >16039<
6 Fri May 25 11:33:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398848< CACHE_HITS/s >16027<
7 Fri May 25 11:38:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398817< CACHE_HITS/s >16089<
8 Fri May 25 11:43:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398878< CACHE_HITS/s >15961<
9 Fri May 25 11:48:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395885< CACHE_HITS/s >15606<
10 Fri May 25 11:53:58 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395965< CACHE_HITS/s >15893<

I’ve put this data in Excel to show the picture where, basically, all 3 environments perform exactly the same:

CapturePGLIOPS

Physical IO

For the second test, I wanted to test physical IOPS. But not to measure disk performance, which is the same for my 3 services. I want to do physical I/O only to see if there’s a difference in context switches when doing non-blocking I/O – which we do not see in the previous test because they were filesystem cache hits. Then I kept the small scale of 4 sessions with 1GB so that there’s a good chance that it remains in the storage cache. But I reduced the memory in order to have less than 4GB in filesystem cache.

pgio comes with an utility (pgio_reduce_free_memory.sh) to allocate enough huge page to limit the filesystem cache:


$ sudo bash pgio/pgio_reduce_free_memory.sh 2
 
Taking action to reduce free memory down to 2GB available.
total used free shared buff/cache available
Mem: 30886100 124676 30698952 19088 62472 30469900
Swap: 0 0 0
 
Attempting to allocate 13966 huge pages
MemAvailable: 1869148 kB
HugePages_Total: 13966

This is perfect: 13966 huge pages, that’s 27 GB in my 30GB VM that cannot be used by the filesystem, so that my 4x1GB will need I/O calls to the disk.

Here is a vmstat extract to confirm that the filesystem cache is less than 2GB

procs -----------memory---------- ---swap-- -----io---- ---system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 4 0 154416 2344 2015204 0 0 93025 0 32425 24295 2 3 51 44 1
0 4 0 153816 2428 2015848 0 0 94923 21 32400 24414 2 3 51 44 1

runit.sh calls vmstat with a 3 seconds delay so you can see a higher number of block/s and context switches.

Here are the interesting lines from the runit.sh output:

$ for i in allopc@144.21.89.85.out allopc@144.21.89.53.out allopc@144.21.89.26.out ; do grep os-release $i | grep PRETTY | tail -1 ; grep x86 $i | tail -1 ; grep -E "runit.out.*DBNAME" $i | tail -10 | nl ; done
 
Fri May 25 14:58:47 UTC 2018 /etc/os-release : PRETTY_NAME="Ubuntu 16.04.4 LTS"
Linux ubuntu 4.4.0-127-generic #153-Ubuntu SMP Sat May 19 10:58:46 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 14:13:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >1214< CACHE_HITS/s >55<
2 Fri May 25 14:18:42 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >4545< CACHE_HITS/s >195<
3 Fri May 25 14:23:43 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >17053< CACHE_HITS/s >682<
4 Fri May 25 14:28:43 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18865< CACHE_HITS/s >801<
5 Fri May 25 14:33:44 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18933< CACHE_HITS/s >794<
6 Fri May 25 14:38:44 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18832< CACHE_HITS/s >777<
7 Fri May 25 14:43:45 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18826< CACHE_HITS/s >757<
8 Fri May 25 14:48:46 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >19229< CACHE_HITS/s >819<
9 Fri May 25 14:53:46 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19408< CACHE_HITS/s >835<
10 Fri May 25 14:58:47 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19524< CACHE_HITS/s >799<
 
Fri May 25 14:58:41 UTC 2018 /etc/os-release : PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
Linux debian 4.9.0-6-amd64 #1 SMP Debian 4.9.88-1+deb9u1 (2018-05-07) x86_64 GNU/Linux
1 Fri May 25 14:13:35 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >1727< CACHE_HITS/s >82<
2 Fri May 25 14:18:36 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >10743< CACHE_HITS/s >534<
3 Fri May 25 14:23:37 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18660< CACHE_HITS/s >763<
4 Fri May 25 14:28:37 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18496< CACHE_HITS/s >811<
5 Fri May 25 14:33:38 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18537< CACHE_HITS/s >757<
6 Fri May 25 14:38:38 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18647< CACHE_HITS/s >774<
7 Fri May 25 14:43:39 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18838< CACHE_HITS/s >775<
8 Fri May 25 14:48:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18686< CACHE_HITS/s >786<
9 Fri May 25 14:53:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18742< CACHE_HITS/s >782<
10 Fri May 25 14:58:41 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >18634< CACHE_HITS/s >793<
 
Fri May 25 14:57:25 UTC 2018 /etc/os-release : PRETTY_NAME="Oracle Linux Server 7.5"
Linux b5e501 4.1.12-124.15.2.el7uek.x86_64 #2 SMP Tue May 22 11:52:31 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 14:12:20 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >17025< CACHE_HITS/s >721<
2 Fri May 25 14:17:21 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19331< CACHE_HITS/s >792<
3 Fri May 25 14:22:21 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19271< CACHE_HITS/s >770<
4 Fri May 25 14:27:22 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19324< CACHE_HITS/s >802<
5 Fri May 25 14:32:22 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18943< CACHE_HITS/s >802<
6 Fri May 25 14:37:23 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19202< CACHE_HITS/s >818<
7 Fri May 25 14:42:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18999< CACHE_HITS/s >803<
8 Fri May 25 14:47:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19459< CACHE_HITS/s >823<
9 Fri May 25 14:52:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19138< CACHE_HITS/s >836<
10 Fri May 25 14:57:25 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18958< CACHE_HITS/s >781<

And I’ve graphed them:
CapturePGPIOPS

As I hit the storage here, I needed a few runs to warm up the storage cache and get the I/O I wanted: low latency rfom storage cache, but involving context switches on the server. And basically, all 3 alternatives (Ubuntu with Linux 4.4, Debian with Linux 4.9 and OEL with the unbreakable kernel 4.1) behave the same. From these tests, I can say that the performance is not the major criteria to choose one of the PaaS alternatives. Just choose the distribution you like.

About pgio, it is great to get performance measures that are reliable and focused on what I want to test. I had no problems to run it on all 3 platforms. Just needed to apt-get / yum to install systat and bc which are not there by default.

 

Cet article Which Bitnami service to choose in the Oracle Cloud Infrastructure? est apparu en premier sur Blog dbi services.

OpenSSH is now available on Windows 10

Fri, 2018-05-25 06:26

A couple of days ago, the Windows 10 april 2018 update was installed on my laptop. And what, you may say? Well, surprisingly this update provides some interesting “hidden” features and one of them concerns OpenSSH that is now available on-demand in Windows 10 (and likely available soon on Windows Server).

blog 134 - 0 - openSSH - Win10

This a obviously a good news because so far, I used either putty or directly a bash environment from my Windows 10 laptop available since the Anniversary Update on 2016 august 2. I know that some of my colleagues use Cygwin as well. An quick example of using the new bash environment from my Win10 Pro laptop:

C:\Users\dab>bash
Performing one-time upgrade of the Windows Subsystem for Linux file system for this distribution...

mikedavem@DBI-LT-DAB:/mnt/c/Users/dab$ cat /proc/version
Linux version 4.4.0-17134-Microsoft (Microsoft@Microsoft.com) (gcc version 5.4.0 (GCC) ) #48-Microsoft Fri Apr 27 18:06:00 PST 2018

mikedavem@DBI-LT-DAB:/mnt/c/Users/dab$ ssh
usage: ssh [-1246AaCfGgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]
           [-D [bind_address:]port] [-E log_file] [-e escape_char]
           [-F configfile] [-I pkcs11] [-i identity_file] [-L address]
           [-l login_name] [-m mac_spec] [-O ctl_cmd] [-o option] [-p port]
           [-Q query_option] [-R address] [-S ctl_path] [-W host:port]
           [-w local_tun[:remote_tun]] [user@]hostname [command]

 

After applying the corresponding update, OpenSSH client is already installed and available to use.

[dab@DBI-LT-DAB:#]> Get-WindowsCapability -Online | ? Name -like 'OpenSSH*'


Name  : OpenSSH.Client~~~~0.0.1.0
State : Installed

Name  : OpenSSH.Server~~~~0.0.1.0
State : NotPresent

 

If you want to also install the server you just have to go through the Add-WindowsCapability cmdlet as follows:

[dab@DBI-LT-DAB:#]> Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0


Path          :
Online        : True
RestartNeeded : False
[dab@DBI-LT-DAB:#]> Get-WindowsCapability -Online | ? Name -like 'OpenSSH*'


Name  : OpenSSH.Client~~~~0.0.1.0
State : Installed

Name  : OpenSSH.Server~~~~0.0.1.0
State : Installed

 

From now on, I may use directly a ssh command from both my PowerShell or my command line environment as follows:

C:\
[dab@DBI-LT-DAB:#]> ssh
usage: ssh [-46AaCfGgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]
           [-D [bind_address:]port] [-E log_file] [-e escape_char]
           [-F configfile] [-I pkcs11] [-i identity_file]
           [-J [user@]host[:port]] [-L address] [-l login_name] [-m mac_spec]
           [-O ctl_cmd] [-o option] [-p port] [-Q query_option] [-R address]
           [-S ctl_path] [-W host:port] [-w local_tun[:remote_tun]]
           destination [command]

 

We will also be able to access a Linux server from either Password-based or Key-based authentication. Let’s try with the first one (Password-based authentication) against my Linux docker private registry:

[dab@DBI-LT-DAB:#]> ssh dab@xxx.xxx.xxx.xxx
The authenticity of host 'xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx)' can't be established.
ECDSA key fingerprint is SHA256:7HwUjHowFNEJ3ILErsmBmgr8sqxossLV+fFt71YsBtA.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'xxx.xxx.xxx.xxx' (ECDSA) to the list of known hosts.
dab@xxx.xxx.xxx.xxx's password:

 

Not a big suprise here! It works as expected (assuming your SSH server is configured to accept authentication with password)! Let’s try now the second method (Key-based authentication). In fact, I already have an .ssh folder from a previous request to connect to our GitLab environment. For the demo, let’s use the same public/private key pairs.

C:\Users\dab
[dab@DBI-LT-DAB:#]> dir .ssh


    Directory: C:\Users\dab\.ssh


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       09.05.2018     11:25           3243 id_rsa
-a----       09.05.2018     11:25            757 id_rsa.pub
-a----       25.05.2018     10:24            380 known_hosts

 

The next step will consist in copying my public key (id_rsa.pub) to the remote Linux server folder .ssh as authorized_keys file.

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> scp .\id_rsa.pub dab@xxx.xxx.xxx.xxx:/home/dab/.ssh/authorized_keys
id_rsa.pub

 

To avoid retyping the secret phrase for each connection, let’s start the ssh-agent service on my Windows 10 machine.

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> Start-Service -Name ssh-agent
C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> Get-Service ssh-agent

Status   Name               DisplayName
------   ----               -----------
Running  ssh-agent          OpenSSH Authentication Agent

 

Then I just have to add the private key to this agent …

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> ssh-add.exe id_rsa
Identity added: id_rsa (id_rsa)

 

… and finally to try a connection to my Linux Server as follows:

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> ssh dab@xxx.xxx.xxx.xxx
Last login: Fri May 25 09:43:16 2018 from gateway

 

It works like a charm! I’m now connecting to my Linux server as dab user. I can get a picture of my docker containers, Note the bash prompt has changed here (server name) even it is pretty similar to my PowerShell prompt. Indeed, I customized my PowerShell profile to be similar to a bash shell in apparence and in some behaviors as well :)

[dab@localhost ~]$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
a60f3412b864        registry:2          "/entrypoint.sh /e..."   9 months ago        Up 37 minutes       0.0.0.0:5000->5000/tcp   registry

 

See you!

 

 

 

 

 

Cet article OpenSSH is now available on Windows 10 est apparu en premier sur Blog dbi services.

Windocks – a different way to use SQL Server on Docker

Thu, 2018-05-24 13:14

I spent some times to write blog posts about SQL Server on Docker since few months and you likely noticed it concerned mainly SQL Server on Linux. But what about SQL Server on Docker and Windows? If you take a look at the Docker images provided by Microsoft, only 2017 version is available and it is only intended for development and testing use. Versions prior 2017 seem to not be supported so far (but I don’t have in mind the Microsoft plan on this topic) and if you want to use containers with older SQL Server versions you have to start from scratch by yourself. This is not a hard task but I have in mind a discussion with one of my customers about the maintenance of SQL Server images prior 2017 and he told me he didn’t allocate resources to create and maintain such images by himself.

In addition, I recently presented at different events including our dbi services internal event  for customers the internal project we are managing about our DMK maintenance module tool for SQL Server. To cut the story short, this tool aims to provide smart maintenance capabilities for customer databases including backups, index, statistics etc …

Over the time, the code grew up and we had to support different versions of SQL Server from 2008 to SQL Server 2017. In the same time, the number of scenarios we have to manage and to test regarding the new features shipped with service packs and cumulative updates as well increased drastically over the time. So we decided to move on docker containers from different obvious reasons:

  • Containers are easy to provision for unit testing
  • Lower footprint on our local machine
  • We can share easily SQL images between members of our “development” team in a private registry
  • We will able to integer containers in a potential “future” CI pipeline

In our context, each developer has a local docker engine installed on a Windows 10 Pro laptop but for medium/large companies, internal rules may prevent installing such product on each individual developer laptop. Therefore, they prefer likely to provide a dedicated environment for developers that meet the internal rules and to keep the focus on flexibility.

In such shared infrastructure a main concern will be probably disk space issues because of the potential testing database(s) size and the number of containers provisioned at the same time. Let’s say that several developers would like to provision one SQL Server instance each with a testing database attached on it and that comes from the production through a sanitized database pipeline. The total size of this database is enough big to warn about a potential disk space issue because each instanced container will include its own image size (roughly 10GB with SQL Server containers on Windows) + the size of the testing database – let’s say more than 50 GBs and likely more for usual cases.

If you’re in one of the aforementioned scenarios, Windocks may be a good alternative scenario.

During this month I had the opportunity to test the product in the context of our internal project and after some experimentation stuff I admit I was agreeably surprised by some built-in capabilities as:

  • Windows authentication mode supported
  • SQL Server database cloning capabilities
  • The ability to use both Docker CLI and Web UI (even if I’m in favor of Docker CLI)
  • The ability to refresh a cloned database image from a differential backup

Before to dig further into the above features let’s just take few seconds to see the Windocks architecture design

blog 133 - 1 - windocks - architecture

We may notice some differences here. With a traditional Docker architecture, containers run on shared operating system kernel (either Windows or Linux) whereas Windocks is a strictly application construct-oriented solution and requires installing a SQL Server instance on the host as base instance for SQL Server containers. This is a fundamental change that provides some interesting advantages:

  • We don’t have to rebuild the containers to get OS & Framework patches through base image updates because containers rely only on the SQL Server base instance
  • We don’t break compatibility with some Windows storage functionalities as VSS and SQL Writer as well if you rely on them with third-party backup tools
  • We benefit from the underlying security and configuration of the SQL Server based instance meaning we may use Windows authentication for instance

Let’s talk now about one interesting feature shipped with Windocks that is cloned databases. Here a picture (from Windocks) that shows a high-level workflow of using SQL Server containers with cloned databases.

blog 133 - 2 - windocks - cloned db architecture

The starting point is a full database backup or a snapshot and Windocks will generate the corresponding VHD parent image of the backup media. Then each SQL Server container generated will use a writable clone of this parent image reducing drastically the disk footprint of each container (at least when the SQL Server container is generated). This is a common workflow that customers may implement for unit testing or for CI/CD pipeline to refresh development environments. The production database may be big in size and in this context the interest of using cloned databases becomes obvious. Another way provided would be to rely on mounted snapshot-based volumes from the storage provider but at this stage I didn’t test it. Maybe a next time!

To give a better picture of what it is possible to do with cloned databases, let me show you one way to implement it in the context of our DMK maintenance tool project. The development workflow of the development project is as follows:

blog 133 - 3 - windocks - dev workflow

We use SSDT and GitLab to manage our DMK maintenance tool sources and we perform unit testing by provisioning one or several SQL Server containers regarding the target version we want to validate at this moment. Furthermore we developed testing scenarios based on tSQLt framework we run after provisioning the SQL Server containers. With such architecture, we initially have to maintain images of different SQL Server versions and each time we want to create a container we have to attach one copy of the customized AdventureWorks_dbi database. Let’s say we want to work with 4 containers (SQL Server 2016) at time. We must to ensure we have sufficient disk space for 4 copies of this database (5 x 4 = 20GB) + space required for each container (10GB x 4 = 40GB) = 60GB. The dbi_tools database is intended to stay very small (512MB up to 1GB) comparing to other testing components, this is way I didn’t include it to the math.

Let’s now apply Windocks with the above context and the new architecture becomes as follows:

blog 133 - 4 - windocks - dev workflow

In this second scenario, we may include both the AdventureWorks_dbi and dbi_tools databases (including tSQLt framework objects) in a VHD parent image. For the demo, I used a SQL Server 2016 instance installed on the host that will be controlled by Windocks service during the container generation.

As I said previously we may rely on the configuration and the security of the base instance. Thus, I configured my security based on both Windows Authentication (dbi-services\clustadmin domain account) for SQL Server sysadmins and SQL authentication for development purpose (windock user).

In terms of configuration, tSQLt framework requires enabling CLR on the server level, so I changed the configuration directly on the SQL based instance to allow all my containers to inherit this configuration change.

SELECT 
	[name],
	principal_id,
	[type_desc]
FROM 
	sys.server_principals 
WHERE 
	[type] IN ('U', 'S')
	AND [name] NOT LIKE '##MS_%##'
	AND [name] NOT LIKE 'NT SERVICE%'

 

blog 133 - 41- windocks - base instance security

Here the content of my docker file.

FROM mssql-2016
SETUPCLONING FULL AdventureWorks_dbi \\Win2012sql16ls\windock\AdventureWorks_dbi_2008.bak
SETUPCLONING FULL dbi_tools \\Win2012sql16ls\windock\dbi_tools.bak

 

You may notice some new commands here:

  • FROM mssql-2016 indicates we will use the SQL Server 2016 instance as base image.
  • SETUPCLONING FULL indicates to generate the VHD parent image that we will include both the AdventureWorks_dbi and dbi_tools databases in a remote network path

Go ahead and let’s generate the corresponding SQL Server docker cloned image with the special -t flag

docker build -t 2016withdmk C:\DMK\WithClone\BasedImage\

 

blog 133 - 5- windocks - cloned database image

The process may take some times to generate the VHD parent image depending on the different database sizes and the location (local fast disk, network share bandwidth etc …)

blog 133 - 6- windocks - cloned db size

As expected, because the VHD parent image is a full byte copy of the data, the size is basically the sum of both AdventureWorks_dbi and dbi_tools database sizes.

Compared to a traditional approach, the provisioning of the associated containers will be faster irrespective to database size. Let’s create 2 containers from the image generated previously with the following docker commands:

PS C:\DMK\WithClone> docker run --name sql20161 -d 2016withdmk
PS C:\DMK\WithClone> docker run --name sql20162 -d 2016withdmk

 

Note that by default, Windocks will choose a random port between a configured range in the node.conf (START_PORT/ PORTS_PER_USER) unless you override the default behavior using the -p parameter.

blog 133 - 7- windocks - create containers

Let’s get a picture of the existing containers. My 2 containers have been generated correctly from the 2016withdmk base image.

PS C:\DMK\WithClone> docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS
89344d99758b        2016withdmk         ""                  5 minutes ago       Started             1433/
4dfd1a198626        2016withdmk         ""                  4 minutes ago       Started             1434/

 

Let’s take another look at the storage side:

blog 133 - 8 - windocks - cloned databases diff

The interesting point here is the two differential disks created during the creation of my 2 previous containers are very small size (~70MB per container).

Let’s try to connect from SSMS to the new fresh containers:

blog 133 - 9 - windocks - ssms connection

It works and did you notice I was connected with my domain account? :)

Last topic I wanted to share with you is the Windocks capability to update a base image from differential backups. In a real context, you may need to refresh frequently your environment with recent data for developers and regarding your full backup size it might be advantageous to deal with differential backups.

Let’s consider this process with my internal project environment. During the development process we already had to scale the database schema of our testing database with the features shipped with new versions, service packs or cumulative update over the time. Let’s say we have to add a new dbo.bigTransactionhistory_cci table to test columnstore index scenarios. We first add the concerned table. Then we will perform a differential backup and finally we will update the 2016withDMK base image with it. Obviously in the context of my demo, the database size is likely not big enough to take full advantage of this feature but I trust you to draw a comparison with a more realistic scenario.

The image I want to update is named 2016withdmk. Note the mssql-xxx images that are in fact images from SQL Server base instances installed on my host server.

PS C:\DMK\WithClone> docker images
REPOSITORY            TAG                 IMAGE ID            CREATED                  VIRTUAL SIZE
2016withdmk           none                b3a249ba-2cf        Less than a second ago   0 B
agitated_heisenberg   none                bbd0ce26-4bb        Less than a second ago   0 B
dotnet-4.5            none                dotnet-4.5          3 years ago              0 B
windows               none                windows             3 years ago              0 B
mssql-2008r2          none                mssql-2008r2        3 years ago              0 B
mssql-2016            none                mssql-2016          3 years ago              0 B
mssql-2014            none                mssql-2014          3 years ago              0 B

 

My new docker file content to update the 2016withdmk base image is as follows.

FROM 2016withdmk

SETUPCLONING DIFF AdventureWorks_dbi \\Win2012sql16ls\windock\AdventureWorks_dbi_2008.DIFF
SETUPCLONING DIFF dbi_tools \\Win2012sql16ls\windock\dbi_tools.DIFF

 

I used the differential backup of the AdventureWorks_dbi database with the SETUPCLONNING DIFF command.

Let’s start updating the 2016withDMK base image with the following docker command (I tagged my new image with 1.2 suffix):

docker build -t 2016withdmk1.2 C:\DMK\WithClone\DiffImage\

 

blog 133 - 10 - windocks - cloned database diff backup

Although using a differential backup may allow for substantial productivity keep in mind that updating a VHD parent image will require creating an additional VHD parent image that will become another full byte copy of the environment as shown below:

blog 133 - 11- windocks - cloned db size diff

After running the same docker run command exposed before, let’s get a picture of the running on my server. The container id 789ce49562d0 is the new fresh container with updated data (2016withdmk1.2 image).

PS C:\DMK\WithClone> docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS
89344d99758b        2016withdmk         ""                  About an hour ago   Started             1433/
4dfd1a198626        2016withdmk         ""                  About an hour ago   Started             1434/
789ce49562d0        2016withdmk1.2      ""                  2 minutes ago       Started             1436/

 

I may retrieve the new dbo.bigTransactionHistory_cci after connecting to the concerned SQL Server instance. I’m now able to update and to execute tSQLt scenarios to test columnstore index maintenance scenarios!

blog 133 - 12- windocks - ssms connection 2

 

In this blog post we’ve just surfaced some capabilities and possibilities provided by Windocks. There are other interesting features as encrypted password in the docker file (as we may use with Docker Swarm for instance), the support of TDE databases or lastly the ability to manage SQL Server on Linux. I will probably blog about it in the near future. Stay tuned!

 

 

 

 

Cet article Windocks – a different way to use SQL Server on Docker est apparu en premier sur Blog dbi services.

Postgres, the fsync() issue, and ‘pgio’ (the SLOB method for PostgreSQL)

Thu, 2018-05-24 08:05

That’s a long blog post title, which is actually just a good pretext to play with Kevin Closson SLOB method for PostgreSQL: pgio
I use the beta version of pgio here. If you want to read more about it, you can start on https://kevinclosson.net/2018/05/22/sneak-preview-of-pgio-the-slob-method-for-postgressql-part-i-the-beta-pgio-readme-file/. If you are used to the SLOB for Oracle (https://kevinclosson.net/slob/) you will quickly understand the ‘why’ and ‘how’ of pgio.

PostgreSQL’s fsync() surprise

You may have read about the fsync() issue. Postgres, from the beginning, relies a lot on the filesystem buffering to optimize I/O. So they write() to the data files but fsync() only at checkpoints. This is ok when everything goes well because the writes since the last checkpoints are protected by the Write Ahead Logging, where fsync() occurs for each writes at commit (if you didn’t change the default parameters for WAL). But when a problem occurs, such as power outage, some writes may be lost, or partially lost, and that’s not easy to detect at checkpoint time with fsync().

So, basically, there’s a risk of corruption and there are no easy ways to detect it.

You can read the details from https://lwn.net/Articles/752063/ and that’s not the subject of this post.

Most of the other databases are opening files with O_DSYNC, which means that the write() call will detect the error immediately. And the major ones are doing direct I/O anyway because they have their own buffer cache and do not need the performance overhead and corruption risk of double buffering.

Why is this so hard to fix?

So why is it so hard to do the same with Postgres? Just because it was not initially designed to optimize I/O and postgres relied heavily on the OS filesystem for that. The database systems which sync at each write, and which can do direct I/O, have implemented many optimizations to reduce the overhead of a disk latency at each write. They have their own buffer cache, with a background database writer which re-orders the writes in the optimal way. And they have multiblock writes for large contiguous writes which bypass the buffer cache.

However, you may have a storage system where write latency is minimal, and you may have an application where the overhead here is not a big problem. This means that you should measure it in order to balance between performance and prevention of corruption. And this is where the SLOB method is awesome: reliable and predictable metrics to measure IOPS.

pgio to the rescue

This is my first trial of pgio, in beta version. It cannot be easier. I’ve just un-tar-ed it:

tar -xvf pgio-0.9.tar
cd pgio

I’ve setup the pgio.conf with 4 schemas and 2 threads per schema:

UPDATE_PCT=10
RUN_TIME=300
NUM_SCHEMAS=4
NUM_THREADS=2
WORK_UNIT=255
UPDATE_WORK_UNIT=8
SCALE=200M
DBNAME=pgio
CONNECT_STRING="pgio"
CREATE_BASE_TABLE=TRUE

Because I want to test writes, I’ve set the UPDATE_PCT so that 10% of calls will do an UPDATE. And I kept the default work unit to read 255 blocks and, for those 10% updates, update 8 blocks only. I’ll run that with 2 threads per schemas, which means 8 concurrent sessions. And they will run for 300 seconds.

In this test I didn’t want to set different values. I just want to see what happens in IOPS for a common workload of lot of reads and small changes. the scale is 200M here. My workload sessions will find their buffers in memory.

On each test, I’ve created the pgio database:

create tablespace pgio location '/u01/pgdata';
CREATE TABLESPACE
create database pgio tablespace pgio;
CREATE DATABASE

Then run the setup.sh to load data in those schemas:

Job info: Loading 200M scale into 4 schemas as per pgio.conf->NUM_SCHEMAS.
Batching info: Loading 2 schemas per batch as per pgio.conf->NUM_THREADS.
Base table loading time: 15 seconds.
Waiting for batch. Global schema count: 2. Elapsed: 0 seconds.
Waiting for batch. Global schema count: 3. Elapsed: 103 seconds.
Waiting for batch. Global schema count: 4. Elapsed: 177 seconds.
Waiting for batch. Global schema count: 4. Elapsed: 249 seconds.
 
Group data loading phase complete. Elapsed: 249 seconds.
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+--------+-------------
public | pgio1 | table | postgres | 200 MB |
public | pgio2 | table | postgres | 200 MB |
public | pgio3 | table | postgres | 200 MB |
public | pgio4 | table | postgres | 200 MB |
public | pgio_base | table | postgres | 29 MB |
(5 rows)

And then I’m ready to run the runit.sh

ext4 mount option

My tablespace is on an ext4 filesystem:

-bash-4.2$ df -HT /u01/pgdata
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdb ext4 32G 1.2G 29G 4% /u01

I’ll run the same workload, several times, with changing only one mount option:

async All I/O to the filesystem should be done asynchronously. (See also the sync option.)
sync All I/O to the filesystem should be done synchronously. In case of media with limited number of write cycles (e.g. some flash drives) "sync" may cause life-cycle shortening.

Which means that some runs will run with /u01 mounted as:

/dev/sdb on /u01 type ext4 (rw,nosuid,nodev,relatime,sync,seclabel,data=ordered)

and some others will run with the default (async):

/dev/sdb on /u01 type ext4 (rw,nosuid,nodev,relatime,seclabel,data=ordered)

I did multiple runs and checked that the result is consistent among them. I’ll show only one result for each configuration.

Run it with async

Here is the output of one ‘runit.sh’ when /u01 was in async:

Date: Thu May 24 10:56:57 CEST 2018
Database connect string: "pgio".
Shared buffers: 128MB.
Testing 4 schemas with 2 thread(s) accessing 200M (25600 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 4 schema(s) will be accessed by 2 thread(s) each.
pg_stat_database stats:
datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE: pgio | 252209 | 118112 | 110420 | 6788 | 18
AFTER: pgio | 25189171 | 136972696 | 159128092 | 147250205 | 573216
DBNAME: pgio. 4 schemas, 2 threads(each). Run time: 300 seconds. RIOPS >456181< CACHE_HITS/s >83123<

This shows that, within those 5 minutes, I’ve fetched 147243417 tuples and updated 573198 ones.

pgio takes snapshots of iostat, vmstat and mpstat. Here is a sample after 1 minute of run where we show that all CPU are busy in user or kernel, but not waiting on I/O latency:

10:57:51 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
10:57:54 AM all 52.82 0.00 42.22 0.09 0.00 1.11 0.00 0.00 0.00 3.76
10:57:54 AM 0 54.11 0.00 40.75 0.00 0.00 1.37 0.00 0.00 0.00 3.77
10:57:54 AM 1 54.42 0.00 40.14 0.34 0.00 1.02 0.00 0.00 0.00 4.08
10:57:54 AM 2 51.19 0.00 43.34 0.34 0.00 0.68 0.00 0.00 0.00 4.44
10:57:54 AM 3 51.02 0.00 44.22 0.34 0.00 1.36 0.00 0.00 0.00 3.06
10:57:54 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
10:57:57 AM all 53.33 0.00 42.15 0.00 0.00 1.02 0.00 0.00 0.00 3.50
10:57:57 AM 0 53.95 0.00 42.27 0.00 0.00 0.69 0.00 0.00 0.00 3.09
10:57:57 AM 1 52.56 0.00 42.66 0.00 0.00 0.68 0.00 0.00 0.00 4.10
10:57:57 AM 2 54.27 0.00 40.27 0.00 0.00 1.37 0.00 0.00 0.00 4.10
10:57:57 AM 3 52.72 0.00 43.54 0.00 0.00 1.36 0.00 0.00 0.00 2.38
10:57:57 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
10:58:00 AM all 54.10 0.00 41.54 0.09 0.00 0.77 0.00 0.00 0.00 3.50
10:58:00 AM 0 55.14 0.00 39.38 0.34 0.00 1.03 0.00 0.00 0.00 4.11
10:58:00 AM 1 54.95 0.00 40.96 0.00 0.00 0.68 0.00 0.00 0.00 3.41
10:58:00 AM 2 54.11 0.00 41.10 0.00 0.00 0.68 0.00 0.00 0.00 4.11
10:58:00 AM 3 52.05 0.00 44.86 0.00 0.00 0.68 0.00 0.00 0.00 2.40
10:58:00 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle

Run it with sync

Here is the output of one ‘runit.sh’ when /u01 was in sync:

Date: Thu May 24 12:18:54 CEST 2018
Database connect string: "pgio".
Shared buffers: 128MB.
Testing 4 schemas with 2 thread(s) accessing 200M (25600 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 4 schema(s) will be accessed by 2 thread(s) each.
pg_stat_database stats:
datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE: pgio | 255169 | 119163 | 112734 | 6945 | 18
AFTER: pgio | 15040938 | 74598977 | 87775490 | 86742056 | 337889
DBNAME: pgio. 4 schemas, 2 threads(each). Run time: 300 seconds. RIOPS >248266< CACHE_HITS/s >49285<

This shows that, within those 5 minutes, I’ve fetched 86735111 tuples and updated 337871 ones. So, basically the IOPS have been divided by two here in this example when waiting on each writes to be synced to disk.

pgio takes snapshots of iostat, vmstat and mpstat. Here is a sample after 1 minute of run where we show that all CPU are 30% idle waiting on I/O completion:

12:19:51 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
12:19:54 PM all 29.95 0.00 27.79 10.88 0.00 1.26 0.00 0.00 0.00 30.13
12:19:54 PM 0 30.63 0.00 27.46 11.27 0.00 0.70 0.00 0.00 0.00 29.93
12:19:54 PM 1 30.07 0.00 27.62 12.24 0.00 0.70 0.00 0.00 0.00 29.37
12:19:54 PM 2 30.28 0.00 27.82 10.92 0.00 0.35 0.00 0.00 0.00 30.63
12:19:54 PM 3 28.02 0.00 28.02 8.56 0.39 3.89 0.00 0.00 0.00 31.13
12:19:54 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
12:19:57 PM all 30.10 0.00 27.92 11.24 0.00 1.00 0.00 0.00 0.00 29.74
12:19:57 PM 0 29.29 0.00 28.57 10.71 0.00 0.36 0.00 0.00 0.00 31.07
12:19:57 PM 1 30.88 0.00 28.07 11.93 0.00 0.35 0.00 0.00 0.00 28.77
12:19:57 PM 2 30.31 0.00 27.18 12.54 0.00 0.70 0.00 0.00 0.00 29.27
12:19:57 PM 3 30.43 0.00 27.67 9.88 0.00 2.77 0.00 0.00 0.00 29.25
12:19:57 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
12:20:00 PM all 29.51 0.00 27.00 10.76 0.00 1.08 0.00 0.00 0.00 31.66
12:20:00 PM 0 29.58 0.00 28.17 10.56 0.00 0.35 0.00 0.00 0.00 31.34
12:20:00 PM 1 29.72 0.00 26.22 12.24 0.00 0.70 0.00 0.00 0.00 31.12
12:20:00 PM 2 29.12 0.00 26.32 10.88 0.00 0.35 0.00 0.00 0.00 33.33
12:20:00 PM 3 29.34 0.00 27.80 8.88 0.00 3.09 0.00 0.00 0.00 30.89
12:20:00 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle

So what?

Currently, Postgres relies on the filesystem to optimize the I/O, but there’s a risk of corruption in case of failure. We can force to wait for I/O completion with the ‘sync’ mount options of the filesystems, or even with some file attributes (chattr -R +S) for ext4 or xfs, but there’s a performance penalty. The important thing is to measure this penalty, and this is where pgio is great: measure the performance penalty with a workload that is customizable (amount of changes, amount of data,…) but also predictable (does not depend on other parameters like an application benchmark). When you know how being in ‘sync’ impacts your system, you can choose. And we can bet that future versions of Postgres will improve and offer ways to stay efficient without compromising the data at first power outage.

 

Cet article Postgres, the fsync() issue, and ‘pgio’ (the SLOB method for PostgreSQL) est apparu en premier sur Blog dbi services.

ADWC – System and session settings (DWCS lockdown profile)

Thu, 2018-05-24 05:04

The Autonomous Data Warehouse Cloud service is a PaaS managed service where we have a PDB and an ADMIN user which has most of the system privileges. For example, we have the privilege to change initialization parameters:
SQL> select * from dba_sys_privs where grantee=user and privilege like 'ALTER S%';
 
GRANTEE PRIVILEGE ADMIN_OPTION COMMON INHERITED
------- --------- ------------ ------ ---------
ADMIN ALTER SESSION YES NO NO
ADMIN ALTER SYSTEM YES NO NO

Still, not everything is allowed for several reasons: ensure that we cannot break the Oracle managed CDB and force us to use only the features allowed in the ‘autonomous’ service. This is limited with a lockdown profile:
SQL> show parameter pdb_lockdown
 
NAME TYPE VALUE
------------ ------ -----
pdb_lockdown string DWCS

DWCS means Data Warehouse Cloud Service which was the name of the Autonomous Data Warehouse Cloud service until Larry Ellison announces this self-driven-no-human trend under the marketing umbrella of ‘autonomous’.

The limitations are all documented but I like to see them by myself and in 18c we have a mean to see the lockdown rules from the PDB itself, through V$LOCKDOWN_RULES.

ALTER SYSTEM

Basically, in this ADWC all ALTER SYSTEM statements are disallowed and then they add the few exceptions for what we are allowed to:

SQL> select * from v$lockdown_rules where rule in ('ALTER SYSTEM') and clause_option is null;
RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS CON_ID
--------- ---- ------ ------------- ------ ----- ------
STATEMENT ALTER SYSTEM DISABLE ALL 73
STATEMENT ALTER SYSTEM SET ENABLE COMMON 73
STATEMENT ALTER SYSTEM KILL SESSION ENABLE ALL 73

You can ignore what is enabled for COMMON users because we have no common user to connect to our PDB. We will see which ALTER SYSTEM SET clauses are allowed. But in addition to those, only the ‘KILL SESSION’ is allowed for ALTER SYSTEM.

Here is the detail about the parameters we can set:

SQL> select * from v$lockdown_rules left outer join (select upper(name) clause_option,display_value,description from v$parameter) using (clause_option) where rule in ('ALTER SYSTEM') and clause_option is not null and status='ENABLE';
 
CLAUSE_OPTION RULE_TYPE RULE CLAUSE STATUS USERS CON_ID DISPLAY_VALUE DESCRIPTION
------------- --------- ---- ------ ------ ----- ------ ------------- -----------
APPROX_FOR_AGGREGATION STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE Replace exact aggregation with approximate aggregation
APPROX_FOR_COUNT_DISTINCT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE Replace count distinct with approx_count_distinct
APPROX_FOR_PERCENTILE STATEMENT ALTER SYSTEM SET ENABLE ALL 73 none Replace percentile_* with approx_percentile
AWR_PDB_AUTOFLUSH_ENABLED STATEMENT ALTER SYSTEM SET ENABLE ALL 73 TRUE Enable/Disable AWR automatic PDB flushing
NLS_LANGUAGE STATEMENT ALTER SYSTEM SET ENABLE ALL 73 AMERICAN NLS language name
NLS_SORT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS linguistic definition name
NLS_TERRITORY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 AMERICA NLS territory name
NLS_CALENDAR STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS calendar system name
NLS_COMP STATEMENT ALTER SYSTEM SET ENABLE ALL 73 BINARY NLS comparison
NLS_CURRENCY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS local currency symbol
NLS_DATE_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 DD-MON-YYYY HH24:MI:ss NLS Oracle date format
NLS_DATE_LANGUAGE STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS date language name
NLS_DUAL_CURRENCY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 Dual currency symbol
NLS_ISO_CURRENCY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS ISO currency territory name
NLS_LENGTH_SEMANTICS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 BYTE create columns using byte or char semantics by default
NLS_NCHAR_CONV_EXCP STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE NLS raise an exception instead of allowing implicit conversion
NLS_NUMERIC_CHARACTERS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS numeric characters
NLS_TIMESTAMP_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 time stamp format
NLS_TIMESTAMP_TZ_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 timestamp with timezone format
NLS_TIME_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 time format
NLS_TIME_TZ_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 time with timezone format
OPTIMIZER_IGNORE_HINTS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 TRUE enables the embedded hints to be ignored
OPTIMIZER_IGNORE_PARALLEL_HINTS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 TRUE enables embedded parallel hints to be ignored
PLSCOPE_SETTINGS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 identifiers:all plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL source code identifier and SQL statement data
PLSQL_CCFLAGS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 PL/SQL ccflags
PLSQL_DEBUG STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE PL/SQL debug
PLSQL_OPTIMIZE_LEVEL STATEMENT ALTER SYSTEM SET ENABLE ALL 73 1 PL/SQL optimize level
PLSQL_WARNINGS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 DISABLE:ALL PL/SQL compiler warnings settings

The APPROX_ ones, disable by default, can be used to transparently use approximations for faster results.
The NLS_ ones can be used to set NLS defaults for our sessions.
OPTIMIZER_IGNORE_ are new in 18c and are set by default here to ignore embedded hints. However, we can set then to false.
PLSQL_ are the defaults for sessions and I don’t understand why warnings are not enabled by default. Fortunately, we are able to change that at PDB level.

There are also some rules to disable some ALTER SYSTEM SET. They are there for the common users only (which have ALTER SYSTEM SET enabled) but they are interesting to see what Oracle choose to set in the ADWC service which cannot be changed in the PDB even by their common users:

SQL> select * from v$lockdown_rules left outer join (select upper(name) clause_option,display_value,description from v$parameter) using (clause_option) where rule in ('ALTER SYSTEM') and clause_option is not null and status='DISABLE';
CLAUSE_OPTION RULE_TYPE RULE CLAUSE STATUS USERS CON_ID DISPLAY_VALUE DESCRIPTION
------------- --------- ---- ------ ------ ----- ------ ------------- -----------
DB_FILES STATEMENT ALTER SYSTEM SET DISABLE ALL 73 25 max allowable # db files
"_PDB_INHERIT_CFD" STATEMENT ALTER SYSTEM SET DISABLE ALL 73
"_PDB_MAX_AUDIT_SIZE" STATEMENT ALTER SYSTEM SET DISABLE ALL 73
"_PDB_MAX_DIAG_SIZE" STATEMENT ALTER SYSTEM SET DISABLE ALL 73
MAX_IDLE_TIME STATEMENT ALTER SYSTEM SET DISABLE ALL 73 60 maximum session idle time in minutes
PARALLEL_DEGREE_POLICY STATEMENT ALTER SYSTEM SET DISABLE ALL 73 AUTO policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE)
_PARALLEL_CLUSTER_CACHE_POLICY STATEMENT ALTER SYSTEM SET DISABLE ALL 73 ADAPTIVE policy used for parallel execution on cluster(ADAPTIVE/CACHED)
_ENABLE_PARALLEL_DML STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enables or disables parallel dml
RESULT_CACHE_MODE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FORCE result cache operator usage mode
RESULT_CACHE_MAX_RESULT STATEMENT ALTER SYSTEM SET DISABLE ALL 73 1 maximum result size as percent of cache size
RESOURCE_MANAGER_PLAN STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FORCE:DWCS_PLAN resource mgr top plan
_CELL_OFFLOAD_VECTOR_GROUPBY STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FALSE enable SQL processing offload of vector group by
PARALLEL_MIN_DEGREE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 CPU controls the minimum DOP computed by Auto DOP
_MAX_IO_SIZE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 33554432 Maximum I/O size in bytes for sequential file accesses
_LDR_IO_SIZE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 33554432 size of write IOs used during a load operation
_LDR_IO_SIZE2 STATEMENT ALTER SYSTEM SET DISABLE ALL 73 33554432 size of write IOs used during a load operation of EHCC with HWMB
_OPTIMIZER_GATHER_STATS_ON_LOAD_ALL STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enable/disable online statistics gathering for nonempty segments
_OPTIMIZER_GATHER_STATS_ON_LOAD_HIST STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enable/disable online histogram gathering for loads
_DATAPUMP_GATHER_STATS_ON_LOAD STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE Gather table statistics during Data Pump load rather thanimporting statistics from the dump file. This should be set to TRUE in the lockdown profile in a DWCS environment.
_OPTIMIZER_ANSWERING_QUERY_USING_STATS STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enable statistics-based query transformation
_PX_XTGRANULE_SIZE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 128000 default size of a external table granule (in KB)
_OPTIMIZER_ALLOW_ALL_ACCESS_PATHS STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FALSE allow all access paths
_DATAPUMP_INHERIT_SVCNAME STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE Inherit and propagate service name throughout job
_DEFAULT_PCT_FREE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 1 Default value of PCT_FREE enforced by DWCS lockdown

So, among the interesting ones, Result Cache is forced for all results (RESULT_CACHE_MODE=FORCE), Parallel DML is enabled for all sessions (but we will see that we can disable it at session level), PCTFREE will always be 1 (_DEFAULT_PCT_FREE=1), statistics are gathered during load (this is a 18c feature). And we cannot change that.

There are only few additional ALTER SYSTEM SET which are allowed at session level:

SQL> select * from v$lockdown_rules where rule in ('ALTER SESSION') and clause is not null and clause_option is not null
and (clause_option,status,users) not in (select clause_option,status,users from v$lockdown_rules where rule in ('ALTER SYSTEM') and clause is not null and clause_option is not null)
;
RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS CON_ID
--------- ---- ------ ------------- ------ ----- ------
STATEMENT ALTER SESSION SET CONTAINER ENABLE ALL 73
STATEMENT ALTER SESSION SET CURRENT_SCHEMA ENABLE ALL 73
STATEMENT ALTER SESSION SET EDITION ENABLE ALL 73
STATEMENT ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES ENABLE ALL 73
STATEMENT ALTER SESSION SET DEFAULT_COLLATION ENABLE ALL 73
STATEMENT ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE ENABLE ALL 73
STATEMENT ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL ENABLE ALL 73
STATEMENT ALTER SESSION SET TIME_ZONE ENABLE ALL 73

Besides the parameters here are what we can do with ALTER SESSION:

SQL> select * from v$lockdown_rules where rule='ALTER SESSION' and clause_option is null;
 
RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS CON_ID
--------- ---- ------ ------------- ------ ----- ------
STATEMENT ALTER SESSION DISABLE ALL 73
STATEMENT ALTER SESSION SET ENABLE COMMON 73
STATEMENT ALTER SESSION ADVISE COMMIT ENABLE ALL 73
STATEMENT ALTER SESSION CLOSE DATABASE LINK ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE COMMIT IN PROCEDURE ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE PARALLEL DDL ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE PARALLEL DML ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE PARALLEL QUERY ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE RESUMABLE ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE COMMIT IN PROCEDURE ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE PARALLEL DDL ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE PARALLEL DML ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE PARALLEL QUERY ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE RESUMABLE ENABLE ALL 73
STATEMENT ALTER SESSION FORCE PARALLEL DDL ENABLE ALL 73
STATEMENT ALTER SESSION FORCE PARALLEL DML ENABLE ALL 73
STATEMENT ALTER SESSION FORCE PARALLEL QUERY ENABLE ALL 73

I’ll show other rules (other than ALTER SYSTEM and ALTER SESSION statements) in a future post. Lockdown profiles is a great feature because they have very fine granularity and makes it easy to document what is allowed or not. Oracle introduced them for their own usage in the public cloud. You can use the same on-premises for your private cloud. This requires multitenant architecture, but the option is not mandatory.

 

Cet article ADWC – System and session settings (DWCS lockdown profile) est apparu en premier sur Blog dbi services.

Adding a Documentum extension into python

Tue, 2018-05-22 08:15

Many years ago, out of frustration by the poorness of scripting tools in Documentum, I realized a Documentum binding for python using the distutils and I remember how easy and straightforward it had been, even for someone not really into these things on a daily basis. Recently, I wanted to reuse that work but couldn’t find the source files, not that they were many, but I did not want to do it over again. Finally, I had to give up and admit it: I lost them for good somewhere among the tens of machines, O/Ses, disks and USB drives I played with during that period. Those were sunny, cloudless times (got it ?). Anyway, I decided to do it again but, as I hate repeating work, by using another method of extending python: this time I went for ctypes (cf. documentation here: https://docs.python.org/3/library/ctypes.html).
One of the advantages of ctypes over distutils is that no compilation is needed, even when changing the version of python or of the O/S because the interface is python, and it gets interpreted at run-time. Thanks to ctypes, there isn’t much to do and interfacing to a run-time library such as libdmcl40.so is a no brainer.
There was however a big change in the evolution from python 2 to python 3: strings no longer are arrays of bytes but are now a distinct, uncompatible type storing unicode characters. Transformation functions are of course provided to go from one type to the other and back. For low-level work such as interfacing a C/C++ shared library, the distinction is important because in C, strings are accessed as “char *”, i.e. array of bytes and one cannot just pass around python text strings with 1 to 4 bytes per character. Fortunately, there was no need to produce two versions of the interface because python 2.7, the last version of python 2, understands the type conversion functions used here:

string.encode('ascii', 'ignore') to convert python 3's strings to python 2's arrays of bytes compatible with C/C++ char*
b.decode() to convert python 2's arrays of bytes to python3 unicode strings

(see https://docs.python.org/3/howto/unicode.html). Thus, it was sufficient to just write one version for python 3 and it would also be compatible with python 2.
I started my work in a venerable 32-bit Ubuntu 14.04 with python 2.7. The Documentum library I used there was the 32-bit libdmcl40.so included in the Content server v5.3 binaries. Later, I installed python 3 on the same VM and made the necessary changes to the interface so it would be accepted by this interpreter. Later on, I copied the interface file to another VM running a 64-bit Ubuntu 16.04 and v7.3 Documentum ContentServer binaries but I couldn’t make it work with the included 64-bit libdmcl40.so. I kept receiving SIGSEGV and core dumps from both python2 and python3. A case for a gdb session sometime… Fortunately, with the java-enabled libdmcl.so library, both pythons worked well, albeit with a perceptible delay at startup because of all the jars to load, a small price to pay though.

The interface

The C functions libdmcl*.so exports are the following:

int dmAPIInit();
int dmAPIDeInit();
int dmAPIExec(const char *str);
char* dmAPIGet(const char *str);
int dmAPISet(const char *str, const char *arg);
char* dmAPIDesc(const char *str, int *code, int *type, int *sess);
char* dmAPIEval(const char *str, const char *arg);
char* dmGetPassword(const char *str);
int dmGetVersion( int *, int * );

However, the last 2 functions don’t seem to really be available from the library. Also, dmAPIDesc() (not to be confused with the describe server method) and dmAPIEval() are not documented in the API reference manual. Therefore, I’ve only considered the first 5 functions, the ones that really do the job as the building blocks of any Documentum script.
From within python, those functions are accessed through the wrapper functions below:

def dmInit()
def dmAPIDeInit()
def dmAPIGet(s)
def dmAPISet(s, value)
def dmAPIExec(stmt)

Those take care of the string conversions operations so you don’t have to; they are the only ones who directly talk to the Documentum API and the only ones to use to do API stuff. Generally, they return True or a string if successful, and False or None if not.
Every Documentum client should start with a call do dmInit() in order to load and initialize the libdmcl*.so library’s internal state. To guarantee that, the interface does it itself at load time. As this function is idempotent, further calls at script start up don’t have any effect. On the other hand, dmAPIDeInit() is not really necessary, just exiting the script will do.
Here, I named the proxy function dmInit() instead of dmAPIInit() for a reason. This function does not just invoke the library’s dmAPIInit() but also initializes the python interface and its usage of ctypes: it loads the shared library and describes the types of the API functions’ arguments (argtypes) and return values (restype). Here is a snippet of its the main part:

dmlib = 'libdmcl40.so'
...
dm = ctypes.cdll.LoadLibrary(dmlib); dm.restype = ctypes.c_char_p
...
dm.dmAPIInit.restype = ctypes.c_int;
dm.dmAPIDeInit.restype = ctypes.c_int;
dm.dmAPIGet.restype = ctypes.c_char_p; dm.dmAPIGet.argtypes = [ctypes.c_char_p] dm.dmAPISet.restype = ctypes.c_int; dm.dmAPISet.argtypes = [ctypes.c_char_p, ctypes.c_char_p] dm.dmAPIExec.restype = ctypes.c_int; dm.dmAPIExec.argtypes = [ctypes.c_char_p] status = dm.dmAPIInit()

The shared library whose name is in dmlib must be in the LD_LIBRARY_PATH (or SHLIB_PATH or LIBPATH, depending on the Unix flavor); specifying its full path name does work too.  As I wrote it before, if the script crashes, try to set it to libdmcl.so instead if it’s available.
The wrapper functions are used by all the verbs documented in the API Reference Manual. When the manual says for example:

Fetch
Purpose Fetches an object from the repository without placing a lock on the object.
Syntax
dmAPIExec("fetch,session,object_id[,type][,persistent_cache][,consistency_check_value]")
...
Return value
The Fetch method returns TRUE if successful or FALSE if unsuccessful.
...

it is the function dmAPIExec() that conveys the verb “fetch” and its arguments to the shared library. It takes just one argument, a string, and return, None if the call failed, a positive integer if it succeeded.

Another example:

Getservermap
Purpose Returns information about the servers known to a connection broker.
Syntax
dmAPIGet("getservermap,session,repository_name[,protocol][,host_name][,port_number]")
...
Return value
The Getservermap method returns a non-persistent ID for a server locator object.
...

Here, it’s dmAPIGet() that does it for the verb “getservermap”. It returns an empty string if the call failed (remapped to None to be more pythonic), a non-empty one with an ID if it succeeded.

For more usage comfort, a few functions have been added in the interface:

def connect(docbase, user_name, password):
"""
connects to given docbase as user_name/password;
returns a session id if OK, None otherwise
"""
def execute(session, dql_stmt):
"""
execute non-SELECT DQL statements;
returns TRUE if OK, False otherwise;
"""
def select(session, dql_stmt, attribute_names):
"""
execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
attributes_names is a list of attributes to extract from the result set;
return True if OK, False otherwise;
"""
def disconnect(session):
"""
closes the given session;
returns True if no error, False otherwise;
"""

Basically, they only wrap some error handling code around the calls to dmAPIGet()/dmAPIExec(). execute() and select() are just examples of how to use the interface and could be removed from it. Let’s give a look at the latter one for instance:

def select(session, dql_stmt, attribute_names):
   """
   execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
   attributes_names is a list of attributes to extract from the result set;
   return True if OK, False otherwise;
   """
   show("in select(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)

      s = ""
      for attr in attribute_names:
         s += "[" + attr + "]\t"
      print(s)
      resp_cntr = 0
      while dmAPIExec("next," + session + "," + query_id):
         s = ""
         for attr in attribute_names:
            value = dmAPIGet("get," + session + "," + query_id + "," + attr)
            if "r_object_id" == attr and value is None:
               raise(getOutOfHere)
            s += "[" + (value if value else "None") + "]\t"
         resp_cntr += 1
         show(str(resp_cntr) + ": " + s)
      show(str(resp_cntr) + " rows iterated")

      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)

      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in select():")
      print(e)
      traceback.print_stack()
      print(resp_cntr); print(attr); print(s); print("[" + value + "]")
      status = False
   finally:
      show("exiting select()")
      return status

If it weren’t for the error handling, it really looks like dmawk code fresh from the API manual !
And here are two invocations:

   print("")
   stmt = "select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document"
   status = DctmAPI.select(session, stmt, ("r_object_id", "object_name", "owner_name", "acl_domain", "acl_name"))
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful")

   print("")
   stmt = "select count(*) from dm_document"
   status = DctmAPI.select(session, stmt,  ["count(*)"])
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful"

Resulting in the following output:

in select(), dql_stmt=select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document
[r_object_id] [object_name] [owner_name] [acl_domain] [acl_name] 1: [0900c350800001d0] [Default Signature Page Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 2: [6700c35080000100] [CSEC Plugin] [dmadmin] [dmadmin] [dm_4500c35080000101] 3: [6700c35080000101] [Snaplock Connector] [dmadmin] [dmadmin] [dm_4500c35080000101] 4: [0900c350800001ff] [Blank Word 2007 / 2010 Document] [dmadmin] [dmadmin] [dm_4500c35080000101] 5: [0900c35080000200] [Blank Word 2007 / 2010 Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 6: [0900c35080000201] [Blank Word 2007 / 2010 Macro-enabled Document] [dmadmin] [dmadmin] [dm_4500c35080000101] 7: [0900c35080000202] [Blank Word 2007 / 2010 Macro-enabled Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 8: [0900c35080000203] [Blank Excel 2007 / 2010 Workbook] [dmadmin] [dmadmin] [dm_4500c35080000101] 9: [0900c35080000204] [Blank Excel 2007 / 2010 Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 10: [0900c350800001da] [11/21/2017 16:31:10 dm_PostUpgradeAction] [dmadmin] [dmadmin] [dm_4500c35080000101] 11: [0900c35080000205] [Blank Excel 2007 / 2010 Macro-enabled Workbook] [dmadmin] [dmadmin] [dm_4500c35080000101] 12: [0900c35080000206] [Blank Excel 2007 / 2010 Macro-enabled Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 13: [0900c35080000207] [Blank Excel 2007 / 2010 Binary Workbook] [dmadmin] [dmadmin] [dm_4500c35080000101] 14: [0900c35080000208] [Blank PowerPoint 2007 / 2010 Presentation] [dmadmin] [dmadmin] [dm_4500c35080000101] 15: [0900c35080000209] [Blank PowerPoint 2007 / 2010 Slide Show] [dmadmin] [dmadmin] [dm_4500c35080000101] ...
880 rows iterated
exiting select()
select [select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document] was successful
in select(), dql_stmt=select count(*) from dm_document
[count(*)] 1: [880] 1 rows iterated
exiting select()
select [select count(*) from dm_document] was successful

Admittedly, the above select() function could be more clever and find by itself the queried attributes by inspecting the returned collection; also, the output could be more structured. Stay tuned on this channel, it’s coming up !

The packaging

In order to make the interface easily usable, it has been packaged into a module named DctmAPI. To use it, just add an “import DctmAPI” statement in the client script and prefix the functions from the module with “DctmAPI”, the module namespace, when calling them, as shown in the example above.
I’ve given some thoughts about making a class out of it but the benefits were not so obvious because many functions are so generic that most of them would have been @staticmethod of the class anyway. Moreover, the only state variable would have been the session id, so instead of carrying it around, an instance of the class would have to be used instead, no real improvement here. Even worse, as the session id would have been hidden in the instance, the statements passed to an instance object would have to be changed not to include it and leave that to the instance, which would hurt the habits of using the standard API argument format; also, as a few API verbs don’t need a session id, exceptions to the rule would need to be introduced, which would mess the class even more. Therefore, I chose to stick as closer as possible to the syntax documented in the API manual, at the only cost of introducing a namespace with the module.

The source

Without further ado, here is the full interface module DctmAPI.py:

"""
This module is a python - Documentum binding based on ctypes;
requires libdmcl40.so/libdmcl.so to be reachable through LD_LIBRARY_PATH;
C. Cervini - dbi-services.com

The binding works as-is for both python2 and python3; no recompilation required; that's the good thing with ctypes compared to e.g. distutils/SWIG;
Under a 32-bit O/S, it must use the libdmcl40.so, whereas under a 64-bit Linux it must use the java backed one, libdmcl.so;

For compatibility with python3 (where strings are now unicode ones and no longer arrays of bytes, ctypes strings parameters are always converted to unicode, either by prefixing them
with a b if litteral or by invoking their encode('ascii', 'ignore') method; to get back to text from bytes, b.decode() is used;these works in python2 as well as in python3 so the source is compatible with these two versions of the language;
"""

import os
import ctypes
import sys, traceback

# use foreign C library;
# use this library in Content server = v6.x, 64-bit Linux;
#dmlib = 'libdmcl.so'

dm = 0
logLevel = 1

class getOutOfHere(Exception):
   pass

def show(mesg):
   "displays the message mesg if allowed"
   if logLevel > 0:
      print(mesg)

def dmInit():
   """
   initializes the Documentum part;
   returns True if successfull, False otherwise;
   """

   show("in dmInit()")
   global dm

   try:
      dm = ctypes.cdll.LoadLibrary(dmlib);  dm.restype = ctypes.c_char_p
      show("dm=" + str(dm) + " after loading library " + dmlib)
      dm.dmAPIInit.restype    = ctypes.c_int;
      dm.dmAPIDeInit.restype  = ctypes.c_int;
      dm.dmAPIGet.restype     = ctypes.c_char_p;      dm.dmAPIGet.argtypes  = [ctypes.c_char_p]
      dm.dmAPISet.restype     = ctypes.c_int;         dm.dmAPISet.argtypes  = [ctypes.c_char_p, ctypes.c_char_p]
      dm.dmAPIExec.restype    = ctypes.c_int;         dm.dmAPIExec.argtypes = [ctypes.c_char_p]
      status  = dm.dmAPIInit()
   except Exception as e:
      print("exception in dminit(): ")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show("exiting dmInit()")
      return True if 0 != status else False
   
def dmAPIDeInit():
   """
   releases the memory structures in documentum's library;
   returns True if no error, False otherwise;
   """
   status = dm.dmAPIDeInit()
   return True if 0 != status else False
   
def dmAPIGet(s):
   """
   passes the string s to dmAPIGet() method;
   returns a non-empty string if OK, None otherwise;
   """
   value = dm.dmAPIGet(s.encode('ascii', 'ignore'))
   return value.decode() if value is not None else None

def dmAPISet(s, value):
   """
   passes the string s to dmAPISet() method;
   returns TRUE if OK, False otherwise;
   """
   status = dm.dmAPISet(s.encode('ascii', 'ignore'), value.encode('ascii', 'ignore'))
   return True if 0 != status else False

def dmAPIExec(stmt):
   """
   passes the string s to dmAPIExec() method;
   returns TRUE if OK, False otherwise;
   """
   status = dm.dmAPIExec(stmt.encode('ascii', 'ignore'))
   return True if 0 != status else False

def connect(docbase, user_name, password):
   """
   connects to given docbase as user_name/password;
   returns a session id if OK, None otherwise
   """
   show("in connect(), docbase = " + docbase + ", user_name = " + user_name + ", password = " + password) 
   try:
      session = dmAPIGet("connect," + docbase + "," + user_name + "," + password)
      if session is None or not session:
         raise(getOutOfHere)
      else:
         show("successful session " + session)
         show(dmAPIGet("getmessage," + session).rstrip())
   except getOutOfHere:
      print("unsuccessful connection to docbase " + docbase + " as user " + user_name)
      session = None
   except Exception as e:
      print("Exception in connect():")
      print(e)
      traceback.print_stack()
      session = None
   finally:
      show("exiting connect()")
      return session

def execute(session, dql_stmt):
   """
   execute non-SELECT DQL statements;
   returns TRUE if OK, False otherwise;
   """
   show("in execute(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)
      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)
      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in execute():")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show(dmAPIGet("getmessage," + session).rstrip())
      show("exiting execute()")
      return status

def select(session, dql_stmt, attribute_names):
   """
   execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
   attributes_names is a list of attributes to extract from the result set;
   return True if OK, False otherwise;
   """
   show("in select(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)

      s = ""
      for attr in attribute_names:
         s += "[" + attr + "]\t"
      print(s)
      resp_cntr = 0
      while dmAPIExec("next," + session + "," + query_id):
         s = ""
         for attr in attribute_names:
            value = dmAPIGet("get," + session + "," + query_id + "," + attr)
            if "r_object_id" == attr and value is None:
               raise(getOutOfHere)
            s += "[" + (value if value else "None") + "]\t"
         resp_cntr += 1
         show(str(resp_cntr) + ": " + s)
      show(str(resp_cntr) + " rows iterated")

      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)

      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in select():")
      print(e)
      traceback.print_stack()
      print(resp_cntr); print(attr); print(s); print("[" + value + "]")
      status = False
   finally:
      show("exiting select()")
      return status

def disconnect(session):
   """
   closes the given session;
   returns True if no error, False otherwise;
   """
   show("in disconnect()")
   try:
      status = dmAPIExec("disconnect," + session)
   except Exception as e:
      print("Exception in disconnect():")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show("exiting disconnect()")
      return status

# initializes the interface;
dmInit()
The test script

Here is an example of script showing how to use the interface:

#!/usr/bin/env python

"""
Test the ctypes-based python interface to Documentum API;
"""

import DctmAPI

# -----------------
# main;
if __name__ == "__main__":
   DctmAPI.logLevel = 1

   # not really needed as it is done in the module itself;
   status = DctmAPI.dmInit()
   if status:
      print("dmInit() was successful")
   else:
      print("dmInit() was not successful")

   print("")
   session = DctmAPI.connect(docbase = "dmtest", user_name = "dmadmin", password = "dmadmin")
   if session is None:
      print("no session opened, exiting ...")
      exit(1)
   
   print("")
   dump = DctmAPI.dmAPIGet("dump," + session + "," + "0900c35080008107")
   print("object 0900c35080008107 dumped:\n" + dump)
   
   print("")
   stmt = "update dm_document object set language_code = 'FR' where r_object_id = '0900c35080008107'"
   status = DctmAPI.execute(session, stmt)
   if status:
      print("execute [" + stmt + "] was successful")
   else:
      print("execute [" + stmt + "] was not successful")

   print("")
   stmt = "select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document"
   status = DctmAPI.select(session, stmt, ("r_object_id", "object_name", "owner_name", "acl_domain", "acl_name"))
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful")

   print("")
   stmt = "select count(*) from dm_document"
   status = DctmAPI.select(session, stmt,  ["count(*)"])
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful")

   print("")
   status = DctmAPI.disconnect(session)
   if status:
      print("successfully disconnected")
   else:
      print("error while  disconnecting")

   print("")
   status = DctmAPI.dmAPIDeInit()
   if status:
      print("successfully deInited")
   else:
      print("error while  deInited")

I’m not a day to day user of python so I guess there are ways to make the interface more idiomatic, or pythonic as they say. Feel free to adapt it to your tastes and needs. Comments and suggestions are welcome of course.

 

Cet article Adding a Documentum extension into python est apparu en premier sur Blog dbi services.

Customizing PostgreSQL parameters in EDB containers in MiniShift/OpenShift

Fri, 2018-05-18 13:06

In the last two posts we deployed an EDB database container and two pgpool instances and then scaled that up to include a read only replica. In this post will use a ConfigMap to adjust parameters in postgresql.conf as you will probably need to do that when you start using the EDB containers in your environment.

A ConfigMap is an object that can be used to provide parameter/values pairs to the container which then will be added to postgresql.conf file of the database containers. Creating a ConfigMap is quite easy, all you need to do is to create a file called “postgresql.conf.in” which lists all the parameters you want to get adjusted:

dwe@dwe:~$ cat /opt/ConfigMaps/postgresql.conf.in
work_mem='12MB'
shared_buffers='56MB'

In that case we want to adjust work_mem and shared_buffers, that’s it. To load that into OpenShift by using the oc command line utility:

dwe@dwe:~$ oc create configmap postgres-map --from-file=/opt/ConfigMaps/postgresql.conf.in
configmap "postgres-map" created
11:01:22 dwe@dwe:~$ oc get configmaps postgres-map
NAME           DATA      AGE
postgres-map   1         12m
dwe@dwe:~$ oc get configmaps postgres-map -o yaml
apiVersion: v1
data:
  postgresql.conf.in: |+
    work_mem='12MB'
    shared_buffers='56MB'

kind: ConfigMap
metadata:
  creationTimestamp: 2018-05-18T08:49:35Z
  name: postgres-map
  namespace: myproject
  resourceVersion: "16618"
  selfLink: /api/v1/namespaces/myproject/configmaps/postgres-map
  uid: 63c3a154-5a78-11e8-992f-ca15bcd30222

The issue is now that our current template does not know anything about that ConfigMap. So either adjust it or create a new one like this (changes are highlighted):

cat edb-as10-0-edb-cust.yaml
apiVersion: v1
kind: Template
metadata:
   name: edb-as10-custom
   annotations:
    description: "Custom EDB Postgres Advanced Server 10.0 Deployment Config"
    tags: "database,epas,postgres,postgresql"
    iconClass: "icon-postgresql"
objects:
- apiVersion: v1 
  kind: Service
  metadata:
    name: ${DATABASE_NAME}-service 
    labels:
      role: loadbalancer
      cluster: ${DATABASE_NAME}
  spec:
    selector:                  
      lb: ${DATABASE_NAME}-pgpool
    ports:
    - name: lb 
      port: ${PGPORT}
      targetPort: 9999
    sessionAffinity: None
    type: LoadBalancer
- apiVersion: v1 
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-pgpool
  spec:
    replicas: 2
    selector:
      lb: ${DATABASE_NAME}-pgpool
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        labels:
          lb: ${DATABASE_NAME}-pgpool
          role: queryrouter
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-pgpool
          env:
          - name: DATABASE_NAME
            value: ${DATABASE_NAME} 
          - name: PGPORT
            value: ${PGPORT} 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/edb/edb-pgpool:v3.5
          imagePullPolicy: IfNotPresent
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5
    triggers:
    - type: ConfigChange
- apiVersion: v1
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-as10-0
  spec:
    replicas: 1
    selector:
      db: ${DATABASE_NAME}-as10-0 
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        creationTimestamp: null
        labels:
          db: ${DATABASE_NAME}-as10-0 
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-as10 
          env:
          - name: DATABASE_NAME 
            value: ${DATABASE_NAME} 
          - name: DATABASE_USER 
            value: ${DATABASE_USER} 
          - name: DATABASE_USER_PASSWORD
            value: 'postgres' 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: PGPORT
            value: ${PGPORT} 
          - name: RESTORE_FILE
            value: ${RESTORE_FILE} 
          - name: LOCALEPARAMETER
            value: ${LOCALEPARAMETER}
          - name: CLEANUP_SCHEDULE
            value: ${CLEANUP_SCHEDULE}
          - name: EFM_EMAIL
            value: ${EFM_EMAIL}
          - name: NAMESERVER
            value: ${NAMESERVER}
          - name: POD_NAMESPACE
            valueFrom:
              fieldRef:
                fieldPath: metadata.namespace
          - name: POD_NODE
            valueFrom:
              fieldRef:
                fieldPath: spec.nodeName 
          - name: POD_IP
            valueFrom:
              fieldRef:
                fieldPath: status.podIP 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/edb/edb-as:v10.3
          imagePullPolicy: IfNotPresent 
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5 
          livenessProbe:
            exec:
              command:
              - /var/lib/edb/testIsHealthy.sh
            initialDelaySeconds: 600 
            timeoutSeconds: 60 
          ports:
          - containerPort: ${PGPORT} 
          volumeMounts:
          - name: ${PERSISTENT_VOLUME}
            mountPath: /edbvolume
          - name: pg-initconf
            mountPath: /initconf
        dnsPolicy: ClusterFirst
        restartPolicy: Always
        volumes:
        - name: ${PERSISTENT_VOLUME}
          persistentVolumeClaim:
            claimName: ${PERSISTENT_VOLUME_CLAIM}
        - name: pg-initconf
          configMap:
            name: postgres-map
             
    triggers:
    - type: ConfigChange
parameters:
- name: DATABASE_NAME
  displayName: Database Name
  description: Name of Postgres database (leave edb for default)
  value: 'edb'
- name: DATABASE_USER
  displayName: Default database user (leave enterprisedb for default)
  description: Default database user
  value: 'enterprisedb'
- name: REPL_USER
  displayName: Repl user
  description: repl database user
  value: 'repl'
- name: PGPORT
  displayName: Database Port
  description: Database Port (leave 5444 for default)
  value: "5444"
- name: LOCALEPARAMETER
  displayName: Locale
  description: Locale of database
  value: ''
- name: CLEANUP_SCHEDULE
  displayName: Host Cleanup Schedule
  description: Standard cron schedule - min (0 - 59), hour (0 - 23), day of month (1 - 31), month (1 - 12), day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0). Leave it empty if you dont want to cleanup.
  value: '0:0:*:*:*'
- name: EFM_EMAIL
  displayName: Email
  description: Email for EFM
  value: 'none@none.com'
- name: NAMESERVER
  displayName: Name Server for Email
  description: Name Server for Email
  value: '8.8.8.8'
- name: PERSISTENT_VOLUME
  displayName: Persistent Volume
  description: Persistent volume name
  value: ''
  required: true
- name: PERSISTENT_VOLUME_CLAIM 
  displayName: Persistent Volume Claim
  description: Persistent volume claim name
  value: ''
  required: true
- name: RESTORE_FILE
  displayName: Restore File
  description: Restore file location
  value: ''
- name: ACCEPT_EULA
  displayName: Accept end-user license agreement (leave 'Yes' for default)
  description: Indicates whether user accepts the end-user license agreement
  value: 'Yes'
  required: true

Once you imported that into OpenShift (check here if you don’t know how to do that) you get a new template you can deploy from:

Selection_025

When you create a new deployment of that one (again, check here if you don’t know how to do that) you will notice several things when you login to the container once it is up and running:

dwe@dwe:~$ oc rsh edb-as10-0-1-gk8dt
sh-4.2$ psql postgres
psql.bin (10.3.8)
Type "help" for help.

postgres=# show work_mem;
 work_mem 
----------
 12MB
(1 row)

postgres=# show shared_buffers ;
 shared_buffers 
----------------
 56MB
(1 row)

First of all and this is what we wanted: The PostgreSQL instance came up with the parameters we specified in the ConfigMap. When you look at the volumes present in the container there is a new one named after what we specified in the template:

sh-4.2$ df -h
Filesystem      Size  Used Avail Use% Mounted on
none             18G  4.0G   14G  24% /
tmpfs          1002M     0 1002M   0% /dev
tmpfs          1002M     0 1002M   0% /sys/fs/cgroup
/dev/sda1        18G  4.0G   14G  24% /initconf
shm              64M   12K   64M   1% /dev/shm
tmpfs          1002M   16K 1002M   1% /run/secrets/kubernetes.io/serviceaccount

Inside that volume there is the postgresql.conf.in file we also specified in the template and that is linked to $PGDATA:

sh-4.2$ ls -la /initconf
total 12
drwxrwsrwx  3 root 1000070000 4096 May 18 09:55 .
drwxr-xr-x 85 root root       4096 May 18 09:55 ..
drwxr-sr-x  2 root 1000070000 4096 May 18 09:55 ..2018_05_18_09_55_19.162613490
lrwxrwxrwx  1 root root         31 May 18 09:55 ..data -> ..2018_05_18_09_55_19.162613490
lrwxrwxrwx  1 root root         25 May 18 09:55 postgresql.conf.in -> ..data/postgresql.conf.in

And finally we can confirm the content of that file:

sh-4.2$ cat /initconf/postgresql.conf.in 
work_mem='12MB'
shared_buffers='56MB'

You can do the same for pg_hba.conf by creating a new ConfigMap for pg_hba.conf.in. In the next post we’ll look at how EDB Failover Manager is configured inside the containers.

 

Cet article Customizing PostgreSQL parameters in EDB containers in MiniShift/OpenShift est apparu en premier sur Blog dbi services.

Scaling the EDB containers in MiniShift/OpenShift

Fri, 2018-05-18 03:21

When you followed the last post you should have two pgpool instances and one EDB Postgres instance running in OpenShift. pgpool is responsible for the load balancing, meaning: Send write requests to the master instance and spread read requests over all instances. In the current setup this does not make much sense as we only have one instance so in this post we will scale the setup to finally consist of one master and one read only replica container.

Lets check what pods are currently running:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-vldkj   1/1       Running   2          5d
edb-pgpool-1-699vh   1/1       Running   2          5d
edb-pgpool-1-nsgrm   1/1       Running   2          5d

As expected, two pgpool containers and one database container are up an running. When we login to one of the pgpool container we should see the pgpool processes:

dwe@dwe:~$ oc rsh edb-pgpool-1-699vh
sh-4.2$ ps -ef | grep pool
edbuser     63     0  0 15:27 ?        00:00:00 pgpool -D -d -f /etc/sysconfig/edb/pgpool3.5/pgpool.conf -a /etc/sysconfig/edb/pgpool3.5/pool_hba.conf
edbuser     64    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     65    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     66    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     67    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     68    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     69    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     70    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     71    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     72    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     73    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     74    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     75    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     76    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     77    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     78    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     79    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     80    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     81    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     82    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     83    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     84    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     85    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     86    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     87    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     88    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     89    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     90    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     91    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     92    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     93    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     94    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     95    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     96    63  0 15:27 ?        00:00:00 pgpool: PCP: wait for connection request
edbuser     97    63  0 15:27 ?        00:00:00 pgpool: worker process
edbuser    365   342  0 15:30 ?        00:00:00 grep pool

What I am interested in is the backend configuration. As we do have only one database container there should be only one backend configured:

sh-4.2$ cat /etc/sysconfig/edb/pgpool3.5/pgpool.conf | grep backend | egrep -v "^ |^#"
backend_hostname0 = '172.17.0.7'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
								   # but also for ordinary conection to backend.
fail_over_on_backend_error = off

Fine, this is what is expected. The same is of course true for the other container. The question is: We have two pgpool instances running against one database instance, how can we connect then? What in addition happened when we deployed the setup is that a service was created:

dwe@dwe:~$ oc get svc -o wide
NAME          TYPE           CLUSTER-IP      EXTERNAL-IP                     PORT(S)          AGE       SELECTOR
edb-service   LoadBalancer   172.30.162.55   172.29.228.247,172.29.228.247   5444:30726/TCP   5d        lb=edb-pgpool

This services proxies the request to one of the pgpool instances which then routes the connection to the database instance. Lets try to connect from outside:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U postgres -W
Password for user postgres: 
psql: FATAL:  md5 authentication failed
DETAIL:  pool_passwd file does not contain an entry for "postgres"

Hm. Looking at the available databases and users in the database containers:

dwe@dwe:~$ oc rsh edb-as10-0-1-vldkj
sh-4.2$ psql edb
psql.bin (10.3.8)
Type "help" for help.

edb=# \l
                                 List of databases
   Name    |    Owner     | Encoding  | Collate | Ctype | ICU |  Access privileges  
-----------+--------------+-----------+---------+-------+-----+---------------------
 edb       | enterprisedb | SQL_ASCII | C       | C     |     | 
 postgres  | edbuser      | SQL_ASCII | C       | C     |     | 
 template0 | edbuser      | SQL_ASCII | C       | C     |     | =c/edbuser         +
           |              |           |         |       |     | edbuser=CTc/edbuser
 template1 | edbuser      | SQL_ASCII | C       | C     |     | =c/edbuser         +
           |              |           |         |       |     | edbuser=CTc/edbuser
(4 rows)

edb=# \du
                                         List of roles
       Role name       |                         Attributes                         | Member of 
-----------------------+------------------------------------------------------------+-----------
 aq_administrator_role | No inheritance, Cannot login                              +| {}
                       | Profile default                                            | 
 edbuser               | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                       | Profile default                                            | 
 enterprisedb          | Superuser                                                 +| {}
                       | Profile default                                            | 
 repl                  | Replication                                               +| {}
                       | Profile default                                            | 

… it becomes clear: There is no user postgres. Using the edbuser it works fine:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# 

How can we verify that the service actually is routing the connection to both pgpool instances? PostgreSQL comes with a set of administration functions and one of those can be used to verify that.

Do a first connection and ask for the client address (the client is one of the pgpool instances in this case):

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# select inet_client_addr();
 inet_client_addr 
------------------
 172.17.0.6
(1 row)

Do a second one:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# select inet_client_addr();
 inet_client_addr 
------------------
 172.17.0.7
(1 row)

postgres=# 

This two IP addresses are my two pgpool instances so this confirms that the service is behaving as expected. As having a single instance in that setup is somehow useless lets scale the setup by adding another database container. We’ll be doing that with the console for now:

Selection_022

After a few seconds another pod is running:
Selection_023

What I expect is that the pgpool instances got re-configured to include the new backend. Looking at the pods:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-frc99   1/1       Running   0          15m
edb-as10-0-1-gkpgq   1/1       Running   0          31m
edb-pgpool-1-lvwsq   1/1       Running   0          31m
edb-pgpool-1-nh4qb   1/1       Running   0          31m

Lets check the configuration of the first pgpool instance:

dwe@dwe:~$ oc rsh edb-pgpool-1-lvwsq
sh-4.2$ ps -ef | grep conf
edbuser   1918     0  0 07:43 ?        00:00:00 pgpool -D -d -f /etc/sysconfig/edb/pgpool3.5/pgpool.conf -a /etc/sysconfig/edb/pgpool3.5/pool_hba.conf
edbuser   2508  2483  0 07:48 ?        00:00:00 grep conf
sh-4.2$ cat /etc/sysconfig/edb/pgpool3.5/pgpool.conf | grep backend | egrep -v "^ |^#"
backend_hostname0 = '172.17.0.7'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
								   # but also for ordinary conection to backend.
fail_over_on_backend_error = off
backend_hostname1 = '172.17.0.5'
backend_port1 = 5444
backend_weight1 = 1
backend_data_directory1 = '/data1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
sh-4.2$ 

Here we go, works as expected. What is a bit strange is that $PGDATA on the second backend is in /data1 where it is in /var/lib/edb/as9.6/data on the first backend. From now on read only queries should land on either the slave or the master node node and statements that modify data should land on the master node, lets check what is master and what is replica:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "show pool_nodes" postgres
Password for user edbuser: 
 node_id |  hostname  | port | status | lb_weight |  role   | select_cnt 
---------+------------+------+--------+-----------+---------+------------
 0       | 172.17.0.7 | 5444 | 2      | 0.500000  | primary | 1
 1       | 172.17.0.5 | 5444 | 2      | 0.500000  | standby | 0
(2 rows)

When I do two read only statements I should be round-robined:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.7
(1 row)

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

Disabling load balancing should bring me to the master:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select /*NO LOAD BALANCE*/ inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select /*NO LOAD BALANCE*/ inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

Works quite fine. In the next post we’ll add ConfigMaps to customize our deployment. Usually you want to pre-configure the deployment so it fits for your requirements and that is what ConfigMaps are for.

 

Cet article Scaling the EDB containers in MiniShift/OpenShift est apparu en premier sur Blog dbi services.

12c upuserxt.lst, upobjxt.lst & Oracle Maintained objects/users

Tue, 2018-05-15 16:24

Mike Dietrich has blogged recently about upuserxt.lst and upobjxt.lst and how to query them with external table. The first time I’ve seen those ‘.lst’ files, the default extension for sqlplus spool files, I wondered whether they were provided in ?/rdbms/admin on purpose, or if they were just some leftovers from some tests Oracle did before packaging the Oracle Home. Finally, I realized that they were there on purpose and that those ‘.lst’ are important files when upgrading to 12c.

I’ll look at an 18c Oracle Home (/rdbms/admin) in the Oracle Cloud but that applies to all 12c (and 18c is a 12cR2 patchset). One of the most important little feature of 12c is the tagging of Oracle Supplied objects and users. Before 12c it was a nightmare to distinguish system users from application ones. I detailed that in a previous post.

At database creation: _oracle_script

In a newly created 12c database, all the objects and users belonging to the system are flagged with ORACLE_MAINTAINED=Y

Here is an example listing system users and roles:

SQL> select listagg(username,',' on overflow truncate) within group (order by username) from dba_users where oracle_maintained='Y';LISTAGG(USERNAME,','ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYUSERNAME)
 
ANONYMOUS,APPQOSSYS,AUDSYS,CTXSYS,DBSFWUSER,DBSNMP,DIP,DVF,DVSYS,GGSYS,GSMADMIN_INTERNAL,GSMCATUSER,GSMUSER,LBACSYS,MDDATA,MDSYS,OJVMSYS,OLAPSYS,ORACLE_OCM,ORDDATA,ORDPLUGINS,ORDSYS,OUTLN,REMOTE_SCHEDULER_AGENT,SI_INFORMTN_SCHEMA,SYS,SYS$UMF,SYSBACKUP,SYSDG,SYSKM,SYSRAC,SYSTEM,WMSYS,XDB,XS$NULL
 
SQL> select listagg(role,',' on overflow truncate) within group (order by role) from dba_roles where oracle_maintained='Y';
LISTAGG(ROLE,','ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYROLE)
 
ADM_PARALLEL_EXECUTE_TASK,APPLICATION_TRACE_VIEWER,AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE,AUDIT_ADMIN,AUDIT_VIEWER,AUTHENTICATEDUSER,CAPTURE_ADMIN,CDB_DBA,CONNECT,CTXAPP,DATAPATCH_ROLE,DATAPUMP_EXP_FULL_DATABASE,DATAPUMP_IMP_FULL_DATABASE,DBA,DBFS_ROLE,DBJAVASCRIPT,DBMS_MDX_INTERNAL,DV_ACCTMGR,DV_ADMIN,DV_AUDIT_CLEANUP,DV_DATAPUMP_NETWORK_LINK,DV_GOLDENGATE_ADMIN,DV_GOLDENGATE_REDO_ACCESS,DV_MONITOR,DV_OWNER,DV_PATCH_ADMIN,DV_POLICY_OWNER,DV_PUBLIC,DV_REALM_OWNER,DV_REALM_RESOURCE,DV_SECANALYST,DV_STREAMS_ADMIN,DV_XSTREAM_ADMIN,EJBCLIENT,EM_EXPRESS_ALL,EM_EXPRESS_BASIC,EXECUTE_CATALOG_ROLE,EXP_FULL_DATABASE,GATHER_SYSTEM_STATISTICS,GDS_CATALOG_SELECT,GGSYS_ROLE,GLOBAL_AQ_USER_ROLE,GSMADMIN_ROLE,GSMUSER_ROLE,GSM_POOLADMIN_ROLE,HS_ADMIN_EXECUTE_ROLE,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,IMP_FULL_DATABASE,JAVADEBUGPRIV,JAVAIDPRIV,JAVASYSPRIV,JAVAUSERPRIV,JAVA_ADMIN,JMXSERVER,LBAC_DBA,LOGSTDBY_ADMINISTRATOR,OEM_ADVISOR,OEM_MONITOR,OLAP_DBA,OLAP_USER,OLAP_XS_ADMIN,OPTIMIZER_PROCESSING_RATE,ORDADMIN,PDB_DBA,PROVISIONER,RDFCTX_ADMIN,RECOVERY_CATALOG_OWNER,RECOVERY_CATALOG_OWNER_VPD,RECOVERY_CATALOG_USER,RESOURCE,SCHEDULER_ADMIN,SELECT_CATALOG_ROLE,SODA_APP,SYSUMF_ROLE,WM_ADMIN_ROLE,XDBADMIN,XDB_SET_INVOKER,XDB_WEBSERVICES,XDB_WEBSERVICES_OVER_HTTP,XDB_WEBSERVICES_WITH_PUBLIC,XS_CACHE_ADMIN,XS_CONNECT,XS_NAMESPACE_ADMIN,XS_SESSION_ADMIN

And here is an exemple listing the owners of system objects flagged with ORACLE_MAINTAINED=Y

SQL> select listagg(num||' '||owner,',' on overflow truncate) within group (order by num) from (select owner,count(*) num from dba_objects where oracle_maintained='Y' group by owner);LISTAGG(NUM||''||OWNER,','ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYNUM)
 
6 APPQOSSYS,8 DBSFWUSER,8 ORACLE_OCM,8 SI_INFORMTN_SCHEMA,10 ORDPLUGINS,10 OUTLN,13 REMOTE_SCHEDULER_AGENT,22 DVF,24 OJVMSYS,25 OLAPSYS,35 AUDSYS,55 DBSNMP,209 GSMADMIN_INTERNAL,239 LBACSYS,292 ORDDATA,398 DVSYS,399 WMSYS,412 CTXSYS,466 SYSTEM,1029 XDB,2574 MDSYS,3171 ORDSYS,12173 PUBLIC,51069 SYS

How this is done? That’s easy. All system objects are created by Oracle scripts, such as those called by catalog.sql and catproc.sql during database creation. Those scripts set “_oracle_script”=true before running the DDL and all object created while “_oracle_script”=true is flagged as Oracle Maintained.

If, in a lab (not in prod), you create your own object in the same way, they will also be flagged as Oracle Maintained:

SQL> connect / as sysdba
Connected.
SQL> alter session set "_oracle_script"=true;
Session altered.
 
SQL> create user FRANCK identified by myself;
User FRANCK created.
SQL> alter user FRANCK quota unlimited on users;
User FRANCK altered.
 
SQL> create table FRANCK.DEMO(n primary key) as select 1 from dual;
Table FRANCK.DEMO created.
SQL> alter session set "_oracle_script"=false;
Session altered.
 
SQL> select username,oracle_maintained from dba_users where username='FRANCK';
 
USERNAME ORACLE_MAINTAINED
-------- -----------------
FRANCK Y
SQL> select owner,object_name,object_type,oracle_maintained from dba_objects where owner='FRANCK';
 
OWNER OBJECT_NAME OBJECT_TYPE ORACLE_MAINTAINED
------ ---- ----- -
FRANCK DEMO TABLE Y
FRANCK SYS_C007409 INDEX Y

So, this one is easy. Database creation runs with “_oracle_script”=true and objects and users created when this parameter is set to true are flagged as Oracle Supplied objects.

And during upgrades?

When you upgrade from 11g to 12c you don’t have this Oracle Maintained information. The catupgrd runs with “_oracle_script”=true but this script does not create all objects. However the upgraded database has all system objects flagged as Oracle Maintained. This is where upuserxt.lst upobjxt.lst are used.

When Oracle developers build a new Oracle Home to be shipped, they create a database (including all options I suppose) and then run the utlupox.sql script. This script will list all Oracle Maintained users and objects, just relying on the flag that has been set during creation, and spool to the upuserxt.lst upobjxt.lst files. And those files will be shipped in the Oracle Home (all that under /rdbms/admin).

These .lst files will be used when upgrading from pre-12c in order to set the flags for Oracle Maintained objects. The external tables SYS.USERXT on upuserxt.lst and SYS.OBJXT on upobjxt.lst are created by catupcox.sql and, finally, those tables are read by catuposb.sql to set Oracle Maintained in USER$ and OBJ$. The catuposb.sql is a bit more complex that that because there are objects that can have different name when a database is created.

Note that this information about Oracle Maintained objects, in addition to being very useful for us, is crucial when you further convert the non-CDB to a PDB because those will become metadata links.

 

Cet article 12c upuserxt.lst, upobjxt.lst & Oracle Maintained objects/users est apparu en premier sur Blog dbi services.

Configuring WebLogic Server 12.2.1.3 Diagnostics Framework policies and actions

Tue, 2018-05-15 09:28

Configure a Policy-action (new name for watch-notification) to send a JMX notification when the monitored WebLogic Server is having stuck threads or high memory usage. We will use a simple JMX notification listener program which can process WLDF JMX notifications. In this case, it will simply print the contents of the notification.
For simplicity, we will have only two servers in the domain, AdminServer (admin-server) and Server1 (managed-server). We will configure WLDF on the Server1 to send a JMX notification when the Heap Free of the managed-server Server1 (actually, any server it is targeted to within the domain) is less than 20% or as soon as there is a Stuck thread.
We will use WebLogic Server Administration Console to configure WLDF. It can also be done with WLST. Console provides a nice feature to Create Repeatable Configuration Scripts, which can be handy for building such scripts. Configuration steps for this case consist of:
1. Create a Diagnostic Module.
2. Enable periodic metrics collection in the Diagnostic module.
3. Create a JMX notification type.
4. Create a policy to detect if the WebLogic Server is having stuck threads
5. Create a policy to detect high memory usage on the WebLogic Server
6. Target the Diagnostic module to the WebLogic Server server1
We will go over these steps and see how to configure WLDF using Console for this task.

1. Create a Diagnostic Module.

a) Log into Console and acquire Edit Lock so we can add new configuration to WebLogic Server.
b) From the left navigation tree, open the Diagnostics node and click on Diagnostic Modules. Console will show a table of existing Diagnostic modules within the domain. Click the New button to create a new diagnostic module. Call it myWLDF. Click OK to create the module. At this point, we have an empty myWLDF diagnostic module.

2. Enable periodic metrics collection in the Diagnostic module.

a) Click on the myWLDF module link in the table of Diagnostics modules.
b) Click on Collected Metrics sub-tab under Configuration tab.
c) Check the Enabled checkbox and set the Sampling Period to 10000 (10 seconds). Click Save.

3. Create a JMX notification type.

a) Configuring a policy/actions (Watch and Notifications in earlier versions) has two aspects. The first aspect is a policy (watch rule) which specifies the condition that WLDF will check. The second aspect is the set of actions (notifications) that will be sent when the rule condition is met. Console provides configuration assistants to make the configuration task easier. To create an action type:
b) Click Policies and Actions sub-tab under Configuration tab.
On the Actions sub-tab, click New in the Actions table.
c) Select “JMX Notification” for the notification type from the drop down list and click Next.
d) Give a name to the notification type (myJMX)
e) Keep the Notification Type to its default value
f) Check the Enable Notification checkbox and click OK to create the notification type.

4. Create a policy to detect servers having stuck threads

Now, we will create the policy rule based on runtime mbean data. Specifically, we will use the StuckThreadCount attribute on the WorkManagerRuntime mbeans. For each server within the domain, there is a WorkManagerRuntime mbean in the domain runtime mbean server. The StuckThreadCount attribute reflects the current number of stuck thread on the server. We will configure a rule which will fire as soon one server in the targets list is having a stuck thread.
a) Click on the Policies sub-tab -> Configuration. Click New in the Policies table.
b) Set Policy Name to WatchServerStuckThread. Select Collected Metrics for Policy Type, check Enabled checkbox and click Next.
c) Set the Policy Expression as below and Click Next:

wls.runtime.query('com.bea:Type=WorkManagerRuntime,*','StuckThreadCount').stream().anyMatch( x -> x > 0 )

Note that the syntax of Policy Expressions has changed from the previous WebLogic Versions. Starting from this version, the policy expression is to be provided in Java Expression Language (EL).

d) Keep The Frequency to “Every N Seconds” and Click Next
e) Set the Repeat parameter to 5 and click Next
f) In this wizard keep the default and click Next
g) In the diagnostic Actions part, in the available actions, select the myJMX created earlier and move it to the “chosen” actions.
h) Click Finish

5. Create a policy to detect high memory usage on the WebLogic Server

Now, we will create the policy rule based on runtime mbean data. Specifically, we will use the StuckThreadCount attribute on the WorkManagerRuntime mbeans. For each server within the domain, there is a WorkManagerRuntime mbean in the domain runtime mbean server. The StuckThreadCount attribute reflects the current number of stuck thread on the server. We will configure a rule which will fire as soon one server in the targets list is having a stuck thread.
a) Click on the Policies sub-tab -> Configuration. Click New in the Policies table.
b) Set Policy Name to WatchServerStuckThread. Select Collected Metrics for Policy Type, check Enabled checkbox and click Next.
c) Set the Policy Expression as below and Click Next:

wls.runtime.serverRuntime.JVMRuntime.heapFreePercent < 20

d) Keep The Frequency to “Every N Seconds” and Click Next
e) Set the Repeat parameter to 5 and click Next
f) In this wizard keep the default and click Next
g) In the diagnostic Actions part, in the available actions, select the myJMX created earlier and move it to the “chosen” actions.
h) Click Finish
6. Target the Diagnostic module to the WebLogic Server server1
a) Click on the Targets TAB
b) In the list of possible Targets, select the Server1 and click on Save
c) Activate Changes

Receiving Notifications

WLDF sends a JMX notification on a specific WLDF runtime mbean, whose ObjectName is of the form:
com.bea:Name=DiagnosticsJMXNotificationSource,ServerRuntime=$SERVER,Type=WLDFWatchJMXNotificationRuntime,WLDFRuntime=WLDFRuntime,WLDFWatchNotificationRuntime=WatchNotification
where $SERVER is the name of the WebLogic Server instance. For our case (Server1), it is:
com.bea:Name=DiagnosticsJMXNotificationSource,ServerRuntime=Server1,Type=WLDFWatchJMXNotificationRuntime,WLDFRuntime=WLDFRuntime,WLDFWatchNotificationRuntime=WatchNotification
By registering for JMX notifications on this mbean, a client program can listen to generated notifications.
We will use the JMXWatchNotificationListener.java provided in the Oracle WLDF documentation (see references). It is a simple notification listener for WLDF JMX notifications. It simply prints the contents of received notification, but can be easily adapted to perform other actions.

A sample Java code of such listener can be downloaded from the Oracle WebLogic Diagnostic Framework documentation (here)

To run it for this blog sample run:

java JMXWatchNotificationListener vm01 7006 weblogic Welcome1 Server1

Note: The WebLogic Managed Server named Server1 is listening on port 7006.

Sample WLDF message sent when the WebLogic Server is having Stuck Threads

Notification name: myJMXNotif called. Count= 79.
Watch severity: Notice
Watch time: Apr 24, 2018 12:08:35 PM CEST
Watch ServerName: Server1
Watch RuleType: Harvester
Watch Rule: wls.runtime.query('com.bea:Type=WorkManagerRuntime,*','StuckThreadCount').stream().anyMatch( x -> x > 0 )
Watch Name: WatchServerStuckThread
Watch DomainName: wldf_domain
Watch AlarmType: None
Watch AlarmResetPeriod: 60000

Sample WLDF message sent when the WebLogic Server heap free becomes low (less than 20%)

Notification name: myJMXNotif called. Count= 114.
Watch severity: Notice
Watch time: Apr 24, 2018 12:11:45 PM CEST
Watch ServerName: Server1
Watch RuleType: Harvester
Watch Rule: wls.runtime.serverRuntime.JVMRuntime.heapFreePercent < 20
Watch Name: WatchServerLowHeapFreePercent
Watch DomainName: wldf_domain
Watch AlarmType: None
Watch AlarmResetPeriod: 60000

 

Cet article Configuring WebLogic Server 12.2.1.3 Diagnostics Framework policies and actions est apparu en premier sur Blog dbi services.

PDB RMAN backups available after plugging in on a new CDB with Oracle 18c

Tue, 2018-05-15 08:53

With Oracle 18c, it is possible to use PDB rman backups created on the source CDB (they are called PREPLUGIN backups) when the PDB has been relocated to a target CDB.

In my environment, my original CDB is DB18, with the PDB named pdborig. The target CDB is PSI18.

The first step consist in running a rman backup on pdborig:

oracle@localhost:/u00/app/oracle/ [DB18] rman target sys/manager@pdborig
Recovery Manager: Release 18.0.0.0.0 - Production on Tue May 15 10:57:38 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB18:PDBORIG (DBID=3031125269)

RMAN> backup pluggable database pdborig plus archivelog;

Starting backup at 15-MAY-2018 10:57:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 device type=DISK
skipping archived logs when connected to a PDB
backup cancelled because there are no files to backup
Finished backup at 15-MAY-2018 10:57:56

Starting backup at 15-MAY-2018 10:57:56
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00029 name=/u00/app/oracle/oradata/DB18/pdborig/undotbs01.dbf
input datafile file number=00027 name=/u00/app/oracle/oradata/DB18/pdborig/system01.dbf
input datafile file number=00028 name=/u00/app/oracle/oradata/DB18/pdborig/sysaux01.dbf
input datafile file number=00030 name=/u00/app/oracle/oradata/DB18/pdborig/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-MAY-2018 10:57:56
channel ORA_DISK_1: finished piece 1 at 15-MAY-2018 10:57:59
piece handle=/u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/
2018_05_15/o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp 
tag=TAG20180515T105756 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-MAY-2018 10:57:59

Starting backup at 15-MAY-2018 10:57:59
using channel ORA_DISK_1
skipping archived logs when connected to a PDB
backup cancelled because there are no files to backup
Finished backup at 15-MAY-2018 10:57:59

Then we have to export the RMAN backup metadata for the non CDB into its dictionary using dbms_pdb.exportrmanbackup()

oracle@localhost:/u00/app/oracle/ [DB18] sqlplus sys/manager@pdborig as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:00:38 2018
Version 18.1.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> execute dbms_pdb.exportrmanbackup();

PL/SQL procedure successfully completed.

As my environment is configured with TDE, in order to unplug the pdborig, we need to export the master key of the container database otherwise we will receive the following error message:

SQL> alter pluggable database pdborig 
unplug into '/home/oracle/pdborig.xml';
alter pluggable database pdborig unplug into '/home/oracle/pdborig.xml'
*
ERROR at line 1:
ORA-46680: master keys of the container database must be exported

We export the master key:

SQL> alter session set container=PDBORIG;
Session altered

SQL> administer key management 
  2  export encryption keys with secret "manager_cdb" 
  3  to '/home/oracle/pdborig.p12'
  4  identified by manager_cdb;

keystore altered.

SQL> alter pluggable database PDBORIG close immediate;

Pluggable database altered.

SQL> alter pluggable database PDBORIG unplug into '/home/oracle/pdborig.xml';

Pluggable database altered.

Finallly on the target CDB named PSI18, we first have to create a wallet and 
open the keystore. Just remember you have to define wallet_root and 
tde_configuration in your CDB environment in order to use TDE:


SQL> show parameter wallet

NAME		       TYPE	    VALUE
-------------------- ----------- -------------------------------------
wallet_root	       string	 /u00/app/oracle/admin/PSI18/walletcdb

SQL> alter system set tde_configuration="keystore_configuration=file";
System altered.

We create and open the keystore on the target CDB and we import the master key:

SQL> administer key management create keystore identified by manager_cdb;

keystore altered.

SQL> administer key management set keystore open 
identified by manager_cdb container=all;

keystore altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> administer key management import encryption keys 
     with secret "manager_cdb" from '/home/oracle/pdborig.p12'
     identified by "manager_cdb" with backup;

keystore altered.

We create pdbnew on the target CDB using pdborig.xml:

SQL> create pluggable database pdbnew using '/home/oracle/pdborig.xml'
file_name_convert=
('/u00/app/oracle/oradata/DB18/pdborig','/home/oracle/oradata/PSI18/pdbnew');

Pluggable database created.

We open the pluggable database pdbnew:

SQL> alter pluggable database pdbnew open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBNEW			  READ WRITE NO

And now the non CDB PDBNEW has been plugged in the target CDB, we can ask if the rman backups are visible because we had exported the rman metadata backup. To visualize that we have to use the preplugin clause:

RMAN> list preplugin backup of pluggable database pdbnew;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
9       Full    463.15M    DISK        00:00:01     15-MAY-2018 10:56:51
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105650
        Piece Name: /u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105650_fho86ltx_.bkp
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
10      Full    463.15M    DISK        00:00:01     15-MAY-2018 10:57:57
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105756
        Piece Name: /u00/app/oracle/fast_recovery_area/DB18/DB18/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

We can also display the archive logs preplugin backups:

RMAN> list preplugin archivelog all;

List of Archived Log Copies for database with db_unique_name PSI18
=====================================================================

Key     Thrd Seq     S Low Time            
------- ---- ------- - --------------------
.....

6       1    16      A 15-MAY-2018 10:08:53
/u00/app/oracle/fast_recovery_area/archivelog/2018_05_15/o1_mf_1_16_fho5r944_.a
...

So let’s see if we can make a restore and recover test:

We delete the user01.dbf datafile:

oracle@localhost:/u00/app/oracle/ [PSI18] rm /home/oracle/oradata/PSI18/pdbnew/users01.dbf

oracle@localhost:/u00/app/oracle/oradata/DB18/pdbseed/ [PSI18] sq

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:20:47 2018
Version 18.1.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> alter pluggable database pdbnew close;

Pluggable database altered.

SQL> alter pluggable database pdbnew open;
alter pluggable database pdbnew open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 23 - see DBWR trace file

Now we try to restore: we connect with rman to the target CDB and we set the PDB that needs to be restored with the command set preplugin container=pdbnew:

oracle@localhost:/u00/app/oracle/ [PSI18] rman target sys/manager@psi18

Recovery Manager: Release 18.0.0.0.0 - Production on Tue May 15 11:25:06 2018
Version 18.1.0.0.0

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

connected to target database: PSI18 (DBID=3896993764)

RMAN> set preplugin container=pdbnew;

executing command: SET PREPLUGIN CONTAINER
using target database control file instead of recovery catalog

RMAN> list preplugin backup of pluggable database pdbnew;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
9       Full    463.15M    DISK        00:00:01     15-MAY-2018 10:56:51
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105650
        Piece Name: /u00/app/oracle/fast_recovery_area/
/6C3BAD3B7C73354AE0530100007F9AD9/backupset/
2018_05_15/o1_mf_nnndf_TAG20180515T105650_fho86ltx_.bkp
  List of Datafiles in backup set 9
  Container ID: 4, PDB Name: PDBNEW
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
10      Full    463.15M    DISK        00:00:01     15-MAY-2018 10:57:57
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105756
        Piece Name: /u00/app/oracle/fast_recovery_area
/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
  List of Datafiles in backup set 10
  Container ID: 4, PDB Name: PDBNEW
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

We run the restore command with the preplugin clause:

RMAN> restore pluggable database pdbnew from preplugin;

Starting restore at 15-MAY-2018 11:26:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00020 to 
/home/oracle/oradata/PSI18/pdbnew/system01.dbf
channel ORA_DISK_1: restoring datafile 00021 to 
/home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00022 to 
/home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00023 to 
/home/oracle/oradata/PSI18/pdbnew/users01.dbf
channel ORA_DISK_1: reading from backup piece /u00/app/oracle/fast_recovery_area/
DB18/DB18/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
channel ORA_DISK_1: piece handle=/u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp tag=TAG20180515T105756
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 15-MAY-2018 11:26:28

We run the recover command with the preplugin clause:

RMAN> recover pluggable database pdbnew from preplugin;

Starting recover at 15-MAY-2018 11:27:02
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/15/2018 11:27:03
RMAN-06054: media recovery requesting unknown archived log 
for thread 1 with sequence 17 and starting SCN of 1081326

We have to catalog the archive logs generated after the backup into the target CDB by issuing the catalog preplugin archivelog command :

RMAN> catalog preplugin archivelog '/u00/app/oracle/fast_recovery_area/
DB18/DB18/archivelog/2018_05_15/o1_mf_1_17_fhob69t7_.arc';

cataloged archived log
archived log file name=/u00/app/oracle/fast_recovery_area/DB18/DB18/
archivelog/2018_05_15/o1_mf_1_17_fhob69t7_.arc RECID=7 STAMP=0

Finally the recover command runs successfully:

RMAN> recover pluggable database pdbnew from preplugin;

Starting recover at 15-MAY-2018 11:32:25
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file 
/u00/app/oracle/fast_recovery_area/DB18/DB18/archivelog/2018_05_15/
o1_mf_1_17_fhob69t7_.arc
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-MAY-2018 11:32:26

We finish the recover and open the target PDB:

RMAN> recover pluggable database pdbnew;

Starting recover at 15-MAY-2018 11:33:10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 15-MAY-2018 11:33:10

RMAN> alter pluggable database pdbnew open;

Statement processed

RMAN> exit


Recovery Manager complete.

As far we can see, the target PDB has been successfully restored and recovered:

oracle@localhost:/u00/app/oracle/oradata/DB18// [PSI18] sq

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:33:37 2018
Version 18.1.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBNEW			  READ WRITE NO

The preplugin clause can be used on the target CDB with rman commands like restore, recover, crosscheck, list and delete.

This new feature helps to maintain the backup compliance after moving to a new target CDB.

 

Cet article PDB RMAN backups available after plugging in on a new CDB with Oracle 18c est apparu en premier sur Blog dbi services.

ADWC – connect from your premises

Mon, 2018-05-14 05:44

In the previous post about the Autonomous Data Warehouse Service, I’ve run queries though the Machine Learning Notebooks. But you obviously want to connect to it from your premises, with SQL*Net.

CaptureADWCconnect001Of course the connection, going through the public internet, must be secured. If you already use a managed service like the Oracle Exadata Express Cloud Service, you already know how to do: download a .zip containing the connection string and the wallet and certificate for SQL*Net encryption.

You get it from the Service Console, logged as the ADMIN user, and Administration tab. The Download Client Credentials asks you for the wallet password. However, this is not a password to protect the .zip file and the .zip file contains an auto-login wallet, so keep it secured.

SQL Developer

CaptureADWCconnect002 The simplest use of this file is with SQL Developer because you don’t even have to unzip it. Just choose a ‘Cloud PDB’ connection type, enter the path of the .zip file as Configuration File, the password as Keystore Password and the ADMIN user (or any user you have created with the Oracle ML Users).

In the tnsnames.ora provided in the .zip file there are 3 network service names connecting to 3 different services: _low, _medium and _high. They map to the resource manager plan so that you can run your queries with different priorities.

SQLcl thin

With SQLcl you do not need to unzip the credentials file, at least when you are using thin JDBC (the default).
You just register it with:

18:53:12 SQL> set cloudconfig /media/sf_share/ADWC/wallet_ADWC.zip
Using temp directory:/tmp/oracle_cloud_config4174171941677611695

and you are ready to connect to the _low, _medium and _high services.

As you see, it unzips the file into a temporary directory so you have to do it each time you run SQLcl. You can add this to login.sql and may add some housekeeping as this temporary directory may remain. Or run all this in a docker container.

This is simple, at least if you are running the latest Java 8 which includes the Java Cryptography Extension (JCE). If it is not the case, as when you use the Java Home provided with 18c (1.8.0_152), you have to add the jars yourself. But don’t worry, all is explained:

SQL> set cloudconfig /media/sf_share/ADWC/wallet_ADWC.zip
***** JCE NOT INSTALLED ****
***** CAN NOT CONNECT TO PDB Service without it ****
Current Java: /u01/app/oracle/product/18.0.0/dbhome_1/jdk/jre
Follow instructions on http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
SQL>

Of course the alternative is to install the latest Java

$ sudo rpm -i jre-8u171-linux-x64.rpm
Unpacking JAR files...
plugin.jar...
javaws.jar...
deploy.jar...
rt.jar...
jsse.jar...
charsets.jar...
localedata.jar...

and set JAVA_HOME to it before starting SQLcl

$ export JAVA_HOME=/usr/java/jre1.8.0_171-amd64
$ SQLPATH=~/sql bash $ORACLE_HOME/sqldeveloper/sqlcl/bin/sql /nolog

Anyway, in all cases, once the credential .zip is provided you can connect with Thin JDBC (the default) with a user/password that has been created in the ADWC:

SQL> connect admin@adwc_high
Password? (**********?) ****************
AArray = [B@24959ca4
AArray = [B@10289886
AArray = [B@32115b28
AArray = [B@2ad48653
Connected.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED CON_ID INSTANCE_MODE EDITION FAMILY DATABASE_TYPE
--------------- ------------- --------- ------- ------------ ------ -------- ------- -------- --------------- ------ ---------------- --------------- ------------- ------------ ------- ------ ------------- ------- ------ -------------
6 esj1pod6 12.2.0.1.0 13-MAY-18 OPEN YES 6 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE RAC

What’s in the .zip

The tnsnames.ora has entries for the low, medium, high services.

adwc_high = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )
 
adwc_low = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_low.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )
 
adwc_medium = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )

The sqlnet.ora mentions the wallet used for SQL*Net encryption:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

Note that the directory is an absolute path and you probably want to change it to your TNS_ADMIN one where you unzip the file.

In the wallet location, you find the ewallet.p12 that contain the certificate and private keys, protected with password, and the cwallet.sso which do not need to provide the password to open it, so protect them with file permissions.

You find also keystore.jks which also contains the Self-signed certificate but in JKS truststore format. and referenced from ojdbc.properties properties:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

Once you have unzipped the credentials, you can use them to connect with OCI.

SQL*Plus or SQLcl -oci

If you want to connect with an OCI client, you have to unzip this file to your TNS_ADMIN directory. That can be the $ORACLE_HOME/network/admin, but be careful to overwrite existing files, or it can be a new directory you will use by setting the TNS_ADMIN environment variable (or registry entry) to it.

Here are some examples where I set TNS_ADMIN to the directory where I unzipped the credentials:

TNS_ADMIN=/media/sf_share/ADWC/wallet_ADWC sqlcl -oci /nolog
TNS_ADMIN=/media/sf_share/ADWC/wallet_ADWC sqlplus /nolog

Any application using OCI (the oracle client, which can be the InstantClient or a full database installation) can use this without providing any password.

Databas Link

With the unzipped credentials you can access through OCI which means that you can also have a database link to the ADWC database. The credentials must be unzipped (or merged) in the TNS_ADMIN (or default ?/rnetwork/admin) of the instance:

SQL> create database link ADWC connect to ADMIN identified by "Ach1z0#dAch1z0#d" using 'adwc_high';
Database link created.
 
SQL> select banner from v$version@ADWC;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Client Credential password

When downloading the .zip you are asked for a password to ‘protect this file to prevent unauthorized database access‘. But that is misleading. The .zip is not password protected. Anyone can open it. And it contains an auto-login wallet, so anybody can use it. You can access the database without this password. Of course, you cannot connect if you don’t have a user/password with a create session privileges, but you access to it for user credentials verification.

So what is this password used for? We have seen that SQL Developer needs the password (or you will get a files as java.io.IOException: Keystore was tampered with, or password was incorrect). Then, you may remove the .sso auto-login wallet from the .zip file when it is used only by SQL Developer. But of course, you have to think about where the password is stored in SQL Developer. Is is more secured than the .sso ?

As long as the auto-login wallet is there, you do not need to store the wallet password. But of course, you will protect credential files.

 

Cet article ADWC – connect from your premises est apparu en premier sur Blog dbi services.

Deploying EDB containers in MiniShift/OpenShift

Fri, 2018-05-11 10:15

In this post we’ll look at how we can deploy EnterpriseDB containers in MiniShift. When you need to setup MiniShift have a look here. In this post we’ll do the setup with the MiniShift console, in a next post we’ll do the same by using the command line tools.

As a few containers will be running at the end MiniShift got more resources when it was started:

dwe@dwe:/opt$ minishift delete
dwe@dwe:/opt$ minishift start --cpus 4 --disk-size 30GB --memory 4GB

Once MiniShift is up and running open the MiniShift console and login as developer/admin:

dwe@dwe:/opt$ minishift console

Selection_001

The first thing we need to do is to grant the necessary permissions after we stepped into “My Project”:
Selection_002

The permission are in Resources->Membership. Add admin,edit and view to the default account:
Selection_004

For accessing the EnterpriseDB container repository a new secret needs to be created which contains the connection details. Secrets are under Resources->Secrets:
Selection_005
Selection_006

As databases are happy when they can store their data on persistent storage we need a volume. Volumes can be created under “Storage”:
Selection_007
Selection_008

Now we need a local registry where we can push the EnterpriseDB containers to:

dwe@dwe:~$ minishift ssh
                        ##         .
                  ## ## ##        ==
               ## ## ## ## ##    ===
           /"""""""""""""""""\___/ ===
      ~~~ {~~ ~~~~ ~~~ ~~~~ ~~~ ~ /  ===- ~~~
           \______ o           __/
             \    \         __/
              \____\_______/
 _                 _   ____     _            _
| |__   ___   ___ | |_|___ \ __| | ___   ___| | _____ _ __
| '_ \ / _ \ / _ \| __| __) / _` |/ _ \ / __| |/ / _ \ '__|
| |_) | (_) | (_) | |_ / __/ (_| | (_) | (__|   <  __/ |
|_.__/ \___/ \___/ \__|_____\__,_|\___/ \___|_|\_\___|_|
Boot2Docker version 1.12.6, build HEAD : 5ab2289 - Wed Jan 11 03:20:40 UTC 2017
Docker version 1.12.6, build 78d1802
docker@minishift:~$ docker run -d -p 5000:5000 --restart=always --name registry registry:2
Unable to find image 'registry:2' locally
2: Pulling from library/registry
81033e7c1d6a: Pull complete 
...
Status: Downloaded newer image for registry:2
14e85f4e2a36e727a0584803e49bbd690ffdb092c02238a241bd2ad003680625
docker@minishift:~$ docker login containers.enterprisedb.com
Username: dbi-services
Password: 
Login Succeeded
docker@minishift:~$ docker pull containers.enterprisedb.com/test/edb-as:v10.3
v10.3: Pulling from test/edb-as
d9aaf4d82f24: Pulling fs layer 
...
Status: Downloaded newer image for containers.enterprisedb.com/test/edb-as:v10.3
docker@minishift:~$ docker tag containers.enterprisedb.com/test/edb-as:v10.3 localhost:5000/test/edb-as:v10.3
docker@minishift:~$ docker push localhost:5000/test/edb-as:v10.3
The push refers to a repository [localhost:5000/test/edb-as]
274db5c4ff47: Preparing 
...
docker@minishift:~$ docker pull containers.enterprisedb.com/test/edb-pgpool:v3.5
v3.5: Pulling from test/edb-pgpool
...
docker@minishift:~$ docker tag containers.enterprisedb.com/test/edb-pgpool:v3.5 localhost:5000/test/edb-pgpool:v3.5
docker@minishift:~$ docker push localhost:5000/test/edb-pgpool:v3.5
The push refers to a repository [localhost:5000/test/edb-pgpool]
8a7df26eb139: Pushed 
...

This is all what is required for the preparation. The next step is to import to the template which specifies the setup. For this little demo we’ll use this one:

apiVersion: v1
kind: Template
metadata:
   name: edb-as10-0
   annotations:
    description: "Standard EDB Postgres Advanced Server 10.0 Deployment Config"
    tags: "database,epas,postgres,postgresql"
    iconClass: "icon-postgresql"
objects:
- apiVersion: v1 
  kind: Service
  metadata:
    name: ${DATABASE_NAME}-service 
    labels:
      role: loadbalancer
      cluster: ${DATABASE_NAME}
  spec:
    selector:                  
      lb: ${DATABASE_NAME}-pgpool
    ports:
    - name: lb 
      port: ${PGPORT}
      targetPort: 9999
    sessionAffinity: None
    type: LoadBalancer
- apiVersion: v1 
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-pgpool
  spec:
    replicas: 2
    selector:
      lb: ${DATABASE_NAME}-pgpool
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        labels:
          lb: ${DATABASE_NAME}-pgpool
          role: queryrouter
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-pgpool
          env:
          - name: DATABASE_NAME
            value: ${DATABASE_NAME} 
          - name: PGPORT
            value: ${PGPORT} 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/test/edb-pgpool:v3.5
          imagePullPolicy: IfNotPresent
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5
    triggers:
    - type: ConfigChange
- apiVersion: v1
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-as10-0
  spec:
    replicas: 1
    selector:
      db: ${DATABASE_NAME}-as10-0 
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        creationTimestamp: null
        labels:
          db: ${DATABASE_NAME}-as10-0 
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-as10 
          env:
          - name: DATABASE_NAME 
            value: ${DATABASE_NAME} 
          - name: DATABASE_USER 
            value: ${DATABASE_USER} 
          - name: DATABASE_USER_PASSWORD
            value: 'postgres' 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: PGPORT
            value: ${PGPORT} 
          - name: RESTORE_FILE
            value: ${RESTORE_FILE} 
          - name: LOCALEPARAMETER
            value: ${LOCALEPARAMETER}
          - name: CLEANUP_SCHEDULE
            value: ${CLEANUP_SCHEDULE}
          - name: EFM_EMAIL
            value: ${EFM_EMAIL}
          - name: NAMESERVER
            value: ${NAMESERVER}
          - name: POD_NAMESPACE
            valueFrom:
              fieldRef:
                fieldPath: metadata.namespace
          - name: POD_NODE
            valueFrom:
              fieldRef:
                fieldPath: spec.nodeName 
          - name: POD_IP
            valueFrom:
              fieldRef:
                fieldPath: status.podIP 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/test/edb-as:v10.3
          imagePullPolicy: IfNotPresent 
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5 
          livenessProbe:
            exec:
              command:
              - /var/lib/edb/testIsHealthy.sh
            initialDelaySeconds: 600 
            timeoutSeconds: 60 
          ports:
          - containerPort: ${PGPORT} 
          volumeMounts:
          - name: ${PERSISTENT_VOLUME}
            mountPath: /edbvolume
        dnsPolicy: ClusterFirst
        restartPolicy: Always
        volumes:
        - name: ${PERSISTENT_VOLUME}
          persistentVolumeClaim:
            claimName: ${PERSISTENT_VOLUME_CLAIM}
    triggers:
    - type: ConfigChange
parameters:
- name: DATABASE_NAME
  displayName: Database Name
  description: Name of Postgres database (leave edb for default)
  value: 'edb'
- name: DATABASE_USER
  displayName: Default database user (leave enterprisedb for default)
  description: Default database user
  value: 'enterprisedb'
- name: REPL_USER
  displayName: Repl user
  description: repl database user
  value: 'repl'
- name: PGPORT
  displayName: Database Port
  description: Database Port (leave 5444 for default)
  value: "5444"
- name: LOCALEPARAMETER
  displayName: Locale
  description: Locale of database
  value: ''
- name: CLEANUP_SCHEDULE
  displayName: Host Cleanup Schedule
  description: Standard cron schedule - min (0 - 59), hour (0 - 23), day of month (1 - 31), month (1 - 12), day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0). Leave it empty if you dont want to cleanup.
  value: '0:0:*:*:*'
- name: EFM_EMAIL
  displayName: Email
  description: Email for EFM
  value: 'none@none.com'
- name: NAMESERVER
  displayName: Name Server for Email
  description: Name Server for Email
  value: '8.8.8.8'
- name: PERSISTENT_VOLUME
  displayName: Persistent Volume
  description: Persistent volume name
  value: ''
  required: true
- name: PERSISTENT_VOLUME_CLAIM 
  displayName: Persistent Volume Claim
  description: Persistent volume claim name
  value: ''
  required: true
- name: RESTORE_FILE
  displayName: Restore File
  description: Restore file location
  value: ''
- name: ACCEPT_EULA
  displayName: Accept end-user license agreement (leave 'Yes' for default)
  description: Indicates whether user accepts the end-user license agreement
  value: 'Yes'
  required: true

For importing that into OpenShift go to “Overview” and select “Import YAML/JSON”:
Selection_010
Selection_011
Selection_012

This imports the template but does not process it right now. When you go back to “Overview” you should see a new template which you can provision:
Selection_013
Selection_014

Selecting the new template brings you to the specification of the variables. The only bits you need to adjust are the values for the volume and the volume claim:
Selection_015
Selection_016

A few moments later the EDB containers are up and running:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-fdr5j   1/1       Running   0          1m
edb-pgpool-1-9twmc   1/1       Running   0          1m
edb-pgpool-1-m5x44   1/1       Running   0          1m

Current there are two pgpool instances and one database instance container. You can double check that the instance is really running with:

dwe@dwe:~$ oc rsh edb-as10-0-1-fdr5j
sh-4.2$ psql postgres
psql.bin (10.3.8)
Type "help" for help.

postgres=# select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 EnterpriseDB 10.3.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

Going back to the “Overview” page in the console shows the same information:
Selection_019

In the next post we’ll scale up the deployment by adding two replicas and configure access from outside the cluster.

 

Cet article Deploying EDB containers in MiniShift/OpenShift est apparu en premier sur Blog dbi services.

Pages