Feed aggregator

Certs and AdminClient … How to login?

DBASolved - Thu, 2019-07-25 15:26

I’ve been building a test environment using Docker for sometime (over and over), to validate some items within Oracle GoldenGate Microservices (current release as of writing – 19.1.0.0.1). Part of setting Oracle GoldenGate Microservices is to make the environment secure by using certificates. Per Oracle documentation, you can use Self-Signed Certificates for testing purposes (more on that in this post).

In my testing, I have built an Oracle GoldenGate 19c Microservices configuraiton with two deployments (Atlanta and New York). I can access the ServiceManager and login to the associated HTML5 pages with no problem. When I went to run items from the command line (adminclient), I wouldn’t login to the ServiceManager/Deployment due to a Network Error.

[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201</p>
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.

OGG (not connected) 1> connect https://ogg19c:16000 deployment NewYork as oggadmin password ********

ERROR: Network error - Certificate validation error

OGG (not connected) 2> exit

This got me thinking and started to ask some questions internally. Which lead me to a new envionrment parameter. This enviornment variable is OGG_CLIENT_TLS_CAPATH. The OGG_CLIENT_TLS_CAPATH variable is used to specify the root certificate athority needed to login to the ServiceManager/Deployment that has been secured using the certificate … in my case, my Self-Signed Certs.

After setting the enviornment variable OGG_CLIENT_TLS_CAPATH, I can now login to the AdminClient as expected.

[oracle@ogg19c scripts]$ export OGG_CLIENT_TLS_CAPATH=/home/oracle/wallet/Root_CA.pem
[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201


Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.


Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.


OGG (not connected) 1> connect https://ogg19c:16000 deployment NewYork as oggadmin password ********


OGG (https://ogg19c:16000 NewYork) 2>

 

I found this quite helpful.

Enjoy!!!!

Categories: DBA Blogs

Simple way to get rid of OGG-01525 for AdminClient

DBASolved - Wed, 2019-07-24 22:22

While installing Oracle GoldenGate 19c tonight, I was wanting to work from the command line and play around with AdminClient. For those who are not up to speed, AdminClient is the replacement for GGSCI when using Microservices.

AdminClient in Microservices provide the same functionalty as GGSCI, but it is a thin, lightweight tool that can be installed on a remote linux box or windows desktop/laptop. It allows you to make simple GGSCI commands and convert them into RESTP API calls on the backend. All the while, providing the same command line interface as GGSCI provided.

It is great that there is still a command line option for Oracle GoldenGate within the Microservices Architecture, however, when you start it you get presented with a Warning. The Warning is an OGG-01525 message that states there is no place to produce a trace file for the session.

WARNING OGG-01525 Failed to open trace output file, ‘/opt/app/oracle/product/19.1.0/oggcore_1/var/log/adminclient.log’, error 2 (No such file or directory).

So how do you fix this issue?

Note: This is not a bug! AdminClient was designed this way.

In order to fix this issue and get rid of the warning, you need to set a new enviornment variable. Since Oracle GoldenGate Microservices has been out for a bit over 2 year, I guess the environment variable isn’t that new. Any ways, the environment variable that needs to be set is OGG_VAR_HOME.

export OGG_VAR_HOME=/tmp

The OGG_VAR_HOME variable is used to tell AdminClient where to keep the adminclient.log file. In my example above, I’m using the temp ( /tmp ) directory for the log file.

Now, there is only one problem with the OGG_VAR_HOME environment variable. This environment variable along with OGG_ETC_HOME have to be set per Deployment Home environment. Meaning, when you have more than one Deployment Home, these environment variables are specific to that deployment.

The question that is begging to asked is -> How do I assign an environment variable for AdminClient and Deployments at the same time?

To solve this problem, I just wrote a simple shell wrapper and placed it in my home directory. The script looks like this:

[oracle@ogg19c scripts]$ cat adminclient.sh
#/bin/bash

export OGG_VAR_HOME=/tmp

${OGG_HOME}/bin/adminclient

Now, I can run the shell script and execute the AdminClient without getting the OGG-01525 warning.

[oracle@ogg19c scripts]$ sh ./adminclient.sh
Oracle GoldenGate Administration Client for Oracle
Version 19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

Linux, x64, 64bit (optimized) on May 25 2019 02:00:23
Operating system character set identified as US-ASCII.

 

OGG (not connected) 1>

For everyone is likes the Oracle GoldenGate command line, you still have access there!

Enjoy!!!

Categories: DBA Blogs

Deploying SQL Server 2019 container on RHEL 8 with podman

Yann Neuhaus - Wed, 2019-07-24 11:12

Having a fresh install of RHEL8 on my lab environment, I was curious to take a look at new containerization stuff from Red Hat in the context of SQL Server 2019. Good chances are the future version of SQL Server should be available and supported on with the latest version of Red Hat but for now this blog post is purely experimental. This time I wanted to share with you some thoughts about the new Podman command.

First of all, we should be aware that since RHEL8 Red Hat decided to replace docker with CRI-O/podman in order to provide a “daemonless” container world and especially for Kubernetes. By 2016, Kubernetes project introduced the Container Runtime Interface (CRI).  Basically, with CRI, Kubernetes can be container runtime-agnostic. CRI-O that is an open source project initiated by Red Hat the same year that gives the ability to run containers directly from Kubernetes without any unnecessary code or tooling as long as the container remains OCI-compliant. Because Docker is not implemented anymore (and officially not supported) by Red Hat since RHEL8, we need a client tool for working with containers and this is where Podman steps in. To cut the story short, Podman implements almost all the Docker CLI commands and more.

So, let’s have an overview of Podman commands through the installation of a SQL Server 2019 based container. It is worth noting that Podman is not intended to be used in the context of a “standalone” container environnement and should be used with an container orchestrator like K8s or an orchestration platform like OpenShift.  That said,  let’s first create a host directory to persist the SQL Server database files.

$ sudo mkdir -p  /var/mssql/data
$ sudo chmod 755 -R /var/mssql/data

 

Then let’s download the SQL Server 2019 RHEL image. We will use the following Podman command:

$ sudo podman pull mcr.microsoft.com/mssql/rhel/server:2019-CTP3.1
Trying to pull mcr.microsoft.com/mssql/rhel/server:2019-CTP3.1...Getting image source signatures
Copying blob 079e961eee89: 70.54 MiB / 70.54 MiB [========================] 1m3s
Copying blob 1b493d38a6d3: 1.20 KiB / 1.20 KiB [==========================] 1m3s
Copying blob 89e62e5b4261: 333.24 MiB / 333.24 MiB [======================] 1m3s
Copying blob d39017c722a8: 174.82 MiB / 174.82 MiB [======================] 1m3s
Copying config dbba412361d7: 4.98 KiB / 4.98 KiB [==========================] 0s
Writing manifest to image destination
Storing signatures
dbba412361d7ca4fa426387e1d6fc3ec85e37d630bfe70e6599b5116d392394d

 

Note that if you’re already comfortable with the Docker commands, the shift to Podman will be easy thanks to the similarity between the both tools. To get information of the new fresh image, we will use the following Podman command:

$ sudo podman images
REPOSITORY                            TAG           IMAGE ID       CREATED       SIZE
mcr.microsoft.com/mssql/rhel/server   2019-CTP3.1   dbba412361d7   3 weeks ago   1.79 GB
$ sudo podman inspect dbba
…
"GraphDriver": {
            "Name": "overlay",
            "Data": {
                "LowerDir": "/var/lib/containers/storage/overlay/b2769e971a1bdb62f1c0fd9dcc0e9fe727dca83f52812abd34173b49ae55e37d/diff:/var/lib/containers/storage/overlay/4b0cbf0d9d0ff230916734a790f47ab2adba69db44a79c8eac4c814ff4183c6d/diff:/var/lib/containers/storage/overlay/9197342671da8b555f200e47df101da5b7e38f6d9573b10bd3295ca9e5c0ae28/diff",
                "MergedDir": "/var/lib/containers/storage/overlay/b372c0d6ff718d2d182af4639870dc6e4247f684d81a8b2dc2649f8517b9fc53/merged",
                "UpperDir": "/var/lib/containers/storage/overlay/b372c0d6ff718d2d182af4639870dc6e4247f684d81a8b2dc2649f8517b9fc53/diff",
                "WorkDir": "/var/lib/containers/storage/overlay/b372c0d6ff718d2d182af4639870dc6e4247f684d81a8b2dc2649f8517b9fc53/work"
            }
        },
…

 

As show above, Podman uses the CRI-O back-end store directory with the /var/lib/containers path, instead of using the Docker default storage location (/var/lib/docker).

Go ahead and let’s take a look at the Podman info command:

$ podman info
…
OCIRuntime:
    package: runc-1.0.0-54.rc5.dev.git2abd837.module+el8+2769+577ad176.x86_64
    path: /usr/bin/runc
    version: 'runc version spec: 1.0.0'
…
store:
  ConfigFile: /home/clustadmin/.config/containers/storage.conf
  ContainerStore:
    number: 0
  GraphDriverName: overlay

 

The same kind of information is provided by the Docker info command including the runtime and the graph driver name that is overlay in my case. Generally speaking, creating and getting information of a container with Podman is pretty similar to what we may use with the usual Docker commands. Here  for instance the command to spin up a SQL Server container based on the RHEL image:

$ sudo podman run -d -e 'ACCEPT_EULA=Y' -e \
> 'MSSQL_SA_PASSWORD=Password1'  \
> --name 'sqltest' \
> -p 1460:1433 \
> -v /var/mssql/data:/var/opt/mssql/data:Z \
> mcr.microsoft.com/mssql/rhel/server:2019-CTP3.1
4f5128d36e44b1f55d23e38cbf8819041f84592008d0ebb2b24ff59065314aa4
$ sudo podman ps
CONTAINER ID  IMAGE                                            COMMAND               CREATED        STATUS            PORTS                   NAMES
4f5128d36e44  mcr.microsoft.com/mssql/rhel/server:2019-CTP3.1  /opt/mssql/bin/sq...  4 seconds ago  Up 3 seconds ago  0.0.0.0:1460->1433/tcp  sqltest

 

Here comes the interesting part. Looking at the pstree output we may notice that there is not dependencies with any (docker) daemon with CRI-O implementation. Usually with the Docker implementation we retrieve the containerd daemon and the related shim for the process within the tree. 

$ pstree
systemd─┬─NetworkManager───2*[{NetworkManager}]
        ├─…
        ├─conmon─┬─sqlservr─┬─sqlservr───138*[{sqlservr}]
        │        │          └─{sqlservr}

 

By using the runc command below, we may notice the MSSQL container (identified by the ID here) is actually running through CRI-O and runc runtime.

$ sudo runc list -q
4f5128d36e44b1f55d23e38cbf8819041f84592008d0ebb2b24ff59065314aa4

 

Let’s have a look at the existing namespace. The 9449 PID corresponds to the SQL Server process running in isolation mode through Linux namespaces.

$ sudo lsns 
…
4026532116 net         2  9449 root   /opt/mssql/bin/sqlservr
4026532187 mnt         2  9449 root   /opt/mssql/bin/sqlservr
4026532188 uts         2  9449 root   /opt/mssql/bin/sqlservr
4026532189 ipc         2  9449 root   /opt/mssql/bin/sqlservr
4026532190 pid         2  9449 root   /opt/mssql/bin/sqlservr

$ ps aux | grep sqlservr
root       9449  0.1  0.6 152072 25336 ?        Ssl  05:08   0:00 /opt/mssql/bin/sqlservr
root       9465  5.9 18.9 9012096 724648 ?      Sl   05:08   0:20 /opt/mssql/bin/sqlservr
clustad+   9712  0.0  0.0  12112  1064 pts/0    S+   05:14   0:00 grep --color=auto sqlservr

 

We can double check that the process belongs to the SQL Server container by using the nsenter command:

sudo nsenter -t 17182 --mount --uts --ipc --net --pid sh
sh-4.2# ps aux
USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root          1  0.0  0.7 152076 28044 ?        Ssl  Jul23   0:00 /opt/mssql/bin/sqlservr
root          9  2.2 19.7 9034224 754820 ?      Sl   Jul23   0:28 /opt/mssql/bin/sqlservr
root        319  0.0  0.0  13908  3400 ?        S    00:01   0:00 sh
root        326  0.0  0.1  53832  3900 ?        R+   00:02   0:00 ps aux

 

Well, we used different Podman commands to spin up a container that meets the OCI specification like Docker. For a sake of curiosity, let’s build a custom image from a Dockerfile. In fact, this is a custom image we developed for customers to meet our best practices requirements. 

$ ls -l
total 40
drwxrwxr-x. 2 clustadmin clustadmin   70 Jul 24 02:06 BestPractices
drwxrwxr-x. 2 clustadmin clustadmin   80 Jul 24 02:06 DMK
-rw-rw-r--. 1 clustadmin clustadmin  614 Jul 24 02:06 docker-compose.yml
-rw-rw-r--. 1 clustadmin clustadmin 2509 Jul 24 02:06 Dockerfile
-rw-rw-r--. 1 clustadmin clustadmin 3723 Jul 24 02:06 entrypoint.sh
-rw-rw-r--. 1 clustadmin clustadmin 1364 Jul 24 02:06 example.docker-swarm-compose.yml
-rw-rw-r--. 1 clustadmin clustadmin  504 Jul 24 02:06 healthcheck.sh
-rw-rw-r--. 1 clustadmin clustadmin   86 Jul 24 02:06 mssql.conf
-rw-rw-r--. 1 clustadmin clustadmin 4497 Jul 24 02:06 postconfig.sh
-rw-rw-r--. 1 clustadmin clustadmin 2528 Jul 24 02:06 Readme.md
drwxrwxr-x. 2 clustadmin clustadmin   92 Jul 24 02:06 scripts

 

To build an image from a Dockerfile the corresponding Podman command is as follow:

$ sudo podman build -t dbi_mssql_linux:2019-CTP3.1 .
…
--> 5db120fba51f3adc7482ec7a9fed5cc4194f13e97b855d9439a1386096797c39
STEP 65: FROM 5db120fba51f3adc7482ec7a9fed5cc4194f13e97b855d9439a1386096797c39
STEP 66: EXPOSE ${MSSQL_TCP_PORT}
--> 8b5e8234af47adb26f80d64abe46715637bd48290b4a6d7711ddf55c393cd5a8
STEP 67: FROM 8b5e8234af47adb26f80d64abe46715637bd48290b4a6d7711ddf55c393cd5a8
STEP 68: ENTRYPOINT ["/usr/local/bin/entrypoint.sh"]
--> 11045806b8af7cf2f67e5a279692e6c9e25212105bcd104ed17b235cdaea97fe
STEP 69: FROM 11045806b8af7cf2f67e5a279692e6c9e25212105bcd104ed17b235cdaea97fe
STEP 70: CMD ["tail -f /dev/null"]
--> bcb8c26d503010eb3e5d72da4b8065aa76aff5d35fac4d7958324ac3d97d5489
STEP 71: FROM bcb8c26d503010eb3e5d72da4b8065aa76aff5d35fac4d7958324ac3d97d5489
STEP 72: HEALTHCHECK --interval=15s CMD [ "/usr/local/bin/healthcheck.sh" ]
--> e7eedf0576f73c95b19adf51c49459b00449da497cf7ae417e597dd39a9e4c8f
STEP 73: COMMIT dbi_mssql_linux:2019-CTP3.1

 

The image built is now available in the local repository:

$ sudo podman images
REPOSITORY                            TAG           IMAGE ID       CREATED         SIZE
localhost/dbi_mssql_linux             2019-CTP3.1   e7eedf0576f7   2 minutes ago   1.79 GB
mcr.microsoft.com/mssql/rhel/server   2019-CTP3.1   dbba412361d7   3 weeks ago     1.79 GB

 

The next step will consist in spinning up a SQL Server container based on this new image. Note that I used a custom parameter DMK=Y to drive the creation of the DMK maintenance tool in our case which including the deployment of a custom dbi_tools database ans related objects that carry out the database maintenance.

$ sudo podman run -d -e 'ACCEPT_EULA=Y' \
> -e 'MSSQL_SA_PASSWORD=Password1' -e 'DMK=Y'  \
> --name 'sqltest2' \
> -p 1470:1433 \
> localhost/dbi_mssql_linux:2019-CTP3.1
d057e0ca41f08a948de4206e9aa07b53450c2830590f2429e50458681d230f6b

 

Let’s check if the dbi_tools has been created during the container runtime phase:

$ sudo podman exec -ti d057 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Password1 -Q"SELECT name from sys.databases"
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
dbi_tools

 

Finally, to make the transition with a future blog post, the Podman tool comes with extra commands (under development) that is not available with Docker CLI. The following example generates a YAML deployment file and the corresponding service from an existing container. Please note however that containers with volumes are not supported yet.

The container definition is a follows:

$ sudo podman run -d -e 'ACCEPT_EULA=Y' -e \
'MSSQL_SA_PASSWORD=Password1'  \
--name 'sqltestwithnovolumes' \
-p 1480:1433 \
mcr.microsoft.com/mssql/rhel/server:2019-CTP3.1
7e99581eaec4c91d7c13af4525bfb3805d5b56e675fdb53d0061c231294cd442

 

And we get the corresponding YAML file generated by the Podman command:

$ sudo podman generate kube -s 7e99
# Generation of Kubernetes YAML is still under development!
#
# Save the output of this file and use kubectl create -f to import
# it into Kubernetes.
#
# Created with podman-1.0.2-dev
apiVersion: v1
kind: Pod
metadata:
  creationTimestamp: 2019-07-24T03:52:18Z
  labels:
    app: sqltestwithnovolumes
  name: sqltestwithnovolumes
spec:
  containers:
  - command:
    - /opt/mssql/bin/sqlservr
    env:
    - name: PATH
      value: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
    - name: TERM
      value: xterm
    - name: HOSTNAME
    - name: container
      value: oci
    - name: ACCEPT_EULA
      value: "Y"
    - name: MSSQL_SA_PASSWORD
      value: Password1
    image: mcr.microsoft.com/mssql/rhel/server:2019-CTP3.1
    name: sqltestwithnovolumes
    ports:
    - containerPort: 1433
      hostPort: 1480
      protocol: TCP
    resources: {}
    securityContext:
      allowPrivilegeEscalation: true
      capabilities: {}
      privileged: false
      readOnlyRootFilesystem: false
    workingDir: /
status: {}
---
apiVersion: v1
kind: Service
metadata:
  creationTimestamp: 2019-07-24T03:52:18Z
  labels:
    app: sqltestwithnovolumes
  name: sqltestwithnovolumes
spec:
  ports:
  - name: "1433"
    nodePort: 30309
    port: 1433
    protocol: TCP
    targetPort: 0
  selector:
    app: sqltestwithnovolumes
  type: NodePort
status:
  loadBalancer: {}

 

By default the service type NodePort has been created by the command. This latest command needs further testing for sure!

See you

Cet article Deploying SQL Server 2019 container on RHEL 8 with podman est apparu en premier sur Blog dbi services.

Rsync DBFS To ACFS For GoldenGate Trail Migration

Michael Dinh - Wed, 2019-07-24 09:25

Planning to move GoldenGate trail files from DBFS to ACFS.

This is pre-work before actual migration to stress IO for ACFS.

Learned some cron along the way.

# Run every 2 hours at even hours
0 */2 * * * /home/oracle/working/dinh/acfs_ggdata02_rsync.sh > /tmp/rsync_acfs_ggdata_to_ggdata02.log 2>&1

# Run every 2 hours at odd hours
0 1-23/2 * * * /home/oracle/working/dinh/acfs_ggdata02_rsync.sh > /tmp/rsync_acfs_ggdata_to_ggdata02.log 2>&1

Syntax and ouptput.

+ /bin/rsync -vrpogt --delete-after /DBFS/ggdata/ /ACFS/ggdata
building file list ... done

dirchk/E_SOURCE.cpe
dirchk/P_TARGET.cpe

dirdat/
dirdat/aa000307647
dirdat/aa000307648
.....
dirdat/aa000307726
dirdat/aa000307727

deleting dirdat/aa000306741
deleting dirdat/aa000306740
.....
deleting dirdat/aa000306662
deleting dirdat/aa000306661

sent 16,205,328,959 bytes  received 1,743 bytes  140,305,893.52 bytes/sec
total size is 203,021,110,174  speedup is 12.53

real	1m56.671s
user	1m24.643s
sys	0m45.875s

+ '[' 0 '!=' 0 ']'

+ /bin/diff -rq /DBFS/ggdata /ACFS/ggdata

Files /DBFS/ggdata/dirchk/E_SOURCE.cpe and /ACFS/ggdata/dirchk/E_SOURCE.cpe differ
Files /DBFS/ggdata/dirchk/P_TARGET.cpe and /ACFS/ggdata/dirchk/P_TARGET.cpe differ

Only in /ACFS/ggdata/dirdat: aa000306742
Only in /ACFS/ggdata/dirdat: aa000306743
Only in /ACFS/ggdata/dirdat: aa000306744
Only in /ACFS/ggdata/dirdat: aa000306745

Only in /DBFS/ggdata/dirdat: aa000307728
Only in /DBFS/ggdata/dirdat: aa000307729

real	69m15.207s
user	2m9.242s
sys	17m3.846s

+ ls /DBFS/ggdata/dirdat/
+ wc -l
975

+ ls -alrt /DBFS/ggdata/dirdat/
+ head
total 190631492
drwxrwxrwx 24 root    root             0 Feb  9  2018 ..
-rw-r-----  1 ggsuser oinstall 199999285 Mar  8  2018 .fuse_hidden001a3c47000001c5
-rw-r-----  1 ggsuser oinstall 199999896 May 23 00:23 .fuse_hidden000002b500000001
-rw-r-----  1 ggsuser oinstall 199999934 Jul 23 06:11 aa000306798
-rw-r-----  1 ggsuser oinstall 199999194 Jul 23 06:13 aa000306799
-rw-r-----  1 ggsuser oinstall 199999387 Jul 23 06:14 aa000306800
-rw-r-----  1 ggsuser oinstall 199999122 Jul 23 06:16 aa000306801
-rw-r-----  1 ggsuser oinstall 199999172 Jul 23 06:19 aa000306802
-rw-r-----  1 ggsuser oinstall 199999288 Jul 23 06:19 aa000306803

+ ls -alrt /DBFS/ggdata/dirdat/
+ tail
-rw-r-----  1 ggsuser oinstall 199999671 Jul 24 07:59 aa000307764
-rw-r-----  1 ggsuser oinstall 199999645 Jul 24 08:01 aa000307765
-rw-r-----  1 ggsuser oinstall 199998829 Jul 24 08:02 aa000307766
-rw-r-----  1 ggsuser oinstall 199998895 Jul 24 08:04 aa000307767
-rw-r-----  1 ggsuser oinstall 199999655 Jul 24 08:05 aa000307768
-rw-r-----  1 ggsuser oinstall 199999930 Jul 24 08:07 aa000307769
-rw-r-----  1 ggsuser oinstall 199999761 Jul 24 08:09 aa000307770
-rw-r-----  1 ggsuser oinstall 199999421 Jul 24 08:11 aa000307771
-rw-r-----  1 ggsuser oinstall   7109055 Jul 24 08:11 aa000307772

+ ls /ACFS/ggdata/dirdat/
+ wc -l
986

+ ls -alrt /ACFS/ggdata/dirdat/
+ head
total 194779104
drwxrwxrwx 24 root    root          8192 Feb  9  2018 ..
-rw-r-----  1 ggsuser oinstall 199999285 Mar  8  2018 .fuse_hidden001a3c47000001c5
-rw-r-----  1 ggsuser oinstall 199999896 May 23 00:23 .fuse_hidden000002b500000001
-rw-r-----  1 ggsuser oinstall 199998453 Jul 23 04:55 aa000306742
-rw-r-----  1 ggsuser oinstall 199999657 Jul 23 04:56 aa000306743
-rw-r-----  1 ggsuser oinstall 199999227 Jul 23 04:57 aa000306744
-rw-r-----  1 ggsuser oinstall 199999389 Jul 23 04:59 aa000306745
-rw-r-----  1 ggsuser oinstall 199999392 Jul 23 05:00 aa000306746
-rw-r-----  1 ggsuser oinstall 199999116 Jul 23 05:01 aa000306747

+ ls -alrt /ACFS/ggdata/dirdat/
+ tail
-rw-r-----  1 ggsuser oinstall 199999876 Jul 24 06:48 aa000307719
-rw-r-----  1 ggsuser oinstall 199999751 Jul 24 06:50 aa000307720
-rw-r-----  1 ggsuser oinstall 199999918 Jul 24 06:51 aa000307721
-rw-r-----  1 ggsuser oinstall 199999404 Jul 24 06:52 aa000307722
-rw-r-----  1 ggsuser oinstall 199999964 Jul 24 06:54 aa000307723
-rw-r-----  1 ggsuser oinstall 199999384 Jul 24 06:56 aa000307724
-rw-r-----  1 ggsuser oinstall 199999283 Jul 24 06:57 aa000307725
-rw-r-----  1 ggsuser oinstall 199998033 Jul 24 06:59 aa000307726
-rw-r-----  1 ggsuser oinstall 199999199 Jul 24 07:00 aa000307727

Oracle EBS 12.2 - ADOP ad_zd_prep.create_patch_service exact fetch returns more than requested number of rows

Senthil Rajendran - Wed, 2019-07-24 02:29
Oracle EBS 12.2 - ADOP ad_zd_prep.create_patch_service exact fetch returns more than requested number of rows

Please note , if you get ADOP issues on PROD please read the logs and understand the problem before executing any commands.

SQL> exec ad_zd_prep.create_patch_service;
BEGIN ad_zd_prep.create_patch_service; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.AD_ZD_PREP", line 342
ORA-06512: at "APPS.AD_ZD_PREP", line 378
ORA-06512: at line 1


A Quick fix without autoconfig


  1. SQL> create table fnd_oam_bkup1 as select * from fnd_oam_context_files;
  2. SQL> truncate fnd_oam_context_files
  3. move DB context file MT
  4. Load the Database Context File
    • $ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=/tmp/test_dbserver.xml
  5. Load the Run File System Context File
    • $ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=/u01/test/appl/fs1/inst/apps/test_mtserver/appl/admin/test_mtserver.xml
  6. Load the Patch File System Context File
    • $ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=/u01/test/appl/fs1/inst/apps/test_mtserver/appl/admin/test_mtserver.xml
Next Maintenance run Autoconfig on Database and Middletier

SQL> exec ad_zd_prep.create_patch_service; -- this will succeed

or if you are running ADOP prepare will succeed.


Oracle EBS 12.2 - ADOP ad_zd_prep.create_patch_service exceptions

Senthil Rajendran - Wed, 2019-07-24 02:23

Oracle EBS 12.2 - ADOP ad_zd_prep.create_patch_service exceptions

Please note , if you get ADOP issues on PROD please read the logs and understand the problem before executing any commands.



There are cases where you might have to create a patch service manually. So usually after cloning or fresh install , add node , mass patching , upgrade this crazy error will pop up.

SQL> exec ad_zd_prep.create_patch_service;
BEGIN ad_zd_prep.create_patch_service; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.AD_ZD_PREP", line 342
ORA-06512: at "APPS.AD_ZD_PREP", line 378
ORA-06512: at line 1

Check the state of the package

SQL> select owner, object_name,status,object_type from dba_objects where object_name like upper ('AD_ZD_PREP');

OWNER                                    OBJECT_NAME                              STATUS  OBJECT_TYPE
---------------------------------------- ---------------------------------------- ------- -----------------------
APPS                                     AD_ZD_PREP                               VALID   PACKAGE
APPS                                     AD_ZD_PREP                               INVALID PACKAGE BODY


SQL> alter package apps.ad_zd_prep compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY APPS.AD_ZD_PREP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
463/3    PL/SQL: SQL Statement ignored
463/19   PL/SQL: ORA-00942: table or view does not exist
467/5    PL/SQL: SQL Statement ignored
467/21   PL/SQL: ORA-00942: table or view does not exist
501/5    PL/SQL: SQL Statement ignored
501/21   PL/SQL: ORA-00942: table or view does not exist


Fix :  follow this note
12.2 E-Business Suite DBA Upgrade Script ADZDEXRPT.sql Fails On Invalid Object 'AD_ZD_PREP' Or Patch 13543062 Fails With Error: ORA-06508: PL/SQL: could not find APPS.AD_ZD_PREPAD_ZD_PREP Due To Missing Package 'xdb_migrateschema' (Doc ID 2066607.1)

1. Confirm if the package 'xdb_migrateschema' exists using the following select statement:

select owner, object_name from dba_objects where object_name like upper ('xdb_migrateschema');

If it does NOT exist, then use the following scripts to create it and confirm results again using the above query:

conn / as sysdba

@?/rdbms/admin/dbmsxdbschmig.sql

@?/rdbms/admin/prvtxdbschmig.plb

2. Run adgrants.sql.

3. Recompile the package AD_ZD_PREP and ensure it compiles successfully.

4. Continue with the upgrade and confirm online patching is successfully enabled.


Move the adgrants.sql from MT to DB before running it.



exec ad_zd_prep.create_patch_service  -- this should succeed.


Oracle EBS 12.2 - ADOP patching on a downtime mode with [ERROR] Patch service is not exist or running

Senthil Rajendran - Wed, 2019-07-24 02:12
ADOP patching on a downtime mode with [ERROR]     Patch service  is not exist or running

Please note , if you get ADOP issues on PROD please read the logs and understand the problem before executing any commands.

When applying adop patches on a downtime mode you might see an error that the patch service is not existing or running. Please note NOT all patches are meant to be applied to a downtime mode so read the readme of the patch to understand the patching process. Exception cases breaking the rule book patches can be applied to a development environment but you have to know how to deal with post adop failure cases.

In this case it is clear that the patch service is not existing.

adop phase=apply patches=1234567 apply_mode=downtime

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

Validating credentials.

Initializing.
   Run Edition context  : /p01/ebs/appl/fs1/inst/apps/ebs_app01/appl/admin/ebs_app01.xml
   Patch edition context: /p01/ebs/appl/fs2/inst/apps/ebs_app01/appl/admin/ebs_app01.xml
   Patch file system free space: 77.95 GB

Validating system setup.
   Node registry is valid.
   [ERROR]     Patch service  is not exist or running
   [WARNING]   ETCC: The following required database fixes have not been applied to node app01:
bla bla bla
Encountered the above errors when performing database validations.
   Resolve the above errors and restart adop.

Fix : connect as APPS user and execute
SQL>  exec ad_zd_prep.create_patch_service;

Run ADOP now and it should proceed further.



Customizing DML in an APEX Interactive Grid

The Anti-Kyte - Tue, 2019-07-23 16:06

It should have been quite a relaxing Cricket World Cup final. After all, it was England v New Zealand. I was guaranteed to be on the winning side.
After several hours of nerve-shredding tension had failed to separate the teams England were awarded the trophy on the basis of dumb luck hitting more boundaries. The result was born with stoicism by the Black Caps, whose philosophy would, in other countries, be known as “Elite Niceness”. By a cruel twist of fate, Ben Stokes – England’s star all-rounder and Man of the Match – was actually born in Christchurch.
Oracle APEX has it’s own star all-rounder in the shape of the Editable Interactive Grid ( see what I did there ?)
As well as presenting information in the same way as an Interactive Report, it allows users to perform DML operations on the records it displays – provided it’s based on a single table.
What we’re going to look at here is how to base an Interactive Grid (IG) on a Query rather than a table whilst retaining the ability to perform DML operations on the displayed records. To achieve this, we’ll be customizing the PL/SQL that is executed when a DML operation is invoked in the IG.

The Application

For what follows, I’ll be using APEX 18.2 running against an Oracle 18cXE database.

We have two related tables which hold information about Men’s 50-over Cricket World Cup Finals :

The tables were created as follows :

create table teams(
    cid varchar2(3) primary key,
    team_name varchar2(100) not null)
/

create table finals(
    tournament_year number(4) primary key,
    date_played date,
    venue varchar2(100),
    winning_tcid varchar2(3) references teams(cid),
    losing_tcid varchar2(3) references teams(cid),
    winning_margin varchar2(100))
/    

… and have been populated with some data. The TEAMS table first…

insert into teams( cid, team_name)
values('AUS', 'AUSTRALIA');

insert into teams( cid, team_name)
values('ENG', 'ENGLAND');

insert into teams( cid, team_name)
values('RSA', 'SOUTH AFRICA');

insert into teams( cid, team_name)
values('WI', 'WEST INDIES');

insert into teams( cid, team_name)
values('IND', 'INDIA');

insert into teams( cid, team_name)
values('NZL', 'NEW ZEALAND');

insert into teams( cid, team_name)
values('PAK', 'PAKISTAN');

insert into teams( cid, team_name)
values('SL', 'SRI LANKA');

insert into teams( cid, team_name)
values('ZIM', 'ZIMBABWE');

insert into teams( cid, team_name)
values('BAN', 'BANGLADESH');

insert into teams( cid, team_name)
values('AFG', 'AFGHANISTAN');

insert into teams( cid, team_name)
values('IRL', 'IRELAND');

commit;

…and then FINALS…

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1975, to_date('21-JUN-1975', 'DD-MON-YYYY'), 'LORDS', 'WI', 'AUS', '17 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1979, to_date('23-JUN-1979' , 'DD-MON-YYYY'), 'LORDS', 'WI', 'ENG', '92 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1983, to_date('25-JUN-1983' , 'DD-MON-YYYY'), 'LORDS', 'IND', 'WI', '43 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1987, to_date('08-NOV-1987' , 'DD-MON-YYYY'), 'EDEN GARDENS', 'AUS', 'ENG', '7 runs');

insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1992, to_date('25-MAR-1992' , 'DD-MON-YYYY'), null, 'PAK', 'ENG', '22 runs');

-- deliberate mistake to be corrected later
insert into finals(tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
values(1997, to_date('17-MAR-1996' , 'DD-MON-YYYY'), 'QADDAFI STADIUM', 'SL', 'AUS', '8 wickets');

commit;

The data that we wish to present to application users can be retrieved with the following query :

select f.tournament_year, f.date_played, 
    initcap(f.venue) as venue,
    initcap(win.team_name) as winners,
    initcap(ru.team_name) as runners_up,
    f.winning_margin
from finals f
inner join teams win on f.winning_tcid = win.cid
inner join teams ru on f.losing_tcid = ru.cid
/

There are a couple of issues with the data as it stands so we want users to be able to edit the existing application records and add new ones.
As we’re using APEX, it would be good if we could use an Editable Interactive Grid as this would mean only needing to write a single page to handle all of these actions.
Of course, we could simply create a view using this query and then knock-up an Instead Of trigger to handle any DML. Alternatively…

Creating the Interactive Grid

The first step is to create a Region…

…and define it as an IG…

…using the above query as the Source SQL Query

When we run this (after saving our changes), we can see that the ROW_SELECTOR and ROW_ACTION widgets are missing :

Making the IG Editable

In order to persuade APEX to add these widgets, we need to make the IG Editable. We can do this by going to the Region’s Attributes and setting the Edit Enabled property to Yes

Among other things, this automatically creates a Save Interactive Grid Data process :

However, if we attempt to run the page now (after saving these changes), we’ll hit an error…

…so we need to select a Primary Key.
TOURNAMENT_YEAR will fulfil this purpose in our IG, so we just need to adjust the properties of the column :

When we save the change and run the page we can see that the ROW_SELECTOR and ROW_ACTION are now present :

The IG is not based on a table or view, remember, so we still need to tell APEX what to do when any DML actions are initiated by the user.

Customising the Save Interactive Grid Data process

Returning to the Processing Tab in the Page Designer we need to change the Type of this process to PL/SQL Code :

In the Source PL/SQL Code box, we need to enter a PL/SQL block which will be run whenever the Page processes a DML action.
In order to tell what specific DML action a row is subject to, we can look at the value of the built-in $APEXROW_STATUS variable.
The possible values are :

  • C – for Create
  • U – Update
  • D – Delete

Therefore, one approach for our PL/SQL block would be simply to include the actual DML statements we want to execute in-line like this :

declare 
    l_win_tcid finals.winning_tcid%type;
    l_ru_tcid finals.losing_tcid%type;
    
    cursor c_team_cid( i_name teams.team_name%type)
    is
        select cid
        from teams
        where team_name = upper(i_name);
begin
    -- reset the variables for each pass through this process
    l_win_tcid := null;
    l_ru_tcid := null;
    
    if :APEX$ROW_STATUS = 'D' then
        -- DELETE the record
        delete from finals
        where tournament_year = :TOURNAMENT_YEAR;
        
    else

        -- As we're either doing an UPDATE or an INSERT, we need to find the 
        -- CID value for each of the team names ( if specified)

        if :WINNERS is not null then
            open c_team_cid(:WINNERS);
            fetch c_team_cid into l_win_tcid;
            close c_team_cid;
        end if;

        if :RUNNERS_UP is not null then
            open c_team_cid(:RUNNERS_UP);
            fetch c_team_cid into l_ru_tcid;
            close c_team_cid;
        end if;
        
        if :APEX$ROW_STATUS = 'U' then
            -- UPDATE the record
            -- Note that, although DATE_PLAYED is a DATE field, the bind variable
            -- contains a string so we need to handle the conversion to a date here
            update finals
            set date_played = nvl(to_date(:DATE_PLAYED, sys_context('userenv', 'nls_date_format')), date_played),
                venue = nvl(:VENUE, venue),
                winning_tcid = nvl(l_win_tcid, winning_tcid),
                losing_tcid = nvl(l_ru_tcid, losing_tcid),
                winning_margin = nvl(:WINNING_MARGIN, winning_margin)
            where tournament_year = :TOURNAMENT_YEAR;
        
        elsif :APEX$ROW_STATUS = 'C' then
            -- CREATE (INSERT) as new record
            -- We need to return the Primary Key of the new record as APEX will
            -- use it to refresh the IG display and show the newly created row
            insert into finals( tournament_year, date_played, venue, 
                winning_tcid, losing_tcid, winning_margin)
            values( :TOURNAMENT_YEAR, to_date(:DATE_PLAYED, sys_context('userenv', 'nls_date_format')), :VENUE,
                l_win_tcid, l_ru_tcid, :WINNING_MARGIN)
            returning tournament_year into :TOURNAMENT_YEAR;
        end if;
    end if;
end;

The main points to note are :

  • The possible values of APEX$ROW_STATUS
  • the bind variables for the IG column values return a string, hence the explicit date conversion of :DATE_PLAYED
  • when the insert code generates a new primary key value (e.g. an ID taken from a sequence), APEX needs to know what it is so that it can display the new record once processing is completed.

On that last point, I believe that we may not need to return the PK value in this case because we’re already providing it explicitly in the UI. I’ve left it here as an illustration that this may be the case in other circumstances.

Anyhow, let’s give this a test.
First of all, we’re going to update the 1992 record with the venue :

The record for 1997 is wrong. The Tournament actually took place in 1996, so we’ll create a corrected record and delete the incorrect one :

In both cases, we get a success message :

…and if we check in the database, we can see that the DML has worked as expected :

NOTE : for any cricketing pedants reading – yes, I know that Sri Lanka won by 7 wickets, not 8. I’ll be correcting this when I “notice it” in a few paragraphs time.

At this point you may be less than thrilled at the prospect of having to maintain large chunks of PL/SQL in your APEX application.
Luckily for you, as the process code is a PL/SQL block, you can do pretty much anything you like…

Calling Stored Program Units from our APEX process

I’ve created a database package to handle DML operations on the FINALS table :

create or replace package edit_finals as

    procedure save_final( 
        i_year in finals.tournament_year%type, 
        i_date in finals.date_played%type,
        i_venue in finals.venue%type default null,
        i_winners in teams.team_name%type default null,
        i_losers in teams.team_name%type default null,
        i_margin in finals.winning_margin%type default null,
        o_year out finals.tournament_year%type);

    procedure delete_final( i_year in finals.tournament_year%type);
end edit_finals;
/

create or replace package body edit_finals as

    function get_team_cid( i_team in teams.team_name%type)
        return teams.cid%type 
    is 
        rtn_cid teams.cid%type;
    begin
        select cid 
        into rtn_cid
        from teams 
        where team_name = upper( i_team);

        return rtn_cid;
    end get_team_cid;

    procedure save_final( 
        i_year in finals.tournament_year%type, 
        i_date in finals.date_played%type,
        i_venue in finals.venue%type default null,
        i_winners in teams.team_name%type default null,
        i_losers in teams.team_name%type default null,
        i_margin in finals.winning_margin%type default null,
        o_year out finals.tournament_year%type)
    is 
        win_tcid teams.cid%type := null;
        ru_tcid teams.cid%type := null;
    begin
        if i_winners is not null then 
            win_tcid := get_team_cid( i_winners);
        end if;

        if i_losers is not null then 
            ru_tcid := get_team_cid( i_losers);
        end if;

        merge into finals 
            using dual 
            on ( tournament_year = i_year)
        when matched then update
            set date_played = nvl(i_date, date_played),
                venue = nvl(i_venue, venue),
                winning_tcid = nvl(win_tcid, winning_tcid),
                losing_tcid = nvl(ru_tcid, losing_tcid),
                winning_margin = nvl(i_margin, winning_margin)
            where tournament_year = i_year
        when not matched then 
            insert( tournament_year, date_played, venue, winning_tcid, losing_tcid, winning_margin)
            values( i_year, i_date, i_venue, win_tcid, ru_tcid, i_margin);

        o_year := i_year;
    end save_final;

    procedure delete_final( i_year in finals.tournament_year%type) is 
    begin
        delete from finals
        where tournament_year = i_year;
    end delete_final;
end edit_finals;
/

This means that the PL/SQL code for the Save IG Process is a bit more compact :

begin
    if :APEX$ROW_STATUS = 'D' then
        edit_finals.delete_final(i_year => :TOURNAMENT_YEAR);
    elsif :APEX$ROW_STATUS in ('C', 'U') then
        edit_finals.save_final( 
           i_year => :TOURNAMENT_YEAR, 
           i_date => to_date(:DATE_PLAYED, sys_context('userenv', 'nls_date_format')),
           i_venue => :VENUE,
           i_winners => :WINNERS,
           i_losers => :RUNNERS_UP,
           i_margin => :WINNING_MARGIN,
           o_year => :TOURNAMENT_YEAR);
   end if;
end;

The behaviour is the same…

…which we can confirm in the database…

The benefit of this approach is that, if you decide to migrate from APEX to another front-end technology, the Package is there in the database and does not need to change.

References

I’ve not managed to find too much else out there on this topic, hence this post.
There is a very good forum post by Patrick Wolf.
This Oracle Tips and Tricks article may be worth a read.
Finally, there’s this rather comprehensive look at Interactive Grids by John Snyders.

SQL Server 2019 availability group R/W connection redirection, routing mesh and load balancing

Yann Neuhaus - Tue, 2019-07-23 10:59

SQL Server 2019 availability group feature will provide secondary to primary replica read/write connection redirection. I wrote about it in a previous blog post here. It consists in redirecting client application connections to the primary replica regardless of the target server specified in the connections string. That’s pretty interesting in some scenarios as read scale-out or specific multi-subnet configurations where creating the traditional AG listener is not an viable option.

The new R/W connection redirection capability does the job but the one-million-dollar question here is what’s happen if one of the replicas specified in my connection string becomes suddenly unavailable? Referring  to the BOL  the connection will fail regardless the role that the replica on the target server plays but we can mitigate the issue by introducing the failover partner parameter in the connection string. As a reminder, the Failover Partner keyword in the connection string works in a database mirror setup and prevent prolonged application downtime. But from my point of view, we could go likely another way and get benefit to all the power of this new availability group feature by introducing a load balancer on the top of this topology as we could do with Docker Swarm or K8s architectures. Indeed, if we take a look more closely, this new provided mechanism by SQL Server 2019 is pretty similar to the routing mesh capabilities of container orchestrators with the same advantages and weaknesses as well. I wrote a blog post about Docker Swarm architectures where we need to implement a proxy to load balance the traffic to avoid getting stuck with the routing mesh capability where a node get unhealthy.

I just applied the same kind of configuration by using an HA Proxy (but you can use your own obviously) with my availability group topology and the behavior was basically the same. Here the intended behavior:

 

Here the configuration of my HAProxy including my 3 AG replicas (WIN20191, WIN20192, WIN20193) and a round robin algorithm at the bottom:

…
backend rserve_backend
    mode tcp
    option tcplog
    option log-health-checks
    option redispatch
    log global
    balance roundrobin
    timeout connect 10s
    timeout server 1m
    server WIN20191 192.168.40.205:1433 check
    server WIN20192 192.168.40.206:1433 check
    server WIN20193 192.168.40.207:1433 check

 

Let’s do a try with connections directly to my HAProxy that is listen on port 81 in my test scenario. Note that for this first test I will connect to the master database to force the local connection getting stick to each replica rather than going through the R/W redirection process. The goal is to check if the round-robin algorithm come into play …

$connectionString = "Server=192.168.40.14,81;uid=sa; pwd=xxxx;Integrated Security=False;Initial Catalog=master;pooling=false”

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$sqlCommandText="SELECT 'Current server : ' + @@SERVERNAME AS server_name"
$sqlCommand = New-Object system.Data.sqlclient.SqlCommand($sqlCommandText,$connection)
$sqlCommand.ExecuteScalar()

$connection.Close()
$connection.Dispose()

 

… and that’s the case as show below:

Test connexion initial server nb : 0 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 1 - 192.168.40.14,81 - Current server : WIN20192
Test connexion initial server nb : 2 - 192.168.40.14,81 - Current server : WIN20193
Test connexion initial server nb : 3 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 4 - 192.168.40.14,81 - Current server : WIN20192
Test connexion initial server nb : 5 - 192.168.40.14,81 - Current server : WIN20193

 

Let’s do a try by forcing the R/W redirection now. This time I set up the correct target database name named dummy2 for my availability group AG2019.

$connectionString = "Server=192.168.40.14,81;uid=sa; pwd=xxxx;Integrated Security=False;Initial Catalog=dummy2;pooling=false”

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$sqlCommandText="SELECT 'Current server : ' + @@SERVERNAME AS server_name"
$sqlCommand = New-Object system.Data.sqlclient.SqlCommand($sqlCommandText,$connection)
$sqlCommand.ExecuteScalar()

$connection.Close()
$connection.Dispose()

Test connexion initial server nb : 0 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 1 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 2 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 3 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 4 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 5 - 192.168.40.14,81 - Current server : WIN20191

 

This time the R/W redirection is taking effect and each established connection is redirected to my primary replica – WIN20191 this time.

Finally, let’s simulate an outage of one of my replicas, let’s say the WIN20193 replica with a turn off operation and let’s see what’s happen below:

Test connexion initial server nb : 32 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 33 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 34 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 35 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 36 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 37 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 38 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 39 - 192.168.40.14,81 - Current server : WIN20191
Test connexion initial server nb : 40 - 192.168.40.14,81 - Current server : WIN20191

 

Well, from a connection perspective nothing has changed and the HAProxy continues to load balance connections between the remaining healthy replicas. The R/W connection redirection mechanism still continue to come into play as well. A quick look at the HAProxy indicates the WIN20193 replica got unhealthy and the HAProxy has evicted this replica from the game.

[WARNING] 203/063813 (1772) : Health check for server rserve_backend/WIN20193 failed, reason: Layer4 timeout, check durati               on: 2001ms, status: 2/3 UP.
[WARNING] 203/063818 (1772) : Health check for server rserve_backend/WIN20193 failed, reason: Layer4 timeout, check durati               on: 2002ms, status: 1/3 UP.
[WARNING] 203/063822 (1772) : Health check for server rserve_backend/WIN20193 failed, reason: Layer4 timeout, check durati               on: 2001ms, status: 0/2 DOWN.
[WARNING] 203/063822 (1772) : Server rserve_backend/WIN20193 is DOWN. 2 active and 0 backup servers left. 2 sessions activ               e, 0 requeued, 0 remaining in queue.
[WARNING] 203/063848 (1772) : Health check for server rserve_backend/WIN20193 failed, reason: Layer4 connection problem, i               nfo: "No route to host", check duration: 4ms, status: 0/2 DOWN.

 

The new R/W redirection capability provided by Microsoft will extend possible scenarios with availability groups for sure. With previous versions of SQL Server, using a load balancer was limited to R/O workloads but SQL Server 2019 will probably change the game on this topic. Let’s see what’s happen in the future!

 

 

 

 

 

Cet article SQL Server 2019 availability group R/W connection redirection, routing mesh and load balancing est apparu en premier sur Blog dbi services.

Check Cluster Resources Where Target != State

Michael Dinh - Tue, 2019-07-23 10:32

Current version.

[oracle@racnode-dc2-1 patch]$ cat /etc/oratab
#Backup file is  /u01/app/12.2.0.1/grid/srvm/admin/oratab.bak.racnode-dc2-1 line added by Agent
-MGMTDB:/u01/app/12.2.0.1/grid:N
hawk1:/u01/app/oracle/12.2.0.1/db1:N
+ASM1:/u01/app/12.2.0.1/grid:N          # line added by Agent
[oracle@racnode-dc2-1 patch]$

Kill database instance process.

[oracle@racnode-dc2-1 patch]$ ps -ef|grep pmon
oracle   13542     1  0 16:09 ?        00:00:00 asm_pmon_+ASM1
oracle   27663     1  0 16:39 ?        00:00:00 ora_pmon_hawk1
oracle   29401 18930  0 16:40 pts/0    00:00:00 grep --color=auto pmon
[oracle@racnode-dc2-1 patch]$
[oracle@racnode-dc2-1 patch]$ kill -9 27663
[oracle@racnode-dc2-1 patch]$

Check cluster resource – close but no cigar (false positive)

[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w '(TARGET != ONLINE) or (STATE != ONLINE)'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.proxy_advm
               OFFLINE OFFLINE      racnode-dc2-1            STABLE
               OFFLINE OFFLINE      racnode-dc2-2            STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      3        OFFLINE OFFLINE                               STABLE
ora.hawk.db
      1        ONLINE  OFFLINE      racnode-dc2-1            Instance Shutdown,ST
                                                             ARTING
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 patch]$

Check cluster resource – BINGO!

[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w '(TARGET = ONLINE) and (STATE != ONLINE)'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  OFFLINE      racnode-dc2-1            Instance Shutdown,ST
                                                             ARTING
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 patch]$

Another example:

[oracle@racnode-dc2-1 ~]$ crsctl stat res -t -w '(TARGET = ONLINE) and (STATE != ONLINE)'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  INTERMEDIATE racnode-dc2-2            STABLE
ora.DATA.dg
               ONLINE  INTERMEDIATE racnode-dc2-2            STABLE
ora.FRA.dg
               ONLINE  INTERMEDIATE racnode-dc2-2            STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  OFFLINE      racnode-dc2-1            Instance Shutdown,ST
                                                             ARTING
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 ~]$

Learned something here.

[oracle@racnode-dc2-1 ~]$ crsctl stat res -v -w 'TYPE = ora.database.type'
NAME=ora.hawk.db
TYPE=ora.database.type
LAST_SERVER=racnode-dc2-1
STATE=ONLINE on racnode-dc2-1
TARGET=ONLINE
CARDINALITY_ID=1
OXR_SECTION=0
RESTART_COUNT=0
***** FAILURE_COUNT=1 
***** FAILURE_HISTORY=1564015051:racnode-dc2-1
ID=ora.hawk.db 1 1
INCARNATION=4
***** LAST_RESTART=07/25/2019 02:39:38
***** LAST_STATE_CHANGE=07/25/2019 02:39:51
STATE_DETAILS=Open,HOME=/u01/app/oracle/12.2.0.1/db1
INTERNAL_STATE=STABLE
TARGET_SERVER=racnode-dc2-1
RESOURCE_GROUP=
INSTANCE_COUNT=2

LAST_SERVER=racnode-dc2-2
STATE=ONLINE on racnode-dc2-2
TARGET=ONLINE
CARDINALITY_ID=2
OXR_SECTION=0
RESTART_COUNT=0
FAILURE_COUNT=0
FAILURE_HISTORY=
ID=ora.hawk.db 2 1
INCARNATION=1
LAST_RESTART=07/25/2019 02:21:45
LAST_STATE_CHANGE=07/25/2019 02:21:45
STATE_DETAILS=Open,HOME=/u01/app/oracle/12.2.0.1/db1
INTERNAL_STATE=STABLE
TARGET_SERVER=racnode-dc2-2
RESOURCE_GROUP=
INSTANCE_COUNT=2

[oracle@racnode-dc2-1 ~]$

Check cluster resource – sanity check.

[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w '((TARGET = ONLINE) and (STATE != ONLINE)'
[oracle@racnode-dc2-1 patch]$
[oracle@racnode-dc2-1 patch]$ crsctl stat res -t -w 'TYPE = ora.database.type'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  ONLINE       racnode-dc2-1            Open,HOME=/u01/app/o
                                                             racle/12.2.0.1/db1,S
                                                             TABLE
      2        ONLINE  ONLINE       racnode-dc2-2            Open,HOME=/u01/app/o
                                                             racle/12.2.0.1/db1,S
                                                             TABLE
--------------------------------------------------------------------------------
[oracle@racnode-dc2-1 patch]$

What is a Blog and What does it Stand for?

VitalSoftTech - Tue, 2019-07-23 09:49

All of us who use the internet daily are familiar with what a blog is. A blog is a web page or a website that one person or a small group regularly updates, written in a simple conversational style. But how many of us know what the word blog means or what does it stand […]

The post What is a Blog and What does it Stand for? appeared first on VitalSoftTech.

Categories: DBA Blogs

Sometimes the Simplest Things Are the Most Important

Cary Millsap - Tue, 2019-07-23 09:44
I didn’t ride in First Class a lot during my career, but I can remember one trip, I was sitting in seat 1B. Aisle seat, very front row. Right behind the lavatory bulkhead. The lady next to me in 1A was very nice, and we traded some stories.

I can remember telling her during dinner, wow, just look at us. Sitting in an aluminum tube going 500 miles per hour, 40,000 feet off the ground. It’s 50º below zero out there. Thousands of gallons of kerosene are burning in huge cans bolted to our wings, making it all go. Yet here we sit in complete comfort, enjoying a glass of wine and a steak dinner. And just three feet away from us in that lavatory right there, a grown man is evacuating his bowels.

I said, you know, out of all the inventions that have brought us to where we are here today, the very most important one is probably that wall.

Bealls, Inc. Drives Operational Agility with Oracle Retail Cloud

Oracle Press Releases - Tue, 2019-07-23 09:00
Press Release
Bealls, Inc. Drives Operational Agility with Oracle Retail Cloud American retailer continues digital transformation with new retail technologies

Bradenton, Florida and Redwood Shores, Calif.—Jul 23, 2019

Bealls, Inc., the company behind more than 500 stores has turned to the Oracle Retail Cloud to digitally transform its operations and drive agility. With new inventory arriving weekly, Bealls recognized the need to modernize processes and gain new management efficiencies of their extensive inventory spanning casual lifestyle, apparel and home merchandise. After a successful implementation of Oracle Human Capital Management (HMC) Cloud and Oracle Enterprise Resource Planning (ERP) Cloud, Bealls continues to modernize its technology infrastructure with Oracle Retail Merchandise Operations, Oracle Retail Home, and Oracle Retail Insights Cloud Service.

“Retail is a dynamic industry that must keep pace with the speed of fashion and continuous disruption from new ecommerce players. Cloud technology has become a strategic investment for us to compete and grow by continuously adopting new innovations and best practices from Oracle that allow us to make more informed business decisions faster than ever before,” said Dave Massey, chief digital and technology officer, Bealls. “With new insights from cloud solutions, we’re able to better understand our customers and make sure we make the right inventory investments that keep them wanting to return.”

Bealls, Inc. leverages traditional and off-price stores to tailor the customer experience. With the Oracle Retail implementation, Bealls will have more information and insight to drive its channel strategy. Logic Information Systems, an Oracle PartnerNetwork Gold level member, will implement a phased cloud deployment of the new solutions.

“To compete in the modern retail world, retailers like Bealls need to be focused on financial performance while delivering a tailored brand experience that converts consumers into advocates,” said Mike Webster, senior vice president and general manager, Oracle Retail. “By shifting to the cloud, Bealls can leverage a continuous cadence of innovation from Oracle Retail that allows them to respond to ever evolving market demands.”

Contact Info
Kaitlin Ambrogio
Oracle
+1.781.434.9555
kaitlin.ambrogio@oracle.com
About Oracle Retail

Oracle is the modern platform for retail. Oracle provides retailers with a complete, open, and integrated platform for best-of-breed business applications, cloud services, and hardware that are engineered to work together. Leading fashion, grocery, and specialty retailers use Oracle solutions to accelerate from best practice to next practice, drive operational agility and refine the customer experience. For more information, visit our website at www.oracle.com/retail.

About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

About Bealls Inc.

Headquartered in Bradenton, Florida since its founding in 1915, the family-owned corporation now operates more than 550 stores in 15 states under the names of Bealls, Bealls Outlet, Burkes Outlet, Home Centric and Bunulu.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Kaitlin Ambrogio

  • +1.781.434.9555

Oracle Health Sciences Clinical Trial Product Named a Leader in Everest Group PEAK Matrix™

Oracle Press Releases - Tue, 2019-07-23 07:00
Press Release
Oracle Health Sciences Clinical Trial Product Named a Leader in Everest Group PEAK Matrix™ Noted as the highest scoring vendor in Vision and Capability for Life Sciences Clinical Trials Products

Redwood Shores, Calif.—Jul 23, 2019

Oracle Health Sciences was positioned as a Leader and the highest scoring vendor in Vision and Capability in the Everest Group PEAK Matrix™ 2019 Assessment of Life Sciences Clinical Trials Products. Everest Group assesses vendors based on their market impact (market adoption, value delivered and portfolio mix) and vision and capability (vision and strategy, functionality, flexibility and ease of deployment, engagement and commercial model and support).

“We are honored to be recognized again as a leader and to be positioned the highest in what we believe is a critical metric–vision and capability. With the launch of Oracle Health Sciences Clinical One, the acquisition of goBalto for study startup, and our prowess in safety, we are able to offer the innovation, stability, reliability and longevity pharma customers are looking for in a technology partner,” said Steve Rosenberg, vice president and general manager for Oracle Health Sciences. 

Everest Group highlighted that as a leader in the industry, Oracle Health Sciences has been adding new innovations to its existing eClincial solutions, enabling the industry to advance clinical trials, and make significant progress in running faster, higher quality and lower cost trials.

“The clinical trials landscape has a significant opportunity for digitalization that focuses on enhancing trial efficiency, optimizing costs, and reducing cycle time to get drugs to market faster. Currently, most life sciences firms use disparate point solutions for different functions across their clinical trials. Oracle Health Sciences with its Clinical One platform is focusing on enabling a platform-led future to help life sciences firms accelerate digital transformation. With its emphasis on developing integrated solutions to assist trial design, site management, and patient safety, Oracle Health Sciences emerges as a credible partner for life sciences firms,” said Nitish Mittal, Practice Director, Healthcare and Life Sciences Technology at Everest Group.

Everest Group identified several Oracle Health Sciences strengths that placed them as the leader in the vision and capability corner, including:

  • Acquisitions and alliances across all phases of clinical trials to build a holistic set of offerings
  • Integrated end-to-end vision and market messaging

“For decades we have worked closely with emerging firms to the household names in pharmaceuticals to tackle the most complex clinical trials,” added Rosenberg. “This is a testament to our continued dedication to the needs of our customers and commitment to helping them bring therapies to patients faster without sacrificing safety and quality.”

    • Download the Everest Group PEAK Matrix™ Summary Report
    • Learn more about Oracle Health Sciences
Contact Info
Judi Palmer
Oracle
+1 650 784 7901
judi.palmer@oracle.com
About Oracle Health Sciences

Oracle Health Sciences breaks down barriers and opens new pathways to unify people and processes to bring new drugs to market faster. As a leader in Life Sciences technology, Oracle Health Sciences is trusted by 29 of the top 30 pharma, 10 of the top 10 biotech and 10 of the top 10 CROs for managing clinical trials and pharmacovigilance around the globe.

About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Judi Palmer

  • +1 650 784 7901

ORA-01000 and agent13c

Yann Neuhaus - Tue, 2019-07-23 03:51

Recently I received errors messages from OEM13c saying too many cursors were opened in a database:

instance_throughput:ORA-01000: maximum open cursors exceeded

My database had currently this open_cursors value:

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

I decided to increase its value to 800:

SQL> alter system set open_cursors=800;

System altered.

But a few minutes later I received again the same message. I decided to have a more precise look to discover what’s was happening.

SQL> SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE  a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;

HIGHEST_OPEN_CUR     MAX_OPEN_CUR
   300                 800

So I need to  find out which session is causing the error:

SQL> select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null;

     VALUE USERNAME                              SID    SERIAL#
---------- ------------------------------ ---------- ----------
         9 SYS                                     6      36943
         1 SYS                                   108      31137
         1 SYS                                   312      15397
       300 SYS                                   417      31049
        11 SYS                                   519      18527
         7 SYS                                   619      48609
         1 SYS                                   721      51139
         0 PUBLIC                                922         37
        17 SYS                                  1024          1
        14 SYS                                  1027      25319
         1 SYS                                  1129      40925

A sys connection is using 300 cursors :=(( let’s see what it is:

Great the agent 13c is causing the problem :=((

I already encountered this kind of problem on another client’s site. In fact the agent 13c is using metrics to determine the High Availability Disk or Media Backup every 15 minutes and is using a lot of cursors. The best way is to disable those metrics to avoid ORA-01000 errors:

After reloading the agent and reevaluating the alert, the incident disappeared successfully :=)

Cet article ORA-01000 and agent13c est apparu en premier sur Blog dbi services.

Resize ACFS Volume

Michael Dinh - Mon, 2019-07-22 13:14

Current Filesystem for ACFS is 299G.

Filesystem             Size  Used Avail Use% Mounted on
/dev/asm/acfs_vol-177  299G  2.6G  248G   2% /ggdata02

Free_MB is 872 which causes paging due to insufficient FREE space from ASM Disk Group ACFS_DATA.

$ asmcmd lsdg -g ACFS_DATA
Inst_ID  State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  EXTERN  N         512   4096  4194304    307184      872                0             872              0             N  ACFS_DATA/
      2  MOUNTED  EXTERN  N         512   4096  4194304    307184      874                0             872              0             N  ACFS_DATA/

Review attributes for ASM Disk Group ACFS_DATA.

$ asmcmd lsattr -l -G ACFS_DATA
Name                     Value       
access_control.enabled   FALSE       
access_control.umask     066         
au_size                  4194304     
cell.smart_scan_capable  FALSE       
compatible.advm          12.1.0.0.0  
compatible.asm           12.1.0.0.0  
compatible.rdbms         12.1.0.0.0  
content.check            FALSE       
content.type             data        
disk_repair_time         3.6h        
failgroup_repair_time    24.0h       
idp.boundary             auto        
idp.type                 dynamic     
phys_meta_replicated     true        
sector_size              512         
thin_provisioned         FALSE       

Resize /ggdata02 to 250G.

$ acfsutil size 250G /ggdata02
acfsutil size: new file system size: 268435456000 (256000MB)

Review results.

$ asmcmd lsdg -g ACFS_DATA
Inst_ID  State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  EXTERN  N         512   4096  4194304    307184    51044                0           51044              0             N  ACFS_DATA/
      2  MOUNTED  EXTERN  N         512   4096  4194304    307184    51044                0           51044              0             N  ACFS_DATA/


$ df -h /ggdata02
Filesystem             Size  Used Avail Use% Mounted on
/dev/asm/acfs_vol-177  250G  2.6G  248G   2% /ggdata02

$ asmcmd volinfo --all
Diskgroup Name: ACFS_DATA

	 Volume Name: ACFS_VOL
	 Volume Device: /dev/asm/acfs_vol-177
	 State: ENABLED
	 Size (MB): 256000
	 Resize Unit (MB): 512
	 Redundancy: UNPROT
	 Stripe Columns: 8
	 Stripe Width (K): 1024
	 Usage: ACFS
	 Mountpath: /ggdata02

Conditionally Required Floating Item

Jeff Kemp - Mon, 2019-07-22 03:43

An item in the Universal Theme using the Optional – Floating template looks like this:

An item using the Required – Floating template looks like this:

In addition, if the item is required we would most probably set the Value Required attribute to Yes. What if the item is sometimes required but not always? How do we create a Conditionally Required field?

Firstly, we would make sure there is a Validation on the field that checks that the value is provided if required. This way, regardless of what the form may or may not send to the database, it is validated appropriately.

Secondly, to indicate to the user that the item is required or optional, based on the value of another item, we can use a Dynamic Action that sets the required item property (this triggers the client-side validation) and adds or removes the is-required class from the item’s container (this shows the little red “required” indicator on the page).

For example, let’s say that whether item P1_COST_CENTRE is required or not is dependent on whether a hidden item, P1_COST_CENTRE_REQUIRED, has the value 'Y'.

  • Create a Dynamic Action
    1. Event: Change
    2. Selection Type: Item(s)
    3. Item(s): P1_COST_CENTRE_REQUIRED
    4. Client-side Condition Type: Item = Value
    5. Item: P1_COST_CENTRE_REQUIRED
    6. Value: Y
  • Create a True Action: Execute JavaScript Code
var item = $("#P1_COST_CENTRE");
item.prop("required",true);
item.closest(".t-Form-fieldContainer").addClass("is-required");
  • Create a False Action: Execute JavaScript Code
var item = $("#P1_COST_CENTRE");
item.prop("required",false);
item.closest(".t-Form-fieldContainer").removeClass("is-required");

The above code works for all item templates (“Optional”, “Optional – Above”, “Optional – Floating”, etc.) in the Universal Theme; I’ve tested this on APEX 18.2 and 19.1.

Note: this is custom code for the Universal Theme, so it may or may not work for other themes; and might stop working in a future revision of the theme.

Plugins

UPDATE 29/7/2019: I’ve created some simple Dynamic Action plugins (for APEX 18.2 and later) to implement this, if you’re interested you can download them from here:

To use these plugins, select them as an Action to take on a Dynamic Action:

EDIT 29/7/2019: modified to use a better method to find the container div.

Older Themes

In other themes, the way that a required field is rendered is different. For example, in Theme 26 (Productivity Applications) the label for a required item is rendered in bold, along with a red asterisk; if the item is optional, no red asterisk is rendered. The way to make an item conditionally mandatory in this theme is:

  1. Set the item to use the Required template (so that the red asterisk is rendered).
  2. In the Dynamic Action JavaScript, execute the following if the item should be optional:
var itemLabel = $("label[for='P1_COST_CENTRE']");
itemLabel.removeClass("uRequired");
itemLabel.addClass("uOptional");

To make the item required again:

var itemLabel = $("label[for='P1_COST_CENTRE']");
itemLabel.removeClass("uOptional");
itemLabel.addClass("uRequired");

KSQL in Football: FIFA Women’s World Cup Data Analysis

Rittman Mead Consulting - Mon, 2019-07-22 02:53

One of the football (as per European terminology) highlights of the summer is the FIFA Women’s World Cup. France, Brazil, and the USA are the favourites, and this year Italy is present at the event for the first time in 20 years.

From a data perspective, the World Cup represents an interesting source of information. There's a lot of dedicated press coverage, as well as the standard social media excitement following any kind of big event.

The idea in this blog post is to mix information coming from two distinct channels: the RSS feeds of sport-related newspapers and Twitter feeds of the FIFA Women’s World Cup. The goal will be to understand how the sentiment of official news related to the two teams involved in the final compares to that of the tweets.

In order to achieve our targets, we'll use pre-built connectors available in Confluent Hub to source data from RSS and Twitter feeds, KSQL to apply the necessary transformations and analytics, Google’s Natural Language API for sentiment scoring, Google BigQuery for data storage, and Google Data Studio for visual analytics.

Data sources

The beginning of our journey starts with connecting to various data sources. Twitter represents the default source for most event streaming examples, and it's particularly useful in our case because it contains high-volume event streaming data with easily identifiable keywords that can be used to filter for relevant topics.

Ingesting Twitter data

Ingesting Twitter data is very easy with Kafka Connect, a framework for connecting Kafka with external systems. Within the pre-built connectors we can find the Kafka Connect Twitter, all we need to do is install it using the Confluent Hub client.

confluent-hub install jcustenborder/kafka-connect-twitter:latest

To start ingesting the Twitter data, we need to create a configuration file containing the following important bits:

  • filter.keywords: We need to list all the keywords we are interested in, separated by a comma. Since we want to check tweets from the FIFA Women’s World Cup, we’ll use FIFAWWC, representing both the World Cup Twitter handle and the most common related hashtag.
  • kafka.status.topic: This topic that will be used to store the tweets we selected. twitter_avro: This is because the connector output format is AVRO.
  • twitter.oauth: This represents Twitter credentials. More information can be found on the Twitter’s developer website.

After the changes, our configuration file looks like the following:

filter.keywords=FIFAWWC
kafka.status.topic=twitter_avro
twitter.oauth.accessToken=<TWITTER ACCESS TOKEN>
twitter.oauth.accessTokenSecret=<TWITTER ACCESS TOKEN SECRET>
twitter.oauth.consumerKey=<TWITTER ACCESS CUSTOMER KEY>
twitter.oauth.consumerSecret=<TWITTER CUSTOMER SECRET>

It's time to start it up! We can use the Confluent CLI load command:

confluent load twitter -d $TWITTER_HOME/twitter.properties

$TWITTER_HOME is the folder containing the configuration file. We can check the Kafka Connect status by querying the REST APIs with the following:

curl -s "http://localhost:8083/connectors/twitter/status" | jq [.connector.state] 
[
  "RUNNING"
]

We can also check if all the settings are correct by consuming the AVRO messages in the twitter_avro topic with a console consumer:

confluent consume twitter_avro --value-format avro

And the result is, as expected, an event stream of tweets.

RSS feeds as another data source

The second data source that we'll use for our FIFA Women’s World Cup sentiment analytics are RSS feeds from sports-related newspapers. RSS feeds are useful because they share official information about teams and players, like results, episodes, and injuries. RSS feeds should be considered neutral since they should only report facts. For this blog post, we’ll use RSS feeds as a way to measure the average sentiment of the news. As per the Twitter case above, a prebuilt Kafka Connect RSS Source exists, so all we need to do is to install it via the Confluent Hub client:

confluent-hub install kaliy/kafka-connect-rss:latest

Then, create a configuration file with the following important parameters:

  • rss.urls: This is a list of space-separated RSS feed URLs. For our Women’s World Cup example, we’ve chosen the following sources: La Gazzetta dello Sport, Transfermarkt, Eurosport, UEFA, The Guardian, Daily Mail, The Sun Daily, BBC
  • topic: The Kafka topic to write to, which is rss_avro in our case

The full configuration file looks like the following:

name=RssSourceConnector
tasks.max=1
connector.class=org.kaliy.kafka.connect.rss.RssSourceConnector
rss.urls=https://www.transfermarkt.co.uk/rss/news https://www.eurosport.fr/rss.xml https://www.uefa.com/rssfeed/news/rss.xml https://www.theguardian.com/football/rss https://www.dailymail.co.uk/sport/index.rss https://www.thesundaily.my/rss/sport http://feeds.bbci.co.uk/news/rss.xml https://www.gazzetta.it/rss/home.xml
topic=rss_avro

And again, we can start the ingestion of RSS feeds with the Confluent CLI:

confluent load RssSourceConnector -d $RSS_HOME/RssSourceConnector.properties

We can test the status of Kafka Connectors using this simple procedure, and calling it like:

./connect_status.sh
RssSourceConnector  |  RUNNING  |  RUNNING
twitter             |  RUNNING  |  RUNNING

We can see that the both the RssSourceConnector and the twitter Connect are up and running. We can then check the actual data with the console consumer.

confluent consume rss_avro --value-format avro

Below is the output as expected.

Shaping the event streams

After ingesting the Twitter and RSS event streams into topics, it’s time to shape them with KSQL. Shaping the topics accomplishes two purposes:

  1. It makes the topics queryable from KSQL
  2. It defines additional structures that can be reused in downstream applications

The Twitter stream lands in Avro format with the fields listed in the related GitHub repo. We can easily declare a TWITTER_STREAM KSQL stream on top of TWITTER_AVRO with:

CREATE STREAM TWITTER_STREAM WITH (
KAFKA_TOPIC='TWITTER_AVRO',
VALUE_FORMAT='AVRO',
TIMESTAMP='CREATEDAT'
);

There is no need to define the single fields in the event stream declaration because they are already in AVRO and thus will be sourced from the Confluent Schema Registry. Schema Registry is the component within Kafka, in charge of storing, versioning and serving the topics Avro Schemas. When a topic is in AVRO format, its schema is stored in the Schema Registry, where downstream applications (like KSQL in this case) can retrieve it and use it to “shape” the messages in the topic.

The important bits of the above KSQL for our definition are:

  • KAFKA_TOPIC='TWITTER_AVRO': the definition of the source topic
  • VALUE_FORMAT='AVRO': the definition of the source topic format
  • TIMESTAMP='CREATEDAT': the Tweet's creation date, which is used as the event timestamp

We can now check that the fields’ definition has correctly been retrieved by the Schema Registry with:

DESCRIBE TWITTER_STREAM;

Or, we can use the REST API by:

curl -X "POST" "http://localhost:8088/ksql" \
-H "Content-Type: application/vnd.ksql.v1+json; charset=utf-8" \
-d $'{
"ksql": "DESCRIBE TWITTER_STREAM;",
"streamsProperties": {}
}'

The resulting fields section will be:

"fields": [
{
"name": "ROWTIME",
"schema": {
"type": "BIGINT",
"fields": null,
"memberSchema": null
},
{
"name": "ROWKEY",
"schema": {
"type": "STRING",
"fields": null,
"memberSchema": null
},
{
"name": "CREATEDAT",
"schema": {
"type": "BIGINT",
"fields": null,
"memberSchema": null
},
{
"name": "ID",
"schema": {
"type": "BIGINT",
"fields": null,
"memberSchema": null
},
...
}

The same applies to the RSS feed contained in rss_avro with:

create stream RSS_STREAM
WITH(
KAFKA_topic='rss_avro',
TIMESTAMP='DATE',
TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ss''Z''',
VALUE_FORMAT='AVRO'
)

The result will be:

ksql> describe RSS_STREAM;
Name                 : RSS_STREAM
Field   | Type
--------------------------------------------------------------
ROWTIME | BIGINT           (system)
ROWKEY  | VARCHAR(STRING)  (system)
FEED    | STRUCT<TITLE VARCHAR(STRING), URL VARCHAR(STRING)>
TITLE   | VARCHAR(STRING)
ID      | VARCHAR(STRING)
LINK    | VARCHAR(STRING)
CONTENT | VARCHAR(STRING)
AUTHOR  | VARCHAR(STRING)
DATE    | VARCHAR(STRING)
--------------------------------------------------------------

We can also use the URL manipulation functions added in KSQL 5.2 to extract useful information from the LINK column with:

CREATE STREAM RSS_STREAM_URL_DECODING AS
SELECT LINK,
URL_EXTRACT_HOST(LINK) HOST,
URL_EXTRACT_PATH(LINK) PATH,
URL_EXTRACT_PROTOCOL(LINK) PROTOCOL,
URL_EXTRACT_QUERY(LINK) QUERY_TEXT
FROM RSS_STREAM;

The result will be:

ksql> SELECT HOST, PATH, PROTOCOL, QUERY_TXT FROM RSS_STREAM_URL_DECODING LIMIT 5;
www.dailymail.co.uk | /sport/football/article-6919585/Paul-Scholes-backs-Manchester-United-spring-surprise-Barcelona.html | https | ns_mchannel=rss&ns_campaign=1490&ito=1490
www.dailymail.co.uk | /sport/formulaone/article-6916337/Chinese-Grand-Prix-F1-race-LIVE-Shanghai-International-Circuit.html | https | ns_mchannel=rss&ns_campaign=1490&ito=1490
www.dailymail.co.uk | /sport/football/article-6919403/West-Brom-make-approach-Preston-manager-Alex-Neil.html | https | ns_mchannel=rss&ns_campaign=1490&ito=1490
www.dailymail.co.uk | /sport/football/article-6919373/Danny-Murphy-Jermaine-Jenas-fascinating-mind-games-thrilling-title-race.html | https | ns_mchannel=rss&ns_campaign=1490&ito=1490
www.dailymail.co.uk | /sport/football/article-6919215/Brazilian-legend-Pele-successfully-undergoes-surgery-remove-kidney-stone-Sao-Paulo-hospital.html | https | ns_mchannel=rss&ns_campaign=1490&ito=1490
Limit Reached
Query terminated
Sentiment analytics and Google’s Natural Language APIs

Text processing is a part of machine learning and is continuously evolving with a huge variety of techniques and related implementations. Sentiment analysis represents a branch of text analytics and aims to identify and quantify affective states contained in a text corpus.

Natural Language APIs provide sentiment scoring as a service using two dimensions:

  1. Score: Positive (Score > 0) or Negative (Score < 0) Emotion
  2. Magnitude: Emotional Content Amount

For more information about sentiment score and magnitude interpretation, refer to the documentation.

Using Natural Language APIs presents various benefits:

  • Model training: Natural Language is a pre-trained model, ideal in situations where we don't have a set of already-scored corpuses.
  • Multi-language: RSS feeds and tweets can be written in multiple languages. Google Natural Language is capable of scoring several languages natively.
  • API call: Natural Language can be called via an API, making the integration easy with other tools.
Sentiment scoring in KSQL with user- defined functions (UDFs)

The Natural Language APIs are available via client libraries in various languages, including Python, C#, and Go. For the purposes of this blog post, we'll be looking at the Java implementation since it is currently the language used to implement KSQL user-defined functions (UDFs). For more details on how to build a UD(A)F function, please refer to How to Build a UDF and/or UDAF in KSQL 5.0 by Kai Waehner, which we'll use as base for the GSentiment class definition.

The basic steps to implementing Natural Language API calls in a UDF are the following:

  1. Add the google.cloud.language JAR dependency in your project. If you are using Maven, you just need to add the following in your pom.xml

<dependency>: <groupId>com.google.cloud</groupId> <artifactId>google-cloud-language</artifactId> <version>1.25.0</version> </dependency>

  1. Create a new Java class called GSentiment.
  2. Import the required classes:

//KSQL UDF Classes import io.confluent.ksql.function.udf.Udf; import io.confluent.ksql.function.udf.UdfDescription; //Google NL Classes import com.google.cloud.language.v1.LanguageServiceClient; import com.google.cloud.language.v1.Sentiment; import com.google.cloud.language.v1.AnalyzeSentimentResponse; import com.google.cloud.language.v1.Document; import com.google.cloud.language.v1.Document.Type;

  1. Define the GSentimen class and add the Java annotations @UdfDescription(name = "gsentiment", description = "Sentiment scoring using Google NL API") public class Gsentiment { ... }.
  2. Within the class, declare gsentiment as the method accepting a String text as input. As of now, UDFs can't return two output values, so we are returning the sentiment score and magnitude as an array of double. @Udf(description = "return sentiment scoring") public List<Double> gsentiment( String text) { ... }.
  3. Within the gsentiment method, invoke the Natural Language API sentiment and cast the result as an array. Since a UDF can return only one parameter currently, we need to pipe the sentiment score and magnitude into an array of two elements.
Double[] arr = new Double[2];   
try (LanguageServiceClient languageServiceClient = LanguageServiceClient.create()) {
Document document = Document.newBuilder()
                .setContent(text)
                .setType(Type.PLAIN_TEXT)
                .build();
AnalyzeSentimentResponse response = languageServiceClient.analyzeSentiment(document);
Sentiment sentiment = response.getDocumentSentiment();

arr[0]=(double)sentiment.getMagnitude();
arr[1]=(double)sentiment.getScore();
} 
catch (Exception e) {
arr[0]=(double) 0.0;
arr[1]=(double) 0.0;
}
return Arrays.asList(arr);

  1. As mentioned in How to Build a UDF and/or UDAF in KSQL 5.0, build an uber JAR that includes the KSQL UDF and any dependencies, and copy it to the KSQL extension directory (defined in the ksql.extension.dir parameter in ksql-server.properties).
  2. Add an environment variable GOOGLE_APPLICATION_CREDENTIALS pointing to the service account key that will be used to authenticate to Google services.
  3. Restart KSQL.

At this point, we should be able to call the GSentiment UDF from KSQL:

ksql> SELECT GSENTIMENT(text) FROM TWITTER_STREAM LIMIT 5;
[0.10000000149011612, -0.10000000149011612]
[0.20000000298023224, 0.20000000298023224]
[0.5                , 0.10000000149011612]
[0.10000000149011612, 0.10000000149011612]
[0.0                , 0.0]
Limit Reached
Query terminated

As expected, the UDF returns an ARRAY of numbers. In order to get the sentiment score and magnitude in separated columns, we simply need to extract the relevant values:

ksql> SELECT GSENTIMENT(TEXT)[0] SCORE, GSENTIMENT(TEXT)[1] MAGNITUDE FROM TWITTER_STREAM LIMIT 5;
0.20000000298023224 | 0.10000000149011612
0.30000001192092896 | 0.10000000149011612
0.800000011920929   | 0.800000011920929
0.0                 | 0.0
0.30000001192092896 | 0.30000001192092896
Limit Reached
Query terminated

However, we should note that Natural Language APIs are priced per API call and, in the above SQL, we are calling the API two times—one for each GSENTIMENT call. Therefore, the above SQL will cost us two API calls per document. To optimise the cost, we can create a new event stream TWITTER_STREAM_WITH_SENTIMENT, which will physicalize in Kafka the array.

CREATE STREAM TWITTER_STREAM_WITH_SENTIMENT AS
SELECT
*,
GSENTIMENT(TEXT) AS SENTIMENT
FROM TWITTER_STREAM;

Next, parse the sentiment SCORE and MAGNITUDE from the TWITTER_STREAM_WITH_SENTIMENT event stream:

CREATE STREAM TWITTER_STREAM_WITH_SENTIMENT_DETAILS as
SELECT *,
SENTIMENT[0] SCORE,
SENTIMENT[1] MAGNITUDE
FROM TWITTER_STREAM_WITH_SENTIMENT;

With this second method, we optimize the cost with a single Natural Language API call per tweet. We can do the same with the RSS feeds by declaring:

CREATE STREAM RSS_STREAM_WITH_SENTIMENT AS
SELECT
*,
GSENTIMENT(CONTENT) SENTIMENT
FROM RSS_STREAM_FLATTENED;
CREATE STREAM RSS_STREAM_WITH_SENTIMENT_DETAILS as
SELECT *,
SENTIMENT[0] SCORE,
SENTIMENT[1] MAGNITUDE
FROM RSS_STREAM_WITH_SENTIMENT;
Sink to Google BigQuery

The following part of this blog post focuses on pushing the dataset into Google BigQuery and visual analysis in Google Data Studio.

Pushing the data into BigQuery is very easy—just install the BigQuery Sink Connector with:

confluent-hub install wepay/kafka-connect-bigquery:latest

Next, configure it while applying the following parameters (amongst others):

  • topics: defines the topic to read (in our case RSS_STREAM_WITH_SENTIMENT_DETAILS and TWITTER_STREAM_WITH_SENTIMENT_DETAILS)
  • project: the name of the Google project that we’ll use for billing
  • datasets=.*=wwc: defines the BigQuery dataset name
  • keyfile=$GOOGLE_CRED/myGoogleCredentials.json: points to the JSON file containing Google's credentials (which in our case is the same file used in the Google Natural Language scoring)

Before starting the connector, we need to ensure the BigQuery dataset named wwc (as per configuration file) exists, otherwise, the connector will fail. To do so, we can log into BigQuery, select the same project defined in the configuration file, and click on CREATE DATASET. Then, we’ll need to fill in all the details (more information about the dataset creation in the Google documentation).

After creating the dataset, it’s time to start the connector with the Confluent CLI:

confluent load bigquery-connector -d $RSS_HOME/connectorBQ.properties

If the Kafka sink works, we should see one table per topic defined in the configuration file, which are RSS_STREAM_WITH_SENTIMENT_DETAILS and TWITTER_STREAM_WITH_SENTIMENT_DETAILS in our case.

Of course, we can query the data from BigQuery itself.

Visual Analysis in Google Data Studio

To start analysing the data in Google Data Studio, simply connect to the related console and select “Blank Report.”

We’ll be asked which data source to use for the project. Thus, we need to set up a connection to the wwc dataset by clicking on CREATE NEW DATASOURCE and selecting BigQuery as the connection. Then, select the project, dataset, and table ( TWITTER_STREAM_WITH_SENTIMENT_DETAILS).


We can then review the list of columns, types, and aggregations,  adding the data source to the report.

Finally, we can start creating visualisations like tiles to show record counts, line charts for the sentiment trend, and bar charts defining the most used languages.

A more advanced visualisation like a scatterplot shows the most common hashtags and the associated average sentiment value.

Below is a map visualising the average sentiment by country.

Analysing and comparing sentiment scores

Now that we have the two streams of data coming from Twitter and RSS feeds, we can do the analysis in KSQL and, in parallel, visually in Google Data Studio. We can, for example, examine the average sentiment over a timeframe and check how one source sentiment score compares to the other.

On the 27th of June, the quarterfinal match between Norway and England was played, with the result being that England beat Norway 3–0. Let’s check if we can somehow find significant similarities in the sentiment scoring of our dataset.

Starting with the Twitter feed, we can check all the tweets including ENGLAND and NORWAY by filtering the related hashtag #NORENG. To obtain the team related overall score, I’m then assigning to each team all the tweets containing the country full name and aggregating the  SENTIMENTSCORE with the following SQL:

CREATE STREAM TWITTER_NORWAY_ENGLAND AS
SELECT
CASE
WHEN UCASE(TEXT) LIKE '%NORWAY%' THEN SENTIMENTSCORE
END AS NORWAY_SENTIMENTSCORE,
CASE
WHEN UCASE(TEXT) LIKE '%ENGLAND%' THEN SENTIMENTSCORE
END AS ENGLAND_SENTIMENTSCORE
FROM TWITTER_STREAM_WITH_SENTIMENT_DETAILS
WHERE TEXT LIKE '%#NORENG%';

We can check the overall sentiment score associated with the two teams using:

SELECT
SUM(NORWAY_SENTIMENTSCORE)/COUNT(NORWAY_SENTIMENTSCORE) AS NORWAY_AVG_SCORE,
SUM(ENGLAND_SENTIMENTSCORE)/COUNT(ENGLAND_SENTIMENTSCORE) AS ENGLAND_AVG_SCORE
FROM TWITTER_NORWAY_ENGLAND
GROUP BY 1;

The GROUP BY 1 is necessary since KSQL currently requires a GROUP BY clause when using aggregation functions like SUM. Since we don’t aggregate for any columns other than the window time, we can use the number 1 as fix aggregator for the total. The result of the above query is in line with the final score, with the winner (England) having an average sentiment score of 0.212, and the loser (Norway) having a score of 0.0979.

We can also look at the behaviour per hour with the TUMBLING KSQL windowing function:

SELECT
TIMESTAMPTOSTRING(WINDOWSTART(), 'EEE dd MMM HH') AS START_WINDOW,
SUM(NORWAY_SENTIMENTSCORE)/COUNT(NORWAY_SENTIMENTSCORE) AS NORWAY_AVG_SCORE,
SUM(ENGLAND_SENTIMENTSCORE)/COUNT(ENGLAND_SENTIMENTSCORE) AS ENGLAND_AVG_SCORE
FROM TWITTER_NORWAY_ENGLAND
WINDOW TUMBLING(SIZE 1 HOURS)
GROUP BY 1;

The query yields the following result:

Thu 27 Jun 16 | 0.18409091 | 0.16075758
Thu 27 Jun 17 | 0.14481481 | 0.13887096
Thu 27 Jun 18 | 0.14714406 | 0.12107647
Thu 27 Jun 19 | 0.07926398 | 0.34757579
Thu 27 Jun 20 | 0.10077705 | 0.13762544
Thu 27 Jun 21 | 0.08387538 | 0.17832865

We can clearly see that towards match time (19:00 BST), the ENGLAND average score has a spike, in coincidence with England’s first goal in the third minute. We can see the same on the Line Chart in Google Data Studio.

We can do a similar exercise on top of the RSS feeds stream, but first, we need to somehow filter it to get only FIFA Women’s World Cup 2019 data, since the predefined connector is ingesting all the news from the RSS sources without a topic filter. To do so, we create a new stream filtering only contents containing WOMEN and CUP:

CREATE STREAM RSS_STREAM_WITH_SENTIMENT_DETAILS_WWC as
SELECT *
FROM RSS_STREAM_WITH_SENTIMENT_DETAILS
WHERE UCASE(CONTENT) LIKE '%WOMEN%'
AND UCASE(CONTENT) LIKE '%CUP%';

We can now analyse the overall RSS sentiment with:

SELECT SUM(SENTIMENTSCORE)/COUNT(SENTIMENTSCORE)
FROM RSS_STREAM_WITH_SENTIMENT_DETAILS_WWC
GROUP BY 1;

As before, the SUM(SENTIMENTSCORE)/COUNT(SENTIMENTSCORE) is calculating the average. We can then calculate the sentiment average for the selected team. Taking the same example of ENGLAND and NORWAY used previously, we just declare a stream filtering the sentiment for the two nations. For example:

CREATE STREAM RSS_NORWAY_ENGLAND AS
SELECT
CASE WHEN UCASE(CONTENT) LIKE '%NORWAY%' THEN SENTIMENTSCORE END NORWAY_SENTIMENTSCORE,
CASE WHEN UCASE(CONTENT) LIKE '%ENGLAND%' THEN SENTIMENTSCORE END ENGLAND_SENTIMENTSCORE
FROM RSS_STREAM_WITH_SENTIMENT_DETAILS_WWC;

Then, we can analyse the separate scoring with:

SELECT
SUM(NORWAY_SENTIMENTSCORE)/COUNT(NORWAY_SENTIMENTSCORE) NORWAY_AVG_SENTIMENT,
SUM(ENGLAND_SENTIMENTSCORE)/COUNT(ENGLAND_SENTIMENTSCORE) ENGLAND_AVG_SENTIMENT
FROM RSS_NORWAY_ENGLAND
WHERE ROWTIME > STRINGTODATE('2019-06-27', 'yyyy-MM-dd')
GROUP BY 1;

The result is an average sentiment of 0.0575 for Norway and 0.111 for England, again in line with the match result where England won 3–0.

We can also understand the variation of the sentiment over time by using KSQL windowing functions like TUMBLING:

SELECT
TIMESTAMPTOSTRING(WINDOWSTART(), 'EEE dd MMM HH') START_WINDOW,
SUM(NORWAY_SENTIMENTSCORE)/COUNT(NORWAY_SENTIMENTSCORE) NORWAY_AVG_SENTIMENT,
SUM(ENGLAND_SENTIMENTSCORE)/COUNT(ENGLAND_SENTIMENTSCORE) ENGLAND_AVG_SENTIMENT
FROM RSS_NORWAY_ENGLAND WINDOW TUMBLING(SIZE 1 HOURS)
WHERE ROWTIME >= STRINGTODATE('2019-06-27', 'yyyy-MM-dd')
GROUP BY 1;

This yields the following results:

Thu 27 Jun 17 | 0.12876364 | 0.12876364
Thu 27 Jun 18 | 0.24957054 | 0.24957054
Thu 27 Jun 19 | 0.15606978 | 0.15606978
Thu 27 Jun 20 | 0.09970317 | 0.09970317
Thu 27 Jun 21 | 0.00809077 | 0.00809077
Thu 27 Jun 23 | 0.41298701 | 0.12389610

As expected from this source, most of the scoring of the two countries are the same since the number of articles is limited and almost all articles mention both ENGLAND and NORWAY.


Strangely, as we can see in the graph above, the NORWAY sentiment score on the 27th of June at 11:00 pm GMT (so after the match ended) is much higher than the ENGLAND one.

We can look at the data closely with:

SELECT ROWKEY, NORWAY_SENTIMENTSCORE, ENGLAND_SENTIMENTSCORE
from NORWAY_ENGLAND where TIMESTAMPTOSTRING(ROWTIME,'dd/MM HH') = '27/06 23';
https://www.theguardian.com/...      | 0.375974032  | 0.375974032
https://www.bbc.co.uk/.../48794550   | null         | -0.45428572
https://www.bbc.co.uk/.../48795487   | 0.449999988  | 0.449999988

We can see that  NORWAY is being associated with two articles: one from The Guardian with a positive 0.375 score and one from BBC with a positive 0.449 score. ENGLAND, on the other hand, is associated with another BBC article, having a negative -0.454 score.
We can also compare the hourly Twitter and RSS sentiment scores by creating two tables:

CREATE TABLE TWITTER_NORWAY_ENGLAND_TABLE AS
SELECT
TIMESTAMPTOSTRING(WINDOWSTART(), 'dd HH') START_WINDOW,
SUM(NORWAY_SENTIMENTSCORE)/COUNT(NORWAY_SENTIMENTSCORE) NORWAY_AVG_SCORE,
SUM(ENGLAND_SENTIMENTSCORE)/COUNT(ENGLAND_SENTIMENTSCORE) ENGLAND_AVG_SCORE
FROM TWITTER_NORWAY_ENGLAND
WINDOW TUMBLING(SIZE 1 HOURS)
GROUP BY 1;
CREATE TABLE RSS_NORWAY_ENGLAND_TABLE AS
SELECT
TIMESTAMPTOSTRING(WINDOWSTART(), 'dd HH') START_WINDOW,
SUM(NORWAY_SENTIMENTSCORE)/COUNT(NORWAY_SENTIMENTSCORE) NORWAY_AVG_SCORE,
SUM(ENGLAND_SENTIMENTSCORE)/COUNT(ENGLAND_SENTIMENTSCORE) ENGLAND_AVG_SCORE
FROM NORWAY_ENGLAND WINDOW TUMBLING(SIZE 1 HOURS)
WHERE ROWTIME >= STRINGTODATE('2019-06-27', 'yyyy-MM-dd')
GROUP BY 1;

The key of both the tables is the window start date, as we can see from:

ksql> select rowkey from RSS_NORWAY_ENGLAND_TABLE limit 1;
1 : Window{start=1561557600000 end=-}

We can then join the results together with the following statement:

SELECT A.START_WINDOW,
A.NORWAY_AVG_SENTIMENT TWITTER_NORWAY_SCORE,
A.ENGLAND_AVG_SENTIMENT TWITTER_ENGLAND_SCORE,
B.NORWAY_AVG_SENTIMENT RSS_NORWAY_SCORE,
B.ENGLAND_AVG_SENTIMENT RSS_ENGLAND_SCORE
FROM
TWITTER_NORWAY_ENGLAND_TABLE A JOIN
RSS_NORWAY_ENGLAND_TABLE B
ON A.ROWKEY = B.ROWKEY;

This yields the following result:

Thu 27 Jun 17 | 0.14481481 | 0.13887096 | 0.12876364 | 0.12876364
Thu 27 Jun 18 | 0.14714406 | 0.12107647 | 0.24957054 | 0.24957054
Thu 27 Jun 19 | 0.07926398 | 0.34757579 | 0.15606978 | 0.15606978
Thu 27 Jun 20 | 0.10077705 | 0.13762544 | 0.09970317 | 0.09970317
Thu 27 Jun 21 | 0.08387538 | 0.17832865 | 0.00809077 | 0.00809077

And the end result similarly in a Data Studio Line Chart

Interested in more?

If you’re interested in what KSQL can do, you can download the Confluent Platform to get started with the event streaming SQL engine for Apache Kafka. To help you get started, Rittman Mead provides a 30 day Kafka quick start package

This article was originally posted on the Confluent blog.

Categories: BI & Warehousing

Documentum – D2+Pack Plugins not installed correctly

Yann Neuhaus - Sun, 2019-07-21 08:43

In a previous blog, I explained how D2 can be installed in silent. In this blog, I will talk about a possible issue that might happen when doing so with the D2+Pack Plugins that aren’t being installed, even if you ask D2 to install them and while there is no message or no errors related to this issue. The first time I had this issue, it was several years ago but I never blogged about it. I faced it again recently so I thought I would this time.

So first, let’s prepare the D2 and D2+Pack packages for the silent installation. I will take the D2_template.xml file from my previous blog as a starting point for the silent parameter file:

[dmadmin@cs_01 ~]$ cd $DOCUMENTUM/D2-Install/
[dmadmin@cs_01 D2-Install]$ ls *.zip *.tar.gz
-rw-r-----. 1 dmadmin dmadmin 491128907 Jun 16 08:12 D2_4.7.0_P25.zip
-rw-r-----. 1 dmadmin dmadmin  61035679 Jun 16 08:12 D2_pluspack_4.7.0.P25.zip
-rw-r-----. 1 dmadmin dmadmin 122461951 Jun 16 08:12 emc-dfs-sdk-7.3.tar.gz
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ unzip $DOCUMENTUM/D2-Install/D2_4.7.0_P25.zip -d $DOCUMENTUM/D2-Install/
[dmadmin@cs_01 D2-Install]$ unzip $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25.zip -d $DOCUMENTUM/D2-Install/
[dmadmin@cs_01 D2-Install]$ unzip $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/C2-Dar-Install.zip -d $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/
[dmadmin@cs_01 D2-Install]$ unzip $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/D2-Bin-Dar-Install.zip -d $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/
[dmadmin@cs_01 D2-Install]$ unzip $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/O2-Dar-Install.zip -d $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/
[dmadmin@cs_01 D2-Install]$ tar -xzvf $DOCUMENTUM/D2-Install/emc-dfs-sdk-7.3.tar.gz -C $DOCUMENTUM/D2-Install/
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ #See the previous blog for the content of the "/tmp/dctm_install/D2_template.xml" file
[dmadmin@cs_01 D2-Install]$ export d2_install_file=$DOCUMENTUM/D2-Install/D2.xml
[dmadmin@cs_01 D2-Install]$ cp /tmp/dctm_install/D2_template.xml ${d2_install_file}
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ sed -i "s,###WAR_REQUIRED###,true," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$ sed -i "s,###BPM_REQUIRED###,true," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$ sed -i "s,###DAR_REQUIRED###,true," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ sed -i "s,###DOCUMENTUM###,$DOCUMENTUM," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ sed -i "s,###PLUGIN_LIST###,$DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/C2-Install-4.7.0.jar;$DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/D2-Bin-Install-4.7.0.jar;$DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/O2-Install-4.7.0.jar;," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ sed -i "s,###JMS_HOME###,$DOCUMENTUM_SHARED/wildfly9.0.1," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ sed -i "s,###DFS_SDK_PACKAGE###,emc-dfs-sdk-7.3," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ read -s -p "  ----> Please enter the Install Owner's password: " dm_pw; echo; echo
  ----> Please enter the Install Owner's password: <TYPE HERE THE PASSWORD>
[dmadmin@cs_01 D2-Install]$ sed -i "s,###INSTALL_OWNER###,dmadmin," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$ sed -i "s,###INSTALL_OWNER_PASSWD###,${dm_pw}," ${d2_install_file}
[dmadmin@cs_01 D2-Install]$
[dmadmin@cs_01 D2-Install]$ sed -i "s/###DOCBASE_LIST###/Docbase1/" ${d2_install_file}
[dmadmin@cs_01 D2-Install]$

 

Now that the silent file is ready and that all source packages are available, we can start the D2 Installation with the command below. Please note the usage of the tracing/debugging options as well as the usage of the “-Djava.io.tmpdir” Java option to ask D2 to put all tmp files in a specific directory, with this, D2 is supposed to trace/debug everything and use my specific temporary folder:

[dmadmin@cs_01 D2-Install]$ java -DTRACE=true -DDEBUG=true -Djava.io.tmpdir=$DOCUMENTUM/D2-Install/tmp -jar $DOCUMENTUM/D2-Install/D2_4.7.0_P25/D2-Installer-4.7.0.jar ${d2_install_file}

 

The D2 Installer printed the following extract:

...
Installing plugin: $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/C2-Install-4.7.0.jar
Plugin install command: [java, -jar, $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/C2-Install-4.7.0.jar, $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/scripts/C6-Plugins-Install_new.xml]
Line read: [ Starting automated installation ]
Installing plugin: $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/D2-Bin-Install-4.7.0.jar
Plugin install command: [java, -jar, $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/D2-Bin-Install-4.7.0.jar, $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/scripts/C6-Plugins-Install_new.xml]
Line read: [ Starting automated installation ]
Installing plugin: $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/O2-Install-4.7.0.jar
Plugin install command: [java, -jar, $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/O2-Install-4.7.0.jar, $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/scripts/C6-Plugins-Install_new.xml]
Line read: [ Starting automated installation ]
Installing plugin: $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/plugin/D2-Widget-Install.jar
...
...
Current line: #################################
Current line: #           Plugins               #
Current line: #################################
Current line: #plugin_1=../C2/C2-Plugin.jar
Updating line with 'plugin_'.
Updating plugin 1 with plugin name: D2-Widget-Plugin.jar and config exclude value of: false
Updating plugin 2 with plugin name: D2-Specifications-Plugin.jar and config exclude value of: false
Current line: #plugin_2=../O2/O2-Plugin.jar
Current line: #plugin_3=../P2/P2-Plugin.jar
...

 

As you can see, there are no errors so if you aren’t paying attention, you might think that the D2+Pack is properly installed. It’s not. At the end of the extract I put above, you can see that the D2 Installer is updating the plugins list with some elements (D2-Widget-Plugin.jar & D2-Specifications-Plugin.jar). If there were no issue, the D2+Pack Plugins would have been added in this section as well, which isn’t the case.

You can check all temporary files, all log files, it will not be printed anywhere that there were an issue while installing the D2+Pack Plugins. In fact, there are 3 things missing:

  • The DARs of the D2+Pack Plugins weren’t installed
  • The libraries of the D2+Pack Plugins weren’t deployed into the JMS
  • The libraries of the D2+Pack Plugins weren’t packaged in the WAR files

There is a way to quickly check if the D2+Pack Plugins DARs have been installed, just look inside the docbase config folder, there should be one log file for the D2 Core DARs as well as one log file for each of the D2+Pack Plugins. So that’s what you should get:

[dmadmin@cs_01 D2-Install]$ cd $DOCUMENTUM/dba/config/Docbase1/
[dmadmin@cs_01 Docbase1]$ ls -ltr *.log
-rw-r-----. 1 dmadmin dmadmin  62787 Jun 16 08:18 D2_CORE_DAR.log
-rw-r-----. 1 dmadmin dmadmin   4794 Jun 16 08:20 D2-C2_dar.log
-rw-r-----. 1 dmadmin dmadmin   3105 Jun 16 08:22 D2-Bin_dar.log
-rw-r-----. 1 dmadmin dmadmin   2262 Jun 16 08:24 D2-O2_DAR.log
[dmadmin@cs_01 Docbase1]$

 

If you only have “D2_CORE_DAR.log”, then you are potentially facing this issue. You could also check the “csDir” folder that you put in the D2 silent parameter file: if this folder doesn’t contain “O2-API.jar” or “C2-API.jar” or “D2-Bin-API.jar”, then you have the issue as well. Obviously, you could also check the list of installed DARs in the repository…

So what’s the issue? Well, you remember above when I mentioned the “-Djava.io.tmpdir” Java option to specifically ask D2 to put all temporary files under a certain location? The D2 Installer, for the D2 part, is using this option without issue… But for the D2+Pack installation, there is actually a hardcoded path for the temporary files which is /tmp. Therefore, it will ignore this Java option and will try instead to execute the installation under /tmp.

This is the issue I faced a few times already and it’s the one I wanted to talk about in this blog. For security reasons, you might have to deal from time to time with specific mount options on file systems. In this case, the “noexec” option was set on the /tmp mount point and therefore D2 wasn’t able to execute commands under /tmp and instead of printing an error, it just bypassed silently the installation. I had a SR opened with the Documentum Support (when it was still EMC) to see if it was possible to use the Java option and not /tmp but it looks like it’s still not solved since I had the exact same issue with the D2 4.7 P25 which was released very recently.

Since there is apparently no way to specify which temporary folder should be used for the D2+Pack Plugins, you should either perform the installation manually (DAR installation + libraries in JMS & WAR files) or remove the “noexec” option on the file system for the time of the installation:

[dmadmin@cs_01 Docbase1]$ mount | grep " /tmp"
/dev/mapper/VolGroup00-LogVol06 on /tmp type ext4 (rw,noexec,nosuid,nodev)
[dmadmin@cs_01 Docbase1]$
[dmadmin@cs_01 Docbase1]$ sudo mount -o remount,exec /tmp
[dmadmin@cs_01 Docbase1]$ mount | grep " /tmp"
/dev/mapper/VolGroup00-LogVol06 on /tmp type ext4 (rw,nosuid,nodev)
[dmadmin@cs_01 Docbase1]$
[dmadmin@cs_01 Docbase1]$ #Execute the D2 Installer here
[dmadmin@cs_01 Docbase1]$
[dmadmin@cs_01 Docbase1]$ sudo mount -o remount /tmp
[dmadmin@cs_01 Docbase1]$ mount | grep " /tmp"
/dev/mapper/VolGroup00-LogVol06 on /tmp type ext4 (rw,noexec,nosuid,nodev)

 

With the workaround in place, the D2 Installer should now print the following (same extract as above):

...
Installing plugin: $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/C2-Install-4.7.0.jar
Plugin install command: [java, -jar, $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/C2-Install-4.7.0.jar, $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/scripts/C6-Plugins-Install_new.xml]
Line read: [ Starting automated installation ]
Line read: Current MAC address : [ Starting to unpack ]
Line read: [ Processing package: core (1/2) ]
Line read: [ Processing package: DAR (2/2) ]
Line read: [ Unpacking finished ]
Line read: [ Writing the uninstaller data ... ]
Line read: [ Automated installation done ]
Installing plugin: $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/D2-Bin-Install-4.7.0.jar
Plugin install command: [java, -jar, $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/D2-Bin-Install-4.7.0.jar, $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/scripts/C6-Plugins-Install_new.xml]
Line read: [ Starting automated installation ]
Line read: Current MAC address : [ Starting to unpack ]
Line read: [ Processing package: core (1/2) ]
Line read: [ Processing package: DAR (2/2) ]
Line read: [ Unpacking finished ]
Line read: [ Writing the uninstaller data ... ]
Line read: [ Automated installation done ]
Installing plugin: $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/O2-Install-4.7.0.jar
Plugin install command: [java, -jar, $DOCUMENTUM/D2-Install/D2_pluspack_4.7.0.P25/Plugins/O2-Install-4.7.0.jar, $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/scripts/C6-Plugins-Install_new.xml]
Line read: [ Starting automated installation ]
Line read: Current MAC address : [ Starting to unpack ]
Line read: [ Processing package: core (1/2) ]
Line read: [ Processing package: DAR (2/2) ]
Line read: [ Unpacking finished ]
Line read: [ Writing the uninstaller data ... ]
Line read: [ Automated installation done ]
Installing plugin: $DOCUMENTUM/D2-Install/tmp/D2_4.7.0/plugin/D2-Widget-Install.jar
...
...
Current line: #################################
Current line: #           Plugins               #
Current line: #################################
Current line: #plugin_1=../C2/C2-Plugin.jar
Updating line with 'plugin_'.
Updating plugin 1 with plugin name: D2-Widget-Plugin.jar and config exclude value of: false
Updating plugin 2 with plugin name: C2-Plugin.jar and config exclude value of: false
Updating plugin 3 with plugin name: O2-Plugin.jar and config exclude value of: false
Updating plugin 4 with plugin name: D2-Specifications-Plugin.jar and config exclude value of: false
Updating plugin 5 with plugin name: D2-Bin-Plugin.jar and config exclude value of: false
Current line: #plugin_2=../O2/O2-Plugin.jar
Current line: #plugin_3=../P2/P2-Plugin.jar
...

 

As you can see above, the output is quite different: it means that the D2+Pack Plugins have been installed.

 

Cet article Documentum – D2+Pack Plugins not installed correctly est apparu en premier sur Blog dbi services.

Firefox on Linux: password field flickering / curser blinks...

Dietrich Schroff - Sat, 2019-07-20 07:53
After upgrading to firefox 68 i was not able to enter the password (and configuration password) field in online registrations.
The cursor in the password fields kept flickering/blinking and on some pages firefox completely freezes.
With other browsers this does not happen, but i wanted to stay with firefox.

The solution was starting firefox with an additional environment variable:

GTK_IM_MODULE=xim firefox

Pages

Subscribe to Oracle FAQ aggregator