Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 10 min 12 sec ago

Dealing with automatic restart and SQL Docker containers

Wed, 2018-06-20 12:57

A couple of weeks ago, a customer asked me how to restart containers automatically after a reboot of the underlying host. In his context, it was not an insignificant question because some containers are concerned by SQL Server databases and he wanted to stay relaxed as long as possible even after a maintenance of the Linux host by sysadmins. The concerned (DEV) environment doesn’t include container orchestration like Swarm or Kubernetes.

blog 139 - 0 - banner

The interesting point is there are several ways to perform the job according to the context. Let’s say I was concerned by services outside Docker that are depend of the containerized database environment.

The first method is a purely sysadmin solution that includes systemd which is a Linux process manager that can be used to automatically restart services that fail with restarting policy values as no, on-success, on-failure, on-abnormal, on-watchdog, on-abort, or always. The latter fits well with my customer scenario.

Is there advantage to use this approach? Well, in my customer context some services outside docker are dependent of the SQL container and using systemd is a good way to control dependencies.

Below the service unit file used during my mission and I have to give credit to the SQL Server Customer Advisory team who published an example of this file included in their monitoring solution based on InfluxDB, Grafana and collectd. The template file includes unit specifiers that make it generic. I just had to change the name of the system unit file accordingly to which container I wanted to control.

[Unit]
Description=Docker Container %I
Requires=docker.service
After=docker.service

[Service]
TimeoutStartSec=0
Restart=always
ExecStart=/usr/bin/docker start -a %i
ExecStop=/usr/bin/docker stop -t 2 %i

[Install]
WantedBy=default.target

 

Let’s say I have one SQL Server container named sql. The next step will consist in copying the service template to /etc/systemd/system and changing the service name accordingly to the SQL container name. Thus, we may now benefit from the systemctl command capabilities

$ sudo cp ./service-template /etc/systemd/system/docker-container@sql.service
$ systemctl daemon-reload
$ sudo systemctl enable docker-container@sql

 

That’s it. I may get the status of my new service as following

$ sudo systemctl status docker-container@sql

 

blog 139 - 1 - systemctl status docker container

 

I can also stop and start my SQL docker container like this:

[clustadmin@docker3 ~]$ sudo systemctl stop docker-container@sql
[clustadmin@docker3 ~]$ docker ps -a
CONTAINER ID        IMAGE                                   COMMAND                  CREATED             STATUS                     PORTS               NAMES
9a8cad6f21f5        microsoft/mssql-server-linux:2017-CU7   "/opt/mssql/bin/sqls…"   About an hour ago   Exited (0) 7 seconds ago                       sql

[clustadmin@docker3 ~]$ sudo systemctl start docker-container@sql
[clustadmin@docker3 ~]$ docker ps
CONTAINER ID        IMAGE                                   COMMAND                  CREATED             STATUS              PORTS                    NAMES
9a8cad6f21f5        microsoft/mssql-server-linux:2017-CU7   "/opt/mssql/bin/sqls…"   About an hour ago   Up 5 seconds        0.0.0.0:1433->1433/tcp   sql

 

This method met my customer requirement but I found one drawback in a specific case when I stop my container from systemctl command and then I restart it by using docker start command. Thus the status is not reported correctly (Active = dead) and I have to run systemctl restart command against my container to go back to normal. I will probably update this post or to write another one after getting some information on this topic or just feel free to comments: I’m willing to hear about you!

 

The second method I also proposed to my customer for other SQL containers without any external dependencies was to rely on the Docker container restart policy capability. This is a powerful feature and very simple to implement with either docker run command or Dockerfile as follows:

docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=P@$$w0rd1' -p 1433:1433 --restart=unless-stopped -d microsoft/mssql-server-linux:2017-CU7

 

Restart-policy values as Always and unless-stopped fit well with my customer scenario even if I prefer the latter option because it provides another level of control if you manually decide to stop the container for any reasons.

I will voluntary omit the third method that consist in installing systemd directly into the container because it is not recommended by Docker itself and not suitable with my customer case as well.

See you!

 

 

 

Cet article Dealing with automatic restart and SQL Docker containers est apparu en premier sur Blog dbi services.

Migrating from ASMLIB to ASMFD

Wed, 2018-06-20 12:33

Before Oracle 12.1 the methods used to configure ASM were
• udev
• asmlib
Oracle 12.1 comes with a new method called Oracle ASM Filter Driver (Oracle ASMFD).
In Oracle documentation we can find following:
Oracle ASM Filter Driver (Oracle ASMFD) is a kernel module that resides in the I/O path of the Oracle ASM disks. Oracle ASM uses the filter driver to validate write I/O requests to Oracle ASM disks.
The Oracle ASMFD simplifies the configuration and management of disk devices by eliminating the need to rebind disk devices used with Oracle ASM each time the system is restarted.
The Oracle ASM Filter Driver rejects any I/O requests that are invalid. This action eliminates accidental overwrites of Oracle ASM disks that would cause corruption in the disks and files within the disk group. For example, the Oracle ASM Filter Driver filters out all non-Oracle I/Os which could cause accidental overwrites.

In the following blog I am going to migrate from asmlib to asmfd. I am using a cluster 12.1 with 2 nodes.

Below we present our actual configuration.

[root@rac12a ~]# crsctl check cluster -all
**************************************************************
rac12a:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac12b:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
[root@rac12a ~]#


[root@rac12a ~]# crsctl get cluster mode status
Cluster is running in "flex" mode
[root@rac12a ~]#

[root@rac12a ~]# ps -ef | grep pmon
grid      7217     1  0 11:20 ?        00:00:00 asm_pmon_+ASM1
grid      8070     1  0 11:21 ?        00:00:00 apx_pmon_+APX1
oracle    8721     1  0 11:22 ?        00:00:00 ora_pmon_mydb_1
root     14395  2404  0 11:32 pts/0    00:00:00 grep --color=auto pmon
[root@rac12a ~]#

First let’s get information about our ASM disks. We will use these outputs later to migrate the disks to ASMFD disks

[root@rac12a ~]# oracleasm listdisks | xargs oracleasm querydisk -p             
Disk "ASM_DATA" is a valid ASM disk
/dev/sdc1: LABEL="ASM_DATA" TYPE="oracleasm"
Disk "ASM_DIVERS" is a valid ASM disk
/dev/sdd1: LABEL="ASM_DIVERS" TYPE="oracleasm"
Disk "ASM_OCR1" is a valid ASM disk
/dev/sdg1: LABEL="ASM_OCR1" TYPE="oracleasm"
Disk "ASM_OCR2" is a valid ASM disk
/dev/sdi1: LABEL="ASM_OCR2" TYPE="oracleasm"
Disk "ASM_VOT1" is a valid ASM disk
/dev/sde1: LABEL="ASM_VOT1" TYPE="oracleasm"
Disk "ASM_VOT2" is a valid ASM disk
/dev/sdh1: LABEL="ASM_VOT2" TYPE="oracleasm"
Disk "ASM_VOT3" is a valid ASM disk
/dev/sdf1: LABEL="ASM_VOT3" TYPE="oracleasm"
[root@rac12a ~]#

To migrate to ASMFD, we first have to change the value of the parameter diskstring for the ASM instance. The actual value can be get by using

[grid@rac12a trace]$ asmcmd dsget
parameter:ORCL:*
profile:ORCL:*
[grid@rac12a trace]$

Let’s set the new value on both nodes

grid@rac12a trace]$ asmcmd dsset 'ORCL:*','AFD:*'

We can then verify

[grid@rac12a trace]$ asmcmd dsget
parameter:ORCL:*, AFD:*
profile:ORCL:*,AFD:*
[grid@rac12a trace]$

Once the new value of the diskstring set, let stop the cluster on both nodes

[root@rac12a ~]# crsctl stop cluster
[root@rac12b ~]# crsctl stop cluster

Once the cluster is stopped we have to disable and stop asmlib on both nodes

[root@rac12a ~]# systemctl disable oracleasm
Removed symlink /etc/systemd/system/multi-user.target.wants/oracleasm.service.

[root@rac12a ~]# oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes

[root@rac12a ~]# oracleasm exit
Unmounting ASMlib driver filesystem: /dev/oracleasm
Unloading module "oracleasm": oracleasm
[root@rac12a ~]#

[root@rac12a ~]# ls -ltr /dev/oracleasm/
total 0
[root@rac12a ~]#

Now let’s remove all packages relative to ASMLIB on both nodes

[root@rac12a oracle]# rpm -e oracleasm-support-2.1.11-2.el7.x86_64 oracleasmlib-2.0.12-1.el7.x86_64
warning: /etc/sysconfig/oracleasm saved as /etc/sysconfig/oracleasm.rpmsave
[root@rac12a oracle]#

The next step is to stop acfsload on both nodes

[root@rac12a ~]# lsmod | grep acfs
oracleacfs           3343483  0
oracleoks             500109  2 oracleacfs,oracleadvm
[root@rac12a ~]#

[root@rac12a ~]# acfsload stop
[root@rac12a ~]# lsmod | grep acfs
[root@rac12a ~]#

As root, we can now configure Oracle ASMFD to filter at the node level. In my case steps were done on both nodes

[root@rac12a oracle]# asmcmd afd_configure
Connected to an idle instance.
AFD-627: AFD distribution files found.
AFD-636: Installing requested AFD software.
AFD-637: Loading installed AFD drivers.
AFD-9321: Creating udev for AFD.
AFD-9323: Creating module dependencies - this may take some time.
AFD-9154: Loading 'oracleafd.ko' driver.
AFD-649: Verifying AFD devices.
AFD-9156: Detecting control device '/dev/oracleafd/admin'.
AFD-638: AFD installation correctness verified.
[root@rac12a oracle]#

Once the configuration done, we can check AFD state on all nodes

[root@rac12a oracle]# asmcmd afd_state
Connected to an idle instance.
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'DISABLED' on host 'rac12a.localdomain'
[root@rac12a oracle]#

We can see that afd module is loaded but the filtering is disabled. We then have to edit the oracleafd.conf to enable the filtering

[root@rac12a etc]# cat oracleafd.conf
afd_diskstring='/dev/sd*1'

And then we have to run on both nodes

[root@rac12a etc]# asmcmd afd_filter -e
Connected to an idle instance.
[root@rac12a etc]#

[root@rac12b ~]#  asmcmd afd_filter -e
Connected to an idle instance.
[root@rac12b ~]#

Running again the afd_state command, we can confirm that the filtering is now enabled.

[root@rac12a etc]# asmcmd afd_state
Connected to an idle instance.
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'rac12a.localdomain'
[root@rac12a etc]#

Now we can migrate all asm disks.

[root@rac12a etc]# asmcmd afd_label ASM_DATA /dev/sdc1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_DIVERS /dev/sdd1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_OCR1 /dev/sdg1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_OCR2 /dev/sdi1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_VOT1 /dev/sde1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_VOT2 /dev/sdh1 --migrate
Connected to an idle instance.
[root@rac12a etc]# asmcmd afd_label ASM_VOT3 /dev/sdf1 --migrate
Connected to an idle instance.
[root@rac12a etc]#

We can verify the ASMFD disks using the command

[root@rac12b ~]# asmcmd afd_lsdsk
Connected to an idle instance.
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
ASM_DATA                    ENABLED   /dev/sdc1
ASM_DIVERS                  ENABLED   /dev/sdd1
ASM_OCR1                    ENABLED   /dev/sdg1
ASM_OCR2                    ENABLED   /dev/sdi1
ASM_VOT1                    ENABLED   /dev/sde1
ASM_VOT2                    ENABLED   /dev/sdh1
ASM_VOT3                    ENABLED   /dev/sdf1
[root@rac12b ~]#

Let’s update the afd.conf so that ASMFD can mount ASMFD disks.

[root@rac12a etc]# cat afd.conf
afd_diskstring='/dev/sd*'
afd_filtering=enable

When the ASMFD disks are visible on both nodes, we can start acsfload on both nodes

[root@rac12a etc]# acfsload start
ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9392: Validating ADVM/ACFS installation files for operating system.
ACFS-9393: Verifying ASM Administrator setup.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9154: Loading 'oracleoks.ko' driver.
ACFS-9154: Loading 'oracleadvm.ko' driver.
ACFS-9154: Loading 'oracleacfs.ko' driver.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9322: completed
[root@rac12a etc]#

Now the conversion is done and we can start crs on both nodes

[root@rac12a ~]# crsctl start crs

[root@rac12b ~]# crsctl start crs

We can remove all asmlib references in the parameter diskstring

[grid@rac12a trace]$ asmcmd dsget
parameter:ORCL:*, AFD:*
profile:ORCL:*,AFD:*

[grid@rac12a trace]$ asmcmd dsset 'AFD:*'

[grid@rac12a trace]$ asmcmd dsget
parameter:AFD:*
profile:AFD:*
[grid@rac12a trace]$

Once the cluster started, we can verify the disk names

[grid@rac12a trace]$ asmcmd lsdsk
Path
AFD:ASM_DATA
AFD:ASM_DIVERS
AFD:ASM_OCR1
AFD:ASM_OCR2
AFD:ASM_VOT1
AFD:ASM_VOT2
AFD:ASM_VOT3
[grid@rac12a trace]$

We can also use following command to confirm that now ASMFD is being used

set linesize 300
col PATH for a20
set pages 20
col LIBRARY for a45
col NAME for a15
select inst_id,group_number grp_num,name,state,header_status header,mount_status mount,path, library
from gv$asm_disk order by inst_id,group_number,name;


   INST_ID    GRP_NUM NAME            STATE    HEADER       MOUNT   PATH                 LIBRARY
---------- ---------- --------------- -------- ------------ ------- -------------------- ---------------------------------------------
         1          1 ASM_DIVERS      NORMAL   MEMBER       CACHED  AFD:ASM_DIVERS       AFD Library - Generic , version 3 (KABI_V3)
         1          2 ASM_OCR1        NORMAL   MEMBER       CACHED  AFD:ASM_OCR1         AFD Library - Generic , version 3 (KABI_V3)
         1          2 ASM_OCR2        NORMAL   MEMBER       CACHED  AFD:ASM_OCR2         AFD Library - Generic , version 3 (KABI_V3)
         1          3 ASM_DATA        NORMAL   MEMBER       CACHED  AFD:ASM_DATA         AFD Library - Generic , version 3 (KABI_V3)
         1          4 ASM_VOT1        NORMAL   MEMBER       CACHED  AFD:ASM_VOT1         AFD Library - Generic , version 3 (KABI_V3)
         1          4 ASM_VOT2        NORMAL   MEMBER       CACHED  AFD:ASM_VOT2         AFD Library - Generic , version 3 (KABI_V3)
         1          4 ASM_VOT3        NORMAL   MEMBER       CACHED  AFD:ASM_VOT3         AFD Library - Generic , version 3 (KABI_V3)
         2          1 ASM_DIVERS      NORMAL   MEMBER       CACHED  AFD:ASM_DIVERS       AFD Library - Generic , version 3 (KABI_V3)
         2          2 ASM_OCR1        NORMAL   MEMBER       CACHED  AFD:ASM_OCR1         AFD Library - Generic , version 3 (KABI_V3)
         2          2 ASM_OCR2        NORMAL   MEMBER       CACHED  AFD:ASM_OCR2         AFD Library - Generic , version 3 (KABI_V3)
         2          3 ASM_DATA        NORMAL   MEMBER       CACHED  AFD:ASM_DATA         AFD Library - Generic , version 3 (KABI_V3)
         2          4 ASM_VOT1        NORMAL   MEMBER       CACHED  AFD:ASM_VOT1         AFD Library - Generic , version 3 (KABI_V3)
         2          4 ASM_VOT2        NORMAL   MEMBER       CACHED  AFD:ASM_VOT2         AFD Library - Generic , version 3 (KABI_V3)
         2          4 ASM_VOT3        NORMAL   MEMBER       CACHED  AFD:ASM_VOT3         AFD Library - Generic , version 3 (KABI_V3)

14 rows selected.
 

Cet article Migrating from ASMLIB to ASMFD est apparu en premier sur Blog dbi services.

Remote syslog from Linux and Solaris

Wed, 2018-06-20 10:47

Auditing operations with Oracle Database is very easy. The default configuration, where SYSDBA operations go to ‘audit_file_dest’ (the ‘adump’ directory) and other operations go to the database may be sufficient to log what is done but is definitely not a correct security audit method as both destinations can have their audit trail deleted by the DBA. If you want to secure your environment by auditing the most privileged accounts, you need to send the audit trail to another server.

This is easy as well and here is a short demo involving Linux and Solaris as the audited environments. I’ve created those 3 computer services in the Oracle Cloud:
CaptureSyslog000

So, I have an Ubuntu service where I’ll run the Oracle Database (XE 11g) and the hostname is ‘ubuntu’

root@ubuntu:~# grep PRETTY /etc/os-release
PRETTY_NAME="Ubuntu 16.04.4 LTS"

I have a Solaris service which will also run Oracle, and the hostname is ‘d17872′

root@d17872:~# cat /etc/release
Oracle Solaris 11.3 X86
Copyright (c) 1983, 2016, Oracle and/or its affiliates. All rights reserved.
Assembled 03 August 2016

I have an Oracle Enterprise Linux service which will be my audit server, collecting syslog messages from remote hosts, the hostname is ‘b5e501′ and the IP address in the PaaS network is 10.29.235.150

[root@b5e501 ~]# grep PRETTY /etc/os-release
PRETTY_NAME="Oracle Linux Server 7.5"

Testing local syslog

I start to ensure that syslog works correctly on my audit server:

[root@b5e501 ~]# jobs
[1]+ Running tail -f /var/log/messages &
[root@b5e501 ~]#
[root@b5e501 ~]# logger -p local1.info "hello from $HOSTNAME"
[root@b5e501 ~]# Jun 20 08:28:35 b5e501 bitnami: hello from b5e501

Remote setting

On the aduit server, I un-comment the lines about receiving syslog from TCP and UDP on port 514

[root@b5e501 ~]# grep -iE "TCP|UDP" /etc/rsyslog.conf
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514
# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514
# Remote Logging (we use TCP for reliable delivery)

I restart syslog service

[root@b5e501 ~]# systemctl restart rsyslog
Jun 20 08:36:47 b5e501 systemd: Stopping System Logging Service...
Jun 20 08:36:47 b5e501 rsyslogd: [origin software="rsyslogd" swVersion="8.24.0" x-pid="2769" x-info="http://www.rsyslog.com"] exiting on signal 15.
Jun 20 08:36:47 b5e501 systemd: Starting System Logging Service...
Jun 20 08:36:47 b5e501 rsyslogd: [origin software="rsyslogd" swVersion="8.24.0" x-pid="2786" x-info="http://www.rsyslog.com"] start
Jun 20 08:36:47 b5e501 systemd: Started System Logging Service.

I tail the /var/log/messages (which is my default destination for “*.info;mail.none;authpriv.none;cron.none”)

[root@b5e501 ~]# tail -f /var/log/messages &
[root@b5e501 ~]# jobs
[1]+ Running tail -f /var/log/messages &

I test with local1.info and check that the message is tailed even when logger is sending it though the network:

[root@b5e501 ~]# logger -n localhost -P 514 -p local1.info "hello from $HOSTNAME"
Jun 20 09:18:07 localhost bitnami: hello from b5e501

That’s perfect.

Now I can test the same from my Ubuntu host to ensure that the firewall settings allow for TCP and UDP on port 514


root@ubuntu:/tmp/Disk1# logger --udp -n 10.29.235.150 -P 514 -p local1.info "hello from $HOSTNAME in UDP"
root@ubuntu:/tmp/Disk1# logger --tcp -n 10.29.235.150 -P 514 -p local1.info "hello from $HOSTNAME in TCP"

Here are the correct messages received:

Jun 20 09:24:46 ubuntu bitnami hello from ubuntu in UDP
Jun 20 09:24:54 ubuntu bitnami hello from ubuntu in TCP

Destination setting for the audit

As I don’t want to have all messages into /var/log/messages, I’m now setting, in the audit server, a dedicated file for “local1″ facility and “info” level that I’ll use for my Oracle Database audit destination

[root@b5e501 ~]# touch "/var/log/audit.log"
[root@b5e501 ~]# echo "local1.info /var/log/audit.log" >> /etc/rsyslog.conf
[root@b5e501 ~]# systemctl restart rsyslog

After testing the same two ‘logger’ commands from the remote host I check the entries in my new file:

[root@b5e501 ~]# cat /var/log/audit.log
Jun 20 09:55:09 ubuntu bitnami hello from ubuntu in UDP
Jun 20 09:55:16 ubuntu bitnami hello from ubuntu in TCP

Remote logging

Now that I validated that remote syslog is working, I set automatic forwarding of syslog messages on my Ubuntu box to send all ‘local1.info to the audit server':

root@ubuntu:/tmp/Disk1# echo "local1.info @10.29.235.150:514" >> /etc/rsyslog.conf
root@ubuntu:/tmp/Disk1# systemctl restart rsyslog

This, with a single ‘@’ forwards in UDP. You can double the ‘@’ to forward using TCP.

Here I check with logger in local (no mention of the syslog host here):

root@ubuntu:/tmp/Disk1# logger -p local1.info "hello from $HOSTNAME with forwarding"

and I verify that the message is logged in the audit server into /var/log/audit.log

[root@b5e501 ~]# tail -1 /var/log/audit.log
Jun 20 12:00:25 ubuntu bitnami: hello from ubuntu with forwarding

Repeated messages

Note that when testing, you may add “$(date)” to your message in order to see it immediately because syslog keeps the message to avoid flooding when the message is repeated. This:

root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Always the same message"
root@ubuntu:/tmp/Disk1# logger -p local1.info "Then another one"

is logged as this:

Jun 20 12:43:12 ubuntu bitnami: message repeated 5 times: [ Always the same message] Jun 20 12:43:29 ubuntu bitnami: Then another one

I hope that one day this idea will be implemented by Oracle when flooding messages to the alert.log ;)

Oracle Instance

The last step is to get my Oracle instance sending audit message to the local syslog, with facility.level local1.info so that they will be automatically forwarded to my audit server. I have to set audit_syslog_level to ‘local1.info’ and the audit_trail to ‘OS':

oracle@ubuntu:~$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 20 11:48:00 2018
 
Copyright (c) 1982, 2011, Oracle. All rights reserved.
 
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
SQL> alter system set audit_syslog_level='local1.info' scope=spfile;
 
System altered.
 
SQL> alter system set audit_trail='OS' scope=spfile;
 
System altered.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 1068937216 bytes
Fixed Size 2233344 bytes
Variable Size 616565760 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes
Database mounted.
Database opened.

It is very easy to check that it works as the SYSDBA and the STARTUP are automatically audited. Here is what I can see in my audit server /var/log/audit.log:

[root@b5e501 ~]# tail -f /var/log/audit.log
Jun 20 11:55:47 ubuntu Oracle Audit[27066]: LENGTH : '155' ACTION :[7] 'STARTUP' DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[13] 'Not Available' STATUS:[1] '0' DBID:[0] ''
Jun 20 11:55:47 ubuntu Oracle Audit[27239]: LENGTH : '148' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[0] ''
Jun 20 11:55:51 ubuntu Oracle Audit[27419]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/0' STATUS:[1] '0' DBID:[10] '2860420539'

In the database server, I have no more files in the adump since this startup:

oracle@ubuntu:~/admin/XE/adump$ /bin/ls -alrt
total 84
drwxr-x--- 6 oracle dba 4096 Jun 20 11:42 ..
-rw-r----- 1 oracle dba 699 Jun 20 11:44 xe_ora_26487_1.aud
-rw-r----- 1 oracle dba 694 Jun 20 11:44 xe_ora_26515_1.aud
-rw-r----- 1 oracle dba 694 Jun 20 11:44 xe_ora_26519_1.aud
-rw-r----- 1 oracle dba 694 Jun 20 11:44 xe_ora_26523_1.aud
drwxr-x--- 2 oracle dba 4096 Jun 20 11:48 .
-rw-r----- 1 oracle dba 896 Jun 20 11:48 xe_ora_26574_1.aud

Solaris

I have also started a Solaris service:

opc@d17872:~$ pfexec su -
Password: solaris_opc
su: Password for user 'root' has expired
New Password: Cl0udP01nts
Re-enter new Password: Cl0udP01nts
su: password successfully changed for root
Oracle Corporation SunOS 5.11 11.3 June 2017
You have new mail.
root@d17872:~#

Here, I add the forwarding to /etc/syslog.conf (tab is a required separator which cannot be replaced with spaces) and restart the syslog service:

root@d17872:~# echo "local1.info\t@10.29.235.150" >> /etc/syslog.conf
root@d17872:~# svcadm restart system-log

Then logging a message locally

root@d17872:~# logger -p local1.info "hello from $HOSTNAME with forwarding"

Here is the messaged received from the audit server:

[root@b5e501 ~]# tail -f /var/log/audit.log
Jun 20 05:27:51 d17872.compute-a511644.oraclecloud.internal opc: [ID 702911 local1.info] hello from d17872 with forwarding

Here in Solaris I have the old ‘syslog’ with no syntax to change the UDP port. The default port is defined in /etc/services, which is the one I’ve configured to listen to on my audit server:

root@d17872:~# grep 514 /etc/services
shell 514/tcp cmd # no passwords used
syslog 514/udp

If you want more features, you can install syslog-ng or rsyslog on Solaris.

 

Cet article Remote syslog from Linux and Solaris est apparu en premier sur Blog dbi services.

How to fix your own SQL plan in Oracle ?

Mon, 2018-06-18 10:23

There is time when you have build an SQL plan and then you want to fix it for all next executions of the query by your application.

In this post I show how fix a plan you have created by yourself.

First we need to identified the query

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 4159986352

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     3 |    57 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS   |     3 |    57 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ITEM_ORDER_IX |     3 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

In that example I’ll take an application query against the ORDER_ITEMS table. I find hat query too fast because it is using the index ITEM_ORDER_IX which is based on the primary key. So we are going to force that query to be executed by accessing the whole table without using any index. Here I’ll use the hint FULL to do the job.

SQL> select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ;

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

On both queries I added a comment to make it easier to retrieve information in the SQL views from Oracle. Now I can get the statistic about my queries:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

Plan control

So my goal is to force the application query “8ms87fhrq01xh” to use the plan from my manual modified query “55x955b31npwq”. To do so, I’m going to use the  “SQL Plan Management” from Oracle which is embedded from the release 11 and can be used with the DBMS_SPM package.

First I need to load the plan from my application query into SPM baseline:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache( sql_id => '8ms87fhrq01xh' ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

I have now a new cursor in the SQL view with the SQL_PLAN_BASELINE identifier:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh        4159986352                                        25         5199     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
8ms87fhrq01xh        4159986352 SQL_PLAN_gt4cxn0aacz0j91520601          1        21703     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

I can now find the SPM content for my SQL:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO

 

What we need to do now is to inject into the SPM baseline the plan from my modified query. To do so, I need the SQL_HANDLE of my application query and the couple of SQL_ID+PLAN_HASH_VALUE of the modified query to inject its plan into the plan baseline of my application query:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => '55x955b31npwq',
    plan_hash_value => 456270211,
    sql_handle => 'SQL_fc919da014a67c11'
  ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

 

Now, let’s seen what’s in the baseline of our application query:

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   NO

 

A new plan called “SQL_PLAN_gt4cxn0aacz0jf91228bb” has been generated and I know want to be sure it is the only one that are goin gto be used. Thus we need to fix it:

SQL> set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.alter_sql_plan_baseline(
    sql_handle => 'SQL_fc919da014a67c11',
plan_name => 'SQL_PLAN_gt4cxn0aacz0jf91228bb',
    attribute_name => 'fixed',
    attribute_value => 'YES'
  ) ;
  dbms_output.put_line( 'plans modified: '||plans_loaded ) ;
end ;
/
plans modified: 1

PL/SQL procedure successfully completed.

SQL> @spm
Enter value for signature: 18199500880047668241
old   9: where signature = '&signature.'
new   9: where signature = '18199500880047668241'

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0j91520601 YES   YES   NO
 18199500880047668241 SQL_fc919da014a67c11           SQL_PLAN_gt4cxn0aacz0jf91228bb YES   YES   YES

 

Then, my Full access plan is fixed and I can check if the index on the primary key is still in used:

SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098

LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY
------------ ---------- ---------- ----------
           1        414        851          5
           2        499        818          3

Execution Plan
----------------------------------------------------------
Plan hash value: 456270211

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    57 |  2022   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDER_ITEMS |     3 |    57 |  2022   (1)| 00:00:01 |
---------------------------------------------------------------------------------

 

I can monitor the plan usage from the SQL view to check if the application has is executing the query with the new plan:

SQL> @sql

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
8ms87fhrq01xh         456270211 SQL_PLAN_gt4cxn0aacz0jf91228bb          6       145687     18199500880047668241 select /* INDEX_ACCESS */ line_item_id, product_id
55x955b31npwq         456270211                                         3       155947     10822814485518112755 select /* FULL_ACCESS */ /*+ full(ORDER_ITEMS) */

 

As I see that both queries are currently using the same plan, I know that my application is now using the new plan with the full access to the ORDER_ITEMS table.

I hope this demonstration may help and please do not hesitate to contact us if you have any further questions or observations.

Scripts used in this article:

-- script sql.sql
set lines 180 pages 500
col sql_id format a14
col sql_plan_baseline format a30
col plan_hash_value format 999999999999999
col exact_matching_signature format 99999999999999999999
col sql_text format a50
select sql_id,
plan_hash_value,
sql_plan_baseline,
executions,
elapsed_time,
exact_matching_signature,
substr(sql_text,0,50) sql_text
from v$sql
where parsing_schema_name != 'SYS'
and sql_text like '%_ACCESS%' ;
-- script spm.sql
set lines 200
set pages 500
col signature format 99999999999999999999
col sql_handle format a30
col plan_name format a30
col enabled format a5
col accepted format a5
col fixed format a5
select
signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where signature = '&signature.'
 

Cet article How to fix your own SQL plan in Oracle ? est apparu en premier sur Blog dbi services.

ADWC new OCI interface

Sun, 2018-06-17 14:51

A few things have changed about the Autonomous Data Warehouse Cloud service recently. And I’ve found the communication not so clear, so here is a short post about what I had to do to start the service again. The service has always been on the OCI data centers but was managed with the classic management interface. It has been recently migrated to the new interface:
CaptureADWCnew
Note that ADWC here is the name I’ve given for my service. It seems that the Autonomous Data Warehouse Cloud Service is now referred by the ADW acronym.

The service itself did not have any outage. The migration concerns only the interface. However, once the migration done, you cannot use the old interface. I went to the old interface with the URL I bookmarked, tried to start the service, and got a ‘last activity START_SERVICE failed’ error message without additional detail.
CaptureADWCfail

You can forget the old bookmark (such as https://psm-tenant.console.oraclecloud.com/psmui/faces/paasRunner.jspx?serviceType=ADWC) and you now have to use the new one (such as https://console.us-ashburn-1.oraclecloud.com/a/db/adws/ocid1.autonomousdwdatabase.oc1.iad.al-long-IAD-identifier)

So I logged to the console https://console.us-ashburn-1.oraclecloud.com (My service is in Ashburn-1 region). There I provided the tenant name (was the cloud account in the old interface) which can also be provided in the URL as https://console.us-ashburn-1.oraclecloud.com/?tenant=tenant. I selected oracleidentitycloudservice as the ‘identity provider’, my username and password and I am on the OCI console.

From the top-left menu, I can go to Autonomous Data Warehouse. I see nothing until I choose the compartement in the ‘list scope’. The ADWC service I had created when in the old interface is in the ‘tenant (root)’ compartment. Here I can start the service.

The previous PSM command line interface cannot be used anymore. We need to install the OCI CLI:

$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

You will need the Tenancy ID (Tenancy OCID:ocid1.tenancy.oc1..aaaaaaaa… that you find on the bottom of each page in the console), the User ID (User OCID ocid1.user.oc1..aaaaaaa… that you find in the ‘users’ menu). All those ‘OCID’ are documented in https://docs.us-phoenix-1.oraclecloud.com/Content/API/Concepts/apisigningkey.htm

If you used the REST API, they change completely. You will have to post to something like:

/20160918/autonomousDataWarehouses/ocid1.autonomousdwdatabase.oc1.iad.abuwcljrb.../actions/start

where the OCID is the database one that cou can copy from the console.

 

Cet article ADWC new OCI interface est apparu en premier sur Blog dbi services.

Convert a WebLogic Cluster from configured to dynamic

Fri, 2018-06-15 00:14

Unless the servers in cluster are not symmetric, which is not recommended anyway, dynamic cluster have many advantages against configured cluster:

  1. Ensure Cluster Member Uniformity
  2. Easily add new servers to manage more traffic
  3. Automatically adapt to load to add/remove managed servers
  4. Can still contain configured servers even if not recommended as for point 1
Server template

A server template defines a set of attributes. A change in a template will be propagated to all server depending on it. A dynamic cluster can be based on a server template.

Here is an overview of the configured to dynamic change:

  1. Write down all customized parameters of server’s member of the cluster.
  2. Create new dynamic cluster
  3. Report all settings. There are two specificities on dynamic cluster:
    1. Listen port and SSL port which can be either:
      1. Static, meaning all servers of the cluster will have same port. This is the best option when you have one server to one machine mapping
      2. Calculated, meaning each server will listen on a different port by step of 1. For example, if first port is set to 7000, server 1 will listen on 7001, server 2 on 7002, …
    2. Machine binding: Use of a specific (filtered) subset of machines from the cluster
  4. Create new server template
Procedure
  1. In left tree, go in Environment, Clusters, Server Templates
  2. Lock & Edit the configuration and click New
    1. Name the template:
      5 - Server Template Name
    2. In the server template list, select the newly created template to customize the parameters.
    3. In General tab, select the Cluster that needs to be converted:
      6 - Server Template Name
    4. Save
  3. In Environment, Clusters, Servers tab, set Server Template name:
    Cluster settings - servers tab
  4. Save and Activate the changes
  5. In Environment, Servers folder, dynamically created servers will be displayed (4 in your example):
    7 - Servers List
    Note that machine are distributed across available machines in round robin and also the Listen port and SSL are incrementing.
  6. Then, start these new servers, test application is running correctly.
  7. Finally, stop configured managed servers by selecting “Work when completes”:
    8 - stop when complete

Cluster is now dynamic and you can easily add or remove managed servers from cluster.

What Next ?

This was a quick overview of how to convert configured to dynamic cluster. As we saw, it still require manual intervention to add or remove servers from cluster.

Coming with 12.2.1, WebLogic introduce a new feature called “elasticity”. This feature allows to automatically scales the amount of managed servers in the cluster based on user defined policies.

Thanks to WebLogic Diagnostic Framework (WLDF) Policies, it is possible to monitor memory, CPU usage, threads and then trigger a scale up or down action.

 

Cet article Convert a WebLogic Cluster from configured to dynamic est apparu en premier sur Blog dbi services.

Is adding a column to a typed table in PostgreSQL instant?

Thu, 2018-06-14 06:26

Today at the SOUG Day I did some little demos and one of them was about creating typed tables. In the demo the two tables did not contain any rows and one of the questions was: When these tables contain a million of rows would adding a column be instant as well? Lets do a quick test.

Same setup as in the post referenced above: Two schemas, one type, two tables based on the type:

postgres=# select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
postgres=# create schema a;
CREATE SCHEMA
postgres=# create schema b;
CREATE SCHEMA
postgres=# create type typ1 as ( a int, b text );
CREATE TYPE
postgres=# create table a.t1 of typ1;
CREATE TABLE
postgres=# create table b.t1 of typ1;
CREATE TABLE
postgres=# insert into a.t1
postgres-# select a.*
postgres-#      , md5(a::text)
postgres-#   from generate_series(1,1000000) a;
INSERT 0 1000000
postgres=# insert into b.t1 select * from a.t1;
INSERT 0 1000000
postgres=# 

Both of the tables contain 1’000’000 rows so how much time would a modification of the type take?

postgres=# \timing
Timing is on.
postgres=# alter type typ1 add attribute c timestamp cascade;;
ALTER TYPE
Time: 9.338 ms
Time: 0.867 ms
postgres=# \d a.t1
                             Table "a.t1"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 a      | integer                     |           |          | 
 b      | text                        |           |          | 
 c      | timestamp without time zone |           |          | 
Typed table of type: typ1

postgres=# \d b.t1
                             Table "b.t1"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 a      | integer                     |           |          | 
 b      | text                        |           |          | 
 c      | timestamp without time zone |           |          | 
Typed table of type: typ1

Almost instant. Btw: Of course you can also remove an attribute from the type:

postgres=# alter type typ1 drop attribute c cascade;
ALTER TYPE
Time: 14.417 ms
postgres=# \d a.t1
                   Table "a.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 
Typed table of type: typ1

postgres=# \d b.t1
                   Table "b.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 
Typed table of type: typ1
 

Cet article Is adding a column to a typed table in PostgreSQL instant? est apparu en premier sur Blog dbi services.

Introducing SQL managed instances on Azure

Wed, 2018-06-13 00:43

I never wrote about data platform solutions on Azure so far. The fact is in Switzerland we’re definitely late about the Cloud adoption and Azure data platform solutions. There are different reasons that are more or less valid but I don’t want to contribute to any debate here. In any case the last announcements in this field with Azure data centers in Switzerland could encourage customers to reconsider this topic in the near future. Don’t get me wrong here, it doesn’t mean that customers in Switzerland must move all their database infrastructures in the Cloud but this is just an opportunity for them to consider rearchitecting some pieces of their information system including databases that may lead to hybrid scenarios. It will be likely the first step to the cloud adoption for data platforms solutions. At dbi services we didn’t exclude Azure from our target but we just silently continued to keep an eye on the data platform stack awaiting the right opportunity to move up a gear.

blog 138 - 0 - banner

Why to begin with SQL managed instances (MI)? After all, this feature is still on preview and it already exists a lot of Azure solutions as Azure SQL Databases (singleton and with elastic pool as well) as well as SQL Server on Azure VMs.

The point is this new feature is interesting in many aspects. Firstly, it will address the gap that currently exists between IaaS infrastructures (SQL Server VMs) and fully-managed services (with Azure SQL DBs). The former still requires maintaining the operating system (and licenses) while the latter didn’t expose all the feature surface needed by various application scenarios.

At the same time, Microsoft introduced another purchasing model that based on VCore. I remembered a discussion with one of my customers sometimes ago about DTUs. He asked me what is exactly DTU and I pointed out the following sentence from the Microsoft documentation.

The amount of resources is calculated as a number of Database Transaction Units or DTUs and is a bundled measure of compute, storage, and IO resources

That is definitely a good way to simply resource management because it makes an abstraction of the physical resources but this is probably it weakness in some degrees. Indeed, how to translate what DBAs and infrastructure team usually manage from on-premises to the cloud? Obviously, Microsoft provided a calculator to help customers to address their questions before moving to the cloud but the fact is database administrators seem to not be comfortable to deal with DTUs . But now let’s talk about flexibility: in many databases scenarios we don’t want to increase / decrease resources in the same bundle but we want to get a better control of the resource configuration by dissociating compute (CPU / RAM) from the storage. From my experience, I had never seen one customer to scale compute and storage in the same manner regarding their workload. Indeed, some workloads require high-performance storage while others are more CPU-bound. This is where the new vCore-based model comes into play by and I believe it will get a better adoption from customers to smoothly move to the cloud. That’s at least my opinion!

So, let’s try to play with MI. As a reminder, currently it is in preview but that’s enough to get a picture of what you may expect in the future. In my demo, I will use intensively CLI tools with dedicated PowerShell cmdlets and mssql-cli as well. This is voluntary because the fact is more and more administration tasks are done in this way and Microsoft provided all the commands to achieve them.

[dab@DBI-LT-DAB:#]> Get-AzureRmResourceGroup -Name sql-mi-rg


ResourceGroupName : sql-mi-rg
Location          : westeurope
ProvisioningState : Succeeded
Tags              :
ResourceId        : /subscriptions/913528f5-f1f8-4d61-af86-30f2eb0839ba/resourceGroups/sql-mi-rg

[dab@DBI-LT-DAB:#]> Get-AzureRmResource -ResourceGroupName sql-mi-rg | ft Name, ResourceType, Location -AutoSize

Name                                                    ResourceType                             Location
----                                                    ------------                             --------
sql-mi-client_OsDisk_1_842d669310b04cbd8352962c4bda5889 Microsoft.Compute/disks                  westeurope
sql-mi-client                                           Microsoft.Compute/virtualMachines        westeurope
shutdown-computevm-sql-mi-client                        Microsoft.DevTestLab/schedules           westeurope
sql-mi-client453                                        Microsoft.Network/networkInterfaces      westeurope
sql-mi-client-nsg                                       Microsoft.Network/networkSecurityGroups  westeurope
sqlmiclientnsg675                                       Microsoft.Network/networkSecurityGroups  westeurope
sql-mi-client-ip                                        Microsoft.Network/publicIPAddresses      westeurope
sqlmiclientip853                                        Microsoft.Network/publicIPAddresses      westeurope
sql-mi-routetable                                       Microsoft.Network/routeTables            westeurope
sql-mi-vnet                                             Microsoft.Network/virtualNetworks        westeurope
sql-mi-dbi                                              Microsoft.Sql/managedInstances           westeurope
sql-mi-dbi/ApplixEnterprise                             Microsoft.Sql/managedInstances/databases westeurope
sql-mi-dbi/dbi_tools                                    Microsoft.Sql/managedInstances/databases westeurope
VirtualClustersql-mi-subnet                             Microsoft.Sql/virtualClusters            westeurope
sqlmirgdiag947                                          Microsoft.Storage/storageAccounts        westeurope

My MI is composed of difference resources including:

  • VirtualClustersql-mi-subnet – a logical container of managed instances?
  • sql-mi-dbi as managed instance
  • sql-mi-dbi/ApplixEnterprise and sql-mi-dbi/dbi_tools as managed databases.
  • Network components including sql-mi-vnet, sql-mi-routetable

Here some more details of my MI:

[dab@DBI-LT-DAB:#]> Get-AzureRmSqlManagedInstance | ft ManagedInstanceName, Location, ResourceGroupName, LicenseType, VCores, StorageSizeInGB -AutoSize

ManagedInstanceName Location   ResourceGroupName LicenseType     VCores StorageSizeInGB
------------------- --------   ----------------- -----------     ------ ---------------
sql-mi-dbi          westeurope sql-mi-rg         LicenseIncluded      8              32

 

I picked up a GEN4 configuration based on General Purpose pricing that includes 8 VCores and 32GB of storage.

My managed databases are as follows:

[dab@DBI-LT-DAB:#]> Get-AzureRmSqlManagedDatabase -ManagedInstanceName sql-mi-dbi -ResourceGroupName sql-mi-rg | ft Name, ManagedInstanceName, Location, DefaultSecondaryLoc
ation, Status, Collation  -AutoSize

Name             ManagedInstanceName Location   DefaultSecondaryLocation Status Collation
----             ------------------- --------   ------------------------ ------ ---------
dbi_tools        sql-mi-dbi          westeurope northeurope              Online Latin1_General_CS_AS_KS
ApplixEnterprise sql-mi-dbi          westeurope northeurope              Online SQL_Latin1_General_CP1_CI_AS

 

Other resources are related to my other virtual client machine to connect to my MI. Indeed, the latter is not exposed through a public endpoint and it is reachable only from an internal network. I didn’t setup a site-to-site VPN to connect the MI from my remote laptop.

Another point that drew my attention is the high availability feature which is based on remote storage and Azure Service Fabric.

blog 138 - 5 - azure - sql managed instances - HA

Do you remember of the VirtualClustersql-mi-subnet described earlier? In fact, my MI is built upon a service fabric. Referring to the Microsoft documentation a Service Fabric enables you to build and manage scalable and reliable applications composed of microservices that run at high density on a shared pool of machines, which is referred to as a cluster.

We may get a picture of this underlying cluster from a set of dedicated sys.dm_hadr_fabric_* DMVs with a high-level view of the underlying cluster …

blog 138 - 6 - azure - sql managed instances - DMVs

… and a more detailed view including my managed databases:

blog 138 - 7 - azure - sql managed instances - DMVs detail

Now let’s get basic information from my MI:

blog 138 - 1 - azure - sql managed instances - engine version

The MI version may be easily identified by the engine_sql number equal to 8.

As said previously I have two user databases that exist and they were restored from backups hosted on my blob storage container.

Here an example of commands I used to restore them. You probably recognize the native RESTORE FROM URL syntax. Note also that you have different ways to restore / migrate your databases from on-premises environment with BACPAC and Azure Database Migration Service as well.

RESTORE FILELISTONLY 
FROM URL = 'https://mikedavemstorage.blob.core.windows.net/backup/ApplixEnterprise2014.bak'

RESTORE DATABASE [ApplixEnterprise] 
FROM URL = 'https://mikedavemstorage.blob.core.windows.net/backup/ApplixEnterprise2014.bak';

 

Here a list of my existing user databases:

blog 138 - 2 - azure - sql managed instances - databases

Let’s go further with database files configuration:

SELECT 
	DB_NAME(database_id) AS [db_name],
	file_id,
	type_desc,
	name AS [logical_name],
	physical_name,
	state_desc AS [state],
	size / 128 AS size_MB,
	max_size
FROM
	sys.master_files;
GO

 

blog 138 - 3 - azure - sql managed instances - database files

 

Some interesting points here:

1 – tempdb is pre-configured with 12 data files and 16MB each? Probably far from our usualk recommendation but anyway the preview allows to change it by using DBA usual scripts.

2- We may also notice that the user databases are placed on a different storage types (premium disk according to the Microsoft documentation). System databases are hosted to a local path C:\WFRoot\DB 3\Fabric\work\data\ as well as well the tempdb database. I use a standard tier meaning that system DBs are all on an attached SSD included in the VCore price.

Just for fun, I tried to install our DMK maintenance tool which basically creates a dbi_tools database with maintenance objects (tables and stored procedures) and related SQL Server agent jobs. A databasemail configuration step is also part of the DMK installation and the good news is the feature is available with MIs. However, I quickly ran into was about some ALTER DATABASE commands we use at the beginning of the deployment script:

Msg 5008, Level 16, State 14, Line 72
This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.
Msg 5069, Level 16, State 1, Line 72
ALTER DATABASE statement failed.
Msg 5008, Level 16, State 14, Line 89
This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.
Msg 5069, Level 16, State 1, Line 89
ALTER DATABASE statement failed.

 

The fix was quite easy and I finally managed to deploy the tool as show below:

blog 138 - 4 - azure - sql managed instances - dbi tools

The next step consisted in testing our different maintenance tasks:

  • Database integrity check task
  • Index maintenance task
  • Update statistics maintenance task
  • Backup task

The first 3 tasks worked well without any modification. However, for backups, I needed to used URL-based backups because it is the only method supported so far. Unfortunately, the current version of our DMK maintenance tool doesn’t not handled correctly it does shared access signatures that come with BACKUP TO URL command since SQL Server 2016. The fix will be included soon to the next release for sure :). For the context of my test I modified a little bit the statement generated by the maintenance objects and it worked perfectly:

-- Backup database dbi_tools
BACKUP DATABASE  [dbi_tools] 
TO URL = 'https://sqlmidbistorage.blob.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK'
WITH COPY_ONLY, CHECKSUM, INIT, FORMAT;

--Verification of the backup https://sqlmidbistorage.blob.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK
RESTORE VERIFYONLY FROM URL = 'https://sqlmidbistorage.blob.core.windows.net/sqlmidbicontainer/sql-mi-dbi.weu157eee5444ccf.database.windows.net_dbi_tools_1_20180611210123.BAK'
WITH STATS = 100, CHECKSUM;

 

And to finish this blog post as a good DBA, let’s have a look at the resource allocation management. First time I took a look at the resources available on the MI I was very surprised. To get an idea, let’s run some DMVs as sys.dm_os_schedulers, sys.dm_os_sys_memory or sys.dm_os_sys_info DMVs to get a real picture of these aforementioned resources:

blog 138 - 8 - azure - sql managed instances - resources

Given the number of visible online schedulers only 8 may be used by the MI. This is an expected outcome according to my initial configuration. Concerning the memory configuration, the theorical amount of memory available I can get should be 8 x 7GB = 56GB according the Microsoft documentation and the sys.dm_os_sys_memory DMV doesn’t really indicate such capping while the sys.dm_os_sys_info DMV does (at least closer to the reality)

blog 138 - 10 - azure - sql managed instances - memory capping

Are the CPU and memory resources managed differently on MI? I found out the right answer in this article from the SQL Server customer advisor team. For MI, this mechanism that is responsible of resource management is called Job Objects. That’s very interesting! Without going into details, this is exactly the same mechanism used by Docker on Windows and it is similar (at least in the concept) to existing CGroups on Linux.

Therefore, we may also benefit from another DMV to get details of resource management:

blog 138 - 11 - azure - sql managed instances - job object dmv

Thanks to this SQL Server customer advisor team article, the situation becomes clearer with the following parameter values:

  • cpu_rate 100% indicates my vCores are used at 100% of their capacity
  • cpu_affinity_mask indicates we are limited to 8 OS level processors
  • process_memory_limit_mb is self-explanatory and corresponds to my previous theorical assumptions :)
  • non_sos_mem_gap_mb corresponds to a safe amount of available memory for non-SQLOS activity

 

Conclusion

I think Microsoft is doing a great strategic play by introducing this solution for customers. Indeed, change is always a challenge and moving towards something almost similar to what we already know allows a smooth transition and a better adoption from humans. We will see what happens!

 

Cet article Introducing SQL managed instances on Azure est apparu en premier sur Blog dbi services.

Change Data Capture from Oracle with StreamSet Data Collector

Mon, 2018-06-11 07:21

With this trend of CQRS architectures where the transactions are streamed to a bunch of heterogenous eventually consistent polyglot-persistence microservices, logical replication and Change Data Capture becomes an important component, already at the architecture design phase. This is good for existing products vendors such as Oracle GoldenGate (which must be licensed even to use only the CDC part in the Oracle Database as Streams is going to be desupported) or Dbvisit replicate to Kafka. But also for Open Source projects. There are some ideas running on (Debezium), VOODOO but not yet released.

Today I tested the Oracle CDC Data Collector for StreamSets. StreamSets Data Collector is an open-source project started by former people from Cloudera and Informatica, to define pipelines streaming data from data collectors. It is easy, simple and has a buch of destinations possible. The Oracle CDC is based on LogMiner which means that it is easy but may have some limitations (mainly datatypes, DDL replication and performance).

Install

The installation guide is at streamsets.com. I choose the easiest way for testing as they provide a Docker container (https://github.com/streamsets)

# docker run --restart on-failure -p 18630:18630 -d --name streamsets-dc streamsets/datacollector
Unable to find image 'streamsets/datacollector:latest' locally
latest: Pulling from streamsets/datacollector
605ce1bd3f31: Pull complete
529a36eb4b88: Pull complete
09efac34ac22: Pull complete
4d037ef9b54a: Pull complete
c166580a58b2: Pull complete
1c9f78fe3d6c: Pull complete
f5e0c86a8697: Pull complete
a336aef44a65: Pull complete
e8d1e07d3eed: Pull complete
Digest: sha256:0428704019a97f6197dfb492af3c955a0441d9b3eb34dcc72bda6bbcfc7ad932
Status: Downloaded newer image for streamsets/datacollector:latest
ef707344c8bd393f8e9d838dfdb475ec9d5f6f587a2a253ebaaa43845b1b516d

CaptureStreamSets001
And that’s all. I am ready to connect with http on port 18630.

The default user/password is admin/admin

The GUI looks simple and efficient. There’s a home page where you define the ‘pipelines’ and monitor them running. In the pipelines, we define sources and destinations. Some connectors are already installed, others can be automatically installed. For Oracle, as usual, you need to download the JDBC driver yourself because Oracle doesn’t allow to get it embedded for legal reasons. I’ll do something simple here just to check the mining from Oracle.

CaptureStreamSets002CaptureStreamSets003
In ‘Package Manager’ (the little gift icon on the top) go to JDBC and check ‘install’ for the streamsets-datacollector-jdbc-lib library
Then in ‘External Libraries’, install (with the ‘upload’ icon at the top) the Oracle jdbc driver (ojdbc8.jar).
I’ve also installed the MySQL one for future tests:

File Name Library ID
ojdbc8.jar streamsets-datacollector-jdbc-lib
mysql-connector-java-8.0.11.jar streamsets-datacollector-jdbc-lib

Oracle CDC pipeline

I’ll use the Oracle Change Data Capture here, based on Oracle LogMiner. The GUI is very easy: just select ‘Oracle CDC’ as source in a new pipeline. Click on it and configure it. I’ve set the minimum here.
In JDBC tab I’ve set only the JDBC Connection String to: jdbc:oracle:thin:scott/tiger@//192.168.56.188:1521/pdb1 which is my PDB (I’m on Oracle 18c here and multitenant is fully supported by StreamSets). In the Credentials tab I’ve set ‘sys as sysdba’ as username and its password. The configuration can also be displayed as JSON and here is the corresponding entry:

"configuration": [
{
"name": "hikariConf.connectionString",
"value": "jdbc:oracle:thin:scott/tiger@//192.168.56.188:1521/pdb1"
},
{
"name": "hikariConf.useCredentials",
"value": true
},
{
"name": "hikariConf.username",
"value": "sys as sysdba"
},
{
"name": "hikariConf.password",
"value": "oracle"
},
...

I’ve provided SYSDBA credentials and only the PDB service, but it seems that StreamSets figured out automatically how to connect to the CDB (as LogMiner can be started only from CDB$ROOT). The advantage of using LogMiner here is that you need only a JDBC connection to the source – but of course, it will use CPU and memory resource from the source database host in this case.

Then I’ve defined the replication in the Oracle CDC tab. Schema to ‘SCOTT’ and Table Name Pattern to ‘%’. Initial Change as ‘From Latest Change’ as I just want to see the changes and not actually replicate for this first test. But of course, we can define a SCN here which is what must be used to ensure consistency between the initial load and the replication. ‘Dictionary source to ‘Online Catalog’ – this is what will be used by LogMiner to map the object and column IDs to table names and column names. But be carefull as table structure changes may not be managed correctly with this option.

{
"name": "oracleCDCConfigBean.baseConfigBean.schemaTableConfigs",
"value": [
{
"schema": "SCOTT",
"table": "%"
}
] },
{
"name": "oracleCDCConfigBean.baseConfigBean.changeTypes",
"value": [
"INSERT",
"UPDATE",
"DELETE",
"SELECT_FOR_UPDATE"
] },
{
"name": "oracleCDCConfigBean.dictionary",
"value": "DICT_FROM_ONLINE_CATALOG"
},

I’ve left the defaults. I can’t think yet about a reason for capturing the ‘select for update’, but it is there.

Named Pipe destination

I know that the destination part is easy. I just want to see the captured changes here and I took the easiest destination: Named Pipe where I configured only the Named Pipe (/tmp/scott) and Data Format (JSON)

{
"instanceName": "NamedPipe_01",
"library": "streamsets-datacollector-basic-lib",
"stageName": "com_streamsets_pipeline_stage_destination_fifo_FifoDTarget",
"stageVersion": "1",
"configuration": [
{
"name": "namedPipe",
"value": "/tmp/scott"
},
{
"name": "dataFormat",
"value": "JSON"
},
...

Supplemental logging

The Oracle redo log stream is by default focused only on recovery (replay of transactions in the same database) and contains only the minimal physical information requried for it. In order to get enough information to replay them in a different database we need supplemental logging for the database, and for the objects involved:

SQL> alter database add supplemental log data;
Database altered.
SQL> exec for i in (select owner,table_name from dba_tables where owner='SCOTT' and table_name like '%') loop execute immediate 'alter table "'||i.owner||'"."'||i.table_name||'" add supplemental log data (primary key) columns'; end loop;
PL/SQL procedure successfully completed.

Run

And that’s all. Just run the pipeline and look at the logs:

CaptureStreamSets005-log

StreamSet Oracle CDC pulls continuously from LogMiner to get the changes. Here are the queries that it uses for that:

BEGIN DBMS_LOGMNR.START_LOGMNR( STARTTIME => :1 , ENDTIME => :2 , OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.NO_SQL_DELIMITER); END;

This starts to mine between two timestamp. I suppose that it will read the SCNs to get finer grain and avoid overlapping information.

And here is the main one:

SELECT SCN, USERNAME, OPERATION_CODE, TIMESTAMP, SQL_REDO, TABLE_NAME, COMMIT_SCN, SEQUENCE#, CSF, XIDUSN, XIDSLT, XIDSQN, RS_ID, SSN, SEG_OWNER FROM V$LOGMNR_CONTENTS WHERE ((( (SEG_OWNER='SCOTT' AND TABLE_NAME IN ('BONUS','DEPT','EMP','SALGRADE')) ) AND (OPERATION_CODE IN (1,3,2,25))) OR (OPERATION_CODE = 7 OR OPERATION_CODE = 36))

This reads the redo records. The operation codes 7 and 36 are for commit and rollbacks. The operations 1,3,2,25 are those that we want to capture (insert, update, delete, select for update) and were defined in the configuration. Here the pattern ‘%’ for the SCOTT schema has been expanded to the table names. As far as I know, there’s no DDL mining here to automatically capture new tables.

Capture

Then I’ve run this simple insert (I’ve added a primary key on this table as it is not ther from utlsampl.sql):

SQL> insert into scott.dept values(50,'IT','Cloud');

And I committed (as it seems that StreamSet buffers the changes until the end of the transaction)

SQL> commit;

and here I got the message from the pipe:

/ $ cat /tmp/scott
 
{"LOC":"Cloud","DEPTNO":50,"DNAME":"IT"}

The graphical interface shows how the pipeline is going:
CaptureStreamSets006

I’ve tested some bulk loads (direct-path inserts) and it seems to be managed correctly. Actually, this Oracle CDC is based on LogMiner so it is fully supported (no mining of proprietary redo stream format) and limitations are clearly documented.

Monitoring

Remember that the main work is done by LogMiner, so don’t forget to look at the alert.log on the source database. With big transactions, you may need large PGA (but you can also choose buffer to disk). If you have Oracle Tuning Pack, you can also monitor the main query which retreives the redo information from LogMiner:
CaptureStreamSets007
You will see a different SQL_ID because the filter predicates sues literals instead of bind variables (which is not a problem here).

Conclusion

This product is very easy to test, so you can do a Proof of Concept within a few hours and test for your context: supported datatypes, operations and performance. By easy to test, I mean: very good documentation, very friendly and responsive graphical interface, very clear error messages,…

 

Cet article Change Data Capture from Oracle with StreamSet Data Collector est apparu en premier sur Blog dbi services.

Do you need the same column with the same check constraint twice? Create a domain!

Fri, 2018-06-08 07:29

Did you know that you can create domains in PostgreSQL? No, nothing to worry about. We’ll take Frank’s leave for a new opportunity as a chance to introduce the concept of domains. @Franck: Yes, although we all fully understand your decision and the reasons to move on to a new challenge, this post is dedicated to you and you need to be the example in the following little demo. Lets go …

For the (not in any way serious scope) of this post lets assume that we do not want Franck anymore to blog on our blog site. We want to ban him. Of course we could simply delete his user account or disable the login. But, hey, we want to do that by using a domain as that is much more fun to do. Lets assume our blog software comes with two little tables that look like this:

postgres=# \d blogs
                            Table "public.blogs"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('blogs_id_seq'::regclass)
 author | text    |           |          | 
 blog   | text    |           |          | 
Indexes:
    "blogs_pk" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "blog_comments" CONSTRAINT "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

postgres=# \d blog_comments
                             Table "public.blog_comments"
 Column  |  Type   | Collation | Nullable |                  Default                  
---------+---------+-----------+----------+-------------------------------------------
 id      | integer |           | not null | nextval('blog_comments_id_seq'::regclass)
 blog_id | integer |           |          | 
 author  | text    |           |          | 
 comment | text    |           |          | 
Indexes:
    "blog_comments__pk" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

When we want that Franck is not anymore able to create blogs and to comment on blogs we could do something like this:

postgres=# alter table blogs add constraint no_franck_blogs check ( author ~ '!^Franck' );
ALTER TABLE
postgres=# alter table blog_comments add constraint no_franck_comments check ( author ~ '!^Franck' );
ALTER TABLE

This will prevent Franck (actually it will prevent all people called Franck, but this is good in that case as we do not like people called Franck anymore) from inserting anything into these two tables:

postgres=# insert into blogs (author,blog) values ('Franck Pachot','another great blog');
ERROR:  new row for relation "blogs" violates check constraint "no_franck_blogs"
DETAIL:  Failing row contains (1, Franck Pachot, another great blog).

(Btw: Did you notice that you can use regular expressions in check constraints?)

This works and does what we want it to do. But there is an easier way of doing it. Currently we need to maintain two check constraints which are doing the same thing. By creating a domain we can centralize that:

postgres=# create domain no_franck_anymore as text check (value ~ '!^Franck' );
CREATE DOMAIN

Once we have that we can use the domain in our tables:

postgres=# alter table blogs drop constraint no_franck_blogs;
ALTER TABLE
postgres=# alter table blog_comments drop constraint no_franck_comments;
ALTER TABLE
postgres=# alter table blogs alter column author type no_franck_anymore;
ALTER TABLE
postgres=# alter table blog_comments alter column author type no_franck_anymore;
ALTER TABLE
postgres=# \d blogs
                                 Table "public.blogs"
 Column |       Type        | Collation | Nullable |              Default              
--------+-------------------+-----------+----------+-----------------------------------
 id     | integer           |           | not null | nextval('blogs_id_seq'::regclass)
 author | no_franck_anymore |           |          | 
 blog   | text              |           |          | 
Indexes:
    "blogs_pk" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "blog_comments" CONSTRAINT "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

postgres=# \d blog_comments
                                  Table "public.blog_comments"
 Column  |       Type        | Collation | Nullable |                  Default                  
---------+-------------------+-----------+----------+-------------------------------------------
 id      | integer           |           | not null | nextval('blog_comments_id_seq'::regclass)
 blog_id | integer           |           |          | 
 author  | no_franck_anymore |           |          | 
 comment | text              |           |          | 
Indexes:
    "blog_comments__pk" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "comments_ref_blogs" FOREIGN KEY (blog_id) REFERENCES blogs(id)

This still prevents Franck from blogging:

postgres=# insert into blogs (author,blog) values ('Franck Pachot','another great blog');
ERROR:  value for domain no_franck_anymore violates check constraint "no_franck_anymore_check"

… but we only need to maintain one domain and not two or more check constraints.

 

Cet article Do you need the same column with the same check constraint twice? Create a domain! est apparu en premier sur Blog dbi services.

Disabling database trap with dataguard broker

Thu, 2018-06-07 09:31

When connecting to dataguard broker and disabling database with ORACLE_SID set before connecting to broker, broker becomes inaccessible, which is shown below:

Login to broker via database ila:


oracle [ILA@p03] /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs : dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;

Configuration - ila

Protection Mode: MaxAvailability
Databases:
ILAP - Primary database
ila - Physical standby database
ilal - Logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Disable database ila in broker:


DGMGRL> disable database ila;
Disabled.

Now neither enabling nor showing configuration is possible any more:


DGMGRL> enable database ila;
ORA-16541: database is not enabled

Configuration details cannot be determined by DGMGRL
DGMGRL> show configuration;
ORA-16541: database is not enabled

Configuration details cannot be determined by DGMGRL
DGMGRL> exit

Solution: You have to connect to broker via a database which is enabled.

 

Cet article Disabling database trap with dataguard broker est apparu en premier sur Blog dbi services.

Dealing with ugrade scenarios for SQL Server on Docker and Swarm

Wed, 2018-06-06 03:49

This blog post comes from an interesting experience with one customer about upgrading SQL Server on a Docker environment. Let’s set quickly the context: a production environment that includes a standalone Docker engine on the top of openSUSE Linux distribution with some SQL Server 2017 Linux containers. The deal was to update those SQL Server instances from 2017 CU1 to 2017 CU7.

blog 137 - 0 - banner update sql docker

The point is we applied the same kind of upgrades earlier on the morning but it concerned virtual environments with SQL Server 2017 on Windows Server 2016. As you already guessed, we cannot go on the same way with SQL Server containers. The good news is that the procedure is fully documented by Microsoft but let’s focus on my customer’s question: Can we achieve rolling upgrades with SQL Server containers? Rolling upgrade in this context may be defined as keeping the existing system running while we each component. Referring to the context of this customer the quick answer is no because they manage the standalone instances. However, we may hope to be as closed as possible to the existing rolling upgrade scenarios with SQL Server HA capabilities but it is pretty limited currently on Docker and didn’t make sense in this specific context.

In addition, let’s say that my customer spins up SQL Server containers by running docker run command. In this case, we had no choice to re-create the concerned containers with the new image. So basically, according to the Microsoft documentation, the game will consist of the following main steps:

  • To download the latest SQL Server image from the Microsoft docker hub.
  • To ensure we are using persistent volumes with SQL Server containers.
  • To Initiate DB user backups (Keep safe here)
  • To remove the concerned container
  • To re-create the container with the same definition but the upgraded based image

The aforementioned steps will lead to some SQL Server instance unavailability.

Let’s simulate this scenario on my lab environment with a custom image (but the principle remains the same as my customer).

[clustadmin@docker1 PROD]$ docker ps
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                    PORTS                    NAMES
aa6b4411e4e4        dbi/dbi_linux_sql2017:CU4   "./entrypoint.sh 'ta…"   22 minutes ago      Up 22 minutes (healthy)   0.0.0.0:1433->1433/tcp   prod_db_1
153b1cc0bbe0        registry:2                  "/entrypoint.sh /etc…"   6 weeks ago         Up About an hour          5000/tcp                 registry.1.pevhlfmsailjx889ktats1fnh

 

The first docker container concerns my SQL Server instance with a custom base image dbi/dbi_linux_sql2017:CU4. My environment includes also one user database dbi_tools and some related external objects as SQL Server jobs and additional logins – dbi and test logins. A simplified representation of my customer scenario …

blog 137 - 0 - mssql-cli sql version

blog 137 - 2 - mssql-cli sql login

blog 137 - 1 - mssql-cli user db

So, the challenge here is to upgrade the current container running on SQL Server 2017 CU4 with the last SQL Server 2017 CU7. The first step will consist in upgrading the dbi/dbi_linuxsql2017:CU4 image. Note I will use docker-compose in the next part of my demo but we’ll achieve exactly the same goal differently. So, let’s modify the FROM command line inside the docker file line as follows:

# set the base image (2017 version)
# > CU4 includes SQL Server agent now
FROM microsoft/mssql-server-linux:2017-CU7

 

Then let’s run a docker-compose command with the following docker-compose input file in order to generate a new fresh SQL Server image (CU7). The interesting part of my docker-compose file:

version: '3.1'
services:
  db: 
    build: .
    image: dbi/dbi_linux_sql2017:CU7
…
[clustadmin@docker1 PROD]$ docker-compose build

 

Let’s take a look at the existing docker images:

[clustadmin@docker1 PROD]$ docker images
REPOSITORY                                   TAG                    IMAGE ID            CREATED             SIZE
dbi/dbi_linux_sql2017                        CU7                    0b4d23626dae        44 minutes ago      1.43GB
dbi/dbi_linux_sql2017                        CU4                    0db4694645ec        About an hour ago   1.42GB
…

 

My new image has been created successfully. We may also notice 2 images now: the current one (with CU4 tag) and the new one (with CU7 tag)

Obviously persistent volumes are an important part of the customer architecture, so I also simulated some volume mappings inside my docker-compose file.

version: '3.1'
services:
  db:
    build: .
    image: dbi/dbi_linux_sql2017:CU7
    #container_name: dbi_linux_sql2017_cu4
    ports:
      - "1433:1433"
    volumes:
      - /u00/db2:/u00
      - /u01/db2:/u01
      - /u02/db2:/u02
      - /u03/db2:/u03
      - /u98/db2:/u98
environment:
      - MSSQL_SA_PASSWORD=Password1
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
      - MSSQL_USER=dbi
      - MSSQL_USER_PASSWORD=Password1
      - TZ=Europe/Berlin

 

Let’s move forward to the next step that consists in removing the current SQL Server 2017 CU4 container (prod_db_1):

[clustadmin@docker1 PROD]$ docker stop prod_db_1 && docker rm prod_db_1
prod_db_1
prod_db_1

 

And finally let’s spin up a new container based on the new image (SQL Server 2017 CU7)

[clustadmin@docker1 PROD]$ docker-compose up -d

 

Just out of curiosity, a quick look at the docker log output command reveals some related records concerning the upgrade process:

2018-06-04 22:45:43.79 spid7s      Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.
2018-06-04 22:45:43.80 spid7s
2018-06-04 22:45:43.80 spid7s      -----------------------------------------
2018-06-04 22:45:43.80 spid7s      Execution of PRE_SQLAGENT100.SQL complete
2018-06-04 22:45:43.80 spid7s      -----------------------------------------
2018-06-04 22:45:43.81 spid7s      DMF pre-upgrade steps...
2018-06-04 22:45:44.09 spid7s      DC pre-upgrade steps...
2018-06-04 22:45:44.09 spid7s      Check if Data collector config table exists...
…
2018-06-04 22:45:59.39 spid7s      ------------------------------------
2018-06-04 22:45:59.39 spid7s      Execution of InstDac.SQL complete
2018-06-04 22:45:59.39 spid7s      ------------------------------------
2018-06-04 22:45:59.40 spid7s      -----------------------------------------
2018-06-04 22:45:59.40 spid7s      Starting execution of EXTENSIBILITY.SQL
2018-06-04 22:45:59.40 spid7s      -----------------------------------------
2018-06-04 22:45:59.40 spid7s      -----------------------------------------
2018-06-04 22:45:59.40 spid7s      Finished execution of EXTENSIBILITY.SQL
2018-06-04 22:45:59.41 spid7s      -----------------------------------------
2018-06-04 22:45:59.44 spid7s      Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2018-06-04 22:45:59.44 spid7s      Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
2018-06-04 22:45:59.45 spid7s      Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.
2018-06-04 22:45:59.45 spid7s      Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.
2018-06-04 22:45:59.53 spid7s      Dropping view [dbo].[sysutility_ucp_configuration]
2018-06-04 22:45:59.53 spid7s      Creating view [dbo].[sysutility_ucp_configuration]...
2018-06-04 22:45:59.54 spid7s      Dropping view [dbo].[sysutility_ucp_policy_configuration]
2018-06-04 22:45:59.54 spid7s      Creating view [dbo].[sysutility_ucp_policy_configuration]...
2018-06-04 22:45:59.55 spid7s      Dropping [dbo].[fn_sysutility_get_is_instance_ucp]
….

 

The container has restarted correctly with the new based image as show below:

[clustadmin@docker1 PROD]$ docker ps
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                             PORTS                    NAMES
a17800f70fff        dbi/dbi_linux_sql2017:CU7   "./entrypoint.sh 'ta…"   4 minutes ago       Up 33 seconds (health: starting)   0.0.0.0:1433->1433/tcp   prod_db_1
153b1cc0bbe0        registry:2                  "/entrypoint.sh /etc…"   6 weeks ago         Up 2 hours                         5000/tcp                 registry.1.pevhlfmsailjx889ktats1fnh

 

Let’s check the new SQL Server version and if all my objects are still present:

blog 137 - 3 - mssql-cli sql version

The upgrade seems to be done successfully and all existing objects previous the upgrade operation still exist:

blog 137 - 4 - mssql-cli sql objects

 

Great job! But let’s go beyond to this procedure with the following question: Could we have done better here? From a process perspective, the response is probably yes but we have to rely on more sophisticated features provided by Swarm mode (or other orchestrators as K8s) as service deployment that will make the upgrade procedure drastically easier. But don’t get me wrong here. Even in Swarm mode or other orchestrators, we are not still able to guarantee the zero down time but we may perform the upgrade faster to be very close to the target.

Previously I used docker-compose to spin up my SQL Server container. Now let’s use this counterpart on a Docker Swarm environment.

[clustadmin@docker1 PROD]$ docker info | grep -i swarm
Swarm: active

[clustadmin@docker1 PROD]$ docker node ls
ID                            HOSTNAME                    STATUS              AVAILABILITY        MANAGER STATUS
hzwjpb9rtstwfex3zsbdnn5yo *   docker1.dbi-services.test   Ready               Active              Leader
q09k7pqe940qvv4c1jprzk2yv     docker2.dbi-services.test   Ready               Active
c9burq3qn4iwwbk28wrpikqra     docker3.dbi-services.test   Ready               Active

 

I already prepared a stack deployment that includes a task related to my SQL Server instance (2017 CU4):

[clustadmin@docker1 PROD]$ docker service ls
ID                  NAME                MODE                REPLICAS            IMAGE                       PORTS
2bmomzq0inu8        dbi_db              replicated          1/1                 dbi/dbi_linux_sql2017:CU4   *:1433->1433/tcp
[clustadmin@docker1 PROD]$ docker service ps dbi_db
ID                  NAME                IMAGE                       NODE                        DESIRED STATE       CURRENT STATE           ERROR               PORTS
rbtbkcz0cy8o        dbi_db.1            dbi/dbi_linux_sql2017:CU4   docker1.dbi-services.test   Running

 

A quick connection to the concerned SQL Server instance confirms we run on SQL Server 2017 CU4:

blog 137 - 5- mssql-cli sql version swarm

Now go ahead and let’s perform the same upgrade we’ve done previously (2017 CU7). In this case the game will consist in updating the corresponding docker-compose file with the new image as follows (I put only the interesting sample of my docker-compose file):

version: '3.1'
services:
  db: 
    build: .
    image: dbi/dbi_linux_sql2017:CU7
…

 

… and then I just have give the new definition of my docker-compose file as input of my stack deployment as follows:

[clustadmin@docker1 PROD]$ docker stack deploy -c docker-compose.yml dbi
Ignoring unsupported options: build

Updating service dbi_db (id: 2bmomzq0inu8q0mwkfff8apm7)
…

 

The system will then perform all the step we previously performed manually in the first test including stopping the old task (container), scheduling the old task’s update with the new image and finally starting the updated container as shown below:

[clustadmin@docker1 PROD]$ docker service ps dbi_db
ID                  NAME                IMAGE                                  NODE                        DESIRED STATE       CURRENT STATE             ERROR               PORTS
4zey68lh1gin        dbi_db.1            127.0.0.1:5000/dbi_linux_sql2017:CU7   docker1.dbi-services.test   Running             Starting 40 seconds ago
rbtbkcz0cy8o         \_ dbi_db.1        dbi/dbi_linux_sql2017:CU4              docker1.dbi-services.test   Shutdown            Shutdown 40 seconds ago

 

A quick check of my new SQL Server version:

blog 137 - 51- mssql-cli sql version swarm

That’s it!

In this blog post, I hope I managed to get you interested in using swarm mode in such case. Next time I will talk about SQL Server upgrade scenarios on K8s that is a little bit different.

Stay tuned!

 

 

 

 

 

Cet article Dealing with ugrade scenarios for SQL Server on Docker and Swarm est apparu en premier sur Blog dbi services.

How to compile PostgreSQL 11 with support for JIT compilation on RHEL/CentOS 7

Wed, 2018-06-06 01:30

As you might already know PostgreSQL 11 will bring support for just-in-time compilation. When you want to compile PostgreSQL 11 with jit support on RedHat/CentOS 7 this requires a little hack (more on the reason below). In this post we’ll look at how you can do it at least for testing. For production it is of course not recommended as hacking the make file is nothing you want to do, at least I would not do it. Lets go.

As mentioned I am on CentOS 7:

postgres@pgbox:$ cat /etc/centos-release
CentOS Linux release 7.5.1804 (Core) 

What you need to get support for jit is llvm. When you check the CentOS repository llvm is there:

postgres@pgbox:$ yum search llvm
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.spreitzer.ch
 * extras: mirror.spreitzer.ch
 * updates: mirror.spreitzer.ch
===================================================== N/S matched: llvm =====================================================
llvm-devel.x86_64 : Libraries and header files for LLVM
llvm-doc.noarch : Documentation for LLVM
llvm-libs.x86_64 : LLVM shared libraries
llvm-ocaml.x86_64 : OCaml binding for LLVM
llvm-ocaml-devel.x86_64 : Development files for llvm-ocaml
llvm-ocaml-doc.noarch : Documentation for LLVM's OCaml binding
llvm-private.i686 : llvm engine for Mesa
llvm-private.x86_64 : llvm engine for Mesa
llvm-private-devel.i686 : Libraries and header files for LLVM
llvm-private-devel.x86_64 : Libraries and header files for LLVM
llvm-static.x86_64 : LLVM static libraries
mesa-private-llvm.i686 : llvm engine for Mesa
mesa-private-llvm.x86_64 : llvm engine for Mesa
mesa-private-llvm-devel.i686 : Libraries and header files for LLVM
mesa-private-llvm-devel.x86_64 : Libraries and header files for LLVM
clang.x86_64 : A C language family front-end for LLVM
llvm.x86_64 : The Low Level Virtual Machine

  Name and summary matches only, use "search all" for everything.

The issue is that the PostgreSQL documentation clearly states that llvm needs to be at least of version 3.9 and in the CentOS repository you’ll find this:

postgres@pgbox:$ yum info llvm
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror1.hs-esslingen.de
 * extras: mirror.fra10.de.leaseweb.net
 * updates: mirror.netcologne.de
Available Packages
Name        : llvm
Arch        : x86_64
Version     : 3.4.2
Release     : 8.el7
Size        : 1.3 M
Repo        : extras/7/x86_64
Summary     : The Low Level Virtual Machine
URL         : http://llvm.org/
License     : NCSA
Description : LLVM is a compiler infrastructure designed for compile-time,
            : link-time, runtime, and idle-time optimization of programs from
            : arbitrary programming languages.  The compiler infrastructure includes
            : mirror sets of programming tools as well as libraries with equivalent
            : functionality.

What to do? What you need to do is to add the epel repository where you can find llvm 3.9 and 5.0:

postgres@pgbox:$ wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
postgres@pgbox:$ sudo yum localinstall epel-release-latest-7.noarch.rpm
postgres@pgbox:$ sudo yum install llvm5.0 llvm5.0-devel clang

Having that we should be ready for configuration:

postgres@pgbox:$ PGHOME=/u01/app/postgres/product/11/db_0
postgres@pgbox:$ SEGSIZE=1
postgres@pgbox:$ BLOCKSIZE=8
postgres@pgbox:$ WALSEGSIZE=16
postgres@pgbox:$ ./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-pam \
            --with-ldap \
            --with-libxml \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
            --with-wal-segsize=${WALSEGSIZE}  \
            --with-llvm LLVM_CONFIG='/usr/lib64/llvm3.9/bin/llvm-config' \
            --with-systemd 

That succeeds so lets compile:

postgres@pgbox:$ make -j 4 all

… and you will run into this issue:

/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I../../src/include  -D_GNU_SOURCE  -flto=thin -emit-llvm -c -o localtime.bc localtime.c
clang: error: unknown argument: '-flto=thin'
make[2]: *** [localtime.bc] Error 1
make[2]: Leaving directory `/home/postgres/postgresql/src/timezone'
make[1]: *** [all-timezone-recurse] Error 2
make[1]: Leaving directory `/home/postgres/postgresql/src'
make: *** [all-src-recurse] Error 2

There is a mail thread on the hackers mailing list which describes the issue. Apparently the clang compiler is too old to understand this argument. What you could do is to adjust the corresponding line in the Makefile:

postgres@pgbox:$ vi src/Makefile.global.in
COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -flto=thin -emit-llvm -c
COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -emit-llvm -c

Doing all the stuff again afterwards:

postgres@pgbox:$ make clean
postgres@pgbox:$ ./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-pam \
            --with-ldap \
            --with-libxml \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
            --with-wal-segsize=${WALSEGSIZE}  \
            --with-llvm LLVM_CONFIG='/usr/lib64/llvm3.9/bin/llvm-config' \
            --with-systemd 
postgres@pgbox:$ make -j 4 all

… led to the following issue (at least for me):

make[2]: g++: Command not found
make[2]: *** [llvmjit_error.o] Error 127
make[2]: *** Waiting for unfinished jobs....
make[2]: Leaving directory `/home/postgres/postgresql/src/backend/jit/llvm'
make[1]: *** [all-backend/jit/llvm-recurse] Error 2
make[1]: Leaving directory `/home/postgres/postgresql/src'
make: *** [all-src-recurse] Error 2

This should be easy to fix:

postgres@pgbox:$ sudo yum install -y gcc-c++
postgres@pgbox:$ which g++
/bin/g++

Again:

postgres@pgbox:$ make -j 4 install
postgres@pgbox:$ cd contrib
postgres@pgbox:$ make -j 4 install

… and this time it succeeds (note that I did not run the regression tests, so maybe something will still go wrong there).

JIT is enabled by default and controlled by these parameters:

postgres=# select name,setting from pg_settings where name like 'jit%';
          name           | setting 
-------------------------+---------
 jit                     | on
 jit_above_cost          | 100000
 jit_debugging_support   | off
 jit_dump_bitcode        | off
 jit_expressions         | on
 jit_inline_above_cost   | 500000
 jit_optimize_above_cost | 500000
 jit_profiling_support   | off
 jit_provider            | llvmjit
 jit_tuple_deforming     | on
(10 rows)

To test that it really kicks in you can do something like this:

postgres=#create table ttt (a int, b text, c date );
postgres=#insert into ttt (a,b,c)
           select aa.*, md5(aa::text), now()
             from generate_series(1,1000000) aa;
postgres=#set jit_above_cost=5;
postgres=#set jit_optimize_above_cost=5;
postgres=#set jit_inline_above_cost=5;
postgres=#explain select sum(a) from ttt;

… which should lead to a plan like this:

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=15554.55..15554.56 rows=1 width=8)
   ->  Gather  (cost=15554.33..15554.54 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=14554.33..14554.34 rows=1 width=8)
               ->  Parallel Seq Scan on ttt  (cost=0.00..13512.67 rows=416667 width=4)
 JIT:
   Functions: 8
   Inlining: true
   Optimization: true
(9 rows)

Hope that helps.

 

Cet article How to compile PostgreSQL 11 with support for JIT compilation on RHEL/CentOS 7 est apparu en premier sur Blog dbi services.

PostgreSQL 11: Instant add column with a non null default value

Tue, 2018-06-05 06:53

As I am currently preparing my session for the Swiss PGDay which is about some of the new features for PostgreSQL 11, I though this one is worth a blog post as well. Up to PostgreSQL 10 when you add a column to table which has a non null default value the whole table needed to be rewritten. With PostgreSQL 11 this is not anymore the case and adding a column in such a way is almost instant. Lets check.

We start by creating a test table in PostgreSQL 10:

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

postgres=# create table test ( a int, b text, c timestamp );
CREATE TABLE
postgres=# insert into test (a,b,c) 
           select aa.*, md5(aa::text), now() 
             from generate_series ( 1, 1000000 ) aa;
INSERT 0 1000000

This gave us 1’000’000 rows and what I want to do is to check the amount of sequential scans against the table before and after the alter table.

postgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;
 seq_scan 
----------
        0
postgres=# alter table test add column d text default 'a';
ALTER TABLE
Time: 1252.188 ms (00:01.252)
postgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;
 seq_scan 
----------
        1

As you can see a sequential scan happened when the alter table was performed and it took more than a second for the alter table to complete. Lets do the same in PostgreSQL 11.

Creating the table:

postgres=# select version();
                                                            version                                                            
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
postgres=# create table test ( a int, b text, c timestamp );
CREATE TABLE
postgres=# insert into test (a,b,c) select aa.*, md5(aa::text), now() from generate_series ( 1, 1000000 ) aa ;
INSERT 0 1000000

Doing the same test again:

postgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;
 seq_scan 
----------
        0
(1 row)

postgres=# alter table test add column d text default 'a';
ALTER TABLE
Time: 5.064 ms
postgres=# select seq_scan from pg_stat_user_tables where relid = 'test'::regclass;
 seq_scan 
----------
        0
(1 row)

No sequential scan at all and it only took 5 ms for the alter table to complete. This is quite a huge improvement. The question is how does that work in the background? Actually the idea is quite simple. The catalog table pg_attribute got two new columns called “attmissingval” and “atthasmissing”:

postgres=# \d pg_attribute
              Table "pg_catalog.pg_attribute"
    Column     |   Type    | Collation | Nullable | Default 
---------------+-----------+-----------+----------+---------
 attrelid      | oid       |           | not null | 
 attname       | name      |           | not null | 
 atttypid      | oid       |           | not null | 
 attstattarget | integer   |           | not null | 
 attlen        | smallint  |           | not null | 
 attnum        | smallint  |           | not null | 
 attndims      | integer   |           | not null | 
 attcacheoff   | integer   |           | not null | 
 atttypmod     | integer   |           | not null | 
 attbyval      | boolean   |           | not null | 
 attstorage    | "char"    |           | not null | 
 attalign      | "char"    |           | not null | 
 attnotnull    | boolean   |           | not null | 
 atthasdef     | boolean   |           | not null | 
 atthasmissing | boolean   |           | not null | 
 attidentity   | "char"    |           | not null | 
 attisdropped  | boolean   |           | not null | 
 attislocal    | boolean   |           | not null | 
 attinhcount   | integer   |           | not null | 
 attcollation  | oid       |           | not null | 
 attacl        | aclitem[] |           |          | 
 attoptions    | text[]    |           |          | 
 attfdwoptions | text[]    |           |          | 
 attmissingval | anyarray  |           |          | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

As soon as a new column with a non null default value is added to a table these columns get populated. We can see that when we check for our current table. The column we added has that set in pg_attribute:

postgres=# select attmissingval
                , atthasmissing 
             from pg_attribute 
            where attrelid = 'test'::regclass 
              and attname = 'd';

 attmissingval | atthasmissing 
---------------+---------------
 {a}           | t

(1 row)

We know that all the rows in that table should have the new default value but we know also that the table was not rewritten. So whenever you select from the table and a row is missing the default it will be populated from pg_attribute:

postgres=# select d from test where a = 1;
 d 
---
 a

For new rows the default will be there anyway and as soon as the table is rewritten the flag is cleared:

postgres=# vacuum full test;
VACUUM
postgres=# select attmissingval
                , atthasmissing 
             from pg_attribute 
            where attrelid = 'test'::regclass 
              and attname = 'd';

 attmissingval | atthasmissing 
---------------+---------------
               | f
(1 row)

Nice feature.

 

Cet article PostgreSQL 11: Instant add column with a non null default value est apparu en premier sur Blog dbi services.

How to flashback databases in dataguard with broker

Tue, 2018-06-05 06:15

Last week I had to do some tests with dataguard. To make restores easier restore points were required.

Given is following configuration:

show configuration;
Configuration - ila
Protection Mode: MaxAvailability
 Databases:
 ila - Primary database
 ilal - Logical standby database
 ILAP - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
 SUCCESS

How to set restore points here ?

Two things to consider:
Redo apply to a physical standby is incompatible with setting a restore point so it must me switched off for a while.
SCN of the restore points of the standby databases must be lower than on primary database, otherwise redo shipment does not continue after flashback.

Setting of restore points

First redo shipment has to be stopped:

edit database "ilal" set state=APPLY-OFF;
edit database "ILAP" set state=APPLY-OFF;

Second, restore points on both standby databases are set:

create restore point RZ_C GUARANTEE FLASHBACK DATABASE;

 

Third, restore point on primary database is set:

create restore point RZ_C GUARANTEE FLASHBACK DATABASE;

Last, redo shipment is switched on again:

edit database "ILAP" set state=APPLY-ON;
edit database "ilal" set state=APPLY-ON;

Now dataguard is running as before.

Let’s flashback the databases:

First switch off redo shipment:

edit database "ILAP" set state=APPLY-OFF;
edit database "ilal" set state=APPLY-OFF;

Second start all three databases in mount mode:

shutdown immediate;
startup mount;

Third flashback primary database:

flashback database to restore point RZ_C;
alter database open read only;

Check whether flashbacked database looks fine.

If yes, open it read write:

shutdown immediate;
startup mount;
alter database open resetlogs;

Do the same on logical standby database:

flashback database to restore point RZ_C;
alter database open read only;

Check whether flashbacked database looks fine.

If yes, open it:

shutdown immediate;
startup mount;
alter database open resetlogs;

Last do flashback on physical standby:

flashback database to restore point RZ_C;

Now start redo shipment again:

edit database "ILAP" set state=APPLY-ON;
edit database "ilal" set state=APPLY-ON;

After a few minutes, dataguard will run as before:

show configuration;
Configuration - ila
Protection Mode: MaxAvailability
 Databases:
 ila - Primary database
 ilal - Logical standby database
 ILAP - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
 SUCCESS
 

Cet article How to flashback databases in dataguard with broker est apparu en premier sur Blog dbi services.

PostgreSQL 11 : Procedures are coming

Sat, 2018-06-02 05:38

Reading about new features about future version PostgreSQL 11. I see that procedures will be implemented. Why is it so important? Until now only functions are available in PostgtreSQL. Of course a function is a procedure that returns a value, we can say, yes it’s true but you cannot manage transactions in a function. To better understand let’s do a quick demonstration.
Note that I am using the snapshot developer version of PostgreSQL 11 .

[postgres@pg_essentials_p1 bin]$ ./psql
Null display is "NULL".
psql (11devel)
Type "help" for help.

(postgres@[local]:5432) [postgres] > select version();
                                                  version
--------------------------------------------------------------------------------
---------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
 (Red Hat 4.8.5-4), 64-bit
(1 row)

(postgres@[local]:5432) [postgres] >

For the demonstration I have a table emp

(postgres@[local]:5432) [test] > table emp;
 id | name
----+------
  1 | toto
  2 | Edge
(2 rows)

(postgres@[local]:5432) [test] >

And let’s say I want to insert data in my table using following function

CREATE or replace FUNCTION fun_insert_emp(id_emp int,  emp_name varchar(20))
 RETURNS  void AS $$
BEGIN
 insert into emp (id,name) values (id_emp,emp_name);
commit;
END;
$$
LANGUAGE PLPGSQL;

We can describe the function like this

(postgres@[local]:5432) [test] >  \sf  fun_insert_emp                           
CREATE OR REPLACE FUNCTION public.fun_insert_emp(id_emp integer, emp_name character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
 insert into emp (id,name) values (id_emp,emp_name);
commit;
END;
$function$
(postgres@[local]:5432) [test] >

To call a function we use a select like any built-in function. So let’s try to insert a new employee with the function

(postgres@[local]:5432) [test] > select fun_insert_emp(3,'New Emp');
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function fun_insert_emp(integer,character varying) line 4 at COMMIT
(postgres@[local]:5432) [test] >

Seems that the word COMMIT is causing trouble. OK let’s recreate the function without the COMMIT

(postgres@[local]:5432) [test] >  \sf  fun_insert_emp                           
CREATE OR REPLACE FUNCTION public.fun_insert_emp(id_emp integer, emp_name character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
 insert into emp (id,name) values (id_emp,emp_name);
END;
$function$
(postgres@[local]:5432) [test] >

And let’s call again the function. We can see that the row was inserted

(postgres@[local]:5432) [test] > select fun_insert_emp(3,'New Emp');             
fun_insert_emp
----------------

(1 row)

(postgres@[local]:5432) [test] > table emp;
 id |  name
----+---------
  1 | toto
  2 | Edge
  3 | New Emp
(3 rows)

(postgres@[local]:5432) [test] >

But the main problem we saw is that a function in PostgreSQL does not support any transaction instruction (BEGIN…COMMIT, BEGIN…ROLLBACK). This means that in a function I cannot use instructions that allow to rollback or commit based on some conditions.
But PostgreSQL 11 will support procedure. Let’s do again the demonstration with a procedure.
Let’s first create the procedure

(postgres@[local]:5432) [test] > create or replace procedure proc_insert_emp (id_emp int,  emp_name varchar(20))
test-# as $$
test$# Begin
test$# insert into emp (id,name) values (id_emp,emp_name);
test$# commit;
test$# end ;
test$# $$
test-# LANGUAGE PLPGSQL;
CREATE PROCEDURE
(postgres@[local]:5432) [test] >

And let’s insert a new row in table emp using proc_insert_emp

(postgres@[local]:5432) [test] > call  proc_insert_emp(4,'Brice');
CALL

(postgres@[local]:5432) [test] > table emp;
 id |  name
----+---------
  1 | toto
  2 | Edge
  3 | New Emp
  4 | Brice
(4 rows)

(postgres@[local]:5432) [test] >

We can see that the row was inserted. But the main difference is the support of autonomous transaction and this will be definitively change life for developers.

 

Cet article PostgreSQL 11 : Procedures are coming est apparu en premier sur Blog dbi services.

Installing MAMP to play with PHP, MySQL and OpenFlights

Sat, 2018-06-02 02:30

You may wonder what I’m doing with those technologies that are completely different from what I usually do. I’ll detail in a future blog post but the idea is giving a short introduction to databases to students at https://powercoders.org/, a coding academy for refugees in Switzerland. They install MAMP (My Apache – MySQL – PHP) during their curriculum for website development, and then I’ll use this environment to teach data modeling and SQL basics. Thus, I’ve to look at MAMP, PHP and MySQL for the first time… And I decided to load the OpenFlights open data to play with.
That explains the title.

CaptureMAMP002
So MAMP is like LAMP (Linux+Apache+PHP+MySQL) but with a M for MacOS, but also Windows (W being an upside-down M after all). Let’s install that on my laptop. I download it from https://www.mamp.info/en/downloads/, run the .exe, all is straightforward and the installer notifies me that the installation will be completed after a reboot.

What? Reboot? Seriously, we are in 2018, that’s Windows 10, I refuse to reboot to install a simple Apache server!

This bundle is very easy to use: a simple window to start and stop the servers (Apache and MySQL) . A setup menu to configure them, but I keep the default. And a link to the start page. All that is installed under C:\MAMP (you can change it, I just kept the default settings). The first time you start the servers, the Windows Firewall configuration is raised and you have to accept it:

CaptureMAMP003

With all defaults (Apache on port 80) my web server pages are on http://localhost (serving the files in C:\MAMP\htdocs) and administration page is at http://localhost/MAMP/
The MySQL administration page (phpMyAdmin) is at http://localhost/MAMP/index.php?page=phpmyadmin. It seems that, at least by default, I don’t need a password to go to the admin pages.

display_errors

I’ll write some PHP and because it’s the first time in my life, I will have some errors. With the default configuration, Apache just raises and Error 500 which does not help me a lot for debugging. This configuration is ok for production because displaying errors may give clues to hackers. But I’m there to play and I want to see the error messages and line numbers.

I have to set display_errors=on for that. The current setting is displayed in http://localhost/MAMP/index.php?language=English&page=phpinfo#module_core and I can change it in C:\MAMP\conf\php7.2.1\php.ini and after a restart of the Apache server I can see full error messages:

Warning: mysqli_real_connect(): (28000/1045): Access denied for user 'root'@'localhost' (using password: YES) in C:\MAMP\htdocs\index.php on line 123

Little fix

But now that I display the errors, I get this annoying message each time I try to do something in phpMyAdmin (which runs as PHP in the same Apache server):

MAMP "count(): Parameter must be an array or an object that implements Countable"

CaptureMAMP004

So this product, which is free but has also a ‘PRO’ version, probably running the same code, is delivered with bad code, raising errors that were ignored. Don’t tell me that it is just a warning. You will see that parentheses are missing, this is a syntax error and raising only a warning for that is quite bad.
CaptureMAMP006
My common sense tells me that we should set display_errors=on and test a few screens before releasing a software. But that step has probably been skipped. Fortunately, the message is clear: line 615 of C:\MAMP\bin\phpMyAdmin\libraries\sql.lib.php

The message is about count() not having the correct parameter. The line 615 shows count($analyzed_sql_results[‘select_expr’] == 1 ) which is probably not correct because it counts a boolean expression. I’ve changed it to (count($analyzed_sql_results[‘select_expr’]) == 1 ) as I suppose they want to count and compare to one.

Well, I’ve never written one line of PHP and I already hate it for its error handling weakness.

Load some data

I want to initialize the database with some data and I’ll use the OpenFlights database. I’ve downloaded and unzipped https://github.com/jpatokal/openflights/blob/master/sql/master.zip
I go to the unzipped directory and run MySQL:

cd /d D:\Downloads\openflights-master

Another little thing to fix here: the sql\create.sql and sql\load-data.sql files contain some lines starting with “\! echo” but this \! command (to run a system command) exists on Linux but not on the Windows port of MySQL. We have to remove them before running the SQL scripts. I’m used to Oracle where I can port my code and scripts from one platform to the other, and was a but surprised by this.

Ready to connect:

C:\MAMP\bin\mysql\bin\mysql test --port=3306 --host=localhost --user root --password
Enter password:

The MySQL connection parameters are displayed on http://localhost/MAMP/ including the password (root)


source sql\create.sql
 
mysql> source sql\create.sql
Query OK, 0 rows affected (0.00 sec)
 
Connection id: 314
Current database: flightdb2
 
Query OK, 0 rows affected (0.02 sec)
 
Query OK, 0 rows affected (0.02 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
Query OK, 0 rows affected (0.00 sec)
...

This has created the flightdb2 database, with openflights user, and 15 tables.

Now, if you are still in the unzipped directory, you can load data with the source sql\load-data.sql script which loads from the data\*.dat files

mysql> source sql\load-data.sql
Query OK, 6162 rows affected, 4 warnings (0.04 sec)
Records: 6162 Deleted: 0 Skipped: 0 Warnings: 4
 
Query OK, 7184 rows affected, 7184 warnings (0.12 sec)
Records: 7184 Deleted: 0 Skipped: 0 Warnings: 7184
 
Query OK, 67663 rows affected (0.53 sec)
Records: 67663 Deleted: 0 Skipped: 0 Warnings: 0
 
Query OK, 260 rows affected (0.01 sec)
Records: 260 Deleted: 0 Skipped: 0 Warnings: 0
 
Query OK, 12 rows affected (0.01 sec)
Records: 12 Deleted: 0 Skipped: 0 Warnings: 0

Query from PHP

So, for my first lines of PHP I’ve added the following to C:\MAMP\htdocs\index.php:

<?php
$user = 'openflights'; $password = '';
$db = 'flightdb2'; $host = 'localhost'; $port = 3306;
 
$conn = mysqli_init();
if (!$conn) {
die("mysqli_init failed");
}
if (!$success = mysqli_real_connect( $conn, $host, $user, $password, $db, $port)) {
die("😠 Connection Error: " . mysqli_connect_error());
}
echo "😎 Connected to database <b>$db</b> as user <b>$user</b>.";
?>
 
<p>
Here are the Airports:
<table border=1>
<tr><th>IATA</th><th>Name</th></tr>
 
<?php
$result = $conn->query("select iata,name from airports where country='Greenland' order by 2");
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["iata"]. "</td><td> " . $row["name"]. "</tr>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
</table>

CaptureMAMP008

Here, I call mysqli_init(), set the credentials and call mysqli_real_connect() to get the connection handle. Then I run my query and display the result as an HTML table. Nothing difficult here. The main challenge is probably to keep the code maintainable.

In my opinion, and despite the small issues encountered, MAMP is a simple way to setup a development environment on Windows. All is there to introduce SQL and Database for developers, and show how to call it from a programming language.

 

Cet article Installing MAMP to play with PHP, MySQL and OpenFlights est apparu en premier sur Blog dbi services.

SQLcl connect target depends on previous connection

Fri, 2018-06-01 05:36

I thought it was a bug for two reasons: first, because I don’t like that my connect destination depends on the context, and then because it is a different behavior than in SQL*Plus. But finally, it is the correct behavior, and the 100% compatibility with SQL*Plus is expected only when SET CLASSIC=ON. And as I was surprised, and I think it can be dangerous, here is a blog post about it. Basically, be very careful if you are connected with a service name and you want to connect locally.

Basically, if you attended my demo about ‘From Transportable Tablespaces to Pluggable Databases’, where I switch between different instances you may have seen that I had to run ‘connect / as sysdba’ two times because the first one failed with invalid username/password

SQL> connect scott/tiger@//localhost/PDB1
Connected.
...
SQL> connect / as sysdba
USER =
URL = jdbc:oracle:oci8:@//localhost/PDB1
Error Message = ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.
 
SQL> connect / as sysdba
Connected.

This is not a big deal, but that means that it tries to connect to //localhost/PDB1 when I wanted to connect locally to my ORACLE_SID environment variable. Here, expecting a bequeath connection, I didn’t provide a password, then I cannot connect to the PDB. But imagine that I use a password, and the password is the same in the two databases… I would have been connected to the wrong database. Just imagine this:

SQL> host echo $ORACLE_SID
TEST
SQL> connect sys/password as sysdba
Connected.
-- checking something on PROD
SQL> connect sys/password@PROD as sysdba
Connected.
...
-- back to TEST (or at least I think so)
SQL> host echo $ORACLE_SID
TEST
SQL> connect sys/password as sysdba
Connected.
-- do terrible things
SQL> drop table VERY_IMPORTANT_TABLE;
Table VERY_IMPORTANT_TABLE dropped.
-- now look where I am:
SQL> show connection
CONNECTION:
SYS@jdbc:oracle:oci8:@PROD AS SYSDBA
CONNECTION_IDENTIFIER:
PROD

Actually, what happens is that when SQLcl is already connected with a connection string (i.e not locally using bequeath) the next connect command will use the same connection string. This means that:

connect user/password

is actually equivalent to

connect user/password@&_CONNECT_IDENTIFIER

SQL*Plus

This behavior has been introduced in SQLcl but this is not how SQL*Plus works:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL> connect / as sysdba
Connected.
SQL> define _CONNECT_IDENTIFIER
DEFINE _CONNECT_IDENTIFIER = "CDB1" (CHAR)

Disconnect

The first solution to avoid this in SQLcl is to always disconnect before you want to connect to a different service:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL> disc
 
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0
SQL> connect sys/oracle as sysdba
Connected.
SQL> show connection
CONNECTION:
SYS@jdbc:oracle:oci8:@ AS SYSDBA
CONNECTION_IDENTIFIER:
CDB$ROOT

This is why the second time was ok in my case: first one failed with invalid password and then I was disconnected.

TWO_TASK

The second solution is to set an impossible TWO_TASK (or LOCAL in Windows) so that local connections are impossible:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL> connect sys/oracle as sysdba
USER = sys
URL = jdbc:oracle:oci8:@NoWhere
Error Message = ORA-12154: TNS:could not resolve the connect identifier specified
USER = sys
URL = jdbc:oracle:thin:@NoWhere
Error Message = IO Error: Unknown host specified
USER = sys
URL = jdbc:oracle:thin:@NoWhere:1521/NoWhere
Error Message = IO Error: Unknown host specified

CLASSIC=ON

The third solution is to run SQLcl in SQL*Plus 100% compatible mode:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL> set classic on
SQL> show classic
SQL*Plus mode: ON
SQL> connect / as sysdba
Connected.

Here we have the same behavior as SQL*Plus: no use of current connection string.

The SQL CLASSIC ON is usually for the output (error messages, autotrace statistics, and a few enhancement made to SQLcl). And the online help still says that it is about output:

SQL> help set classic
SET CLASSIC
SET CLASSIC [ ON | OFF ] Set classic SQL*Plus Settings on
This will allow scripts which expect traditional output to be honored.

However, it seems that this CLASSIC mode is also very important for connection.

Test and show _CONNECTION_STRING

If you show the connection string at the prompt, this may prevent errors:

SQL> set sqlprompt "_connect_identifier> "
//localhost/CDB2> connect sys/oracle@//localhost/CDB1 as sysdba
Connected.
//localhost/CDB1> connect sys/oracle as sysdba
Connected.

Always check which database

By the way, when I prepare a script that can make some damages when not run at the correct place, I usually add a test on DBID on top of it:

CDB1> whenever sqlerror exit failure;
CDB1> select 0/decode(dbid,'944121612',1,0) from v$database;
 
Error starting at line : 1 in command -
select 0/decode(dbid,'944121612',1,0) from v$database
Error report -
ORA-01476: divisor is equal to zero

Different passwords

Of course, you should have different passwords on prod and test databases. However, I prefer to have passwords in a wallet (external password file) and then you will always have the correct identification as it is recorded for each service name.

 

Cet article SQLcl connect target depends on previous connection est apparu en premier sur Blog dbi services.

Patching ODA lite to 12.2.1.3.0

Thu, 2018-05-31 09:57

Here is the latest patch for your ODA and it seems that Oracle documentation for this patch is not exactly the procedure you’ll have to follow to successfully patch your appliance. I recently updated X6-2M and X6-2L to this latest release and here is how to do that. In this example I was patching from 12.1.2.12.0, no intermediate patch was needed.

1) Download the patch

It seems odd but sometimes finding the corresponding patch is not so easy! With the patch number, it’s more convenient. For 12.2.1.3.0 with the dcs stack the number is 27648057. This patch will update all the components:  dcs (odacli), operating system, bios/firmwares, ilom, GI and dbhomes.

Copy the patch in a temporary folder on the server, for example /opt/patch. You’ll need to be root to apply the patch.

2) Check the actual versions and free up space on disk

I recommend you to check the actual versions. It’s easy:

odacli describe-component

System Version
---------------
12.1.2.12.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       12.1.2.12.0           up-to-date
GI                                        12.1.0.2.170814       up-to-date
DB {
[ OraDB12102_home1 ]                      12.1.0.2.170814       up-to-date
[ OraDB11204_home1 ]                      11.2.0.4.170814       up-to-date
}
ILOM                                      3.2.7.26.a.r112632    up-to-date
BIOS                                      39050100              up-to-date
OS                                        6.8                   up-to-date
FIRMWARECONTROLLER {
[ c6 ]                                    4.650.00-7176         up-to-date
[ c0,c1,c2,c3,c4,c5                       KPYABR3Q              up-to-date
}
FIRMWAREDISK                              0R3Q                  up-to-date

Don’t care about Available Version column, it’s only valid after you register a new patch. Your ODA doesn’t check the latest patch online.

For free space check that folders /, /u01 and /opt have enough free GB to process. At least 10GB I think.

3) Prepare the patch files

It’s slightly different than previous versions. Only the first zipfile has to be uncompressed and registered. The 2 other files can directly be registered without unzipping them.

cd /opt/patch
unzip p27648057_122130_Linux-x86-64_1of3.zip

odacli update-repository -f /opt/patch/oda-sm-12.2.1.3.0-180504-server1of3.zip
odacli update-repository -f /opt/patch/p27648057_122130_Linux-x86-64_2of3.zip
odacli update-repository -f /opt/patch/p27648057_122130_Linux-x86-64_3of3.zip

4) Update the dcs-agent

First you’ll have to update the dcs-agent:

odacli update-dcsagent -v 12.2.1.3.0
{
"jobId" : "150b3486-cfb2-4b32-b751-0ed89ce3d7be",
"status" : "Created",
"message" : "Dcs agent will be restarted after the update. Please wait for 2-3 mins before executing the other commands",
"reports" : [ ],
"createTimestamp" : "May 22, 2018 10:31:50 AM CEST",
"resourceList" : [ ],
"description" : "DcsAgent patching",
"updatedTime" : "May 22, 2018 10:31:50 AM CEST"
}

As for every kind of operation you do with odacli, you receive a jobId you can monitor:

odacli describe-job -i "150b3486-cfb2-4b32-b751-0ed89ce3d7be"

Job details
----------------------------------------------------------------
                     ID:  150b3486-cfb2-4b32-b751-0ed89ce3d7be
            Description:  DcsAgent patching
                 Status:  Success
                Created:  May 22, 2018 10:31:50 AM CEST
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
dcsagent rpm verification                May 22, 2018 10:31:50 AM CEST       May 22, 2018 10:31:50 AM CEST       Success
Patch location validation                May 22, 2018 10:31:50 AM CEST       May 22, 2018 10:31:50 AM CEST       Success
dcs-agent upgrade                        May 22, 2018 10:31:50 AM CEST       May 22, 2018 10:31:51 AM CEST       Success

This update takes only a minute.

Check again the version and you will see a new component in 18c, quite weird:

odacli describe-component
System Version
---------------
12.2.1.3.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       12.1.2.12.0           12.2.1.3.0
GI                                        12.1.0.2.170814       12.2.0.1.180116
DB {
[ OraDB12102_home1 ]                      12.1.0.2.170814       12.1.0.2.180116
[ OraDB11204_home1 ]                      11.2.0.4.170814       11.2.0.4.180116
}
DCSAGENT                                  18.1.3.0.0            up-to-date
ILOM                                      3.2.7.26.a.r112632    up-to-date
BIOS                                      39050100              39090000
OS                                        6.8                   6.9
FIRMWARECONTROLLER {
[ c6 ]                                    4.650.00-7176         up-to-date
[ c0,c1,c2,c3,c4,c5 ]                     KPYABR3Q              kpyagr3q
}
FIRMWAREDISK                              0R3Q                  up-to-date

5) Update the server

Updating the server will actually update not only the operating system but also the ILOM, the BIOS, the firmware of internal disks and the GI. For the OS, Oracle Linux will go from 6.8 to 6.9 and the update process will assume that no additional packages have been installed. If you installed additional packages, please remove them as they can prevent the patch to apply correctly. Even devel packages should be removed, the upgrade of normal packages linked to them will not work because of the dependencies. You can easily remove the install packages with rpm -e, for example:

rpm -e openssl-devel-1.0.1e-48.el6_8.4.x86_64
rpm -e krb5-devel-1.10.3-57.el6.x86_64
rpm -e zlib-devel-1.2.3-29.el6.x86_64
rpm -e keyutils-libs-devel-1.4-5.0.1.el6.x86_64
rpm -e libcom_err-devel-1.42.8-1.0.2.el6.x86_64
rpm -e libselinux-devel-2.0.94-7.el6.x86_64
rpm -e libsepol-devel-2.0.41-4.el6.x86_64

Now you can safely run the patching:

odacli update-server -v 12.2.1.3.0

This update is the longest one (between 30 minutes and 1 hour), while you should see your server rebooting. As usual check the status of the job with describe-job:

odacli describe-job -i "27d2195f-f16b-44d8-84e0-6af6e48ccad7"

At the end of the process, describe-job will look like that:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Patch location validation                May 23, 2018 11:07:09 AM CEST       May 23, 2018 11:07:09 AM CEST       Success
dcs-controller upgrade                   May 23, 2018 11:07:09 AM CEST       May 23, 2018 11:07:09 AM CEST       Success
Patch location validation                May 23, 2018 11:07:09 AM CEST       May 23, 2018 11:07:09 AM CEST       Success
dcs-cli upgrade                          May 23, 2018 11:07:09 AM CEST       May 23, 2018 11:07:09 AM CEST       Success
Creating repositories using yum          May 23, 2018 11:07:09 AM CEST       May 23, 2018 11:07:10 AM CEST       Success
Applying HMP Patches                     May 23, 2018 11:07:10 AM CEST       May 23, 2018 11:09:04 AM CEST       Success
Patch location validation                May 23, 2018 11:09:04 AM CEST       May 23, 2018 11:09:04 AM CEST       Success
oda-hw-mgmt upgrade                      May 23, 2018 11:09:04 AM CEST       May 23, 2018 11:09:04 AM CEST       Success
Creating repositories using yum          May 23, 2018 11:09:04 AM CEST       May 23, 2018 11:09:09 AM CEST       Success
Applying OS Patches                      May 23, 2018 11:09:09 AM CEST       May 23, 2018 11:11:04 AM CEST       Success
OSS Patching                             May 23, 2018 11:11:04 AM CEST       May 23, 2018 11:11:04 AM CEST       Success
Applying Firmware Disk Patches           May 23, 2018 11:11:04 AM CEST       May 23, 2018 11:11:08 AM CEST       Success
Applying Firmware Expander Patches       May 23, 2018 11:11:08 AM CEST       May 23, 2018 11:11:13 AM CEST       Success
Applying Firmware Controller Patches     May 23, 2018 11:11:13 AM CEST       May 23, 2018 11:11:16 AM CEST       Success
Checking Ilom patch Version              May 23, 2018 11:11:17 AM CEST       May 23, 2018 11:11:19 AM CEST       Success
Patch location validation                May 23, 2018 11:11:19 AM CEST       May 23, 2018 11:11:20 AM CEST       Success
Apply Ilom patch                         May 23, 2018 11:11:21 AM CEST       May 23, 2018 11:11:22 AM CEST       Success
Copying Flash Bios to Temp location      May 23, 2018 11:11:22 AM CEST       May 23, 2018 11:11:22 AM CEST       Success
Starting the clusterware                 May 23, 2018 11:11:23 AM CEST       May 23, 2018 11:11:23 AM CEST       Success
Creating GI home directories             May 23, 2018 11:11:23 AM CEST       May 23, 2018 11:11:23 AM CEST       Success
Cloning Gi home                          May 23, 2018 11:11:23 AM CEST       May 23, 2018 11:13:53 AM CEST       Success
Configuring GI                           May 23, 2018 11:13:53 AM CEST       May 23, 2018 11:14:06 AM CEST       Success
Running GI upgrade root scripts          May 23, 2018 11:14:06 AM CEST       May 23, 2018 11:29:04 AM CEST       Success
Resetting DG compatibility               May 23, 2018 11:29:04 AM CEST       May 23, 2018 11:29:09 AM CEST       Success
Running GI config assistants             May 23, 2018 11:29:09 AM CEST       May 23, 2018 11:30:10 AM CEST       Success
restart oakd                             May 23, 2018 11:30:13 AM CEST       May 23, 2018 11:30:23 AM CEST       Success
Updating GiHome version                  May 23, 2018 11:30:23 AM CEST       May 23, 2018 11:30:25 AM CEST       Success
preRebootNode Actions                    May 23, 2018 11:30:39 AM CEST       May 23, 2018 11:31:25 AM CEST       Success
Reboot Ilom                              May 23, 2018 11:31:25 AM CEST       May 23, 2018 11:31:25 AM CEST       Success

Don’t forget that if there is a problem during the patching process (you forgot to remove an additional rpm for example), you can relaunch the patching and it will skip the already patched components. But you will loose a lot of time! Please control the components version after the reboot with describe-component.

6) Patch the dbhomes

You now need to patch the dbhomes separately. First of all list them:

odacli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
86b6a068-55a8-4171-9f94-48b86f135065     OraDB12102_home1     12.1.0.2.170814 (26680878, 26609798)     /u01/app/oracle/product/12.1.0.2/dbhome_1     Configured
c6e7d556-5785-41d8-a120-ed3ef756848a     OraDB11204_home1     11.2.0.4.170814 (26609929, 26609445)     /u01/app/oracle/product/11.2.0.4/dbhome_1     Configured

And then patch the first one:

odacli update-dbhome -v 12.2.1.3 -i c6e7d556-5785-41d8-a120-ed3ef756848a

It will also apply the datapatch on all the primary databases connected to this home. If you don’t have any database linked to the dbhome patching is fast:

odacli describe-job -i "9defa52d-2997-42ea-9bd3-aafdbf0a4dc5"
Job details
----------------------------------------------------------------
                     ID:  9defa52d-2997-42ea-9bd3-aafdbf0a4dc5
            Description:  DB Home Patching: Home Id is c6e7d556-5785-41d8-a120-ed3ef756848a
                 Status:  Success
                Created:  May 23, 2018 11:43:55 AM CEST
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
clusterware patch verification           May 23, 2018 11:43:56 AM CEST       May 23, 2018 11:43:58 AM CEST       Success
Patch location validation                May 23, 2018 11:43:58 AM CEST       May 23, 2018 11:43:59 AM CEST       Success
Opatch updation                          May 23, 2018 11:44:19 AM CEST       May 23, 2018 11:44:20 AM CEST       Success
Patch conflict check                     May 23, 2018 11:44:20 AM CEST       May 23, 2018 11:44:27 AM CEST       Success
db upgrade                               May 23, 2018 11:44:27 AM CEST       May 23, 2018 11:46:01 AM CEST       Success

About 2 minutes for an empty dbhome.

And for a dbhome linked to databases:

odacli update-dbhome -v 12.2.1.3 -i 86b6a068-55a8-4171-9f94-48b86f135065
{
"jobId" : "8083dc43-61fe-49da-8081-43b4e5257e95",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "May 23, 2018 12:46:06 PM CEST",
"resourceList" : [ ],
"description" : "DB Home Patching: Home Id is 86b6a068-55a8-4171-9f94-48b86f135065",
"updatedTime" : "May 23, 2018 12:46:06 PM CEST"
}

odacli describe-job -i "e871f741-7dd6-49c6-9b4a-af0d68e647e7"

Job details
----------------------------------------------------------------
ID:  e871f741-7dd6-49c6-9b4a-af0d68e647e7
Description:  DB Home Patching: Home Id is 86b6a068-55a8-4171-9f94-48b86f135065
Status:  Success
Created:  May 23, 2018 1:37:00 PM CEST
Message:  WARNING::Failed to run datapatch on db DB01TST##WARNING::Failed to run datapatch on db DB03TST

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
clusterware patch verification           May 23, 2018 1:37:21 PM CEST        May 23, 2018 1:37:23 PM CEST        Success
Patch location validation                May 23, 2018 1:37:23 PM CEST        May 23, 2018 1:37:23 PM CEST        Success
Opatch updation                          May 23, 2018 1:37:23 PM CEST        May 23, 2018 1:37:23 PM CEST        Success
Patch conflict check                     May 23, 2018 1:37:23 PM CEST        May 23, 2018 1:37:23 PM CEST        Success
db upgrade                               May 23, 2018 1:37:23 PM CEST        May 23, 2018 1:37:23 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:37:23 PM CEST        May 23, 2018 1:37:57 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:37:57 PM CEST        May 23, 2018 1:38:26 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:38:26 PM CEST        May 23, 2018 1:38:51 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:38:51 PM CEST        May 23, 2018 1:39:49 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:39:49 PM CEST        May 23, 2018 1:40:28 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:40:28 PM CEST        May 23, 2018 1:40:53 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:40:53 PM CEST        May 23, 2018 1:41:19 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:41:19 PM CEST        May 23, 2018 1:41:44 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:41:44 PM CEST        May 23, 2018 1:42:14 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:42:14 PM CEST        May 23, 2018 1:42:48 PM CEST        Success
SqlPatch upgrade                         May 23, 2018 1:42:48 PM CEST        May 23, 2018 1:43:22 PM CEST        Success

It’s longer but less than 10 minutes for this example. You can see the number of databases here (one line SqlPatch upgrade for one database). The job is successful, but actually not so successful because 2 warnings are raised. And be careful because message field in the job details is limited: check the /opt/oracle/dcs/log/dcs-agent.log for extended warning messages.

Always check the DB Version after the update of all dbhomes, this is the new version of the binaries:

odacli list-dbhomes

ID                                       Name                 DB Version                               Home Location                                 Status
---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
86b6a068-55a8-4171-9f94-48b86f135065     OraDB12102_home1     12.1.0.2.180116 (26925218, 26925263)     /u01/app/oracle/product/12.1.0.2/dbhome_1     Configured
c6e7d556-5785-41d8-a120-ed3ef756848a     OraDB11204_home1     11.2.0.4.180116 (26609929, 26925576)     /u01/app/oracle/product/11.2.0.4/dbhome_1     Configured

If update job is really successful, jump to step 8.

7) Update the databases where datapatch failed

For all the databases where datapatch cannot be applied, you’ll have to do it manually. I discovered that if your database is not in AMERICAN_AMERICA language and territory (FRENCH_FRANCE in my case) odacli will not be able to apply the datapatch on the database. Let’s check the status of the datapatch on the database, set the NLS_LANG environment to AMERICAN_AMERICA and apply the patch on these databases:

su – oracle
. oraenv <<< DB01TST
cd $ORACLE_HOME/OPatch
sqlplus / as sysdba
SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 26609798
Action : APPLY
Action Time : 24-SEP-2017 23:50:14
Description : DATABASE BUNDLE PATCH 12.1.0.2.170814
Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/26609798/21481992/26609798_apply_G100316_CD
BROOT_2017Sep24_23_48_22.log
Status : SUCCESS

Patch Id : 26925263
Action : APPLY
Action Time : 29-MAY-2018 15:40:27
Description : DATABASE BUNDLE PATCH 12.1.0.2.180116
Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/26925263/21857460/26925263_apply_DB01TST_20
18May29_15_40_13.log
Status : FAILED

PL/SQL procedure successfully completed.

exit
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
./datapatch -verbose
sqlplus / as sysdba
SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 26609798
Action : APPLY
Action Time : 24-SEP-2017 23:50:14
Description : DATABASE BUNDLE PATCH 12.1.0.2.170814
Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/26609798/21481992/26609798_apply_G100316_CD
BROOT_2017Sep24_23_48_22.log
Status : SUCCESS

Patch Id : 26925263
Action : APPLY
Action Time : 29-MAY-2018 15:40:27
Description : DATABASE BUNDLE PATCH 12.1.0.2.180116
Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/26925263/21857460/26925263_apply_DB01TST_20
18May29_15_40_13.log
Status : FAILED

Patch Id : 26925263
Action : APPLY
Action Time : 29-MAY-2018 15:53:43
Description : DATABASE BUNDLE PATCH 12.1.0.2.180116
Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/26925263/21857460/26925263_apply_DB01TST_20
18May29_15_53_01.log
Status : SUCCESS

exit

Repeat this operation on all the faulty databases.

8) Patching the storage

Patching the storage is actually patching the NVMe disks. This is not always mandatory as your disks can already have the latest version. So, if needed, apply the patch:

odacli update-storage -v 12.2.1.3.0
{
"jobId" : "4a221df4-8c85-4f34-aa7f-e014cdb751f7",
"status" : "Created",
"message" : "Success of Storage Update may trigger reboot of node after 4-5 minutes. Please wait till node restart",
"reports" : [ ],
"createTimestamp" : "May 29, 2018 13:27:41 PM CEST",
"resourceList" : [ ],
"description" : "Storage Firmware Patching",
"updatedTime" : "May 29, 2018 13:27:41 PM CEST"
}

odacli describe-job -i "4a221df4-8c85-4f34-aa7f-e014cdb751f7"

Job details
----------------------------------------------------------------
ID:  4a221df4-8c85-4f34-aa7f-e014cdb751f7
Description:  Storage Firmware Patching
Status:  Success
Created:  May 29, 2018 1:27:41 PM CEST
Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Applying Firmware Disk Patches           May 29, 2018 1:27:41 PM CEST        May 29, 2018 1:27:45 PM CEST        Success
Applying Firmware Expander Patches       May 29, 2018 1:27:45 PM CEST        May 29, 2018 1:27:52 PM CEST        Success
Applying Firmware Controller Patches     May 29, 2018 1:27:52 PM CEST        May 29, 2018 1:28:51 PM CEST        Success
preRebootNode Actions                    May 29, 2018 1:28:51 PM CEST        May 29, 2018 1:28:51 PM CEST        Success
Reboot Ilom                              May 29, 2018 1:28:51 PM CEST        May 29, 2018 1:28:51 PM CEST        Success

Beware of the reboot of the server few minutes after the patching is finished! And control the components version after the reboot.

9) Optional: deploy the latest db clone files

If you’ll never deploy a new dbhome this step is not necessary. If you will, or simply if you don’t know if you’ll have to do that later, download and register the new db clone files in the repository to be able to create a new dbhome at the same patch level, for example:

cd /opt/patch
unzip p23494992_122130_Linux-x86-64.zip
Archive:  p23494992_122130_Linux-x86-64.zip
 extracting: odacli-dcs-12.2.1.3.0-180320-DB-12.1.0.2.zip
  inflating: README.txt

update-image --image-files odacli-dcs-12.2.1.3.0-180320-DB-12.1.0.2.zip
Unpacking will take some time,  Please wait...
Unzipping odacli-dcs-12.2.1.3.0-180320-DB-12.1.0.2.zip

10) Control the version of the components

Now the patching is done. It took about 2 hours, not so bad for all these updates. Finally, do a last check of the components to see if everything is fine:

odacli describe-component
System Version
---------------
12.2.1.3.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       12.2.1.3.0            up-to-date
GI                                        12.2.0.1.180116       up-to-date
DB {
[ OraDB12102_home1 ]                      12.1.0.2.180116       up-to-date
[ OraDB11204_home1 ]                      11.2.0.4.180116       up-to-date
}
DCSAGENT                                  18.1.3.0.0            up-to-date
ILOM                                      4.0.0.24.r121140      up-to-date
BIOS                                      39090000              up-to-date
OS                                        6.9                   up-to-date
FIRMWARECONTROLLER {
[ c6 ]                                    4.650.00-7176         up-to-date
[ c0,c1,c2,c3,c4,c5 ]                     KPYAGR3Q              up-to-date
}
FIRMWAREDISK                              0R3Q                  up-to-date

Everything should be up-to-date now, until the next patch :-)

 

Cet article Patching ODA lite to 12.2.1.3.0 est apparu en premier sur Blog dbi services.

Introducing SQL Server on Kubernetes

Thu, 2018-05-31 06:50

After spending some times with Docker Swarm let’s introduce SQL Server on Kubernetes (aka K8s). Why another container orchestrator? Well, because Microsoft gives a strong focus on Kubernetes in their documentation and their events and because K8s is probably one of the most popular orchestration tools in the IT industry. By the way, I like to refer to the Portworx Annual Container Adoption Survey to get a picture of container trend over the years and we may notice there is no clear winner among orchestration tools yet between Swarm and K8s. By 2017, one another interesting point was persistent storage challenge that is the top 1 of the top list of adoption. I’m looking forward to see the next report about this point because you probably guessed, database containers rely mainly on it.

Anyway, as an IT services company, it appears justifiable to include K8s to our to-do list about container orchestrators :)

blog 136 - 000 - K8s - banner

First of all, let’s say this blog post doesn’t aim to compare Docker Swarm and K8s. Each platform has pros and cons and you can read a lot on the internet. I will rather expose some thoughts about deploying our dbi services docker image on this platform. Indeed, since last year we mainly work on our SQL Server docker image based on Docker and Docker Swarm architectures and it may be interesting to see if we may go the same way with K8s.

But before deploying our custom image we need to install a K8s infrastructure. From an installation perspective K8s cluster is likely harder to use than Docker Swarm. This time rather than using my own lab environment, I will shift on both Azure container and Azure container registry services to provision an operational K8s service. I just want here to focus on deploying my image and get some experience feedbacks about interacting with K8s. The Microsoft procedure is well-documented so there is no really adding-value to duplicate the installation steps. Because we operate on Azure, I will use a lot of az cli and kubectl commands to deploy and to manage my K8s service. Here some important information concerning my infrastructure:

I first installed and configured a private registry through the Azure container registry service in order to push my custom docker image for SQL Server 2017 on Linux. Obviously, this step may be optional regarding your context. My custom image is named dbi_linux_sql2017.

[dab@DBI-LT-DAB:#]> az acr repository list --name dbik8registry --output table
Result
------------------
dbi_linux_sql2017
mssql-server-linux

 

Then I installed my K8s service that includes 2 nodes. This is likely not a recommended scenario for production but it will fit with my need for the moment. I will probably scale my architecture for future tests.

[dab@DBI-LT-DAB:#]> kubectl cluster-info
Kubernetes master is running at https://dbik8sclus-k8s-rg-913528-...
Heapster is running at https://dbik8sclus-k8s-rg-913528-...
KubeDNS is running at https://dbik8sclus-k8s-rg-913528-...
kubernetes-dashboard is running at https://dbik8sclus-k8s-rg-913528-...
…
[dab@DBI-LT-DAB:#]> kubectl config view
apiVersion: v1
clusters:
- cluster:
    certificate-authority-data: REDACTED
    server: https://dbik8sclus-k8s-rg-913528-3eb7146d.hcp.westeurope.azmk8s.io:443
  name: dbik8scluster
contexts:
- context:
    cluster: dbik8scluster
    user: clusterUser_k8s-rg_dbik8scluster
  name: dbik8scluster
current-context: dbik8scluster
…

[dab@DBI-LT-DAB:#]> kubectl get nodes
NAME                       STATUS    ROLES     AGE       VERSION
aks-nodepool1-78763348-0   Ready     agent     6h        v1.9.6
aks-nodepool1-78763348-1   Ready     agent     6h        v1.9.6

 

From an Azure perspective, my K8s cluster is composed of several resources in a dedicated resource group with virtual machines, disks, network interfaces, availability sets and a K8s load balancer reachable from a public IP address.

blog 136 - 00 - K8s - Azure config

Finally, I granted to my K8s cluster sufficient permissions to access my private Docker registry (READ role).

[dab@DBI-LT-DAB:#]>$CLIENT_ID=(az aks show --resource-group k8s-rg --name dbik8scluster --query "servicePrincipalProfile.clientId" --output tsv)
[dab@DBI-LT-DAB:#]>$ACR_ID=$(az acr show --name dbik8registry --resource-group k8s-rg --query "id" --output tsv)

[dab@DBI-LT-DAB:#]>az role assignment create --assignee $CLIENT_ID --role Reader --scope $ACR_ID

[dab@DBI-LT-DAB:#]> kubectl get secrets
NAME                  TYPE                                  DATA      AGE
default-token-s94vc   kubernetes.io/service-account-token   3         6h

 

Similar to Docker Swarm, we may rely on secret capabilities to protect the SQL Server sa password. So, let’s take advantage of it!

[dab@DBI-LT-DAB:#]> kubectl create secret generic mssql --from-literal=SA_PASSWORD="xxxxx"
[dab@DBI-LT-DAB:#]> kubectl get secrets
NAME                  TYPE                                  DATA      AGE
default-token-s94vc   kubernetes.io/service-account-token   3         6h
mssql                 Opaque                                1         6h

 

At this stage before deploying my SQL Server application, let’s introduce some K8s important concepts we should be familiar as a database administrator.

  • Pod

Referring to the K8s documentation, a pod is a logical concept that represents one or more application containers with some shared resources as shared storage, networking including unique cluster IP address and metadata about each container image such image version, exposed port etc ….

Each container in the same pod is always co-located and co-scheduled and run in shared context on the same node. Comparing to Docker Swarm, the latter doesn’t offer such capabilities because as far I as know by default, tasks are spread services across the cluster and there is no really easy way to achieve the same concept than K8s pod.

To simplify, a K8s pod is a group of containers that are deployed together on the same host. Referring to my SQL Server deployment with only one container, pod may be replaced by container here but in a real production scenario SQL Server will likely be one part of a K8s pod.

blog 136 - 0 - K8s - POD

 

We may correlate what was said previously by using K8s related commands to pods. Here a status of the pod related to my SQL Server deployment.

[dab@DBI-LT-DAB:#]> kubectl get pods
NAME                                READY     STATUS    RESTARTS   AGE
mssql-deployment-5845f974c6-xx9jv   1/1       Running   0          3h

[dab@DBI-LT-DAB:#]> kubectl describe pod mssql-deployment-5845f974c6-xx9jv
Name:           mssql-deployment-5845f974c6-xx9jv
Namespace:      default
Node:           aks-nodepool1-78763348-0/10.240.0.4
Start Time:     Wed, 30 May 2018 19:16:46 +0200
Labels:         app=mssql
                pod-template-hash=1401953072
Annotations:    <none>
Status:         Running
IP:             10.244.1.13
Controlled By:  ReplicaSet/mssql-deployment-5845f974c6
Containers:
  mssql:
    Container ID:   docker://b71ba9ac3c9fa324d8ff9ffa8ec24015a676a940f4d2b64cbb85b9de8ce1e227
    Image:          dbik8registry.azurecr.io/dbi_linux_sql2017:CU4
    Image ID:       docker-pullable://dbik8registry.azurecr.io/dbi_linux_sql2017@sha256:5b9035c51ae2fd4c665f957da2ab89432b255db0d60d5cf63d3405b22a36ebc1
    Port:           1433/TCP
    State:          Running
      Started:      Wed, 30 May 2018 19:17:22 +0200
    Ready:          True
    Restart Count:  0
    Environment:
      ACCEPT_EULA:        Y
      MSSQL_SA_PASSWORD:  xxxxxx
      DMK:                Y
    Mounts:
      /var/opt/mssql from mssqldb (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from default-token-s94vc (ro)
Conditions:
  Type           Status
  Initialized    True
  Ready          True
  PodScheduled   True
Volumes:
  mssqldb:
    Type:       PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace)
    ClaimName:  mssql-data
    ReadOnly:   false
….

 

  • Replica set

A Replication set is a structure that enables you to easily create multiple pods, then make sure that that number of pods always exists. If a pod does crash, the Replication Controller replaces it. It also provides scale capabilities as we get also from Docker Swarm.

  • Service

From K8s documentation a service is also an abstraction which defines a logical set of Pods and a policy by which to access them – sometimes called a micro-service. The set of Pods targeted by a service is (usually) determined by a Label Selector. While there are some differences under the hood, we retrieve the same concepts with Docker Swarm from a deployment perspective.

  •  Virtual IP and service proxies

Referring again to the K8s documentation, every node in a Kubernetes cluster runs a kube-proxy that is responsible for implementing a form of virtual IP for Services. It includes Ingress network that is also part of Docker Swarm architecture with overlay networks and routing mesh capabilities.

In my case, as described previously I used an external load balancer with an EXTERNAL-IP configured to access my SQL Server container from the internet (xx.xxx.xxx.xx is my masked public IP as you already guessed)

[dab@DBI-LT-DAB:#]> kubectl get services
NAME               TYPE           CLUSTER-IP     EXTERNAL-IP     PORT(S)          AGE
kubernetes         ClusterIP      10.0.0.1       <none>          443/TCP          7h
mssql-deployment   LoadBalancer   10.0.200.170   xx.xxx.xxx.xx   1433:30980/TCP   4h

 

From an Azure perspective the above output corresponds to what we may identify as my Kubernetes load balancer and public IP address resources as well.

blog 136 - 1 - K8s - Load Balancer

blog 136 - 12 - K8s - Public IP

Once again, my intention was not to compare Docker Swarm and K8s at all but just to highlight the fact if you’re already comfortable with Docker Swarm, the move on K8s is not as brutal as we may suppose from a high-level point of view.

Ok let’s start now the deployment phase. As said previously my private container registry already contains my custom SQL Server image. I just had to tag my image on my local machine and to push it the concerned registry as I might do with other remote Docker registries.

[dab@DBI-LT-DAB:#]> docker images
REPOSITORY                                    TAG                 IMAGE ID            CREATED             SIZE
dbik8registry.azurecr.io/dbi_linux_sql2017    CU4                 3c6bafb33a5c        17 hours ago        1.42GB
dbi/dbi_linux_sql2017                         CU4                 3c6bafb33a5c        17 hours ago        1.42GB

[dab@DBI-LT-DAB:#]> az acr repository list --name dbik8registry --output table
Result
------------------
dbi_linux_sql2017
mssql-server-linux

[dab@DBI-LT-DAB:#]> az acr repository show-tags --name dbik8registry --repository mssql-server-linux --output table
Result
--------
2017-CU4

 

In addition, I used a persistent storage based on Azure managed disk in order to guarantee persistence for my SQL Server database files.

[dab@DBI-LT-DAB:#]> kubectl describe pvc mssql-data
Name:          mssql-data
Namespace:     default
StorageClass:  azure-disk
Status:        Bound
Volume:        pvc-32a42393-6402-11e8-885d-f2170a386bd7
…

 

Concerning the image itself we use some custom parameters to create both a dedicated user for applications that will run on the top of the SQL Server instance and to enable the installation of the DMK maintenance module for SQL Server at the container start up. We have other customization topics but for this blog post it will be sufficient to check what we want to test.

Here my deployment file. Comparing to Docker Swarm deployment file, I would say the manifest is more complex with K8s (that’s a least my feeling).

apiVersion: apps/v1beta1
kind: Deployment
metadata:
  name: mssql-deployment
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: mssql
    spec:
      terminationGracePeriodSeconds: 10
      containers:
      - name: mssql
        image: dbik8registry.azurecr.io/dbi_linux_sql2017:CU4
        ports:
        - containerPort: 1433
        env:
        - name: ACCEPT_EULA
          value: "Y"
        - name: MSSQL_SA_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mssql
              key: SA_PASSWORD 
        - name: DMK
          value: "Y"
        volumeMounts:
        - name: mssqldb
          mountPath: /var/opt/mssql
      volumes:
      - name: mssqldb
        persistentVolumeClaim:
          claimName: mssql-data
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-deployment
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 1433
      targetPort: 1433
  type: LoadBalancer

 

Let’s deploy and let’s spin up our SQL Server application

[dab@DBI-LT-DAB:#]> kubectl apply -f T:\dbi_dbaas_azure\sqlk8sazuredeployment.yaml
deployment "mssql-deployment" created
service "mssql-deployment" created

 

Pod and services are created. Let’s take a look at some information about them. Deployment and pod are ok. The last command shows the associated internal IP to connect in order to the SQL Server pod as well as a external / public IP address that corresponds to the Ingress load-balancer to connect from outside Azure internal network. We also get a picture of exposed ports.

[dab@DBI-LT-DAB:#]> kubectl get deployments
NAME               DESIRED   CURRENT   UP-TO-DATE   AVAILABLE   AGE
mssql-deployment   1         1         1            1           7m

[dab@DBI-LT-DAB:#]> kubectl get pods -o wide
NAME                               READY     STATUS    RESTARTS   AGE       IP            NODE
mssql-deployment-8c67fdccc-pbg6d   1/1       Running   0          12h       10.244.1.16   aks-nodepool1-78763348-0

[dab@DBI-LT-DAB:#]> kubectl get replicasets
NAME                         DESIRED   CURRENT   READY     AGE
mssql-deployment-8c67fdccc   1         1         1         12h

[dab@DBI-LT-DAB:#]> kubectl get services
NAME               TYPE           CLUSTER-IP     EXTERNAL-IP     PORT(S)          AGE
kubernetes         ClusterIP      10.0.0.1       <none>          443/TCP          9h
mssql-deployment   LoadBalancer   10.0.134.101   xxx.xxx.xxx.xxx   1433:31569/TCP   7m

[dab@DBI-LT-DAB:#]> kubectl describe service mssql-deployment
Name:                     mssql-deployment
Namespace:                default
Labels:                   <none>
…
Selector:                 app=mssql
Type:                     LoadBalancer
IP:                       10.0.134.101
LoadBalancer Ingress:     xxx.xxx.xxx.xxx
Port:                     <unset>  1433/TCP
TargetPort:               1433/TCP
NodePort:                 <unset>  31569/TCP
Endpoints:                10.244.1.16:1433
Session Affinity:         None
External Traffic Policy:  Cluster
Events:                   <none>

 

Let’s try now to connect to new fresh SQL Server instance from my remote laptop:

blog 136 - 2 - K8s - Container

Great job! My container includes all my custom stuff as the dbi_tools database and dedicated maintenance jobs related to our DMK maintenance tool. We may also notice the dbi user created during the container start up.

Just out of curiosity, let’s have a look at the pod log or container log because there is only one in the pod in my case. The log includes SQL Server log startup and I put only some interesting samples here that identify custom actions we implemented during the container startup.

[dab@DBI-LT-DAB:#]> kubectl get po -a
NAME                               READY     STATUS    RESTARTS   AGE
mssql-deployment-8c67fdccc-pk6sm   1/1       Running   0          21m

[dab@DBI-LT-DAB:#]> kubectl logs mssql-deployment-8c67fdccc-pk6sm
…
======= 2018-05-30 21:04:59 Creating /u00 folder hierarchy ========
cat: /config.log: No such file or directory
======= 2018-05-30 21:04:59 Creating /u01 folder hierarchy ========
======= 2018-05-30 21:04:59 Creating /u02 folder hierarchy ========
======= 2018-05-30 21:04:59 Creating /u03 folder hierarchy ========
======= 2018-05-30 21:04:59 Creating /u98 folder hierarchy ========
======= 2018-05-30 21:04:59 Linking binaries and configuration files to new FHS ========
======= 2018-05-30 21:04:59 Creating MSFA OK =======
….
2018-05-30 21:05:13.85 spid22s     The default language (LCID 1033) has been set for engine and full-text services.
======= 2018-05-30 21:05:29 MSSQL SERVER STARTED ========
======= 2018-05-30 21:05:29 Configuring tempdb database files placement =======
…
2018-05-30 21:06:05.16 spid51      Configuration option 'max server memory (MB)' changed from 2147483647 to 1500. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'max server memory (MB)' changed from 2147483647 to 1500. Run the RECONFIGURE statement to install.
======= 2018-05-30 21:06:05 Configuring max server memory OK =======
======= 2018-05-30 21:06:05 Creating login dbi =======
======= 2018-05-30 21:06:05 Creating login dbi OK =======
======= 2018-05-30 21:06:05 Installing DMK =======
Changed database context to 'master'.
Creating dbi_tools...
2018-05-30 21:06:12.47 spid51      Setting database option MULTI_USER to ON for database 'dbi_tools'.
Update complete.
Changed database context to 'dbi_tools'.

(1 rows affected)

(1 rows affected)
======= 2018-05-30 21:06:12 Installing DMK OK =======
======= MSSQL CONFIG COMPLETED =======
2018-05-30 21:10:09.20 spid51      Using 'dbghelp.dll' version '4.0.5'
2018-05-30 21:10:19.76 spid51      Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
2018-05-30 21:10:19.87 spid51      Using 'xplog70.dll' version '2017.140.3022' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.

 

To finish this blog post up properly, let’s simulate a pod failure to check the K8s behavior with our SQL Server container.

[dab@DBI-LT-DAB:#]> kubectl delete pod mssql-deployment-8c67fdccc-pk6sm
pod "mssql-deployment-8c67fdccc-pk6sm" deleted

[dab@DBI-LT-DAB:#]> kubectl get pods
NAME                               READY     STATUS        RESTARTS   AGE
mssql-deployment-8c67fdccc-jrdgg   1/1       Running       0          5s
mssql-deployment-8c67fdccc-pk6sm   1/1       Terminating   0          26m

[dab@DBI-LT-DAB:#]> kubectl get pods
NAME                               READY     STATUS    RESTARTS   AGE
mssql-deployment-8c67fdccc-jrdgg   1/1       Running   0          2m

 

As expected, the replica set is doing its job by re-creating the pod to recover my SQL Server instance, and by connecting to the persistent storage. We can check we can still connect on the instance from the load balancer IP address without running into any corruption issue.

blog 136 - 3 - K8s - Container 2

To conclude, I would say that moving our custom SQL docker image was not as hard as I expected. Obviously, there are some difference between the both orchestrator products but from an application point of view it doesn’t make a big difference. In an administration perspective, I’m agree the story is probably not the same :)

What about K8s from a development perspective? You may say that you didn’t own such Azure environment but the good news is you can use Minikube which is the single node version of Kubernetes mainly designed for local development. I will probably blog about it in the future. Stay tuned!

 

 

Cet article Introducing SQL Server on Kubernetes est apparu en premier sur Blog dbi services.

Pages