Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 7 hours 6 min ago

Oracle Data Guard RedoRoutes : What is Priority 8 ?

Wed, 2020-07-01 09:59

When dealing with cascading or far sync in a Data Guard environment, it is important to understand how to configure the RedoRoutes property.
By default, a primary database sends redo to each transport destination that is configured in the destination. We can create more complex transport topology, depending of our environment, using the RedoRoutes property.
Basically the RedoRoutes property has this format

(redo_routing_rule_1) [(redo_routing_rule_n)]

Where each routing rule contains a redo source field and a redo destination field separated by a colon:

(redo source : redo destination)

One can have more information in Oracle documentation

In this blog I am trying to simply explain how to configure the RedoRoutes property in a Data Guard environment with Far Sync Instance. See my previous blog for far sync instance creation.

I am using Oracle 20c.

The first configuration we consider is the following one

We have
1 primary database: prod20_site1
2 standby databases: prod20_site2 and prod20_site4
1 far sync instance fs_site3

For far sync creation with Oracle 20c see my previous blog

Below the status of the broker configuration

DGMGRL> show configuration

Configuration - prod20

  Protection Mode: MaxAvailability
  Members:
  prod20_site1 - Primary database
    prod20_site2 - Physical standby database
    prod20_site4 - Physical standby database
    fs_site3     - Far sync instance

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 47 seconds ago)

Actually, there is no configured RedoRoutes

DGMGRL> show database prod20_site1 redoroutes;
  RedoRoutes = ''
DGMGRL> show database prod20_site2 redoroutes;
  RedoRoutes = ''
DGMGRL> show database prod20_site4 redoroutes;
  RedoRoutes = ''
DGMGRL> 

For this configuration I want the primary database to send the redo according following rules

prod20_site2 will receive redo directly from prod20_site1
prod20_site1 =====> prod20_site2

prod20_site4 will receive redo via fs_site3 which will forward redo to prod20_site4
prod20_site1 =====> fs_site3 =====> prod20_site4

and if fs_site3 is not available, prod20_site4 will receive directly redo from prod20_site1
prod20_site1 =====> prod20_site4

For this we have to first edit the primary database RedoRoutes property like

DGMGRL> edit database prod20_site1 set property redoroutes='(local:prod20_site2,(fs_site3 priority=1,prod20_site4 priority=2))';
Property "redoroutes" updated

In this rule we have these meanings

local:prod20_site2: if prod20_site1 is the primary database then redo will be sent to prod20_site2

local: (fs_site3 priority=1,prod20_site4 priority=2 ): if prod20_site1 is the primary database then redo will be sent to fs_site3 or to prod20_site4. As the priority of the fs_site3 is higher, indeed smaller priority numbers mean higher priority, redo will be sent first to fs_site3, and if fs_site3 is unavailable, changes will be sent to prod20_site4.
Just note that as fs_site3 has a higher priority, if fs_site3 becomes available, redo will be again sent to fs_site3.

And then we have to tell to fs_site3 to forward redo received from prod20_site1 to prod20_site4.

DGMGRL> edit far_sync fs_site3 set property redoroutes='(prod20_site1:prod20_site4 ASYNC)';
Property "redoroutes" updated

Below the redoroutes we have configured for prod20_site1 and fs_site3

DGMGRL> show database prod20_site1 redoroutes;
  RedoRoutes = '(local:prod20_site2,(fs_site3 priority=1,prod20_site4 priority=2))'
DGMGRL> show database prod20_site2 redoroutes;
  RedoRoutes = ''
DGMGRL> show database prod20_site4 redoroutes;
  RedoRoutes = ''
DGMGRL> show far_sync  fs_site3 redoroutes;
  RedoRoutes = '(prod20_site1:prod20_site4 ASYNC)'
DGMGRL>

And we can verify the status of our configuration

DGMGRL> show configuration verbose


Configuration - prod20

  Protection Mode: MaxPerformance
  Members:
  prod20_site1 - Primary database
    prod20_site2 - Physical standby database
    fs_site3     - Far sync instance
      prod20_site4 - Physical standby database
    prod20_site4 - Physical standby database (alternate of fs_site3)
…
…
Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>

Let’s now consider this configuration where we have two far syn instances. As in the first configuration, we want to send first the redo to far sync instances if possible, otherwise redo will be send directly to standby databases

The RedoRoutes property of the primary can be configured as below

DGMGRL> edit database prod20_site1 set property redoroutes='(local:(fs_site5 priority=1,prod20_site2 priority=2),(fs_site3 priority=1,prod20_site4 priority=2))';
Warning: ORA-16677: Standby database has the same or higher priority than other members specified in the RedoRoutes group.

Property "redoroutes" updated
DGMGRL>

And the redoroutes for the far sysnc fs_site5 can be adjusted like

DGMGRL> edit far_sync fs_site5 set property redoroutes='(prod20_site1:prod20_site2 ASYNC)';
Property "redoroutes" updated
DGMGRL>

We can then verify the satus of the configuration

DGMGRL> show configuration verbose

Configuration - prod20

  Protection Mode: MaxPerformance
  Members:
  prod20_site1 - Primary database
    fs_site5     - Far sync instance
      prod20_site2 - Physical standby database
    prod20_site2 - Physical standby database (alternate of fs_site5)
    fs_site3     - Far sync instance
      prod20_site4 - Physical standby database
    prod20_site4 - Physical standby database (alternate of fs_site3)

…
…

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>

As we can see when configuring RedoRoutes, we sometimes have to deal with the property PRIORITY.
This property can have a value between 1 and 8. 1 as the highest priority and 8 the lowest priority.
Let’s consider two destination A and B in the same group

Case 1: A and B with the same prority

Redo will be sent to A or B, let’s say A. When A is unavailable, redo will be then sent to B. And when A become reachable again, redo will continue to be sent to B.

(local:(A priority=1,B priority=1))

Case 2: A has a higher priority then B
Redo will be sent to A. If A becomes unavailable, redo will be sent to B. And if A becomes again reachable, redo will be sent to A as it has the highest priority

(local:(A priority=1,B priority=2))

But sometimes in the same group, we may want to send redo to both members. For example if we consider the following configuration, we just want that redo will be sent to fs_site3 if possible and if fs_site3 is not reachable then changes will be sent to both prod20_site2 et prod20_site4.

In this case we can use the PRIORITY 8 which has a special meaning. If the primary sends redo to a member with PRIORITY 8, then it must also send these redo to each member with the PRIORITY 8 in the group

In the configuration above, we want following rules

prod20_site1 will send changes to fs_site3 which will forward to prod20_site2 and prod20_site4 and if fs_site3 is not avalaible, prod20_site1 will ship redo to both standby databases.

And when fs_site3 becomes again available, redo will be send again to fs_site3

The redoRoutes for the primary database can be like

DGMGRL> edit database prod20_site1 set property redoroutes='(local:(fs_site3 priority=1,prod20_site2 priority=8,prod20_site4 priority=8))';
Warning: ORA-16677: Standby database has the same or higher priority than other members specified in the RedoRoutes group.

Property "redoroutes" updated
DGMGRL>

And for the far sync instance

DGMGRL> edit far_sync fs_site3 set property redoroutes='(prod20_site1:prod20_site2 ASYNC,prod20_site4 ASYNC)';
Property "redoroutes" updated
DGMGRL>

The status of the configuration

DGMGRL> show configuration verbose

Configuration - prod20

  Protection Mode: MaxPerformance
  Members:
  prod20_site1 - Primary database
    fs_site3     - Far sync instance
      prod20_site2 - Physical standby database
      prod20_site4 - Physical standby database
    prod20_site2 - Physical standby database (alternate of fs_site3)
    prod20_site4 - Physical standby database (alternate of fs_site3)
…
…

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>
Conclusion

Depending to the configuration, the redo transport topology can be very complex. What I can recommend when dealing with far sync instances, is to think about all possible cases, including switchover and failover. And based of all possible cases to design an architecture for the redo transport. In this blog we just consider the case when prod20_site1 is the primary.

Cet article Oracle Data Guard RedoRoutes : What is Priority 8 ? est apparu en premier sur Blog dbi services.

Oracle Autonomous Linux: cron’d ksplice and yum updates

Tue, 2020-06-30 16:14
By Franck Pachot

.
Oracle Enterprise Linux (OEL) is a Linux distribution which is binary compatible with Red Hat Enterprise Linux (RHEL). However, unlike RHEL, OEL is open source, free to download, free to use, free to distribute, free to update and gets free bug fixes. And there are more frequent updates in OEL than in CentOS, the free base of RHEL. You can pay a subscription for additional support and features (like Ksplice or Dtrace) in OEL. It can run the same kernel as RHEL but also provides, still for free, the ‘unbreakable kernel’ (UEK) which is still compatible with RHEL but enhanced with optimizations, recommended especially when running Oracle products.

This is not new and I didn’t resist to illustrate the previous paragraph with the animated gif from the years of this UEK arrival. What is new is that OEL is also the base for the new Autonomous Linux which can run in the Oracle Cloud, automates Ksplice for updating the system online, without restart, and sending notifications about these updates. You can use it in the Oracle Cloud Free Tier.

When creating an Always Free compute instance you select the Oracle Autonomous Linux image. I’ve summarized all steps there:

Autonomous Linux image

Generate an API private key

[opc@al ~]$ mkdir ~/.oci
[opc@al ~]$ openssl genrsa -out ~/.oci/oci_api_key.pem 2048 # no passphrase
[opc@al ~]$ chmod go-rwx ~/.oci/oci_api_key.pem
[opc@al ~]$ openssl rsa -pubout -in ~/.oci/oci_api_key.pem -out ~/.oci/oci_api_key_public.pem
writing RSA key

This generates an API key temporarily.

Configure OCI CLI profile

[opc@al ~]$ sudo al-config -u ocid1.user.oc1..aaaaaaaafo2liscovfefeubflxm2rswrzpnnmosue4lczmgaaodwtqdljj3q -t ocid1.tenancy.oc1..aaaaaaaazlv5wxkdhldyvxkkta7rjn25ocovfefexhidte5zgiyauut2i2gq -k ~/.oci/oci_api_key.pem
Configured OCI CLI profile.
Please delete /home/opc/.oci/oci_api_key.pem

This configures the OCI CLI profile for my user (ocid1.user.oc1.. is my user OCID which I find in Oracle Cloud » Governance and Administration » Identity » Users » Users Detail » OCID copy) in my tenancy (ocid1.tenancy.oc1.. is my tenancy OCI I find in Oracle Cloud » Governance and Administration » Administration » Tenancy Details » OCID copy).

Notifications

When something happens autonomously you want to be notified for it. This uses the OCI notification service with a topic you subscribe to. This is also available in the Free Tier. The topic is created with Oracle Cloud » Application Integration » Notifications » Create Topic, where you just put a name and a description and get the OCID for it (ocid1.onstopic.oc1.eu-frankfurt-1… for me).

While in the console, on this topic I’ve created a subscription where I have put my e-mail address. I’ll receive by e-mail all notifications sent to this topic.

Configure OCI notification service topic OCID

[opc@al ~]$ sudo al-config -T ocid1.onstopic.oc1.eu-frankfurt-1.aaaaaaaaamo7khj3xab6oec5xtcovfefeokqszapwsafeje6g6ltlnhd363a
Configured OCI notification service topic OCID.
Publishing message 'AL: Notification enabled on instance AL'
Published message 'AL: Notification enabled on instance AL'

In the Autonomous Linux instance I’ve setup the OCI notification service topic OCID. And that’s all.

Check your e-mails, you have to acknowledge the reception of notifications of course.

Kernel version

[opc@al ~]$ uname -a
Linux al 4.14.35-1902.301.1.el7uek.x86_64 #2 SMP Tue Mar 31 16:50:32 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux

Here is the kernel version that has been installed


[opc@al ~]$ sudo uptrack-uname -a
Linux al 4.14.35-1902.302.2.el7uek.x86_64 #2 SMP Fri Apr 24 14:24:11 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux

This is the effective kernel updated with Ksplice


[opc@al ~]$ sudo uptrack-show
Installed updates:
[cp1p7rl5] Known exploit detection.
[3kfqruxl] Known exploit detection for CVE-2017-7308.
[6vy9wlov] Known exploit detection for CVE-2018-14634.
[r8wncd28] KPTI enablement for Ksplice.
[3e9je971] Known exploit detection for CVE-2018-18445.
[20bmudk6] Out-of-bounds access when classifying network packets with traffic control index.
[oy5cke5u] NULL dereference while writing Hyper-V SINT14 MSR.
[5jsm8lzj] CVE-2020-9383: Information leak in floppy disk driver.
[5p7yd05e] NULL pointer dereference when initializing Differentiated Services marker driver.
[sajmv0xh] CVE-2018-19854: Information leak in cryptography socket NETLINK_CRYPTO call.
[1gefn4lp] CVE-2019-19965: Denial-of-service in SCSI device removal.
[6hu77eez] Invalid memory access when sending an excessively large packet using Segmentation Offloads.
[f0zxddhg] Livelock in loop device block resize operation.
[2lgm3hz9] CVE-2019-14814, CVE-2019-14815, CVE-2019-14816: Denial-of-service when parsing access point settings in Marvell WiFi-Ex driver.
[3yqxyw42] CVE-2019-20096: Memory leak while changing DCCP socket SP feature values.
[9g5kf79r] Improved fix for CVE-2020-2732: Privilege escalation in Intel KVM nested emulation.
[bq9hiiuj] Race condition in ipoib during high request load causes denial-of-service.
[3youemoz] CVE-2020-11494: Information leak in serial line CAN device communication.
[jpbi3wnm] Use-after-free when removing generic block device.
[if1ety6t] Memory corruption when reading EFI sysfs entries.
[iv8r17d8] CVE-2020-8648: Use-after-free in virtual terminal selection buffer.
[mojwd0zk] Various Spectre-V1 information leaks in KVM.
[nvi6r5wx] CVE-2019-19527: Denial-of-service in USB HID device open.
[o3df6mds] CVE-2020-8647, CVE-2020-8649: Use-after-free in the VGA text console driver.
[kjyqg48a] CVE-2019-19532: Denial-of-service when initializing HID devices.
[74j9dhee] Divide-by-zero when CPU capacity changes causes denial-of-service.
[lgsoxuy7] CVE-2019-19768: Use-after-free when reporting an IO trace.

Effective kernel version is 4.14.35-1902.302.2.el7uek

all details are there about the fixes applied by Ksplice, without any reboot.

One month later

I’ve created that on May 23th, 2020 and writing this one month later.

Here are the e-mails I’ve received from the topic subscription:

And my current machine state:


[opc@al ~]$ uptime
 19:26:39 up 38 days, 13:49,  2 users,  load average: 0.07, 0.02, 0.00
[opc@al ~]$ uname -a
Linux al 4.14.35-1902.301.1.el7uek.x86_64 #2 SMP Tue Mar 31 16:50:32 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux
[opc@al ~]$ sudo uptrack-uname -a
Linux al 4.14.35-1902.303.4.1.el7uek.x86_64 #2 SMP Fri May 29 14:56:41 PDT 2020 x86_64 x86_64 x86_64 GNU/Linux
[opc@al ~]$

The VM has been running 24/7 without outage and the effective kernel is now higher than when installed.

Ksplice updates

This effective kernel has been updated on Tue Jun 16 08:04:33 GMT 2020 as reported by this e-mail I received:


noreply@notification.eu-frankfurt-1.oraclecloud.com
Jun 16, 2020, 10:04 AM
to AutonomousLinux

+------------------------------------------------------------------------+
|  Summary (Tue Jun 16 08:04:33 GMT 2020)                                |
+------------------------------------------------------------------------+
Ksplice updates installed: yes
Yum updates installed: no
Uptime: 08:04:33 up 24 days,  2:27,  0 users,  load average: 0.72, 0.20, 0.06
+------------------------------------------------------------------------+
|  Ksplice upgrade report                                                |
+------------------------------------------------------------------------+
Running 'ksplice -y all upgrade'.
Updating on-disk packages for new processes
Loaded plugins: langpacks
No packages marked for update
Nothing to do.
The following steps will be taken:
Install [i622mubr] Information leak in KVM_HC_CLOCK_PAIRING hypercall.
Install [35xnb9pi] CVE-2019-9500: Potential heap overflow in Broadcom FullMAC WLAN driver.
Install [ppqwl5uh] CVE-2019-15505: Out-of-bounds access in Technisat DVB-S/S2 USB2.0 driver.
Install [ctobm6wo] CVE-2019-19767: Use-after-free in with malformed ext4 filesystems.
Install [l5so0kqe] CVE-2019-19056, CVE-2019-19057: Denial-of-service in the Marvell mwifiex PCIe driver.
Install [b4iszmv7] CVE-2019-20636: Out-of-bounds write via crafted keycode table.
Install [5oec4s3n] Denial-of-service when mounting an ocfs2 filesystem.
Install [rafq9pe9] CVE-2019-9503: Denial-of-service when receiving firmware event frames over a Broadcom WLAN USB dongle.
Install [nlpu7kxi] Denial-of-service when initializing a serial CAN device.
Install [lnz9di5t] CVE-2020-11608: NULL pointer dereference when initializing USB GSPCA based webcams.
Install [2bodr9yk] CVE-2019-19537: Denial-of-service in USB character device registration.
Install [9iw2y1wn] CVE-2019-19524: Use-after-free when unregistering memoryless force-feedback driver.
Install [h5s7eh41] CVE-2020-11609: NULL pointer dereference when initializing STV06XX USB Camera device.
Install [behlqry8] Denial-of-service via invalid TSC values in KVM.
Install [onllaobw] CVE-2019-12819: Use-after-free during initialization of MDIO bus driver.
Install [fdn63bdc] CVE-2019-11599: Information leak in the coredump implementation.
Install [kb3b03z9] CVE-2019-19058: Denial-of-service in iwlwifi firmware interface.
Install [mgfi6p6r] Use-after-free when writing to SLIP serial line.
Install [hs2h9j8w] CVE-2019-14896, CVE-2019-14897: Denial-of-service when parsing BSS in Marvell 8xxx Libertas WLAN driver.
Install [bb9sd52m] CVE-2020-11668: NULL pointer dereference when initializing Xirlink C-It USB camera device.
Install [p4ygwgyj] Information leak in KVM's VMX operation path.
Install [1uxt1xo6] NFSv4 client fails to correctly renew lease when using fsinfo.
Install [hjoeh3zi] CVE-2020-0543: Side-channel information leak using SRBDS.
Installing [i622mubr] Information leak in KVM_HC_CLOCK_PAIRING hypercall.
Installing [35xnb9pi] CVE-2019-9500: Potential heap overflow in Broadcom FullMAC WLAN driver.
Installing [ppqwl5uh] CVE-2019-15505: Out-of-bounds access in Technisat DVB-S/S2 USB2.0 driver.
Installing [ctobm6wo] CVE-2019-19767: Use-after-free in with malformed ext4 filesystems.
Installing [l5so0kqe] CVE-2019-19056, CVE-2019-19057: Denial-of-service in the Marvell mwifiex PCIe driver.
Installing [b4iszmv7] CVE-2019-20636: Out-of-bounds write via crafted keycode table.
Installing [5oec4s3n] Denial-of-service when mounting an ocfs2 filesystem.
Installing [rafq9pe9] CVE-2019-9503: Denial-of-service when receiving firmware event frames over a Broadcom WLAN USB dongle.
Installing [nlpu7kxi] Denial-of-service when initializing a serial CAN device.
Installing [lnz9di5t] CVE-2020-11608: NULL pointer dereference when initializing USB GSPCA based webcams.
Installing [2bodr9yk] CVE-2019-19537: Denial-of-service in USB character device registration.
Installing [9iw2y1wn] CVE-2019-19524: Use-after-free when unregistering memoryless force-feedback driver.
Installing [h5s7eh41] CVE-2020-11609: NULL pointer dereference when initializing STV06XX USB Camera device.
Installing [behlqry8] Denial-of-service via invalid TSC values in KVM.
Installing [onllaobw] CVE-2019-12819: Use-after-free during initialization of MDIO bus driver.
Installing [fdn63bdc] CVE-2019-11599: Information leak in the coredump implementation.
Installing [kb3b03z9] CVE-2019-19058: Denial-of-service in iwlwifi firmware interface.
Installing [mgfi6p6r] Use-after-free when writing to SLIP serial line.
Installing [hs2h9j8w] CVE-2019-14896, CVE-2019-14897: Denial-of-service when parsing BSS in Marvell 8xxx Libertas WLAN driver.
Installing [bb9sd52m] CVE-2020-11668: NULL pointer dereference when initializing Xirlink C-It USB camera device.
Installing [p4ygwgyj] Information leak in KVM's VMX operation path.
Installing [1uxt1xo6] NFSv4 client fails to correctly renew lease when using fsinfo.
Installing [hjoeh3zi] CVE-2020-0543: Side-channel information leak using SRBDS.
Your kernel is fully up to date.
Effective kernel version is 4.14.35-1902.303.4.1.el7uek
+------------------------------------------------------------------------+
|  Yum upgrade report                                                    |
+------------------------------------------------------------------------+
Running 'yum-cron' with update cmd: default.
+------------------------------------------------------------------------+
|  Ksplice updates status                                                |
+------------------------------------------------------------------------+
Running 'ksplice all show'.
Ksplice user-space updates:
No Ksplice user-space updates installed

Ksplice kernel updates:
Installed updates:
[cp1p7rl5] Known exploit detection.
[3kfqruxl] Known exploit detection for CVE-2017-7308.
[6vy9wlov] Known exploit detection for CVE-2018-14634.
[r8wncd28] KPTI enablement for Ksplice.
[3e9je971] Known exploit detection for CVE-2018-18445.
[20bmudk6] Out-of-bounds access when classifying network packets with traffic control index.
[oy5cke5u] NULL dereference while writing Hyper-V SINT14 MSR.
[5jsm8lzj] CVE-2020-9383: Information leak in floppy disk driver.
[5p7yd05e] NULL pointer dereference when initializing Differentiated Services marker driver.
[sajmv0xh] CVE-2018-19854: Information leak in cryptography socket NETLINK_CRYPTO call.
[1gefn4lp] CVE-2019-19965: Denial-of-service in SCSI device removal.
[6hu77eez] Invalid memory access when sending an excessively large packet using Segmentation Offloads.
[f0zxddhg] Livelock in loop device block resize operation.
[2lgm3hz9] CVE-2019-14814, CVE-2019-14815, CVE-2019-14816: Denial-of-service when parsing access point settings in Marvell WiFi-Ex driver.
[3yqxyw42] CVE-2019-20096: Memory leak while changing DCCP socket SP feature values.
[9g5kf79r] Improved fix for CVE-2020-2732: Privilege escalation in Intel KVM nested emulation.
[bq9hiiuj] Race condition in ipoib during high request load causes denial-of-service.
[3youemoz] CVE-2020-11494: Information leak in serial line CAN device communication.
[jpbi3wnm] Use-after-free when removing generic block device.
[if1ety6t] Memory corruption when reading EFI sysfs entries.
[iv8r17d8] CVE-2020-8648: Use-after-free in virtual terminal selection buffer.
[mojwd0zk] Various Spectre-V1 information leaks in KVM.
[nvi6r5wx] CVE-2019-19527: Denial-of-service in USB HID device open.
[o3df6mds] CVE-2020-8647, CVE-2020-8649: Use-after-free in the VGA text console driver.
[kjyqg48a] CVE-2019-19532: Denial-of-service when initializing HID devices.
[74j9dhee] Divide-by-zero when CPU capacity changes causes denial-of-service.
[lgsoxuy7] CVE-2019-19768: Use-after-free when reporting an IO trace.
[i622mubr] Information leak in KVM_HC_CLOCK_PAIRING hypercall.
[35xnb9pi] CVE-2019-9500: Potential heap overflow in Broadcom FullMAC WLAN driver.
[ppqwl5uh] CVE-2019-15505: Out-of-bounds access in Technisat DVB-S/S2 USB2.0 driver.
[ctobm6wo] CVE-2019-19767: Use-after-free in with malformed ext4 filesystems.
[l5so0kqe] CVE-2019-19056, CVE-2019-19057: Denial-of-service in the Marvell mwifiex PCIe driver.
[b4iszmv7] CVE-2019-20636: Out-of-bounds write via crafted keycode table.
[5oec4s3n] Denial-of-service when mounting an ocfs2 filesystem.
[rafq9pe9] CVE-2019-9503: Denial-of-service when receiving firmware event frames over a Broadcom WLAN USB dongle.
[nlpu7kxi] Denial-of-service when initializing a serial CAN device.
[lnz9di5t] CVE-2020-11608: NULL pointer dereference when initializing USB GSPCA based webcams.
[2bodr9yk] CVE-2019-19537: Denial-of-service in USB character device registration.
[9iw2y1wn] CVE-2019-19524: Use-after-free when unregistering memoryless force-feedback driver.
[h5s7eh41] CVE-2020-11609: NULL pointer dereference when initializing STV06XX USB Camera device.
[behlqry8] Denial-of-service via invalid TSC values in KVM.
[onllaobw] CVE-2019-12819: Use-after-free during initialization of MDIO bus driver.
[fdn63bdc] CVE-2019-11599: Information leak in the coredump implementation.
[kb3b03z9] CVE-2019-19058: Denial-of-service in iwlwifi firmware interface.
[mgfi6p6r] Use-after-free when writing to SLIP serial line.
[hs2h9j8w] CVE-2019-14896, CVE-2019-14897: Denial-of-service when parsing BSS in Marvell 8xxx Libertas WLAN driver.
[bb9sd52m] CVE-2020-11668: NULL pointer dereference when initializing Xirlink C-It USB camera device.
[p4ygwgyj] Information leak in KVM's VMX operation path.
[1uxt1xo6] NFSv4 client fails to correctly renew lease when using fsinfo.
[hjoeh3zi] CVE-2020-0543: Side-channel information leak using SRBDS.

Effective kernel version is 4.14.35-1902.303.4.1.el7uek

--
You are receiving notifications as a subscriber to the topic: AL (Topic OCID: ocid1.onstopic.oc1.eu-frankfurt-1.aaaaaaaaamo7khj3xab6oec5xt5c7ia6eokqszapwsafeje6g6ltlnhd363a). To stop receiving notifications from this topic, unsubscribe.

Please do not reply directly to this email. If you have any questions or comments regarding this email, contact your account administrator.
Ksplice updates

I’ve also seen a notification about failed updates:


+------------------------------------------------------------------------+
|  Summary (Mon Jun 29 08:03:19 GMT 2020)                                |
+------------------------------------------------------------------------+
Ksplice updates installed: failed
Yum updates installed: no
Uptime: 08:03:19 up 37 days,  2:25,  0 users,  load average: 0.31, 0.08, 0.03
+------------------------------------------------------------------------+
|  Ksplice upgrade report                                                |
+------------------------------------------------------------------------+
Running 'ksplice -y all upgrade'.
Updating on-disk packages for new processes
Loaded plugins: langpacks
No packages marked for update
Nothing to do.
Unexpected error communicating with the Ksplice Uptrack server. Please
check your network connection and try again. If this error re-occurs,
e-mail ksplice-support_ww@oracle.com.

(Network error: TCP connection reset by peer)

Ok, network error at that time.
However, the next run was ok:


+------------------------------------------------------------------------+
|  Summary (Tue Jun 30 08:03:13 GMT 2020)                                |
+------------------------------------------------------------------------+
Ksplice updates installed: no
Yum updates installed: no
Uptime: 08:03:13 up 38 days,  2:25,  1 user,  load average: 0.00, 0.00, 0.00

and I can confirm by running manually:


[opc@al ~]$ ksplice -y all upgrade
Error: failed to configure the logger
[opc@al ~]$ sudo ksplice -y all upgrade
Updating on-disk packages for new processes
Loaded plugins: langpacks
ol7_x86_64_userspace_ksplice                                                                                                                     | 2.8 kB  00:00:00
No packages marked for update
100% |################################################################################################################################################################|
Nothing to do.
Nothing to be done.
Your kernel is fully up to date.
Effective kernel version is 4.14.35-1902.303.4.1.el7uek

Ksplice is about the kernel and some user space libraries such as glibc and openssl.
But Autonomous Linux also updates the packages.

Yum updates

In addition to kernel patches, the packages are also updated:


The following updates will be applied on al:
================================================================================
 Package                  Arch    Version                  Repository      Size
================================================================================
Installing:
 kernel                   x86_64  3.10.0-1127.13.1.el7     al7             50 M
Updating:
 bpftool                  x86_64  3.10.0-1127.13.1.el7     al7            8.4 M
 ca-certificates          noarch  2020.2.41-70.0.el7_8     al7            382 k
 kernel-tools             x86_64  3.10.0-1127.13.1.el7     al7            8.0 M
 kernel-tools-libs        x86_64  3.10.0-1127.13.1.el7     al7            8.0 M
 libgudev1                x86_64  219-73.0.1.el7_8.8       al7            107 k
 microcode_ctl            x86_64  2:2.1-61.10.0.1.el7_8    al7            2.7 M
 ntpdate                  x86_64  4.2.6p5-29.0.1.el7_8.2   al7             86 k
 python-perf              x86_64  3.10.0-1127.13.1.el7     al7            8.0 M
 python36-oci-cli         noarch  2.12.0-1.el7             al7            4.4 M
 python36-oci-sdk         x86_64  2.17.0-1.el7             al7             10 M
 rsyslog                  x86_64  8.24.0-52.el7_8.2        al7            620 k
 selinux-policy           noarch  3.13.1-266.0.3.el7_8.1   al7            497 k
 selinux-policy-targeted  noarch  3.13.1-266.0.3.el7_8.1   al7            7.2 M
 systemd                  x86_64  219-73.0.1.el7_8.8       al7            5.1 M
 systemd-libs             x86_64  219-73.0.1.el7_8.8       al7            416 k
 systemd-python           x86_64  219-73.0.1.el7_8.8       al7            143 k
 systemd-sysv             x86_64  219-73.0.1.el7_8.8       al7             95 k
Removing:
 kernel                   x86_64  3.10.0-1127.el7          @anaconda/7.8   64 M

Transaction Summary
================================================================================
Install   1 Package
Upgrade  17 Packages
Remove    1 Package
The updates were successfully applied

All packages are maintained up-to-date without human intervention and without downtime.

Package repository

The package repository is limited:


[opc@al ~]$ yum repolist
Loaded plugins: langpacks
ol7_x86_64_userspace_ksplice/primary_db                                                                                                          | 193 kB  00:00:00
repo id                                                       repo name                                                                                           status
!al7/x86_64                                                   Autonomous Linux 7Server (x86_64)                                                                   3,392
ol7_x86_64_userspace_ksplice                                  Ksplice aware userspace packages for Oracle Linux 7Server (x86_64)                                    438
repolist: 3,830
[opc@al ~]$ yum list all | wc -l
1462

1462 packages in one repo.
As a comparison, here is an Oracle Enterprise Linux image:


[opc@ol ~]$ yum repolist
Loaded plugins: langpacks, ulninfo
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
repo id                                                  repo name                                                                                                status
!ol7_UEKR5/x86_64                                        Latest Unbreakable Enterprise Kernel Release 5 for Oracle Linux 7Server (x86_64)                            200
!ol7_addons/x86_64                                       Oracle Linux 7Server Add ons (x86_64)                                                                       421
!ol7_developer/x86_64                                    Oracle Linux 7Server Development Packages (x86_64)                                                        1,319
!ol7_developer_EPEL/x86_64                               Oracle Linux 7Server Development Packages (x86_64)                                                       31,78$
!ol7_ksplice                                             Ksplice for Oracle Linux 7Server (x86_64)                                                                 6,41$
!ol7_latest/x86_64                                       Oracle Linux 7Server Latest (x86_64)                                                                     18,86$
!ol7_oci_included/x86_64                                 Oracle Software for OCI users on Oracle Linux 7Server (x86_64)                                              26$
!ol7_optional_latest/x86_64                              Oracle Linux 7Server Optional Latest (x86_64)                                                            13,91$
!ol7_software_collections/x86_64                         Software Collection Library release 3.0 packages for Oracle Linux 7 (x86_64)                             14,47$
repolist: 87,645
[opc@ol ~]$ yum list all | wc -l
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
36720
[opc@ol ~]$

There is a lot more here. Remember that OEL is compatible with RHEL.

If you need more packages you can open a SR and ask to have it added to the Autonomous Linux repository. For example, I use tmux everyday, especially in a free tier VM (see https://blog.dbi-services.com/always-free-always-up-tmux-in-the-oracle-cloud-with-ksplice-updates/).

If you don’t want to ask for it, there’s the possibility to add public-yum-ol7.repo there:


[opc@al ~]$ sudo yum-config-manager --add-repo http://yum.oracle.com/public-yum-ol7.repo
Loaded plugins: langpacks
adding repo from: http://yum.oracle.com/public-yum-ol7.repo
grabbing file http://yum.oracle.com/public-yum-ol7.repo to /etc/yum.repos.d/public-yum-ol7.repo
repo saved to /etc/yum.repos.d/public-yum-ol7.repo

This added the public Oracle Enterprise Linux repository. Is it correct to do that? It depends what you want: the minimum validated by Oracle to be autonomously updated without any problem, or a little additional customization.

And then install the package you want:


[opc@al ~]$ sudo yum install -y tmux

Loaded plugins: langpacks
Resolving Dependencies
--> Running transaction check
---> Package tmux.x86_64 0:1.8-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
 Package                              Arch                                   Version                                   Repository                                  Size
========================================================================================================================================================================
Installing:
 tmux                                 x86_64                                 1.8-4.el7                                 ol7_latest                                 241 k

Transaction Summary
========================================================================================================================================================================
Install  1 Package

Total download size: 241 k
Installed size: 554 k
Downloading packages:
tmux-1.8-4.el7.x86_64.rpm                                                                                                                        | 241 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : tmux-1.8-4.el7.x86_64                                                                                                                                1/1
  Verifying  : tmux-1.8-4.el7.x86_64                                                                                                                                1/1

Installed:
  tmux.x86_64 0:1.8-4.el7

Now the package is installed and will be updated

Autonomous cron

Those updates are scheduled by cron but you change the schedule through the al-config bash script provided:


[opc@al ~]$ sudo al-config -s
Current daily auto update time window(24-hour): 7-11
Current daily auto update time(24-hour): 08:03

This has set a random time during the 7am to 11 am window, which is here 08:03


[opc@al ~]$ cat /etc/cron.d/al-update
# Daily cron job for AL auto updates.
# Created by al-config, do not modify this file.
# If you want to change update time, use
# 'sudo al-config -w ' to set auto update time window
3 8 * * * root /usr/sbin/al-update >/dev/null

That’s the autonomous thing here: you don’t set the crontab job. You just call the al-config with a time window and it sets the crontab for you in a random time within this window.

Let’s play with this:


[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 01:12
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 01:33
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 00:47
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 00:00
Created cron job file /etc/cron.d/al-update .
[opc@al ~]$ sudo al-config -w 0-2
Configured daily auto update time window(24-hour): 0-2
Configured daily auto update time(24-hour): 00:41
Created cron job file /etc/cron.d/al-update .

You see the idea. Very simple. But simple is awesome, right?

What is this scheduled job doing autonomously every day? You see it in the notification e-mail. Basically it runs:


ksplice -y all upgrade
yum-cron
ksplice all show

and sends the output to your e-mail

This is what keeps your Autonomous Linux up-to-date: ksplice, yum, and the output sent to your e-mail through:


Received: by omta-ad1-fd1-102-eu-frankfurt-1.omtaad1.vcndpfra.oraclevcn.com (Oracle Communications Messaging Server 8.1.0.1.20200619 64bit (built Jun 19 2020)) w

This is an excerpt from the notification e-mail headers. “Oracle Communications Messaging Server” is a heritage from Sun which, according to wikipedia, has its roots in Netscape Messaging Server. All those little bricks from years of enterprise IT are nicely wired together to bring this automation known as Autonomous.

Cet article Oracle Autonomous Linux: cron’d ksplice and yum updates est apparu en premier sur Blog dbi services.

Oracle GoldenGate 19c: Cannot register Integrated EXTRACT due to ORA-44004

Mon, 2020-06-29 04:15

The global_name in an oracle database has a direct impact on the Golden Gate Extract process registration we need to do when we create an Integrated Extract.

In my example below, I use Oracle GoldenGate MicroServices architecture but the same behaviour occurs with Oracle GoldenGate Classic architecture.

 

Let’s start with the creation of the Extract process by clicking on the plus button :

 

Choose Integrated Extract and click on Next button:

Fulfill all mandatory fields:

 

Add the list of tables you want to replicate:

 

Click on Create and Run the process. After a few seconds, the following error message appears :

An integrated extract must to be registered into the database and this is the registration process which fails.

By checking into the report file, we see that Logmining server does not exist on the database. This error is a consequence of the extract registration process failure.

 

The problem is Oracle GoldenGate doesn’t support the character “-” in the GLOBAL_NAME:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DB1.IT.DBI-SERVICES.COM

SQL> sho parameter db_domain

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain     string it.dbi-services.com

The solution is to modify the db_domain and rename the global_name :

SQL> alter system set db_domain='it.dbiservices.com' scope=spfile;

System altered.


SQL> alter database rename global_name to DB1.ITDBISERVICES.COM;

Database altered.

Let’s now try create the Extract Process:

Now it works :

Let’s check the report file by clicking in Action/details button :

 

The new global_name (DB1.ITDBISERVICES.COM) is now active  and Logminig server is enabled into the database.

Cet article Oracle GoldenGate 19c: Cannot register Integrated EXTRACT due to ORA-44004 est apparu en premier sur Blog dbi services.

Postgres Vision 2020 is still live

Fri, 2020-06-26 04:07

This year the Postgres Vision 2020 conference was a Free Virtual Event that took place on June 23-24, 2020 and the Postgres Vision Theme was “Postgres is for builders”


Experts, IT professionals and Postgres community leaders from all around the world were talking about the future of Postgres, real-world stories, Postgres in the cloud, Postgres containerized but also this year there were also sessions focusing on how developers are building scalable and secure applications.

Introduction

In the following URL, Ed Boyajian, EDB CEO has posted a short video introducing Postgres Vision 2020

Now, once logged in,  let’s get in the virtual Expo Hall

To access sessions you have to enter the theater where all sessions are displayed with their abstract and speaker(s).

My first session was “Power to Postgres”and it started on Tuesday: 23/06/2020 at 9:15am (local time)
I was immediately blown away by the huge number of people connected. It was coming from all over the world.
During this 45mn talk, Ed Boyajian, EDB CEO, was sharing his experience and vision for the Postgres movement along with stories of how organizations are leveraging Postgres to accelerate innovation.

His last words in this monologue were rather reassuring regarding the future of Postgres.
“This is the revolution and Postgres will win it, power to Postgres”

Following are only some of the “live” sessions I attended to and I won’t detailed them as it is possible, once registered to watch them again on demand (click on the registration button).

– “A Look at the Elephants Trunk – PostgreSQL 13” – Speaker : Magnus Hagander
– “What’s New in 3.0 and Coming in PostGIS 3.1?” – Speaker: Regina Obe
– “EDB Postgres Advanced Server – What’s New?” – Speaker: Rushabh Lathia
– “Table Partitioning in Postgres: How Far We’ve Come” – Speaker: Amit Langote

Conclusion

In these difficult times, especially because of Covid-19, this event was really a great success by its organization, by the quality of the speakers and their presentations. I think that in the future, this type of virtual event will become more widespread and allow a greater number of participants.
My only regret is that it does not allow to meet the participants, the speakers between sessions and to discuss with them.

Cet article Postgres Vision 2020 is still live est apparu en premier sur Blog dbi services.

Oracle Cloud basics for beginners

Thu, 2020-06-25 09:26
Introduction

Cloud, Cloud, Cloud. Everyone is talking about the Cloud but a lot of people are still in the fog with Cloud technologies. Let’s talk about basic features of the Oracle Cloud, called OCI for Oracle Cloud Infrastructure.

What is really OCI?

OCI is physically a lot of servers in datacenters all around the world. These servers are not very different from the servers you probably have in your own datacenter. Some of these servers are already in use by the customers, and some are immediately available for existing customers or new customers. Most of the customers will not use complete servers but part of them, thanks to the virtualization layer on OCI. A real server can hold several virtual servers, quite a lot actually. Oracle tells us that there is no overprovisionning on OCI: if you create your own server with 2 CPUs and 64GB of RAM, you’re pretty sure that these resources are available for you on the physical server, even if you don’t plan to use them at full throttle. If you need a complete physical server for yourself, it’s also possible, and it’s easy to provision just like a virtual machine.

What do I need to create a server in OCI?

OCI is actually available through a website, the OCI console, but you’ll have to buy Cloud credits to be able to create resources in this Cloud.

Two other options are available:
– ask your Oracle software provider for free trial Cloud credits for testing OCI
– create a free account and use only always-free resources (quite limitating)

When you’re connected to your brand new OCI account onto the console, just create a compute instance. A compute instance is a server for multi-purpose usage. Several options are available at server creation, like the number of CPUs, the amount of RAM, the size of the boot disk, and the OS that will come pre-installed. Provisionning a simple Linux server takes 2 minutes. Deleting a server is also a matter of minutes.

Can I go straight to server creation?

Not really. You cannot simply create a server, because you’ll need to put this server in a compartment, a kind or virtual container for your servers. So first step is to create a compartment. Compartments are fully isolated between them.

Then, you’ll need a private network (called Virtual Cloud Network or VCN) where to put your server. This private network should be created with care because it cannot overlap your on-premise network, especially if you plan to connect them (you surely need to). With network creation, other basic network components need to be also configured.

What are the basic network resources to configure?

First of all, all these resources are virtual resources in OCI. When configuring your network, you’ll also need at least one subnet from your VCN, a firewall (called security list), a router (route table) and a gateway for connecting this server (NAT gateway for outbound internet connexion or internet gateway for both inbound and outbound connexions).

Your OCI network will be linked to your on-premise network with IPSec VPN technology or FastConnect. This last option being a dedicated connexion to your existing infrastructure that does not go through internet.

So before creating your first server, you’ll need to define and configure all these network settings properly.

How to connect to this server?

If you don’t want to configure a VPN or a FastConnect link for now, you can associate your compute instance to an internet gateway to make it available from everywhere. Security is achieved with SSH keys: you provide your public key(s) on the OCI console for this server, and only you will be able to establish a connexion to your server. Later, a VPN or FastConnect configuration will let you reach all your OCI servers as if they were on your network.

What are the other services available?

If you’re thinking about OCI, it’s probably because you do not only need servers: you need Oracle databases. Actually, you don’t have to provision compute instances to install databases on it. You can directly provision databases, for various versions, Standard or Enterprise Edition, with you own license or without any license (the license fee will be billed as if it were an OCI resource – on a monthly basis). For sure, an underlying server will be provisionned, but you don’t have to create it as a separate task. If you need to connect later to this server, it’s possible as if it were a normal compute instance.

A key feature of OCI is what they call autonomous database: it’s a self-managed database that doesn’t give you access to the server or even the SYSDBA role on the database. You control this kind of DB through a dedicated interface (for loading data for example) and let Oracle automatically manage the classic DBA tasks, even those high-level. Autonomous database comes in two flavours: OLTP or Datawarehouse. Embedded autonomous engine will act differently.

Database services also come with automatic backup you can simply configure when creating the database (or after). Just define what kind of backup you need (mainly choose from various retentions and frequencies) and RMAN will automatically take care of your backups. Restore can be done directly through the OCI console.

Other services are also available, like load balancer or MySQL databases. Some services are free, some come at a cost.

How about the storage?

Multiple storage options are available for your servers depending on your needs:
– block storage: this is similar to LUNs on SAN. Choose the size at block storage creation and plug this storage to your server for a dedicated use
– file storage: this is similar to NFS. A shared storage for multiple servers
– object storage: this storage is usefull to make some files available wherever you need, just by sharing a link

Storage on OCI only relies on SSD disks, so expect high performances regarding I/Os.

How much it costs?

That’s the most difficult question, because you’ll have to define your needs, build your infrastructure on paper, then compute the cost with a cost calculator provided by Oracle. There is two billing options available at this moment: prepaid, with Universal Cloud Credits, or pay-as-you-go based on service utilization. The costs may vary depending on multiple parameters. Base budget for an OCI infrastructure starts from 1000$ a month. Don’t expect an OCI infrastructure to be much less expensive than on-premise servers: it’s mainly interesting because you don’t bother with budgeting, buying, deploying, managing servers on your own. And think about how quick you can deploy a new environment, or destroy an old one. It’s another way of spending your IT budget.

The cost calculator is here.

Conclusion

OCI is a mature Cloud, ready for production and with multiple services available and evolving constantly. Test-it to discover how powerfull it is and make sure to understand all the benefits you can get compared to on-premise solutions.

Cet article Oracle Cloud basics for beginners est apparu en premier sur Blog dbi services.

Attaching your own CentOS 7 yum repository to AWS SSM

Thu, 2020-06-25 06:02

From some blogs I’ve written in the past you might already know that we are using AWS SSM to patch and maintain the complete EC2 and onprem instances at one of our customers. The previous posts about that topic are here:

While that in general is working fine and fully automated we ran into an issue lately which forced us to create our own CentOS 7 repositories and use them with SSM to apply the patches to the CentOS machines.

To describe the issue: We have two patch baselines per operating system. One for all development and test systems that applies all patches that are released up until the date the patch baseline is running. Then we have second one for the production systems with an approval delay of 14 days. As we run production patching 2 weeks after we patched the development and test systems that should guarantee that we get the same patches applied to production. And exactly here is the issue: “if a Linux repository doesn’t provide release date information for packages, Systems Manager uses the build time of the package as the auto-approval delay for Amazon Linux, Amazon Linux 2, RHEL, and CentOS. If the system isn’t able to find the build time of the package, Systems Manager treats the auto-approval delay as having a value of zero.”. That basically means: As you never know when CentOS will release their patches, which are based on the RedHat sources, you can never be sure that you get the same patches applied to production as they were applied 14 days before to development and test. Lets do an example: Our patching for development and test happened the 10th of April. The kernel package v3.10.0-1127 was released for CentOS on April 27th and was therefore not applied to the development and test systems. When production patching happened two weeks later that kernel package was available but also satisfied our auto approval rule of 14 days. So we basically had a patch installed on the production which never made it to the development and test systems. This is why we decided to go for our own repositories so we can decide when the repositories are synced.

Setting up a local yum repository is quite easy and you can find plenty of howtos in the internet, so here is just a summary without much explanation. We deployed a new CentOS 7 EC2 instance, then installed a webserver and the epel repository:

[centos@ip-10-47-99-158 ~]$ sudo yum install epel-release nginx -y
[centos@ip-10-47-99-158 ~]$ sudo systemctl start nginx
[centos@ip-10-47-99-158 ~]$ sudo systemctl enable nginx
[centos@ip-10-47-99-158 ~]$ sudo systemctl status nginx

As yum gets the packages over http or https adjust the firewall rules:

[centos@ip-10-47-99-158 ~]$ sudo systemctl start firewalld
[centos@ip-10-47-99-158 ~]$ sudo systemctl enable firewalld
[centos@ip-10-47-99-158 ~]$ sudo firewall-cmd --zone=public --permanent --add-service=http
[centos@ip-10-47-99-158 ~]$ sudo firewall-cmd --zone=public --permanent --add-service=https
[centos@ip-10-47-99-158 ~]$ sudo firewall-cmd --reload

Update the complete system and install the yum utilities and the createrepo packages:

[centos@ip-10-47-99-158 ~]$ sudo yum update -y
[centos@ip-10-47-99-158 ~]$ sudo yum install createrepo  yum-utils -y

Prepare the directory structure and synchronize the repositories:

[centos@ip-10-47-99-158 ~]$ sudo mkdir -p /var/www/html/repos
[centos@ip-10-47-99-158 ~]$ sudo chmod -R 755 /var/www/html/repos
[centos@ip-10-47-99-158 ~]$ sudo reposync -g -l -d -m --repoid=base --newest-only --download-metadata --download_path=/var/www/html/repos/centos-7/7/
[centos@ip-10-47-99-158 ~]$ sudo reposync -l -d -m --repoid=extras --newest-only --download-metadata --download_path=/var/www/html/repos/centos-7/7/
[centos@ip-10-47-99-158 ~]$ sudo reposync -l -d -m --repoid=updates --newest-only --download-metadata --download_path=/var/www/html/repos/centos-7/7/
[centos@ip-10-47-99-158 ~]$ sudo reposync -l -d -m --repoid=epel --newest-only --download-metadata --download_path=/var/www/html/repos/centos-7/7/

Create the repositories from what was synced above:

[centos@ip-10-47-99-158 ~]$ sudo createrepo /var/www/html/repos/centos-7/7/base
[centos@ip-10-47-99-158 ~]$ sudo createrepo /var/www/html/repos/centos-7/7/extras
[centos@ip-10-47-99-158 ~]$ sudo createrepo /var/www/html/repos/centos-7/7/updates
[centos@ip-10-47-99-158 ~]$ sudo createrepo /var/www/html/repos/centos-7/7/epel

… and set the selinux context:

[centos@ip-10-47-99-158 ~]$ sudo semanage fcontext -a -t httpd_sys_content_t "/var/www/html/repos(/.*)?"
[centos@ip-10-47-99-158 ~]$ sudo restorecon -Rv /var/www/html/repos

Configure nginx to point to the repositories:

[centos@ip-10-47-99-158 ~]$ sudo vi /etc/nginx/conf.d/repos.conf 
## add the folling section
server {
        listen   80;
        server_name  10.47.99.158;	
        root   /var/www/html/repos/;
        location / {
                index  index.php index.html index.htm;
                autoindex on;	#enable listing of directory index
        }
}

… and restart the webserver:

[centos@ip-10-47-99-158 ~]$ sudo systemctl restart nginx

From now on you should see the directory structure when you point your browser to the IP of the EC2 instance:

To regularly synchronize the repositories depending on your requirements create a small script that does the job and schedule that with cron, e.g.:

#!/bin/bash
LOCAL_REPOS="base extras updates epel"
##a loop to update repos one at a time
for REPO in ${LOCAL_REPOS}; do
    if [ "$REPO" = "base" ]; then
        reposync -g -l -d -m --repoid=$REPO --newest-only --download-metadata --download_path=/var/www/html/repos/centos-7/7/
    else
        reposync -l -d -m --repoid=extras --newest-only --download-metadata --download_path=/var/www/html/repos/centos-7/7/
    fi
    createrepo /var/www/html/repos/centos-7/7/$REPO
    semanage fcontext -a -t httpd_sys_content_t "/var/www/html/repos(/.*)?"
    restorecon -Rv /var/www/html/repos
done

Test the repository from another CentOS 7 instance:

Using username "centos".
Authenticating with public key "imported-openssh-key"
[centos@ip-10-47-98-80 ~]$ sudo bash
[root@ip-10-47-98-80 centos]$ cd /etc/yum.repos.d/
[root@ip-10-47-98-80 yum.repos.d]$ ls
CentOS-Base.repo  CentOS-CR.repo  CentOS-Debuginfo.repo  CentOS-fasttrack.repo  CentOS-Media.repo  CentOS-Sources.repo  CentOS-Vault.repo
[root@ip-10-47-98-80 yum.repos.d]$ rm -f *
[root@ip-10-47-98-80 yum.repos.d]$ ls -la
total 12
drwxr-xr-x.  2 root root    6 Jun 25 06:39 .
drwxr-xr-x. 77 root root 8192 Jun 25 06:36 ..


[root@ip-10-47-98-80 yum.repos.d]$ cat local-centos.repo
[local]
name=CentOS Base
baseurl=http://10.47.99.158/centos-7/7/base/
gpgcheck=0
enabled=1

[extras]
name=CentOS Extras
baseurl=http://10.47.99.158/centos-7/7/extras/
gpgcheck=0
enabled=1

[updates]
name=CentOS Updates
baseurl=http://10.47.99.158/centos-7/7/updates/
gpgcheck=0

[epel]
name=CentOS Updates
baseurl=http://10.47.99.158/centos-7/7/epel/
gpgcheck=0
[root@ip-10-47-98-80 yum.repos.d]#


[root@ip-10-47-98-80 yum.repos.d]$ yum search wget
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
======================================================================= Name Exactly Matched: wget =======================================================================
wget.x86_64 : A utility for retrieving files using the HTTP or FTP protocols

  Name and summary matches only, use "search all" for everything.
[root@ip-10-47-98-80 yum.repos.d]#

… and you’re done from a repository perspective.

Now it is time to tell SSM to use the local repositories with your patch baseline. If you don’t know how SSM works or how you can apply patches using SSM check the previous post.

All you need to do is to adjust the patch baseline to include your repositories as “Patch sources”:

Schedule your patching and then check the logs. You should see that SSM is now using the local repositories:

...
u'sources': [{u'configuration': u'[local]\nname=CentOS Base\nbaseurl=http://10.47.99.158/centos-7/7/base/\ngpgcheck=0\nenabled=1', u'products': [u'*'], u'name': u'base'}, {u'configuration': u'[extras]\nname=CentOS Extras\nbaseurl=http://10.47.99.158/centos-7/7/extras/\ngpgcheck=0\nenabled=1', u'products': [u'*'], u'name': u'extras'}, {u'configuration': u'[updates]\nname=CentOS Updates\nbaseurl=http://10.47.99.158/centos-7/7/updates/\ngpgcheck=0', u'products': [u'*'], u'name': u'updates'}
...
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-Base.repo
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-CR.repo
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-Debuginfo.repo
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-Media.repo
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-Sources.repo
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-Vault.repo
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-fasttrack.repo
06/25/2020 10:00:28 root [INFO]: Moving file: CentOS-x86_64-kernel.repo
06/25/2020 10:00:28 root [INFO]: Executing lambda _create_custom_repos
06/25/2020 10:00:28 root [INFO]: Creating custom repo base
06/25/2020 10:00:28 root [INFO]: Creating custom repo extras
06/25/2020 10:00:28 root [INFO]: Creating custom repo updates
06/25/2020 10:00:28 root [INFO]: Creating custom repo epel
Loaded plugins: fastestmirror

That’s it. Doing it this way you have full control about which packages will be installed. The downside, of course, is, that you need to maintain your own copy of the repositories.

Cet article Attaching your own CentOS 7 yum repository to AWS SSM est apparu en premier sur Blog dbi services.

Some myths about PostgreSQL vs. Oracle

Wed, 2020-06-24 12:42
By Franck Pachot

.
I originally wrote this as a comment on the following post that you may find on internet:
https://www.2ndquadrant.com/en/blog/oracle-to-postgresql-reasons-to-migrate/
but my comment was not published (many links in it… I suppose it has been flagged as spam?) so I put it there.

You should never take any decision on what you read on the internet without verifying. It is totally valid to consider a move to Open Source databases, but doing it without good understanding is a risk for your migration project success.

In italics are the quotes from the article.

Kirk,
As you do a comparison and link to a list of PostgreSQL features, let me refine the name and description of the Oracle features you compare to, so that people can find them and do a fair comparison. I’m afraid they may not recognize the names and descriptions you provide, at least in current versions. As an example, nobody will get search hits for “Federation”, or “plSQL”, or “HTML DB”… in the Oracle documentation but they will find “Oracle Gateway”, “PL/SQL”, “APEX”…

Federation vs. Foreign Data Wrappers


There is no feature called “Federation”. 
The closest from your description is Database links and Heterogeneous Services through Database Gateway. They go further than FDW in many points. But anyway, I would never use that for ETL. ETL needs optimized bulk loads and there are other features for that (like External Tables to read files, and direct-path inserts to fast load). If your goal is to federate and distribute some small reference tables, then Materialized Views is the feature you may look for.
https://docs.oracle.com/en/database/oracle/oracle-database/20/heter/introduction.html#GUID-EC402025-0CC0-401F-AF93-888B8A3089FE

plSQL vs. everything else


“Oracle has a built-in programming language called plSQL.”
PL/SQL is more than that. It is compiled (to pcode or native), manages dependencies (tracks dependencies on schema objects), optimized for data access (UDF can even be compiled to run within the SQL engine), can be multithreaded (Parallel Execution). That’s different from PL/pgSQL which is interpreted at execution time. You mention languages as “as plug-ins” and for this, there are other ways to run different languages (external procedures, OJCM, External Table preprocessor,…) but when it comes to performance, transaction control, dependency tracking,… that’s PL/SQL.
https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/overview.html#GUID-17166AA4-14DC-48A6-BE92-3FC758DAA940

Application programming


Providing an “API to communicate with the database” is not about open source as the main goal is: encapsulation and hide implementation details. In order to access internal structures, which is what you mention, Oracle provides relational views (known as V$ views) accessible with the most appropriate API for a relational database: SQL
https://docs.oracle.com/en/database/oracle/oracle-database/20/refrn/dynamic-performance-views.html#GUID-8C5690B0-DE10-4460-86DF-80111869CF4C

Internationalization and Localization


The “globalization toolkit” is only one part of the globalization features. You can also use any “any character encoding, collation and code page” but not relying on the OS implementation of it makes it cross-platform compatible and OS upgrade compatible (see https://wiki.postgresql.org/wiki/Locale_data_changes)
https://docs.oracle.com/en/database/oracle/oracle-database/20/nlspg/overview-of-globalization-support.html#GUID-6DD587EE-6686-4802-9C08-124B495978D5

Web Development


“Oracle acknowledges the existence of HTML through HTML DB. PostgreSQL natively supports JSON, XML and plugs in Javascript”. HTML DB can be found in paper books, but the name is “APEX” since 2006. And it is not (only) about HTML, JSON, or XML but is a low-code Rapid Application Development with no equivalent for other databases.
Support for the structures and languages you mention are all there. The latest trend being JSON: https://docs.oracle.com/en/database/oracle/oracle-database/20/adjsn/index.html

Authentication


“Oracle has a built-in authentication system.”
Yes, to be platform-independent, and has many other External Authentication: https://docs.oracle.com/en/database/oracle/oracle-database/20/dbseg/configuring-authentication.html#GUID-BF8E5E84-FE7E-449C-8081-755BAA4CF8DB

Extensibility


“Oracle has a plug-in system”. I don’t know what you are referring to. Oracle is multi-platform proprietary software. Commercial, which means with vendor supported. There are a lot of APIs for extensions, but the vendor must have to control what runs in the engine in order to provide support.

Read Scalability


“PostgreSQL can create a virtually unlimited read cluster”. Oracle has active/active cluster (called RAC) and read replicas (called Active Data Guard). For horizontal scalability, you use the same as for vertical (Parallel Execution) across multiple nodes (in sync, with instance affinity on partitions,…)
https://docs.oracle.com/en/database/oracle/oracle-database/20/vldbg/parallel-exec-intro.html#GUID-F9A83EDB-42AD-4638-9A2E-F66FE09F2B43

Cost


“they don’t mind charging you again for every single instance.” 
No, that’s wrong, license metrics are on processors (CPU) or users (NUP). You run as many instances as you want on your licensed servers for your licensed users: https://www.oracle.com/a/ocom/docs/corporate/oracle-software-licensing-basics.pdf
“jamming everything into a single instance just to reduce costs”
No, database consolidation is recommended to scale the management of multiple databases, but not for licensing costs. If you go there, there are a lot of features to allow isolation and data movement in consolidated databases: Multitenant, Resource Manager, Online Relocate, Lockdown Profiles,…

Performance


“differentiate the tuning parameters for your warehouse to OLTP to reporting to the data lake”: I already mentioned the point about read replicas and about multiple instances in a server. But with oracle, all the parameters I want to set different for OLTP or reporting do not require another instance. They can be set at session or PDB level. As Oracle does not need the filesystem buffer cache, there’s no need to separate on different servers to avoid noisy neighbours. 

I hope this helps to look further at the features. There are many reasons to migrate and the main one is the will to move from a commercial model (with license and support) to an open-source one (start with low cost, help from community). But decision must be made on facts and not rumours.

Franck.

Cet article Some myths about PostgreSQL vs. Oracle est apparu en premier sur Blog dbi services.

Azure Migrate: how to assess on-premises servers for a future Azure migration

Thu, 2020-06-18 02:14

Azure Migrate provides a set of tools to assess and migrate on-premise infrastructure like Hyper-V or VMware virtual machines, physical servers as well as SQL and other Databases, web application…
The Azure Migrate overview page resumes the different key scenario available.


Lets have a look how easily you can assess your Hyper-V virtual machines for a future migration to Azure via this migration tool.
You need to add a tool to discover your VMs but before that you need to create a specific Resource Group for this Azure Migration, give a name to this project and a preferred location for your future Azure Virtual Machines.

Once done you need to select the assessment tool followed by the migration tool. Personally, I choose the Azure ones but others ISV (independent software vendor) offerings are available.
At the end of this process you can review your selection and if you are satisfied with your selection click on “Add tools” button:

The next step is to discover your on-premise servers, here my Virtual machines. To do it you have two possibilities:

  • deploy an Azure migrate appliance, this appliance stays connected to Azure Migrate and will continuously discover your on-premises machines.
  • use a CSV file to import an inventory of the VMs you would like to quickly assess to have a cost and also a compatibility view. If you need an accurate assessment you need to use the appliance, same if you want to migrate the assessed servers.

For my first text I used a CSV file containing 3 Virtual machines. Once imported you can see in the Azure migrate Server Assessment my 3 discovered servers.

If you click on discovered servers, you will have an overview of the server and you can see that they have been imported and not discovered by an appliance:

You can now create a group which is a collection of machines you would like to assess and migrate together. In this example I took two machines from my imported ones:

Now that your group is created you can run an assessment for this group by:

  • selecting Imported machines
  • give a name to your assessment
  • choose the sizing criteria for your assessment:
    • Performance-based: based on performance-data values specified
    • As on-premises: based on on-premises sizing
  • select or create a new group
  • click the “Create assessment” button:

After some minutes the assessment is available, you are able to visualize your assessment with 3 parts:

  • Azure Readiness: tell you if your servers are ready for migration to Azure or if not shows problems & possible remediation
  • Monthly cost estimate: sum based on size recommendations for Azure VMs (compute) and associated VMs storage
  • Storage – Monthly cost estimate: total storage cost split by storage type

You can have more information by clicking on each part of the assessment.
On Azure Readiness you can see the type of Azure Virtual Machine which have been selected during the assessment, the number of disks per on-premises VM and their size:

If you click on a specific on-premise machine you will have a more detailed view:

  • a green light for the migration + the Azure VM size + the estimated monthly coast divided between compute and storage
  • the Operating System used on your VM
  • A compute resumes with the number of cores and the size of the RAM
  • the selected storage with the number of disks, the target disk type & size

As written in the beginning of this blog I cannot migrate my on-premises Virtual Machines after this assessment as I didn’t use the Azure Migrate appliance to discover my on-premises servers. Nevertheless, it’s a good starting point to have a better feeling of the cost if you want to migrate to Azure.
I will definitively deploy an appliance on my next blog post to have an accurate assessment and follow it by a migration.

Cet article Azure Migrate: how to assess on-premises servers for a future Azure migration est apparu en premier sur Blog dbi services.

Documentum – Change/Rename of LDAP Config object

Wed, 2020-06-17 14:04

Using an LDAP Server with Documentum (or any ECM for that matter) is pretty common to avoid managing users locally. In this blog, I wanted to talk about something that isn’t very common and that is the change of an LDAP Server. I’m not talking about just changing the server where your LDAP is hosted but rather changing the full LDAP Server Config inside Documentum (including its name). This is probably something that you will not do very often but I had this use case before so I thought it would be interesting to share.

 

So the use case I had was the following one: during a migration (including an upgrade in the process) from Virtual Machines to Kubernetes pods of dozens of environments, I had to automate the setup & management of the LDAP Server as well as normalize the configuration (name & other parameters) according to certain characteristics. The source and target LDAP Server was the same (a_application_type: netscape) so it was really just a matter of automation and conventions (if the target isn’t the same, it wouldn’t change anything for this blog). As you know if you already came across one of my previous blogs, DA is doing some magic, which prevents you to really manage the LDAP Server in the same way between DA and automation.

 

Therefore, the first part of the use case could have just been to change the LDAP Config object “certdb_location” parameter from the default “ldapcertdb_loc” to another “dm_location” which is using a “file” “path_type” and not a “directory” one. If you understood that last sentence, well done! If you didn’t, don’t worry, I would just suggest you to go and read the blog I linked above. It shows why you would need to use a dedicated dm_location with a path_type set to a file (that is the SSL Certificate of the LDAP Server) in order to automate the setup of an LDAP Config object. Of course, this is only needed if you are using SSL communications with the LDAP Server (i.e. LDAPS). Therefore, that would have been the simple part that is easily understandable, however it is another story if you also need to normalize the LDAP Config object setup in the process. Changing parameters of the LDAP Config object is easy and shouldn’t bring any issues or interruptions, but it’s another story if you need to change the name of the LDAP object… If the field is greyed out in DA, it’s for a reason ;).

 

The dozens of environments were all using the exact same backend LDAP Server but there were still some small differences in the setup like different names of course, different attributes mapped here and there, things like that. To be sure everything would be aligned on the target, I took the simplest road: removing all the source LDAP Config objects and recreating them in our CI/CD (JenkinsX pipelines, Ansible playbooks, aso…). Before removing anything, make sure that you understand what it means for your specific setup, it might not always be that straightforward! Alright, let’s check the status of the LDAP at the beginning:

[dmadmin@stg_cs ~]$ export docbase=REPO1
[dmadmin@stg_cs ~]$
[dmadmin@stg_cs ~]$ iapi ${docbase} -U${USER} -Pxxx << EOC
> ?,c,select r_object_id, object_name from dm_ldap_config;
> ?,c,select ldap_config_id from dm_server_config;
> ?,c,select user_source, count(*), user_login_domain from dm_user group by user_login_domain, user_source;
> EOC


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2016
        All rights reserved.
        Client Library Release 7.3.0040.0025


Connecting to Server using docbase REPO1
[DM_SESSION_I_SESSION_START]info:  "Session 010f123451ad5e0f started for user dmadmin."


Connected to Documentum Server running Release 7.3.0050.0039  Linux64.Oracle
Session id is s0
API> 
r_object_id       object_name
----------------  ----------------
080f1234500edaf3  Source_LDAP_Name
(1 row affected)

API> 
ldap_config_id
----------------
080f1234500edaf3
080f1234500edaf3
080f1234500edaf3
(3 rows affected)

API> 
user_source       count(*)                user_login_domain
----------------  ----------------------  -----------------
LDAP                               55075  Source_LDAP_Name
inline password                      307  
                                    1466  
(3 rows affected)

API> Bye
[dmadmin@stg_cs ~]$

 

As you can see above, there is currently one LDAP Config object that is used by around 55k users. There are three rows for the second query because it’s a HA Repository with 3 Content Servers. The first thing I did was therefore to remove the LDAP Config object and change the different references to prepare for the new name of the object (you can put the two last update commands in just one query, it would be better but I split them here for this example):

[dmadmin@stg_cs ~]$ export old_ldap_name=Source_LDAP_Name
[dmadmin@stg_cs ~]$ export new_ldap_name=Target_LDAP_Name
[dmadmin@stg_cs ~]$
[dmadmin@stg_cs ~]$ iapi ${docbase} -U${USER} -Pxxx << EOC
> ?,c,delete dm_ldap_config objects where object_name='${old_ldap_name}';
> ?,c,update dm_server_config object set ldap_config_id='0000000000000000';
> ?,c,update dm_user object set user_login_domain='${new_ldap_name}' where user_login_domain='${old_ldap_name}' and user_source='LDAP';
> ?,c,update dm_user object set user_global_unique_id='' where user_login_domain='${new_ldap_name}' and user_source='LDAP';
> EOC


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2016
        All rights reserved.
        Client Library Release 7.3.0040.0025


Connecting to Server using docbase REPO1
[DM_SESSION_I_SESSION_START]info:  "Session 010f123451ad5e11 started for user dmadmin."


Connected to Documentum Server running Release 7.3.0050.0039  Linux64.Oracle
Session id is s0
API> 
objects_deleted
---------------
              1
(1 row affected)
[DM_QUERY_I_NUM_UPDATE]info:  "1 objects were affected by your DELETE statement."


API> 
objects_updated
---------------
              3
(1 row affected)
[DM_QUERY_I_NUM_UPDATE]info:  "3 objects were affected by your UPDATE statement."


API> 
objects_updated
---------------
          55075
(1 row affected)
[DM_QUERY_I_NUM_UPDATE]info:  "55075 objects were affected by your UPDATE statement."


API> 
objects_updated
---------------
          55075
(1 row affected)
[DM_QUERY_I_NUM_UPDATE]info:  "55075 objects were affected by your UPDATE statement."


API> Bye
[dmadmin@stg_cs ~]$

 

At the moment, the new LDAP Config object wasn’t created yet but to avoid any disturbance, I already changed the “user_login_domain” from the old name to the new one. Another point to note is the reset of the “user_global_unique_id” parameter to an empty value. As soon as users are synchronized using an LDAP, for example, they will be assigned with a unique identity. For an LDAP account, the id should be something like “###dm_ldap_config.object_name###:###random_numbers###” (E.g.: Source_LDAP_Name:078b1d9f-f4c35cd2-cad867c1-1f4a7872) while for an inline account it should be more “###dm_docbase_config.object_name###:###dm_user.user_name###” (E.g.: REPO1:Patou Morgan). If you just change the LDAP Config object name and update the “user_login_domain” of the user without anything else, in the end, the users won’t be able to login and if you try to execute the LDAP Sync, you should see some warnings showing that your users haven’t been synched (skipped). This is because the user already exist with a different identity. The message on the LDAP Sync would be something like “WARNING: A User with same user_name (Patou Morgan) exists in the docbase with a different identity”. The identity is generated by Documentum and written in the dm_user object under the “user_global_unique_id” attribute. Setting this to an empty value will allow Documentum to generate a new identity so that the user can work with the new LDAP.

 

Verification after the deletion:

[dmadmin@stg_cs ~]$ iapi ${docbase} -U${USER} -Pxxx << EOC
> ?,c,select r_object_id, object_name from dm_ldap_config;
> ?,c,select ldap_config_id from dm_server_config;
> ?,c,select user_source, count(*), user_login_domain from dm_user group by user_login_domain, user_source;
> EOC


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2016
        All rights reserved.
        Client Library Release 7.3.0040.0025


Connecting to Server using docbase REPO1
[DM_SESSION_I_SESSION_START]info:  "Session 010f123451ad5e12 started for user dmadmin."


Connected to Documentum Server running Release 7.3.0050.0039  Linux64.Oracle
Session id is s0
API> 
r_object_id       object_name
----------------  ----------------
(0 rows affected)

API> 
ldap_config_id
----------------
0000000000000000
0000000000000000
0000000000000000
(3 rows affected)

API> 
user_source       count(*)                user_login_domain
----------------  ----------------------  -----------------
LDAP                               55075  Target_LDAP_Name
inline password                      307  
                                    1466  
(3 rows affected)

API> Bye
[dmadmin@stg_cs ~]$

 

Once that was done, I simply re-created a new LDAP Config object as described in the blog I linked at the beginning of this post and using the new name “Target_LDAP_Name” (including the re-encryption of the password). Then running the LDAP Sync to make sure everything is working properly and checking the user_global_unique_id of some LDAP users to make sure it has been regenerated properly. It should be something like “Source_LDAP_Name:078b1d9f-f4c35cd2-cad867c1-1f4a7872” before and then updated to something like “Target_LDAP_Name:2d7c130d-84a47969-926907fa-f1649678” after the change of LDAP Config object name (+ execution of LDAP Sync) – obviously the names and the IDs will change in your case.

 

Cet article Documentum – Change/Rename of LDAP Config object est apparu en premier sur Blog dbi services.

Power BI Report Server – URL reservation Warning – Delegation blocked

Wed, 2020-06-17 01:47
Introduction

Recently I experienced some strange behavior on Power BI Report Server. My customer reported some failure when authenticating on shared data source using Kerberos delegation. On the test environment everything was working as expected and the delegation was working fine. Where as on the development server the delegation was not working at all. We had as well a comparable behavior on the production environment, but only sporadically. The same data source worked fine for one user and suddenly no more for another one, or for the same user one data source connection was accessible but another one was failing with the well known error:

Investigation

It was clear that it was a Kerberos delegation issue at first glance, therefore the first things you look at are the settings of your service account:
– Is “Trust for delegation” set
– If you use constraint delegation, check if you address the right services

After that, we double checked that the SPN’s were still correct. Everything was fine and set as it must be.

The next step was to check the configuration of the Power BI Report Server. So opening the Power BI Report Server Configuration Manager, I went through all the configuration settings here again everything seemed to be correct.

Having some experiences working with Reporting Services and now with Power BI Report Server, I knew that playing and changing with the URL settings of the Web Server and the Web portal might corrupt the rsreportserver.config file, especially if you use https configuration. If you don’t understand what I’m talking about, you will sooner or later, if you have to install and configure this application often.
Bingo, it was the first issue. There was still entries in the xml nodes that were no more valid.

In that case my way to go is to return to the Power BI Report Server Configfuration Manager et reset the URL configurations to a default ones, deleting the ones existing in the web service and the web portal and apply the changes. After that make sure that in rsreportserver.config your tag is limited to the following example. If not, stop first your Power BI Report Server service, make first a backup of your file, than clean it and save it.

 

<URLReservations>
	<Application>
		<Name>ReportServerWebService</Name>
		<VirtualDirectory>ReportServer</VirtualDirectory>
		<URLs>
			<URL>
				<UrlString>http://+:80</UrlString>
				<AccountSid>S-1-5-80-1730998386-2757299892-37364343-1607169425-3512908663</AccountSid>
				<AccountName>NT SERVICE\PowerBIReportServer</AccountName>
			</URL>
		</URLs>
	</Application>
	<Application>
		<Name>ReportServerWebApp</Name>
		<VirtualDirectory>Reports</VirtualDirectory>
		<URLs>
			<URL>
				<UrlString>http://+:80</UrlString>
				<AccountSid>S-1-5-80-1730998386-2757299892-37364343-1607169425-3512908663</AccountSid>
				<AccountName>NT SERVICE\PowerBIReportServer</AccountName>
			</URL>
		</URLs>
	</Application>
</URLReservations>

 

Make sure as well that at the end of the file you do not have a tag . If you have one, delete the whole tag from to the end of and save you file again.

…of course restart your Power BI Report Server service after that

Now that my configuration file was clean, I tried again to set up again my HTTPS configuration for my web service and web portal the configuration manager tool. When you do it, please look carefully at the scrolling messages at the bottom of the configuration manager to see if it the settings apply successfully. For me it was full of green lights and the end message as well.

But, do not miss to read carefully the warnings. Right at the beginning one message was warning that the URL could not be reserved, but the following one said the URL has been removed and the next one that it was reserved successfully. So why to worry!!?? Just because my issue with Kerberos was still not solved.

URL Reservation list

Therefore I decided to follow this strange warning and search information about reserved URL.
I found out the following command to execute to see what were the URL’s reserved on my server and discover all that were reserved by the user NT SERVICE\PowerBIReportServer.

netsh http show urlacl

Then again I reset my URL configurations and clean my rsreportserver.config file once more

Removing URL Reservation

This time I left my Power BI Report Server service stopped and removed manually all the reserved URL linked to the user NT SERVICE\PowerBIReportServer. You should find always 4 URL’s, one for the Web service with the folder you used (in mase case …/ReportServer), one for the Web Portal with the folder you defined (in…/Reports), another folder named /PowerBI and the last one /wopi.

netsh http delete urlacl https://yourserver.yourdomain.com:443/ReportServer/
netsh http delete urlacl https://yourserver.yourdomain.com:443/Reports/
netsh http delete urlacl https://yourserver.yourdomain.com:443/PowerBI/
netsh http delete urlacl https://yourserver.yourdomain.com:443/wopi/

You can remove all the URL leaving on the leave only the one
With +80 and +8083 in the URL.

Then start your configuration manager again et make the URL configuration looking at the scrolling messages at the bottom. Normally no warnings will be displayed anymore.

… and finally

Well, when I was finished, I tested again my data sources connections and by magic all worked perfectly and the delegation was successfully done.
I hope this post will help you and save you time, it took me some hours to figure out that the URL reservation was causing delegation issues.

Cet article Power BI Report Server – URL reservation Warning – Delegation blocked est apparu en premier sur Blog dbi services.

Oracle non-linguistic varchar2 columns to order by without sorting

Tue, 2020-06-16 15:51
By Franck Pachot

.
Sorting data is an expensive operation and many queries declare an ORDER BY. To avoid the sort operation you can build an index as it maintains a sorted structure. This helps with Top-N queries as you don’t have to read all rows but only those from a range of index entries. However, indexes are sorted by binary values. For NUMBER or DATE datatypes, the internal storage ensures that the order is preserved in the binary format. For character strings, the binary format is ASCII, which follows the English alphabet. That’s fine when your session language, NLS_LANGUAGE, defines an NLS_SORT that follows this BINARY order. But as soon as you set a language that has some specific alphabetical order, having an index on a VARCHAR2 or CHAR column does not help to avoid a SORT operation. However, in Oracle 12.2 we can define the sort order at column level with the SQL Standard COLLATE. One use case is for alpha-numeric columns that have nothing to do with any language. Like some natural keys combining letters and numbers. The user expects them to be listed in alphabetical order but, storing only 7-bits ASCII characters, you don’t care about linguistic collation.

I am running this on the Oracle 20c preview in the Oracle Cloud.

VARCHAR2

It can happen that a primary key is not a NUMBER but a CHAR or VARCHAR2, like this:


SQL> create table demo (ID constraint demp_pk primary key) as
  2  select cast(dbms_random.string('U',1)||to_char(rownum,'FM0999') as varchar2(5)) ID
  3  from xmltable('1 to 10');

Table created.

SQL> select * from demo order by ID;

      ID
________
K0003
K0009
L0007
L0010
M0008
O0002
S0001
W0005
Y0006
Z0004

10 rows selected.

I query with ORDER BY because sorting can make sense on a natural key.

Index

I have an index on this column, which is sorted, and then the execution plan is optimized:


SQL> select * from dbms_xplan.display_cursor(format=>'basic');

                      PLAN_TABLE_OUTPUT
_______________________________________
EXPLAINED SQL STATEMENT:
------------------------
select * from demo order by ID

Plan hash value: 1955576728

------------------------------------
| Id  | Operation        | Name    |
------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 |  INDEX FULL SCAN | DEMP_PK |
------------------------------------

13 rows selected.

There’s no SORT operation because the INDEX FULL SCAN follows the index entries in order.

NLS_LANGUAGE

However, there are many countries where we don’t speak English:


SQL> alter session set nls_language='French';

Session altered.

In French, like in many languages, we have accentuated characters and other specificities so that the language-alphabetical order does not always follow the ASCII order.

I’m running exactly the same query:


SQL> select * from demo order by ID;

      ID
________
K0003
K0009
L0007
L0010
M0008
O0002
S0001
W0005
Y0006
Z0004

10 rows selected.

SQL> select * from dbms_xplan.display_cursor(format=>'basic');

                      PLAN_TABLE_OUTPUT
_______________________________________
EXPLAINED SQL STATEMENT:
------------------------
select * from demo order by ID

Plan hash value: 2698718808

------------------------------------
| Id  | Operation        | Name    |
------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 |  SORT ORDER BY   |         |
|   2 |   INDEX FULL SCAN| DEMP_PK |
------------------------------------

14 rows selected.

This time, there’s a SORT operation. even if I’m still reading with INDEX FULL SCAN.

NLS_SORT

The reason is that, by setting the ‘French’ language, I’ve also set the French sort collating sequence.


SQL> select * from nls_session_parameters;
                 PARAMETER                           VALUE
__________________________ _______________________________
NLS_LANGUAGE               FRENCH
NLS_SORT                   FRENCH

And this is different from the BINARY one that I had when my language was ‘American’.

Actually, only a few languages follow the BINARY order of the ASCII table:


SQL>
  declare
   val varchar2(64);
  begin
    for i in (select VALUE from V$NLS_VALID_VALUES where PARAMETER='LANGUAGE') loop
    execute immediate 'alter session set nls_language='''||i.value||'''';
    select value into val from NLS_SESSION_PARAMETERS where PARAMETER='NLS_SORT';
    if val='BINARY' then dbms_output.put(i.value||' '); end if;
    end loop;
    dbms_output.put_line('');
  end;
/

AMERICAN JAPANESE KOREAN SIMPLIFIED CHINESE TRADITIONAL CHINESE ENGLISH HINDI TAMIL KANNADA TELUGU ORIYA MALAYALAM ASSAMESE GUJARATI MARATHI PUNJABI BANGLA MACEDONIAN LATIN SERBIAN IRISH

PL/SQL procedure successfully completed.

This is ok for real text but not for my primary key where ASCII order is ok. I can set the NLS_SORT=BINARY for my session, but that’s too wide as my problem is only with a column.

Or I can create an index for the French collation. Actually, this is what is used internally:


SQL> explain plan for select * from demo order by ID;
Explained.

SQL> select * from dbms_xplan.display(format=>'basic +projection');
                                                      PLAN_TABLE_OUTPUT
_______________________________________________________________________
Plan hash value: 2698718808

------------------------------------
| Id  | Operation        | Name    |
------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 |  SORT ORDER BY   |         |
|   2 |   INDEX FULL SCAN| DEMP_PK |
------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) NLSSORT("DEMO"."ID",'nls_sort=''GENERIC_M''')[50],
       "DEMO"."ID"[VARCHAR2,5]
   2 - "DEMO"."ID"[VARCHAR2,5]

GENERIC_M is the sort collation for many European languages.

But that again, does not fit the scope of my problem as I don’t want to create an index for any possible NLS_SORT setting.

COLLATE

The good solution is to define the collation for my table column: this ID is a character string, but it is an ASCII character string which has nothing to do with my language. In 18c I can do that:


SQL> alter table demo modify ID collate binary;

Table altered.

The COLLATE is a SQL Standard syntax that exists in other databases, and it came to Oracle in 12cR2.

And that’s all:


SQL> explain plan for select * from demo order by ID;

Explained.

SQL> select * from dbms_xplan.display(format=>'basic +projection');

                                             PLAN_TABLE_OUTPUT
______________________________________________________________
Plan hash value: 1955576728

------------------------------------
| Id  | Operation        | Name    |
------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 |  INDEX FULL SCAN | DEMP_PK |
------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "DEMO"."ID"[VARCHAR2,5]

No SORT operation needed, whatever the language I set for my session.

Here is the DDL for my table:


SQL> ddl demo

  CREATE TABLE "SYS"."DEMO"
   (    "ID" VARCHAR2(5) COLLATE "BINARY",
         CONSTRAINT "DEMP_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" ;

My column explicitly follows the BINARY collation.

Extended Data Types

Now, all seems easy, but there’s a prerequisite:


SQL> show parameter max_string_size

NAME            TYPE   VALUE
--------------- ------ --------
max_string_size string EXTENDED

I have set my PDB to EXTENDED string size.

If I try the same in a PDB with the ‘old’ limit of 4000 bytes:


SQL> alter session set container=PDB1;

Session altered.

SQL> show parameter max_string_size

NAME            TYPE   VALUE
--------------- ------ --------
max_string_size string STANDARD

SQL> drop table demo;

Table dropped.

SQL> create table demo (ID varchar2(5) collate binary constraint demp_pk primary key);

create table demo (ID varchar2(5) collate binary constraint demp_pk primary key)
 *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

This new feature is allowed only with the Extended Data Types introduced in 12c release 2.

ORDER BY COLLATE

Ok, let’s create the table with the default collation:


SQL> create table demo (ID constraint demp_pk primary key) as
  2  select cast(dbms_random.string('U',1)||to_char(rownum,'FM0999') as varchar2(5)) ID
  3  from xmltable('1 to 10');

Table created.

SQL> select * from dbms_xplan.display_cursor(format=>'basic +projection');

                                                   PLAN_TABLE_OUTPUT
____________________________________________________________________
EXPLAINED SQL STATEMENT:
------------------------
select * from demo order by ID

Plan hash value: 2698718808

------------------------------------
| Id  | Operation        | Name    |
------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 |  SORT ORDER BY   |         |
|   2 |   INDEX FULL SCAN| DEMP_PK |
------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) NLSSORT("DEMO"."ID",'nls_sort=''FRENCH''')[50],
       "DEMO"."ID"[VARCHAR2,5]
   2 - "DEMO"."ID"[VARCHAR2,5]

As my NLS_SORT is ‘French’ there is a SORT operation.

But I can explicitly request a BINARY sort for this:


SQL> select * from demo order by ID collate binary;

      ID
________
D0003
H0002
L0009
N0008
P0010
Q0005
R0004
W0007
Y0001
Z0006

10 rows selected.

SQL> select * from dbms_xplan.display_cursor(format=>'basic +projection');

                                             PLAN_TABLE_OUTPUT
______________________________________________________________
EXPLAINED SQL STATEMENT:
------------------------
select * from demo order by ID collate binary

Plan hash value: 2698718808

------------------------------------
| Id  | Operation        | Name    |
------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 |  SORT ORDER BY   |         |
|   2 |   INDEX FULL SCAN| DEMP_PK |
------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "DEMO"."ID" COLLATE "BINARY"[5],
       "DEMO"."ID"[VARCHAR2,5]
   2 - "DEMO"."ID"[VARCHAR2,5]

I have no idea why there is still a sort operation. I think that the INDEX FULL SCAN returns already the rows in binary order. And that should require additional sorting for the ORDER BY … COLLATE BINARY.

Cet article Oracle non-linguistic varchar2 columns to order by without sorting est apparu en premier sur Blog dbi services.

Publishing a PowerShell script to AWS Lambda

Tue, 2020-06-16 08:20

I’ve done some Lambda functions with Python in the past and it was quite easy to publish that to Lambda (by just uploading a zip file with all my code and dependencies). You might ask yourself why I want to do that with PowerShell but the reason is quite simple: There was a requirement at a customer to automatically collect all the KBs that are installed in the AWS Windows WorkSpaces for compliance reasons. Doing that for EC2 or on-prem instances is quite easy using Lambda for Python against SSM when you are using SSM for patching, but if you want to list the installed KBs of your deployed AWS WorkSpaces you need a different way of doing that. After discussing that with AWS Support it turned out that the easiest solution for this is to use the PowerShell Get-HotFix module remotely against the AWS WorkSpaces. Easy, I thought, when I can deploy Python code in Lambda I can easily do this for PowerShell as well. But this is definitely not true as the process is quite different. So, here we go …

The first bit you need to prepare is a PowerShell development environment for AWS. As I am running Linux (KDE Neon, if you want to know exactly), and PowerShell is available on Linux since quite some time, I’ll be showing how to do that on Linux (the process is more or less the same for Windows though).
Obviously PowerShell needs to be installed and this is documented by Microsoft quite well, no need to further explain this. Basically it is matter of:

$ wget -q https://packages.microsoft.com/config/ubuntu/18.04/packages-microsoft-prod.deb
$ sudo dpkg -i packages-microsoft-prod.deb
$ sudo apt-get update
$ sudo add-apt-repository universe
$ sudo apt-get install -y powershell

… and that’s it (take care to follow the steps for your Linux distribution). Once that is done PowerShell can be started:

$ pwsh
PowerShell 7.0.2
Copyright (c) Microsoft Corporation. All rights reserved.

https://aka.ms/powershell
Type 'help' to get help.

PS /home/dwe> 

The first additional module you’ll need is AWSLambdaPSCore:

PS /home/dwe> Install-Module AWSLambdaPSCore -Scope CurrentUser

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its InstallationPolicy value by running the 
Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): Y

Usually you want to work with other AWS Services in your Lambda code so it is recommended to install the AWS.Tools.Installer module as it provides a convenient way for installing the various tools required for working with the various AWS services. In addition the AWSPowerShell.NetCore module is required:

PS /home/dwe> Install-Module -Name AWS.Tools.Installer -Force    
PS /home/dwe> Install-Module -name AWSPowerShell.NetCore -Force 

Now, dependend on which AWS services you want to work with, just install what you need (in this example EC2, S3 and WorkSpaces):

PS /home/dwe> Install-AWSToolsModule AWS.Tools.EC2,AWS.Tools.S3 -CleanUp -Force                                                                             
Installing module AWS.Tools.EC2 version 4.0.6.0                                                                                                             
Installing module AWS.Tools.S3 version 4.0.6.0                                                                                                              
PS /home/dwe> Install-AWSToolsModule AWS.Tools.Workspaces -CleanUp -Force                                                                                   
Installing module AWS.Tools.WorkSpaces version 4.0.6.0  

Once you have that ready you can use the AWS tools for PowerShell to generate a template you can start with:

PS /home/dwe> Get-AWSPowerShellLambdaTemplate                                                                                                                                                                                                                                                                           Template                     Description                                                                                                                    
--------                     -----------                                                                                                                    
Basic                        Bare bones script                                                                                                              
CloudFormationCustomResource PowerShell handler base for use with CloudFormation custom resource events
CodeCommitTrigger            Script to process AWS CodeCommit Triggers
DetectLabels                 Use Amazon Rekognition service to tag image files in S3 with detected labels.
KinesisStreamProcessor       Script to be process a Kinesis Stream
S3Event                      Script to process S3 events
S3EventToSNS                 Script to process SNS Records triggered by S3 events
S3EventToSNSToSQS            Script to process SQS Messages, subscribed to an SNS Topic that is triggered by S3 events
S3EventToSQS                 Script to process SQS Messages triggered by S3 events
SNSSubscription              Script to be subscribed to an SNS Topic
SNSToSQS                     Script to be subscribed to an SQS Queue, that is subscribed to an SNS Topic
SQSQueueProcessor            Script to be subscribed to an SQS Queue


PS /home/dwe> cd ./Documents/aws
PS /home/dwe/Documents/aws> New-AWSPowerShellLambda -ScriptName MyFirstPowershellLambda -Template Basic
Configuring script to use installed version 4.0.6.0 of (@{ ModuleName = 'AWS.Tools.Common'; ModuleVersion = '4.0.5.0' }.Name)
Created new AWS Lambda PowerShell script MyFirstPowershellLambda.ps1 from template Basic at /home/dwe/Documents/aws/MyFirstPowershellLambda

PS /home/dwe/Documents/aws/MyFirstPowershellLambda> ls
MyFirstPowershellLambda.ps1  readme.txt

The generated template is quite simple but it gives you an idea how to start:

PS /home/dwe/Documents/aws/MyFirstPowershellLambda> cat ./MyFirstPowershellLambda.ps1
# PowerShell script file to be executed as a AWS Lambda function. 
# 
# When executing in Lambda the following variables will be predefined.
#   $LambdaInput - A PSObject that contains the Lambda function input data.
#   $LambdaContext - An Amazon.Lambda.Core.ILambdaContext object that contains information about the currently running Lambda environment.
#
# The last item in the PowerShell pipeline will be returned as the result of the Lambda function.
#
# To include PowerShell modules with your Lambda function, like the AWS.Tools.S3 module, add a "#Requires" statement
# indicating the module and version. If using an AWS.Tools.* module the AWS.Tools.Common module is also required.

#Requires -Modules @{ModuleName='AWS.Tools.Common';ModuleVersion='4.0.6.0'}

# Uncomment to send the input event to CloudWatch Logs
# Write-Host (ConvertTo-Json -InputObject $LambdaInput -Compress -Depth 5)

Just add the modules for the specific AWS services you want to work with in the “#Requires” section (you need to install them before of course) and write your script:

PS /home/dwe/Documents/aws/MyFirstPowershellLambda> cat ./MyFirstPowershellLambda.ps1
# PowerShell script file to be executed as a AWS Lambda function. 
# 
# When executing in Lambda the following variables will be predefined.
#   $LambdaInput - A PSObject that contains the Lambda function input data.
#   $LambdaContext - An Amazon.Lambda.Core.ILambdaContext object that contains information about the currently running Lambda environment.
#
# The last item in the PowerShell pipeline will be returned as the result of the Lambda function.
#
# To include PowerShell modules with your Lambda function, like the AWS.Tools.S3 module, add a "#Requires" statement
# indicating the module and version. If using an AWS.Tools.* module the AWS.Tools.Common module is also required.

#Requires -Modules @{ModuleName='AWS.Tools.Common';ModuleVersion='4.0.6.0'}
#Requires -Modules @{ModuleName='AWS.Tools.S3';ModuleVersion='4.0.6.0'}
#Requires -Modules @{ModuleName='AWS.Tools.EC2';ModuleVersion='4.0.6.0'}

# Uncomment to send the input event to CloudWatch Logs
# Write-Host (ConvertTo-Json -InputObject $LambdaInput -Compress -Depth 5)
Write-Output "Test"

The AWS documentation for the PowerShell Cmdlets is here.

Assuming that the script is completed (the above script does a simple print to the console) you need to deploy it to Lambda. For Python all you need to do is to zip your code and upload that to AWS Lambda. For PowerShell you need to call the “Publish-AWSPowerShellLambda” module passing in the script, a name for the Lambda function and the AWS region you want to have the function deployed to:

PS /home/dwe/Documents/aws/MyFirstPowershellLambda> Publish-AWSPowerShellLambda -ScriptPath ./MyFirstPowershellLambda.ps1 -Name MyFirstPowershellLambda  -Region eu-central-1

… and this will fail with:

Get-Command: /home/dwe/.local/share/powershell/Modules/AWSLambdaPSCore/2.0.0.0/Private/_DeploymentFunctions.ps1:544
Line |
 544 |      $application = Get-Command -Name dotnet
     |                     ~~~~~~~~~~~~~~~~~~~~~~~~
     | The term 'dotnet' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name,
     | or if a path was included, verify that the path is correct and try again.

Exception: /home/dwe/.local/share/powershell/Modules/AWSLambdaPSCore/2.0.0.0/Private/_DeploymentFunctions.ps1:547
Line |
 547 |          throw '.NET Core 3.1 SDK was not found which is required to b …
     |          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | .NET Core 3.1 SDK was not found which is required to build the PowerShell Lambda package bundle. Download the .NET Core 3.1 SDK from
     | https://www.microsoft.com/net/download

The error message is quite clear: You need to install the “.NET Core 3.1 SDK” but as we added the Microsoft repositories above this is just a matter of (again, adjust for your package manager):

$ sudo apt-get install -y dotnet-sdk-3.1

Trying the same again and this time it succeeds:

PS /home/dwe/Documents/aws/MyFirstPowershellLambda> Publish-AWSPowerShellLambda -ScriptPath ./MyFirstPowershellLambda.ps1 -Name MyFirstPowershellLambda  -Region eu-central-1
Staging deployment at /tmp/MyFirstPowershellLambda
Configuring PowerShell to version 7.0.0
Generating C# project /tmp/MyFirstPowershellLambda/MyFirstPowershellLambda.csproj used to create Lambda function bundle.
Generating /tmp/MyFirstPowershellLambda/Bootstrap.cs to load PowerShell script and required modules in Lambda environment.
Generating aws-lambda-tools-defaults.json config file with default values used when publishing project.
Copying PowerShell script to staging directory
...
... zipping:   adding: Namotion.Reflection.dll (deflated 58%)
... zipping:   adding: System.Diagnostics.PerformanceCounter.dll (deflated 60%)
... zipping:   adding: MyFirstPowershellLambda.ps1 (deflated 53%)
... zipping:   adding: System.Management.dll (deflated 62%)
... zipping:   adding: Markdig.Signed.dll (deflated 62%)
... zipping:   adding: libpsl-native.so (deflated 69%)
...
Creating new Lambda function MyFirstPowershellLambda
Enter name of the new IAM Role:
dwe-tmp-role
...
Select IAM Policy to attach to the new role and grant permissions
    1) AWSLambdaFullAccess (Provides full access to Lambda, S3, DynamoDB, CloudWatch Metrics and  ...)
    2) AWSLambdaReplicator
...
1
Waiting for new IAM Role to propagate to AWS regions
...............  Done
New Lambda function created

Heading over to the AWS console we can see that the function is there:

Hope this helps…

Cet article Publishing a PowerShell script to AWS Lambda est apparu en premier sur Blog dbi services.

No{Join,GroupBy}SQL – Analytic Views for BI

Sun, 2020-06-14 13:36
By Franck Pachot

.
Advocates of NoSQL can query their structures without having to read a data model first. And without writing long table join clauses. They store and query a hierarchical structure without the need to follow relationships, and without the need to join tables on a foreign key name, in order to get a caption or description from a lookup table. The structure, like an XML or JSON document, provides metadata to understand the structure and map it to business objects. The API is simple ‘put’ and ‘get’ where you can retrieve a whole hierarchy, with aggregates at all levels, ready to drill-down from summary to details. Without the need to write sum() functions and group by clauses. For analytics, SQL has improved a lot with window functions and grouping sets but, despite being powerful, this makes the API more complex. And, at a time were the acceptable learning curve should reach its highest point after 42 seconds (like watching the first bits of a video or getting to the stackoverflow top-voted answer), this complexity cannot be adopted easily.

Is SQL too complex? If it does, then something is wrong. SQL was invented for end-users: to query data like in plain English, without the need to know the internal implementation and the procedural algorithms that can make sense out of it. If developers are moving to NoSQL because of the complexity of SQL, then SQL missed something from its initial goal. If they go to NoSQL because “joins are expensive” it just means that joins should not be exposed to them. Because optimizing access paths and expensive operations is the job of the database optimizer, with the help of the database designer, but not the front-end developer. However, this complexity is unfortunately there. Today, without a good understanding of the data model (entities, relationships, cardinalities) writing SQL queries is difficult. Joining over many-to-many relationships, or missing a group by clause, can give wrong results. When I see a select with a DISTINCT keyword, I immediately think that there’s an error in the query and the developer, not being certain of the aggregation level he is working on, has masked it with a DISTINCT because understanding the data model was too time-consuming.

In data warehouses, where the database is queried by the end-user, we try to avoid this risk by building simple star schemas with only one fact tables and many-to-one relationships to dimensions. And on top of that, we provide a reporting tool that will generate the queries correctly so that the end-user does not need to define the joins and aggregations. This requires a layer of metadata on top of the database to describe the possible joins, aggregation levels, functions to aggregate measures,… When I was a junior on databases I’ve been fascinated by those tools. On my first Data Warehouse, I’ve built a BusinessObjects (v3) universe. It was so simple: define the “business objects”, which are the attributes mapped to the dimension columns. Define the fact measures, with the aggregation functions that can apply. And for the joins, it was like the aliases in the from clause, a dimension having multiple roles: think about an airport that can be the destination or the origin of a flight. And then we defined multiple objects: all the airport attributes in the destination role, and all the airport attributes as an origin, were different objects for the end-user. Like “origin airport latitude”, rather than “airport latitude” that makes sense only after a join on “origin airport ID”. That simplifies a lot the end-user view on our data: tables are still stored as relational tables to be joined at query time, in order to avoid redundancy, but the view on top of that shows the multiple hierarchies, like in a NoSQL structure, for the ease of simple queries.

But, as I mentioned, this is the main reason for SQL and this should be done with SQL. All these descriptions I did in the BusinessObjects universe should belong to the database dictionary. And that’s finally possible with Analytic Views. Here is an example on the tables I’ve created in a previous post. I am running on the 20c cloud preview, but this can run on 18c or 19c. After importing the .csv of covid-19 cases per day and countries, I’ve built one fact and one snowflake-dimension tables:


create table continents as select rownum continent_id, continentexp continent_name from (select distinct continentexp from covid where continentexp!='Other');
create table countries as select country_id,country_code,country_name,continent_id,popdata2018 from (select distinct geoid country_id,countryterritorycode country_code,countriesandterritories country_name,continentexp continent_name,popdata2018 from covid where continentexp!='Other') left join continents using(continent_name);
create table cases as select daterep, geoid country_id,cases from covid where continentexp!='Other';
alter table continents add primary key (continent_id);
alter table countries add foreign key (continent_id) references continents;
alter table countries add primary key (country_id);
alter table cases add foreign key (country_id) references countries;
alter table cases add primary key (country_id,daterep);

The dimension hierarchy is on country/continent. I should have created one for time (day/month/quarter/year) but the goal is to keep it simple to show the concept.

When looking at the syntax, it may seem complex. But, please, understand that the goal is to put more in the static definition so that runime usage is easier.

Attribute Dimension

I’ll describe the Country/Continent dimension. It can be in one table (Star Schema) or multiple (Snowflake Schema). I opted for snowflake to show how it is supported since 18c. In 12c we have to create a view on it as the using clause can be only a table or view identifier.


create or replace attribute dimension COUNTRIES_DIM_ATT
using COUNTRIES a ,CONTINENTS b join path country_continent on a.CONTINENT_ID=b.CONTINENT_ID
attributes ( a.COUNTRY_ID "Country ID", a.COUNTRY_CODE "Country", a.COUNTRY_NAME "Country name", a.CONTINENT_ID "Continent ID", b.CONTINENT_NAME "Continent")
level "Continent"
  key "Continent ID"
  member name         '#'||to_char("Continent ID")
  member caption      upper(substr("Continent",1,3))
  member description  "Continent"
  determines ("Continent")
level "Country"
  key "Country ID"
  member name         "Country ID"
  member caption      "Country"
  member description  "Country name"
  determines ("Country ID","Country", "Country name", "Continent ID", "Continent")
 all member name 'WORLD'
/

Let’s take it simply, I have an internal name for my dimension COUNTRIES_DIM_ATT and a USING clause which declares the dimension table and an optional join for snowflake schemas with JOIN PATH. Then I’ve declared the attributes which are the projection of those columns. For this example, I decided to use quoted identifiers for the one that I add in this layer, to distinguish them from the table columns. But do as you want.

The most important here is about levels and dependency. In a star schema, we denormalize the fact tables for simplification (and because it is not a problem as there are no updates, and size is not as large as the fact tables). The metadata we declare here describes the relationships. I have two levels: country and continent. And a many-to-one relationship from country to continent. This is what I declare with the LEVEL and DETERMINES keyword: from all the attributes declared, which ones are functional dependencies of others.

The second important description here is standard naming. In the analytic view, I can query the attributes as columns from the USING clause. But for the ease of querying by simple tools, they will also have standard columns names. Each attribute has as MEMBER NAME (I used the 2-letter country code here which is the COUNTRY_ID primary key in my COUNTRIES dimension table. They have a MEMBER CAPTION as a short name and a MEMBER DESCRIPTION for a longer one. Those are standardized names for each object. The idea is to provide a view that can be used without reading the data model: for each level, the end-user can query the name, caption or the description.

The idea is that those hierarchy levels will be selected in the WHERE clause by a LEVEL_NAME instead of mentioning all columns in GROUP BY clause or PARTITION BY analytic function windowing clause. Note that the’s also an ALL level for the top-most aggregation and we can keep the ‘ALL’ name or a specific one like the ‘WORLD’ I’ve defined here for all countries.

This is the most important metadata is defined by the dimension but we don’t query on dimensions. We can only look at the definitions in the dictionary:


SQL> select * FROM user_attribute_dimensions;

      DIMENSION_NAME    DIMENSION_TYPE    CACHE_STAR    MAT_TABLE_OWNER    MAT_TABLE_NAME    ALL_MEMBER_NAME    ALL_MEMBER_CAPTION    ALL_MEMBER_DESCRIPTION    COMPILE_STATE    ORIGIN_CON_ID
____________________ _________________ _____________ __________________ _________________ __________________ _____________________ _________________________ ________________ ________________
COUNTRIES_DIM_ATT    STANDARD          NONE                                               'WORLD'                                                            VALID                           3
CALENDAR_DIM_ATT     STANDARD          NONE                                               'ALL'                                                              VALID                           3
DAYS_DIM_ATT         TIME              NONE                                               'ALL'                                                              VALID                           3

SQL> select * FROM user_attribute_dim_attrs;

      DIMENSION_NAME    ATTRIBUTE_NAME    TABLE_ALIAS       COLUMN_NAME    ORDER_NUM    ORIGIN_CON_ID
____________________ _________________ ______________ _________________ ____________ ________________
DAYS_DIM_ATT         Date              CASES          DATEREP                      0                3
COUNTRIES_DIM_ATT    Country ID        A              COUNTRY_ID                   0                3
COUNTRIES_DIM_ATT    Country           A              COUNTRY_CODE                 1                3
COUNTRIES_DIM_ATT    Country name      A              COUNTRY_NAME                 2                3
COUNTRIES_DIM_ATT    Continent ID      A              CONTINENT_ID                 3                3
COUNTRIES_DIM_ATT    Continent         B              CONTINENT_NAME               4                3
CALENDAR_DIM_ATT     Date              CASES          DATEREP                      0                3

SQL> select * FROM user_attribute_dim_levels;

      DIMENSION_NAME    LEVEL_NAME    SKIP_WHEN_NULL    LEVEL_TYPE                MEMBER_NAME_EXPR               MEMBER_CAPTION_EXPR    MEMBER_DESCRIPTION_EXPR    ORDER_NUM    ORIGIN_CON_ID
____________________ _____________ _________________ _____________ _______________________________ _________________________________ __________________________ ____________ ________________
COUNTRIES_DIM_ATT    Continent     N                 STANDARD      '#'||to_char("Continent ID")    upper(substr("Continent",1,3))    "Continent"                           0                3
DAYS_DIM_ATT         Day           N                 DAYS          TO_CHAR("Date")                                                                                         0                3
COUNTRIES_DIM_ATT    Country       N                 STANDARD      "Country ID"                    "Country"                         "Country name"                        1                3
CALENDAR_DIM_ATT     Day           N                 STANDARD      TO_CHAR("Date")                                                                                         0                3

There are more that we can define here. I the same way we want to simplify the PARTITION BY clause of analytic function, thanks to levels, we avoid the ORDER BY clause with ordering in each level. I keep it simple here.

For drill-down analytics, we query on hierarchies.

Hierarchy

This is a simple declaration of parent-child relationship between levels:


SQL> 
create or replace hierarchy "Countries"
    using COUNTRIES_DIM_ATT
    ( "Country" child of "Continent")
 /

Hierarchy created.

This is actually a view that we can query, and the best way to understand it is to look at it.

The definition from the dictionary just reflects what we have created:


SQL> select * FROM user_hierarchies;

   HIER_NAME    DIMENSION_OWNER       DIMENSION_NAME    PARENT_ATTR    COMPILE_STATE    ORIGIN_CON_ID
____________ __________________ ____________________ ______________ ________________ ________________
Countries    DEMO               COUNTRIES_DIM_ATT                   VALID                           3

SQL> select * FROM user_hier_levels;

   HIER_NAME    LEVEL_NAME    ORDER_NUM    ORIGIN_CON_ID
____________ _____________ ____________ ________________
Countries    Continent                0                3
Countries    Country                  1                3

We can also query USER_HIER_COLUMNS to see what is exposed as a view.

but a simple DESC will show them:


SQL> desc "Countries"

                 Name    Role            Type
_____________________ _______ _______________
Country ID            KEY     VARCHAR2(10)
Country               PROP    VARCHAR2(3)
Country name          PROP    VARCHAR2(50)
Continent ID          KEY     NUMBER
Continent             PROP    VARCHAR2(10)
MEMBER_NAME           HIER    VARCHAR2(41)
MEMBER_UNIQUE_NAME    HIER    VARCHAR2(95)
MEMBER_CAPTION        HIER    VARCHAR2(12)
MEMBER_DESCRIPTION    HIER    VARCHAR2(50)
LEVEL_NAME            HIER    VARCHAR2(9)
HIER_ORDER            HIER    NUMBER
DEPTH                 HIER    NUMBER(10)
IS_LEAF               HIER    NUMBER
PARENT_LEVEL_NAME     HIER    VARCHAR2(9)
PARENT_UNIQUE_NAME    HIER    VARCHAR2(95)

This is like a join on the COUNTRIES and CONTINENTS (defined in the using clause of the attribute dimension) with the attributes exposed. But there are also additional columns that are there with standard names in all hierarchies: member name/caption/description and level information. Because all levels are here, as if we did some UNION ALL over GROUP BY queries.

Additional columns and additional rows for each level. Let’s query it:


SQL> select * from "Countries";

   Country ID    Country                         Country name    Continent ID    Continent    MEMBER_NAME    MEMBER_UNIQUE_NAME    MEMBER_CAPTION                   MEMBER_DESCRIPTION    LEVEL_NAME    HIER_ORDER    DEPTH    IS_LEAF    PARENT_LEVEL_NAME    PARENT_UNIQUE_NAME
_____________ __________ ____________________________________ _______________ ____________ ______________ _____________________ _________________ ____________________________________ _____________ _____________ ________ __________ ____________________ _____________________
                                                                                           WORLD          [ALL].[WORLD]                                                                ALL                       0        0          0
                                                                            1 Asia         #1             [Continent].&[1]      ASI               Asia                                 Continent                 1        1          0 ALL                  [ALL].[WORLD]
AE            ARE        United_Arab_Emirates                               1 Asia         AE             [Country].&[AE]       ARE               United_Arab_Emirates                 Country                   2        2          1 Continent            [Continent].&[1]
AF            AFG        Afghanistan                                        1 Asia         AF             [Country].&[AF]       AFG               Afghanistan                          Country                   3        2          1 Continent            [Continent].&[1]
BD            BGD        Bangladesh                                         1 Asia         BD             [Country].&[BD]       BGD               Bangladesh                           Country                   4        2          1 Continent            [Continent].&[1]
...
VN            VNM        Vietnam                                            1 Asia         VN             [Country].&[VN]       VNM               Vietnam                              Country                  43        2          1 Continent            [Continent].&[1]
YE            YEM        Yemen                                              1 Asia         YE             [Country].&[YE]       YEM               Yemen                                Country                  44        2          1 Continent            [Continent].&[1]
                                                                            2 Africa       #2             [Continent].&[2]      AFR               Africa                               Continent                45        1          0 ALL                  [ALL].[WORLD]
AO            AGO        Angola                                             2 Africa       AO             [Country].&[AO]       AGO               Angola                               Country                  46        2          1 Continent            [Continent].&[2]
BF            BFA        Burkina_Faso                                       2 Africa       BF             [Country].&[BF]       BFA               Burkina_Faso                         Country                  47        2          1 Continent            [Continent].&[2]
...

I’ve removed many rows for clarity, but there is one row for all countries, the deepest level, plus one row for each continent, plus one row for the top summary (‘WORLD’). This is how we avoid GROUP BY in the end-user query: we just mention the level: LEVEL_NAME=’ALL’, LEVEL_NAME=’Continent’, LEVEL_NAME=’Country’. Or query the DEPTH: 0 for the global summary, 1 for continents, 2 for countries. The countries, being the most detailed level can also be queried by IS_LEAF=1. The attributes may be NULL for non-leaf levels, like “Country name” when at ‘Continent’ level, or “Continent” when at ‘ALL’ level.

In addition to the attributes, we have the standardized names, so that the user GUI can see the same column names for all dimensions. I don’t show all countries and I don’t query MEMBER_NAME and MEMBER_CAPTION to get it short here:


SQL>
select MEMBER_NAME,MEMBER_UNIQUE_NAME,LEVEL_NAME,PARENT_LEVEL_NAME,PARENT_UNIQUE_NAME,HIER_ORDER,DEPTH,IS_LEAF
 from "Countries" order by DEPTH,HIER_ORDER fetch first 10 rows only;

   MEMBER_NAME    MEMBER_UNIQUE_NAME    LEVEL_NAME    PARENT_LEVEL_NAME    PARENT_UNIQUE_NAME    HIER_ORDER    DEPTH    IS_LEAF
______________ _____________________ _____________ ____________________ _____________________ _____________ ________ __________
WORLD          [ALL].[WORLD]         ALL                                                                  0        0          0
#1             [Continent].&[1]      Continent     ALL                  [ALL].[WORLD]                     1        1          0
#2             [Continent].&[2]      Continent     ALL                  [ALL].[WORLD]                    45        1          0
#3             [Continent].&[3]      Continent     ALL                  [ALL].[WORLD]                   101        1          0
#4             [Continent].&[4]      Continent     ALL                  [ALL].[WORLD]                   156        1          0
#5             [Continent].&[5]      Continent     ALL                  [ALL].[WORLD]                   165        1          0
AE             [Country].&[AE]       Country       Continent            [Continent].&[1]                  2        2          1
AF             [Country].&[AF]       Country       Continent            [Continent].&[1]                  3        2          1
BD             [Country].&[BD]       Country       Continent            [Continent].&[1]                  4        2          1
BH             [Country].&[BH]       Country       Continent            [Continent].&[1]                  5        2          1

A row can be identified by the level (LEVEL_NAME or DEPTH) and its name but a unique name is generated here with the full path (in MDX style). This is MEMBER_UNIQUE_NAME and we have also the PARENT_UNIQUE_NAME if we want to follow the hierarchy.

Analytic View

Now that I have a view on the hierarchy, I want to join it to the fact table, in order to display the measures at different levels of aggregation. Again, I don’t want the user to think about joins and aggregation functions, and this must be encapsulated in a view, an ANALYTIC VIEW:


create or replace analytic view "COVID cases"
using CASES
dimension by (
  COUNTRIES_DIM_ATT key COUNTRY_ID references "Country ID"
  hierarchies ( "Countries")
 )
measures (
  "Cases"          fact CASES aggregate by sum,
  "Highest cases"  fact CASES aggregate by max
)
/

The USING clause just mentions the fact table. The DIMENSION clause lists all the dimensions (I have only one here for the simplicity of the example, but you will have all dimensions here) and how they join to the dimension (foreign key REFERENCES the lowest level key of the dimension). The MEASURES defines the fact columns and the aggregation function to apply to them. This can be complex to be sure it always makes sense. What is stored in one fact column can be exposed as multiple business objects attribute depending on the aggregation.

There are many functions for measures calculated. For example in the screenshot you will see at the end, I added the following to show the country covid cases as a ration on their continent ones.


 "cases/continent" as 
  ( share_of("Cases" hierarchy COUNTRIES_DIM_ATT."Countries"  level "Continent") )
  caption 'Cases Share of Continent' description 'Cases Share of Continent'

But for the moment I keep it simple with only “Cases” and “Highest cases”.

Here is the description:


SQL> desc "COVID cases"

            Dim Name    Hier Name                  Name    Role            Type
____________________ ____________ _____________________ _______ _______________
COUNTRIES_DIM_ATT    Countries    Country ID            KEY     VARCHAR2(10)
COUNTRIES_DIM_ATT    Countries    Country               PROP    VARCHAR2(3)
COUNTRIES_DIM_ATT    Countries    Country name          PROP    VARCHAR2(50)
COUNTRIES_DIM_ATT    Countries    Continent ID          KEY     NUMBER
COUNTRIES_DIM_ATT    Countries    Continent             PROP    VARCHAR2(10)
COUNTRIES_DIM_ATT    Countries    MEMBER_NAME           HIER    VARCHAR2(41)
COUNTRIES_DIM_ATT    Countries    MEMBER_UNIQUE_NAME    HIER    VARCHAR2(95)
COUNTRIES_DIM_ATT    Countries    MEMBER_CAPTION        HIER    VARCHAR2(12)
COUNTRIES_DIM_ATT    Countries    MEMBER_DESCRIPTION    HIER    VARCHAR2(50)
COUNTRIES_DIM_ATT    Countries    LEVEL_NAME            HIER    VARCHAR2(9)
COUNTRIES_DIM_ATT    Countries    HIER_ORDER            HIER    NUMBER
COUNTRIES_DIM_ATT    Countries    DEPTH                 HIER    NUMBER(10)
COUNTRIES_DIM_ATT    Countries    IS_LEAF               HIER    NUMBER
COUNTRIES_DIM_ATT    Countries    PARENT_LEVEL_NAME     HIER    VARCHAR2(9)
COUNTRIES_DIM_ATT    Countries    PARENT_UNIQUE_NAME    HIER    VARCHAR2(95)
                     MEASURES     Cases                 BASE    NUMBER
                     MEASURES     Highest cases         BASE    NUMBER

I have columns from all hierarchies, with KEY and PROPERTY attributes, and standardized names from the HIERARCHY, and the measures. You must remember that it is a virtual view: you will never query all columns and all rows. You SELECT the columns and filter (WHERE) the rows and levels and you get the result you want without GROUP BY and JOIN. If you look at the execution plan you will see the UNION ALL, JOIN, GROUP BY on the star or snowflake table. But this is out of the end-user concern. As a DBA you can create some materialized views to pre-build some summaries and query rewrite will used them.

We are fully within the initial SQL philosophy: a logical view provides an API that is independent of the physical design and easy to query, on a simple row/column table easy to visualize.

Analytic query

A query on the analytic view is then very simple. In the FROM clause, instead of tables with joins, I mention the analytic view, and instead of mentioning table aliases, I mention the hierarchy. I reference only the standard column names. Only the hierarchy names and the measures are specific. In the where clause, I can also reference the LEVEL_NAME:


SQL> 
select MEMBER_DESCRIPTION, "Cases"
 from "COVID cases" hierarchies ("Countries")
 where ( "Countries".level_name='Country' and "Countries".MEMBER_CAPTION in ('USA','CHN') )
    or ( "Countries".level_name in ('Continent','ALL') )
 order by "Cases";

         MEMBER_DESCRIPTION      Cases
___________________________ __________
Oceania                           8738
China                            84198
Africa                          203142
Asia                           1408945
United_States_of_America       1979850
Europe                         2100711
America                        3488230
                               7209766

Here I wanted to see the total covid-19 cases for all countries (‘ALL’), for each continent, and only two ones at the country level: USA and China. And this was a simple SELECT … FROM … WHERE … ORDER BY without joins and group by. Like a query on an OLAP cube.

If I had no analytic views, here is how I would have queried the tables:


SQL>
select coalesce(CONTINENT_NAME, COUNTRY_NAME,'ALL'), CASES from (
select CONTINENT_NAME, COUNTRY_NAME, sum(CASES) cases, COUNTRY_CODE, grouping(COUNTRY_CODE) g_country
from CASES join COUNTRIES using(COUNTRY_ID) join CONTINENTS using(CONTINENT_ID)
group by grouping sets ( () , (CONTINENT_NAME) , (COUNTRY_CODE,COUNTRY_NAME) )
)
where COUNTRY_CODE in ('USA','CHN') or g_country >0
order by cases
/

   COALESCE(CONTINENT_NAME,COUNTRY_NAME,'ALL')      CASES
______________________________________________ __________
Oceania                                              8738
China                                               84198
Africa                                             203142
Asia                                              1408945
United_States_of_America                          1979850
Europe                                            2100711
America                                           3488230
ALL                                               7209766

This was with GROUPING SETS to add multiple levels and GROUPING() function to detect the level. Without GROUPING SETS I may have done it with many UNION ALL between GROUP BY subqueries.

Back to roots of SQL

You may think that you don’t need Analytic Views because the same can be done by some BI reporting tools. But this should belong to the database. SQL was invented to provide a simple API to users. If you need an additional layer with a large repository of metadata and complex transformations between the user-defined query and the SQL to execute, then something is missed from the initial goal. One consequence is people going to NoSQL hierarchical databases with the idea that they are easier to visualize: simple API (a key-value get) and embedded metadata (as JSON for example). While SQL was more and more powerful to process data in the database, the complexity was going too far and developers prefered to come back to their procedural code rather than learning something new. And the first step of many current developments is to move the data out of the database, to NoSQL, or to an OLAP cube in our case.

Analytic views bring back the power of SQL: the view exposes a Data Mart as one simple table with columns and rows, containing all dimensions and levels of aggregation. The metadata that describes the data model is back where it belongs: the data dictionary. My example here is a very simple one but it can go further, with classification to add more metadata for self-documentation, with more hierarchies (and a special one for the time dimension), and many calculated measures.
SQL on it is simplified, and there are also some GUI over analytic views, like APEX, or SQL Developer:

And if SQL is still too complex, it seems that we can query Analytic Views with MDX (MultiDimensional eXpressions). The MEMBER_UNIQUE_NAME follows the MDX syntax and we can find this in ?/mesg/oraus.msg list of error messages:


/============================================================================
/
/    18200 - 18699 Reserved for Analytic View Sql (HCS) error messages
/
/============================================================================
/
/// 18200 - 18219 reserved for MDX Parser
/

HCS is the initial name of this feature (Hierarchical Cubes). I’ve not seen other mentions of MDX in the Oracle Database documentation, so I’ve no idea if it is already implemented.

Cet article No{Join,GroupBy}SQL – Analytic Views for BI est apparu en premier sur Blog dbi services.

Control-M/EM :Put a job already ordered in dummy job

Thu, 2020-06-11 08:14

Hi everybody.

Today we will see how to put a job in dummy mode on the monitoring part.

Introduction:

In some case you want, or you will be asked to bypass a treatment. For that, you can put the job in dummy mode. But how can we proceed if this request is needed for a job already ordered and ready to be executed?

Example:

  1. You made a manual backup and the same task is usually scheduled in a job
  2. This job is part of a workflow and depending of other jobs
  3. You want to keep this workflow processing.

If you want to avoid job’s execution of one or more jobs, you may have many choices:

1.Update the job in job definition (configure it in dummy mode in the planning pane) then order it again in monitoring part that implies you have to substitute the previous one and be careful to give it the same conditions to fit to the workflow)

Quite a touchy action as you must link the dummy job to the workflow (keeping all conditions ) and delete the old job that you want to substitute.

2.Delete the job,that implies you must review conditions of the workflow

3.Update the job in monitoring part with the run now options. (which is much better and the one we will choose)

4.Other tricky modifications and workarounds( such as substitute the command line,example: echo “backup ever done today”). The aim is only to get the job ending OK without any incidence in the workflow.

 

Workshop :

We will use the 3dr method Update the job in monitoring part with the run now options

Note that it will be available only for the current day

Let’s take an example:

Below you have a workflow containing a job performing a save.

When the workflow is performed as usual,we can check that the saving script is executing , as you can see in the output:

Then once finished, this job will send the conditions to the next job.

 

To bypass this backup action performed in the job, we will use the jobs options:

-Right click on the job and select “run now” then “Select Bypass Options”

-Tick the box Run as dummy jobs in the additional bypass options

 

-Once done , you can verify that the job is set as a dummy job by checking its log:

 

Now let see if the workflow will process and if the saving job will execute or not:

As we can see the job had the same behavior of a “native” dummy job ( no execution, just giving a status OK and send conditions )

Consequently , when you try to check the log you get this message explaining that no output is available :

Which is expected, as a dummy job has no output.

Note that for this case ,you can have some elements to take in account to manage your workflow:

As you know , the dummy job gives his status OK quickly , so you must take in account that your job may have its running time that impact the workflow ending time .Following our example, if the backup job has an average execution time of 3 hours, then if you put it in dummy,the next depending job will sent his report 3hours earlier,as dummy job has instantaneous execution.

So, everything depends on your strategy and when you expect the workflow to finish

 

Important:

You have noticed that when you put a job in dummy using this way you have no GHOST icon, you must check the job’s log to see is the job was set in dummy during the day.

Contrary to dummy update through planning pane:

When the job is ordered after having checked off the run as dummy box , you will have on monitoring the ghost icon ( but we have to be aware that this modification is not only for the day but it is definitive and is not satisfying the first request which was to dummy the job only for the day )

 

Conclusion:

 

Using run now option on a job is a quick and safe way to put job in dummy mode for the current day, keep also in mind that execution time may make your depending jobs executing earlier.

In case you want to put the job in dummy for many days, you must update it in the planning pane.

Once again, I invite you to consult my other posts and my colleagues’ blogs.

You can also get more tips and tricks following the BMC support site!

 

 

Cet article Control-M/EM :Put a job already ordered in dummy job est apparu en premier sur Blog dbi services.

Control-M/EM: Cyclic job interval less than 1 min

Thu, 2020-06-11 08:13
Introduction

By default, the minimal execution’s interval of a cyclic job is 1 minute (with the rerun every 0 sec option it will loop instantaneously).

But what happens when you need to rerun it in a interval between 0 and 1 minute?

 

Let’s use a little trick to achieve that:

 

1)Job configuration

 

  • In planning pane create a job
  • When selecting this job, go in “scheduling” tab part and tick the “cyclic” box:

 

 

Result:

As soon as you define your job as cyclic it will take the default configured value and rerun every (1) minutes from job’s start

 

  • We will update this part by switching to 0 minute(s) from Job’s start (be careful about using cyclic job to  0 )

 

 

  • We also will keep the maximum rerun at 0 for our test:

 

2)Using sleep command and Control-M/Agent _sleep utility

 

Depending of the OS where agent is installed, you can use these two commands:

 

a) For Windows OS :

you have to use the Control-M/Agent _sleep utility  (time in sec):

Definition from Control-M Help ( available on your Control-M workload automation client opening file/help/view help  ):

b) For Unix/Linux OS:

the sleep command with time needed in second

 

[ctmag900@CTMSRVCENTOS ~]$ sleep 30

[ctmag900@CTMSRVCENTOS ~]$¨

 

As we are on a Linux machine, we will use the sleep built-in shell function from UNIX.

 

We will add it to “post-execution” or “pre-execution” command part :

 

(Note: On older Control-M version (before 8.0.00) it was named pre-cmd and post-cmd)

In our example we have scheduled the job to be executed every 30 sec by using the below syntax:

sleep 30

3)Order job and check log

Having a look on the submission intervals, it appears that we have the expected result ,with job executing every 30 sec:

 

 

Note:

You can have a slight delay depending of the response time of your machine where the task is running

 

 

Conclusion:

You are now able to perform cyclic jobs in less than 1 minute intervals by using ths tip.

 

To get more information, you can consult BMC’s site and of course don’t forget to check dbi’s blogger for more tips and tricks!

Cet article Control-M/EM: Cyclic job interval less than 1 min est apparu en premier sur Blog dbi services.

Oracle 12c – global partial index

Wed, 2020-06-10 00:02
By Franck Pachot

.
We have an incredible number of possibilities with Oracle. Yes, an index can be global (indexing many partitions without having to be partitioned itself on the same key) and partial (skipping some of the table partitions where we don’t need indexing). In the previous post of this series of small examples on recent features I partitioned a table, with covid-19 cases per day and per country, partitioned on range of date by interval. The index on the country code (GEOID) was not very efficient for data ingested per day, because countries are scattered through all the table. And then I have reorganized the old partitions to cluster them on countries.

My global index on country code is defined as:


SQL> create index covid_geoid on covid(geoid);

Index created.

This is efficient, thanks to clustering, except for the new rows coming again in time order. As those go to a new partition that is small (the idea in the post was to have short time range for the current partition, and larger ones for the old, using the ALTER TABLE … MERGE ONLINE to merge the newly old one to the others). For the current partition only, it is preferable to full scan this last partition. And even avoid maintaining the index entries for this partition as this will accelerate data ingestion.

I think that partial indexing is well known for local indexes, as this is like marking some index partitions as unusable. But here I’m showing it on a global index.

Splitting partitions

In order to continue from the previous previous post where I merged all partitions, I’ll split them again, and this can be an online operation in 12cR2:


SQL> alter table covid split partition oldmerged at (date '2020-04-01') into (partition old, partition new) online;

Table altered.

SQL> alter index COVID_GEOID coalesce cleanup;

Index altered.

I have two partitions, “old” and “new”, and a global index. I also cleaned up the orphaned index entries to get clean execution plans. And it has to be done anyway.

Here is my query, using the index:


SQL> explain plan for select trunc(daterep,'mon'), max(cases) from covid where geoid='US' group by trunc(daterep,'mon') order by 1;

Explained.

SQL> select * from dbms_xplan.display();
                                                                                                              PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________________________
Plan hash value: 2816502185

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |             |   101 |  1515 |     6  (34)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                               |             |   101 |  1515 |     6  (34)| 00:00:01 |       |       |
|   2 |   HASH GROUP BY                              |             |   101 |  1515 |     6  (34)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| COVID       |   160 |  2400 |     4   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                         | COVID_GEOID |   160 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------

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

   4 - access("GEOID"='US')

This goes to all partitions, as the ROWID in a global index carries the partition information through the data object id. We see that with Pstart/Pstop=ROWID.

Partial indexing

Now I want to set my global index on countries to be a partial index:


SQL> alter index covid_geoid indexing partial;

Index altered.

This doesnt change anything for the moment. The indexing of partitions will depend on the partition attributes which is by default INDEXING ON.

I set the “new” partition to not maintain indexes (INDEXING OFF), for this partition only.


SQL> alter table covid modify partition new indexing off;

Table altered.

This means that partial indexes will not reference the “new” partition. Whether they are local (which then means no index partition) or global (which then means no index entries for this partition).

And that’s all. Now there will be no overhead in maintaining this index when ingesting new data in this partition.

Table Expansion

And then, the optimizer has a transformation to split the execution plan in two branches: one for the index access and one without. This transformation was introduced in 11g for unusable local partitions and is now used even with global indexes. :


SQL> explain plan for /*+ index(covid) */ select trunc(daterep,'mon'), max(cases) from covid where geoid='US' group by trunc(daterep,'mon') order by 1;

Explained.

SQL> select * from dbms_xplan.display();
                                                                                                                PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________________________________________
Plan hash value: 1031592504

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |             |   321 |  7062 |    37   (6)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                                 |             |   321 |  7062 |    37   (6)| 00:00:01 |       |       |
|   2 |   HASH GROUP BY                                |             |   321 |  7062 |    37   (6)| 00:00:01 |       |       |
|   3 |    VIEW                                        | VW_TE_2     |   321 |  7062 |    35   (0)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                  |             |       |       |            |          |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| COVID       |    93 |  1395 |     4   (0)| 00:00:01 |     1 |     1 |
|*  6 |       INDEX RANGE SCAN                         | COVID_GEOID |   160 |       |     1   (0)| 00:00:01 |       |       |
|   7 |      PARTITION RANGE SINGLE                    |             |    68 |  1020 |    27   (0)| 00:00:01 |     2 |     2 |
|*  8 |       TABLE ACCESS FULL                        | COVID       |    68 |  1020 |    27   (0)| 00:00:01 |     2 |     2 |
|   9 |      TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| COVID       |   160 |  4320 |     4   (0)| 00:00:01 | ROWID | ROWID |
|* 10 |       INDEX RANGE SCAN                         | COVID_GEOID |   160 |       |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("COVID"."DATEREP"=TO_DATE(' 2020-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "COVID"."DATEREP"<TO_DATE(' 2021-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  10 - access("GEOID"='US')
       filter(TBL$OR$IDX$PART$NUM("COVID",0,8,0,ROWID)=1 AND TBL$OR$IDX$PART$NUM("COVID",0,0,65535,ROWID)1 AND
              TBL$OR$IDX$PART$NUM("COVID",0,0,65535,ROWID)2)

The TABLE ACCESS BY GLOBAL INDEX ROWID is for partition 1 as mentioned by Pstart/Pstop, which is the “old” one with INDEXING ON. The TABLE ACCESS FULL is for partition 2, the “new” one, that has INDEXING OFF. The optimizer uses predicates on the partition key to select the branch safely.

But this plan has also an additional branch and this TBL$OR$IDX$PART$NUM again because I have interval partitioning. With interval partitioning, there is no known Pstop, it then it has handle the cases where a new partition has been created (with indexing on). Then, the third branch can access by index ROWID for the partitions that are not hardcoded in this plan.

Let’s remove interval partitioning just to get the plan easier to read:


SQL> alter table covid set interval();

Table altered.


SQL> explain plan for select trunc(daterep,'mon'), max(cases) from covid where geoid='US' group by trunc(daterep,'mon') order by 1;

Explained.

SQL> select * from dbms_xplan.display();
                                                                                                                PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________________________________________
Plan hash value: 3529087922

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |             |   161 |  3542 |    35   (6)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                                 |             |   161 |  3542 |    35   (6)| 00:00:01 |       |       |
|   2 |   HASH GROUP BY                                |             |   161 |  3542 |    35   (6)| 00:00:01 |       |       |
|   3 |    VIEW                                        | VW_TE_2     |   161 |  3542 |    33   (0)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                  |             |       |       |            |          |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| COVID       |    93 |  1395 |     6   (0)| 00:00:01 |     1 |     1 |
|*  6 |       INDEX RANGE SCAN                         | COVID_GEOID |   160 |       |     1   (0)| 00:00:01 |       |       |
|   7 |      PARTITION RANGE SINGLE                    |             |    68 |  1020 |    27   (0)| 00:00:01 |     2 |     2 |
|*  8 |       TABLE ACCESS FULL                        | COVID       |    68 |  1020 |    27   (0)| 00:00:01 |     2 |     2 |
------------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("COVID"."DATEREP"<TO_DATE(' 2020-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("GEOID"='US')
   8 - filter("GEOID"='US')

Here it is clear: access by index to the partition 1 and full table scan for partition 2. This is exactly what I wanted because I know the clustering factor on the new partition is not very good until I reorganize it (move or merge as I did in the previous post).

All these features help to manage the lifecycle of data. That’s a completely different approach from purpose-built databases where you have one database service for fast ingest with simple queries on recent data (NoSQL folks may think about DynamoDB for that), then streaming data to a relational database for more OLTP queries (RDS to continue with the AWS analogy), and move old data into a database dedicated to analytics (that could be Redshift then). With Oracle, which has always been a multi-purpose database, the goal is to avoid duplication and replication and manage data in-place for all usage. Through the 40 years of this database engine, many approaches have been implemented to cluster data: CLUSTER and IOT can sort (or hash) data as soon as it is inserted, in order to put them at their optimal place for future queries. But the agility of heap tables finally wins. Now, with the ease of in-database data movement (partitioning and online operations) and improvement of full scan (multiblock reads, direct-path reads, storage indexes) we can get the best of both: heap tables with few indexes for fast ingest of current data, reorganize regularly to be clustered, with additional indexes.

I mentioned NoSQL and I mentioned fast ingest. Actually, there’s a feature called Fast Ingest for IoT (lowercase ‘o’ there) that goes further with this idea. Instead of inserting into a persistent segment and reorganize later, rows are buffered in a ‘memoptimized rowstore’ before going to the heap segment in bulk. But that’s an Exadata feature and I like to think about Oracle as a multiplatform database.

Cet article Oracle 12c – global partial index est apparu en premier sur Blog dbi services.

Oracle 12c – reorg and split table with clustering

Tue, 2020-06-09 23:49
By Franck Pachot

.
In this series of small examples on recent features, I have imported in a previous post, the statistics of covid-19 per day and per countries. This is typical of data that comes as a time-series ordered by date, because this is how it is generated day after day, but where you probably want to query from another dimension, like per countries.

If you want to ingest data faster, you keep it in the order of arrival, and insert it in heap table blocks. If you want to optimize for the future queries on the other dimension, you may load it in a table with a specialized organization where each row has its place: an Index Organized Table, a Hash Cluster, a partitioned table, or a combination of those. With Oracle we are used to storing data without the need to reorganize it. It is a multi-purpose database. But in 12c we have many features that make this reorganization easier, like partitioning, online move and online split. We can then think about a two-phase lifecycle for some operational tables that are used later for analytics:

  • Fast ingest and query on short time window: we insert data on the flow, with conventional inserts, into a conventional heap table. Queries on recent data is fast as the rows are colocated as they arrived.
  • Optimal query on history: regularly we reorganize physically the latest ingested rows, to be clustered on another dimension, because we will query for a large time range on this other dimension

Partitioning is the way to do those operations. We can have a weekly partition for the current week. When the week is over new rows will go to a new partition (11g PARTITION BY RANGE … INTERVAL) and we can optionally merge the old partition with the one containing old data, per month or year for example, to get larger time ranges for the past data. This merge is easy (18c MERGE PARTITIONS … ONLINE). And while doing that we can reorganize rows to be clustered together. This is what I’m doing in this post.

Partitioning

From the table, I have created in the previous post I create an index on GEOID (as the goal is to query by countries) and I partition it by range on DATEREP:


SQL> create index covid_geoid on covid(geoid);

Index created.

SQL> alter table covid modify partition by range(daterep) interval (numToYMinterval(1,'year')) ( partition old values less than (date '2020-01-01') , partition new values less than (date '2021-01-01') ) online;

Table altered.

This is an online operation in 12cR2. So I have two partitions, one for “old” data and one for “new” data.

I query all dates for one specific country:


SQL> select trunc(daterep,'mon'), max(cases) from covid where geoid='US' group by trunc(daterep,'mon') order by 1
  2  /
   TRUNC(DATEREP,'MON')    MAX(CASES)
_______________________ _____________
01-DEC-19                           0
01-JAN-20                           3
01-FEB-20                          19
01-MAR-20                       21595
01-APR-20                       48529
01-MAY-20                       33955
01-JUN-20                       25178

This reads rows scattered through the whole table because they were inserted day after day.

This is visible in the execution plan: the optimizer does not use the index but a full table scan:


SQL> select * from dbms_xplan.display_cursor(format=>'+cost iostats last')
  2  /
                                                                                       PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________
SQL_ID  2nyu7m59d7spv, child number 0
-------------------------------------
select trunc(daterep,'mon'), max(cases) from covid where geoid='US'
group by trunc(daterep,'mon') order by 1

Plan hash value: 4091160977

-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |    55 (100)|      7 |00:00:00.01 |     180 |
|   1 |  SORT ORDER BY       |       |      1 |     77 |    55   (4)|      7 |00:00:00.01 |     180 |
|   2 |   PARTITION RANGE ALL|       |      1 |     77 |    55   (4)|      7 |00:00:00.01 |     180 |
|   3 |    HASH GROUP BY     |       |      2 |     77 |    55   (4)|      7 |00:00:00.01 |     180 |
|*  4 |     TABLE ACCESS FULL| COVID |      2 |    105 |    53   (0)|    160 |00:00:00.01 |     180 |
-----------------------------------------------------------------------------------------------------

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

   4 - filter("GEOID"='US')

This has read 180 blocks, with multiblock reads.

I force the access by index in order to compare the cost:


SQL> select /*+ index(covid) */ trunc(daterep,'mon'), max(cases) from covid where geoid='US' group by trunc(daterep,'mon') order by 1
  2  /

   TRUNC(DATEREP,'MON')    MAX(CASES)
_______________________ _____________
01-DEC-19                           0
01-JAN-20                           3
01-FEB-20                          19
01-MAR-20                       21595
01-APR-20                       48529
01-MAY-20                       33955
01-JUN-20                       25178

SQL> select * from dbms_xplan.display_cursor(format=>'+cost iostats last')
  2  /
                                                                                                                     PLAN_TABLE_OUTPUT
______________________________________________________________________________________________________________________________________
SQL_ID  2whykac7cnjks, child number 0
-------------------------------------
select /*+ index(covid) */ trunc(daterep,'mon'), max(cases) from covid
where geoid='US' group by trunc(daterep,'mon') order by 1

Plan hash value: 2816502185

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |             |      1 |        |    95 (100)|      7 |00:00:00.01 |     125 |
|   1 |  SORT ORDER BY                               |             |      1 |     77 |    95   (3)|      7 |00:00:00.01 |     125 |
|   2 |   HASH GROUP BY                              |             |      1 |     77 |    95   (3)|      7 |00:00:00.01 |     125 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| COVID       |      1 |    105 |    93   (0)|    160 |00:00:00.01 |     125 |
|*  4 |     INDEX RANGE SCAN                         | COVID_GEOID |      1 |    105 |     1   (0)|    160 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("GEOID"='US')

Even if the number of blocks is a bit smaller, 125 blocks, they are single block reads and then the cost is higher: 95 for index access when the full table scan was 55. Using hints and comparing the cost is how I often try to understand the optimizer choice and here the reason is clear: because rows are scattered, the clustering factor of the index access is really bad.

I said that I want to merge the partitions. And maybe reorg with an online table move. But now, for this second phase of the lifecycle, I want to cluster rows on the country dimension rather than on arrival date.

Attribute clustering

This preference can be declared on the table with 12c Attribute Clustering:


SQL> alter table covid add clustering by linear order (continentexp, countriesandterritories);

Table altered.

You see that I can mention multiple columns and I don’t need to use the GEOID column that I will use to query. This is not an index. This just a preference to cluster rows and, if they are clustered on the country name, they will be also clustered on continent, country code, geoid,… I have chosen those columns for clarity when reading the DDL:


SQL> exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

SQL> ddl covid

  CREATE TABLE "COVID"
   (    "DATEREP" DATE,
        "N_DAY" NUMBER,
        "N_MONTH" NUMBER,
        "N_YEAR" NUMBER,
        "CASES" NUMBER,
        "DEATHS" NUMBER,
        "COUNTRIESANDTERRITORIES" VARCHAR2(50),
        "GEOID" VARCHAR2(10),
        "COUNTRYTERRITORYCODE" VARCHAR2(3),
        "POPDATA2018" NUMBER,
        "CONTINENTEXP" VARCHAR2(10)
   )
 CLUSTERING
 BY LINEAR ORDER ("COVID"."CONTINENTEXP",
  "COVID"."COUNTRIESANDTERRITORIES")
   YES ON LOAD  YES ON DATA MOVEMENT
 WITHOUT MATERIALIZED ZONEMAP
  PARTITION BY RANGE ("DATEREP") INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
 (PARTITION "OLD"  VALUES LESS THAN (TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
 PARTITION "NEW"  VALUES LESS THAN (TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ) ;

  CREATE INDEX "COVID_GEOID" ON "COVID" ("GEOID")
  ;

As you can see the default is YES for ON LOAD which means that direct-path inserts will cluster rows, and ON DATA MOVEMENT is also YES which is why merging partitions will also cluster rows.

I’ve done that afterward here but this is something you can do at table creation. You mention on which attributes you want to cluster. You mention when: direct-path inserts (YES ON LOAD) and/or table reorganization (YES ON DATA MOVEMENT). This is defined at table level. Beyond those defaults, the table reorganizations (ALTER TABLE … MOVE, ALTER TABLE … MERGE PARTITIONS) can explicitly DISALLOW CLUSTERING or ALLOW CLUSTERING.

Move Partition

When I have ingested some data and think that it would be better to cluster them, maybe at the time this partition is completed and new inserts go to a higher interval, I can reorganize it with a simple ALTER TABLE … MOVE:


SQL> alter table covid move partition new online allow clustering;

Table altered.

This will cluster rows together on the clustering attributes. I mentioned ALLOW CLUSTERING to show the syntax but it is the default (YES ON DATA MOVEMENT) anyway here.

At that point, you may also want to compress the old partitions with basic compression (the compression that does not require an additional option but is possible only with bulk load or data movement). However, be careful: the combination of online operation and basic compression requires the Advanced Compression Option. More info in a previous post on “Segment Maintenance Online Compress” feature usage.

Merge Partition

As my goal is to cluster data on a different dimension than the time one, I may want to have larger partitions for the past ones. Something like the current partition holding a week of data at maximum, but the past partitions being on quarter or yearly ranges. That can be done with partition merging, which is an online operation in 18c (and note that I have a global index here and an online operation does not invalidate indexes):


SQL> alter table covid merge partitions old,new into partition oldmerged online allow clustering;

Table altered.

This is a row movement and clustering on data movement is enabled. Again I mentioned ALLOW CLUSTERING just to show the syntax.

Let’s see the number of buffers read now with index accesss. The statistics of the index (clustering factor) has not been updated, so the optimizer may not choose the index access yet (until dbms_stats runs on stale tables). I’m forcing with an hint:


SQL> select /*+ index(covid) */ trunc(daterep,'mon'), max(cases) from covid where geoid='US' group by trunc(daterep,'mon') order by 1;

   TRUNC(DATEREP,'MON')    MAX(CASES)
_______________________ _____________
01-DEC-19                           0
01-JAN-20                           3
01-FEB-20                          19
01-MAR-20                       21595
01-APR-20                       48529
01-MAY-20                       33955
01-JUN-20                       25178

SQL> select * from dbms_xplan.display_cursor(format=>'+cost iostats last')
  2  /
                                                                                                                     PLAN_TABLE_OUTPUT
______________________________________________________________________________________________________________________________________
SQL_ID  2whykac7cnjks, child number 0
-------------------------------------
select /*+ index(covid) */ trunc(daterep,'mon'), max(cases) from covid
where geoid='US' group by trunc(daterep,'mon') order by 1

Plan hash value: 2816502185

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |             |      1 |        |    95 (100)|      7 |00:00:00.01 |       8 |
|   1 |  SORT ORDER BY                               |             |      1 |     77 |    95   (3)|      7 |00:00:00.01 |       8 |
|   2 |   HASH GROUP BY                              |             |      1 |     77 |    95   (3)|      7 |00:00:00.01 |       8 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| COVID       |      1 |    105 |    93   (0)|    160 |00:00:00.01 |       8 |
|*  4 |     INDEX RANGE SCAN                         | COVID_GEOID |      1 |    105 |     1   (0)|    160 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("GEOID"='US')
       filter(TBL$OR$IDX$PART$NUM(,0,8,0,"COVID".ROWID)=1)

The cost has not changed (because of the statistics) but the number of buffers read is minimal: only the 8 buffers where all my rows for this country are clustered. Remember that I clustered on the country name but use the GEOID here in my predicate. That doesn’t matter as long as the rows are together.

Asynchronous global index maintenance

Note the strange predicate on TBL$OR$IDX$PART$NUM(,0,8,0,”COVID”.ROWID)=1 that results from another 12c feature where global indexes are maintained usable during the partition maintenance (which is required for an online operation) but optimized to be cleaned-out asynchronously later. This is visible from DBA_INDEXES:


SQL> select index_name,to_char(last_analyzed,'hh24:mi:ss') last_analyzed,clustering_factor,orphaned_entries from user_indexes where table_name='COVID';

    INDEX_NAME    LAST_ANALYZED    CLUSTERING_FACTOR    ORPHANED_ENTRIES
______________ ________________ ____________________ ___________________
COVID_GEOID    08:33:34                        19206 YES

Orphaned entries mean that some entries in the global index may reference the dropped segment after my MOVE or MERGE and the query has to ignore them.

Those ranges of rowid are determined from the segment concerned, stored in the dictionary:


SQL> select * from sys.index_orphaned_entry$;
   INDEXOBJ#    TABPARTDOBJ#    HIDDEN
____________ _______________ _________
       79972           79970 O
       79972           79971 O
       79972           79980 O
       79972           79973 O

HIDDEN=’O’ means Orphaned and the ROWIDs addressing these partitions are filtered out from the dirty index entries buy the predicated filter(TBL$OR$IDX$PART$NUM(,0,8,0,”COVID”.ROWID)=1) above.

This maintenance of the dirty index will be done during the maintenance window but I can do it immediately to finish my reorganization correctly:


SQL> alter index COVID_GEOID coalesce cleanup;

Index altered.

SQL> select index_name,to_char(last_analyzed,'hh24:mi:ss') last_analyzed,clustering_factor,orphaned_entries from user_indexes where table_name='COVID';

    INDEX_NAME    LAST_ANALYZED    CLUSTERING_FACTOR    ORPHANED_ENTRIES
______________ ________________ ____________________ ___________________
COVID_GEOID    08:33:34                        19206 NO

No orphaned index entries anymore. Note that I could also have called the DBMS_PART.CLEANUP_GIDX procedure to do the same.

This is fine for the query, but as the statistics were not updated, the optimizer doesn’t know yet how clustered is my table. In order to complete my reorganization and have queries benefiting from this immediately, I gather the statistics:


SQL> exec dbms_stats.gather_table_stats(user,'COVID',options=>'gather auto');

PL/SQL procedure successfully completed.

SQL> select index_name,to_char(last_analyzed,'hh24:mi:ss') last_analyzed,clustering_factor,orphaned_entries from user_indexes where table_name='COVID';

    INDEX_NAME    LAST_ANALYZED    CLUSTERING_FACTOR    ORPHANED_ENTRIES
______________ ________________ ____________________ ___________________
COVID_GEOID    08:38:40                          369 NO

GATHER AUTO gathers only the stale ones, and, as soon as I did my MOVE or MERGE, the index was marked as stale (note that the ALTER INDEX COALESCE does not mark them a stale by itself).

And now my query will use this optimal index without the need for any hint:


SQL_ID  2nyu7m59d7spv, child number 0
-------------------------------------
select trunc(daterep,'mon'), max(cases) from covid where geoid='US'
group by trunc(daterep,'mon') order by 1

Plan hash value: 2816502185

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |             |      1 |        |     7 (100)|      7 |00:00:00.01 |       5 |
|   1 |  SORT ORDER BY                               |             |      1 |    101 |     7  (29)|      7 |00:00:00.01 |       5 |
|   2 |   HASH GROUP BY                              |             |      1 |    101 |     7  (29)|      7 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| COVID       |      1 |    160 |     5   (0)|    160 |00:00:00.01 |       5 |
|*  4 |     INDEX RANGE SCAN                         | COVID_GEOID |      1 |    160 |     2   (0)|    160 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("GEOID"='US')

and, thanks to the coalesce cleanup, there’s no predicate on orphan ROWIDs anymore.

With this pattern, you may realize that my global index on countries is useful only for past data. Not for the recent one that has not been clustered yet. Then, we can even avoid maintaining the index for this partition. We will see that in the next post. it is called partial indexing.

With this pattern, we can even doubt about the need to maintain an index for the old partitions. As all my rows for GEOID=’US’ were packed in a few contiguous blocks, why not just store the range of ROWIDs rather than the list of it? This is called Zone Maps. But this is only available on Exadata and I like to think about Oracle as a multiplatform database.

Those many features came in the recent releases thanks to the development of the Autonomous Database. When the DBA is a cloud provider, whether it is automated or not, all maintenance must be done online without stopping the application. Those features are the bricks to build automatic lifecycle management and performance optimization.

Cet article Oracle 12c – reorg and split table with clustering est apparu en premier sur Blog dbi services.

Oracle 12c – peak detection with MATCH_RECOGNIZE

Mon, 2020-06-08 09:39
By Franck Pachot

.
This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I’ll show a very basic example of “Row Pattern Recognition” (the MATCH_RECOGNIZE clause in a SELECT which is documented as “row pattern matching in native SQL” feature by Oracle”). You may be afraid of those names. Of course, because SQL is a declarative language there is a small learning curve to get beyond this abstraction. Understanding procedurally how it works may help. But when you understand the declarative nature it is really powerful. This post is there to start simple on a simple table with time series where I just want to detect peaks (the points where the value goes up and then down).

Historically, a SELECT statement was operating on single rows (JOIN, WHERE, SELECT) within a set, or an aggregation of rows (GROUP BY, HAVING) to provide a summary. Analytic functions can operate on windows of rows (PARTITION BY, ORDER BY, ROWS BETWEEN,…) where you keep the detailed level or rows and compare it to the aggregated values of the group. A row can then look at its neighbours and when needing to go further, the SQL MODEL can build the equivalent of spreadsheet cells to reference other rows and columns. As in a spreadsheet, you can also PIVOT to move row detail to columns or vice versa. All that can be done in SQL, which means that you don’t code how to do it but just define the result you want. However, there’s something that is easy to do in a spreadsheet application like Excel but not easy to code with analytic functions: looking at a Chart, as a Line Graph, to detect some behaviour. That’s something we can code in SQL with MATCH_RECOGNIZE.

For example, from the “COVID” table I have imported in the previous post I want to see each peak of covid-19 cases in Switzerland:

I did this manually in Excel: showing all labels but keeping only those that are at a peak, whether it is a small peak or high one. There’s one value per day in this timeseries but I’m am not interested by the intermediate values. Only peaks. So, this was done from the .csv imported from http://opendata.ecdc.europa.eu/covid19/casedistribution/csv/ through an external table but, as I imported it into an Oracle table for the previous post (Oracle 18c – select from a flat file).

Ok, let’s show directly the result. Here is a small SQL statement that show me exactly those peaks, each match being numbered:


SQL> select countriesandterritories "Country","Peak date","Peak cases","match#"
  2  from covid
  3  match_recognize (
  4   partition by continentexp, countriesandterritories order by daterep
  5   measures
  6    match_number() as "match#",
  7    last(GoingUp.dateRep) as "Peak date",
  8    last(GoingUp.cases) as "Peak cases"
  9   one row per match
 10   pattern (GoingUp+ GoingDown+)
 11   define
 12    GoingUp as ( GoingUp.cases > prev(GoingUp.cases) ),
 13    GoingDown as ( GoingDown.cases < prev(GoingDown.cases))
 14  )
 15  where countriesandterritories='Switzerland';

       Country    Peak date    Peak cases    match#
______________ ____________ _____________ _________
Switzerland    26-FEB-20                1         1
Switzerland    28-FEB-20                7         2
Switzerland    07-MAR-20              122         3
Switzerland    09-MAR-20               68         4
Switzerland    14-MAR-20              267         5
Switzerland    16-MAR-20              841         6
Switzerland    18-MAR-20              450         7
Switzerland    22-MAR-20             1237         8
Switzerland    24-MAR-20             1044         9
Switzerland    28-MAR-20             1390        10
Switzerland    31-MAR-20             1138        11
Switzerland    03-APR-20             1124        12
Switzerland    08-APR-20              590        13
Switzerland    10-APR-20              785        14
Switzerland    16-APR-20              583        15
Switzerland    18-APR-20              346        16
Switzerland    20-APR-20              336        17
Switzerland    24-APR-20              228        18
Switzerland    26-APR-20              216        19
Switzerland    01-MAY-20              179        20
Switzerland    09-MAY-20               81        21
Switzerland    11-MAY-20               54        22
Switzerland    17-MAY-20               58        23
Switzerland    21-MAY-20               40        24
Switzerland    24-MAY-20               18        25
Switzerland    27-MAY-20               15        26
Switzerland    29-MAY-20               35        27
Switzerland    06-JUN-20               23        28


28 rows selected.

Doing that with analytic functions or MODEL clause is possible, but not easy.

So let’s explain the clauses in this simple example.

Define

I’ll need to define what is a peak. For that, I need to define two very primary patterns. The value I’m looking for, which is the one you see on the graph, is the column “CASES”, which is the number of covid-19 cases for the day and country. How do you detect peaks visually? Like when hiking in mountains: it goes up and when you continue it goes down. Here are those two primary patterns:


 11   define
 12    GoingUp as ( GoingUp.cases >= prev(GoingUp.cases) ),
 13    GoingDown as ( GoingDown.cases < prev(GoingDown.cases))

“GoingUp” matches a row where “cases” value is higher than the preceding row and “GoingDown” matches a row where “cases” is lower than the preceding one. The sense of “preceding one”, of course, depends on an order, like with analytic functions. We will see it below.

Pattern

A peak is when a row matches GoingDown just after matching GoingUp. That’s simple but you can imagine crazy things that a data scientist would want to recognize. And then the MATCH_RECOGNIZE defines patterns in a similar way as Regular Expressions: mentioning the primary patterns in a sequence with some modifiers. Mine is so simple:


 10   pattern (GoingUp+ GoingDown+)

This means: one or more GoingUp followed by one or more GoingDown. This is exactly what I did in the graph above: ignore intermediate points. So, the primary pattern compares a row with the preceding only and consecutive comparisons are walked through and compared with the pattern.

Partition by

As mentioned, I follow the rows in order. For a timeseries, this is simple: the key is the country here, I partition by continent and country, and the order (x-axis) is the date. I’m looking at the peaks per country when the value (“cases”) is ordered by date (“daterep”):


  2  from covid
...
  4   partition by continentexp, countriesandterritories order by daterep
...
 15* where countriesandterritories='Switzerland';

I selected only my country here with a standard where clause, to show simple things.

Measures

Eatch time a pattern is recognized, I want to display only one row (“ONE ROW PER MATCH”) with some measures for it. Of course, I must access to the point I’m interested in: the x-axis date and y-axis value for it. I can reference points within the matching window and I use the pattern variables to reference them. The peak is the last row in the “GoingUp” primary pattern and last(GoingUp.dateRep) and last(GoingUp.cases) are my points:


  5   measures
  6    match_number() as "match#",
  7    last(GoingUp.dateRep) as "Peak date",
  8    last(GoingUp.cases) as "Peak cases"
  9   one row per match

Those measures are accessible in the SELECT clause of my SQL statement. I added the match_number() to identify the points.

Here is the final query, with the partition, measures, pattern and define clauses within the MATCH_RECOGNIZE():


select countriesandterritories "Country","Peak date","Peak cases","match#"
from covid
match_recognize (
 partition by continentexp, countriesandterritories order by daterep
 measures
  match_number() as "match#",
  last(GoingUp.dateRep) as "Peak date",
  last(GoingUp.cases) as "Peak cases"
 one row per match
 pattern (GoingUp+ GoingDown+)
 define
  GoingUp as ( GoingUp.cases > prev(GoingUp.cases) ),
  GoingDown as ( GoingDown.cases < prev(GoingDown.cases))
)
where countriesandterritories='Switzerland';

The full syntax can have more and of course all is documented: https://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG8982

Debug mode

In order to understand how it works (and debug) we can display “all rows” (ALL ROWS PER MATCH instead of ONE ROW PER MATCH in line 9), and add the row columns (DATEREP and CASES in line 1) and, in addition to the match_number() I have added the classifier() measure:


  1  select countriesandterritories "Country","Peak date","Peak cases","match#",daterep,cases,"classifier"
  2  from covid
  3  match_recognize (
  4   partition by continentexp, countriesandterritories order by daterep
  5   measures
  6    match_number() as "match#", classifier() as "classifier",
  7    last(GoingUp.dateRep) as "Peak date",
  8    last(GoingUp.cases) as "Peak cases"
  9   all rows per match
 10   pattern (GoingUp+ GoingDown+)
 11   define
 12    GoingUp as ( GoingUp.cases > prev(GoingUp.cases) ),
 13    GoingDown as ( GoingDown.cases < prev(GoingDown.cases))
 14  )
 15* where countriesandterritories='Switzerland';

“all rows per match” shows all rows where pattern matching is tested, classifier() shows which primary pattern is matched.

Here are the rows around the 10th match. You must keep in mind that rows are processed in order and for each row, it looks ahead to recognize a pattern.


       Country    Peak date    Peak cases    match#      DATEREP    CASES    classifier
______________ ____________ _____________ _________ ____________ ________ _____________
...
Switzerland    24-MAR-20             1044         9 24-MAR-20        1044 GOINGUP
Switzerland    24-MAR-20             1044         9 25-MAR-20         774 GOINGDOWN
Switzerland    26-MAR-20              925        10 26-MAR-20         925 GOINGUP
Switzerland    27-MAR-20             1000        10 27-MAR-20        1000 GOINGUP
Switzerland    28-MAR-20             1390        10 28-MAR-20        1390 GOINGUP
Switzerland    28-MAR-20             1390        10 29-MAR-20        1048 GOINGDOWN
Switzerland    30-MAR-20             1122        11 30-MAR-20        1122 GOINGUP
Switzerland    31-MAR-20             1138        11 31-MAR-20        1138 GOINGUP              
Switzerland    31-MAR-20             1138        11 01-APR-20         696 GOINGDOWN  
Switzerland    02-APR-20              962        12 02-APR-20         962 GOINGUP
Switzerland    03-APR-20             1124        12 03-APR-20        1124 GOINGUP
Switzerland    03-APR-20             1124        12 04-APR-20        1033 GOINGDOWN

You see here how we came to output the 10th matched (28-MAR-20 1390 cases). After the peak of 24-MAR-20 we were going down the next day 25-MAR-20 (look at the graph). This was included in the 10th match because of regular expression “GoingDown+”. Then up 26-MAR-2020 to 28-MAR-20, which matches GoingUp+ followed by a “GoingDown” on 29-MAR-20 which means that a 11th match has been recognized. It continues for all “GoingDown+” but there’s only one here as the next one is a higher value: 1122 > 1048 so the 11th match is closed here on 29-MAR-20. This is where the ONE ROW PER MATCH is returned, when processing the row from 29-MAR-20, with the values from the last row classified as GOINGUP, and defined in the measures, which are 28-MAR-20 and 1390. And then the pattern matching continues from this row and a GoingUp has been detected…

If you want to go further, there are good examples from Lucas Jellama: https://technology.amis.nl/?s=match_recognize
And about its implementation in SQL engines, read Markus Winand https://modern-sql.com/feature/match_recognize

And I’ll probably have more blog posts here in this series about recent features interesting for BI and DWH…

Cet article Oracle 12c – peak detection with MATCH_RECOGNIZE est apparu en premier sur Blog dbi services.

What is a serverless database?

Fri, 2020-06-05 15:44
By Franck Pachot

.
After reading the https://cloudwars.co/oracle/oracle-deal-8×8-larry-ellison-picks-amazons-pocket-again/ paper, I am writing some thoughts about how a database can be serverless and elastic. Of course, a database needs a server to process its data. Serverless doesn’t mean that there are no servers.

Serverless as not waiting for server provisioning

The first idea of “serverless” is about provisioning. In the past when a developer required a new database to start a new project she had to wait that a server is installed. In 1996 my first development on Oracle Database started like this: we asked Sun for a server and OS and asked Oracle for the database software, all for free for a few months, in order to start our prototype. Today this would be a Cloud Free Tier access. At that time we had to wait to receive, unbox, and install all this. I learned a lot there about Installing an OS, configuring the network, setting up disk mirroring… This was an awesome experience for a junior starting in IT. Interestingly, I think that today a junior can learn the same concepts with a Cloud Foundation training and certification. This has not really changed except the unboxing and cabling. The big difference is that today we do not have to wait weeks for it and can setup the same infrastructure in 10 minutes.

That was my first DevOps experience: we wanted to develop our application without waiting for the IT department. But it was not serverless at all.

A few years later I was starting a new datawarehouse for a mobile telco in Africa. Again, weeks to months were required to order and install a server for it. And we didn’t wait. We started the first version of the datawarehouse on a spare PC we had. This was maybe my first serverless experience: the server provisioning is out of the critical path in the project planning. Of course, a PC is not a server and reliability and performance were not there. But we were lucky and when the server arrived we already had good feedback from this first version.

We need serverless, but we need real servers behind it. Today, this is possible: you don’t need to wait and you can provision a new database in the public or private cloud, or simply on a VM, without waiting. And all security, reliability and performance are there. With Oracle, it is a bit more difficult if you can’t do it in their public cloud because licensing do not count vCPUs and you often need specific hardware for it like in the old days. Appliances like ODA can help. Public Cloud or Cloud@Customer definitely helps.

Serverless as not taking responsibility for server administration

Serverless is not only about running on virtual servers with easy provisioning. If you are serverless, you don’t want to manage those virtual machines. You start and connect to a compute instance. You define its shape (CPU, RAM) but you don’t want to know where it runs physically. Of course, you want to define the region for legal, performance or cost reasons, but not which data center, which rack,… That’s the second step of serverless: you don’t manage the physical servers. In Oracle Cloud, you run a Compute Instance where you can install a database. In AWS this is an EC2 instance where you can install a database.

But, even if you don’t own the responsibility of the servers, this is not yet “serverless”. Because you pay for them. If your CFO still sees a bill for compute instance, you are not serverless.

Serverless as not paying for the server

AWS has a true serverless and elastic database offer: Amazon Aurora Serverless. You don’t have to start or stop the servers. This is done automatically when you connect. More activity adds more servers. No connection stops it. And you pay only for what the application is using. You don’t pay for the database servers running. You really pay for what the application is using.

Azure has also a Serverless SQL Server: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless

Those are, as far as I know, the only true serverless databases yet. If we need to stop and start the compute services ourselves, even with some level of auto-scaling, we can call that on-demand but not serverless.

All AWS RDS services including Aurora can be started and stopped on demand. They can scale up or down with minimal downtime, especially in Multi-AZ because the standby can be scaled and activated. Redshift cannot be stopped because it uses local storage. But you can take a snapshot and terminate the instance, and restore it later.

On Oracle side, the Autonomous Database can be stopped and started. Then again, we can say that we don’t pay when we don’t use the database but cannot say that we don’t pay when we don’t use the application. Because the database is up even if the application is not used. However, you can scale without the need to stop and start. And there’s also some level of auto-scaling where the additional application usage is really billed on CPU usage metrics: you pay for n OCPUs when the ATP or ADB is up and you can use up to n*3 sessions on CPU, with true serverless billing for what is above the provisioned OCPUs. Maybe the future will go further. The technology allows it: multitenant allows PDB level CPU caging where the capacity can be changed online (setting CPU_COUNT) and AWR gathers the CPU load with many metrics that can be used for billing.

Serverless

The name is funny because serverless programs run on servers. And the crush for running without servers is paradoxical. When I started programming, it was on very small computers (ZX-81, Apple //e, IBM PC-XT) and I was really proud when I started to do real stuff running on real servers, with a schema on *the* company database. Actually, what is called serverless today is, in my opinion, showing the full power of servers: don’t need to buy a computer for a project but use some mutualized compute power.

The cloud wars use strange marketing terms, but really good technology and concepts are coming.

Cet article What is a serverless database? est apparu en premier sur Blog dbi services.

DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables

Fri, 2020-06-05 14:51

A couple of weeks ago I upgraded an Oracle Standard Edition 2 test database from 12.1.0.2 to 12.2.0.1 (with the April 2020 Patch Bundle) on Windows. Recently I upgraded the production database. Both upgrades were done with the Database Upgrade Assistant DBUA. I didn’t use AUTOUPGRADE because I had to upgrade only 1 database and the DBUA handles everything for me (including changing the necessary Windows services and update the timezone file).

Both upgrades did hang at the finalizing phase of the components upgrade.

So I checked what the upgrade process is waiting for in the DB:


SQL> select sid, sql_id, event,p1,p2,p3 from v$session 
   2 where status='ACTIVE' and type='USER' and sid not in 
   3 (select sid from v$mystat);

       SID SQL_ID        EVENT                                                 P1         P2         P3
---------- ------------- -------------------------------------------------- ----- ---------- ----------
      1142 fgus25bx1md8q Streams AQ: waiting for messages in the queue      17409 1.4072E+14 2147483647

SQL> set long 400000 longchunksize 200
SQL> select sql_fulltext from v$sqlarea where sql_id='fgus25bx1md8q';

SQL_FULLTEXT
---------------------------------------------------------------------------------
DECLARE
        cursor table_name_cursor  is
                select  x.name table_name
                from sys.x$krvxdta x
                where bitand(x.flags, 12) != 0;
        filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
        obj_lst    DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
        ind number := 1;
BEGIN
   for rec in table_name_cursor loop
      begin
        filter_lst.extend(1);
        filter_lst(ind).ownname := 'SYSTEM';
        filter_lst(ind).objname := 'LOGMNR_'|| rec.table_name||'';
        ind := ind + 1;
      end;
   end loop;
   DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SYSTEM', objlist=>obj_lst, obj_filter_list=>filter_lst);
END;

So obviously the upgrade process tried to gather stats on LOGMNR-tables owned by SYSTEM and waits for messages in the scheduler queue SCHEDULER$_EVENT_QUEUE (Object ID 17409). I.e. this is something similar as documented in MOS Note 1559487.1.

The upgrade was stuck at this point. So what to do?

Fortunately I remembered a blog about DBUA being restartable in 12.2. from Mike Dietrich:

Restarting a failed Database Upgrade with DBUA 12.2

So I killed the waiting session:


SQL> select serial# from v$session where sid=1142;

   SERIAL#
----------
     59722

SQL> alter system kill session '1142,59722';

System altered.

Then I let the DBUA run into tons of errors and let it finish his work. To restart it I just clicked on “Retry” in the GUI. After some time DBUA went into an error again. I quickly checked the log-files and clicked again on “Retry”. That time it went through without issues. Checking the log-files and the result of the upgrade showed all components migrated correctly.

So in summary: A failed upgrade (crashed or hanging) with DBUA is not such a bad thing anymore as it was before 12.2. You can just let DBUA (or AUTOUPGRADE) retry its work. Of course, usually you have to fix the reason for the failure before restarting/retrying.

REMARK: See Mike Dietrich’s Blog about resumability and restartability of Autoupgrade here:

Troubleshooting, Restoring and Restarting AutoUpgrade

Cet article DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables est apparu en premier sur Blog dbi services.

Pages