Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 3 hours 30 min ago

Loading data from S3 to AWS RDS for PostgreSQL

Wed, 2020-11-11 13:44

AWS RDS for PostgreSQL comes with an extension that allows you to fetch data from AWS S3 and to write back data to AWS S3. The use case for this is obvious: Either you use other AWS services that write data to S3 and you want to further process that data in PostgreSQL, or you want other AWS services to consume data from PostgreSQL by providing that data in S3. Let’s have a look at how that works.

The extension AWS is providing for working with S3 from inside PostgreSQL is called “aws_s3”:

postgres=> select * from pg_available_extensions where name like '%aws%';
    name     | default_version | installed_version |                   comment                   
-------------+-----------------+-------------------+---------------------------------------------
 aws_commons | 1.0             |                   | Common data types across AWS services
 aws_s3      | 1.0             |                   | AWS S3 extension for importing data from S3
(2 rows)

If you try to install the extension you’ll notice that there is a dependency on the “aws_commons” extension:

postgres=> create extension aws_s3;
ERROR:  required extension "aws_commons" is not installed
HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.

You can install both extensions in one step using the “CASCADE” option:

postgres=> create extension aws_s3 cascade;
NOTICE:  installing required extension "aws_commons"
CREATE EXTENSION

These extensions provide a couple of helper functions (aws_commons) and the function to import a file from S3 (aws_s3):

postgres=> \dx+ aws_commons
             Objects in extension "aws_commons"
                     Object description                      
-------------------------------------------------------------
 function aws_commons.create_aws_credentials(text,text,text)
 function aws_commons.create_s3_uri(text,text,text)
 schema aws_commons
 type aws_commons._aws_credentials_1
 type aws_commons._s3_uri_1
(5 rows)

postgres=> \dx+ aws_s3
                                       Objects in extension "aws_s3"
                                            Object description                                             
-----------------------------------------------------------------------------------------------------------
 function aws_s3.table_import_from_s3(text,text,text,aws_commons._s3_uri_1,aws_commons._aws_credentials_1)
 function aws_s3.table_import_from_s3(text,text,text,text,text,text,text,text,text)
 schema aws_s3
(3 rows)

Having the extension ready we need a file we can import, so lets create one (exactly the same file as in the previous post, but a bit less rows):

dwe@dwe:~/Downloads$ cat gen_data.sh 
#!/bin/bash
 
FILE="/home/dwe/Downloads/sample.csv"
rm -rf ${FILE}
 
for i in {1..1000000}; do
    echo "${i},firstname${i},lastname${i},xxx${i}@xxx.com,street${i},country${i},description${i}" >> ${FILE}
done

dwe@dwe:~/Downloads$ chmod +x gen_data.sh 
dwe@dwe:~/Downloads$ ./gen_data.sh 
dwe@dwe:~/Downloads$ head -5 sample.csv 
1,firstname1,lastname1,xxx1@xxx.com,street1,country1,description1
2,firstname2,lastname2,xxx2@xxx.com,street2,country2,description2
3,firstname3,lastname3,xxx3@xxx.com,street3,country3,description3
4,firstname4,lastname4,xxx4@xxx.com,street4,country4,description4
5,firstname5,lastname5,xxx5@xxx.com,street5,country5,description5
dwe@dwe:~/Downloads$ ls -lha sample.csv 
-rw-rw-r-- 1 dwe dwe 96M Nov 10 11:11 sample.csv

We’ll be using a new bucket for this demo, so lets create one and then upload the file we just generated:

dwe@dwe:~/Downloads$ aws s3 mb s3://s3-rds-demo --region eu-central-1
make_bucket: s3-rds-demo
dwe@dwe:~/Downloads$ aws s3 cp sample.csv s3://s3-rds-demo/
upload: ./sample.csv to s3://s3-rds-demo/sample.csv         

Before we can do anything against S3 from RDS for PostgreSQL we need to setup the required permissions. You can use security credentials for this, but it is recommended to use IAM roles and policies. The first step is to create a policy that allows listing the bucket, read and write (write is required for writing data to S3 later on):

dwe@dwe:~$ aws iam create-policy \
>    --policy-name rds-s3-policy \
>    --policy-document '{
>      "Version": "2012-10-17",
>      "Statement": [
>        {
>          "Sid": "s3import",
>          "Action": [
>            "s3:GetObject",
>            "s3:ListBucket",
>            "S3:PutObject"
>          ],
>          "Effect": "Allow",
>          "Resource": [
>            "arn:aws:s3:::s3-rds-demo", 
>            "arn:aws:s3:::s3-rds-demo/*"
>          ] 
>        }
>      ] 
>    }' 
{
    "Policy": {
        "PolicyName": "rds-s3-policy",
        "PolicyId": "ANPA2U57KX3NFH4HU4COG",
        "Arn": "arn:aws:iam::xxxxxxxx:policy/rds-s3-policy",
        "Path": "/",
        "DefaultVersionId": "v1",
        "AttachmentCount": 0,
        "PermissionsBoundaryUsageCount": 0,
        "IsAttachable": true,
        "CreateDate": "2020-11-10T12:04:34+00:00",
        "UpdateDate": "2020-11-10T12:04:34+00:00"
    }
}

Once the policy is in place we create an IAM role which gets the policy just created attached to:

dwe@dwe:~$ aws iam create-role \
>    --role-name rds-s3-role \
>    --assume-role-policy-document '{
>      "Version": "2012-10-17",
>      "Statement": [
>        {
>          "Effect": "Allow",
>          "Principal": {
>             "Service": "rds.amazonaws.com"
>           },
>          "Action": "sts:AssumeRole"
>        }
>      ] 
>    }'
{
    "Role": {
        "Path": "/",
        "RoleName": "rds-s3-role",
        "RoleId": "AROA2U57KX3NP2XWVCELI",
        "Arn": "arn:aws:iam::xxxxxxxxxx:role/rds-s3-role",
        "CreateDate": "2020-11-10T12:07:20+00:00",
        "AssumeRolePolicyDocument": {
            "Version": "2012-10-17",
            "Statement": [
                {
                    "Effect": "Allow",
                    "Principal": {
                        "Service": "rds.amazonaws.com"
                    },
                    "Action": "sts:AssumeRole"
                }
            ]
        }
    }
}

Attaching the policy to the role (you will need the ARN of the policy from above):

dwe@dwe:~$ aws iam attach-role-policy \
>    --policy-arn arn:aws:iam::xxxxxxxxxx:policy/rds-s3-policy \
>    --role-name rds-s3-role

Finally you need to attach the IAM role to the RDS instance by providing the ARN of the role and the identifier of your RDS instance:

aws rds add-role-to-db-instance \
   --db-instance-identifier dwe-postgres-helvetia \
   --feature-name s3Import \
   --role-arn arn:aws:iam::xxxxxxxx:role/rds-s3-role   \
   --region eu-central-1

Your RDS instance needs to be running to do that, otherwise you’ll get this:

An error occurred (InvalidDBInstanceState) when calling the AddRoleToDBInstance operation: The status for the dwe-postgres DB instance is stopped. The DB instance is not available for s3Import feature.

Having the IAM policy attached to the RDS instance we can load the csv, but first the s3 URI needs to be defined (we do not want to use access keys and credentials):

postgres=> SELECT aws_commons.create_s3_uri('s3-rds-demo'
postgres(>                                 ,'sample.csv'
postgres(>                                 ,'eu-central-1'
postgres(>                                 ) AS s3_uri \gset
postgres=> select :'s3_uri';
               ?column?                
---------------------------------------
 (s3-rds-demo,sample.csv,eu-central-1)
(1 row)

No we are ready to load the file:

postgres=> create table sample ( id int primary key
postgres(>                              , firstname varchar(20)
postgres(>                              , lastname varchar(20)
postgres(>                              , email varchar(20)
postgres(>                              , street varchar(20)
postgres(>                              , country varchar(20)
postgres(>                              , description varchar(20)
postgres(>                              );
CREATE TABLE
postgres=> SELECT aws_s3.table_import_from_s3 ( 'sample'
                                   , ''
                                   , '(format csv)'
                                   , :'s3_uri'
                                   );
                                 table_import_from_s3                                 
--------------------------------------------------------------------------------------
 1000000 rows imported into relation "sample" from file sample.csv of 100222272 bytes
(1 row)
postgres=> select * from sample limit 5;
 id |  firstname  |  lastname  |     email     |  street  |  country  |  description  
----+-------------+------------+---------------+----------+-----------+---------------
 77 | firstname77 | lastname77 | xxx77@xxx.com | street77 | country77 | description77
 78 | firstname78 | lastname78 | xxx78@xxx.com | street78 | country78 | description78
 79 | firstname79 | lastname79 | xxx79@xxx.com | street79 | country79 | description79
  1 | firstname1  | lastname1  | xxx1@xxx.com  | street1  | country1  | description1
  2 | firstname2  | lastname2  | xxx2@xxx.com  | street2  | country2  | description2
(5 rows)

And we’re done. The follow up post will show the opposite: Writing back to to S3 from RDS for PostgreSQL.

Cet article Loading data from S3 to AWS RDS for PostgreSQL est apparu en premier sur Blog dbi services.

Oracle Grid Infrastructure on Windows With 2 Nodes

Wed, 2020-11-11 10:53

Oracle Grid Infrastructure can be also installed on Windows server. In this blog I am explaining how this installation can be done. I am going to install an environment with to nodes. Oracle 19c is being used. We have two servers
winrac1 :
winrac2 :
with the same characteristics


I would like to note that it is just a test environment on virtual machines on VirtualBox. I did not have any dns server and I just have 1 scan address instead of 3 as recommended. Below my hosts files with the IP used

PS C:\Windows\System32\drivers\etc> Get-Content .\hosts | findstr 192
192.168.168.100         winrac1
192.168.168.101         winrac2
192.168.1.100           winrac1-priv
192.168.1.101           winrac2-priv
192.168.168.110         winrac1-vip
192.168.168.111         winrac2-vip
192.168.168.120         winrac-scan
PS C:\Windows\System32\drivers\etc>

The installation user can be local user or a domain user. If he is a local user he should
-be member of Administrators group
-exist on both nodes if he is a local one
-have the same password on both nodes

In my case the Administrator user was used
The oracle grid sofware WINDOWS.X64_193000_grid_home is already downloaded and unpacked to the GRID_HOME

C:\app\19..0.0.0\grid

The shared disks are Disk1 and Disk2 are already presented to both nodes winrac1 and winrac2

We have to disable write caching on each shared disk if supported by the system. For this right click on Disk1 for example and uncheck the Enable write caching on the device on both nodes

The next steps is to create a volume on shared disks. On the first node do following steps for all shared disks. Right click on the shared disk to create a New Simple Volume.






Once done , do a rescan disks on all other nodes


We now have to create logical partition with the shared disks

DISKPART> list disk

  Disk ###  Status         Size     Free     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  Disk 0    Online           60 GB      0 B
* Disk 1    Online         5120 MB  5118 MB
  Disk 2    Online           32 GB    31 GB

DISKPART> select disk 1

Disk 1 is now the selected disk.

DISKPART> create partition extended

DiskPart succeeded in creating the specified partition.

DISKPART> create partition logical

DiskPart succeeded in creating the specified partition.

DISKPART>

DISKPART> select disk 2

Disk 2 is now the selected disk.

DISKPART> create partition extended

DiskPart succeeded in creating the specified partition.

DISKPART> create partition logical

DiskPart succeeded in creating the specified partition.

Do a rescan from all other nodes

Now we are going to prepare our disks to be used with ASM. For this we use the tool asmtoolg.exe. Just launch it on the first node

c:\app\19.0.0.0\grid\bin>c:\app\19.0.0.0\grid\bin\asmtoolg.exe





Repeat these steps for all disks you will use with ASM. You can list your labelled disks with following command

c:\app\19.0.0.0\grid\bin>asmtool.exe  -list
NTFS                             \Device\Harddisk0\Partition1              549M
NTFS                             \Device\Harddisk0\Partition2            60889M
ORCLDISKVOTOCR0                  \Device\Harddisk1\Partition1             5117M
ORCLDISKDATA0                    \Device\Harddisk2\Partition1            32765M

Before launching the installation, we have to set these registry values on both nodes

Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config



And to stop the firewall on both nodes with Administrator

C:\Users\Administrator>netsh advfirewall set allprofiles state off
Ok.

Login with grid and open a terminal with as an Administrator and launch the install command

c:\app\19.0.0.0\grid>gridSetup.bat













As it is just a test environment, I decided to ignore the errors and to continue


The verification failed because of errors I ignored

But the installation is fine

At the end of the installation I can validate the cluster

C:\Users\Administrator>crsctl check cluster -all
**************************************************************
winrac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
winrac2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

C:\Users\Administrator>

The status of the different resources

C:\Users\Administrator>crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       winrac1                  STABLE
               ONLINE  ONLINE       winrac2                  STABLE
ora.net1.network
               ONLINE  ONLINE       winrac1                  STABLE
               ONLINE  ONLINE       winrac2                  STABLE
ora.ons
               ONLINE  ONLINE       winrac1                  STABLE
               ONLINE  ONLINE       winrac2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      winrac1                  STABLE
               OFFLINE OFFLINE      winrac2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       winrac1                  STABLE
      2        ONLINE  ONLINE       winrac2                  STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       winrac1                  STABLE
ora.VOTOCR.dg(ora.asmgroup)
      1        ONLINE  ONLINE       winrac1                  STABLE
      2        ONLINE  ONLINE       winrac2                  STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       winrac1                  Started,STABLE
      2        ONLINE  ONLINE       winrac2                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       winrac1                  STABLE
      2        ONLINE  ONLINE       winrac2                  STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       winrac1                  STABLE
ora.qosmserver
      1        ONLINE  ONLINE       winrac1                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       winrac1                  STABLE
ora.winrac1.vip
      1        ONLINE  ONLINE       winrac1                  STABLE
ora.winrac2.vip
      1        ONLINE  ONLINE       winrac2                  STABLE
--------------------------------------------------------------------------------

C:\Users\Administrator>

The voting disk

C:\Users\Administrator>crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   01909d45161e4f74bfad14dff099dcc0 (\\.\ORCLDISKVOTOCR0) [VOTOCR]
Located 1 voting disk(s).

The OCR

C:\Users\Administrator>ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84300
         Available space (kbytes) :     407384
         ID                       :  461265202
         Device/File Name         :    +VOTOCR
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded


C:\Users\Administrator>
Conclusion

We just see that Oracle Clusterware can be configured in a Windows environement. In coming blogs we will show how to configure database on it. But it will the same that on Linux environment

Cet article Oracle Grid Infrastructure on Windows With 2 Nodes est apparu en premier sur Blog dbi services.

Deploy GitLab on Kubernetes using Helm

Tue, 2020-11-10 05:29


Over the last years GitLab became one of the most popular and powerful open source git source code platform. From pure source code repository to a DevOps platform, it’s interesting to see how GitLab has evolved and this is reflected in the technological choices made by the technical teams. GitLab can be deployed on Cloud or On-premise environments in multiple ways; automatically (packages) manually (compile all sources)…. https://about.gitlab.com/install/

This blog post aims to explain how to deploy GitLab in Kubernetes but also serves as a customer feedback where we are using GitLab deployed on Kubernetes in production.

Prerequisites

  • a Kubernetes cluster up and running
  • an ingress controller configured for external access

In our example, we will use Google Kubernetes Engine (GKE) and Nginx as ingress controller. 

Helm installation

If you already have Helm client and Tiller installed on your cluster, you can skip to the next section.
You can install Helm using the following commands:

➜ curl -o get_helm.sh https://raw.githubusercontent.com/kubernetes/helm/master/scripts/get
chmod 700 get_helm.sh

➜ ./get_helm.sh
helm installed into /usr/local/bin/helm
tiller installed into /usr/local/bin/tiller
Run 'helm init' to configure helm.

All installation methods and the documentation can be found here: https://helm.sh/docs/intro/install/

Installing Tiller
➜ helm init
Creating /Users/meb/.helm
Creating /Users/meb/.helm/repository
Creating /Users/meb/.helm/repository/cache
Creating /Users/meb/.helm/repository/local
Creating /Users/meb/.helm/plugins
Creating /Users/meb/.helm/starters
Creating /Users/meb/.helm/cache/archive
Creating /Users/meb/.helm/repository/repositories.yaml
Adding stable repo with URL: https://kubernetes-charts.storage.googleapis.com
Adding local repo with URL: http://127.0.0.1:8879/charts
$HELM_HOME has been configured at /Users/meb/.helm.

Tiller (the Helm server-side component) has been installed into your Kubernetes Cluster.

 

Deploy GitLab with Helm Storage Class

Let’s first create a storage class in our cluster, GitLab will be able to store data.

cat > pd-ssd-storage.yaml <<EOF
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: pd-ssd
provisioner: kubernetes.io/gce-pd
parameters:
  type: pd-ssd
EOF
kubectl apply -f pd-ssd-storage.yaml

Once done we can install GitLab chart using Helm.

Install the GitLab chart

The GitLab chart requires custom configuration so that it can leverage the external data stores that you previously provisioned.

Download the configuration file template:

wget https://raw.githubusercontent.com/terraform-google-modules/terraform-google-gke-gitlab/master/values.yaml.tpl

After customisation, the final values.yaml file should contains the following:

# Values for gitlab/gitlab chart on GKE
global:
  edition: ce
  hosts:
    domain: xip.io
    https: true
    gitlab: {}
    externalIP: 35.225.196.151 #Replace by your Nginx Ingress ExternalIP
    ssh: ~
    gitlab:
      name: gitlab.xip.io 
      https: true
    registry:
      name: gitlab-registry.xip.io
      https: true
    minio:
      name: gitlab-minio.xip.io
      https: true
  minio:
    enabled: true
  ## doc/charts/globals.md#configure-ingress-settings
  ingress:
    configureCertmanager: false
    class: "nginx"
    enabled: true
    tls:
      enabled: true
certmanager:
  install: false
nginx-ingress:
  enabled: false
prometheus:
  install: true
redis:
  install: true
postgresql:
  install: true
gitlab-runner:
  install: true
registry:
  enable: true

Install the chart by using the following Helm commands:

helm repo add gitlab https://charts.gitlab.io/
helm install -f values.yaml --version 2.3.7 -n gitlab gitlab/gitlab
Verify your deployment

Pod status:

kubectl get pods -n gitlab -o wide
NAME                                        READY   STATUS      RESTARTS   AGE   IP          NODE                                    NOMINATED NODE   READINESS GATES
gitlab-gitaly-0                             1/1     Running     0          1d   10.0.1.18   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-gitlab-exporter-7cf6b77d78-89f46     1/1     Running     0          1d   10.0.2.10   gke-jx-dbi-default-pool-01891a7e-1rb5              
gitlab-gitlab-shell-c548bd566-g6p7b         1/1     Running     0          1d   10.0.1.14   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-gitlab-shell-c548bd566-plg4t         1/1     Running     0          1d   10.0.2.13   gke-jx-dbi-default-pool-01891a7e-1rb5              
gitlab-migrations.1-tf5jl                   0/1     Completed   0          1d   10.0.1.10   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-minio-75567fcbb6-j9z9z               1/1     Running     0          1d   10.0.1.15   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-minio-create-buckets.1-2c8th         0/1     Completed   0          1d   10.0.1.11   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-postgresql-66d8d9574b-r66fm          2/2     Running     0          1d   10.0.2.12   gke-jx-dbi-default-pool-01891a7e-1rb5              
gitlab-prometheus-server-6fb685b9c7-q4jl5   2/2     Running     0          1d   10.0.1.16   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-redis-7668c4d476-nqddk               2/2     Running     0          1d   10.0.1.17   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-registry-5675459cbd-gjnx6            1/1     Running     0          1d   10.0.1.13   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-registry-5675459cbd-rpcn2            1/1     Running     0          1d   10.0.2.11   gke-jx-dbi-default-pool-01891a7e-1rb5              
gitlab-sidekiq-all-in-1-5fdc8f5868-8fnxl    1/1     Running     1          1d   10.0.1.9    gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-task-runner-5cb689799-pzz5r          1/1     Running     0          1d   10.0.1.12   gke-jx-dbi-default-pool-01891a7e-2g7j              
gitlab-unicorn-9f9bf8574-cjqdh              2/2     Running     0          1d   10.0.2.9    gke-jx-dbi-default-pool-01891a7e-1rb5              
gitlab-unicorn-9f9bf8574-mfqrn              2/2     Running     0          1d   10.0.0.11   gke-jx-dbi-default-pool-01891a7e-715p              

Services:

kubectl get svc -n gitlab
NAME                       TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)             AGE
gitlab-gitaly              ClusterIP   None                   8075/TCP,9236/TCP   1d
gitlab-gitlab-exporter     ClusterIP   10.3.254.216           9168/TCP            1d
gitlab-gitlab-shell        ClusterIP   10.3.242.235           22/TCP              1d
gitlab-minio-svc           ClusterIP   10.3.244.99            9000/TCP            1d
gitlab-postgresql          ClusterIP   10.3.240.220           5432/TCP            1d
gitlab-prometheus-server   ClusterIP   10.3.255.170           80/TCP              1d
gitlab-redis               ClusterIP   10.3.240.239           6379/TCP,9121/TCP   1d
gitlab-registry            ClusterIP   10.3.247.123           5000/TCP            1d
gitlab-unicorn             ClusterIP   10.3.253.173           8080/TCP,8181/TCP   1d

Ingress resources:

kubectl get ing -n gitlab
NAME              HOSTS                          ADDRESS          PORTS     AGE
gitlab-minio      gitlab-minio.xip.io            35.225.196.151   80, 443   1d
gitlab-registry   gitlab-registry.xip.io         35.225.196.151   80, 443   1d
gitlab-unicorn    gitlab.35.225.196.151.xip.io   35.225.196.151   80, 443   1d
Connect to GitLab Web UI

Once your GitLab instance is up and running, log int to it as follows:

1. Get the URL for your GitLab server:

export GITLAB_HOSTNAME=$(kuexport GITLAB_HOSTNAME=$(kubectl get ingresses.extensions gitlab-unicorn \
    -o jsonpath='{.spec.rules[0].host}')
echo "Your GitLab URL is: https://${GITLAB_HOSTNAME}"

Your GitLab URL is: https://gitlab.35.225.196.151.xip.io

2. Get the root password:

kubectl get secret gitlab-gkubectl get secret gitlab-gitlab-initial-root-password \
    -o go-template='{{.data.password}}' | base64 -d && echo
fZHnoz0H5p4exjBbUCqHvB13JIvIwepOOgolCtaS8XhdXbeL0akUJT6ZtViksnYF

Visit the GitLab URL in your browser from step 1 and log in as the root user with the password from step 2. Below the screenshot from the login page.

Cet article Deploy GitLab on Kubernetes using Helm est apparu en premier sur Blog dbi services.

PostgreSQL in AWS: clearing the doubts

Mon, 2020-11-09 03:53
By Franck Pachot

.
I’ve heard and read people saying that the PostgreSQL managed service is not the true open-source PostgreSQL from the community.
This is wrong and I’m writing this post to clarify it.

PostgreSQL on EC2

Obviously, you can install PostgreSQL on an EC2 instance, as a database running on IaaS (Infrastructure as a Service). You have the full choice of version, you can even compile it from sources, and add whatever extensions you want. This has the lowest cost because PostgreSQL is free of any subscription. But you need to do all the “Ops” work (so the TCO may be higher than what you think). Please take care of your backups if you do that. There’s a trend to build microservices with the database embedded with the stateless application and people forget that the database is a stateful component (we called that persistent 15 years ago, or durable 30 years ago) that cannot be stopped and started elsewhere. But if you consider cloud as a hosting solution, installing PostgreSQL in EC2 + EBS is a valid solution. There are no doubts about this: you run the community postgres.

Managed PostgreSQL on RDS

Here is where I’ve heard some wrong messages, so let’s be clear: Amazon RDS for PostgreSQL is running the real PostgreSQL, compiled from the postgres community sources. RDS is the family name for all managed relational databases and this includes Open Source databases (PostgreSQL, MySQL, MariaDB), some commercial databases (Oracle Database, Microsoft SQL Server), and Amazon Aurora (I will talk about it later). Here is how you create a PostgreSQL database in RDS: you select “PostgreSQL” with the PostgreSQL logo and can choose mostly any supported version (at the time of writing this: any minor version between 9.5.2 to 12.4):

There is no ambiguity there: only one service has the PostgreSQL name and logo. You cannot mistakenly select Aurora here. If you create an Amazon RDS PostgreSQL service, you have the “real” PostgreSQL. And you can even do that on the Free tier.

You can check the version and compilation:


$ PGHOST=database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com PGPORT=5432 PGPASSWORD=postgres psql -U postgres

psql (12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

The only difference in the features is that, as it is a managed database, you don’t have all privileges:


postgres=> \du
                                                                     List of roles
    Role name    |                         Attributes                         |                          Member of

-----------------+------------------------------------------------------------+---------------------------------------------------
 postgres        | Create role, Create DB                                    +| {rds_superuser}
                 | Password valid until infinity                              |
 rds_ad          | Cannot login                                               | {}
 rds_iam         | Cannot login                                               | {}
 rds_password    | Cannot login                                               | {}
 rds_replication | Cannot login                                               | {}
 rds_superuser   | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                 | Password valid until infinity                              |
 rdsrepladmin    | No inheritance, Cannot login, Replication                  | {}

postgres=> select * from pg_hba_file_rules;

 line_number | type  |   database    | user_name  | address  | netmask | auth_method | options | error
-------------+-------+---------------+------------+----------+---------+-------------+---------+-------
           4 | local | {all}         | {all}      |          |         | md5         |         |
          10 | host  | {all}         | {rdsadmin} | samehost |         | md5         |         |
          11 | host  | {all}         | {rdsadmin} | 0.0.0.0  | 0.0.0.0 | reject      |         |
          12 | host  | {rdsadmin}    | {all}      | all      |         | reject      |         |
          13 | host  | {all}         | {all}      | 0.0.0.0  | 0.0.0.0 | md5         |         |
          14 | host  | {replication} | {all}      | samehost |         | md5         |         |

But this is exactly the same as a PostgreSQL installed from the community sources where you are not the superuser.

There are a few additional RDS specific libraries (which are not open source):


postgres=> show shared_preload_libraries;

  shared_preload_libraries
-----------------------------
 rdsutils,pg_stat_statements

postgres=> select name,setting from pg_settings where name like 'rds.%';

                  name                  |                                                                                                                                                                                                                                                                                                                                                                                          setting                                                                                                                                                                                                                                                                                            
----------------------------------------+------------------------------------------------------------------------
 rds.extensions                         | address_standardizer, address_standardizer_data_us, amcheck, aws_commons, aws_s3, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hll, hstore, hstore_plperl, intagg, intarray, ip4r, isn, jsonb_plperl, log_fdw, ltree, orafce, pageinspect, pgaudit, pgcrypto, pglogical, pgrouting, pgrowlocks, pgstattuple, pgtap, pg_buffercache, pg_freespacemap, pg_hint_plan, pg_prewarm, pg_proctab, pg_repack, pg_similarity, pg_stat_statements, pg_transport, pg_trgm, pg_visibility, plcoffee, plls, plperl, plpgsql, plprofiler, pltcl, plv8, postgis, postgis_tiger_geocoder, postgis_raster, postgis_topology, postgres_fdw, prefix, rdkit, sslinfo, tablefunc, test_parser, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp
 rds.force_admin_logging_level          | disabled
 rds.force_autovacuum_logging_level     | info
 rds.internal_databases                 | rdsadmin,template0
 rds.logical_replication                | off
 rds.rds_superuser_reserved_connections | 2
 rds.restrict_logical_slot_creation     | off
 rds.restrict_password_commands         | off
 rds.superuser_variables                | session_replication_role
 rds.tablespace_path_prefix             | /rdsdbdata/db/base/tablespace

This is still the community edition that allows extensibility.
There are also some additional functionalities, like RDS Performance Insights to show the database activity with a time x-axis and active session y-axis, drilling down to wait events.

By curiosity I’ve run the regression tests that are provided by the PostgreSQL distribution:


PGHOST=database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com PGPORT=5432 PGPASSWORD=postgres psql -U postgres -c "select version();"
cd /var/tmp
git clone --branch REL_12_STABLE https://github.com/postgres/postgres.git
cd postgres
./configure
cd src/test/regress
export PGHOST=database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com
export PGPORT=5432
export PGPASSWORD=postgres
export PGUSER=postgres
make installcheck

It starts by setting some parameters, which fails for missing privileges:


============== dropping database "regression"         ==============
DROP DATABASE
============== creating database "regression"         ==============
CREATE DATABASE
ERROR:  permission denied to set parameter "lc_messages"
command failed: "/usr/local/pgsql/bin/psql" -X -c "ALTER DATABASE \"regression\" SET lc_messages TO 'C';ALTER DATABASE \"regression\" SET lc_m
onetary TO 'C';ALTER DATABASE \"regression\" SET lc_numeric TO 'C';ALTER DATABASE \"regression\" SET lc_time TO 'C';ALTER DATABASE \"regressio
n\" SET bytea_output TO 'hex';ALTER DATABASE \"regression\" SET timezone_abbreviations TO 'Default';" "regression"
make: *** [installcheck] Error 2

In a managed database, the cloud provider needs to lockdown some administration commands in order to secure his platform, but we have the possibility to define those parameters by creating a parameter group. This is what I did in the console, and then removed those ALTER DATABASE from pg_regress.c:


sed -ie 's/"ALTER DATABASE/--&/' pg_regress.c

Then, I’m ready to run the regression tests:


[opc@a regress]$ make installcheck

...

../../../src/test/regress/pg_regress --inputdir=. --bindir='/usr/local/pgsql/bin'    --dlpath=. --max-concurrent-tests=20  --schedule=./serial_schedule
(using postmaster on database-1.ce5fwv4akhjp.eu-central-1.rds.amazonaws.com, port 5432)
============== dropping database "regression"         ==============
DROP DATABASE
============== creating database "regression"         ==============
CREATE DATABASE
============== running regression test queries        ==============
test tablespace                   ... FAILED     1406 ms
test boolean                      ... ok          684 ms
test char                         ... ok          209 ms
test name                         ... ok          311 ms
test varchar                      ... ok          201 ms
test text                         ... ok          549 ms
test int2                         ... ok          354 ms
test int4                         ... ok          580 ms
test int8                         ... ok          954 ms
test oid                          ... ok          222 ms
test float4                       ... FAILED      660 ms
test float8                       ... FAILED     1136 ms
test bit                          ... ok         1751 ms
test numeric                      ... ok         5388 ms

...

test hash_part                    ... ok          240 ms
test indexing                     ... FAILED     6406 ms
test partition_aggregate          ... ok         1569 ms
test partition_info               ... ok          620 ms
test event_trigger                ... FAILED     1237 ms
test fast_default                 ... ok         1990 ms
test stats                        ... FAILED      643 ms

======================================================
 90 of 194 tests failed, 1 of these failures ignored.
======================================================

The differences that caused some tests to fail can be viewed in the
file "/var/tmp/postgres/src/test/regress/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/var/tmp/postgres/src/test/regress/regression.out".

There are multiple tests that fail because of missing privileges. Actually, pg_regression expects to have all privileges. Using pg_regress is not a good idea for testing that the RDS PostgreSQL behaves like expected. It is not made for functional tests.

PostgreSQL-like API in on RDS Aurora

This is where the confusion comes from. Aurora is a proprietary database built by Amazon. They started it by forking MySQL and modifying the storage layer in order to build a cloud-native database. Rather than storing the database files in EBS block storage attached to the database node where the instance is running, like all other RDS databases, the database service is split into separate (micro)services for the compute (EC2) and the storage (distributed over multiple AZ to provide High Availability, similar to the DynamoDB storage). Aurora code is not open-source and is very different from the community MySQL. More and more because of many improvements. Part of it is running on EC2 to parse, optimize and execute SQL statements and transactions, and update the buffers in cache. And part of it runs in the storage server which receives the redo to apply it on the data file blocks, which are distributed and shared with reader instances. This was in 2014 and Amazon always presented Aurora as another database engine in RDS. I’ve copy-pasted below a few archive.org snapshot of the https://aws.amazon.com/rds/ page if you want to look at the history. Amazon modified a lot the lower layers of the code but kept the upper layer to stay compatible with MySQL (version 5) in order to ease the application migration to Aurora. And this is how it is presented: Aurora is a new database with MySQL compatibility. Once you select the Aurora service in RDS, you can choose the version which mentions with MySQL version it is compatible with. For example, the latest version Aurora 2.09.. is labelled “Aurora (MySQL 5.7)”. Note that there are not only modification to adapt to the cloud native storage, but Amazon brings also some interesting improvement, unfortunately not given back to the open-source community.

Here are the screenshots from archive.org where you see from the begining that RDS for Aurora is different than RDS for MySQL. And when MySQL or PostgreSQL is mentioned with Aurora there’s always the “compatible” mention:

What I mean by this is that, in my opinion, there was no ambiguity from Amazon about what is the open-source database and what is their proprietary engine.

That’s a long story about the MySQL compatible Aurora because it was the only compatible API from 2014 to 2017. Then, as they had a strong storage engine, and a well layered code, they have built another flavor of Aurora taking the PostgreSQL upper layer to replace the MySQL one in Aurora. Then they have two proprietary databases: Aurora with MySQL compatibility and Aurora with PostgreSQL compatibility. As far as I know it has always been clear that it is only “compatibility” and Aurora has never been advertised to be a real PostgreSQL engine. They have RDS PostgreSQL for that. And I think they have different use cases. Aurora is probably the best choice when High Availability, scalability and elasticity is the most important. PostgreSQL may provide lower latency on block storage, and is probably cheaper (but Aurora serverless can also reduce the cost for rarely used databases).

However, there is still some confusion and some Aurora users are asking the PostgreSQL community for help. The PostgreSQL community helps a lot their users because they know the engine (which is very well documented, and source code and source comments are accessible). But they cannot do anything for Aurora as we don’t even know what has been modified. As an example, I mentioned in a previous post that some wait events are specific au Aurora – don’t ask the postgres mailing lists for that. I also think that the PostgreSQL community would appreciate that the improvements made by Amazon on the PostgreSQL code are shared with the community.

PostgreSQL on other public clouds

No only Amazon RDS PostgreSQL is the real PostgreSQL from the community, but it is also in my opinion one of the most advanced managed service for it.

  • Oracle Cloud provides no managed service for PostgreSQL but only a Bitnami image. I hope one day we will have a managed service with the same quality as the MySQL one.
  • Azure provides an old service running on Windows but that will change as they bought Citus Data and hired well known PsotgreSQL contributors.
  • Google Cloud DB has a decent managed service, but I need to look at their backups
  • .

Talking about backups, Amazon RDS PostgreSQL has all features to avoid or recover from mistakes, which I listed in a 5-points tweet:
https://twitter.com/FranckPachot/status/1325565762946396162?s=20

Cet article PostgreSQL in AWS: clearing the doubts est apparu en premier sur Blog dbi services.

Patching Oracle Database Appliance to 19.9

Thu, 2020-11-05 03:35

ODA 19.9 has just been released for Bare Metal yesterday, and I had the opportunity to already patch a customer production ODA to this latest version. Through this blog I wanted to share my experience on patching an ODA to 19.9 as well as a new tricky skip-orachk option.

Patching requirement

To patch the Bare Metal ODA to 19.9 version (patch 31922078), we need to be in either 19.5, 19.6, 19.7 or 19.8 version. This is described in the ODA documentation.

First of all we need to ensure we have enough space on /, /u01 and /opt file systems. At least 20 GB should be available. If not, we can do some cleaning or extend the LVM partitions.

[root@ODA01 /]# df -h / /u01 /opt
Filesystem                          Size  Used Avail Use% Mounted on
/dev/mapper/VolGroupSys-LogVolRoot   30G  9.5G   19G  34% /
/dev/mapper/VolGroupSys-LogVolU01    99G   55G   40G  59% /u01
/dev/mapper/VolGroupSys-LogVolOpt    75G   43G   29G  60% /opt



Then we will check that no hardware failure is existing on the ODA. This can be checked with the ILOM GUI or using a ssh connection on the ILOM :

-> show /SP/faultmgmt

 /SP/faultmgmt
    Targets:
        shell

    Properties:

    Commands:
        cd
        show

-> start /SP/faultmgmt/shell
Are you sure you want to start /SP/faultmgmt/shell (y/n)? y

faultmgmtsp> fmadm faulty
No faults found



Recommendation is to use odabr tool and perform a snapshot backup :

[root@ODA01 /]# /opt/odabr/odabr backup -snap
INFO: 2020-11-04 16:30:42: Please check the logfile '/opt/odabr/out/log/odabr_37159.log' for more details


--------------------------------------------------------
odabr - ODA node Backup Restore
Author: Ruggero Citton 
RAC Pack, Cloud Innovation and Solution Engineering Team
Copyright Oracle, Inc. 2013, 2019
Version: 2.0.1-47
--------------------------------------------------------

INFO: 2020-11-04 16:30:42: Checking superuser
INFO: 2020-11-04 16:30:42: Checking Bare Metal
INFO: 2020-11-04 16:30:42: Removing existing LVM snapshots
WARNING: 2020-11-04 16:30:42: LVM snapshot for 'opt' does not exist
WARNING: 2020-11-04 16:30:42: LVM snapshot for 'u01' does not exist
WARNING: 2020-11-04 16:30:42: LVM snapshot for 'root' does not exist
INFO: 2020-11-04 16:30:42: Checking LVM size
INFO: 2020-11-04 16:30:42: Doing a snapshot backup only
INFO: 2020-11-04 16:30:42: Boot device backup
INFO: 2020-11-04 16:30:42: ...getting boot device
INFO: 2020-11-04 16:30:42: ...making boot device backup
INFO: 2020-11-04 16:30:44: ...boot device backup saved as '/opt/odabr/out/hbi/boot.img'
INFO: 2020-11-04 16:30:44: Getting EFI device
INFO: 2020-11-04 16:30:44: ...making efi device backup
INFO: 2020-11-04 16:30:46: EFI device backup saved as '/opt/odabr/out/hbi/efi.img'
INFO: 2020-11-04 16:30:46: OCR backup
INFO: 2020-11-04 16:30:47: ...ocr backup saved as '/opt/odabr/out/hbi/ocrbackup_37159.bck'
INFO: 2020-11-04 16:30:47: Making LVM snapshot backup
SUCCESS: 2020-11-04 16:30:49: ...snapshot backup for 'opt' created successfully
SUCCESS: 2020-11-04 16:30:49: ...snapshot backup for 'u01' created successfully
SUCCESS: 2020-11-04 16:30:49: ...snapshot backup for 'root' created successfully
SUCCESS: 2020-11-04 16:30:49: LVM snapshots backup done successfully

[root@ODA01 /]# /opt/odabr/odabr infosnap

--------------------------------------------------------
odabr - ODA node Backup Restore
Author: Ruggero Citton 
RAC Pack, Cloud Innovation and Solution Engineering Team
Copyright Oracle, Inc. 2013, 2019
Version: 2.0.1-47
--------------------------------------------------------


LVM snap name         Status                COW Size              Data%
-------------         ----------            ----------            ------
root_snap             active                30.00 GiB             0.01%
opt_snap              active                60.00 GiB             0.01%
u01_snap              active                100.00 GiB            0.01%



We can as well run orachk excluding the rdbms checks :

[root@ODA01 /]# cd /opt/oracle/dcs/oracle.ahf/orachk

[root@ODA01 orachk]# ./orachk -nordbms

.  .  .  .  .  .

Either Cluster Verification Utility pack (cvupack) does not exist at /opt/oracle/dcs/oracle.ahf/common/cvu or it is an old or invalid cvupack

Checking Cluster Verification Utility (CVU) version at CRS Home - /u01/app/19.0.0.0/grid

This version of Cluster Verification Utility (CVU) was released on 10-Mar-2020 and it is older than 180 days. It is highly recommended that you download the latest version of CVU from MOS patch 30166242 to ensure the highest level of accuracy of the data contained within the report

Do you want to download latest version of Cluster Verification Utility (CVU) from my oracle support? [y/n] [y] n

Running older version of Cluster Verification Utility (CVU) from CRS Home - /u01/app/19.0.0.0/grid

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS on oda01

.
.  .  . . . .  .  .  .
-------------------------------------------------------------------------------------------------------
                                                 Oracle Stack Status
-------------------------------------------------------------------------------------------------------
  Host Name       CRS Installed  RDBMS Installed    CRS UP    ASM UP  RDBMS UP    DB Instance Name
-------------------------------------------------------------------------------------------------------
oda01               Yes           No          Yes      Yes       No
-------------------------------------------------------------------------------------------------------
.
.  .  .  .  .  .


.
.
.
.

.



*** Checking Best Practice Recommendations ( Pass / Warning / Fail ) ***


Collections and audit checks log file is
/opt/oracle/dcs/oracle.ahf/data/oda01/orachk/orachk_oda01_110420_163217/log/orachk.log

============================================================
           Node name - oda01
============================================================

 Collecting - ASM Disk Group for Infrastructure Software and Configuration
 Collecting - ASM Diskgroup Attributes
 Collecting - ASM initialization parameters
 Collecting - Disk I/O Scheduler on Linux
 Collecting - Interconnect network card speed
 Collecting - Kernel parameters
 Collecting - Maximum number of semaphore sets on system
 Collecting - Maximum number of semaphores on system
 Collecting - Maximum number of semaphores per semaphore set
 Collecting - OS Packages
 Collecting - Patches for Grid Infrastructure
 Collecting - number of semaphore operations per semop system call
 Collecting - CRS user limits configuration
 Collecting - Database Server Infrastructure Software and Configuration
 Collecting - umask setting for GI owner

Data collections completed. Checking best practices on oda01.
------------------------------------------------------------

 INFO =>     Oracle Database Appliance Best Practice References
 INFO =>     Oracle Data Pump Best practices.
 INFO =>     Important Storage Minimum Requirements for Grid & Database Homes
 WARNING =>  soft or hard memlock are not configured according to recommendation
 INFO =>     CSS disktimeout is not set to the default value
 WARNING =>  OCR is not being backed up daily
 INFO =>     CSS misscount is not set to the default value of 30
 INFO =>     Jumbo frames (MTU >= 9000) are not configured for interconnect
 INFO =>     Information about hanganalyze and systemstate dump
 WARNING =>  One or more diskgroups from v$asm_diskgroups are not registered in clusterware registry
Best Practice checking completed. Checking recommended patches on oda01
--------------------------------------------------------------------------------
Collecting patch inventory on CRS_HOME /u01/app/19.0.0.0/grid
Collecting patch inventory on ASM_HOME /u01/app/19.0.0.0/grid

------------------------------------------------------------
                      CLUSTERWIDE CHECKS
------------------------------------------------------------

------------------------------------------------------------
Detailed report (html) -  /opt/oracle/dcs/oracle.ahf/data/oda01/orachk/orachk_oda01_110420_163217/orachk_oda01_110420_163217.html

UPLOAD [if required] - /opt/oracle/dcs/oracle.ahf/data/oda01/orachk/orachk_oda01_110420_163217.zip

Then we need to ensure to have a good backup for the opened databases that will run on the ODA. If we are patching an ODA having High Availability (Data Guard for EE edition or dbvisit for SE edition), we will ensure to have run switchover and have only standby databases running on the ODA. And we need to stop the databases’ synchronization in that case.

Patching the ODA to 19.9

Once this requirements are met, we can start the patching.

We first need to unzip the downloaded patch files. The patch 31922078 files will be downloaded from oracle web support portal.

[root@ODA01 orachk]# cd /u01/app/patch/

[root@ODA01 patch]# ls -ltrh
total 16G
-rw-r--r-- 1 root root 6.7G Nov  4 14:11 p31922078_199000_Linux-x86-64_2of2.zip
-rw-r--r-- 1 root root 9.2G Nov  4 15:17 p31922078_199000_Linux-x86-64_1of2.zip

[root@ODA01 patch]# unzip p31922078_199000_Linux-x86-64_1of2.zip
Archive:  p31922078_199000_Linux-x86-64_1of2.zip
 extracting: oda-sm-19.9.0.0.0-201023-server1of2.zip
  inflating: README.txt
  
[root@ODA01 patch]# unzip p31922078_199000_Linux-x86-64_2of2.zip
Archive:  p31922078_199000_Linux-x86-64_2of2.zip
 extracting: oda-sm-19.9.0.0.0-201023-server2of2.zip

[root@ODA01 patch]# ls -ltrh
total 32G
-rw-r--r-- 1 root root 9.2G Oct 29 04:51 oda-sm-19.9.0.0.0-201023-server1of2.zip
-rw-r--r-- 1 root root 6.7G Oct 29 04:53 oda-sm-19.9.0.0.0-201023-server2of2.zip
-rw-r--r-- 1 root root  190 Oct 29 06:17 README.txt
-rw-r--r-- 1 root root 6.7G Nov  4 14:11 p31922078_199000_Linux-x86-64_2of2.zip
-rw-r--r-- 1 root root 9.2G Nov  4 15:17 p31922078_199000_Linux-x86-64_1of2.zip

[root@ODA01 patch]# rm -f p31922078_199000_Linux-x86-64_2of2.zip

[root@ODA01 patch]# rm -f p31922078_199000_Linux-x86-64_1of2.zip



We can then update the ODA repository with the patch files :

[root@ODA01 patch]# odacli update-repository -f /u01/app/patch/oda-sm-19.9.0.0.0-201023-server1of2.zip
{
  "jobId" : "0c23cb4e-2455-4ad2-832b-168edce2f40c",
  "status" : "Created",
  "message" : "/u01/app/patch/oda-sm-19.9.0.0.0-201023-server1of2.zip",
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 16:55:52 PM CET",
  "resourceList" : [ ],
  "description" : "Repository Update",
  "updatedTime" : "November 04, 2020 16:55:52 PM CET"
}

[root@ODA01 patch]# odacli describe-job -i "0c23cb4e-2455-4ad2-832b-168edce2f40c"

Job details
----------------------------------------------------------------
                     ID:  0c23cb4e-2455-4ad2-832b-168edce2f40c
            Description:  Repository Update
                 Status:  Success
                Created:  November 4, 2020 4:55:52 PM CET
                Message:  /u01/app/patch/oda-sm-19.9.0.0.0-201023-server1of2.zip

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------

[root@ODA01 patch]# odacli update-repository -f /u01/app/patch/oda-sm-19.9.0.0.0-201023-server2of2.zip
{
  "jobId" : "04ecd45d-6b92-475c-acd9-202f0137474f",
  "status" : "Created",
  "message" : "/u01/app/patch/oda-sm-19.9.0.0.0-201023-server2of2.zip",
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 16:58:05 PM CET",
  "resourceList" : [ ],
  "description" : "Repository Update",
  "updatedTime" : "November 04, 2020 16:58:05 PM CET"
}

[root@ODA01 patch]# odacli describe-job -i "04ecd45d-6b92-475c-acd9-202f0137474f"

Job details
----------------------------------------------------------------
                     ID:  04ecd45d-6b92-475c-acd9-202f0137474f
            Description:  Repository Update
                 Status:  Success
                Created:  November 4, 2020 4:58:05 PM CET
                Message:  /u01/app/patch/oda-sm-19.9.0.0.0-201023-server2of2.zip

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------

[root@ODA01 patch]# odacli list-jobs | head -n 3;  odacli list-jobs | tail -n 3

ID                                       Description                                                                 Created                             Status
---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------
0c23cb4e-2455-4ad2-832b-168edce2f40c     Repository Update                                                           November 4, 2020 4:55:52 PM CET     Success
04ecd45d-6b92-475c-acd9-202f0137474f     Repository Update                                                           November 4, 2020 4:58:05 PM CET     Success



We can already clean up the patch folder as the files are not needed any more :

[root@ODA01 patch]# ls -ltrh
total 16G
-rw-r--r-- 1 root root 9.2G Oct 29 04:51 oda-sm-19.9.0.0.0-201023-server1of2.zip
-rw-r--r-- 1 root root 6.7G Oct 29 04:53 oda-sm-19.9.0.0.0-201023-server2of2.zip
-rw-r--r-- 1 root root  190 Oct 29 06:17 README.txt

[root@ODA01 patch]# rm -f *.zip

[root@ODA01 patch]# rm -f README.txt



We will check the current version and available new version :

[root@ODA01 patch]# odacli describe-component
System Version
---------------
19.6.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       19.6.0.0.0            19.9.0.0.0
GI                                        19.6.0.0.200114       19.9.0.0.201020
DB                                        18.7.0.0.190716       18.12.0.0.201020
DCSAGENT                                  19.6.0.0.0            19.9.0.0.0
ILOM                                      4.0.4.51.r133528      5.0.1.21.r136383
BIOS                                      52021000              52030400
OS                                        7.7                   7.8
FIRMWARECONTROLLER                        VDV1RL02              VDV1RL04
FIRMWAREDISK                              1102                  1132
HMP                                       2.4.5.0.1             2.4.7.0.1



I’m usually stopping the databases at that time. It is not mandatory, but I personally prefer. This can be achieved by stopping each database with srvctl stop database command or srvctl stop home command to stop all databases from same rdbms home.

Now we can update the dcs-agent :

[root@ODA01 patch]# /opt/oracle/dcs/bin/odacli update-dcsagent -v 19.9.0.0.0
{
  "jobId" : "fa6c5e53-b0b7-470e-b856-ccf19a0305ef",
  "status" : "Created",
  "message" : "Dcs agent will be restarted after the update. Please wait for 2-3 mins before executing the other commands",
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 17:02:53 PM CET",
  "resourceList" : [ ],
  "description" : "DcsAgent patching",
  "updatedTime" : "November 04, 2020 17:02:53 PM CET"
}

[root@ODA01 patch]# odacli describe-job -i "fa6c5e53-b0b7-470e-b856-ccf19a0305ef"

Job details
----------------------------------------------------------------
                     ID:  fa6c5e53-b0b7-470e-b856-ccf19a0305ef
            Description:  DcsAgent patching
                 Status:  Success
                Created:  November 4, 2020 5:02:53 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
dcs-agent upgrade  to version 19.9.0.0.0 November 4, 2020 5:02:53 PM CET     November 4, 2020 5:04:28 PM CET     Success
Update System version                    November 4, 2020 5:04:28 PM CET     November 4, 2020 5:04:28 PM CET     Success



We will now update the DCS admin :

[root@ODA01 patch]# /opt/oracle/dcs/bin/odacli update-dcsadmin -v 19.9.0.0.0
{
  "jobId" : "bdcbda55-d325-44ca-8bed-f0b15eeacfae",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 17:04:57 PM CET",
  "resourceList" : [ ],
  "description" : "DcsAdmin patching",
  "updatedTime" : "November 04, 2020 17:04:57 PM CET"
}

[root@ODA01 patch]# odacli describe-job -i "bdcbda55-d325-44ca-8bed-f0b15eeacfae"

Job details
----------------------------------------------------------------
                     ID:  bdcbda55-d325-44ca-8bed-f0b15eeacfae
            Description:  DcsAdmin patching
                 Status:  Success
                Created:  November 4, 2020 5:04:57 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Patch location validation                November 4, 2020 5:04:58 PM CET     November 4, 2020 5:04:58 PM CET     Success
dcs-admin upgrade                        November 4, 2020 5:04:58 PM CET     November 4, 2020 5:05:04 PM CET     Success



We will update the DCS components :

[root@ODA01 patch]# /opt/oracle/dcs/bin/odacli update-dcscomponents -v 19.9.0.0.0
{
  "jobId" : "4782c035-86fd-496b-b9f1-1055d77071b3",
  "status" : "Success",
  "message" : null,
  "reports" : null,
  "createTimestamp" : "November 04, 2020 17:05:48 PM CET",
  "description" : "Job completed and is not part of Agent job list",
  "updatedTime" : "November 04, 2020 17:05:48 PM CET"
}



We will run the prepatch report :

[root@ODA01 patch]# /opt/oracle/dcs/bin/odacli create-prepatchreport -s -v 19.9.0.0.0

Job details
----------------------------------------------------------------
                     ID:  d836f326-aba3-44e6-9be4-aaa031b5d730
            Description:  Patch pre-checks for [OS, ILOM, GI, ORACHKSERVER]
                 Status:  Created
                Created:  November 4, 2020 5:07:37 PM CET
                Message:  Use 'odacli describe-prepatchreport -i d836f326-aba3-44e6-9be4-aaa031b5d730' to check details of results

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------

And we will check the report :

[root@ODA01 patch]# odacli describe-prepatchreport -i d836f326-aba3-44e6-9be4-aaa031b5d730

Patch pre-check report
------------------------------------------------------------------------
                 Job ID:  d836f326-aba3-44e6-9be4-aaa031b5d730
            Description:  Patch pre-checks for [OS, ILOM, GI, ORACHKSERVER]
                 Status:  FAILED
                Created:  November 4, 2020 5:07:37 PM CET
                 Result:  One or more pre-checks failed for [ORACHK]

Node Name
---------------
ODA01

Pre-Check                      Status   Comments
------------------------------ -------- --------------------------------------
__OS__
Validate supported versions     Success   Validated minimum supported versions.
Validate patching tag           Success   Validated patching tag: 19.9.0.0.0.
Is patch location available     Success   Patch location is available.
Verify OS patch                 Success   Verified OS patch
Validate command execution      Success   Validated command execution

__ILOM__
Validate supported versions     Success   Validated minimum supported versions.
Validate patching tag           Success   Validated patching tag: 19.9.0.0.0.
Is patch location available     Success   Patch location is available.
Checking Ilom patch Version     Success   Successfully verified the versions
Patch location validation       Success   Successfully validated location
Validate command execution      Success   Validated command execution

__GI__
Validate supported GI versions  Success   Validated minimum supported versions.
Validate available space        Success   Validated free space under /u01
Is clusterware running          Success   Clusterware is running
Validate patching tag           Success   Validated patching tag: 19.9.0.0.0.
Is system provisioned           Success   Verified system is provisioned
Validate ASM in online          Success   ASM is online
Validate minimum agent version  Success   GI patching enabled in current
                                          DCSAGENT version
Validate GI patch metadata      Success   Validated patching tag: 19.9.0.0.0.
Validate clones location exist  Success   Validated clones location
Is patch location available     Success   Patch location is available.
Patch location validation       Success   Successfully validated location
Patch verification              Success   Patches 31771877 not applied on GI
                                          home /u01/app/19.0.0.0/grid on node
                                          ODA01
Validate Opatch update          Success   Successfully updated the opatch in
                                          GiHome /u01/app/19.0.0.0/grid on node
                                          ODA01
Patch conflict check            Success   No patch conflicts found on GiHome
                                          /u01/app/19.0.0.0/grid on node
                                          ODA01
Validate command execution      Success   Validated command execution

__ORACHK__
Running orachk                  Failed    Orachk validation failed: .
Validate command execution      Success   Validated command execution
Software home                   Failed    Software home check failed



The prepatch report has been failing on orachk and the software home part. In the html report from orachk I could check and see that the software home check is failing due to missing files :

FAIL => Software home check failed
 
Error Message:
File "/u01/app/19.0.0.0/grid/jdk/jre/lib/amd64/libjavafx_font_t2k.so" could not be verified on node "oda01". OS error: "No such file or directory"
Error Message:
File "/u01/app/19.0.0.0/grid/jdk/jre/lib/amd64/libkcms.so" could not be verified on node "oda01". OS error: "No such file or directory"
Error Message:
File "/u01/app/19.0.0.0/grid/rdbms/lib/ksms.o" could not be verified on node "oda01". OS error: "No such file or directory"



This files are expected during orachk check as referenced in the XML files :

[root@ODA01 ~]# grep ksms /u01/app/19.0.0.0/grid/cv/cvdata/ora_software_cfg.xml
         <File Path="rdbms/lib/" Name="ksms.o" Permissions="644"/>
         <File Path="bin/" Name="genksms" Permissions="755"/>
         <File Path="rdbms/lib/" Name="genksms.o"/>
         <File Path="rdbms/lib/" Name="ksms.o" Permissions="644"/>
         <File Path="bin/" Name="genksms" Permissions="755"/>
         <File Path="rdbms/lib/" Name="genksms.o"/>
         <File Path="rdbms/lib/" Name="ksms.o" Permissions="644"/>
         <File Path="bin/" Name="genksms" Permissions="755"/>
         <File Path="rdbms/lib/" Name="genksms.o"/>
		 
[root@ODA01 ~]# grep ksms /u01/app/19.0.0.0/grid/cv/cvdata/19/ora_software_cfg.xml
         <File Path="rdbms/lib/" Name="ksms.o" Permissions="644"/>
         <File Path="rdbms/lib/" Name="genksms.o"/>
         <File Path="bin/" Name="genksms" Permissions="755"/>
         <File Path="rdbms/lib/" Name="ksms.o" Permissions="644"/>
         <File Path="rdbms/lib/" Name="genksms.o"/>
         <File Path="bin/" Name="genksms" Permissions="755"/>
         <File Path="rdbms/lib/" Name="ksms.o" Permissions="644"/>
         <File Path="rdbms/lib/" Name="genksms.o"/>
         <File Path="bin/" Name="genksms" Permissions="755"/>



I find following MOS note that can be related to same problem : File “$GRID_HOME/rdbms/lib/ksms.o” could not be verified on node (Doc ID 1908505.1).

As per this note we can ignore this erreur and move forward. I then decided to move forward with the server patching.

I patched the server :

[root@ODA01 patch]# /opt/oracle/dcs/bin/odacli update-server -v 19.9.0.0.0
{
  "jobId" : "78f3ea84-4e31-4e1f-b195-eb4e75429102",
  "status" : "Created",
  "message" : "Success of server update will trigger reboot of the node after 4-5 minutes. Please wait until the node reboots.",
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 17:17:57 PM CET",
  "resourceList" : [ ],
  "description" : "Server Patching",
  "updatedTime" : "November 04, 2020 17:17:57 PM CET"
}



But the patching failed immediately as orachk was not successful due to the problem just described before :

[root@ODA01 patch]# odacli describe-job -i "78f3ea84-4e31-4e1f-b195-eb4e75429102"

Job details
----------------------------------------------------------------
                     ID:  78f3ea84-4e31-4e1f-b195-eb4e75429102
            Description:  Server Patching
                 Status:  Failure
                Created:  November 4, 2020 5:17:57 PM CET
                Message:  DCS-10702:Orachk validation failed: Please run describe-prepatchreport 78f3ea84-4e31-4e1f-b195-eb4e75429102 to see details.

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Server patching                          November 4, 2020 5:18:05 PM CET     November 4, 2020 5:22:05 PM CET     Failure
Orachk Server Patching                   November 4, 2020 5:18:05 PM CET     November 4, 2020 5:22:05 PM CET     Failure



So starting 19.9 it seems that orachk is mandatory before doing any patching, and if orachk will not be successful the patching will then fail.

By chance there is a new skip-orachk option to skip the orachk during server patching :

[root@ODA01 patch]# /opt/oracle/dcs/bin/odacli update-server -v 19.9.0.0.0 -h
Usage: update-server [options]
  Options:
    --component, -c
      The component that is requested for update. The supported components
      include: OS
    --force, -f
      Ignore precheck error and force patching
    --help, -h
      get help
    --json, -j
      json output
    --local, -l
      Update Server Components Locally
    --node, -n
      Node to be updated
    --precheck, -p
      Obsolete flag
    --skip-orachk, -sko
      Option to skip orachk validations
    --version, -v
      Version to be updated



I then could successfully patch the server :

[root@ODA01 patch]# /opt/oracle/dcs/bin/odacli update-server -v 19.9.0.0.0 -sko
{
  "jobId" : "878fac12-a2a0-4302-955c-7df3d4fdd517",
  "status" : "Created",
  "message" : "Success of server update will trigger reboot of the node after 4-5 minutes. Please wait until the node reboots.",
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 18:03:15 PM CET",
  "resourceList" : [ ],
  "description" : "Server Patching",
  "updatedTime" : "November 04, 2020 18:03:15 PM CET"
}
[root@ODA01 ~]# uptime
 19:06:00 up 2 min,  1 user,  load average: 2.58, 1.32, 0.52
 
[root@ODA01 ~]# odacli describe-job -i "878fac12-a2a0-4302-955c-7df3d4fdd517"

Job details
----------------------------------------------------------------
                     ID:  878fac12-a2a0-4302-955c-7df3d4fdd517
            Description:  Server Patching
                 Status:  Success
                Created:  November 4, 2020 6:03:15 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Patch location validation                November 4, 2020 6:03:23 PM CET     November 4, 2020 6:03:23 PM CET     Success
dcs-controller upgrade                   November 4, 2020 6:03:23 PM CET     November 4, 2020 6:03:28 PM CET     Success
Patch location validation                November 4, 2020 6:03:30 PM CET     November 4, 2020 6:03:30 PM CET     Success
dcs-cli upgrade                          November 4, 2020 6:03:30 PM CET     November 4, 2020 6:03:30 PM CET     Success
Creating repositories using yum          November 4, 2020 6:03:30 PM CET     November 4, 2020 6:03:33 PM CET     Success
Updating YumPluginVersionLock rpm        November 4, 2020 6:03:33 PM CET     November 4, 2020 6:03:33 PM CET     Success
Applying OS Patches                      November 4, 2020 6:03:33 PM CET     November 4, 2020 6:13:18 PM CET     Success
Creating repositories using yum          November 4, 2020 6:13:18 PM CET     November 4, 2020 6:13:18 PM CET     Success
Applying HMP Patches                     November 4, 2020 6:13:18 PM CET     November 4, 2020 6:13:38 PM CET     Success
Client root Set up                       November 4, 2020 6:13:38 PM CET     November 4, 2020 6:13:41 PM CET     Success
Client grid Set up                       November 4, 2020 6:13:41 PM CET     November 4, 2020 6:13:46 PM CET     Success
Patch location validation                November 4, 2020 6:13:46 PM CET     November 4, 2020 6:13:46 PM CET     Success
oda-hw-mgmt upgrade                      November 4, 2020 6:13:46 PM CET     November 4, 2020 6:14:17 PM CET     Success
OSS Patching                             November 4, 2020 6:14:17 PM CET     November 4, 2020 6:14:18 PM CET     Success
Applying Firmware Disk Patches           November 4, 2020 6:14:18 PM CET     November 4, 2020 6:14:21 PM CET     Success
Applying Firmware Controller Patches     November 4, 2020 6:14:21 PM CET     November 4, 2020 6:14:24 PM CET     Success
Checking Ilom patch Version              November 4, 2020 6:14:25 PM CET     November 4, 2020 6:14:27 PM CET     Success
Patch location validation                November 4, 2020 6:14:27 PM CET     November 4, 2020 6:14:28 PM CET     Success
Save password in Wallet                  November 4, 2020 6:14:29 PM CET     November 4, 2020 6:14:30 PM CET     Success
Apply Ilom patch                         November 4, 2020 6:14:30 PM CET     November 4, 2020 6:22:34 PM CET     Success
Copying Flash Bios to Temp location      November 4, 2020 6:22:34 PM CET     November 4, 2020 6:22:34 PM CET     Success
Starting the clusterware                 November 4, 2020 6:22:35 PM CET     November 4, 2020 6:23:58 PM CET     Success
clusterware patch verification           November 4, 2020 6:23:58 PM CET     November 4, 2020 6:24:01 PM CET     Success
Patch location validation                November 4, 2020 6:24:01 PM CET     November 4, 2020 6:24:01 PM CET     Success
Opatch update                            November 4, 2020 6:24:43 PM CET     November 4, 2020 6:24:46 PM CET     Success
Patch conflict check                     November 4, 2020 6:24:46 PM CET     November 4, 2020 6:25:31 PM CET     Success
clusterware upgrade                      November 4, 2020 6:25:52 PM CET     November 4, 2020 6:50:57 PM CET     Success
Updating GiHome version                  November 4, 2020 6:50:57 PM CET     November 4, 2020 6:51:12 PM CET     Success
Update System version                    November 4, 2020 6:51:16 PM CET     November 4, 2020 6:51:16 PM CET     Success
Cleanup JRE Home                         November 4, 2020 6:51:16 PM CET     November 4, 2020 6:51:16 PM CET     Success
preRebootNode Actions                    November 4, 2020 6:51:16 PM CET     November 4, 2020 6:51:57 PM CET     Success
Reboot Ilom                              November 4, 2020 6:51:57 PM CET     November 4, 2020 6:51:57 PM CET     Success



I could check the new current installed version :

[root@ODA01 ~]# odacli describe-component
System Version
---------------
19.9.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       19.9.0.0.0            up-to-date
GI                                        19.9.0.0.201020       up-to-date
DB                                        18.7.0.0.190716       18.12.0.0.201020
DCSAGENT                                  19.9.0.0.0            up-to-date
ILOM                                      5.0.1.21.r136383      up-to-date
BIOS                                      52030400              up-to-date
OS                                        7.8                   up-to-date
FIRMWARECONTROLLER                        VDV1RL02              VDV1RL04
FIRMWAREDISK                              1102                  1132
HMP                                       2.4.7.0.1             up-to-date



I patched the storage :

[root@ODA01 ~]# odacli update-storage -v 19.9.0.0.0
{
  "jobId" : "61871e3d-088b-43af-8b91-94dc4fa1331a",
  "status" : "Created",
  "message" : "Success of Storage Update may trigger reboot of node after 4-5 minutes. Please wait till node restart",
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 19:07:17 PM CET",
  "resourceList" : [ ],
  "description" : "Storage Firmware Patching",
  "updatedTime" : "November 04, 2020 19:07:17 PM CET"
}

[root@ODA01 ~]# odacli describe-job -i "61871e3d-088b-43af-8b91-94dc4fa1331a"

Job details
----------------------------------------------------------------
                     ID:  61871e3d-088b-43af-8b91-94dc4fa1331a
            Description:  Storage Firmware Patching
                 Status:  Success
                Created:  November 4, 2020 7:07:17 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Applying Firmware Disk Patches           November 4, 2020 7:07:20 PM CET     November 4, 2020 7:07:21 PM CET     Success
preRebootNode Actions                    November 4, 2020 7:07:21 PM CET     November 4, 2020 7:07:21 PM CET     Success
Reboot Ilom                              November 4, 2020 7:07:21 PM CET     November 4, 2020 7:07:21 PM CET     Success



Surprisingly the storage patching was done immediately and with no reboot.

I checked the version and could see that effectively the storage was still running old firmware versions :

[root@ODA01 ~]# odacli describe-component
System Version
---------------
19.9.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
OAK                                       19.9.0.0.0            up-to-date
GI                                        19.9.0.0.201020       up-to-date
DB                                        18.7.0.0.190716       18.12.0.0.201020
DCSAGENT                                  19.9.0.0.0            up-to-date
ILOM                                      5.0.1.21.r136383      up-to-date
BIOS                                      52030400              up-to-date
OS                                        7.8                   up-to-date
FIRMWARECONTROLLER                        VDV1RL02              VDV1RL04
FIRMWAREDISK                              1102                  1132
HMP                                       2.4.7.0.1             up-to-date



I opened a SR and could get confirmation from support that this is a bug and can be left as it is. It will not impact any functionnality.
The BUG is the following one :
Bug 32017186 – LNX64-199-CMT : FIRMWARECONTROLLER NOT PATCHED FOR 19.9

As per the rdbms home, they can be patched later. If we are using a High Availability solution, both primary and standby databases’ homes need to be patched during same maintenance windows.

Post patching activities

We can now run post patching activities.

We will ensure there is no new hardware problem :

login as: root
Keyboard-interactive authentication prompts from server:
| Password:
End of keyboard-interactive prompts from server

Oracle(R) Integrated Lights Out Manager

Version 5.0.1.21 r136383

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

Warning: HTTPS certificate is set to factory default.

Hostname: ODA01-ILOM

-> show /SP/faultmgmt

 /SP/faultmgmt
    Targets:
        shell

    Properties:

    Commands:
        cd
        show

->



We can also remove our odabr snapshot backup :

[root@ODA01 ~]# export PATH=/opt/odabr:$PATH

[root@ODA01 ~]# odabr infosnap

--------------------------------------------------------
odabr - ODA node Backup Restore
Author: Ruggero Citton 
RAC Pack, Cloud Innovation and Solution Engineering Team
Copyright Oracle, Inc. 2013, 2019
Version: 2.0.1-47
--------------------------------------------------------


LVM snap name         Status                COW Size              Data%
-------------         ----------            ----------            ------
root_snap             active                30.00 GiB             22.79%
opt_snap              active                60.00 GiB             34.37%
u01_snap              active                100.00 GiB            35.58%


[root@ODA01 ~]# odabr delsnap
INFO: 2020-11-04 19:31:46: Please check the logfile '/opt/odabr/out/log/odabr_81687.log' for more details

INFO: 2020-11-04 19:31:46: Removing LVM snapshots
INFO: 2020-11-04 19:31:46: ...removing LVM snapshot for 'opt'
SUCCESS: 2020-11-04 19:31:46: ...snapshot for 'opt' removed successfully
INFO: 2020-11-04 19:31:46: ...removing LVM snapshot for 'u01'
SUCCESS: 2020-11-04 19:31:47: ...snapshot for 'u01' removed successfully
INFO: 2020-11-04 19:31:47: ...removing LVM snapshot for 'root'
SUCCESS: 2020-11-04 19:31:47: ...snapshot for 'root' removed successfully
SUCCESS: 2020-11-04 19:31:47: Remove LVM snapshots done successfully



We can cleanup previous patching version from repository and give additionnal space to /opt :

[root@ODA01 ~]# df -h / /u01 /opt
Filesystem                          Size  Used Avail Use% Mounted on
/dev/mapper/VolGroupSys-LogVolRoot   30G   11G   18G  38% /
/dev/mapper/VolGroupSys-LogVolU01    99G   59G   35G  63% /u01
/dev/mapper/VolGroupSys-LogVolOpt    75G   60G   12G  84% /opt

[root@ODA01 ~]# odacli cleanup-patchrepo -comp GI,DB -v 19.6.0.0.0
{
  "jobId" : "97b9669b-6945-4358-938e-a3a3f3b73693",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "November 04, 2020 19:32:16 PM CET",
  "resourceList" : [ ],
  "description" : "Cleanup patchrepos",
  "updatedTime" : "November 04, 2020 19:32:16 PM CET"
}

[root@ODA01 ~]# odacli describe-job -i "97b9669b-6945-4358-938e-a3a3f3b73693"

Job details
----------------------------------------------------------------
                     ID:  97b9669b-6945-4358-938e-a3a3f3b73693
            Description:  Cleanup patchrepos
                 Status:  Success
                Created:  November 4, 2020 7:32:16 PM CET
                Message:

Task Name                                Start Time                          End Time                            Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Cleanup Repository                       November 4, 2020 7:32:17 PM CET     November 4, 2020 7:32:17 PM CET     Success
Cleanup JRE Home                         November 4, 2020 7:32:17 PM CET     November 4, 2020 7:32:17 PM CET     Success

[root@ODA01 ~]# df -h / /u01 /opt
Filesystem                          Size  Used Avail Use% Mounted on
/dev/mapper/VolGroupSys-LogVolRoot   30G   11G   18G  38% /
/dev/mapper/VolGroupSys-LogVolU01    99G   59G   35G  63% /u01
/dev/mapper/VolGroupSys-LogVolOpt    75G   49G   23G  68% /opt

We can restart our databases with srvctl start database command or srvctl start home command.

Finally we will activate our database synchronization if using Data Guard or dbvisit.

Cet article Patching Oracle Database Appliance to 19.9 est apparu en premier sur Blog dbi services.

NoSQL and SQL: key-value access always scale

Wed, 2020-11-04 12:40
By Franck Pachot

.
I have written about some NoSQL myths in previous posts (<a href="https://blog.dbi-services.com/rdbms-scales-the-algorithm/" rel="noopener noreferrer" target="_blank"4here and here) and I got some feedback from people mentioning that the test case was on relatively small data. This is true. In order to understand how it works, we need to explain and trace the execution, and that is easier on a small test case. Once the algorithm is understood it is easy to infer how it scales. Then, if readers want to test it on huge data, they can. This may require lot of cloud credits, and I usually don’t feel the need to do this test for a blog post, especially when I include all the code to reproduce it on a larger scale.

But this approach may be biased by the fact that I’ve been working a lot with RDBMS where we have all tools to understand how it works. When you look at the execution plan, you know the algorithm and can extrapolate the numbers to larger tables. When you look at the wait events, you know on which resource they can scale with higher concurrency. But times change, NoSQL databases, especially the ones managed by the cloud providers, provide only a simple API with limited execution statistics. Because that’s the goal: simple usage. And this is why people prefer to look at real scale executions when talking about performance. And, as it is not easy to run a real scale Proof of Concept, they look at the well-known big data users like Google, Facebook, Amazon…

I was preparing my DOAG presentation “#KnowSQL: Where are we with SQL, NoSQL, NewSQL in 2020?” where I’ll mention at 4TB table I’ve seen at a customer. The table was far too big (30 million extents!) because it was not managed for years (a purge job failing, not being monitored, and the outsourcing company adding datafiles for years without trying to understand). But the application was still working well, with happy users. Because they use a key-value access, and this has always been scalable. Here is a common misconception: NoSQL databases didn’t invent new techniques to store data. Hash partitioning and indexes are the way to scale this. It existed in RDBMS for a long time. What NoSQL did was providing easy access to this limited API, and restraining data access to this simple API in order to guarantee predictable performance.

By coincidence, with my presentation in mind, I had access to an Oracle Exadata that was not yet used, and I got the occasion to create a similar table containing billion of items:


SQL> info+ BIG

TABLE: BIG
         LAST ANALYZED:2020-10-29 22:32:06.0
         ROWS         :3049226754
         SAMPLE SIZE  :3049226754
         INMEMORY     :
         COMMENTS     :

Columns

NAME         DATA TYPE      NULL  DEFAULT    LOW_VALUE   HIGH_VALUE   NUM_DISTINCT   HISTOGRAM
------------ -------------- ----- ---------- ----------- ------------ -------------- ---------
*K           RAW(16 BYTE)   No                                            3049226754     HYBRID
 V           BLOB           Yes                                           0              NONE

Indexes
INDEX_NAME             UNIQUENESS   STATUS   FUNCIDX_STATUS   COLUMNS
---------------------- ------------ -------- ---------------- ------
FRANCK.SYS_C00205258   UNIQUE       N/A                       K

Just two columns: one RAW(16) to store the key as a UUID and one BLOB to store any document value. Exactly like a key-value document store today, and similar to the table I’ve seen at my customer. Well, at this customer, this was desined in the past century, with LONG RAW instead of BLOB, but this would make no sense today. And this table was not partitioned because they didn’t expect this size. In my test I did what we should do today for this key-value use case: partition by HASH:


create table BIG ( K RAW(16) primary key using index local, V BLOB ) tablespace users
LOB (V) store as securefile (enable storage in row nocache compress high)
partition by hash (K) partitions 100000 parallel 20

It is probably not useful to have 100000 partitions for a few terabytes table, but then this table is ready for a lot of more data. And in Oracle 100000 partition is far from the limit which is 1 million partitions. Note that this is a lab. I am not recommending to create 100000 partitions if you don’t need to. I’m just saying that it is easy to create a terabytes table with the performance of really small tables when accessed with the partitioning key.

So, here is the size:


14:58:58 SQL> select segment_type,segment_name,dbms_xplan.format_size(sum(bytes)) "SIZE",count(*)
from dba_segments where owner='FRANCK'
group by grouping sets ((),(segment_type),(segment_type,segment_name))
;

SEGMENT_TYPE                   SEGMENT_NAME                   SIZE         COUNT(*)
------------------------------ ------------------------------ ---------- ----------
LOB PARTITION                  SECURFILE                      781G           100000
LOB PARTITION                                                 781G           100000
INDEX PARTITION                SYS_C00205258                  270G           100000
INDEX PARTITION                SYS_IL0000826625C00002$$       6250M          100000
INDEX PARTITION                                               276G           200000
TABLE PARTITION                BIG                            7691G          100000
TABLE PARTITION                                               7691G          100000
                                                              8749G          400000
8 rows selected.

There’s 8.5 TB in total here. The table, named “BIG”, has 100000 partitions for a total of 7691 GB. The primary key index, “SYS_C00205258” is 270 GB as it contains the key (so 16 bytes, plus the ROWID to address the table, per entry). It is a local index, with same HASH partitioning as the table. For documents that are larger than the table block, the LOB partition can store them. Here I have mostly small documents which are stored in the table.

I inserted the rows quickly with a bulk load which I didn’t really tune or monitor. But here is an excerpt from AWR report when the insert was running:



Plan Statistics                                          DB/Inst: EXA19C/EXA19C1  Snaps: 16288-16289
-> % Snap Total shows the % of the statistic for the SQL statement compared to the instance total

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                        3.2218E+07   16,108,804.2    99.4
CPU Time (ms)                            2.1931E+07   10,965,611.8    99.4
Executions                                        2            1.0     0.0
Buffer Gets                              6.5240E+08  326,201,777.5   115.6
Disk Reads                               4.0795E+07   20,397,517.5   100.2
Parse Calls                                      48           24.0     0.0
Rows                                     5.1202E+08  256,008,970.0     N/A
User I/O Wait Time (ms)                   2,024,535    1,012,267.6    96.9
Cluster Wait Time (ms)                    4,293,684    2,146,841.9    99.9
Application Wait Time (ms)                      517          258.5    20.6
Concurrency Wait Time (ms)                4,940,260    2,470,130.0    96.0
          -------------------------------------------------------------

This is about 16 key-value ingested per millisecond (256,008,970.0/16,108,804.2). And it can go further as I have 15% of buffer contention that I can easily get rid of if I take care of the index definition.

After running this a few days, I have nearly 5 billion rows here:

SQL> select count(*) from BIG;

  COUNT(*)
----------
4967207817

Elapsed: 00:57:05.949

The full scan to get the exact count lasted one hour here because I’ve run it without parallel query (an equivalent of map reduce) so the count was done on one CPU only. Anyway, if counting the rows were a use case, I would create a materialized view to aggregate some metrics.

By curiosity I’ve run the same with parallel query: 6 minutes to count the 5 billion documents with 20 parallel processes:

My goal is to test reads. In order to have predictable results, I flush the buffer cache:


5:10:51 SQL> alter system flush buffer_cache;

System FLUSH altered.

Elapsed: 00:00:04.817

Of course, in real life, there’s a good chance that all the index branches stay in memory.


15:11:11 SQL> select * from BIG where K=hextoraw('B23375823AD741B3E0532900000A7499');

K                                V
-------------------------------- --------------------------------------------------------------------------------
B23375823AD741B3E0532900000A7499 4E2447354728705E776178525C7541354640695C577D2F2C3F45686264226640657C3E5D2453216A

Elapsed: 00:00:00.011

“Elapsed” is the elapsed time in seconds. Here 11 milliseconds. NoSQL databases advertise their “single digit millisecond” and that’s right, because “No SQL” provides a very simple API (key-value access). Any database, NoSQL or RDBMS, can be optimized for this key-value access. An index on the key ensures a O(logN) scalability and, when you can hash partition it, you can maintain this cost constant when data grows, which is then O(1).

In order to understand not only the time, but also how it scales with more data or high throughput, I look at the execution plan:



15:11:27 SQL> select * from dbms_xplan.display_cursor(format=>'allstats last');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gqcazx39y5jnt, child number 21
--------------------------------------
select * from BIG where K=hextoraw('B23375823AD741B3E0532900000A7499')

Plan hash value: 2410449747

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |      1 |        |      1 |00:00:00.01 |       3 |     3 |
|   1 |  PARTITION HASH SINGLE             |               |      1 |      1 |      1 |00:00:00.01 |       3 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| BIG           |      1 |      1 |      1 |00:00:00.01 |       3 |     3 |
|*  3 |    INDEX UNIQUE SCAN               | SYS_C00205258 |      1 |      1 |      1 |00:00:00.01 |       2 |     2 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("K"=HEXTORAW('B23375823AD741B3E0532900000A7499'))

I’ve read only 3 “Buffers” here. Thanks to the partitioning (PARTITION HASH SINGLE), each local index is small, with a root branch and a leaf block: 2 buffers read. This B*Tree index (INDEX UNIQUE SCAN) returns the physical address in the table (TABLE ACCESS BY LOCAL INDEX ROWID) in order to get the additional column.

Finally, I insert one row:


SQL> set timing on autotrace on
SQL> insert into BIG values(hextoraw('1D15EA5E8BADF00D8BADF00DFF'),utl_raw.cast_to_raw(dbms_random.string('p',1024)));

1 row created.

Elapsed: 00:00:00.04

This takes 40 milliseconds

The autotrace shows what is behind:


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |  1177 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | BIG  |       |       |            |          |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          8  db block gets
          1  consistent gets
          7  physical reads
       1864  redo size
        872  bytes sent via SQL*Net to client
       1048  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

There are a few blocks to maintain (db block gets) when adding a new entry into a B*Tree index, especially when there are some blocks to split to allocate more space in the tree. In RDBMS you should categorize the data ingestion into:

  • high throughput for big data, like metrics and logs from IoT, with the rate of bulk inserts as I did to fill-in the table
  • fast response time to put one of few items and this is milliseconds, scaling thanks to local index partitioning

I’m talking about the roots of NoSQL here: providing the simplest key-value access in order to scale. But the most advanced NoSQL managed services went further, pushing the data ingest performance with LSM (log-structured merge) indexes rather than B*Tree in-place index maintenance. They have also implemented many features to autonomously maintain the partitions at their best for storage, performance and high availability. This presentation explains a few in the context of AWS DynamoDB:

With DynamoDB you can’t get the execution plan, but you can ask for the ConsumedCapacity to be returned with the result. This helps to validate your understanding of the data access even without running on huge volume and expensive provisioned capacity. This is what I did in https://blog.dbi-services.com/rdbms-scales-the-algorithm/ measuring the linear increase of RCU on a small 2 million items table, which is sufficient to extrapolate to larger data sets. Key-value access always scales in this way: response time remains constant when data grows. And It can remain constant when the users grow as well, by splitting partitions to more storage.

Cet article NoSQL and SQL: key-value access always scale est apparu en premier sur Blog dbi services.

Getting started with Exasol – Distribution keys

Tue, 2020-11-03 09:31

After the previous posts about Exasol, which have been more around installation, administration, backup & restore, loading data and general notes around transactions and sessions we’ll now go into more details how Exasol manages data. Here is the list of the previous posts:

  1. Getting started with Exasol – Setting up an environment
  2. Getting started with Exasol – Loading data from PostgreSQL
  3. Getting started with Exasol – Some words about indexes and transactions
  4. Getting started with Exasol – Sessions and auditing
  5. Getting started with Exasol – Installation from ISO
  6. Getting started with Exasol – Backup and restore (1)
  7. Getting started with Exasol – Backup and restore (2)
  8. Getting started with Exasol – A multi node cluster in AWS

Having a two node cluster running in AWS is a good starting point to explore an important concept in Exasol: Distribution keys. As soon as you have more than one data node in the cluster, data gets distributed across the cluster nodes, if a certain limit in size is reached, automatically. To start with, lets generate a CSV file we can import into Exasol. I’ll use this simple bash script to generate a file with 100’000’000 rows:

dwe@dwe:~/Downloads$ cat gen_data.sh 
#!/bin/bash

FILE="/home/dwe/Downloads/sample.csv"
rm -rf ${FILE}

for i in {1..10000000}; do
    echo "${i},firstname${i},lastname${i},xxx${i}@xxx.com,street${i},country${i},description${i}" >> ${FILE}
done

This results in a file of around 1GB:

dwe@dwe:~/Downloads$ chmod +x gen_data.sh 
dwe@dwe:~/Downloads$ ./gen_data.sh 
dwe@dwe:~/Downloads$ head -5 sample.csv 
1,firstname1,lastname1,xxx1@xxx.com,street1,country1,description1
2,firstname2,lastname2,xxx2@xxx.com,street2,country2,description2
3,firstname3,lastname3,xxx3@xxx.com,street3,country3,description3
4,firstname4,lastname4,xxx4@xxx.com,street4,country4,description4
5,firstname5,lastname5,xxx5@xxx.com,street5,country5,description5
dwe@dwe:~/Downloads$ ls -lha sample.csv 
-rw-rw-r-- 1 dwe dwe 1023M Oct 17 19:27 sample.csv

We’ll import that using the import command as we already did it in the previous posts. If you have multiple nodes, you can list of all of them in the connection string:

/home/dwe/EXAplus-7.0.0/exaplus -c 18.193.84.41,54.93.49.6:8563 -u sys -p xxxxx

Creating the schema and the table that will hold the data we want to import:

SQL_EXA> create schema demo;
EXA: create schema demo;

Rows affected: 0

SQL_EXA> open schema demo;
EXA: open schema demo;

Rows affected: 0

SQL_EXA> create table sample ( id int primary key
                             , firstname varchar(20)
                             , lastname varchar(20)
                             , email varchar(20)
                             , street varchar(20)
                             , country varchar(20)
                             , description varchar(20)
                             );
EXA: create table sample ( id int primary key...

Rows affected: 0

Importing the sample csv created above:

SQL_EXA> IMPORT INTO sample  
         FROM LOCAL CSV FILE '/home/dwe/Downloads/sample.csv'
         COLUMN SEPARATOR = ',';
EXA: IMPORT INTO sample  ...

Rows affected: 10000000

If we check if this table already has a distribution key assigned, we’ll notice that there is none:

SQL_EXA> select TABLE_NAME,TABLE_HAS_DISTRIBUTION_KEY from exa_dba_tables where TABLE_NAME = 'SAMPLE';
EXA: select TABLE_NAME,TABLE_HAS_DISTRIBUTION_KEY from exa_dba_tables where...

TABLE_NAME           TABLE
-------------------- -----
SAMPLE               FALSE

1 row in resultset.

If no distribution key is specified, the distribution of the rows is random. Using the IPROC function, you can you ask, on which node a given row is stored. If we do that for our sample table we’ll see this distribution of data:

SQL_EXA> select count(*) from (select id, iproc() from sample) where iproc=0;
EXA: select count(*) from (select id, iproc() from sample) where iproc=0;

COUNT(*)             
---------------------
              5002896

1 row in resultset.

SQL_EXA> select count(*) from (select id, iproc() from sample) where iproc=1;
EXA: select count(*) from (select id, iproc() from sample) where iproc=1;

COUNT(*)             
---------------------
              4997104

1 row in resultset.

As we currently have two data nodes, we see an approximate even distribution of the rows across the two nodes. To know if that distribution is a good choice, you need to know what columns will be used in joins. To demonstrate this let’s create a copy of the table, again without specifying a distribution key:

SQL_EXA> create table sample2 as select * from sample;
EXA: create table sample2 as select * from sample;

Rows affected: 10000000

SQL_EXA> alter table sample2 add constraint sample_pk primary key (id);
EXA: alter table sample2 add constraint sample_pk primary key (id);

Rows affected: 0

Suppose we want to join these two tables on the primary key like this:

SQL_EXA> select sample.id, sample.firstname 
              from sample join sample2 on sample.id = sample2.id 
             where sample.id < 20;
EXA: select sample.id, sample.firstname from sample join sample2 on sample....

ID                    FIRSTNAME           
--------------------- --------------------
                    1 firstname1          
                    2 firstname2          
                    3 firstname3          
                    4 firstname4          
                    5 firstname5          
                    6 firstname6          
                    7 firstname7          
                    8 firstname8          
                    9 firstname9          
                   10 firstname10         
                   11 firstname11         
                   12 firstname12         
                   13 firstname13         
                   14 firstname14         
                   15 firstname15         
                   16 firstname16         
                   17 firstname17         
                   18 firstname18         
                   19 firstname19         

19 rows in resultset.

How can we know if the distribution of the rows is good for this query? In PostgreSQL we would have a look at the execution plan but there is no command to generate an execution plan in Exasol (at least not in the same way you know it from other databases). What you need to do for getting the details about what the cost based optimizer is doing, is to turn on profiling. Let’s do a simple example: We’ll create a simple table, select from that table and then check what happened in the background. As the first step, lets create the table and add one row:

SQL_EXA> create table dummy ( a int, b varchar(20), c date );
EXA: create table dummy ( a int, b varchar(20), c date );

Rows affected: 0

SQL_EXA> insert into dummy values (1,'aaaa',sysdate);
EXA: insert into dummy values (1,'aaaa',sysdate);

Rows affected: 1

Now we want to profile a simple statement and that requires some steps to complete: Turn on profiling (which is turned off by default), disable the query cache (otherwise we might get the results for subsequent executions of the same SQL statement from the cache), flush the statistics, and finally the steps to get out the information so we see what happened:

SQL_EXA> alter session set profile='on';
EXA: alter session set profile='on';

Rows affected: 0

SQL_EXA> alter session set query_cache='off';
EXA: alter session set query_cache='off';

Rows affected: 0

SQL_EXA> set autocommit off;
SQL_EXA> select count(*) from dummy;
EXA: select count(*) from dummy;

COUNT(*)             
---------------------
                    1

1 row in resultset.

SQL_EXA> flush statistics;
EXA: flush statistics;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_ROWS,OUT_ROWS from EXA_DBA_PROFILE_LAST_DAY where session_id=current_session and command_name = 'SELECT' preferring high stmt_id order by part_id; 
EXA: select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,...

STMT_ID      COMMAND_NAME                             PART_ID   PART_NAME            PART_INFO            OBJECT_SCHEMA   OBJECT_NAME                    OBJECT_ROWS           OUT_ROWS             
------------ ---------------------------------------- --------- -------------------- -------------------- --------------- ------------------------------ --------------------- ---------------------
           8 SELECT                                           1 COMPILE / EXECUTE                                                                                                                   
           8 SELECT                                           2 SCAN                                      DEMO            DUMMY                                              1                     1
           8 SELECT                                           3 GROUP BY             GLOBAL on TEMPORARY                  tmp_subselect0                                     0                     1
                                                                                     table                                                                                                          

3 rows in resultset.

SQL_EXA> 

What can we see here: Three steps have been executed:

  1. COMPILE / EXECUTE: This is always the first step, the time it takes to compile the query
  2. SCAN: This step does the scan over the dummy table
  3. GROUP BY: Finally a group by to get the aggregation

There is much more information in EXA_DBA_PROFILE_LAST_DAY, but we’ll ignore that for now. Now, that we know how to get that information we can go back to our initial query, what do we see here?

SQL_EXA> select sample.id, sample.firstname 
              from sample join sample2 on sample.id = sample2.id 
             where sample.id < 20;
EXA: select sample.id, sample.firstname from sample join sample2 on sample....

ID                    FIRSTNAME           
--------------------- --------------------
                    1 firstname1          
                    2 firstname2          
                    3 firstname3          
                    4 firstname4          
                    5 firstname5          
                    6 firstname6          
                    7 firstname7          
                    8 firstname8          
                    9 firstname9          
                   10 firstname10         
                   11 firstname11         
                   12 firstname12         
                   13 firstname13         
                   14 firstname14         
                   15 firstname15         
                   16 firstname16         
                   17 firstname17         
                   18 firstname18         
                   19 firstname19         

19 rows in resultset.

SQL_EXA> flush statistics;
EXA: flush statistics;


Rows affected: 0

SQL_EXA> 
SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_ROWS,OUT_ROWS,DURATION from EXA_DBA_PROFILE_LAST_DAY where session_id=current_session and command_name = 'SELECT' preferring high stmt_id order by part_id; 
EXA: select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,...

STMT_ID      COMMAND_NAME                             PART_ID   PART_NAME            PART_INFO            OBJECT_SCHEMA   OBJECT_NAME                    OBJECT_ROWS           OUT_ROWS              DURATION   
------------ ---------------------------------------- --------- -------------------- -------------------- --------------- ------------------------------ --------------------- --------------------- -----------
          43 SELECT                                           1 COMPILE / EXECUTE                                                                                                                          0.199
          43 SELECT                                           2 SCAN                                      DEMO            SAMPLE                                      10000000                    19       0.028
          43 SELECT                                           3 JOIN                 GLOBAL               DEMO            SAMPLE2                                     10000000                    19       0.000
          43 SELECT                                           4 INSERT               on TEMPORARY table                   tmp_subselect0                                     0                    19       0.000

4 rows in resultset.

What you usually don’t want to see is a global join but exactly this is happening (I’ve also added the duration so we can compare timings later). If a join partner can not be found on the local node a global joins happens (communication with the node that holds the join partner) and this introduces network traffic on the private network, and that takes time.

Lets change the distribution key of the two tables (this will re-write the table, of course), so that IDs with the same hash will located on the same node:

SQL_EXA> alter table sample distribute by id;
EXA: alter table sample distribute by id;

Rows affected: 0

SQL_EXA> alter table sample2 distribute by id;
EXA: alter table sample2 distribute by id;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

Does that change the execution steps?

SQL_EXA> select sample.id, sample.firstname 
              from sample join sample2 on sample.id = sample2.id 
             where sample.id < 20;
EXA: select sample.id, sample.firstname from sample join sample2 on sample....

ID                    FIRSTNAME           
--------------------- --------------------
                    1 firstname1          
                    2 firstname2          
                    3 firstname3          
                    4 firstname4          
                    5 firstname5          
                    6 firstname6          
                    7 firstname7          
                    8 firstname8          
                    9 firstname9          
                   10 firstname10         
                   11 firstname11         
                   12 firstname12         
                   13 firstname13         
                   14 firstname14         
                   15 firstname15         
                   16 firstname16         
                   17 firstname17         
                   18 firstname18         
                   19 firstname19         

19 rows in resultset.


SQL_EXA> flush statistics;
EXA: flush statistics;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_ROWS,OUT_ROWS,DURATION from EXA_DBA_PROFILE_LAST_DAY where session_id=current_session and command_name = 'SELECT' preferring high stmt_id order by part_id; 
EXA: select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,...

STMT_ID      COMMAND_NAME                             PART_ID   PART_NAME            PART_INFO            OBJECT_SCHEMA   OBJECT_NAME                    OBJECT_ROWS           OUT_ROWS              DURATION   
------------ ---------------------------------------- --------- -------------------- -------------------- --------------- ------------------------------ --------------------- --------------------- -----------
          51 SELECT                                           1 COMPILE / EXECUTE                                                                                                                          0.011
          51 SELECT                                           2 SCAN                                      DEMO            SAMPLE                                      10000000                    19       0.028
          51 SELECT                                           3 JOIN                                      DEMO            SAMPLE2                                     10000000                    19       0.000
          51 SELECT                                           4 INSERT               on TEMPORARY table                   tmp_subselect0                                     0                    19       0.000

4 rows in resultset.

Yes, definitely. We do not see a global join anymore, but a local join and this improves performance. So, with Exasol, you should avoid global joins, because that increases traffic on the private network. Try to distribute the data on the join columns for getting local joins.

Btw: You can see the distribution keys if you do a full describe against a table:

SQL_EXA> desc full sample;
EXA: desc full sample;

COLUMN_NAME                    SQL_TYPE                                 NULLABLE DISTRIBUTION_KEY PARTITION_KEY    COLUMN_COMMENT                                    
------------------------------ ---------------------------------------- -------- ---------------- ---------------- --------------------------------------------------
ID                             DECIMAL(18,0)                            FALSE    TRUE             FALSE                                                              
FIRSTNAME                      VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
LASTNAME                       VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
EMAIL                          VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
STREET                         VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
COUNTRY                        VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
DESCRIPTION                    VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                        

To show the effect on global joins on the network, lets redo the example, but this time also add the NET column from EXA_DBA_PROFILE_LAST_DAY, which shows the network traffic in MiB per second. An easy way to get to our initial state is to drop the distribution keys and then manually re-organize the tables:

SQL_EXA> alter table sample drop distribution keys;
EXA: alter table sample drop distribution keys;

Rows affected: 0

SQL_EXA> alter table sample2 drop distribution keys;
EXA: alter table sample2 drop distribution keys;

Rows affected: 0

SQL_EXA> reorganize table sample;
EXA: reorganize table sample;

Rows affected: 0

SQL_EXA> reorganize table sample2;
EXA: reorganize table sample2;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> desc full sample;
EXA: desc full sample;

COLUMN_NAME                    SQL_TYPE                                 NULLABLE DISTRIBUTION_KEY PARTITION_KEY    COLUMN_COMMENT                                    
------------------------------ ---------------------------------------- -------- ---------------- ---------------- --------------------------------------------------
ID                             DECIMAL(18,0)                            FALSE    FALSE            FALSE                                                              
FIRSTNAME                      VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
LASTNAME                       VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
EMAIL                          VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
STREET                         VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
COUNTRY                        VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
DESCRIPTION                    VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              

7 rows in resultset.

SQL_EXA> desc full sample2;
EXA: desc full sample2;

COLUMN_NAME                    SQL_TYPE                                 NULLABLE DISTRIBUTION_KEY PARTITION_KEY    COLUMN_COMMENT                                    
------------------------------ ---------------------------------------- -------- ---------------- ---------------- --------------------------------------------------
ID                             DECIMAL(18,0)                            FALSE    FALSE            FALSE                                                              
FIRSTNAME                      VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
LASTNAME                       VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
EMAIL                          VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
STREET                         VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
COUNTRY                        VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              
DESCRIPTION                    VARCHAR(20) UTF8                         TRUE     FALSE            FALSE                                                              

7 rows in resultset.

This is how we started. Let’s have a look at the first case once more (please note that I’ll execute the statement twice, as the first execution will create the index), but we also change the statement to go through the whole table:

SQL_EXA> select count(*)
           from sample join sample2 on sample.id = sample2.id;
EXA: select count(*)...

COUNT(*)             
---------------------
             10000000

1 row in resultset.

SQL_EXA> select count(*)
           from sample join sample2 on sample.id = sample2.id;
EXA: select count(*)...

COUNT(*)             
---------------------
             10000000

1 row in resultset.

SQL_EXA> flush statistics;
EXA: flush statistics;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_ROWS,OUT_ROWS,DURATION,NET from EXA_DBA_PROFILE_LAST_DAY where session_id=current_session and command_name = 'SELECT' preferring high stmt_id order by part_id;
EXA: select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,...

STMT_ID      COMMAND_NAME                             PART_ID   PART_NAME            PART_INFO            OBJECT_SCHEMA   OBJECT_NAME                    OBJECT_ROWS           OUT_ROWS              DURATION    NET      
------------ ---------------------------------------- --------- -------------------- -------------------- --------------- ------------------------------ --------------------- --------------------- ----------- ---------
         123 SELECT                                           1 COMPILE / EXECUTE                                                                                                                          0.031     129.9
         123 SELECT                                           2 SCAN                                      DEMO            SAMPLE                                      10000000              10000000       0.007     166.4
         123 SELECT                                           3 JOIN                 GLOBAL               DEMO            SAMPLE2                                     10000000              10000000       1.128          
         123 SELECT                                           4 GROUP BY             GLOBAL on TEMPORARY                  tmp_subselect0                                     0                     1       0.009     155.6
                                                                                     table                                                                                                                                

4 rows in resultset.

SQL_EXA> 

Changing the distribution keys again, and re-do the test:

SQL_EXA> alter table sample distribute by id;
EXA: alter table sample distribute by id;

Rows affected: 0

SQL_EXA> alter table sample2 distribute by id;
EXA: alter table sample2 distribute by id;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> select count(*)
           from sample join sample2 on sample.id = sample2.id;
EXA: select count(*)...

COUNT(*)             
---------------------
             10000000

1 row in resultset.

SQL_EXA> select count(*)
           from sample join sample2 on sample.id = sample2.id;
EXA: select count(*)...

COUNT(*)             
---------------------
             10000000

1 row in resultset.

SQL_EXA> flush statistics;
EXA: flush statistics;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,OBJECT_NAME,OBJECT_ROWS,OUT_ROWS,DURATION,NET from EXA_DBA_PROFILE_LAST_DAY where session_id=current_session and command_name = 'SELECT' preferring high stmt_id order by part_id;
EXA: select STMT_ID,COMMAND_NAME,PART_ID,PART_NAME,PART_INFO,OBJECT_SCHEMA,...

STMT_ID      COMMAND_NAME                             PART_ID   PART_NAME            PART_INFO            OBJECT_SCHEMA   OBJECT_NAME                    OBJECT_ROWS           OUT_ROWS              DURATION    NET      
------------ ---------------------------------------- --------- -------------------- -------------------- --------------- ------------------------------ --------------------- --------------------- ----------- ---------
         131 SELECT                                           1 COMPILE / EXECUTE                                                                                                                          0.006       0.2
         131 SELECT                                           2 SCAN                                      DEMO            SAMPLE                                      10000000              10000000       0.005       0.0
         131 SELECT                                           3 JOIN                                      DEMO            SAMPLE2                                     10000000              10000000       0.330          
         131 SELECT                                           4 GROUP BY             GLOBAL on TEMPORARY                  tmp_subselect0                                     0                     1       0.004       0.1
                                                                                     table                                                                                                                                

4 rows in resultset.

SQL_EXA> 

We come down to almost no network traffic and this is what you want to see.

In the next post we’ll look into partitioning.

Cet article Getting started with Exasol – Distribution keys est apparu en premier sur Blog dbi services.

Jenkins how to create a cyclic job

Tue, 2020-11-03 08:42
Introduction

Hi everybody
Today it’s not about Control-M jobs but about Jenkins jobs ,Jenkins allows you also to schedule jobs and below we will see how to make it cyclic.
First , we will create a job using Jenkins graphic web interface

How to create a job

Connect to Jenkins interface with your login/password


-Select new item
-Select freestyle build
-Name your build


In the build’s configuration , enter your command line in the build  part (here we will chose “execute shell” in the drop down menu )

 

configure the scheduling

To schedule a job,Jenkins is using the same syntax as Linux crontab,then it’s easy to perform a cyclic job
Check the box ” build periodically”It is Based on the same logic of Linux cron , moreover you have example and syntax by clicking on the “?” icon on the right of the field.
On this example we configured the scheduling to have a build execution every 2 minutes
The screenshot is showing  that it will be executed on monday( and every other week days) every 2 minutes and next execution is planned.

Start the build

After that you can launch your build and check if the job is looping as expected (note that each execution create a build)
we can see that build is triggered as expected and time interval is respected as well

Conclusion

Jenkins is quite similar with Control-M which allows you to schedule jobs and do many other interesting tasks such as managing a complete workflow using “pipelines”, it can also be mounted as a Docker image and has a good flexibility.the web interface is really intuitive and we can be associate it  to other software ( like Control-M )
Stay tuned to get more Control-M and Jenkins tips and don’t forget to check dbi bloggers.

Cet article Jenkins how to create a cyclic job est apparu en premier sur Blog dbi services.

Control-M/EM Logon issue when connecting to the CCM Cannot get UTF8 locale

Mon, 2020-11-02 06:44
Introduction

Hi everyone,today we will see how to fix a logon issue I have encountered when I installed one of my Control-M lab.
Kind of tricky issue ,as you can log into the local client GUI without problem but not to the CCM.

Issue description

When trying to connect to the CCM even from the Workload Automation GUI you can get the below error:

When you check your Control-M client’s logs,you can see this type of message:

------------ Diagnostic Parameters -------------------------------------------------------------------------------------------------
Diagnostic device is a kind of 'Cyclic', it prints out into #5 file(s), file capacity is #25000 messages
DefaulDebugLevel=2, DefaultBufferLevel=4, file pattern IsPIDUsed=TRUE, logger mode UseFileLevel=FALSE .
Cleanup settings are: ApplyCleanup=TRUE, RetainDays=3
-------------------------------------------------------------------------------------------------------------------------------------

------------------ List of all component versions ---------------------
em_security version=9.00.01;build="10021";timestamp=Sep 13 2018 16:44:05;os=Windows;compiler=MSC_1600;location=C:/git_repo/prod/EM_INT_9.0.18.200/Server/;host=VW-TLV-CTM-BL61;note=;
em_foundation version=9.00.01;build=10021;timestamp=Sep 13 2018 16:43:53;os=Windows;compiler=MSC_1600;location=C:/git_repo/prod/EM_INT_9.0.18.200/Server/;host=VW-TLV-CTM-BL61;note=;
core_util version=4.0;build="10056";timestamp=Sep 13 2018 16:42:14;os=Windows;compiler=MSC_1600;location=C:/git_repo/prod/EM_INT_9.0.18.200/Server/;host=VW-TLV-CTM-BL61;note=;
core_diag version=4.0;build=10056;timestamp=Sep 13 2018 16:42:12;os=Windows;compiler=MSC_1600;location=C:/git_repo/prod/EM_INT_9.0.18.200/Server/;host=VW-TLV-CTM-BL61;note=;
------------------------------------------------------------------------
28/01/2020 08:14:44.282 [1] cmcbaseapi GetCMSDomainsList IDL_GetLDAPDomainNames returned 0 domains
28/01/2020 08:14:44.322 [1] loginhandler LoginHandler::SetConnectionData PasswordEncode System Parameter: 2
28/01/2020 08:14:44.325 [1] loginhandler GL_Login failed with excepion: Cannot get UTF8 locale, probably there is no locale dir: C:\Program Files\BMC Software\Control-M EM\DBI-LT-NSO_BINARY\locale : cannot create a .MS65001 locale, code 99, severity 12, programm C:\git_repo\prod\EM_INT_9.0.18.200\Server\corekit\src\corekit\unicode_configuration.cpp 167
28/01/2020 08:14:44.325 [1] cmcbaseapi LogToServer Login Failed:

Indeed this problem occurs even when you achieve to connect to the Control-M client GUI and you try to connect to CCM through the tool pane.

Cause

This problem can happen when Windows ‘Region Settings’ have the ‘Beta: Use Unicode UTF-8 for worldwide language support’ option selected.

Solution

After deselecting the ‘Beta: Use Unicode UTF-8 for worldwide language support’ and restarting the desktop OS, the Control-M Configuration Manager login able to proceed as expected
You have to untick the box concerning UTF8 Language on the windows desktop options ,then restart your desktop to take it in account:

After that you can try to log from the GUI client and you will be able to connect to the CCM.

Conclusion

Hope this tip will help and for more topics  you can check the other dbi’s blogger

Cet article Control-M/EM Logon issue when connecting to the CCM Cannot get UTF8 locale est apparu en premier sur Blog dbi services.

Control-M-EM check and update agent status using CLI

Mon, 2020-11-02 06:27

Introduction
Hi everybody, sometime access to CCM may be limited or unavailable,so how can it be possible to manage Control-M agent status without GUI?What about using Control-M CLI utility?Here is the solution
Connexion on Control-m server
Check status by using with agent_list utility

CTMSRVCENTOS% agent_list

Time Stamp :
-------------
11/02/2020 09:25:52

Agent serveurctmv919 is Disabled
Agent ctmsrvcentos is Unavailable
Agent 192.168.56.12 is Unavailable
Agent 192.168.56.10 is Available

Update agent status with ctm_agstat utility

CTMSRVCENTOS% ctm_agstat

Time Stamp :
-------------
11/02/2020 09:26:39

Nothing requested
Usage: ctm_agstat { -LIST | -UPDATE {AVAILABLE | DISABLED} }
[ -DEBUG ]
[ -QUIET ]

CTMSRVCENTOS% ctm_agstat -UPDATE serveurctmv919 AVAILABLE

Time Stamp :
-------------
11/02/2020 09:27:01

Agent serveurctmv919 is now Available
CTMSRVCENTOS% agent_list

Time Stamp :
-------------
11/02/2020 09:27:08

Agent serveurctmv919 is Available
Agent ctmsrvcentos is Unavailable
Agent 192.168.56.12 is Unavailable
Agent 192.168.56.10 is Available
CTMSRVCENTOS%

Example:


conclusion
Here is a quick and efficient way to get agent listing and status and update them don’t hesitate to try it, it can be useful.
Feel free to check my other topics and also other dbi bloggers for more info and help.

Cet article Control-M-EM check and update agent status using CLI est apparu en premier sur Blog dbi services.

What is EDB?

Sun, 2020-11-01 03:09

Last few days I played around with EDB tools. In this Blog I will introduce you to EDB.

What is EDB?

PostgreSQL is Open Source and has no owner. The owner of PostgreSQL is the community itself. That means, if you have any problems with your PostgreSQL which you cannot solve, you have tree options:

  • Search the problem on Search Engines
  • Ask the community
  • Hire PostgreSQL Consultant

On the PostgreSQL license we can read following:

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

The license could be found here: https://www.postgresql.org/about/licence/

So that means you can copy the source code, add your own features and sell it. This is what EDB does. Enterprise DB extends the functionality of PostgreSQL. For example:

  • Increases the security for example Password Management Policy and SQL injection protection
  • More auditing
  • Database Migration
  • Ready to use Enterprise tools such as for Backup and High availability

As the EDB is not free, they also offer support for their own Product. It’s like CentOS and RedHat. Companies which are ready to pay for RedHat, they also want support for their Database.

Let’s look two tools how they can help us

EDB Backup and Recovery Tool (BART):

  • Manage multiple local or remote hosts for Backups
  • Block-level incremental or Full Backups
  • Global policies for Backup rotation
  • Admin Panel with GUI
  • And a lot of other features

Postgres Failover Manager

  • Automatically switch traffic to a standby database
  • Monitor the system healthy
  • Reconfigures load balancer on failover
  • It needs a Load Balancer!
  • And a lot of other features

There are also few Open Source Projects from EDB:

  • Pgpool-II
    • Load balancer between applications and PostgreSQL database
    • High availability
  • Foreign Data Wrappers
    • Extends the default Foreign Data Wrapper

To get access to EDB repository you need create an EDB account. You will have 60 days unlimited access to this repository. After that you need a subscription. Link to create EDB account

 

After you created EDB account and verified your E-Mail, you will have access to the EDB repositories. It’s very simple. You choose left site which tools you want to install and on the right side you see How to tutorial.

 

Conclusion: If you need Enterprise Database solutions and you need 24/7 support, you might be look at EDB solutions. But if you need only High Availability or Backups tools there are also awesome Open Source projects for example “Patroni” for High Availability developed by Zalando. The tool “pgbackrest” is very simple to use to create PostgreSQL Backups, Mouhamadou has a greate Blog about pgbackrest.

Cet article What is EDB? est apparu en premier sur Blog dbi services.

Getting started with SQL Server on Linux containers with Docker Desktop and WSL2

Sat, 2020-10-31 11:31

In this blog post, I will show you the steps I went through to run SQL Server inside a Linux Docker container on my Windows 10 laptop.
The aim here is to deploy quickly and easily SQL Server instances in my lab environment.

Introduction

Since May 2020 Windows ships with WSL 2. Windows System for Linux is a feature that creates a lightweight environment that allows you to run Linux distributions (Debian, Ubuntu, etc.) without having to set up a VM or dual-booting. WSL has been around for a while but with WSL 2 the architecture has been reconfigured to run a Linux kernel, adding full system call compatibility and increasing file system performance. You can now run Docker in your Linux distro on Windows with WSL 2.

In this blog post, I decided to install the Docker Engine on Windows with Docker Desktop but you could also install Docker directly on a WSL2 Linux distro.

We will see the following topics

– WSL 2 installation
– Docker Desktop installation
– Run a SQL Server container
– Connect to the SQL Server instance with SSMS
– Enable the SQL Server Agent
– Create a custom image
– Transfert a database backup file

WSL 2 installation

The requirement to use WSL 2 is to have Windows 10 version 1903 or higher with Build 18362 or higher.

So the first thing is to check the windows version and build number. It can be done with PowerShell with Get-ComputerInfo.

Let’s check if WSL is already enabled:

You can enable it using the command below (in an administrative PowerShell). You will have to restart your computer after that.

Once enabled WSL 2 needs to be updated.
To do so you need to download and run the latest WSL 2 update package.

Docker Desktop installation

Download the latest Docker Desktop for Windows installer from Docker Hub.
The installation is pretty straightforward.

Now that Docker Desktop is installed you can go into the Docker Settings and verify that the “Use the WSL 2 based engine” option is checked.

Now that Docker Desktop is installed you can use the following command to list distributions installed on your computer. Docker Desktop comes with 2 distributions and we can verify what version WSL we have.

Run an SQL Server container

We are now all set up.
To start working with a container we first need to choose a container image to run from.
The list of official SQL Server from Microsoft is available on Docker Hub. I will choose the 2019 CU8 on Ubuntu 16.04 one.

To get the image from the online registry just run the “docker pull” command. Docker commands can be used in PowerShell.

We can list the available images.

To start a new container from the image the command is “docker run”. I specify a name for my container and the SA account’s password.

My container is now Up and running. I can check the status with “docker ps”.

Connect to the instance with SSMS

I mapped the SQL Server default port 1433 to the instance port inside the container (also 1433) so I can just connect with SSMS using “localhost” as Server name:

That’s it. I’m now connected to my instance.

If I need a new instance again I can run the same command just changing the container name and the port.

I can confirm the instance is running querying the DMV sys.dm_os_host_info.

Enable the SQL Server Agent

On the screenshot above we can see the SQL Server Agent is disabled by default. It can be enabled in T-SQL from the instance:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Agent XPs',1
reconfigure

The SQL Server Agent could be enabled at the run with an SQL Server environment variable ;  MSSQL_AGENT_ENABLED. Here is an example;

Create a custom image

Now in T-SQL I will create a database “TestDB” with a table and insert rows inside.

create database testDB
go
use testDB
go
create table testTable (
	id int identity primary key
	, colA char(4)
)
go
insert into testTable(colA) values('test')
go 2020

 

To create an image from my “sql2019lab” container I first need to stop it.

Then I need to run docker commit.

The image is ready. The image has no repository or tag. I could upload my custom image to Docker Hub and run it from anywhere but I’ll keep everything locally for now. I can run a container from my new image.

When I connect to the instance on my new “sql2019lab3” container the SQL Server Agent is already running and my database is there.

Transfert a database backup file

It’s quite easy to transfer a file from Windows to a running container with the command “docker cp”.
Here I copy a database backup into the default directory /var/opt/mssql/data

The backup file can be restored from SQL Server in T-SQL or using SSMS.

Conclusion

In conclusion, we’ve seen how easy it is to set up Docker on Windows and run containers.
It’s not more complicated than installing SQL Server on a VM with Hyper-V or VirtualBox.
Once the image has been downloaded a new SQL Server instance can be deployed in a few seconds.
I hope you enjoyed this short introduction to SQL Server in containers.

Cet article Getting started with SQL Server on Linux containers with Docker Desktop and WSL2 est apparu en premier sur Blog dbi services.

Oracle 19c Grid Infrastructure for a cluster

Fri, 2020-10-30 05:48

In this previous blog , we deal with Oracle Restart and ACFS. We saw that using ACFS with Oracle Restart will require some manual tasks. The solution is to install a Grid Infrastructure for a cluster, even if we are only using one node.

I am using the same configuration
-Oracle 19c
-Oracle Linux Server 7.6
-Kernel 4.14.35-1902.2.0.el7uek.x86_64

As we are going to install a GI for a cluster, we will need

-Public IP: 192.168.2.21
-Virtual IP: 192.168.2.23
-SCAN: 192.168.2.22
-Interconnect: 10.14.163.67

The installation was done like the previous blog , we will only show the differences for the screenshots

-The disks were configured to use AFD (see this blog )
-The patch 27494830 was applied (see this blog )

During my first try I got following errors when executing the root.sh script

[root@oraadserver network-scripts]# /u01/app/19.0.0.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/19.0.0.0/grid

…
…
CRS-2672: Attempting to start 'ora.gpnpd' on 'oraadserver'
CRS-2676: Start of 'ora.gpnpd' on 'oraadserver' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'oraadserver'
CRS-2674: Start of 'ora.gipcd' on 'oraadserver' failed
CRS-2673: Attempting to stop 'ora.gpnpd' on 'oraadserver'
CRS-2677: Stop of 'ora.gpnpd' on 'oraadserver' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'oraadserver'
CRS-2677: Stop of 'ora.cssdmonitor' on 'oraadserver' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'oraadserver'
CRS-2677: Stop of 'ora.mdnsd' on 'oraadserver' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'oraadserver'
CRS-2677: Stop of 'ora.evmd' on 'oraadserver' succeeded
CRS-2673: Attempting to stop 'ora.driver.afd' on 'oraadserver'
CRS-2677: Stop of 'ora.driver.afd' on 'oraadserver' succeeded
CRS-4000: Command Start failed, or completed with errors.
2020/10/23 09:54:11 CLSRSC-119: Start of the exclusive mode cluster failed
Died at /u01/app/19.0.0.0/grid/crs/install/crsinstall.pm line 2439.
[root@oraadserver network-scripts]#

According this document, it seems that there is a bug normally fixed starting with 18.8. In my case with Oracle 19c, it was not. As workaround I choose cluster name smaller than 15 characters.
CLSRSC-119: Start of the exclusive mode cluster failed While Running root.sh While Installing Grid Infrastructure 19c (Doc ID 2568395.1)

The root.sh was successfully executed

Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/19.0.0.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/19.0.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/oracle/crsdata/oraadserver/crsconfig/rootcrs_oraadserver_2020-10-23_02-43-14PM.log
2020/10/23 14:43:19 CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'.
2020/10/23 14:43:19 CLSRSC-594: Executing installation step 2 of 19: 'ValidateEnv'.
2020/10/23 14:43:19 CLSRSC-363: User ignored prerequisites during installation
2020/10/23 14:43:19 CLSRSC-594: Executing installation step 3 of 19: 'CheckFirstNode'.
2020/10/23 14:43:21 CLSRSC-594: Executing installation step 4 of 19: 'GenSiteGUIDs'.
2020/10/23 14:43:21 CLSRSC-594: Executing installation step 5 of 19: 'SetupOSD'.
2020/10/23 14:43:21 CLSRSC-594: Executing installation step 6 of 19: 'CheckCRSConfig'.
2020/10/23 14:43:22 CLSRSC-594: Executing installation step 7 of 19: 'SetupLocalGPNP'.
2020/10/23 14:43:41 CLSRSC-594: Executing installation step 8 of 19: 'CreateRootCert'.
2020/10/23 14:43:46 CLSRSC-594: Executing installation step 9 of 19: 'ConfigOLR'.
2020/10/23 14:43:52 CLSRSC-594: Executing installation step 10 of 19: 'ConfigCHMOS'.
2020/10/23 14:43:52 CLSRSC-594: Executing installation step 11 of 19: 'CreateOHASD'.
2020/10/23 14:43:55 CLSRSC-594: Executing installation step 12 of 19: 'ConfigOHASD'.
2020/10/23 14:43:56 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2020/10/23 14:44:03 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2020/10/23 14:45:25 CLSRSC-594: Executing installation step 13 of 19: 'InstallAFD'.
2020/10/23 14:46:46 CLSRSC-594: Executing installation step 14 of 19: 'InstallACFS'.
2020/10/23 14:48:03 CLSRSC-594: Executing installation step 15 of 19: 'InstallKA'.
2020/10/23 14:48:06 CLSRSC-594: Executing installation step 16 of 19: 'InitConfig'.

[INFO] [DBT-30161] Disk label(s) created successfully. Check /u01/app/oracle/cfgtoollogs/asmca/asmca-201023PM024840.log for details.


2020/10/23 14:49:55 CLSRSC-482: Running command: '/u01/app/19.0.0.0/grid/bin/ocrconfig -upgrade oracle oinstall'
CRS-4256: Updating the profile
Successful addition of voting disk aecbe50ed1474f05bfbb0fd8192e5358.
Successfully replaced voting disk group with +CRS_DSKGP.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   aecbe50ed1474f05bfbb0fd8192e5358 (AFD:CRS_DSK) [CRS_DSKGP]
Located 1 voting disk(s).
2020/10/23 14:51:03 CLSRSC-594: Executing installation step 17 of 19: 'StartCluster'.
2020/10/23 14:52:36 CLSRSC-343: Successfully started Oracle Clusterware stack
2020/10/23 14:52:36 CLSRSC-594: Executing installation step 18 of 19: 'ConfigNode'.
2020/10/23 14:57:38 CLSRSC-594: Executing installation step 19 of 19: 'PostConfig'.
2020/10/23 15:00:11 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded
[root@oraadserver oracle]#

The Oracle cluster verification failed because some prerequisites were ignored

At the end of the installation we can check the status of the cluster

[oracle@oraadserver ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_DSKGP.VOL_ACFS.advm
               ONLINE  ONLINE       oraadserver              STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       oraadserver              STABLE
ora.chad
               ONLINE  ONLINE       oraadserver              STABLE
ora.data_dskgp.vol_acfs.acfs
               ONLINE  ONLINE       oraadserver              mounted on /share_ac
                                                             fs,STABLE
ora.net1.network
               ONLINE  ONLINE       oraadserver              STABLE
ora.ons
               ONLINE  ONLINE       oraadserver              STABLE
ora.proxy_advm
               ONLINE  ONLINE       oraadserver              STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       oraadserver              STABLE
      2        ONLINE  OFFLINE                               STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.CRS_DSKGP.dg(ora.asmgroup)
      1        ONLINE  ONLINE       oraadserver              STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.DATA_DSKGP.dg(ora.asmgroup)
      1        ONLINE  ONLINE       oraadserver              STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  OFFLINE                               STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       oraadserver              Started,STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       oraadserver              STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       oraadserver              STABLE
ora.oraadserver.vip
      1        ONLINE  ONLINE       oraadserver              STABLE
ora.qosmserver
      1        ONLINE  ONLINE       oraadserver              STABLE
ora.scan1.vip
      1        ONLINE  OFFLINE                               STABLE
--------------------------------------------------------------------------------
[oracle@oraadserver ~]$

Once the installation done, we will be able to create an ACFS filesystem which will be automatically mounted at server startup. See here for the creation of ACFS.

Conclusion

For using ACFS, a Grid Infrastructure for a cluster must be configured (starting with Oracle 12). I guess this is the reason why we have a scan and an interconnect configured on non HA ODA, even if this scan is disabled on ODAs

Cet article Oracle 19c Grid Infrastructure for a cluster est apparu en premier sur Blog dbi services.

ODA 19.9 software release is out

Thu, 2020-10-29 12:30
Introduction

6 months after the 19.6, the first 19c production release, here comes the 19.9 Oracle Database Appliance patch with major improvements.

Will my ODA supports this 19.9 patch?

First, the 19.9 release will be the same for all ODAs, as usual. Like 19.6, the oldest ODA compatible with this release will be the X5-2. Don’t expect to install 19c on older models. X4-2 is stuck to 18.8, X3-2 is stuck to 18.5 and V1 is stuck to 12.2. If you are still using these models, please consider an upgrade to X8-2 and 19c to go back to supported hardware and software.

What are the new features?

For sure, 19.9 includes all the latest patches for all database homes, including for those versions no more supported with Premier Support (provided patches are the very latest from the 20th of October, 2020).

It’s not a new feature, but odacli now support Data Guard since 19.8. Expect 19.9 to be more reliable regarding this feature. This was a great improvement over previous versions.

The most important new feature is dedicated for those willing to use virtualization. The OLVM/KVM stack has now replaced OVM/Xen. It means the implicit death of oakcli and the end of having to manage 2 appliance tools for ODA, odacli finally becoming the main and only tool for appliance management, associated with the GUI if you need it. This OLVM/KVM virtualization stack comes for sure with hard partitioning for you ODAs, useful if you have Enterprise Edition licenses: the cores not enabled for databases are available for running application VMs. And now it also works with ODA X8-2S and X8-2M: virtualization was always limited to HA ODAs before. This can be a game changer as ODA lite, especially X8-2M, has plenty of resources for other purposes than databases.

If you plan to use virtualization, you do not have to deploy a specific ISO image for virtualized mode anymore. All ODAs will now be deployed as Bare Metal, and virtualization is running on top of this Bare Metal deployment. Your databases will continue to run on Bare Metal, having the advantages of both previous solutions (fully virtualized or Bare Metal without any VM).

What is also interesting is the ability to dedicate additional Bare Metal CPU pools to other DBhomes for better database isolation. For example, you can imagine to have a 2-core pool for test databases and a 4-core pool for production databases. CPU pools are also available in the VM CPU pools flavour, here for dedicating cores to group of VMs on your ODA.

Finally, the SE-HA high availability feature for SE2 (the one that replaced RAC removed from the SE recently) seems to be included as well with odacli.

Still able to run older databases with 19.9?

Yes, 19.9 will let you run all versions of database starting from 11.2.0.4. However, it’s highly recommended to migrate to 19c, as it’s the only version with long term support available now. Deploying 19.9 and planning to migrate your databases in the next months is definitely a brilliant idea. With ODA you can easily migrate your databases with odacli move-database: this move to another home running 19c will update your database to 19c accordingly.

Is is possible to upgrade to 19.6 from my current release?

You will need to already run 19.x release, starting from 19.5, to apply this patch. If your ODA is running on 18.8, you will have to patch to 19.6 prior applying 19.9. If your ODA is running on 18.7 or older 18.x release, an upgrade to 18.8 will be needed before patching to 19.6. If you are using older versions, it’s highly recommended to do a reimaging of your ODA. It will be easier than applying 3+ patches. And you’ll benefit from a brand new and clean ODA. Patching is still a lot of work, and if you didn’t patch regularly, it could be tough to bring your ODA to the latest version. Reimaging is a lot of work too, but it’s success guaranteed.

If you are using a virtualized ODA with OVM/Xen, you will not be able to patch. A complete reimaging is needed. But it’s worth it.

Conclusion

19.9 is a major release for customers using ODAs. Apart from maturity of databases including 19c, you will benefit from virtualization even for lite ODA models. And virtualization keeping Bare Metal databases is a great solution.

Cet article ODA 19.9 software release is out est apparu en premier sur Blog dbi services.

Oracle Restart 19c and ACFS

Tue, 2020-10-27 10:53

In this blog we are going to install an Oracle Restart 19c with ASM Filter Driver (AFD). I am using following configuration
-Oracle 19c
-Oracle Linux Server 7.6
-Kernel 4.14.35-1902.2.0.el7uek.x86_64

Once the Oracle Restart installed, I will create an ACFS filesystem

We will have to configure following disks for AFD
-/dev/sdc
-/dev/sdd

The Oracle downloaded software was unzipped to the GRID_HOME

[oracle@oraadserver u01]$ unzip -d /u01/app/19.0.0.0/grid LINUX.X64_193000_grid_home.zip

After setting the variables ORACLE_HOME and ORACLE_BASE, we then use with user root the command asmcmd afd_label to provision disk devices for use with Oracle ASM Filter Driver.

[root@oraadserver dev]# export ORACLE_HOME=/u01/app/19.0.0.0/grid
[root@oraadserver dev]# export ORACLE_BASE=/tmp
[root@oraadserver dev]# /u01/app/19.0.0.0/grid/bin/asmcmd afd_label DATA /dev/sdc --init
[root@oraadserver dev]# /u01/app/19.0.0.0/grid/bin/asmcmd afd_label CRS /dev/sdd --init

We can verify the status of the disks with the command afd_lslbl

[root@oraadserver ~]# /u01/app/19.0.0.0/grid/bin/asmcmd afd_lslbl /dev/sdc
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
DATA                                  /dev/sdc
[root@oraadserver ~]# /u01/app/19.0.0.0/grid/bin/asmcmd afd_lslbl /dev/sdd
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
CRS                                   /dev/sdd
[root@oraadserver ~]#

In my case with the kernel 4.14.35-1902.2.0.el7uek.x86_64, I had to apply the following
Patch 27494830: BUILD UEK5U2 COMPATIBLE ACFS GRID KERNEL MODULES
To avoid this error during the installation

Action - To proceed, do not specify or select the Oracle ASM Filter Driver option.  Additional Information:
 - AFD-620: AFD is not supported on this operating system version: '4.14.35-1902.2.0.el7uek.x86_64' 

The patch was applied before the installation as described in the following documentation
How to Apply a Grid Infrastructure Patch Before Grid Infrastructure Configuration (before root.sh or rootupgrade.sh or gridsetup.bat) is Executed (Doc ID 1410202.1)
The opatch version was verified

[oracle@oraadserver u01]$ /u01/app/19.0.0.0/grid/OPatch/opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.
[oracle@oraadserver u01]$

After the patch was unpacked

[oracle@oraadserver u01]$ unzip p27494830_193000ACFSRU_Linux-x86-64.zip

And then the patch was applied

[oracle@oraadserver u01]$ /u01/app/19.0.0.0/grid/gridSetup.sh -silent -applyRU /u01/27494830/
Preparing the home to patch...
Applying the patch /u01/27494830/...
Successfully applied the patch.
The log can be found at: /tmp/GridSetupActions2020-10-20_09-32-54PM/installerPatchActions_2020-10-20_09-32-54PM.log

And now we lunch the install. Note that only some screenshots will be shown. And for the non shown pictures the default was kept.

[oracle@oraadserver grid]$ pwd
/u01/app/19.0.0.0/grid
[oracle@oraadserver grid]$ ./gridSetup.sh

For the ASM diskgroups configuration

The groups of my installation

I got some warnings that Idecided to ignore (just a test environment)



The two scripts are executed

[root@oraadserver oraInventory]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@oraadserver oraInventory]#
[root@oraadserver oraInventory]# /u01/app/19.0.0.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/19.0.0.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/19.0.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/oracle/crsdata/oraadserver/crsconfig/roothas_2020-10-20_09-58-51PM.log
2020/10/20 21:58:55 CLSRSC-363: User ignored prerequisites during installation
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node oraadserver successfully pinned.
2020/10/20 22:00:43 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

oraadserver     2020/10/20 22:03:14     /u01/app/oracle/crsdata/oraadserver/olr/backup_20201020_220314.olr     2451816761
2020/10/20 22:03:49 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
[root@oraadserver oraInventory]#


After the installation we can validate that the ASM was up

[root@oraadserver oraInventory]# crsctl check has
CRS-4638: Oracle High Availability Services is online
[root@oraadserver oraInventory]# ps -ef | grep pmon
root     10153  1730  0 22:10 pts/0    00:00:00 grep --color=auto pmon
oracle   26037     1  0 22:05 ?        00:00:00 asm_pmon_+ASM
[root@oraadserver oraInventory]#

Now using asmca, let’s configure an ACFS filesystem . Basically we
-Create a CRS diskgroup
-In CRS diskgroup we create a volume
-In this volume we create an ACFS filesystem and mount it

[oracle@oraadserver ~]$ which asmca
/u01/app/19.0.0.0/grid/bin/asmca
[oracle@oraadserver ~]$ asmca

The ACFS filesystem is now mounted

[root@oraadserver ~]# df -h /share_acfs/
Filesystem           Size  Used Avail Use% Mounted on
/dev/asm/acfs_vol-3  5.0G  319M  4.7G   7% /share_acfs
[root@oraadserver ~]#

Now let’s reboot the system and verify that the acfs filesystem is mounted.

[root@oraadserver ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             1.8G  8.0K  1.8G   1% /dev
tmpfs                1.8G  637M  1.2G  35% /dev/shm
tmpfs                1.8G  8.8M  1.8G   1% /run
tmpfs                1.8G     0  1.8G   0% /sys/fs/cgroup
/dev/mapper/ol-root   47G   14G   34G  29% /
/dev/sda1            497M  230M  268M  47% /boot
tmpfs                368M     0  368M   0% /run/user/54323
tmpfs                368M     0  368M   0% /run/user/0

No. When trying to manually mount it, I got this error

[root@oraadserver ~]# /bin/mount -t acfs /dev/asm/acfs_vol-3 /share_acfs
mount.acfs: CLSU-00107: operating system function: open64; failed with error data: 2; at location: OOF_1
mount.acfs: CLSU-00101: operating system error message: No such file or directory
mount.acfs: CLSU-00104: additional error information: open64 (/dev/ofsctl)
mount.acfs: ACFS-00502: Failed to communicate with the ACFS driver.  Verify the ACFS driver has been loaded.
[root@oraadserver ~]#

I then load the acfs drivers

[root@oraadserver ~]#  /u01/app/19.0.0.0/grid/bin/acfsload start -s
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9294: updating file /etc/sysconfig/oracledrivers.conf
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9154: Loading 'oracleoks.ko' driver.
ACFS-9154: Loading 'oracleadvm.ko' driver.
ACFS-9154: Loading 'oracleacfs.ko' driver.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9309: ADVM/ACFS installation correctness verified.
[root@oraadserver ~]#

When checking the volume, it was disabled

ASMCMD> volinfo --all
Diskgroup Name: CRS

         Volume Name: ACFS_VOL
         Volume Device: /dev/asm/acfs_vol-3
         State: DISABLED
         Size (MB): 5120
         Resize Unit (MB): 64
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /share_acfs

So I enable enable it

ASMCMD> volenable –all
ASMCMD> volinfo --all
Diskgroup Name: CRS

         Volume Name: ACFS_VOL
         Volume Device: /dev/asm/acfs_vol-3
         State: ENABLED
         Size (MB): 5120
         Resize Unit (MB): 64
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /share_acfs

ASMCMD>

And then I run the mount command

[root@oraadserver trace]# /bin/mount -t acfs /dev/asm/acfs_vol-3 /share_acfs

[root@oraadserver trace]# df -h /share_acfs/
Filesystem           Size  Used Avail Use% Mounted on
/dev/asm/acfs_vol-3  5.0G  319M  4.7G   7% /share_acfs
[root@oraadserver trace]#

It was strange to manually mount the ACFS filesystem at system reboot but it is mentioned in Oracle documentation
Starting with Oracle Database 12c, Oracle Restart configurations do not support the Oracle ACFS registry.
This means that the Oracle ACFS file system resource is supported only for Oracle Grid Infrastructure cluster configurations; it is not supported for Oracle Restart configurations. When using ACFS on Oracle Restart, we have to mount the filesystem manually mount and umount the ACFS filesystem

Conclusion

When using ACFS with Oracle Restart, we have to manually mount and umount the ACFS filesystem when the system stops or starts. This can be done by some customized scripts.

Cet article Oracle Restart 19c and ACFS est apparu en premier sur Blog dbi services.

Control-M/EM Manage job’s submission by using the ctm_pause utility

Mon, 2020-10-26 05:35

Hi everybody,

Introduction

Today a quick trick to pause job submissions on Control-M
sometimes it can be useful, during some operations or investigations related to Control-M behavior, to avoid job submission (in our case all jobs on AJF must be held but for specific folders or hosts you can use workload manager)
You got two ways to put Control-M in pause

Using command line

CTMSRVCENTOS% ctm_pause Y
CONTROL-M/Server Pause mode is already set to Y
CTMSRVCENTOS% ctm_pause
CONTROL-M/Server Pause mode is Y
CTMSRVCENTOS% id
uid=1001(controlm) gid=3110(controlm) groups=3110(controlm) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
CTMSRVCENTOS%

use Y or N to activate the pause mode

Using CCM interface

select you controlm server components then right click and pause,validate the confirmation pop-up

Conclusion

Here we have a quick and efficient way to pause jobs submission on Control-M when you want to investigate abnormal behavior in Control-M/Server, without shutting it down, or if you want to start it up after an upgraded.that allows you to perform your task or investigation with more serenity.

Cet article Control-M/EM Manage job’s submission by using the ctm_pause utility est apparu en premier sur Blog dbi services.

Disk usage on ODA – Free MB and usable MB

Wed, 2020-10-21 02:34
Introduction

Oracle Database Appliances rely on ASM to manage disk redundancy. And ASM is brilliant. Compared to RAID, redundancy is managed at the block level. For NORMAL redundancy, which is similar to RAID1, you need at least 2 disks, but it can also work with 3 disks, 4 disks, 5 disks and so on. There is no need for parity at the disk level. HIGH redundancy, which does not exist in RAID technology, is basically a triple security. Each block is written on 3 different disks. For this kind of redundancy, you need at least 3 disks, but you can also use 4 disks, 5 disks, 6 disks and so on. You can add and remove disks online, without any downtime, using various degrees of parallelism to increase speed or to lower CPU usage during the rebalancing operations.

RAW space vs usable space

As there is no RAID controler in your ODA, you will see from the system, and more precisely from ASM instance, the RAW space available. For example, on ODA X8-2M with 4 disks, RAW capacity is 25.6TB. This is the free space size you would see on this kind of ODA if there were no databases configured on it. This is not a problem as soon as you understand that you don’t really have these 25.6TB. There is also a usable space notion. One should think it is space available with redundancy being computed, but it’s not exactly that. It can be quite different actually depending on your ODA.

Real world example

For my example, I will use an ODA X8-2M with 4 disks running on 19.6. Redundancy has been set to NORMAL, and DATA/RECO ratio to 90/10. Several databases are running on this ODA. Regarding the spec sheet of this server, the ODA X8-2M comes with 2x 6.4TB disks as standard, and you can add up to 5 expansions, each expansion being a bundle of 2x 6.4TB disks. RAW capacity starts from 12.4TB and goes up to 76.8TB. As you probably know, a 6.4TB disk hasn’t 6.4TB of real usable capacity, so don’t expect to store more than 5.8TB on each disk. But this is not related to ODA. It’s been years that disk manufacturers are writing optimistic sizes on their disks.

I’m using V$ASM_DISKGROUP dynamic view from +ASM1 instance to check available space and free space.

desc v$asm_diskgroup
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NUMBER                                       NUMBER
 NAME                                               VARCHAR2(30)
 SECTOR_SIZE                                        NUMBER
 LOGICAL_SECTOR_SIZE                                NUMBER
 BLOCK_SIZE                                         NUMBER
 ALLOCATION_UNIT_SIZE                               NUMBER
 STATE                                              VARCHAR2(11)
 TYPE                                               VARCHAR2(6)
 TOTAL_MB                                           NUMBER
 FREE_MB                                            NUMBER
 HOT_USED_MB                                        NUMBER
 COLD_USED_MB                                       NUMBER
 REQUIRED_MIRROR_FREE_MB                            NUMBER
 USABLE_FILE_MB                                     NUMBER
 OFFLINE_DISKS                                      NUMBER
 COMPATIBILITY                                      VARCHAR2(60)
 DATABASE_COMPATIBILITY                             VARCHAR2(60)
 VOTING_FILES                                       VARCHAR2(1)
 CON_ID                                             NUMBER

One can guess that real diskgroup free % is normally FREE_MB/TOTAL_MB:

SQL> set lines 200
SQL> select GROUP_NUMBER, NAME, TOTAL_MB, FREE_MB, USABLE_FILE_MB, TYPE from v$asm_diskgroup;

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB USABLE_FILE_MB TYPE
------------ ------------------------------ ---------- ---------- -------------- ------
           1 DATA                             21977088    9851876        2178802 NORMAL
           2 RECO                              2441216    1421004         405350 NORMAL


Select round(9851876/21977088*100,1) "% Free"  from dual;

    % Free
----------
      44.8


Free space is more than 44% on my ODA. Not bad.

And when I use USABLE_FILE_MB to get another metric for the same thing:

SQL> set lines 200
SQL> select GROUP_NUMBER, NAME, TOTAL_MB, FREE_MB, USABLE_FILE_MB, TYPE from v$asm_diskgroup;

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB USABLE_FILE_MB TYPE
------------ ------------------------------ ---------- ---------- -------------- ------
           1 DATA                             21977088    9851876        2178802 NORMAL
           2 RECO                              2441216    1421004         405350 NORMAL


Select round(2178801/21977088*100,1) "% Free"  from dual;

    % Free
----------
       9.9

This is bad. According to this metric, I have less than 10% free in that diskgroup. I’m getting anxious… I thought I was fine but I’m now critical?

What is really USABLE_FILE_MB?

When you look into the documentation, it’s quite clear:

  • USABLE_FILE_MB is free MB according to diskgroup redundancy. Among 9’851’876 MB, only half, 4’925’938 MB of data, can be used in that diskgroup. This is for NORMAL redundancy (each block exists on 2 different disks). This is quite relevant regarding what has been said before
  • USABLE_FILE_MB is free MB according to a disk being able to get lost and redundancy would be guaranteed. On this ODA with 4 disks, ¼ of the total disk space shouldn’t be considered as available unlike RAID system (a loss of one disk is not visible by the system). For a total MB of 21’977’088, only 16’482’816 MB should be considered as usable for DATA
  • Finally, USABLE_FILE_MB is the mix of these 2 facts. For NORMAL redundancy, the formula is USABLE_FILE_MB = (FREE_MB – TOTAL_MB/nb_disks) / 2 = (9’851’876 MB – 5’494’272 MB) / 2 = 2’178’802 MB

Let’s take another example to be sure. This time it’s an ODA X8-2M with 6 disks in NORMAL redundancy. Let’s do the math:

SQL> set lines 200
SQL> select GROUP_NUMBER, NAME, TOTAL_MB, FREE_MB, USABLE_FILE_MB, TYPE from v$asm_diskgroup;

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB USABLE_FILE_MB TYPE
------------ ------------------------------ ---------- ---------- -------------- ------
           1 DATA                             32965632   25028756        9767242 NORMAL
           2 RECO                              3661824    2549852         969774 NORMAL

select round((25028756 - 32965632/6)/2,1) "DATA_USABLE_FILE_MB" from v$asm_diskgroup where name='DATA';

DATA_USABLE_FILE_MB
-------------------
            9767242

The formula is correct.

Should I use USABLE_FILE_MB for monitoring?

That’s a good question. Using USABLE_FILE_MB for monitoring is considering the worst case. Using FREE_MB/TOTAL_MB is considering the best case. Using FREE_MB seems recommended but with lower values than a normal filesystem: WARNING should be triggered when 65/70% is reached, and CRITICAL should be triggered when 80/85% is reached. For 2 reasons: because the volume will be filled 2 times faster than a view through a RAID system (3 times faster with HIGH redundancy) and because when your disks are nearly full, the only way to extend the volume is to buy new disks from Oracle (if you have not reached the limit).

Remember that the only resilience guarantee for an ODA is not having enough space in diskgroups for loosing one disk but having a functional Data Guard configuration. It’s why I never configure HIGH redundancy on ODA, it’s a waste of disk space and it does not provide me much higher failure tolerance (I still have “only” 2 power supplies and 2 network interfaces).

To make it crystal clear, let’s compare again to a RAID system. Imagine you have a 4x 6TB disks RAID1 system. These 4 disks have a RAW capacity of 24TB, but only 12TB are usable. If you loose one disk, 12TB are still usable, but you’ve lost redundancy for half of the data. With ASM in NORMAL redundancy, you can see a total of 24TB, but only 12TB is really available for your databases. But if you look at the USABLE_FILE_MB, you will find that only 9TB is usable, because you redundancy can survive to a disk crash. The RAID is simply not able to do that.

Furthermore, if you want to do the same with RAID1 you could, but it means that you will need 5 disks instead of 4. The fifth one being the spare disk to rebuild redundancy in case of disk failure of one of the four disks.

Should I use storage even if USABLE_FILE_MB is 0?

Yes, you can. But you have to know that if you loose a disk, redundancy cannot be guaranteed anymore. Like if it were on a RAID system. You can also see negative values in USABLE_FILE_MB.

And what about the number of disks?

For sure, the more disk you have, the less space you will “loose” from the USABLE_FILE_MB view. An ODA with 3 or 4 disks with NORMAL redundancy is definitely not very comfortable, but starting from 6 disks, this USABLE_FILE_MB becomes much more convenient.

On a 2-disk ODA with NORMAL redundancy, there is no way of keeping redundancy after loosing a disk. That’s quite obvious. X8-2S and X8-2M with base disk configuration are not that nice for this reason.

Number of disks is not only a matter of storage size you need but also an increased level of security for your databases. The more disks you have, the more disk failure you can survive keeping redundancy (if disks are not having simultaneous failures for sure).

Conclusion

ODA storage is commonly misunderstood, because it does not use classic RAID. ASM is very powerful and more secure than a RAID system. Don’t hesitate to order more disks than needed on your ODAs. Yes it’s expensive but this is a good investment for the next 5 years. And it’s usually cheaper to order additional disks with the ODA than ordering them later.

Cet article Disk usage on ODA – Free MB and usable MB est apparu en premier sur Blog dbi services.

SQL Server: Generating SQL script using PowerShell and Template file

Sun, 2020-10-18 11:46

In this blog post, I will share with you a small PowerShell script I did recently.

I have noticed that my customer performs a very repetitive and time-consuming task almost every day.
New columns are added to tables on their business-critical database and they need to maintain SQL scripts file with all the ALTER TABLE statements for each new column.

For every new column, my customer copy-pastes the following SQL Script and then change parts of it.

/***********************************
*
* New column 
*            Schema:       Order
*            Table:        TestTable2     
*            Column:       ColumnName1    
*            
* History    
*            Date:         18/10/2020 
*            User:         Steven Naudet 
*
************************************/

IF NOT EXISTS (
       SELECT * 
       FROM sys.tables AS t 
       JOIN sys.[columns] AS c ON t.[object_id] = c.[object_id]
       JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id]  
       WHERE 1=1 
       AND s.name = 'Order'  
       AND t.name = 'TestTable2' 
       AND c.name = 'ColumnName1' 
) 
BEGIN 
       PRINT 'Altering table Order.TestTable2 adding column [ColumnName1]' ; 
       ALTER TABLE [Order].TestTable2 
       ADD 
       ColumnName1 NOT NULL; 
END 

/***********************************
*
* End New column ColumnName1  
*
************************************/

The highlighted lines are manually edited by my customer every time there’s a new column to be added to the database, which can occur 20 times per week.
I decided to write a PowerShell function to do this task faster so my customer can work on more interesting things instead.

The idea is to use a Template file for the SQL Script. The file is similar to the SSMS templates.
The PowerShell script modifies the template and as output sends the SQL to Clipboard using Set-Clipboard.
Consecutive calls to the function will add the SQL commands after one another in the Clipboard. This way my customer can just Paste the generated SQL script to his SQL source control tool.

You can see the script in action with the GIF below.

PowerShell Script in action GIF

Here is the script.

function New-AddColumnSQL {

    [CmdletBinding()]
    param (
        [Parameter(Mandatory=$true)][string] $Schema,
        [Parameter(Mandatory=$true)][string] $Table,
        [Parameter(Mandatory=$true)][string] $Column,
        [Parameter(Mandatory=$true)][string] $Type,
        [Parameter(Mandatory=$false)][string] $defaultValue,
        [Parameter(Mandatory=$false)][switch] $isNotNull = $false,
        [Parameter(Mandatory=$false)][string] $User = 'Steven NAUDET'
    )

    $TemplateFile = 'Z:\scripts\TemplateAddColumn.sql'

    $Clipboard = Get-Clipboard
    
    # Clear Clipboard if first call to the function
    if ($Clipboard -like '*Altering table*') {
        $returnMessage = 'SQL Script appended to Clipboard'
    } else {
        $returnMessage = 'SQL Script pasted to Clipboard'
        Set-Clipboard -Value $null
    }

    $ColumnDef = $Type

    # NOT NULL
    if($isNotNull) { 
        $ColumnDef = $ColumnDef + ' NOT'
    }
    $ColumnDef = $ColumnDef + ' NULL'

    # DEFAULT value
    if($defaultValue) { 
        $ColumnDef = $ColumnDef + ' DEFAULT ' + $defaultValue
    }

    $SQLscript = Get-Item -Path $TemplateFile | Get-Content
    
    $SQLscript = $SQLscript.Replace('<Date>', (Get-Date -UFormat "%d/%m/%Y"))
    $SQLscript = $SQLscript.Replace('<SchemaName>', $Schema)
    $SQLscript = $SQLscript.Replace('<TableName>', $Table)
    $SQLscript = $SQLscript.Replace('<ColumnName>', $Column)
    $SQLscript = $SQLscript.Replace('<UserName>', $User)
    $SQLscript = $SQLscript.Replace('<ColumnDefinition>', $ColumnDef)

    Set-Clipboard $SQLscript -Append

    return $returnMessage

}

There’s probably a lot of room for improvement for this code but the goal of this blog post is to show you how handy PowerShell can be. It can help you save a lot of time.
I took about 1 hour to write this code and I’m sure my customer will save more than that every month.

Cet article SQL Server: Generating SQL script using PowerShell and Template file est apparu en premier sur Blog dbi services.

Oracle Database Appliance vs Oracle Cloud Infrastructure

Sun, 2020-10-18 05:33
Introduction

Oracle Database Appliances are very popular these days. And not only among new customers for this kind of engineered systems. Almost all customers already using old generation ODAs are renewing their infrastructure by choosing again ODAs, meaning that the solution is good enough and probably better than anything else. But now, public clouds are a real alternative to on-premise servers, and Oracle Cloud Infrastructure is a solid competitor vs Amazon and Azure public clouds. So what’s the best solution for your databases, ODA or OCI? Let’s do the match.

Round 1 – Cost

Yes, it is important. You will need to buy ODAs and you will need a budget for that. Nothing new regarding this platform, it requires an investment. ODA is cheaper since light models are available, but if you need significant amount of storage, it comes at a cost. But hopefully, the cost is quite similar to another x86 platform, and the ODA doesn’t have these hidden costs due to additional work for troubleshooting compatibility issues.
Cost works differently on OCI. Basically, you will pay for servers, storage, services on a monthly basis. No initial investment is needed, and that is one of the advantages of OCI. However, don’t expect the “TCO” to be lower than acquiring your own hardware. I do not mean that cloud solutions are expensive, but the cost will be quite similar to an on-premise solution after some years. Going to the cloud is mainly changing your mind about what’s an infrastructure. Is it servers you manage on your own or is it a platform for running your information system?
There is no winner in this round, you will only know after several years which solution would have been the less expensive.

Winner: none

Round 2 – Deployment speed

ODA allows fast deployment of a new database infrastructure. Actually, it’s the best on-premise solution regarding that point. And it’s a serious advantage over DIY platforms. Being able to create your first database the same day you open the box is quite nice. But OCI is even better, because at this very moment we are talking now, your future servers are already available, Terabytes of storage are waiting for you, and databases are almost there, few clicks away from now. If you’re looking for fast deployment, OCI is an easy winner.

Winner: OCI

Round 3 – Security

Everybody is talking about security. Is my database safer in the cloud than in my own datacenter? Actually, it’s quite hard to tell. For sure, OCI is a public cloud, meaning that your database can be reached from virtually everywhere. But you will probably build strong security rules to protect your cloud infrastructure. You will use IPSec VPN between OCI and your on-premise site, or a FastConnect channel to dedicate a link between your on-premise equipment and OCI avoiding data to transit through the internet. Putting your database in the cloud is not less secure than giving remote connection on your infrastructure to your employees or providers. Furthermore, databases in OCI are stored using encryption, even with Standard Edition and without the need for Advanced Security option.
On ODA, you database is in your network, meaning not on something public and meaning less visible. This is good, but again, only if you have good security rules inside your company.

Winner: none

Round 4 – Performance

ODA is a strong performer, especially the X8-2M model. With up to 75TB of NVMe SSD, it’s quite tough to achieve better performance with anything else. Yes you could grab few MB/s more or ms less with few other solutions, but do you really think that your users will see the difference? No. And what about OCI? OCI rely on SSD storage only, that’s a very good start. And do they offer NVMe? Yes, for sure. Bare metals servers (BM Dense I/O) provide up to 51TB of RAW capacity based on 8 NVMe drives. And something tells me that these servers are actually nearly the same as ODA X7-2Ms. So expect similar performance on both solutions.

Winner: none

Round 5 – License management

No doubt that on-demand capacity of Enterprise licenses is one of the key feature of the ODA. You can start with only 1 Enterprise license on each ODA, and increase the number of licenses when you need more resources. A kind of fine tuning for the licenses.

On OCI, you can choose to bring your own license you bought long time ago, and keep your investment for later if for some reason you would like to go back to on-premise infrastructure. Or you can choose to include the license fees into the monthly fees. With the first option, you manage your licenses as you always did, and should be careful when you increase the cloud resources dedicated to your databases (mainly the oCPUs). With the second option, you don’t have to manage your licenses anymore: you don’t need to buy them, pay the yearly support, or review them regularly because all is included with your OCI database services. It’s simply a great feature.

Winner: OCI

Round 6 – Simplicity

ODA and OCI share the same goal: simplify your database infrastructure. ODA is simplifying by providing the best automation available for deploying complex Oracle stack. And when you come from an existing on-premise infrastructure, migration to ODA will be quite easy. OCI looks even more simplifying, but if you will not have to work on the servers, you’ll have to think about how to implement your infrastructure. Which subnet for my databases? Should I also move my application servers? What kind of network connectivity with my on-premise environment? Which kind of database service fits my needs?

If you’re starting from scratch with Oracle databases, it’s probably more simple to go directly to OCI. If you’re migrating from an existing on-premise environment, it’s probably more simple to replace your existing servers with ODAs. No winner here.

Winner: none

Round 7 – Control

For some customers, being able to control their infrastructure is vital. On public clouds, you will not have control on everything, because someone will do a part of the maintenance job, mostly automated tasks. And this is for some other customers something they don’t want to manage. On ODA, you control everything on your server: first, it’s not mandatory to connect it to the internet. Updates on ODA cannot be automated and will be applied manually through good old zipfiles, and in case of serious problems, ODA is fast to redeploy. So if you need to have total control over your infrastructure, the ODA is the best solution for you.

OCI is only a good solution if you already planned to lose some control, for obvious workload reasons.

Winner: ODA

Round 8 – Resilience

Disaster recovery solutions were not so common 10 years ago. People were relying on tape backups, were confident about this solution and were believing they would be able to restore the tape “somewhere”, without asking them where actually was “somewhere”. At best, the old servers were kept for disaster recovery usage, in the same rack.
This has definitely changed, and now disaster recovery is part of each new infrastructure design. And regarding the software side of the database, this is something mature and highly reliable (with Data Guard or Dbvisit standby). The most complex part being to design the split into multiple datacenters (2, most of the time). Implementing that cleverly, avoiding Single Point Of Failure that could wipe out the efforts to achieve high resiliency, being a tough challenge. ODA is a server like others, and you will have to do the same amount of work to design a high resilient infrastructure.

Cloud providers have been thinking about disaster recovery since the very beginning. The datacenters are spread all around the world, and each one has separate availability domains (isolated building blocks), allowing multiple levels of disaster recovery scenarios. Furthermore, storage and backups naturally embed this high resilience. And as everyone will use the same mechanisms, you can trust OCI regarding resilience.

As a conclusion, it’s nearly impossible to reach the level of resilience of OCI on your on-premise ODA infrastructure, that must be said…

Winner: OCI

What about the other solutions?

For sure, it still possible to build your own database infrastructure with classic servers. But do you really have time for that?
EXADATA is also a nice solution if you need such a beast for multi-TB databases with high number of transactions or fastest BI platform. And now it can bring you both the advantages of OCI and appliance with the Cloud@customer mode. Oracle brings the server in your datacenter, and you only pay for it monthly as if you were using it in the cloud.
Hybrid solution with a mix of ODA of OCI could also fit your needs but you’ll have to manage both technologies, and that’s not so smart. Unless you need this kind of solution for the transition to the cloud…

Conclusion

Is ODA better than OCI? Is OCI better than ODA? Both solutions are smart choices and none will disappoint you if you achieve to leverage the advantages and avoid the constraints of each one. On OCI, you will benefit from immediate availability of the resources, fast provisioning, flexibility, no-brainer license management. With ODA, you will keep your database infrastructure at home, and you will have strong performance and full control over your servers, including for the cost. Choosing between these two solutions is only a matter of strategy, and this does not only concern the DBA.

Cet article Oracle Database Appliance vs Oracle Cloud Infrastructure est apparu en premier sur Blog dbi services.

Upgrade to Oracle 19c – performance issue

Thu, 2020-10-15 08:37

In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked just fine. While most of them finished in less than one hour, we run into one which would have taken days to complete.

Issue

After starting the database upgrade from Oracle 12.2.0.1.0 to Production Version 19.8.0.0.0 the upgrade locked up during compiling:

@utlrp

 

Reason

One select-statement on the unified_audit_trail was running for hours with no result, blocking the upgrade progress and consuming nearly all database resources. The size of the audit_trail itself was about 35MB, so not the size you would expect such a bottleneck from:

SQL> SELECT count(*) from gv$unified_audit_trail;

 

Solution

After some research and testing (see notes below) I found the following workaround (after killing the upgrade process):

SQL> begin
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
end;
/
SQL> set timing on;
SELECT count(*) from gv$unified_audit_trail;
exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

 

Note

As a first attempt I used the procedure below, described in Note 2212196.1.

But flush_unified_audit_trail lasted too long, so I killed the process after it ran for one hour. The flash procedure again worked fine after using clean_audit_trail as described above:

SQL> begin
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
for i in 1..10 loop
DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;
end loop;
end;
/

 

 

A few days later we encountered the same issue on an Oracle 12.1.0.2 database which requires Patch 25985768 for executing dbms_audit_mgmt.transfer_unified_audit_records.

This procedure is available out of the box in the Oracle 12.2 database and in the Oracle 12.1.0.2 databases which have been patched with Patch 25985768.

To avoid to get caught in this trap it is my advise that you gather all relevant statistics before any upgrade from Oracle 12c to 19c and to query gv$unified_audit_trail in advance. This query usually finishes within a few seconds.

 

Related documents

Doc ID 2212196.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=257639407234852&id=2212196.1&_afrWindowMode=0&_adf.ctrl-state=rd4zvw12p_4

Master Note For Database Unified Auditing (Doc ID 2351084.1)

Bug 18920838 : 12C POOR QUERY PERFORMANCE ON DICTIONARY TABLE SYS.X$UNIFIED_AUDIT_TRAIL

Bug 21119008 : POOR QUERY PERFORMANCE ON UNIFIED_AUDIT_TRAIL

Performance Issues While Monitoring the Unified Audit Trail of an Oracle12c Database (Doc ID 2063340.1)

Cet article Upgrade to Oracle 19c – performance issue est apparu en premier sur Blog dbi services.

Pages