Yann Neuhaus

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

Alfresco – Unable to see the content of folders with ‘+’ in the name

Wed, 2017-08-16 15:23

As you might now if you are following our blogs (see this one for example), we are using Alfresco Community Edition internally for some years now and we also have a few customers using it. Today, I will present you a small issue I faced with Alfresco which wasn’t able to display the content of a few – very specific – folders… As you will see below, it was actually not related to Alfresco but that’s a good example.

 

For some background on this issue, an end-user contacted me, saying that he wasn’t able to see the content of three folders in which he was sure there were at least some PDFs. So I checked and yes, even as an Alfresco Admin, I wasn’t able to see the content of this folder using Share. The only common point I could see with these three folders is that they all contained a “+” in their name. I recently upgraded and migrated this Alfresco environment so I was kind of sure this was somehow linked (since the folders existed and were working before the upgrade+migration). For this blog, I will use a test folder named “Test+Folder” and I’m using an Apache HTTPD as a front end. I put this folder under the Shared folder:

//alfresco_server_01/share/page/context/shared/sharedfilesURL: https://alfresco_server_01/share/page/context/shared/sharedfiles

 

Clicking on this folder’s name from the central screen or from the browsertree on the left side will result in the following screen:

Folder2URL: https://alfresco_server_01/share/page/context/shared/sharedfiles#filter=path|%2FTest%2BFolder|&page=1

 

As you can see above, the URL accessed is the correct one, it contains the “/Test+Folder” path so that’s correct (%2F is “/” and %2B is “+”). However, the screen is empty, just like if this path would not exist and on the breadcrumb, it is shown “Shared Files” while it should be “Shared Files > Test+Folder”. So in summary, the Share UI isn’t able to display the content of this folder. For testing purpose, I accessed this folder using WebDAV and AOS and these clients were able to see the content of the folder but not Share. Like I mentioned above, this Alfresco environment is using Apache HTTPD as a front-end and then mod_jk for the communication with the Tomcat. You can take a look at this blog or the official documentation for more information on this setup.

 

Since other clients were working properly, I tried to access Alfresco Share without going through the front-end (so accessing Tomcat directly) in order to ensure that the issue isn’t with Alfresco itself. By doing so, I was able to see the PDFs. If the issue isn’t with Alfresco, then it should be with the front-end and in particular with the mod_rewrite and mod_jk, in this case. The idea here is to check what are doing these two mods and then compare the outcome with the initial request and what is being transferred to the Tomcat using the access logs.

 

While doing this analysis, I found out that the mod_jk was most probably the root cause of this issue. When mod_jk is doing its job, it will decode the URL’s encoded characters like %2F for “/”, like %2B for “+”, like %20 for a space, aso… Then once the rules have been processed, it will, by default, re-encode these special characters before transmitting the request to Tomcat. However in the Tomcat access logs, it appeared that the other special characters were indeed present in their encoded format but it wasn’t the case for the “+” sign which was shown like that (so no %2B anymore).

This is an example (it’s not the real requests in the background but this is the URL on a Web browser so it gives a good example):

  • URL accessed:                    https://alfresco_server_01/share/page/context/shared/sharedfiles#filter=path|%2FTest%2BFolder|&page=1
  • URL decoded by mod_jk:  https://alfresco_server_01/share/page/context/shared/sharedfiles#filter=path|/Test+Folder|&page=1
  • URL sent to Tomcat:          https://alfresco_server_01/share/page/context/shared/sharedfiles#filter=path|%2FTest+Folder|&page=1

 

For some reasons (it looks like a bug), the mod_jk does not re-encode the “+” sign and this prevents the Tomcat in the back-end to complete the request properly. The behavior of mod_jk related to the URIs is managed using the JkOptions. This property can have the following values regarding the Forwarded URI:

  • JkOptions     +ForwardURIProxy
    • Default value in version > mod_jk 1.2.23
    • The forwarded URI will be partially re-encoded after processing inside Apache and before forwarding to Tomcat
  • JkOptions     +ForwardURICompatUnparsed
    • Default value in version = mod_jk 1.2.23
    • The forwarded URI will be unparsed so no decoding nor re-encoding
  • JkOptions     +ForwardURICompat
    • Default value in version < mod_jk 1.2.23
    • The forwarded URI will be decoded by Apache
  • JkOptions     +ForwardURIEscaped
    • The forwarded URI will be the encoded form of the URI used by ForwardURICompat

 

I’m using a fairly recent version of mod_jk on this Alfresco environment so the default value is “ForwardURIProxy”. Therefore on the paper, it should work properly since URIs will be decoded and re-encoded… However we saw above that this is working but not for the “+” sign which is not re-encoded.

 

To workaround this issue, I just updated the JkOptions to have “JkOptions     +ForwardURICompatUnparsed” in my Apache HTTPD configuration and after a reload of the conf, I was able to access the content of the folder:

Folder3URL: https://alfresco_server_01/share/page/context/shared/sharedfiles#filter=path|%2FTest%2BFolder|&page=1

 

Please note that “ForwardURICompatUnparsed” will always forward the original request URI, so rewriting URIs with mod_rewrite might not work properly, it all depends how you configured the rewrite rules and what you need to do with it. Honestly, I don’t think this issue is linked to all mod_jk versions above 1.2.23 since I was using a version 1.2.40 on RedHat before and I never noticed such issue so this might be linked to a specific mod_jk version on a specific OS (Ubuntu?). So basically if you don’t have this issue, I would suggest you to use the default JkOptions.

 

 

Cet article Alfresco – Unable to see the content of folders with ‘+’ in the name est apparu en premier sur Blog dbi services.

Replicating specific tables in PostgreSQL 10 Beta with mimeo

Tue, 2017-08-15 11:31

In this blog I am going to test the extension mimeo with PostgreSQL 10 beta. Mimeo is a replication extension for copying specific tables in one of several specialized ways from any number of source databases to a destination database where mimeo is installed.
In our configuration we are going to replicate data on a same server but between 2 clusters running on different ports. But it’s same for different servers. The pg_hba.conf should be configured to allow remote connection.
Source
Hostname: pgservertools.localdomain (192.168.56.30)
Database: prima (port 5432)
Target
Hostname: pgservertools.localdomain (192.168.56.30)
Database: repl (port 5433)
The first thing is to install the extension on the destination server. For this we will use the command git to clone the extension directory on the server.
[root@pgservertools ~]# yum install perl-Git.noarch
[root@pgservertools ~]# git clone git://github.com/omniti-labs/mimeo.git
Cloning into 'mimeo'...
remote: Counting objects: 1720, done.
remote: Total 1720 (delta 0), reused 0 (delta 0), pack-reused 1720
Receiving objects: 100% (1720/1720), 1.24 MiB | 429.00 KiB/s, done.
Resolving deltas: 100% (1094/1094), done.
[root@pgservertools ~]#

Then in the mimeo directory let’s run following commands

[root@pgservertools mimeo]# make
[root@pgservertools mimeo]# make install

If there is no error, we can create our two databases. The source database will be named prima and the target will be named repl.

[postgres@pgservertools postgres]$ psql
psql (10beta2)
Type "help" for help.
.
postgres=# show port;
port
------
5432
(1 row)
.
postgres=# create database prima;
CREATE DATABASE
postgres=#


postgres=# show port;
port
------
5433
(1 row)
.
postgres=# create database repl;
CREATE DATABASE
postgres=#

Now we have to install the extension mimeo in the destination database repl.
The extension mimeo requires the extension dblink. If this extension is not present, an error will be raised

repl=# create schema mimeo;
CREATE SCHEMA
.
repl=# create extension mimeo schema mimeo;
ERROR: required extension "dblink" is not installed
HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.
repl=#

The extension dblink should be already present with the standard installation. This can be verified by listing files the extension directory.

[root@pgservertools extension]# pwd
/usr/pgsql-10/share/extension
.
[root@pgservertools extension]# ls -l dblink*
-rw-r--r--. 1 root root 419 Jul 13 12:15 dblink--1.0--1.1.sql
-rw-r--r--. 1 root root 2832 Jul 13 12:15 dblink--1.1--1.2.sql
-rw-r--r--. 1 root root 6645 Jul 13 12:15 dblink--1.2.sql
-rw-r--r--. 1 root root 170 Jul 13 12:15 dblink.control
-rw-r--r--. 1 root root 2863 Jul 13 12:15 dblink--unpackaged--1.0.sql
[root@pgservertools extension]#

So rexecuting the instruction with the cascade option will install the extension dblink.

repl=# create extension mimeo schema mimeo cascade;
NOTICE: installing required extension "dblink"
CREATE EXTENSION
repl=#

On the target database let’s create a user mimeo we will use for the replication and let’s give him all required privileges. Superuser is not needed by will also work.

repl=# create user mimeo password 'root';
CREATE ROLE
repl=# GRANT USAGE ON SCHEMA mimeo TO mimeo;
GRANT
repl=# GRANT USAGE ON SCHEMA public TO mimeo;
GRANT
repl=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA mimeo TO mimeo;
GRANT
repl=#

On the source database let’s create same user on the source and give him required privileges

prima=# create user mimeo password 'root';
CREATE ROLE
prima=# CREATE SCHEMA mimeo;
CREATE SCHEMA
prima=# ALTER SCHEMA mimeo OWNER TO mimeo;
ALTER SCHEMA
prima=#

Every source database needs to have its connection information stored in mimeo’s dblink_mapping_mimeo table on the destination database. You can have as many source databases as you need, which makes creating a central replication destination for many master databases easy. All data is pulled by the destination database, never pushed by the source.

repl=# INSERT INTO mimeo.dblink_mapping_mimeo (data_source, username, pwd)
VALUES ('host=192.168.56.30 port=5432 dbname=prima', 'mimeo', 'root');
INSERT 0 1
repl=#

On the source let’s create table to be replicated and insert some data

prima=# create table article(idart int primary key, name varchar(20));
CREATE TABLE


prima=# table article;
idart | name
-------+-------
1 | Paper
2 | Food
(2 rows)
prima=#

Grant required privilege to mimeo

prima=# grant select on article to mimeo;
GRANT
prima=# grant trigger on article to mimeo;
GRANT
prima=#

Now we are ready to start the replication. We have three methods of replication:
-Snapshot replication
-Incremental replication
-DML replication
We will discuss only for snapshot and DML methods. Indeed the incremental method can replicate only inserted and updated data. It will not replicate any deleted data. See the documentation here

Snapshot Replication
This method is the only one to replicate data and structure change (add column….)
To initialize the table we use the function snapshot_maker (as we are using snapshot replication) and we pass as arguments the table to be replicated and the id of the dblink we want to use.

repl=# select * from mimeo.dblink_mapping_mimeo ;
data_source_id | data_source | username | pwd
----------------+-------------------------------------------+----------+------
1 | host=192.168.56.30 port=5432 dbname=prima | mimeo | root

So following command is used to initialize the table

repl=# SELECT mimeo.snapshot_maker('public.article', 1);
NOTICE: attempting first snapshot
NOTICE: attempting second snapshot
NOTICE: Done
snapshot_maker
----------------
.
(1 row)
repl=#

And we can easily verify that the two tables are synchronized.

repl=# table article;
idart | name
-------+-------
1 | Paper
2 | Food
(2 rows)
repl=#

Now let’s insert new data in the source table and let’s see how to refresh the target table.

prima=# table article;
idart | name
-------+-------
1 | Paper
2 | Food
3 | Oil
4 | Books

On the target database we just have to use the refresh_snap function

repl=# SELECT mimeo.refresh_snap('public.article');
refresh_snap
--------------
.
(1 row)

And we see that the source table was updated.

repl=# table article;
idart | name
-------+-------
1 | Paper
2 | Food
3 | Oil
4 | Books
(4 rows)
repl=#

A refresh can be scheduled using crontab for example every two minutes in my case

[postgres@pgservertools ~]$ crontab -l
*/2 * * * * psql -p 5433 -d repl -c "SELECT mimeo.refresh_snap('public.article')";
[postgres@pgservertools ~]$

DML Replication
The snapshot method is easier to setup, but it is not recommended for large table as
a table setup with this method will have the entire contents refreshed every time it is run.
So for large tables DML replication is recommended.
Let’s create a table customers on the source

prima=# create table customers(idcust int primary key, name varchar(30));
CREATE TABLE
. ^
prima=# insert into customers values(1,'Dbi');
INSERT 0 1
prima=# insert into customers values(2,'XZZ');
INSERT 0 1
.
prima=# table customers
prima-# ;
idcust | name
--------+------
1 | Dbi
2 | XZZ
(2 rows)
prima=#

And let’s grant required privileges to mimeo on customers

prima=# grant select on customers to mimeo;
GRANT
prima=# grant trigger on customers to mimeo;
GRANT
prima=#

On the target we use the function dml_maker to replicate data. We can see that we can even change the name of the destination table.

repl=# SELECT mimeo.dml_maker('public.customers', 1, p_dest_table := 'public.customers_repl');
NOTICE: Creating objects on source database (function, trigger & queue table)...
NOTICE: Pulling data from source...
dml_maker
-----------
.
(1 row)

We can verify that the table customers_repl is created

repl=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | article | view | postgres
public | article_snap1 | table | postgres
public | article_snap2 | table | postgres
public | customers_repl | table | postgres
(4 rows)

And that data are replicated

repl=# select * from customers_repl ;
idcust | name
--------+------
1 | Dbi
2 | XZZ
(2 rows)

Now let’s insert again new data in the source table and let’s see how to refresh the target

prima=# insert into customers values(3,'Linux');
INSERT 0 1
prima=# insert into customers values(4,'Unix');
INSERT 0 1
.
prima=# table customers
;
idcust | name
--------+-------
1 | Dbi
2 | XZZ
3 | Linux
4 | Unix
(4 rows)

On the target database we have to run the refresh_dml function

repl=# SELECT mimeo.refresh_dml('public.customers_repl');
refresh_dml
-------------
.
(1 row)


repl=# table customers_repl;
idcust | name
--------+-------
1 | Dbi
2 | XZZ
3 | Linux
4 | Unix
(4 rows)
repl=#

Like the snapshot method a crontab can be scheduled .
Conclusion
In a previous blog, we saw that logical replication is now supported on PostgreSQL 10. But the extension mimeo can still be used.

 

Cet article Replicating specific tables in PostgreSQL 10 Beta with mimeo est apparu en premier sur Blog dbi services.

ODA X6 Small Medium Large and High Availability

Mon, 2017-08-14 10:46

There are 4 models of Oracle Database Appliance with the new ODA X6 which is for the moment the latest ODA hardware version. One is similar to the previous X5-2 one, and 3 smaller ones known as ODA Lite. They are 1 year old already, here is a small recap of the differences and links to more detail.

System

The ODA X6 are composed with Oracle Server X6-2:

  • ODA X6-2S has one server
  • ODA X6-2M has one server
  • ODA X6-2L has one server
  • ODA X6-2HA is a cluster of two servers with one shared storage shelf (DE3-24C). It can be expanded with one or two additional storage shelf.

Those servers have 2 USB 2.0 ports accessible (2 in front, 2 in back) and 2 internal USB ports.
They have one serial console (SER MGT/RJ-45 connector) port. And One VGA DB-15 connector, easier to plug before you put the cable rails. Resolution: 1,600 x1,200×16 MB @ 60 Hz. Note that the resolution is 1,024 x 768 when viewed remotely via Oracle ILOM).
Each server has 2 redundant power supplies (hot swappable).

Rack Unit
  • ODA X6-2S is 2U
  • ODA X6-2M is 2U
  • ODA X6-2L is 4U
  • ODA X6-2HA is 6U (up to 10U with storage expansions)
CPU

The processor of X6 servers is an Intel 2.2GHz 10-Core Xeon E5-2630 v4, 85W
Cache:
Level 1: 32 KB instruction and 32 KB data L1 cache per core
Level 2: 256 KB shared data and instruction L2 cache per core
Level 3: 25 MB shared inclusive L3 cache per processor

  • ODA X6-2S has 1 processor: 10 cores
  • ODA X6-2M has 2 processors: 20 cores
  • ODA X6-2L has 2 processors: 20 cores
  • ODA X6-2 HA has two servers with 2 processors each: 40 cores

The core factor of the processor is 0.5 and you can license the full capacity for Enterprise Edition: 5 licenses for one ODA X6-2S, 10 licenses for one ODA X6-2M or 2L, 20 licenses for ODA X6-2 HA. You can also run NUP licenses with a minimum of 125 NUP for one ODA X6-2S, 250 NUP for one ODA X6-2M or 2L, 500 NUP for ODA X6-2 HA.
Of course, you can do Capacity on Demand on all models, so the minimum processor license is 1 license for ODA Lite (when 2 cores only are enabled) or 2 licenses for ODA HA because the nodes must by symmetric (except if you run only one node). In NUP, it means 25 NUP minimum for ODA Lite or 50 NUP for ODA HA.

On ODA Lite (2S, 2M, 2L) you can choose to run Standard Edition. Then you count the number of processors: 1 license for ODA X6-2S, 2 licenses for one ODA X6-2M or 2L. When in Standard Edition 2 you can license in NUP with the minimum of 10 NUP per server.

RAM

The X6 servers have 14 DIMM slots and are partially populated with 32GB DIMMs (DDR4-2400).

  • ODA X6-2S has 4 slots populated: 128GB RAM. It can be expanded with additional 8 DIMMs for 384GB
  • ODA X6-2M has 8 slots populated: 256GB RAM. It can be expanded with additional 16 DIMMs for 512GB or 768GB which populates all the slots.
  • ODA X6-2L has 8 slots populated: 256GB RAM. It can be expanded with additional 16 DIMMs for 512GB or 768GB which populates all the slots.
  • ODA X6-2 HA has 8 slots populated per server: 256GB RAM. It can be expanded with additional 16 DIMMs for 512GB or 768GB which populates all the slots.
Network

ODA Lite:
2x 10GbE SFP+ (fiber) and 2x 10GBase-T (copper) ports for ODAX6-2S (4x 10GBase-T for 2M and 2L)
ODA HA:
InfiniBand interconnect, 4x 10GBase-T (copper) ports (bonded to provide two public interfaces) or optional 2x 10GbE SFP+ (fiber) per server and one pair of 10GBase-T will be used for interconnect.

Storage

The servers have two 2.5″ 480GB SAS-3 SDDs disks in front, mirrored for the Operating System and the Oracle Database Software

ODA X6-2S and 2M have additional two 2.5″ 3.2TB NVMe PCIe 3.0 SSD disks for DATA and RECO disk groups (6.4TB raw capacity, 2.4TB double-mirrored, 1.6TB triple-mirrored)
They are expandable to 12.8 TB with two additional disks.

ODA X6-2L has six 3.5-inch disk bays are populated with 3.2TB SSDs (19.2TB raw capacity, 9.6TB double-mirrored, 6.4TB triple-mirrored)
They are expandable to 28.8 TB with two additional disks.

ODA X6-HA has a storage shelf, as previous X5 model (similar but not compatible), but with SSD and lower capacity for DATA: 10x SAS SSD flash 1.2TB (1.6 formatted to 1.2, over-provisioned for write performance because of garbage collection) which means 12 TB (12TB raw capacity, 6TB double-mirrored, 4TB triple-mirrored) expandable to 24 TB in the same chassis. You can add another storage shelf with additional 24TB.

Note that latest ODA X5 had 16x 8TB disks, so 128TB so X6-HA has higher performance but decreased capacity.

Virtualization, High Availability

Virtualization with OVM is only for ODA X6-HA. Only one VM per node has the resources to run Oracle Databases. You can add additional ‘Guest VMs’ for your applications (not for databases as the I/O is optimal only in the ODA Base VM). You can define VLANs.

The ODA Lite (2S/2M/2L) cannot run applications on guest VMs as they are bare metal only. If you want to run applications on ODA Lite, you can now use KVM, but remember that you still need to license all activated cores for each product because KVM is only ‘soft partitioning’ for Oracle LMS.

High Availability (with RAC) is only for ODA X6-HA.
A standby database to another ODA is possible for all models: Data Guard when in Enterprise Edition, or manual standby (we recommend Dbvisit standby – see one of our customer case study http://www.dbvisit.com/customers/case-study-das-protection-juridique/) when in Standard Edition.

Price

According to the Price List (http://www.oracle.com/us/corporate/pricing/exadata-pricelist-070598.pdf)

  • ODA X6-2S costs USD 18000
  • ODA X6-2M costs USD 24000
  • ODA X6-2L costs USD 40000
  • ODA X6-2 HA costs USD 72000
Full specifications

The full specification is available in the Oracle System Handbook:

A simple presentation is in the 3D view:

 

Cet article ODA X6 Small Medium Large and High Availability est apparu en premier sur Blog dbi services.

WebLogic – Cannot register for disconnect events on local server

Sun, 2017-08-13 03:15

When working with WebLogic, there will probably be a moment when you will ask yourself: damn, what is wrong? Nowadays, software are so complex that it is kind of easy to introduce bugs/non-wanted behaviors in them… In this blog, I will present a small thing that just blew my mind when I faced it: I had a fully working WLS on which I executed a WLST script in order to configure the SSL Ciphers for the Admin Server as well as Managed Servers. After this, the WLS wasn’t able to start anymore but this had nothing to do with the WLST script since I was sure it didn’t contain any error and it did what I expected… To stay generic, to update the Ciphers of all Managed Servers/Domains of a single server and because we manage more than one hundred WebLogic Servers at this customer, an automated deployment was really necessary.

On all these WLS, we have a very few of them that contain two domains on the same server (with different IPs/DNS Aliases of course). To handle this case, we had to use a variable that point to the Admin Server in order to connect to this server and execute the WLST script against it. The name of the variable we defined is, of course, “ADMIN_URL”. This is the name used by WebLogic in the configuration files to point to the Admin Server. So what better name than this? Well actually, anything would have been better since this is what caused this issue…

 

So let’s demonstrate this. First, I’m just trying to start the Admin Server without the variable defined:

[weblogic@weblogic_server_01 ~]$ cd $DOMAIN_HOME/bin
[weblogic@weblogic_server_01 bin]$ 
[weblogic@weblogic_server_01 bin]$ ./startWebLogic.sh
.
.
JAVA Memory arguments: -Xms256m -Xmx512m -XX:MaxPermSize=256m
.
CLASSPATH=$ORACLE_HOME/wlserver/server/lib/jcmFIPS.jar:$ORACLE_HOME/wlserver/server/lib/sslj.jar:$ORACLE_HOME/wlserver/server/lib/cryptoj.jar::$JAVA_HOME/lib/tools.jar:$ORACLE_HOME/wlserver/server/lib/weblogic_sp.jar:$ORACLE_HOME/wlserver/server/lib/weblogic.jar:$ORACLE_HOME/wlserver/../oracle_common/modules/net.sf.antcontrib_1.1.0.0_1-0b3/lib/ant-contrib.jar:$ORACLE_HOME/wlserver/modules/features/oracle.wls.common.nodemanager_2.0.0.0.jar:$ORACLE_HOME/wlserver/../oracle_common/modules/com.oracle.cie.config-wls-online_8.1.0.0.jar:$ORACLE_HOME/wlserver/common/derby/lib/derbyclient.jar:$ORACLE_HOME/wlserver/common/derby/lib/derby.jar:$ORACLE_HOME/wlserver/server/lib/xqrl.jar:$DOMAIN_HOME/lib/LB.jar:$DOMAIN_HOME/lib/LBJNI.jar:
.
PATH=$ORACLE_HOME/wlserver/server/bin:$ORACLE_HOME/wlserver/../oracle_common/modules/org.apache.ant_1.9.2/bin:$JAVA_HOME/jre/bin:$JAVA_HOME/bin:$DOMAIN_HOME/D2/lockbox:$DOMAIN_HOME/D2/lockbox/lib/native/linux_gcc34_x64:$JAVA_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/weblogic/bin
.
***************************************************
* To start WebLogic Server, use a username and    *
* password assigned to an admin-level user. For   *
* server administration, use the WebLogic Server  *
* console at http://hostname:port/console         *
***************************************************
starting weblogic with Java version:
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)
Starting WLS with line:
$JAVA_HOME/bin/java -server -Xms256m -Xmx512m -XX:MaxPermSize=256m -Dweblogic.Name=AdminServer -Djava.security.policy=$ORACLE_HOME/wlserver/server/lib/weblogic.policy -Dweblogic.ProductionModeEnabled=true -Ddomain.home=$DOMAIN_HOME -Dweblogic.nodemanager.ServiceEnabled=true -Dweblogic.security.SSL.minimumProtocolVersion=TLSv1.2 -Dweblogic.security.disableNullCipher=true -Djava.security.egd=file:///dev/./urandom -Dweblogic.security.allowCryptoJDefaultJCEVerification=true -Dweblogic.nodemanager.ServiceEnabled=true -Djava.endorsed.dirs=$JAVA_HOME/jre/lib/endorsed:$ORACLE_HOME/wlserver/../oracle_common/modules/endorsed -da -Dwls.home=$ORACLE_HOME/wlserver/server -Dweblogic.home=$ORACLE_HOME/wlserver/server -Dweblogic.utils.cmm.lowertier.ServiceDisabled=true weblogic.Server
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
<Aug 5, 2017 1:37:30 PM UTC> <Info> <Security> <BEA-090906> <Changing the default Random Number Generator in RSA CryptoJ from ECDRBG128 to FIPS186PRNG. To disable this change, specify -Dweblogic.security.allowCryptoJDefaultPRNG=true.>
<Aug 5, 2017 1:37:30 PM UTC> <Notice> <WebLogicServer> <BEA-000395> <The following extensions directory contents added to the end of the classpath:
$DOMAIN_HOME/lib/LB.jar:$DOMAIN_HOME/lib/LBJNI.jar.>
<Aug 5, 2017 1:37:30 PM UTC> <Info> <WebLogicServer> <BEA-000377> <Starting WebLogic Server with Java HotSpot(TM) 64-Bit Server VM Version 25.102-b14 from Oracle Corporation.>
<Aug 5, 2017 1:37:31 PM UTC> <Info> <Management> <BEA-141107> <Version: WebLogic Server 12.1.3.0.0 Wed May 21 18:53:34 PDT 2014 1604337 >
<Aug 5, 2017 1:37:32 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STARTING.>
<Aug 5, 2017 1:37:32 PM UTC> <Info> <WorkManager> <BEA-002900> <Initializing self-tuning thread pool.>
<Aug 5, 2017 1:37:32 PM UTC> <Info> <WorkManager> <BEA-002942> <CMM memory level becomes 0. Setting standby thread pool size to 256.>
<Aug 5, 2017 1:37:33 PM UTC> <Notice> <Log Management> <BEA-170019> <The server log file $DOMAIN_HOME/servers/AdminServer/logs/AdminServer.log is opened. All server side log events will be written to this file.>
<Aug 5, 2017 1:37:34 PM UTC> <Notice> <Security> <BEA-090082> <Security initializing using security realm myrealm.>
<Aug 5, 2017 1:37:36 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STANDBY.>
<Aug 5, 2017 1:37:36 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STARTING.>
Aug 5, 2017 1:37:36 PM weblogic.wsee.WseeCoreMessages logWseeServiceStarting
INFO: The Wsee Service is starting
<Aug 5, 2017 1:37:37 PM UTC> <Warning> <Munger> <BEA-2156227> <The "META-INF/application.xml" deployment descriptor for the "consoleapp" module is in DTD format. Overrides from the deployment plan will NOT be applied since only deployment descriptors in XML Schema format are supported.>
<Aug 5, 2017 1:37:37 PM UTC> <Warning> <Munger> <BEA-2156227> <The "WEB-INF/weblogic.xml" deployment descriptor for the "consolehelp" module is in DTD format. Overrides from the deployment plan will NOT be applied since only deployment descriptors in XML Schema format are supported.>
<Aug 5, 2017 1:37:38 PM UTC> <Notice> <Security> <BEA-090171> <Loading the identity certificate and private key stored under the alias mycert from the JKS keystore file $DOMAIN_HOME/certs/identity.jks.>
<Aug 5, 2017 1:37:38 PM UTC> <Notice> <Security> <BEA-090169> <Loading trusted certificates from the JKS keystore file $DOMAIN_HOME/certs/trust.jks.>
<Aug 5, 2017 1:37:39 PM UTC> <Notice> <Log Management> <BEA-170027> <The server has successfully established a connection with the Domain level Diagnostic Service.>
<Aug 5, 2017 1:37:40 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to ADMIN.>
<Aug 5, 2017 1:37:40 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RESUMING.>
<Aug 5, 2017 1:37:40 PM UTC> <Notice> <Server> <BEA-002613> <Channel "DefaultSecure" is now listening on weblogic_server_01:8443 for protocols iiops, t3s, ldaps, https.>
<Aug 5, 2017 1:37:40 PM UTC> <Notice> <WebLogicServer> <BEA-000329> <Started the WebLogic Server Administration Server "AdminServer" for domain "DOMAIN" running in production mode.>
<Aug 5, 2017 1:37:40 PM UTC> <Notice> <WebLogicServer> <BEA-000360> <The server started in RUNNING mode.>
<Aug 5, 2017 1:37:40 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING.>

^C
<Aug 5, 2017 1:37:55 PM UTC> <Notice> <WebLogicServer> <BEA-000388> <JVM called the WebLogic Server shutdown hook. The server will force shutdown now.>
<Aug 5, 2017 1:37:55 PM UTC> <Notice> <WebLogicServer> <BEA-000396> <Server shutdown has been requested by <WLS Kernel>.>
<Aug 5, 2017 1:37:55 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SUSPENDING.>
<Aug 5, 2017 1:37:55 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to ADMIN.>
<Aug 5, 2017 1:37:55 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SHUTTING_DOWN.>
<Aug 5, 2017 1:37:55 PM UTC> <Notice> <Server> <BEA-002607> <Channel "DefaultSecure", listening on weblogic_server_01:8443, was shut down.>
[weblogic@weblogic_server_01 bin]$

 

As you can see above, the Admin Server is starting properly. Once in RUNNING state, I just stopped it (CTRL+C) to continue the demo.

The next step is to find the current value of “ADMIN_URL”. This variable is used in the files stopWebLogic.sh, stopManagedWebLogic.sh and startManagedWebLogic.sh. To be able to stop these components, the address of the Admin Server is needed. The funny thing is that in two of these three files, it is using the “t3s” protocol and on the third one, it is using “https” (or t3/http if not in SSL-enabled). Once you have this value, you define your environment variable using this exact same value (manual setup or more automatic setup):

[weblogic@weblogic_server_01 bin]$ echo $ADMIN_URL

[weblogic@weblogic_server_01 bin]$
[weblogic@weblogic_server_01 bin]$
[weblogic@weblogic_server_01 bin]$ grep -E 'ADMIN_URL="[th]' *.sh
startManagedWebLogic.sh:	ADMIN_URL="https://weblogic_server_01:8443"
stopManagedWebLogic.sh:		ADMIN_URL="t3s://weblogic_server_01:8443"
stopWebLogic.sh:			ADMIN_URL="t3s://weblogic_server_01:8443"
[weblogic@weblogic_server_01 bin]$
[weblogic@weblogic_server_01 bin]$ export ADMIN_URL="https://weblogic_server_01:8443"
[weblogic@weblogic_server_01 bin]$ # or
[weblogic@weblogic_server_01 bin]$ export ADMIN_URL=`grep -E 'ADMIN_URL="[th]' stopWebLogic.sh | sed 's,ADMIN_URL="\([^"]*\)",\1,'`
[weblogic@weblogic_server_01 bin]$
[weblogic@weblogic_server_01 bin]$ echo $ADMIN_URL
t3s://weblogic_server_01:8443
[weblogic@weblogic_server_01 bin]$

 

At this point, I defined the ADMIN_URL variable using the T3S protocol. We can think that this wouldn’t affect the start of our domain. I mean we are just defining a variable that exist in the shell scripts of WebLogic with the exact same value… But note that this variable isn’t defined in the file “startWebLogic.sh”… Once this is done, we can reproduce the issue. For that, simply try to start the Admin Server again:

[weblogic@weblogic_server_01 bin]$ ./startWebLogic.sh
.
.
JAVA Memory arguments: -Xms256m -Xmx512m -XX:MaxPermSize=256m
.
CLASSPATH=$ORACLE_HOME/wlserver/server/lib/jcmFIPS.jar:$ORACLE_HOME/wlserver/server/lib/sslj.jar:$ORACLE_HOME/wlserver/server/lib/cryptoj.jar::$JAVA_HOME/lib/tools.jar:$ORACLE_HOME/wlserver/server/lib/weblogic_sp.jar:$ORACLE_HOME/wlserver/server/lib/weblogic.jar:$ORACLE_HOME/wlserver/../oracle_common/modules/net.sf.antcontrib_1.1.0.0_1-0b3/lib/ant-contrib.jar:$ORACLE_HOME/wlserver/modules/features/oracle.wls.common.nodemanager_2.0.0.0.jar:$ORACLE_HOME/wlserver/../oracle_common/modules/com.oracle.cie.config-wls-online_8.1.0.0.jar:$ORACLE_HOME/wlserver/common/derby/lib/derbyclient.jar:$ORACLE_HOME/wlserver/common/derby/lib/derby.jar:$ORACLE_HOME/wlserver/server/lib/xqrl.jar:$DOMAIN_HOME/lib/LB.jar:$DOMAIN_HOME/lib/LBJNI.jar:
.
PATH=$ORACLE_HOME/wlserver/server/bin:$ORACLE_HOME/wlserver/../oracle_common/modules/org.apache.ant_1.9.2/bin:$JAVA_HOME/jre/bin:$JAVA_HOME/bin:$DOMAIN_HOME/D2/lockbox:$DOMAIN_HOME/D2/lockbox/lib/native/linux_gcc34_x64:$JAVA_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/weblogic/bin
.
***************************************************
* To start WebLogic Server, use a username and *
* password assigned to an admin-level user. For *
* server administration, use the WebLogic Server *
* console at http://hostname:port/console *
***************************************************
starting weblogic with Java version:
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)
Starting WLS with line:
$JAVA_HOME/bin/java -server -Xms256m -Xmx512m -XX:MaxPermSize=256m -Dweblogic.Name=AdminServer -Djava.security.policy=$ORACLE_HOME/wlserver/server/lib/weblogic.policy -Dweblogic.ProductionModeEnabled=true -Ddomain.home=$DOMAIN_HOME -Dweblogic.nodemanager.ServiceEnabled=true -Dweblogic.security.SSL.minimumProtocolVersion=TLSv1.2 -Dweblogic.security.disableNullCipher=true -Djava.security.egd=file:///dev/./urandom -Dweblogic.security.allowCryptoJDefaultJCEVerification=true -Dweblogic.nodemanager.ServiceEnabled=true -Djava.endorsed.dirs=$JAVA_HOME/jre/lib/endorsed:$ORACLE_HOME/wlserver/../oracle_common/modules/endorsed -da -Dwls.home=$ORACLE_HOME/wlserver/server -Dweblogic.home=$ORACLE_HOME/wlserver/server -Dweblogic.management.server=t3s://weblogic_server_01:8443 -Dweblogic.utils.cmm.lowertier.ServiceDisabled=true weblogic.Server
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
<Aug 5, 2017 1:40:04 PM UTC> <Info> <Security> <BEA-090906> <Changing the default Random Number Generator in RSA CryptoJ from ECDRBG128 to FIPS186PRNG. To disable this change, specify -Dweblogic.security.allowCryptoJDefaultPRNG=true.>
<Aug 5, 2017 1:40:04 PM UTC> <Notice> <WebLogicServer> <BEA-000395> <The following extensions directory contents added to the end of the classpath:
$DOMAIN_HOME/lib/LB.jar:$DOMAIN_HOME/lib/LBJNI.jar.>
<Aug 5, 2017 1:40:04 PM UTC> <Info> <WebLogicServer> <BEA-000377> <Starting WebLogic Server with Java HotSpot(TM) 64-Bit Server VM Version 25.102-b14 from Oracle Corporation.>
<Aug 5, 2017 1:40:05 PM UTC> <Warning> <Security> <BEA-090924> <JSSE has been selected by default, since the SSLMBean is not available.>
<Aug 5, 2017 1:40:05 PM UTC> <Info> <Security> <BEA-090908> <Using the default WebLogic SSL Hostname Verifier implementation.>
<Aug 5, 2017 1:40:05 PM UTC> <Notice> <Security> <BEA-090169> <Loading trusted certificates from the jks keystore file $ORACLE_HOME/wlserver/server/lib/DemoTrust.jks.>
<Aug 5, 2017 1:40:06 PM UTC> <Notice> <Security> <BEA-090169> <Loading trusted certificates from the jks keystore file $JAVA_HOME/jre/lib/security/cacerts.>
<Aug 5, 2017 1:40:06 PM UTC> <Info> <Management> <BEA-141298> <Could not register with the Administration Server: java.rmi.RemoteException: [Deployer:149147]Could not reach the Administration Server through any of its URLs: "https://weblogic_server_01:8443".>
<Aug 5, 2017 1:40:06 PM UTC> <Info> <Management> <BEA-141107> <Version: WebLogic Server 12.1.3.0.0 Wed May 21 18:53:34 PDT 2014 1604337 >
<Aug 5, 2017 1:40:07 PM UTC> <Info> <Security> <BEA-090908> <Using the default WebLogic SSL Hostname Verifier implementation.>
<Aug 5, 2017 1:40:07 PM UTC> <Notice> <Security> <BEA-090169> <Loading trusted certificates from the jks keystore file $ORACLE_HOME/wlserver/server/lib/DemoTrust.jks.>
<Aug 5, 2017 1:40:07 PM UTC> <Notice> <Security> <BEA-090169> <Loading trusted certificates from the jks keystore file $JAVA_HOME/jre/lib/security/cacerts.>
<Aug 5, 2017 1:40:07 PM UTC> <Alert> <Management> <BEA-141151> <The Administration Server could not be reached at https://weblogic_server_01:8443.>
<Aug 5, 2017 1:40:07 PM UTC> <Info> <Configuration Management> <BEA-150018> <This server is being started in Managed Server independence mode in the absence of the Administration Server.>
<Aug 5, 2017 1:40:07 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STARTING.>
<Aug 5, 2017 1:40:07 PM UTC> <Info> <WorkManager> <BEA-002900> <Initializing self-tuning thread pool.>
<Aug 5, 2017 1:40:07 PM UTC> <Info> <WorkManager> <BEA-002942> <CMM memory level becomes 0. Setting standby thread pool size to 256.>
<Aug 5, 2017 1:40:07 PM UTC> <Notice> <Log Management> <BEA-170019> <The server log file $DOMAIN_HOME/servers/AdminServer/logs/AdminServer.log is opened. All server side log events will be written to this file.>
<Aug 5, 2017 1:40:09 PM UTC> <Notice> <Security> <BEA-090082> <Security initializing using security realm myrealm.>
<Aug 5, 2017 1:40:10 PM UTC> <Error> <Configuration Management> <BEA-150000> <An error occurred while establishing a connection back to the Adminstration Server t3s://weblogic_server_01:8443 during startup. Since bootstrap succeeded, check that t3s://weblogic_server_01:8443 uniquely identifies the Administration Server.
javax.naming.ConfigurationException: Cannot register for disconnect events on local server
    at weblogic.server.channels.RemoteChannelServiceImpl.registerInternal(RemoteChannelServiceImpl.java:234)
    at weblogic.server.channels.RemoteChannelServiceImpl.registerForever(RemoteChannelServiceImpl.java:190)
    at weblogic.protocol.ConnectMonitorFactory.registerForever(ConnectMonitorFactory.java:54)
    at weblogic.management.provider.MSIService.registerForReconnectToAdminServer(MSIService.java:188)
    at weblogic.management.mbeanservers.compatibility.internal.CompatibilityMBeanServerService.start(CompatibilityMBeanServerService.java:183)
    Truncated. see log file for complete stacktrace
>
<Aug 5, 2017 1:40:10 PM UTC> <Notice> <Security> <BEA-090171> <Loading the identity certificate and private key stored under the alias myacert from the JKS keystore file $DOMAIN_HOME/certs/identity.jks.>
<Aug 5, 2017 1:40:10 PM UTC> <Notice> <Security> <BEA-090169> <Loading trusted certificates from the JKS keystore file $DOMAIN_HOME/certs/trust.jks.>
<Aug 5, 2017 1:40:12 PM UTC> <Critical> <WebLogicServer> <BEA-000362> <Server failed. Reason:

    There are 1 nested errors:

javax.naming.ConfigurationException: Cannot register for disconnect events on local server
    at weblogic.server.channels.RemoteChannelServiceImpl.registerInternal(RemoteChannelServiceImpl.java:234)
    at weblogic.server.channels.RemoteChannelServiceImpl.registerForever(RemoteChannelServiceImpl.java:190)
    at weblogic.protocol.ConnectMonitorFactory.registerForever(ConnectMonitorFactory.java:54)
    at weblogic.management.provider.MSIService.registerForReconnectToAdminServer(MSIService.java:188)
    at weblogic.management.mbeanservers.compatibility.internal.CompatibilityMBeanServerService.start(CompatibilityMBeanServerService.java:183)
    at weblogic.server.AbstractServerService.postConstruct(AbstractServerService.java:78)
    at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.glassfish.hk2.utilities.reflection.ReflectionHelper.invoke(ReflectionHelper.java:1017)
    at org.jvnet.hk2.internal.ClazzCreator.postConstructMe(ClazzCreator.java:388)
    at org.jvnet.hk2.internal.ClazzCreator.create(ClazzCreator.java:430)
    at org.jvnet.hk2.internal.SystemDescriptor.create(SystemDescriptor.java:456)
    at org.glassfish.hk2.runlevel.internal.AsyncRunLevelContext.findOrCreate(AsyncRunLevelContext.java:225)
    at org.glassfish.hk2.runlevel.RunLevelContext.findOrCreate(RunLevelContext.java:82)
    at org.jvnet.hk2.internal.Utilities.createService(Utilities.java:2488)
    at org.jvnet.hk2.internal.ServiceHandleImpl.getService(ServiceHandleImpl.java:98)
    at org.jvnet.hk2.internal.ServiceHandleImpl.getService(ServiceHandleImpl.java:87)
    at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$QueueRunner.oneJob(CurrentTaskFuture.java:1162)
    at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$QueueRunner.run(CurrentTaskFuture.java:1147)
    at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$UpOneLevel.run(CurrentTaskFuture.java:753)
    at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:553)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:311)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:263)

>
<Aug 5, 2017 1:40:12 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FAILED.>
<Aug 5, 2017 1:40:12 PM UTC> <Error> <WebLogicServer> <BEA-000383> <A critical service failed. The server will shut itself down.>
<Aug 5, 2017 1:40:12 PM UTC> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SHUTTING_DOWN.>
[weblogic@weblogic_server_01 bin]$

 

So what is happening exactly that can cause this simple variable definition to prevent you to start your Admin Server? If you take a look at the script “startWebLogic.sh”, you will not see any use of this variable so you should be good, right? Well not really because this script is actually loading its environment by using the well-known “setDomainEnv.sh” (in the same folder). The interesting part is in this second file… If you are checking the usage of “ADMIN_URL” in this setDomainEnv, you will see an “if-then-else” section:

[weblogic@weblogic_server_01 bin]$ grep -C2 "ADMIN_URL" setDomainEnv.sh
# Clustering support (edit for your cluster!)

if [ "${ADMIN_URL}" = "" ] ; then
        # The then part of this block is telling us we are either starting an admin server OR we are non-clustered
        CLUSTER_PROPERTIES=""
        export CLUSTER_PROPERTIES
else
        CLUSTER_PROPERTIES="-Dweblogic.management.server=${ADMIN_URL}"
        export CLUSTER_PROPERTIES
fi
[weblogic@weblogic_server_01 bin]$

 

With this small portion of code, you can actually understand the issue:

  • if the variable “ADMIN_URL” isn’t defined when loading the file setDomainEnv.sh, then WebLogic will suppose that you are starting an Admin Server or that you are in a non-clustered environment.
  • if the variable “ADMIN_URL” is defined when loading the file setDomainEnv.sh, then WebLogic will suppose that you are starting a clustered environment

 

Therefore when defining the ADMIN_URL in the environment, we are actually – unintentionally – saying to WebLogic that this is a cluster and that the Management Server can be found at $ADMIN_URL… But this URL is the local Admin Server which we are trying to start. Thus the failure to start…

If you carefully read the logs above, you could actually saw this small difference… When reproducing the error, if you take a look at the end of the line 21, you will see that the JVM parameter “-Dweblogic.management.server=t3s://weblogic_server_01:8443″ is present and that’s the reason of this issue.

 

 

Cet article WebLogic – Cannot register for disconnect events on local server est apparu en premier sur Blog dbi services.

OUD 11.1.2.3 – How to recover a lost entry in a replicated OUD environment

Thu, 2017-08-10 04:57

By accident, it could happen that someone dropped an OUD entry in your replicated environment and of course, the entry is deleted on all replicas as well. And besides that, you got no logical ldif export of your OUD. Not a good situation, but if you have a done regular backups, there is a way out without resetting the whole OUD environment to an old timestamp.

The idea is, to create a second empty OUD instance, restore only the Context which is needed, export the entry as ldif and import it again into your current replicated OUD.

Let’s suppose, we have lost the following OUD entry:

Distinguished Name: cn=dbit122_ldap,cn=OracleContext,dc=william,dc=dbi,dc=com

To recover it, follow these steps:

  1. Create new OUD instance
  2. Create suffix
  3. Stop OUD instance asinst_2
  4. Restore the correct dc
  5. Start OUD instance asinst_2
  6. Export entry from asinst_2
  7. Import entry into asinst_1
  8. Cleanup asinst_2
1. Create new OUD instance
[dbafmw@dbidg01 ~]$ cd /u01/app/oracle/product/Middleware/11.1.2.3/Oracle_OUD1
[dbafmw@dbidg01 Oracle_OUD1]$
[dbafmw@dbidg01 Oracle_OUD1]$ ./oud-setup --cli --baseDN dc=dbi,dc=com --addBaseEntry --adminConnectorPort 5444 --ldapPort 2389 \
> --rootUserDN cn=Directory\ Manager --rootUserPasswordFile ~/.oudpwd \
> --ldapsPort 2636 --generateSelfSignedCertificate \
> --hostname dbidg01 --integration generic \
> --serverTuning -Xms2048m\ -Xmx2048m\ -d64\ -XX:+UseCompressedOops\ -server\ -XX:MaxTenuringThreshold=1\ -XX:+UseConcMarkSweepGC\ -XX:CMSInitiatingOccupancyFraction=55 \
> --offlineToolsTuning -Xms2048m\ -Xmx2048m\ -d64\ -XX:+UseCompressedOops\ -server\ -XX:+UseParallelGC\ -XX:+UseNUMA \
> --no-prompt --noPropertiesFile
OUD Instance location successfully created - /u01/app/oracle/product/Middleware/11.1.2.3/Oracle_OUD1/../asinst_2"

Oracle Unified Directory 11.1.2.3.170718
Please wait while the setup program initializes...

See /u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD/logs/oud-setup
for a detailed log of this operation.

Configuring Directory Server ..... Done.
Configuring Certificates ..... Done.
Creating Base Entry dc=dbi,dc=com ..... Done.
Preparing the server for Oracle integration ....... Done.
Starting Directory Server ....... Done.
Creating Net Services suffixes ..... Done.

To see basic server configuration status and configuration you can launch
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD/bin/status


The new OUD instance was started automatically.


[dbafmw@dbidg01 bin]$ /u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD/bin/status


>>>> Specify Oracle Unified Directory LDAP connection parameters

Administrator user bind DN [cn=Directory Manager]:

Password for user 'cn=Directory Manager':

          --- Server Status ---
Server Run Status:        Started
Open Connections:         1

          --- Server Details ---
Host Name:                dbidg01
Administrative Users:     cn=Directory Manager
Installation Path:
/u01/app/oracle/product/Middleware/11.1.2.3/Oracle_OUD1
Instance Path:
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD
Version:                  Oracle Unified Directory 11.1.2.3.170718
Java Version:             1.7.0_151
Administration Connector: Port 5444 (LDAPS)

          --- Connection Handlers ---
Address:Port : Protocol : State
-------------:----------:---------
--           : LDIF     : Disabled
0.0.0.0:161  : SNMP     : Disabled
0.0.0.0:1689 : JMX      : Disabled
0.0.0.0:2389 : LDAP     : Enabled
0.0.0.0:2636 : LDAPS    : Enabled

          --- Data Sources ---
Base DN:     cn=OracleContext
Backend ID:  OIDCompatibility
Entries:     26
Replication: Disabled

Base DN:     cn=OracleContext,dc=dbi,dc=com
Backend ID:  OracleContext0
Entries:     17
Replication: Disabled

Base DN:     cn=OracleSchemaVersion
Backend ID:  OIDCompatibility
Entries:     3
Replication: Disabled

Base DN:     cn=virtual acis
Backend ID:  virtualAcis
Entries:     0
Replication: Disabled

Base DN:     dc=dbi,dc=com
Backend ID:  userRoot
Entries:     1
Replication: Disabled

[dbafmw@dbidg01 bin]$
2. Create suffix
[dbafmw@dbidg01 bin]$ pwd
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD/bin

[dbafmw@dbidg01 bin]$ ./manage-suffix create --baseDN "dc=william,dc=dbi,dc=com" \
> --entries base-entry --integration Generic \
> --hostname localhost --port 5444 \
> --bindDN cn="Directory Manager" --bindPasswordFile ~/.oudpwd \
> --trustAll --no-prompt
Reading Configuration ..... Done.

Creating suffixes ..... Done.

Adding Data ..... Done.

Updating Oracle Integration ..... Done.
[dbafmw@dbidg01 bin]$
3. Stop OUD instance asinst_2
[dbafmw@dbidg01 bin]$ pwd
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD/bin
[dbafmw@dbidg01 bin]$ ./stop-ds
Stopping Server...

[10/Aug/2017:11:10:34 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=OIDCompatibility,cn=Workflow Elements,cn=config is now taken offline
[10/Aug/2017:11:10:34 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=OracleContext for dc=william\,dc=dbi\,dc=com,cn=Workflow elements,cn=config is now taken offline
[10/Aug/2017:11:10:35 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=OracleContext0,cn=Workflow elements,cn=config is now taken offline
[10/Aug/2017:11:10:35 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=userRoot,cn=Workflow Elements,cn=config is now taken offline
[10/Aug/2017:11:10:35 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=userRoot-0,cn=Workflow elements,cn=config is now taken offline
[10/Aug/2017:11:10:35 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=virtualAcis,cn=Workflow Elements,cn=config is now taken offline
[10/Aug/2017:11:10:35 +0200] category=CORE severity=NOTICE msgID=458955 msg=The Directory Server is now stopped
4. Restore the correct dc
[dbafmw@dbidg01 bin]$ pwd
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD/bin
[dbafmw@dbidg01 bin]$ ./restore --listBackups --backupDirectory="/u99/backup/OUD/20170810/OracleContext for dc=william,dc=dbi,dc=com"
Backup ID:          20170810085118Z
Backup Date:        10/Aug/2017:10:51:18 +0200
Is Incremental:     false
Is Compressed:      true
Is Encrypted:       false
Has Unsigned Hash:  false
Has Signed Hash:    false
Dependent Upon:     none

[dbafmw@dbidg01 bin]$ ./restore --dry-run --backupDirectory="/u99/backup/OUD/20170810/OracleContext for dc=william,dc=dbi,dc=com"
[10/Aug/2017:11:19:47 +0200] category=JEB severity=NOTICE msgID=8847444 msg=Verifying: 00000000.jdb

[dbafmw@dbidg01 bin]$ ./restore  --backupDirectory="/u99/backup/OUD/20170810/OracleContext for dc=william,dc=dbi,dc=com"
[10/Aug/2017:11:20:11 +0200] category=JEB severity=NOTICE msgID=8847445 msg=Restored: 00000000.jdb (size 114414)
5. Start OUD instance asinst_2
[dbafmw@dbidg01 bin]$ pwd
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD/bin
[dbafmw@dbidg01 bin]$ ./start-ds
[10/Aug/2017:11:20:30 +0200] category=CORE severity=INFORMATION msgID=132 msg=The Directory Server is beginning the configuration bootstrapping process
[10/Aug/2017:11:20:31 +0200] category=CORE severity=NOTICE msgID=458886 msg=Oracle Unified Directory 11.1.2.3.170718 (build 20170621135318Z, R1706210545) starting up
[10/Aug/2017:11:20:34 +0200] category=RUNTIME_INFORMATION severity=NOTICE msgID=20381717 msg=Installation Directory:  /u01/app/oracle/product/Middleware/11.1.2.3/Oracle_OUD1
[10/Aug/2017:11:20:34 +0200] category=RUNTIME_INFORMATION severity=NOTICE msgID=20381719 msg=Instance Directory:      /u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD
[10/Aug/2017:11:20:34 +0200] category=RUNTIME_INFORMATION severity=NOTICE msgID=20381713 msg=JVM Information: 1.7.0_151-b15 by Oracle Corporation, 64-bit architecture, 2130051072 bytes heap size
[10/Aug/2017:11:20:34 +0200] category=RUNTIME_INFORMATION severity=NOTICE msgID=20381714 msg=JVM Host: dbidg01, running Linux 4.1.12-94.5.7.el7uek.x86_64 amd64, 5986422784 bytes physical memory size, number of processors available 2
[10/Aug/2017:11:20:34 +0200] category=RUNTIME_INFORMATION severity=NOTICE msgID=20381715 msg=JVM Arguments: "-Xms2048m", "-Xmx2048m", "-XX:+UseCompressedOops", "-XX:MaxTenuringThreshold=1", "-XX:+UseConcMarkSweepGC", "-XX:CMSInitiatingOccupancyFraction=55", "-Dorg.opends.server.scriptName=start-ds"
[10/Aug/2017:11:20:34 +0200] category=ACCESS_CONTROL severity=INFORMATION msgID=12582978 msg=Added 11 Global Access Control Instruction (ACI) attribute types to the access control evaluation engine
[10/Aug/2017:11:20:34 +0200] category=BACKEND severity=INFORMATION msgID=9437595 msg=Local DB backend OracleContext0 does not specify the number of lock tables: defaulting to 97
[10/Aug/2017:11:20:34 +0200] category=BACKEND severity=INFORMATION msgID=9437594 msg=Local DB backend OracleContext0 does not specify the number of cleaner threads: defaulting to 24 threads
[10/Aug/2017:11:20:34 +0200] category=BACKEND severity=INFORMATION msgID=9437615 msg=Local DB backend OracleContext0 does not specify the percentage of the heap space to allocate to the database cache: defaulting to 35 percent
[10/Aug/2017:11:20:34 +0200] category=BACKEND severity=INFORMATION msgID=9437613 msg=Local DB backend OracleContext0 does not specify the size of the file handle cache: sizing automatically to use 100 file descriptors
[10/Aug/2017:11:20:35 +0200] category=JEB severity=NOTICE msgID=8847402 msg=The database backend cn=OracleContext0,cn=Workflow elements,cn=config containing 17 entries has started
[10/Aug/2017:11:20:35 +0200] category=ACCESS_CONTROL severity=INFORMATION msgID=12582962 msg=Added 5 Access Control Instruction (ACI) attribute types found in context "cn=OracleContext,dc=dbi,dc=com" to the access control evaluation engine
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437595 msg=Local DB backend virtualAcis does not specify the number of lock tables: defaulting to 97
[10/Aug/2017:11:20:35 +0200] category=JEB severity=NOTICE msgID=8847402 msg=The database backend cn=virtualAcis,cn=Workflow Elements,cn=config containing 0 entries has started
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437595 msg=Local DB backend userRoot-0 does not specify the number of lock tables: defaulting to 97
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437594 msg=Local DB backend userRoot-0 does not specify the number of cleaner threads: defaulting to 24 threads
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437615 msg=Local DB backend userRoot-0 does not specify the percentage of the heap space to allocate to the database cache: defaulting to 35 percent
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437613 msg=Local DB backend userRoot-0 does not specify the size of the file handle cache: sizing automatically to use 100 file descriptors
[10/Aug/2017:11:20:35 +0200] category=JEB severity=NOTICE msgID=8847402 msg=The database backend cn=userRoot-0,cn=Workflow elements,cn=config containing 1 entries has started
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437595 msg=Local DB backend OracleContext for dc=william,dc=dbi,dc=com does not specify the number of lock tables: defaulting to 97
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437594 msg=Local DB backend OracleContext for dc=william,dc=dbi,dc=com does not specify the number of cleaner threads: defaulting to 24 threads
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437615 msg=Local DB backend OracleContext for dc=william,dc=dbi,dc=com does not specify the percentage of the heap space to allocate to the database cache: defaulting to 35 percent
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437613 msg=Local DB backend OracleContext for dc=william,dc=dbi,dc=com does not specify the size of the file handle cache: sizing automatically to use 100 file descriptors
[10/Aug/2017:11:20:35 +0200] category=JEB severity=NOTICE msgID=8847402 msg=The database backend cn=OracleContext for dc=william\,dc=dbi\,dc=com,cn=Workflow elements,cn=config containing 18 entries has started
[10/Aug/2017:11:20:35 +0200] category=ACCESS_CONTROL severity=INFORMATION msgID=12582962 msg=Added 5 Access Control Instruction (ACI) attribute types found in context "cn=OracleContext,dc=william,dc=dbi,dc=com" to the access control evaluation engine
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437595 msg=Local DB backend userRoot does not specify the number of lock tables: defaulting to 97
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437594 msg=Local DB backend userRoot does not specify the number of cleaner threads: defaulting to 24 threads
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437615 msg=Local DB backend userRoot does not specify the percentage of the heap space to allocate to the database cache: defaulting to 35 percent
[10/Aug/2017:11:20:35 +0200] category=BACKEND severity=INFORMATION msgID=9437613 msg=Local DB backend userRoot does not specify the size of the file handle cache: sizing automatically to use 100 file descriptors
[10/Aug/2017:11:20:35 +0200] category=JEB severity=NOTICE msgID=8847402 msg=The database backend cn=userRoot,cn=Workflow Elements,cn=config containing 1 entries has started
[10/Aug/2017:11:20:36 +0200] category=BACKEND severity=INFORMATION msgID=9437595 msg=Local DB backend OIDCompatibility does not specify the number of lock tables: defaulting to 97
[10/Aug/2017:11:20:36 +0200] category=BACKEND severity=INFORMATION msgID=9437594 msg=Local DB backend OIDCompatibility does not specify the number of cleaner threads: defaulting to 24 threads
[10/Aug/2017:11:20:36 +0200] category=BACKEND severity=INFORMATION msgID=9437615 msg=Local DB backend OIDCompatibility does not specify the percentage of the heap space to allocate to the database cache: defaulting to 35 percent
[10/Aug/2017:11:20:36 +0200] category=BACKEND severity=INFORMATION msgID=9437613 msg=Local DB backend OIDCompatibility does not specify the size of the file handle cache: sizing automatically to use 100 file descriptors
[10/Aug/2017:11:20:36 +0200] category=JEB severity=NOTICE msgID=8847402 msg=The database backend cn=OIDCompatibility,cn=Workflow Elements,cn=config containing 29 entries has started
[10/Aug/2017:11:20:36 +0200] category=ACCESS_CONTROL severity=INFORMATION msgID=12582962 msg=Added 10 Access Control Instruction (ACI) attribute types found in context "cn=OracleContext" to the access control evaluation engine
[10/Aug/2017:11:20:36 +0200] category=ACCESS_CONTROL severity=INFORMATION msgID=12582962 msg=Added 1 Access Control Instruction (ACI) attribute types found in context "cn=OracleSchemaVersion" to the access control evaluation engine
[10/Aug/2017:11:20:36 +0200] category=EXTENSIONS severity=INFORMATION msgID=1048797 msg=DIGEST-MD5 SASL mechanism using a server fully qualified domain name of: dbidg01
[10/Aug/2017:11:20:36 +0200] category=CORE severity=INFORMATION msgID=731 msg=LDAP Connection Handler 0.0.0.0 port 2389 does not specify the number of request handler threads: sizing automatically to use 8 threads
[10/Aug/2017:11:20:36 +0200] category=CORE severity=INFORMATION msgID=731 msg=LDAP Connection Handler 0.0.0.0 port 2636 does not specify the number of request handler threads: sizing automatically to use 8 threads
[10/Aug/2017:11:20:36 +0200] category=CORE severity=INFORMATION msgID=720 msg=No worker queue thread pool size specified: sizing automatically to use 24 threads
[10/Aug/2017:11:20:36 +0200] category=PROTOCOL severity=NOTICE msgID=2556180 msg=Started listening for new connections on Administration Connector 0.0.0.0 port 5444
[10/Aug/2017:11:20:36 +0200] category=PROTOCOL severity=NOTICE msgID=2556180 msg=Started listening for new connections on LDAP Connection Handler 0.0.0.0 port 2389
[10/Aug/2017:11:20:36 +0200] category=PROTOCOL severity=NOTICE msgID=2556180 msg=Started listening for new connections on LDAP Connection Handler 0.0.0.0 port 2636
[10/Aug/2017:11:20:36 +0200] category=CORE severity=NOTICE msgID=458887 msg=The Directory Server has started successfully
[10/Aug/2017:11:20:36 +0200] category=CORE severity=NOTICE msgID=458891 msg=The Directory Server has sent an alert notification generated by class org.opends.server.core.DirectoryServer (alert type org.opends.server.DirectoryServerStarted, alert ID 458887):  The Directory Server has started successfully
6. Export entry from asinst_2

Now let’s check if we got our entry “cn=dbit122_ldap,cn=OracleContext,dc=william,dc=dbi,dc=com” back.

[dbafmw@dbidg01 bin]$ ./ldapsearch --hostname localhost --port 2389 --bindDN "cn=Directory Manager" \
> --bindPasswordFile ~/.oudpwd --baseDN "cn=OracleContext,dc=william,dc=dbi,dc=com" "(cn=dbit122_ldap)" objectclass orclNetDescString orclNetDescName orclVersion
dn: cn=dbit122_ldap,cn=OracleContext,dc=william,dc=dbi,dc=com
orclNetDescName: 000:cn=DESCRIPTION_0
orclNetDescString: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg
 01)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521)))(CONNECT_DATA=
 (SERVICE_NAME=DBIT122_PRI)))
objectclass: orclNetService
objectclass: top

Looks good, so we can create the ldif now.

[dbafmw@dbidg01 bin]$ ./ldapsearch --hostname localhost --port 2389 --bindDN "cn=Directory Manager" --bindPasswordFile 
> ~/.oudpwd --baseDN "cn=OracleContext,dc=william,dc=dbi,dc=com" "(cn=dbit122_ldap)" objectclass 
> orclNetDescString orclNetDescName orclVersion > /tmp/dbit122.ldif

[dbafmw@dbidg01 bin]$ cat /tmp/dbit122.ldif
dn: cn=dbit122_ldap,cn=OracleContext,dc=william,dc=dbi,dc=com
orclNetDescName: 000:cn=DESCRIPTION_0
orclNetDescString: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg
 01)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521)))(CONNECT_DATA=
 (SERVICE_NAME=DBIT122_PRI)))
objectclass: orclNetService
objectclass: top
7. Import the entry into asinst_1 (this is the replicated OUD environment)
[dbafmw@dbidg01 bin]$ pwd
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/bin

[dbafmw@dbidg01 bin]$ ./ldapmodify --defaultAdd --filename /tmp/dbit122.ldif --hostname dbidg01 --port 1389 \
> --bindDN "cn=Directory Manager" --bindPasswordFile ~/.oudpwd
Processing ADD request for cn=dbit122_ldap,cn=OracleContext,dc=william,dc=dbi,dc=com
ADD operation successful for DN cn=dbit122_ldap,cn=OracleContext,dc=william,dc=dbi,dc=com
8. Cleanup asinst_2
[dbafmw@dbidg01 OUD]$ pwd
/u01/app/oracle/product/Middleware/11.1.2.3/asinst_2/OUD
[dbafmw@dbidg01 OUD]$ ./uninstall --cli --remove-all

The server is currently running and must be stopped before uninstallation can
continue.
Stop the Server and permanently delete the files? (yes / no) [yes]: yes

Stopping Directory Server ..... Done.
Deleting Files under the Installation Path ..... Done.

Uninstall Completed Successfully.
See /tmp/oud-uninstall-4049143346007549356.log for a detailed log of this operation.
[dbafmw@dbidg01 OUD]$

Ready, we got our entry back, and even cleaned up the leftovers from the temporary OUD instance asinst_2.

Conclusion

Loosing entries from your replicated OUD environment is not the end of the world. If you have good backups, you can create anytime a new OUD instance and recover only the Context that you need.

 

Cet article OUD 11.1.2.3 – How to recover a lost entry in a replicated OUD environment est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths VI – Index Scan

Wed, 2017-08-09 13:58

In the previous post my queries were still reading the indexed column only, from a table which had no modifications since the last vacuum, and then didn’t need to read table pages: it was Index Only Scan. However, we often need more columns than the ones that are in the index. Here is the Index Scan access path.

I’m continuing on the table that I’ve created in the first post of the series. I’ve run VACUUM (the lazy one, not the full one) and did not do any modification after that, as we have seen that Index Only Access is efficient only when there are no modifications.

create table demo1 as select generate_series n , 1 a , lpad('x',1000,'x') x from generate_series(1,10000);
SELECT 10000
create unique index demo1_n on demo1(n);
CREATE INDEX
vacuum demo1;
VACUUM

I have 10000 rows, a unique column N with decimal numbers, indexed and another column A which is not indexed.

Index Only Scan

I’ll now query one row, the one with N=1000.

explain (analyze,verbose,costs,buffers) select n from demo1 where n=1000 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..4.30 rows=1 width=4) (actual time=0.123..0.124 rows=1 loops=1)
Output: n
Index Cond: (demo1.n = 1000)
Heap Fetches: 0
Buffers: shared hit=3
Planning time: 0.625 ms
Execution time: 0.137 ms

It seems that the query planner estimates to read one block:

  • The startup cost of 0.29 as we have seen before
  • Read one index page, cost=4 (random_page_cost=4)
  • 1 result row to process, estimated at cpu_tuple_cost=0.01

As the index is a B*Tree with 30 pages, I expect to read at least one branch in addition to the leaf block. The execution has actually read 3 blocks (Buffers: shared hit=3). Here it seems that Postgres decides to ignore the branches and count only the leaf blocks.

In Oracle, the estimation cost=1 and execution has read 2 blocks:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gusay436hpzck, child number 0
-------------------------------------
select /*+ */ n from demo1 where n=1000
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 1 |00:00:00.01 | 2 |
|* 1 | INDEX UNIQUE SCAN| DEMO1_N | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Both Oracle and Postgres read only the index here. This is the fastest access to one indexed column: no need to read the table because the column is in the index. The use-case is quite limited here: just testing the existence of the column. I will now select another column than the one used in the where clause.

Select another column

I filter on N but now query the column A which is not in the index. The Index Only Scan changes to an Index Scan:

explain (analyze,verbose,costs,buffers) select a from demo1 where n=1000 ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using demo1_n on public.demo1 (cost=0.29..8.30 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)
Output: a
Index Cond: (demo1.n = 1000)
Buffers: shared hit=3
Planning time: 0.639 ms
Execution time: 0.030 ms

The cost is the same except that there is one additional page to read, which pushes it to cost=8.30:

  • The startup cost of 0.29 as we have seen before
  • Read one index page, and one table page: cost=8 (random_page_cost=4)
  • 1 result row to process, estimated at cpu_tuple_cost=0.01

In Oracle it is not a different operation. We still have the INDEX UNIQUE SCAN, but in addition to it, an additional operation to read the table: TABLE ACCESS BY INDEX ROWID. The index entry returns the ROWID (physical address of the table block, equivalent to the Postgres TID). And then we have the detail of the cost, and execution buffer reads: one more block.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8q4tcxgk1n1vn, child number 0
-------------------------------------
select /*+ */ a from demo1 where n=1000
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO1 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | DEMO1_N | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"[NUMBER,22] 2 - "DEMO1".ROWID[ROWID,10]

The important thing here is within the predicate information where we see the part of the where clause which is not a filter applied after the scan, but is used for optimal access by the index. It is displayed as access() in Oracle execution plan:

access("N"=1000)

In PostgreSQL execution plan, the same information is displayed as ‘Index Cond':

Index Cond: (demo1.n = 1000)

Postgres Range Scan

That was retrieving only one row with an equality predicate on a unique index column. The index scan helps to get directly to the value because of the B*Tree structure. As the index is sorted, an inequality predicate can also use the index to find the rows in a range of values.

The Postgres plan looks the same, with Index Scan:

explain (analyze,verbose,costs,buffers) select a from demo1 where n<=1000 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using demo1_n on public.demo1 (cost=0.29..175.78 rows=1000 width=4) (actual time=0.029..0.780 rows=1000 loops=1)
Output: a
Index Cond: (demo1.n <= 1000)
Buffers: shared hit=147
Planning time: 1.019 ms
Execution time: 0.884 ms

Same plan but of course we have more index blocks to scan, and more rows to fetch from the table, which is why the cost is higher.

In order to understand the cost, I’ve changed the query planner constants one by one. Here is what I got:

  • (cost=0.29..33.78 rows=1000 width=4) when seq_page_cost=0 instead of 1, which means that it estimates (175.78-33.78)/1=142 sequential reads
  • (cost=0.29..159.78 rows=1000 width=4) when random_page_cost=0 instead of 4, which means that it estimates (175.78-159.78)/4=4 random reads
  • (cost=0.29..165.78 rows=1000 width=4) when cpu_tuple_cost=0 instead of 0.01, which means that it estimates (175.78-165.78)/0.01=1000 rows
  • (cost=0.29..170.78 rows=1000 width=4) when cpu_index_tuple_cost=0 instead of 0.005, which means that it estimates (175.78-170.78)/0.005=1000 index entries
  • (cost=0.00..173.00 rows=1000 width=4) when cpu_operator_cost=0 instead of 0.0025, which means that it estimates (175.78-173.00)/0.0025=1112 cpu operations (116 for initial cost + 996 to get all rows)

I understand the 4 random reads from the index pages. However, I expected random reads, and not sequential reads, to fetch the rows from the table. But this is a case where the clustering factor is very good: the rows have been inserted in the same order as the indexed column, and this means that those reads from table probably read consecutive pages.

In order to validate this guess, I’ve traced the system calls on Linux

25734 open("base/12924/42427", O_RDWR) = 42
25734 lseek(42, 0, SEEK_END) = 11706368
25734 open("base/12924/42433", O_RDWR) = 43
25734 lseek(43, 0, SEEK_END) = 245760

The file descriptor 42 is my table (demo1) and the descriptor 43 is the index (demo1_n). The file name is in the open() call and it includes the file id:

select relname,relfilenode from pg_class where relname='demo1';
-[ RECORD 1 ]--+------
relname | demo1
relfilenode | 42427
 
select relname,relfilenode from pg_class where relname='demo1_n';
-[ RECORD 1 ]--+--------
relname | demo1_n
relfilenode | 42433

Then we see some random reads from the index (branches and first leaf):

25734 lseek(43, 0, SEEK_SET) = 0
25734 read(43, "100036037360374 b152"..., 8192) = 8192
25734 lseek(43, 24576, SEEK_SET) = 24576
25734 read(43, "121000836360374 35023720330237 "..., 8192) = 8192
25734 lseek(43, 8192, SEEK_SET) = 8192
25734 read(43, "13245t360374 211 340237 "..., 8192) = 8192

Then we see 53 reads from the table:

25734 lseek(42, 0, SEEK_SET) = 0
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
...

Only one lseek. The other reads are all single block (8k) I/O calls but without seek, which means that they are sequential. When relying on filesystem prefetching, this may avoid the latency for each I/O call.

Then the next leaf block from the index is read, and then 52 reads from the table (no lseek):

25734 read(43, "13245t360374 211 340237 "..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
...

And again, one index block and 38 contiguous table blocks:

25734 lseek(43, 32768, SEEK_SET) = 32768
25734 read(43, "13245t360374 211 340237 "..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
25734 read(42, "40042203 4 36023330103402273010"..., 8192) = 8192
...

Here is the summary of the cost 175.78

  • The startup cost of 0.29 as we have seen before
  • Estimates 4 random reads (reading 1000 rows from a 30 pages index which contains 10000 rows): cost=16 (random_page_cost=4)
  • Estimates 142 sequential reads: cost=142 (seq_page_cost=1)
  • 1000 index entries to process, estimated at cost=5 (cpu_index_tuple_cost=0.005)
  • 1000 result row to process, estimated at cost=10 (cpu_tuple_cost=0.01)
  • about 1000 operators or functions estimated at cpu_operator_cost=0.0025

The very interesting thing here is that the query planner is totally aware of the clustering factor and uses sequential read estimation.

Oracle Range Scan

Here is the same query on the similar table on Oracle:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a3gqx19xs9wxq, child number 0
-------------------------------------
select /*+ */ a from demo1 where n<=1000
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 147 (100)| 1000 |00:00:00.01 | 148 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO1 | 1 | 1000 | 147 (0)| 1000 |00:00:00.01 | 148 |
|* 2 | INDEX RANGE SCAN | DEMO1_N | 1 | 1000 | 4 (0)| 1000 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"<=1000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"[NUMBER,22] 2 - "DEMO1".ROWID[ROWID,10]

The straces shows calls to pread:

open("/u01/oradata/CDB1A/PDB/users01.dbf", O_RDWR|O_DSYNC) = 7
fcntl(7, F_SETFD, FD_CLOEXEC) = 0
fcntl(7, F_DUPFD, 256) = 258
fcntl(258, F_SETFD, FD_CLOEXEC) = 0
close(7) = 0
pread(258, "62422313G275"142532'!1?275""..., 8192, 2252800 ) = 8192
pread(258, "62422413C275"14x2432'!1?275""..., 8192, 2260992 ) = 8192
pread(258, "6242313v3362274"24b+1&!1354274""..., 8192, 24731648 ) = 8192
pread(258, "6242314v3362274"24e*1&!1354274""..., 8192, 24739840 ) = 8192
pread(258, "6242315v3362274"24d51&!1354274""..., 8192, 24748032 ) = 8192
pread(258, "6242316v3362274"24g41&!1354274""..., 8192, 24756224 ) = 8192
pread(258, "6242317v3362274"24f71&!1354274""..., 8192, 24764416 ) = 8192
pread(258, "6242320v3362274"24y71&!1354274""..., 8192, 24772608 ) = 8192

pread is similar to lseek()+read() here and, as far as I know, Linux detects when there is no need to seek, and this allows prefetching as well. Oracle has also its own prefetching but I’ll not go into the detail here (read Timur Akhmadeev on Pythian blog about this).

With Oracle, there is no need to run strace because all system calls are instrumented as ‘wait events’ and here is a trace:

PARSE #140375247563104:c=2000,e=1872,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=187737470,tim=53267437268
EXEC #140375247563104:c=0,e=147,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=187737470,tim=53267437481
WAIT #140375247563104: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=74022 tim=53267437532
WAIT #140375247563104: nam='db file sequential read' ela= 8 file#=12 block#=275 blocks=1 obj#=74023 tim=53267437679
WAIT #140375247563104: nam='db file sequential read' ela= 5 file#=12 block#=276 blocks=1 obj#=74023 tim=53267437785
WAIT #140375247563104: nam='db file sequential read' ela= 5 file#=12 block#=3019 blocks=1 obj#=74022 tim=53267437902
FETCH #140375247563104:c=0,e=368,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=187737470,tim=53267437977
WAIT #140375247563104: nam='PGA memory operation' ela= 14 p1=0 p2=0 p3=0 obj#=74022 tim=53267438017
WAIT #140375247563104: nam='SQL*Net message from client' ela= 280 driver id=1413697536 #bytes=1 p3=0 obj#=74022 tim=53267438385
WAIT #140375247563104: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=74022 tim=53267438419
WAIT #140375247563104: nam='db file sequential read' ela= 3 file#=12 block#=3020 blocks=1 obj#=74022 tim=53267438443
WAIT #140375247563104: nam='PGA memory operation' ela= 7 p1=1114112 p2=2 p3=0 obj#=74022 tim=53267438475
WAIT #140375247563104: nam='db file sequential read' ela= 5 file#=12 block#=3021 blocks=1 obj#=74022 tim=53267438504
WAIT #140375247563104: nam='db file sequential read' ela= 3 file#=12 block#=3022 blocks=1 obj#=74022 tim=53267438532
WAIT #140375247563104: nam='db file sequential read' ela= 2 file#=12 block#=3023 blocks=1 obj#=74022 tim=53267438552
WAIT #140375247563104: nam='db file sequential read' ela= 3 file#=12 block#=3024 blocks=1 obj#=74022 tim=53267438576
WAIT #140375247563104: nam='db file sequential read' ela= 4 file#=12 block#=3025 blocks=1 obj#=74022 tim=53267438603
WAIT #140375247563104: nam='db file sequential read' ela= 26 file#=12 block#=3026 blocks=1 obj#=74022 tim=53267438647
WAIT #140375247563104: nam='db file sequential read' ela= 4 file#=12 block#=3027 blocks=1 obj#=74022 tim=53267438680
WAIT #140375247563104: nam='db file sequential read' ela= 2 file#=12 block#=3028 blocks=1 obj#=74022 tim=53267438699
WAIT #140375247563104: nam='db file sequential read' ela= 4 file#=12 block#=3029 blocks=1 obj#=74022 tim=53267438781
WAIT #140375247563104: nam='db file sequential read' ela= 3 file#=12 block#=3030 blocks=1 obj#=74022 tim=53267438807
WAIT #140375247563104: nam='db file sequential read' ela= 28 file#=12 block#=3031 blocks=1 obj#=74022 tim=53267438878
...

The name ‘sequential read’ does not mean the same as the Postgres ‘sequential read’. It only means single-block reads that are done one after the other, but they are actually random reads. However, looking at the block# they appear as reading contiguous blocks.

At the end, because I have an index with good clustering factor, and because I’m using the defaults on Linux without direct read and asynchronous I/O, the execution is very similar to the postgres one: read the few index blocks and follow the pointer to the 140 blocks of the table.

The cost estimation looks similar (same number) between Postgres and Oracle but it is not the same unit. Postgres estimates the cost with sequential reads, but Oracle estimates the cost as random reads. In addition to that, Postgres, with its default planner parameters, gives more importance than Oracle to the CPU usage.

This is the good case of Index Access where we have a good clustering/correlation factor between the physical order of the table and the logical order of the index. The random reads are finally behaving as sequential read because there is no seek() between them. You can imagine that in the next post I’ll try the same with a very bad clustering factor.

 

Cet article Postgres vs. Oracle access paths VI – Index Scan est apparu en premier sur Blog dbi services.

SQL Server on Linux: Introduction to DBFS experimental tool

Wed, 2017-08-09 08:14

A couple of months ago, Microsoft announced two additional command line tools for SQL Server that are mssql-scripter and DBFS. The latter has drawn my attention because it exposes live data from SQL Server DMVs as virtual files in a virtual directory on Linux operating system. Microsoft has probably taken another positive step in the SQL Server’s adoption on Linux. Indeed, in a Linux world, we may get Kernel’s performance and configuration data either directly from the procfs or indirectly by using tools that involve procfs in the background.

blog 123 - sqlserver dbfs

DBFS uses the FUSE filesystem module to expose DMVs and according to Microsoft blog, reading data from each concerned DMV file is a live process that ensures to get always fresh data.

[mikedavem@sql server]$ rpm -qR $(rpm -qa | grep dbfs)
glibc
fuse
fuse-devel
freetds
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(PayloadIsXz) <= 5.2-1

After installing DBFS, let’s start the tool by using the following command line:

[mikedavem@sql mssql]$ dbfs -m /var/opt/mssql/dmv -c /var/opt/mssql/secrets/dmvtool.conf -v      
1: Processing entry for section server in configuration file:
SUCCESSFULLY added entry for server server.

The configuration file (-c parameter) stores sensitive information to connect to the SQL Server instance (hostname, user and “insecure” password). You shall consider to protect this file with suitable permissions.

DBFS is a background process by default but you may change the behavior by using -f parameter at the startup.

[mikedavem@sql mssql]$ ps -e | grep dbfs
  2673 ?        00:00:00 dbfs

At this stage, the negative point is that to start DBFS manually if the server restarts. I had thought to wrap DBFS in a cron job that will run at the server startup but my preference would be to get the possibility to control DBFS through system as a service.

Exploring the DMV mounted filesystem

In fact, every DMV is exposed in two formats (normal file and JSON file).

[mikedavem@sql server]$ pwd
/var/opt/mssql/dmv/server
[mikedavem@sql server]$ ls -I "*.json" | wc -l
494

DBFS exposes a lot of DMVs as we notice above. We may also want to filter regarding the category plan described in the BOL by using grep.

For instance, AlwaysOn related DMVs …

[mikedavem@sql server]$ ls -I "*.json" | grep dm_hadr
dm_hadr_automatic_seeding
dm_hadr_auto_page_repair
dm_hadr_availability_group_states
dm_hadr_availability_replica_cluster_nodes
dm_hadr_availability_replica_cluster_states
dm_hadr_availability_replica_states
dm_hadr_cluster
dm_hadr_cluster_members
dm_hadr_cluster_networks
dm_hadr_database_replica_cluster_states
dm_hadr_database_replica_states
dm_hadr_instance_node_map
dm_hadr_name_id_map
dm_hadr_physical_seeding_stats
…

… or database related DMVs

[mikedavem@sql server]$ ls -I "*.json" | grep dm_db
dm_db_column_store_row_group_operational_stats
dm_db_column_store_row_group_physical_stats
dm_db_file_space_usage
dm_db_fts_index_physical_stats
dm_db_index_usage_stats
dm_db_log_space_usage
dm_db_mirroring_auto_page_repair
dm_db_mirroring_connections
dm_db_mirroring_past_actions
dm_db_missing_index_details
dm_db_missing_index_groups
dm_db_missing_index_group_stats
dm_db_partition_stats
dm_db_persisted_sku_features
dm_db_rda_migration_status
dm_db_rda_schema_update_status
dm_db_script_level
dm_db_session_space_usage
dm_db_task_space_usage 
…

The schema name suffix (sys) is not present for DMV related files.

Extracting data from DMV related files

Linux provides very powerful tools to consume data from files like tr, cut, split, sort, join, cat, grep and awk to cite few of them. You may find some examples in Github but I decided to get my own experience by attempting to address some usual DBA queries.

  • Extracting data from sys.dm_os_sys_info to retrieve CPU, memory available information on the server as well as the memory manager consumption

We may use either cut command to extract required columns from the dm_os_sys_info file.

[mikedavem@sql server]$ cut -d$'\t' -f3,4,5,7,8,9,15,27 dm_os_sys_info
cpu_count       hyperthread_ratio       physical_memory_kb      committed_kb    committed_target_kb      visible_target_kb       scheduler_count virtual_machine_type_desc
4       4       3918848 207160  3914240 3914240 4       HYPERVISOR

But the above output is not very readable and we may want to display the information differently (in column rather than in row). Using awk may be your best solution in this case:

[mikedavem@sql server]$ for ((i=1;i<$(cut -d$'\t' -f3,4,5,7,8,9,15,27 dm_os_sys_info | head -n 1 | wc -w);i++)); do awk '{print $'$i'}' <(cut -d$'\t' -f3,4,5,7,8,9,15,27 dm_os_sys_info) | tr '\n' ' ';echo;done | column -t
cpu_count			4
hyperthread_ratio	4
physical_memory_kb	3918848
committed_kb		207296
committed_target_kb	3914240
visible_target_kb	3914240
scheduler_count		4

 

  • Extracting information from sys.databases

A typical query I may see is to retrieve database information including their name, id, state, recovery model and owner. Basically , we have to join two DMVs to get all the requested information: sys.databases and sys.server_principals. In Linux world, you may also use the join command to gather information from different files but it implies to pre-sort each data file first.

But getting the desired output may be challenging. Indeed, firstly we will probably choose the grep / cut tools to extract only rows and columns we want. But using such tools will prevent to keep the column context and getting only column values from the DMV related files may be meaningless in this case as shown below :

[mikedavem@sql server]$ join -1 2 -2 3 -t$'\t' -o 2.1,2.2,2.3,2.4,2.5,1.1 \
<(grep -Ev '^[NT|##]|SERVER_ROLE' server_principals | cut -d$'\t' -f 1,3 | sort -t$'\t' -k2) \
<(cut -d$'\t' -f 1,2,4,14,22 databases | sort -t$'\t' -k3) | column -t
model				3  01  ONLINE  FULL    sa
test				6  01  ONLINE  FULL    sa
ApplixEnterprise	5  01  ONLINE  SIMPLE  sa
master				1  01  ONLINE  SIMPLE  sa
msdb				4  01  ONLINE  SIMPLE  sa
tempdb				2  01  ONLINE  SIMPLE  sa

But preserving the column context may become also your concern if you want to sort column data values. Indeed, let’s say you want to sort the above output by the database_id column value. You will quickly notice that the header file will be included by the sort operator. Definitely not the result we expect in this case. So, in order to meet our requirement, we may use again the very powerful awk command as shown below:

[mikedavem@sql server]$ join --header -1 2 -2 3 -t$'\t' -o 2.1,2.2,2.3,2.4,2.5,1.1 \
<(head -n1 server_principals | cut -d$'\t' -f 1,3;grep -Ev '^[NT|##]|SERVER_ROLE' server_principals | cut -d$'\t' -f 1,3 | sort -t$'\t' -k2) \
<(head -n 1 databases | cut -d$'\t' -f 1,2,4,14,22;cut -d$'\t' -f 1,2,4,14,22 databases | sort -t$'\t' -k3) \
| awk 'NR<2{print $0;next}{print $0 | "sort -k2"}' | column -t
name				database_id  	owner_sid	state_desc	recovery_model_desc	name
master				1				01		ONLINE      	SIMPLE				sa
tempdb				2            	01		ONLINE      	SIMPLE				sa
model				3            	01		ONLINE      	FULL				sa
msdb				4            	01		ONLINE      	SIMPLE				sa
ApplixEnterprise	5            	01		ONLINE      	SIMPLE				sa
test				6            	01		ONLINE      	FULL				sa

We finally managed to display the desired output but my feeling is we worked hard for few results and usually we have to deal with more DMVs and complex join than the previous example in the same query. Furthermore, we often have to aggregate data from DMVs to get relevant results and I may easily imagine the additional efforts to produce the corresponding scripts if we extend the scenarios we have to deal with.

There are probably other tools on Linux to make the task easier but my opinion is that DBFS should include the ability to execute custom queries already used by DBAs day-to-day to go beyond the actual capabilities. We didn’t surface JSON format in this blog. This is another way to consume data from DMV related files but in my opinion, it targets more the developers than the DBAs audience.

I noticed that I was not the first and the only one to think about those enhancements by looking at the DBFS Github and the issues section. Anyway, I like the Microsoft’s initiative to give us the ability to consume DMVs related data from the command line in a Linux world and I’m looking forward the next features about this tool!

 

 

 

Cet article SQL Server on Linux: Introduction to DBFS experimental tool est apparu en premier sur Blog dbi services.

MariaDB – How to migrate quickly MySQL to MariaDB over different hosts with mydumper/myloader and ncat

Wed, 2017-08-09 07:27

A lot of possibilities exist to migrate MySQL to MariaDB. In this blog I would like to show a scenario where I migrate MySQL 5.7.19 with a lot of databases to MariaDB 10.2.7, which is on a different host. The tools I am using are mydumper/myloader and ncat.

Please be aware that mydumper does not come out of the box. It has to be installed beforehand, like explained in my previous blog: https://blog.dbi-services.com/mariadb-speed-up-your-logical-mariadb-backups-with-mydumper/

The same applies to ncat. It might not be on your system. However, the installation of ncat is quite simple. Just run

# yum install nmap-ncat

Ncat is just a networking utility which reads and writes data across networks from the command line. A quite underestimated tool from my point view. And please take care, ncat does not encrypt your network traffic per default. That’s why it might be faster than coping it via scp/sftp. Ncat operates in one of two primary modes: connect mode and listen mode. So, we need to install it on both hosts. In my case on node mysql01 (connect mode) and on mysql02 (listen mode). If you want to learn more about ncat, take a look at the following web site. http://nmap.org/ncat

Ok. Let’s get started. First of all, we take a look at the current MySQL instance. As you can see in the output below, I do have a lot of databases that I want to migrate, e.g. air, dbi and a couple of sakila databases.

mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] u | grep -A 1 mysqld6
mysqld6 is running        : 5.7.19 (mysql-5.7.19)
Port                      : 33006

mysqld6-(root@localhost) [(none)]> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)

mysqld6-(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| air                |
| dbi                |
| mysql              |
| performance_schema |
| sakila             |
| sakila01           |
| sakila02           |
| sakila03           |
| sakila04           |
| sakila05           |
| sakila06           |
| sys                |
| wrs                |
+--------------------+
14 rows in set (0.00 sec)

MyDumper comes with a quite cool parameter, called –regex. By using the –regex parameter, we can dump out all databases except the ones the we don’t want in an elegant way. e.g. we might not want to dump (mysql|test|information_schema|sys|performance_schema)

mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld6/.my.cnf \
--threads=6 --regex '^(?!(mysql|test|information_schema|sys|performance_schema))' \
--outputdir=/u99/mysqlbackup/mysqld6/mydumper_mysqld6

Ok. We got now all databases dumped out and we can copy it over to the MariaDB host with ssh, ftp, rsync or other tools. Or we do it via ncat directly. For doing so, we need to start the ncat in listen mode (-l) on the destination host mysql02.

-- On host mysql02:

mysql@mysql02:/u00/app/mysql/ [mysqld6] cd /u99/mysqlbackup/mysqld6/mydumper_mysqld6
mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6]
mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] nc -4 -l 33333 | tar -xzvp
...
this host is waiting now for network packets

On our source node, we start mydumper, pipe it to tar and send it to ncat.

On host mysql01:

mysql@mysql01:/u00/app/mysql/ [mysqld6] cd /u99/mysqlbackup/mysqld6/mydumper_mysqld6
mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6]
mysql@mysql01:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld6/.my.cnf \
--threads=6 --regex '^(?!(mysql|test|information_schema|sys|performance_schema))' | tar -czv -f - .  | nc --send-only mysql02 33333
./
./export-20170809-062635/
./export-20170809-062635/air-schema-create.sql
./export-20170809-062635/dbi-schema-create.sql
./export-20170809-062635/sakila-schema-create.sql
./export-20170809-062635/sakila01-schema-create.sql
./export-20170809-062635/sakila02-schema-create.sql
./export-20170809-062635/sakila03-schema-create.sql
./export-20170809-062635/sakila04-schema-create.sql
./export-20170809-062635/sakila05-schema-create.sql
./export-20170809-062635/sakila06-schema-create.sql
./export-20170809-062635/wrs-schema-create.sql
./export-20170809-062635/metadata
./export-20170809-062635/dbi.dbi_t.sql
./export-20170809-062635/sakila05.category.sql
./export-20170809-062635/sakila.actor.sql
./export-20170809-062635/sakila.address.sql
./export-20170809-062635/sakila.category.sql
./export-20170809-062635/sakila.city.sql
./export-20170809-062635/sakila.country.sql
./export-20170809-062635/sakila.customer.sql
./export-20170809-062635/sakila.film.sql
./export-20170809-062635/sakila.film_actor.sql
./export-20170809-062635/sakila.film_category.sql
./export-20170809-062635/sakila.film_text.sql
./export-20170809-062635/sakila.inventory.sql
./export-20170809-062635/sakila.language.sql
./export-20170809-062635/sakila.payment.sql
./export-20170809-062635/sakila.rental.sql
./export-20170809-062635/sakila.staff.sql
./export-20170809-062635/sakila.store.sql
./export-20170809-062635/sakila01.actor.sql
./export-20170809-062635/sakila01.address.sql
...
...

On the other terminal, you can see that the files are being received.

On host mysql02:

mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] nc -4 -l 33333 | tar -xzvp
./
./export-20170809-062635/
./export-20170809-062635/air-schema-create.sql
./export-20170809-062635/dbi-schema-create.sql
./export-20170809-062635/sakila-schema-create.sql
./export-20170809-062635/sakila01-schema-create.sql
./export-20170809-062635/sakila02-schema-create.sql
./export-20170809-062635/sakila03-schema-create.sql
./export-20170809-062635/sakila04-schema-create.sql
./export-20170809-062635/sakila05-schema-create.sql
./export-20170809-062635/sakila06-schema-create.sql
./export-20170809-062635/wrs-schema-create.sql
./export-20170809-062635/metadata
./export-20170809-062635/dbi.dbi_t.sql
./export-20170809-062635/sakila05.category.sql
./export-20170809-062635/sakila.actor.sql
./export-20170809-062635/sakila.address.sql
./export-20170809-062635/sakila.category.sql
./export-20170809-062635/sakila.city.sql
./export-20170809-062635/sakila.country.sql
./export-20170809-062635/sakila.customer.sql
./export-20170809-062635/sakila.film.sql
./export-20170809-062635/sakila.film_actor.sql
./export-20170809-062635/sakila.film_category.sql
./export-20170809-062635/sakila.film_text.sql
./export-20170809-062635/sakila.inventory.sql
./export-20170809-062635/sakila.language.sql
./export-20170809-062635/sakila.payment.sql
./export-20170809-062635/sakila.rental.sql
./export-20170809-062635/sakila.staff.sql
./export-20170809-062635/sakila.store.sql
./export-20170809-062635/sakila01.actor.sql
./export-20170809-062635/sakila01.address.sql
...
...
...

mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] ls -l
total 20
drwx------ 2 mysql mysql 16384 Aug  9 06:26 export-20170809-062635

That’s it. We got now all the files on our destination host mysql02. Now it’s time to import the data via myloader into the new MariaDB 10.2.7 which is empty at the moment.

mysql@mysql02:/u00/app/mysql/ [mysqld6] u | grep -A 1 mysqld6
mysqld6 is running        : 10.2.7-MariaDB (mariadb-10.2.7)
Port                      : 33006

mysql@mysql02:/u00/app/mysql/product/tools/mydumper-0.9.2/bin/ [mysqld6] mq
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.2.7-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysqld6-(root@localhost) [(none)]> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 10.2.7-MariaDB-log |
+--------------------+
1 row in set (0.00 sec)

Per default, the myloader tool starts 4 parallel threads, and runs in verbose mode 2 which means that only warning messages are shown. In case you want to have it more verbose, you can specify –verbose=3

mysql@mysql02:/u99/mysqlbackup/mysqld6/mydumper_mysqld6/ [mysqld6] /u00/app/mysql/product/tools/mydumper/bin/myloader \
--defaults-file=/u00/app/mysql/admin/mysqld6/.my.cnf --threads=6 --verbose=3 \
--directory /u99/mysqlbackup/mysqld6/mydumper_mysqld6/export-20170809-062635
** Message: 6 threads created
** Message: Creating database `air`
** Message: Creating table `air`.`muc`
** Message: Creating database `dbi`
** Message: Creating table `dbi`.`dbi_t`
** Message: Creating table `dbi`.`dbi_t2`
** Message: Creating database `sakila`
** Message: Creating table `sakila`.`actor`
** Message: Creating table `sakila`.`address`
** Message: Creating table `sakila`.`category`
** Message: Creating table `sakila`.`city`
** Message: Creating table `sakila`.`country`
** Message: Creating table `sakila`.`customer`
** Message: Creating table `sakila`.`film`
...
...

If you take a look at the process list, you should see 6 threads doing the work.

mysqld6-(root@localhost) [(none)]> show processlist;
...
...
| 30 | root        | localhost | sakila01 | Query   |    0 | Opening tables          | INSERT INTO `city` VALUES
(1,"A Corua (La Corua)",87,"2006-02-15 03:45:25"),
(2,"Abha",82,"2006-02-1 |    0.000 |
| 31 | root        | localhost | sakila   | Query   |    6 | update                  | INSERT INTO `film_actor` VALUES
(1,1,"2006-02-15 04:05:03"),
(1,23,"2006-02-15 04:05:03"),
(1,25,"20 |    0.000 |
| 32 | root        | localhost | sakila   | Query   |    4 | update                  | INSERT INTO `payment` VALUES
(1,1,1,76,2.99,"2005-05-25 11:30:37","2006-02-15 21:12:30"),
(2,1,1,573 |    0.000 |
| 33 | root        | localhost | sakila   | Query   |    3 | update                  | INSERT INTO `rental` VALUES
(1,"2005-05-24 22:53:30",367,130,"2005-05-26 22:04:30",1,"2006-02-15 20: |    0.000 |
| 34 | root        | localhost | sakila01 | Query   |    2 | update                  | INSERT INTO `address` VALUES
(1,"47 MySakila Drive",NULL,"Alberta",300,"","",">\n2]c |    0.000 |
| 35 | root        | localhost | sakila   | Query   |    4 | update                  | INSERT INTO `inventory` VALUES

That’s it. We got now the data migrated to MariaDB 10.2.7.

Conclusion

Many ways do exist for migrating MySQL to MariaDB. Using mydumper/myloader in combination with ncat is just one of those. However, from my point of view, a quite cool one.

 

Cet article MariaDB – How to migrate quickly MySQL to MariaDB over different hosts with mydumper/myloader and ncat est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths V – FIRST ROWS and MIN/MAX

Tue, 2017-08-08 08:58

We have seen how an index can help to avoid a sorting operation in the previous post. This avoids a blocking operation: the startup cost is minimal and the first rows can be immediately returned. This is often desired when displaying rows to the user screen. Here is more about Postgres startup cost, Oracle first_rows costing, and fetching first rows only.

Here is the execution plan we had in Oracle to get the values of N sorted. The cost for Oracle is the cost to read the index leaves: estimated to 46 random reads:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dbck3rgnqbakg, child number 0
-------------------------------------
select /*+ */ n from demo1 where n is not null order by n
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 46 (100)| 10000 |00:00:00.01 | 48 |
| 1 | INDEX FULL SCAN | DEMO1_N | 1 | 10000 | 46 (0)| 10000 |00:00:00.01 | 48 |
---------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

In PostreSQL, we have two costs (cost=0.29..295.29):

explain (analyze,verbose,costs,buffers) select n from demo1 where n is not null order by n ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..295.29 rows=10000 width=4) (actual time=0.194..2.026 rows=10000 loops=1)
Output: n
Index Cond: (demo1.n IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=30
Planning time: 1.190 ms
Execution time: 2.966 ms

I explained where the total cost (295.29) comes from:

  • The index on the column X has 30 blocks witch is estimated at cost=120 (random_page_cost=4)
  • We have 10000 index entries to process, estimated at cost=50 (cpu_index_tuple_cost=0.005)
  • We have 10000 result rows to process, estimated at cost=100 (cpu_tuple_cost=0.01)
  • We have evaluated 10000 ‘is not null’ conditions, estimated at cost=25 (cpu_operator_cost=0.0025)

But the Postgres EXPLAIN also show the startup cost (0.29) which is the cost before returning the first rows (only few cpu_operator_cost here).

From that, I can guess that fetching 1 row will have the following cost:

  • The startup cost of 0.29
  • Read the first index page, cost=4 (random_page_cost=4)
  • 1 index entry to process at cpu_index_tuple_cost=0.005
  • 1 result row to process, estimated at cpu_tuple_cost=0.01
  • 1 ‘is not null’ conditions, estimated at cpu_operator_cost=0.0025

This should be approximately cost=4.3075 for one row. Roughly the cost to read one index page. We will see later that the query planner do not count this first index page.

Oracle First Rows

In Oracle, we have only the total cost in the execution plan, but we can estimate the cost to retrieve 1 row with the FIRST_ROWS(1) hint:


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0fjk9vv4g1q1w, child number 0
-------------------------------------
select /*+ first_rows(1) */ n from demo1 where n is not null order by
n
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 10000 |00:00:00.01 | 48 |
| 1 | INDEX FULL SCAN | DEMO1_N | 1 | 10000 | 2 (0)| 10000 |00:00:00.01 | 48 |
---------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

The cost here is small, estimated to 2 random reads (1 B*Tree branch and 1 leaf) which is sufficient to get the first row. Of course, I’ve estimated it for 1 row but I finally retrieved all rows (A-Rows=10000), reading all blocks (Buffers=48). However, my execution plan is optimized for fetching one row.

Fetch first rows

I can run the previous query and finally fetch only one row, but I can also explicitly filter the result to get one row only. If you use older versions of Oracle, you may have used the ‘rownum’ way of limiting rows, and this implicitly adds the first_rows hint. Here I’m using the FETCH FIRST syntax and I need to explicitely add the FIRST_ROWS() hint to get the plan optimized for that.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9bcm542sk64az, child number 0
-------------------------------------
select /*+ first_rows(1) */ n from demo1 where n is not null order by n fetch first 1 row only
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 3 |
|* 1 | VIEW | | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
| 3 | INDEX FULL SCAN | DEMO1_N | 1 | 10000 | 2 (0)| 2 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "N")<=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_002"."N"[NUMBER,22], "from$_subquery$_002"."rowlimit_$$_rownumber"[NUMBER,22] 2 - (#keys=1) "N"[NUMBER,22], "DEMO1".ROWID[ROWID,10], ROW_NUMBER() OVER ( ORDER BY "N")[22] 3 - "DEMO1".ROWID[ROWID,10], "N"[NUMBER,22]

The cost is the same, estimated to 2 random reads, but we see how Oracle implements the FETCH FIRST: with window functions. And only one row has been fetched (A-Rows) reading 3 blocks (buffers). Note that because the index is sorted, the window function is a NOSORT operation.

Postgres

I can run the same query on PostgreSQL and get the execution plan:

explain (analyze,verbose,costs,buffers) select n from demo1 where n is not null order by n fetch first 1 row only;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..0.31 rows=1 width=4) (actual time=0.124..0.124 rows=1 loops=1)
Output: n
Buffers: shared hit=3
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..295.29 rows=10000 width=4) (actual time=0.124..0.124 rows=1 loops=1)
Output: n
Index Cond: (demo1.n IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=3
Planning time: 0.576 ms
Execution time: 0.143 ms

Here, the total cost of the query is lower than the total cost of the Index Only Scan, because we know we will not read all index entries. Then the total cost of the query (0.31) is based on the startup cost (0.29) of the index access. I suppose there is 0.01 for the cpu_tuple_cost but I expected to see the cost to get the first page because we cannot get a row without reading the whole page. My guess is that Postgres divides the total cost (295) by the number of rows (10000) and uses that as a per-row estimation. This makes sense for a lot of rows but underestimates the cost to get the first page.

In order to validate my guess, I force a Seq Scan to have a higher cost and fetch first 5 rows:

explain (analyze,verbose,costs,buffers) select n from demo1 where n is not null fetch first 5 row only ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.76 rows=5 width=4) (actual time=0.026..0.029 rows=5 loops=1)
Output: n
Buffers: shared hit=1
-> Seq Scan on public.demo1 (cost=0.00..1529.00 rows=10000 width=4) (actual time=0.022..0.024 rows=5 loops=1)
Output: n
Filter: (demo1.n IS NOT NULL)
Buffers: shared hit=1
Planning time: 1.958 ms
Execution time: 0.057 ms

My guess is: ( 1529.00 / 10000 ) * 5 = 0.7645 which is exactly the cost estimated for the Limit operation. This approximation does not take the page granularity into account.

MIN/MAX

The “order by n fetch first 1 row only” finally reads only one index entry, the first one, and returns the indexed value. We can get the same value with a “select max(N)” and Oracle has a special operation for that:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 29bsqfg69nudp, child number 0
-------------------------------------
select /*+ */ min(n) from demo1
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 2 |
| 2 | INDEX FULL SCAN (MIN/MAX)| DEMO1_N | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) MIN("N")[22] 2 - "N"[NUMBER,22]

This goes through the index branches (blevel=1 here in this small index so root is the first and only one branch) to the first leaf in order to get the value in the first entry. This has read 2 blocks here. The same can be done to get the last index entry in case we “select max(N)”.

Postgres do not show a special operation for it, but a plan which is very similar to the one we have seen above when fetching the first row: Index Only Scan, with a Limit:


explain (analyze,verbose,costs,buffers) select min(n) from demo1 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.31..0.32 rows=1 width=4) (actual time=0.123..0.124 rows=1 loops=1)
Output: $0
Buffers: shared hit=3
InitPlan 1 (returns $0)
-> Limit (cost=0.29..0.31 rows=1 width=4) (actual time=0.121..0.121 rows=1 loops=1)
Output: demo1.n
Buffers: shared hit=3
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..295.29 rows=10000 width=4) (actual time=0.119..0.119 rows=1 loops=1)
Output: demo1.n
Index Cond: (demo1.n IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=3
Planning time: 0.415 ms
Execution time: 0.140 ms

If we look at the ‘Index Only Scan’ we see exactly what I had at the top of this post with “select n from demo1 where n is not null order by n”.

Above it, there’s the Limit clause which is exactly the same as the one with the “fetch 1 row only” because the query planner understands that getting the MIN(N) is the same as getting the first value from the ordered index on N.

This is processed as a non-correlated subquery (query block), also called InitPlan. The result of it ($0) is used by the result with an additional cost of 0.01 for the cpu_tuple_cost in this additional step. I don’t really know the reason for this additional step here, but anyway, the cost is minimal. Basically, both Oracle and Postgres take advantage of the index structure to get the minimum – or first value – from the sorted index entries.

In this series, I’m running very simple queries in order to show how it works. In this post, we reached the minimum: one column and one row. The next post will finally select one additional column, which is not in the index.

 

Cet article Postgres vs. Oracle access paths V – FIRST ROWS and MIN/MAX est apparu en premier sur Blog dbi services.

24 HOP French edition 2017 – Session videos are available

Mon, 2017-08-07 10:09

The 2nd edition of 24HOP French Edition 2017 is over and we had great sessions about SQL Server and various topics (
SQL Server 2017 new features, Azure, PowerBI, High Availability , Linux, Hyper-convergence , Modeling …)

24hopsqlinuxha

If you did not attend to this event, you now have the opportunity to watch the videos of the different sessions. From my side, I had the chance to present SQL Server and High Availability on Linux.

Hope to see you next time!

 

Cet article 24 HOP French edition 2017 – Session videos are available est apparu en premier sur Blog dbi services.

MariaDB – Speed up your logical MariaDB backups with mydumper

Mon, 2017-08-07 07:08

Per default, MariaDB is shipped with a utility called mysqldump for logical backups. For more information, please take a look at the following link.

https://mariadb.com/kb/en/mariadb/mysqldump/

The mysqldump has advantages, e.g. it is easy to use and it is shipped with the standard MariaDB installation.  So, no additional installation is needed. However, it has also some disadvantages. E.g. it is single threaded and it is  writing to one big file, even with the latest version which is MariaDB 10.2.7 at the moment.

In case you want to dump out your data very quickly this can be your bottleneck. This is where the mydumper comes into play. The main feature of mydumper is that you can parallelize it. The mydumper utility uses 4 parallel threads per default if not otherwise specified.

./mydumper --help | grep threads
  -t, --threads               Number of threads to use, default 4

Another cool feature is compression.

./mydumper --help | grep compress
  -c, --compress              Compress output files

The biggest disadvantage is that mydumper is not delivered out of the box. You have to compile it yourself. To do so, simply follow the following steps:

Install the packages, which are needed for the mydumper compilation

# yum install gcc gcc-c++ glib2-devel mysql-devel zlib-devel \
  pcre-devel openssl-devel cmake

Unzip and compile mydumper

$ unzip mydumper-master.zip
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] unzip mydumper-master.zip
Archive:  mydumper-master.zip
e643528321f51e21a463156fbf232448054b955d
   creating: mydumper-master/
  inflating: mydumper-master/.bzrignore
  inflating: mydumper-master/CMakeLists.txt
  inflating: mydumper-master/README
  inflating: mydumper-master/binlog.c
  inflating: mydumper-master/binlog.h
   creating: mydumper-master/cmake/
   creating: mydumper-master/cmake/modules/
  inflating: mydumper-master/cmake/modules/CppcheckTargets.cmake
  inflating: mydumper-master/cmake/modules/FindGLIB2.cmake
  inflating: mydumper-master/cmake/modules/FindMySQL.cmake
  inflating: mydumper-master/cmake/modules/FindPCRE.cmake
  inflating: mydumper-master/cmake/modules/FindSphinx.cmake
  inflating: mydumper-master/cmake/modules/Findcppcheck.cmake
  inflating: mydumper-master/cmake/modules/Findcppcheck.cpp
  inflating: mydumper-master/common.h
  inflating: mydumper-master/config.h.in
   creating: mydumper-master/docs/
  inflating: mydumper-master/docs/CMakeLists.txt
   creating: mydumper-master/docs/_build/
  inflating: mydumper-master/docs/_build/conf.py.in
  inflating: mydumper-master/docs/_build/sources.cmake.in
  inflating: mydumper-master/docs/authors.rst
  inflating: mydumper-master/docs/compiling.rst
  inflating: mydumper-master/docs/examples.rst
  inflating: mydumper-master/docs/files.rst
  inflating: mydumper-master/docs/index.rst
  inflating: mydumper-master/docs/mydumper_usage.rst
  inflating: mydumper-master/docs/myloader_usage.rst
  inflating: mydumper-master/g_unix_signal.c
  inflating: mydumper-master/g_unix_signal.h
  inflating: mydumper-master/mydumper.c
  inflating: mydumper-master/mydumper.h
  inflating: mydumper-master/myloader.c
  inflating: mydumper-master/myloader.h
  inflating: mydumper-master/server_detect.c
  inflating: mydumper-master/server_detect.h

mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] mv mydumper-master mydumper-0.9.2
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] cd mydumper-0.9.2
mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1] cmake . -DCMAKE_INSTALL_PREFIX=/u00/app/mysql/product/tools/mydumper-0.9.2
-- The C compiler identification is GNU 4.8.5
-- The CXX compiler identification is GNU 4.8.5
-- Check for working C compiler: /usr/bin/cc
-- Check for working C compiler: /usr/bin/cc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Using mysql-config: /u00/app/mysql/product/mysql-5.6.37/bin/mysql_config
-- Found MySQL: /u00/app/mysql/product/mysql-5.6.37/include, /u00/app/mysql/product/mysql-5.6.37/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so
-- Found ZLIB: /usr/lib64/libz.so (found version "1.2.7")
-- Found PkgConfig: /usr/bin/pkg-config (found version "0.27.1")
-- checking for one of the modules 'glib-2.0'
-- checking for one of the modules 'gthread-2.0'
-- checking for module 'libpcre'
--   found libpcre, version 8.32
-- Found PCRE: /usr/include
1
-- ------------------------------------------------
-- MYSQL_CONFIG = /u00/app/mysql/product/mysql-5.6.37/bin/mysql_config
-- CMAKE_INSTALL_PREFIX = /u00/app/mysql/product/tools/mydumper-0.9.2
-- BUILD_DOCS = ON
-- WITH_BINLOG = OFF
-- RUN_CPPCHECK = OFF
-- Change a values with: cmake -D<Variable>=<Value>
-- ------------------------------------------------
--
-- Configuring done
-- Generating done
-- Build files have been written to: /u00/app/mysql/product/tools/mydumper-0.9.2

HINT: In case you don’t have Sphinx installed, you can use the -DBUILD_DOCS=OFF option. Sphinx is a documentation generator. For more information see http://sphinx-doc.org/

mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1] make
Scanning dependencies of target mydumper
[ 16%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
[ 33%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
[ 50%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
Linking C executable mydumper
[ 50%] Built target mydumper
Scanning dependencies of target myloader
[ 66%] Building C object CMakeFiles/myloader.dir/myloader.c.o
Linking C executable myloader
[ 66%] Built target myloader
Scanning dependencies of target doc_sources
[ 66%] Built target doc_sources
Scanning dependencies of target doc_html
[ 83%] Building HTML documentation with Sphinx
/u00/app/mysql/product/tools/mydumper-0.9.2/docs/_sources/files.rst:39: WARNING: unknown option: mydumper --schemas
WARNING: html_static_path entry '/u00/app/mysql/product/tools/mydumper-0.9.2/docs/_static' does not exist
[ 83%] Built target doc_html
Scanning dependencies of target doc_man
[100%] Building manual page with Sphinx
[100%] Built target doc_man

mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1] make install
[ 50%] Built target mydumper
[ 66%] Built target myloader
[ 66%] Built target doc_sources
[ 83%] Building HTML documentation with Sphinx
[ 83%] Built target doc_html
[100%] Building manual page with Sphinx
[100%] Built target doc_man
Install the project...
-- Install configuration: ""
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/bin/mydumper
-- Removed runtime path from "/u00/app/mysql/product/tools/mydumper-0.9.2/bin/mydumper"
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/bin/myloader
-- Removed runtime path from "/u00/app/mysql/product/tools/mydumper-0.9.2/bin/myloader"
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/authors.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/compiling.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/examples.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/files.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/index.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/mydumper_usage.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/myloader_usage.rst
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/authors.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/authors.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/compiling.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/examples.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/files.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/index.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/mydumper_usage.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_sources/myloader_usage.txt
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/compiling.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/examples.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/files.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/index.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/mydumper_usage.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/myloader_usage.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/genindex.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/search.html
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/pygments.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/ajax-loader.gif
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/basic.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/comment-bright.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/comment-close.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/comment.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/doctools.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/down-pressed.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/down.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/file.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/jquery-1.11.1.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/jquery.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/minus.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/plus.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/searchtools.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/underscore-1.3.1.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/underscore.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/up-pressed.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/up.png
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/websupport.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/classic.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/sidebar.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/_static/default.css
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/.buildinfo
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/searchindex.js
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/doc/mydumper/html/objects.inv
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/man/man1/mydumper.1
-- Installing: /u00/app/mysql/product/tools/mydumper-0.9.2/share/man/man1/myloader.1
mysql@mysql01:/u00/app/mysql/product/tools/mydumper-0.9.2/ [mysqld1]

mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1] ln -s mydumper-0.9.2 mydumper
mysql@mysql01:/u00/app/mysql/product/tools/ [mysqld1]

If compiled correctly, you will see two new binaries created. The mydumper and the myloader.

mysql@mysql01:/u00/app/mysql/product/tools/mydumper/bin/ [mysqld1] ls -l
total 280
-rwxr-xr-x 1 mysql mysql 218808 Aug  7 07:25 mydumper
-rwxr-xr-x 1 mysql mysql  63448 Aug  7 07:25 myloader

And besides that, you will have the documentation compiled as html in the ../mydumper-0.9.2/share/doc/mydumper/html folder.

MyDumper HTML

Ok. Let’s see now mysqldump vs. mydumper in action. My sample database is about 10G in size. Of course, the bigger the database is, the bigger the performance impact of mydumper will be.

First, we dump out all databases with mysqldump (without and with compression) and record the time.

-- no compression 

mysql@mysql01:/u00/app/mysql/ [mysqld1] mysqldump --version 
mysqldump  Ver 10.16 Distrib 10.2.7-MariaDB, for Linux (x86_64) 

mysql@mysql01:/u00/app/mysql/ [mysqld1] time mysqldump --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --single-transaction --all-databases > mysqldump.sql 

real    3m38.94s 
user    1m29.11s 
sys     0m11.85s 

mysql@mysql01:/u00/app/mysql/ [mysqld1] ls -lh mysqldump.sql     
-rw-r--r-- 1 mysql mysql 10G Aug  7 11:33 mysqldump.sql 

-- compression 

mysql@mysql01:/u00/app/mysql/ [mysqld1] time mysqldump --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --single-transaction --all-databases | gzip > mysqldump.sql.gz 

real    4m43.75s 
user    4m55.25s 
sys     0m10.65s 

mysql@mysql01:/u00/app/mysql/ [mysqld1] ls -lh mysqldump.sql.gz 
-rw-r--r-- 1 mysql mysql 3.1G Aug  7 11:55 mysqldump.sql.gz

The uncompressed dump took about 3.39 Minute (10G) and the compressed one about 4.44 Minute (3.1G).

Now we repeat it with mydumper.

-- no compression 

mysql@mysql01:/u00/app/mysql/ [mysqld1] time /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --trx-consistency-only --threads=6 --outputdir=/mydump/mysqld1/mydumper_mysqld1 

real    1m22.44s 
user    0m41.17s 
sys     0m7.31s 

mysql@mysql01:/u00/app/mysql/ [mysqld1] du -hs /mydump/mysqld1/mydumper_mysqld1/ 
10G     mydumper_mysqld1/ 

-- compression 

mysql@mysql01:/u00/app/mysql/ [mysqld1] time /u00/app/mysql/product/tools/mydumper/bin/mydumper --defaults-file=/u00/app/mysql/admin/mysqld1/.my.cnf --trx-consistency-only --threads=6 --compress --outputdir=/mydump/mysqld1/mydumper_mysqld1 

real    3m4.99s 
user    3m54.94s 
sys     0m5.11s 

mysql@mysql01:/u00/app/mysql/ [mysqld1] du -hs /mydump/mysqld1/mydumper_mysqld1/ 
3.1G    mydumper_mysqld1/

With mydumper, the uncompressed dump took about 1.23 Minute (10G) and the compressed one about 3.04 Minute (3.1G).

As you can see in the results, the uncompressed dump was about 3 times faster with mydumper. The compressed mydumper export only about 30% faster. The reason for the compressed export being only 30% faster might be due to the fact that I have only 2 virtual cpu’s assigned to my VM.

Conclusion

MyDumper is a great tool that can speed up your database exports quite dramatically. Take a look at it. It might be worth it.

 

 

 

Cet article MariaDB – Speed up your logical MariaDB backups with mydumper est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths IV – Order By and Index

Sat, 2017-08-05 15:00

I realize that I’m talking about indexes in Oracle and Postgres, and didn’t mention yet the best website you can find about indexes, with concepts and examples for all RDBMS: http://use-the-index-luke.com. You will probably learn a lot about SQL design. Now let’s continue on execution plans with indexes.

As we have seen two posts ago, an index can be used even with a 100% selectivity (all rows), when we don’t filter any rows. Oracle has INDEX FAST FULL SCAN which is the fastest, reading blocks sequentially as they come. But this doesn’t follow the B*Tree leaves chain and does not return the rows in the order of the index. However, there is also the possibility to read the leaf blocks in the index order, with INDEX FULL SCAN and random reads instead of multiblock reads.
It is similar to the Index Only Scan of Postgres except that there is no need to get to the table to filter out uncommitted changes. Oracle reads the transaction table to get the visibility information, and goes to undo records if needed.

The previous post had a query with a ‘where n is not null’ predicate to be sure having all index entries in Oracle indexes and we will continue on this by adding an order by.

For this post, I’ve increased the size of the column N in the Oracle table, by adding 1/3 to each number. I did this for this post only, and for the Oracle table only. The index on N is now 45 blocks instead of 20. The reason is to show what happens when the cost of ‘order by’ is high. I didn’t change the Postgres table because there is only one way to scan the index, where result is always sorted.

Oracle Index Fast Full Scan vs. Index Full Scan


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dbck3rgnqbakg, child number 0
-------------------------------------
select /*+ */ n from demo1 where n is not null order by n
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 46 (100)| 10000 |00:00:00.01 | 48 |
| 1 | INDEX FULL SCAN | DEMO1_N | 1 | 10000 | 46 (0)| 10000 |00:00:00.01 | 48 |
---------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Index Full Scan, the random read version of index read is chosen here by the Oracle optimizer because we want the result on the column N and the index can provide this without additional sorting.

We can force the optimizer to do multiblock reads, with INDEX_FFS hint:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID anqfbf5caat2a, child number 0
-------------------------------------
select /*+ index_ffs(demo1) */ n from demo1 where n is not null order
by n
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 82 (100)| 10000 |00:00:00.01 | 51 | | | |
| 1 | SORT ORDER BY | | 1 | 10000 | 82 (2)| 10000 |00:00:00.01 | 51 | 478K| 448K| 424K (0)|
| 2 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 14 (0)| 10000 |00:00:00.01 | 51 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "N"[NUMBER,22] 2 - "N"[NUMBER,22]

The estimated cost is higher: the index read is cheaper (cost=14 instead of 46) but then the sort operation brings this to 82. We can see additional columns in the execution plan here because the sorting operation needs a workarea in memory (estimated 478K, actually 424K used during the execution). Note that the multiblock read has a few blocks of overhead (reads 51 blocks instead of 48) because it has to read the segment header to identify the extents to scan.

Postgres Index Only Scan

In PostgreSQL there’s only one way to scan indexes: random reads by following the chain of leaf blocks. This returns the rows in the order of the index and does not require an additional sort:


explain (analyze,verbose,costs,buffers) select n from demo1 where n is not null order by n ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..295.29 rows=10000 width=4) (actual time=0.125..1.277 rows=10000 loops=1)
Output: n
Index Cond: (demo1.n IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=30
Planning time: 0.532 ms
Execution time: 1.852 ms

In the previous posts, we have seen a cost of cost=0.29..270.29 for the Index Only Scan. Here we have an additional cost of 25 for the cpu_operator_cost because I’ve added the ‘where n is not null’. As the default constant is 0.0025 this is the query planner estimating to evaluate it for 10000 rows.

First Rows

The Postgres cost always shows two values. The first one is the startup cost: the cost just before being able to return the first row. Some operations have a very small startup cost, others have some blocking operations that must finish before sending their first result rows. Here, as we have no sort operation, the first row retrieved from the index can be returned immediately and the startup cost is small: 0.29
In Oracle you can see the initial cost by optimizing the plan to retrieve the first row, with the FIRST_ROWS() hint:


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0fjk9vv4g1q1w, child number 0
-------------------------------------
select /*+ first_rows(1) */ n from demo1 where n is not null order by
n
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 10000 |00:00:00.01 | 48 |
| 1 | INDEX FULL SCAN | DEMO1_N | 1 | 10000 | 2 (0)| 10000 |00:00:00.01 | 48 |
---------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

The actual number of blocks read (48) is the same as before because I finally fetched all rows, but the cost is small because it was estimated for two rows only. Of course, we can also tell Postgres or Oracle that we want only the first rows. This is for the next post.

Character strings

The previous example is an easy one because the column N is a number and both Oracle and Postgres stores number in a binary format that follows the same order as the numbers. But that’s different with character strings. If you are not in America, there is a very little chance that the order you want to see follows the ASCII order. Here I’ve run a similar query but using the column X instead of N, which is a text (VARCHAR2 in Oracle):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fsqk4fg1t47v5, child number 0
-------------------------------------
select /*+ */ x from demo1 where x is not null order by x
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2493 (100)| 10000 |00:00:00.27 | 1644 | 18 | | | |
| 1 | SORT ORDER BY | | 1 | 10000 | 2493 (1)| 10000 |00:00:00.27 | 1644 | 18 | 32M| 2058K| 29M (0)|
|* 2 | INDEX FAST FULL SCAN| DEMO1_X | 1 | 10000 | 389 (0)| 10000 |00:00:00.01 | 1644 | 18 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) NLSSORT("X",'nls_sort=''FRENCH''')[2000], "X"[VARCHAR2,1000] 2 - "X"[VARCHAR2,1000]

I have created an index on X, and as you can see it can be used to get all X values, but with an Index Fast Full Scan, the multiblock index only access which is fast but does not return rows in the order of the index. And then a sort operation is applied. I can force an Index Full Scan with INDEX() hint but the sort will still have to be done.

The reason can be seen in the column projection note. My Oracle client application is running on a laptop where the OS is in French and Oracle returns the setting according to what the end-user can expect. This is National Language Support. An Oracle database can be accessed by users all around the world and they will see ordered lists, date format, decimal separators,… according to their country and language.

ORDER BY … COLLATE …

My databases has been created in a system which is in English. In Postgres we can get results sorted in French with the COLLATE option of ORDER BY:


explain (analyze,verbose,costs,buffers) select x from demo1 where x is not null order by x collate "fr_FR" ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=5594.17..5619.17 rows=10000 width=1036) (actual time=36.163..37.254 rows=10000 loops=1)
Output: x, ((x)::text)
Sort Key: demo1.x COLLATE "fr_FR"
Sort Method: quicksort Memory: 1166kB
Buffers: shared hit=59
-> Index Only Scan using demo1_x on public.demo1 (cost=0.29..383.29 rows=10000 width=1036) (actual time=0.156..1.559 rows=10000 loops=1)
Output: x, x
Index Cond: (demo1.x IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=52
Planning time: 0.792 ms
Execution time: 38.264 ms

Same idea here as in Oracle: there is an additional sort operation, which is a blocking operation that needs to be completed before being able to return the first row.

The detail of the cost is the following:

  • The index on the column X has 52 blocks witch is estimated at cost=208 (random_page_cost=4)
  • We have 10000 index entries to process, estimated at cost=50 (cpu_index_tuple_cost=0.005)
  • We have 10000 result rows to process, estimated at cost=100 (cpu_tuple_cost=0.01)
  • We have evaluated 10000 ‘is not null’ conditions, estimated at cost=25 (cpu_operator_cost=0.0025)

In Oracle we can use the same COLLATE syntax, but the name of the language is different, consistent across platforms rather than useing the OS one:


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 82az4syppyndf, child number 0
-------------------------------------
select /*+ */ x from demo1 where x is not null order by x collate "French"
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2493 (100)| 10000 |00:00:00.28 | 1644 | | | |
| 1 | SORT ORDER BY | | 1 | 10000 | 2493 (1)| 10000 |00:00:00.28 | 1644 | 32M| 2058K| 29M (0)|
|* 2 | INDEX FAST FULL SCAN| DEMO1_X | 1 | 10000 | 389 (0)| 10000 |00:00:00.01 | 1644 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) NLSSORT("X" COLLATE "French",'nls_sort=''FRENCH''')[2000], "X"[VARCHAR2,1000] 2 - "X"[VARCHAR2,1000]

In Oracle, we do not need to use the COLLATE option. The language can be set for the session (NLS_LANGUAGE=’French’) or from the environment (NLS_LANG=’=French_.’). Oracle can share cursors across sessions (to avoid to waste resource compiling and optimizing the same statements used by different sessions) but will not share execution plans among different NLS environments because, as we have seen, the plan can be different. Postgres do not have to manage that because each PREPARE statement does a full compilation and optimization. There is no cursor sharing in Postgres.

Indexing for different languages

We have seen in the Oracle execution plan Column Projection Information that an NLSSORT operation is applied on the column to get a value that follows the collation order of the language. We have seen in the previous post that we can index a function on a column. Then we have the possibility to create an index for different languages. The following index will be used to avoid sort from French users:

create index demo1_x_fr on demo1(nlssort(x,'NLS_SORT=French'));

Since 12cR2 we can create the same with de collate syntax:

create index demo1_x_fr on demo1(x collate "French");

Both syntaxes create the same index, which can be used by queries with ORDER BY … COLLATE or with session that set the NLS_LANGUAGE:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 82az4syppyndf, child number 0
-------------------------------------
select /*+ */ x from demo1 where x is not null order by x collate "French"
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4770 (100)| 10000 |00:00:00.02 | 4772 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DEMO1 | 1 | 10000 | 4770 (1)| 10000 |00:00:00.02 | 4772 |
| 2 | INDEX FULL SCAN | DEMO1_X_FR | 1 | 10000 | 3341 (1)| 10000 |00:00:00.01 | 3341 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "X"[VARCHAR2,1000] 2 - "DEMO1".ROWID[ROWID,10], "DEMO1"."SYS_NC00004$"[RAW,2000]

There’s no sort operation here as the INDEX FULL SCAN returns the rows in order.

PostgreSQL has the same syntax:

create index demo1_x_fr on demo1(x collate "fr_FR");

and then the query can use this index and bypass the sort operation:

explain (analyze,verbose,costs,buffers) select x from demo1 where x is not null order by x collate "fr_FR" ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_x_fr on public.demo1 (cost=0.29..383.29 rows=10000 width=1036) (actual time=0.190..1.654 rows=10000 loops=1)
Output: x, x
Index Cond: (demo1.x IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=32 read=20
Planning time: 1.049 ms
Execution time: 2.304 ms

Avoiding a sort operation can really improve the performance of queries in two ways: save the resources required by a sort operation (which will have to spill to disk when the workarea do not fit in memory) and avoid a blocking operation and then be able to return the first rows quickly.

We have seen how indexes can be used to access a subset of columns from a smaller structure, and how they can be used to access a sorted version of the rows. Future posts will show how the index access is used to quickly filter a subset of rows. But for the moment I’ll continue on this blocking operation. We have seen a lot of Postgres costs, and they have two values (startup cost and total cost). More on startup cost in the next post.

 

Cet article Postgres vs. Oracle access paths IV – Order By and Index est apparu en premier sur Blog dbi services.

Encryption of shell scripts

Sat, 2017-08-05 07:52

In this blog, I will talk about the encryption of files and in particular the encryption of a shell script because that was my use case. Before starting, some people may say/think that you shouldn’t encrypt any scripts and I globally agree with that BUT I still think that there might be some exceptions. I will not debate this further but I found the encryption subject very interesting so I thought I would write a small blog with my thoughts.

 

Encryption?

So, when we talk about encryption, what is it exactly? There are actually two not-so-different concepts that people often mix up: encryption and obfuscation. The encryption is a technique to keep an information confidential by changing its form, which becomes unreadable. The obfuscation, on the other hand, refers to the protection of something by trying to hide it, convert it into something more difficult to read but it’s not completely unreadable. The main difference is that if you know what technique was used to encrypt something, you cannot decrypt it without the key while you can remove the obfuscation if you know how it was done.

The reason why I’m including this small paragraph in this blog is because when I was searching for a way to encrypt a shell script in Linux, I read a LOT of blogs and websites that just got it wrong… The problem with encrypted shell scripts is that at some points, the Operating System will need to know which commands should be executed. So, at some point, it will need to be decrypted.

 

Shell script

So, let’s start with the creation a test shell script that I will use for the rest of this blog. I’m creating a small, very simple, test script which contains a non-encrypted password that I need to enter correctly in order to get an exit code of 0. If the password is wrong, after 3 tries, I should get an exit code of 1. Please note that if the shell script contains interactions, then you need to use the redirection from tty (“< /dev/tty”) like I did in my example.

Below I’m displaying the content of this script and using it, without encryption, to show you the output. Please note that in my scripts, I included colors (green for INFO and OK, yellow for WARN and red for ERROR messages) which aren’t displayed in the blog… Sorry about that but I can’t add colors to the blog unfortunately!

[morgan@linux_server_01 ~]$ cat test_script.sh
#!/bin/bash
#
# File: test_script.sh
# Purpose: Shell script to test the encryption solutions
# Author: Morgan Patou (dbi services)
# Version: 1.0 29-Jul-2017
#
###################################################

### Defining colors & execution folder
red_c="33[31m"
yellow_c="33[33m"
green_c="33[32m"
end_c="33[m"
script_folder=`which ${0}`
script_folder=`dirname ${script_folder}`

### Verifying password
script_password="TestPassw0rd"
echo
echo -e "${green_c}INFO${end_c} - This file is a test script to test the encryption solutions."
echo -e "${green_c}INFO${end_c} - Entering the correct password will return an exit code of 0."
echo -e "${yellow_c}WARN${end_c} - Entering the wrong password will return an exit code of 1."
echo
retry_count=0
retry_max=3
while [ "${retry_count}" -lt "${retry_max}" ]; do
  echo
  read -p "  ----> Please enter the password to execute this script: " entered_password < /dev/tty
  if [[ "${entered_password}" == "${script_password}" ]]; then
    echo -e "${green_c}OK${end_c} - The password entered is the correct one."
    exit 0
  else
    echo -e "${yellow_c}WARN${end_c} - The password entered isn't the correct one. Please try again."
    retry_count=`expr ${retry_count} + 1`
  fi
done

echo -e "${red_c}ERROR${end_c} - Too many failed attempts. Exiting."
exit 1

[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ chmod 700 test_script.sh
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ ./test_script.sh

INFO - This file is a test script to test the encryption solutions.
INFO - Entering the correct password will return an exit code of 0.
WARN - Entering the wrong password will return an exit code of 1.


  ----> Please enter the password to execute this script: Password1
WARN - The password entered isn't the correct one. Please try again.

  ----> Please enter the password to execute this script: Password2
WARN - The password entered isn't the correct one. Please try again.

  ----> Please enter the password to execute this script: Password3
WARN - The password entered isn't the correct one. Please try again.
ERROR - Too many failed attempts. Exiting.
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ echo $?
1
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ ./test_script.sh

INFO - This file is a test script to test the encryption solutions.
INFO - Entering the correct password will return an exit code of 0.
WARN - Entering the wrong password will return an exit code of 1.


  ----> Please enter the password to execute this script: TestPassw0rd
OK - The password entered is the correct one.
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ echo $?
0
[morgan@linux_server_01 ~]$

 

As you can see above, the script is doing what I expect it to do so that’s fine.

 

SHc?

So, what is SHc? Is it really a way to encrypt your shell scripts?

Simple answer: I would NOT use SHc for that. I don’t have anything against SHc, this is actually a utility that might be useful but from my point of view, it’s clearly not a good solution for encrypting a shell script.

 

SHc is a utility (check its website) that – from a shell script – will create a C source code which represents it using a RC4 algorithm. This C source code contains a random structure as well as the decryption method. Then it is compiled to create a binary file. The problem with SHc is that the binary file contains the original shell script (encrypted) but also the decryption materials because this is needed to execute it. So, let’s install this utility:

[morgan@linux_server_01 ~]$ wget http://www.datsi.fi.upm.es/~frosal/sources/shc-3.8.9b.tgz
--2017-07-29 14:10:14--  http://www.datsi.fi.upm.es/~frosal/sources/shc-3.8.9b.tgz
Resolving www.datsi.fi.upm.es... 138.100.9.22
Connecting to www.datsi.fi.upm.es|138.100.9.22|:80... connected.
Proxy request sent, awaiting response... 200 OK
Length: 20687 (20K) [application/x-gzip]
Saving to: “shc-3.8.9b.tgz”

100%[===================================================================>] 20,687      --.-K/s   in 0.004s

2017-07-29 14:10:14 (5.37 MB/s) - “shc-3.8.9b.tgz” saved [20687/20687]

[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ tar -xvzf shc-3.8.9b.tgz
shc-3.8.9b/CHANGES
shc-3.8.9b/Copying
shc-3.8.9b/match
shc-3.8.9b/pru.sh
shc-3.8.9b/shc-3.8.9b.c
shc-3.8.9b/shc.c
shc-3.8.9b/shc.1
shc-3.8.9b/shc.README
shc-3.8.9b/shc.html
shc-3.8.9b/test.bash
shc-3.8.9b/test.csh
shc-3.8.9b/test.ksh
shc-3.8.9b/makefile
shc-3.8.9b/testit
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ cd shc-3.8.9b/
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ make
cc -Wall  shc.c -o shc
***     Do you want to probe shc with a test script?
***     Please try...   make test
[morgan@linux_server_01 shc-3.8.9b]$

 

At this point, I only built the utility locally because I will be removing it shortly. Now, let’s “encrypt” the file using shc:

[morgan@linux_server_01 shc-3.8.9b]$ cp ../test_script.sh ./
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ ls test_script*
test_script.sh
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ ./shc -f test_script.sh
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ ls test_script*
test_script.sh  test_script.sh.x  test_script.sh.x.c
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ # Removing the C source code and original script
[morgan@linux_server_01 shc-3.8.9b]$ rm test_script.sh test_script.sh.x.c
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ # Renaming the "encrypted" file to .bin
[morgan@linux_server_01 shc-3.8.9b]$ mv test_script.sh.x test_script.bin
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ ls test_script*
test_script.bin
[morgan@linux_server_01 shc-3.8.9b]$

 

So above, I used shc and it created two files:

  • test_script.sh.x => This is the C compiled file which can then be executed. I renamed it to test_script.bin to really see the differences between the files
  • test_script.sh.x.c => This is the C source code which I removed since I don’t need it

 

At this point, if you try to view the content of the .bin file (previously test_script.sh.x), you will not be able to see the real content and you will see something that looks like a real .bin executable. To see its “binary” content, you can use the “strings” command which will display all readable (printable) words from the file and you will see that we cannot see the password or any commands from the original shell script. So, at first look, that seems to be a success, the shell script seems to be encrypted:

[morgan@linux_server_01 shc-3.8.9b]$ strings test_script.bin
/lib64/ld-linux-x86-64.so.2
__gmon_start__
libc.so.6
sprintf
perror
__isoc99_sscanf
fork
...
EcNB
,qIB`^
gLSI
U)L&
fX4u
j[5,
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ ./test_script.bin
 
INFO - This file is a test script to test the encryption solutions.
INFO - Entering the correct password will return an exit code of 0.
WARN - Entering the wrong password will return an exit code of 1.
 
 
  ----> Please enter the password to execute this script: Password1
WARN - The password entered isn't the correct one. Please try again.
 
  ----> Please enter the password to execute this script: TestPassw0rd
OK - The password entered is the correct one.
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ echo $?
0
[morgan@linux_server_01 shc-3.8.9b]$

 

So, what is the issue with SHc? Why am I saying that this isn’t a suitable encryption solution? Well that’s because you can always just strip the text out of the file or substitute the normal shell to another one in order to grab the text when it runs. There are also several projects on GitHub (like UnSHc) which will allow you to retrieve the original content of the shell script and to revert the changes done by SHc. This works because the content of the bin file is predictable and can be analysed in order to decrypt it. So, that’s not really a good solution I would say.

There are a lot of ways to see the original content of a file encrypted by SHc. One of them being just checking the list of processes and you will see that the original shell script is actually passed as a parameter to the binary file in this format: ./test_script.bin -c   <<<a lot of spaces>>>    <<<script_unencrypted_newlines_separated_by_’?’>>>. See below my example:

[morgan@linux_server_01 shc-3.8.9b]$ ./test_script.bin& (ps -ef | grep "test_script.bin" | grep -v grep > test_decrypt_content.sh)
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ # The real file is in 1 line only. For readability on the blog, I split that in several lines 
[morgan@linux_server_01 shc-3.8.9b]$ cat test_decrypt_content.sh
405532   20125  2024  0 16:18 pts/3    00:00:00 ./test_script.bin -c                                                                              
                                                                                                                                                  
                                                                                                                                                  
                                                                                                                                                  
                                                                                                                                                  
                                                                                                                                                  
#!/bin/bash?#?# File: test_script.sh?# Purpose: Shell script to test the encryption solutions?# Author: Morgan Patou (dbi services)?# Version: 1.029-Jul-2017?
#?###################################################??### Defining colors & execution folder?red_c="33[31m"?yellow_c="33[33m"?green_c="33[32m"?end_c="\
033[m"?script_folder=`which ${0}`?script_folder=`dirname ${script_folder}`??### Verifying password?script_password="TestPassw0rd"?echo?echo -e "${green_c}INFO
${end_c} - This file is a test script to test the encryption solutions."?echo -e "${green_c}INFO${end_c} - Entering the correct password will return an exit c
ode of 0."?echo -e "${yellow_c}WARN${end_c} - Entering the wrong password will return an exit code of 1."?echo?retry_count=0?retry_max=3?while [ "${retry_coun
t}" -lt "${retry_max}" ]; do?  echo?  read -p "  ----> Please enter the password to execute this script: " entered_password < /dev/tty?  if [[ "${entered_pass
word}" == "${script_password}" ]]; then?    echo?    echo -e "${green_c}OK${end_c} - The password entered is the correct one."?    exit 0?  else?    echo -e "
${yellow_c}WARN${end_c} - The password entered isn't the correct one. Please try again."?    retry_count=`expr ${retry_count} + 1`?  fi?done??echo -e "${red_c
}ERROR${end_c} - Too many failed attempts. Exiting."?exit 1?? ./test_script.bin
[morgan@linux_server_01 shc-3.8.9b]$

 

As you can see above, the whole content of the original shell script is displayed in the “ps” command. Not very hard to find out what is the original content… With a pretty simple command, we can even reformat the original file:

[morgan@linux_server_01 shc-3.8.9b]$ sed -i -e 's,?,\n,g' -e 's,.*     [[:space:]]*,,' test_decrypt_content.sh
[morgan@linux_server_01 shc-3.8.9b]$
[morgan@linux_server_01 shc-3.8.9b]$ cat test_decrypt_content.sh
#!/bin/bash
#
# File: test_script.sh
# Purpose: Shell script to test the encryption solutions
# Author: Morgan Patou (dbi services)
# Version: 1.0 29-Jul-2017
#
###################################################

### Defining colors & execution folder
red_c="33[31m"
yellow_c="33[33m"
green_c="33[32m"
end_c="33[m"
script_folder=`which ${0}`
script_folder=`dirname ${script_folder}`

### Verifying password
script_password="TestPassw0rd"
echo
echo -e "${green_c}INFO${end_c} - This file is a test script to test the encryption solutions."
echo -e "${green_c}INFO${end_c} - Entering the correct password will return an exit code of 0."
echo -e "${yellow_c}WARN${end_c} - Entering the wrong password will return an exit code of 1."
echo
retry_count=0
retry_max=3
while [ "${retry_count}" -lt "${retry_max}" ]; do
  echo
  read -p "  ----> Please enter the password to execute this script: " entered_password < /dev/tty
  if [[ "${entered_password}" == "${script_password}" ]]; then
    echo -e "${green_c}OK${end_c} - The password entered is the correct one."
    exit 0
  else
    echo -e "${yellow_c}WARN${end_c} - The password entered isn't the correct one. Please try again."
    retry_count=`expr ${retry_count} + 1`
  fi
done

echo -e "${red_c}ERROR${end_c} - Too many failed attempts. Exiting."
exit 1

 ./test_script.bin
[morgan@linux_server_01 shc-3.8.9b]$

 

And voila, with two very simple command, it is possible to retrieve the original file with its original formatting too (just remove the final line which is the call of the script itself). Please also note that if the original script contains some ‘?’ characters, they will also be replaced with a newline but that’s spotted pretty easily. With Shell options, you can also just ask your shell to print all commands that it executes so again without even additional commands you can see the content of the binary file.

 

What solution then?

For this section, I will re-use the same un-encrypted shell script (test_script.sh). So, what can be done to really protect a shell script? Well there are no perfect solutions because like I said previously, at some point, the OS will need to know which commands should be executed and for that purpose, it needs to be decrypted. There are a few ways to encrypt a shell script but the simplest would probably be to use openssl because it’s quick, it’s free and it’s portable without having to install anything since openssl is usually already there on Linux. Also, it allows you to choose the encryption algorithm you want to use. To encrypt the base file, I created a small shell script which I named “encrypt_script.sh”. This shell script takes an input file which is the un-encrypted original file and a second parameter is the output file which will contain the encryption:

[morgan@linux_server_01 shc-3.8.9b]$ cd ..
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ ls
encrypt_script.sh shc-3.8.9b  shc-3.8.9b.tgz  test_script.sh
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ rm -rf shc-3.8.9b*
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ cat encrypt_script.sh
#!/bin/bash
#
# File: encrypt_script.sh
# Purpose: Script to encrypt a shell script and provide the framework around it for execution
# Author: Morgan Patou (dbi services)
# Version: 1.0 26/03/2016
#
###################################################

### Defining colors & execution folder
green_c="33[32m"
end_c="33[m"
script_folder="`which ${0}`"
script_folder="`dirname ${script_folder}`"
encryption="aes-256-cbc"

### Help
if [[ ${#} != 2 ]]; then
  echo -e "`basename ${0}`: usage: ${green_c}`basename ${0}`${end_c} <${green_c}shell_script_to_encrypt${end_c}> <${green_c}encrypted_script${end_c}>"
  echo -e "\t<${green_c}shell_script_to_encrypt${end_c}>  : Name of the shell script to encrypt. Must be placed under '${green_c}${script_folder}${end_c}'"
  echo -e "\t<${green_c}encrypted_script${end_c}>         : Name of the encrypted script to be created. The file will be created under '${green_c}${script_folder}${end_c}'"
  echo
  exit 1
else
  shell_script_to_encrypt="${1}"
  encrypted_script="${2}"
fi

### Encrypting the input file into a temp file
openssl enc -e -${encryption} -a -A -in "${script_folder}/${shell_script_to_encrypt}" > "${script_folder}/${shell_script_to_encrypt}.txt"

### Creating the output script with the requested name and containing the content to decrypt it
echo "#!/bin/bash" > "${script_folder}/${encrypted_script}"
echo "# " >> "${script_folder}/${encrypted_script}"
echo "# File: ${encrypted_script}" >> "${script_folder}/${encrypted_script}"
echo "# Purpose: Script containing the encrypted version of ${shell_script_to_encrypt} (this file has been generated using `basename ${0}`)" >> "${script_folder}/${encrypted_script}"
echo "# Author: Morgan Patou (dbi services)" >> "${script_folder}/${encrypted_script}"
echo "# Version: 1.0 26/03/2016" >> "${script_folder}/${encrypted_script}"
echo "# " >> "${script_folder}/${encrypted_script}"
echo "###################################################" >> "${script_folder}/${encrypted_script}"
echo "" >> "${script_folder}/${encrypted_script}"
echo "#Storing the encrypted script in a variable" >> "${script_folder}/${encrypted_script}"
echo "encrypted_script=\"`cat "${script_folder}/${shell_script_to_encrypt}.txt"`\"" >> "${script_folder}/${encrypted_script}"
echo "" >> "${script_folder}/${encrypted_script}"
echo "#Decrypting the encrypted script and executing it" >> "${script_folder}/${encrypted_script}"
echo "echo \"\${encrypted_script}\" | openssl enc -d -${encryption} -a -A | sh -" >> "${script_folder}/${encrypted_script}"
echo "" >> "${script_folder}/${encrypted_script}"

### Removing the temp file and setting the output file to executable
rm "${script_folder}/${shell_script_to_encrypt}.txt"
chmod 700 "${script_folder}/${encrypted_script}"
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ ./encrypt_script.sh
encrypt_script.sh: usage: encrypt_script.sh <shell_script_to_encrypt> <encrypted_script>
        <shell_script_to_encrypt>  : Name of the shell script to encrypt. Must be placed under '/home/morgan'
        <encrypted_script>         : Name of the encrypted script to be created. The file will be created under '/home/morgan'

[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ ./encrypt_script.sh test_script.sh encrypted_test_script.sh
enter aes-256-cbc encryption password:
Verifying - enter aes-256-cbc encryption password:
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$
[morgan@linux_server_01 ~]$ # The real variable "encrypted_script" below is in 1 line only. For readability, I split that in several lines
[morgan@linux_server_01 ~]$ cat encrypted_test_script.sh
#!/bin/bash
#
# File: encrypted_test_script.sh
# Purpose: Script containing the encrypted version of test_script.sh (this file has been generated using encrypt_script.sh)
# Author: Morgan Patou
# Version: 1.0 26/03/2016
#
###################################################

#Storing the encrypted script in a variable
encrypted_script="U2FsdGVkX18QaIvqrQ27FQE8fNhJi2Izi9zRHwANEEt4WJkA3gQzOkrPOF+JYpIEFuvjweL2Eq02vr0MhkjMXIGXYlLipQ7U8TG912/9LdUOYlEx7YV4/1g9enBfZc2gBRHcGL6XW7oMih3wexGNrrq3J5Ys+mDgrmKDLJ75aU6v87iIPFi2ZfFx2NchAc4tHHDQ8gcZFLMByCkWwPZoicx8ODgUstNLRHKTMA7nj/v0fig1BLygQUQpEFjvNTScK6MT01aby8DvNuka0t0hjavTcP8gBEFVC5GQk3Ds/FVQBDqCdltxIhtnHGgbetloKHVwieSw+OsfKyKj9fuOKJ4RRCb7pNq42FHtiwUHhy2FkpxbkJxLgT3uMJopqJy3dU8tlf3nRqGQbm1eNZsf+uWLxgmd7Eq5rsywZjwjbsq1oIeCGzEq4k6WNCbMi3O1RIkKmJ6eR1q8pZcmLT6sEGJUlO3PfkD7ONcO4Ta48zCi7Rsi1PNJouGyNK8NrD34pbEKwu9MTsYTyNzKHCScDjt8QQne6NB+3ODQM26/6SAUM5gd9WmzZMByW6gFyKmkXhRxHsWDlNN5SJDbdd5w4r7+guqnLo/31hZSC2GZLSbQzrmz5FMKoriSuSxmZITQMV5yMp1IaYzJGxTECyl2V5g89aiOLqhehlM6c4uDfkPYZtZlmPX1JVfTTTy7dUeu08VUQqzvU2qdJV4g2rKJQtMw7py4B4a8E0+ShQgpp/Zi6yvKDxlzx9oZC+Gjtegg7TEsOx4kiefzSr+s3Vy/5puBza1vFBG51ZygyDb+p/ptCrmwUClY9qqR7bm+Wd9uRsG41XxReI5WXyZt1t/GZT0x5EkYQ5tn1DKQMc33G1f11yYTSZinwbbO49qL5xw0ZCSUB5AKTBye+b3rHTNKIhkd16P3+rkUN5fjMgUgEo0ojhh99PmwzszVJYdZQdliyHXbn1PJNMa4BLebmcH8PP6uzz8IDaMLrhHkFGTlkTQY+DoMPCb5FXztth3+FVry/Z2AdFDKogB7rXFfWeGWfQ4F+nZnvcqzasZTL9vWLGiFYCovra29ul5pHU5xLeTxi6FSC5naoT2yj0KY2jaRyPc4MKhb5T6DU/K/Wgj/0TNIS0TL/sbReprFtU0f/Kj6z/tzsIucBb0hN9QFIlOBzDfS0dz5xYoMlJ4Es22iMELiNhvF/zv6+j7IE0QdxhfcnJbYZAA9/ehL2osABkSCOBwUH8dkC1CSAvjgYB/WZSGAWpQhrARWTIJiwEYeMMh1+lRmR9qk4OrWzzJrgLvKOrYTjeAMmXZrRFt8vGQ5I7jiJN2VwET4zqm8pppY4eptK9Uaac2sEunGoxg0eBhuWY6dYgDeW6RMa3kK4wJ3DafJLlhmrhpxULEI8Owo8SzJjHpR+UrhrK3hPBw/Zy30El6MCIJ6pJNgeETpF4naK/EZqqKzrxQ8uSAwLDIucVVtOEdV+4lIcISPV1jza2O4eMu/1W39jSs6sA1ORb8H/taSkYvO80iygERCcYCxNBHZEW3mWRzGGWwojpQjmKaALCHYxprmXdKaL8aDoV+43V+90UO++gfamW8kWxzVeV7R/VoyhQQ1R+tem5eGZSsRpMEL7k1p7YIwyg3Yxt3bha22DEDf0UUzzOwakpnK09gzCnxH3RUSSNnutEkTSw9I22IZXJRkrHydARauj7S0Fd9MDRPgBRloiELVNM2uVNyCdFtMheg8q0wlF+GKLvWyzQ=="

#Decrypting the encrypted script and executing it
echo "${encrypted_script}" | openssl enc -d -aes-256-cbc -a -A | sh -

[morgan@linux_server_01 ~]$

 

As you can see above, when encrypting the shell script, you will have to enter an encryption password. This is NOT the password contained in the original shell script. This is a new password that you define and that you will need to remember because without it, you will NOT be able to execute it properly. Also, you can see that the file “encrypted_test_script.sh” contains the variable “encrypted_script”. This variable is the encrypted string representing the original shell script.

/!\ Please note that if you replace “sh -” at the end of the file with “cat” for example, then upon execution, you will see the content of the original shell script. That suppose that you know the password to decrypt it, of course, so that’s still secure. However, it would be easy for someone with bad intentions to change the file encrypted_script.sh so that when you execute it and provide the right password, it in fact send it via email or something like that. I will not describe it but it would be possible to protect you against that by using signatures for example so you are sure the content of the shell script is the one you generated and it hasn’t been tampered.

So like I said before, no perfect solutions… Or at least no easy solutions.

 

To execute the encrypted script, enter the encryption password and then the script is executed automatically:

[morgan@linux_server_01 ~]$ ./encrypted_test_script.sh
enter aes-256-cbc decryption password:

INFO - This file is a test script to test the encryption solutions.
INFO - Entering the correct password will return an exit code of 0.
WARN - Entering the wrong password will return an exit code of 1.


  ----> Please enter the password to execute this script: Password1
WARN - The password entered isn't the correct one. Please try again.

  ----> Please enter the password to execute this script: TestPassw0rd
OK - The password entered is the correct one.
[morgan@linux_server_01 ~]$

 

Complicated topic, isn’t it? I’m not a security expert but I like these kind of subjects, so… If you have other ideas or thoughts, don’t hesitate to share!

 

 

Cet article Encryption of shell scripts est apparu en premier sur Blog dbi services.

Documentum – Unable to install xCP 2.3 on a CS 7.3

Sat, 2017-08-05 02:53

Beginning of this year, we were doing our first silent installations of the new Documentum stack. I already created a few blogs to talk about some issues with CS 7.3 and xPlore 1.6. This time, I will talk about xCP 2.3 and in particular the installation on a CS 7.3. The Patch of xCP as well as the patch for the CS 7.3 doesn’t matter since all versions are affected. Please just note that the first supported patch on a CS 7.3 is xCP 2.3 P03 so you shouldn’t be installing a previous patch on 7.3.

So, when installing an xCP 2.3 on a Content Server 7.3, you will get a pop-up in the installer with the following error message: “Installation of DARs failed”. You will only have an “OK” button on this pop-up which will close the installer. Ok so there is an issue with the installation of the DARs but what’s the issue exactly?

 

On the installation log file, we can see the following:

[dmadmin@content_server_01 ProcessEngine]$ cat logs/install.log
13:44:45,356  INFO [Thread-8] com.documentum.install.pe.installanywhere.actions.PEInitializeSharedLibrary - Done InitializeSharedLibrary ...
13:44:45,395  INFO [Thread-10] com.documentum.install.appserver.jboss.JbossApplicationServer - setApplicationServer sharedDfcLibDir is:$DOCUMENTUM_SHARED/dfc
13:44:45,396  INFO [Thread-10] com.documentum.install.appserver.jboss.JbossApplicationServer - getFileFromResource for templates/appserver.properties
13:44:45,532  WARN [Thread-10] com.documentum.install.pe.installanywhere.actions.DiWAPeInitialize - init-param tags found in Method Server webapp:

<init-param>
      <param-name>docbase_install_owner_name</param-name>
      <param-value>dmadmin</param-value>
</init-param>
<init-param>
      <param-name>docbase-GR_DOCBASE</param-name>
      <param-value>GR_DOCBASE</param-value>
</init-param>
<init-param>
      <param-name>docbase-DocBase1</param-name>
      <param-value>DocBase1</param-value>
</init-param>
<init-param>
      <param-name>docbase-DocBase2</param-name>
      <param-value>DocBase2</param-value>
</init-param>
13:44:58,771  INFO [AWT-EventQueue-0] com.documentum.install.pe.ui.panels.DiWPPELicenseAgreementPanel - UserSelection: "I accept the terms of the license agreement."
13:46:13,398  INFO [AWT-EventQueue-0] com.documentum.install.appserver.jboss.JbossApplicationServer - The batch file: $DOCUMENTUM_SHARED/temp/installer/wildfly/dctm_tmpcmd0.sh exist? false
13:46:13,399  INFO [AWT-EventQueue-0] com.documentum.install.appserver.jboss.JbossApplicationServer - The user home is : /home/dmadmin
13:46:13,405  INFO [AWT-EventQueue-0] com.documentum.install.appserver.jboss.JbossApplicationServer - Executing temporary batch file: $DOCUMENTUM_SHARED/temp/installer/wildfly/dctm_tmpcmd0.sh for running: $DOCUMENTUM_SHARED/java64/1.8.0_77/bin/java -cp $DOCUMENTUM_SHARED/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/dfc.jar:$DOCUMENTUM_SHARED/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/aspectjrt.jar:$DOCUMENTUM_SHARED/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/DctmUtils.jar com.documentum.install.appserver.utils.DctmAppServerAuthenticationString $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer jboss
13:46:42,320  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeInstallActions - starting DctmActions
13:46:42,724  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - user name = admin
13:46:42,724  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - Server DctmServer_MethodServer already exists!
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - Deploying to Group MethodServer... bpm (bpm.ear): does not exist!
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - resolving $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer/deployments/bpm.ear/APP-INF/classes/dfc.properties
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - resolving $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer/deployments/bpm.ear/APP-INF/classes/log4j.properties
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - resolving $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer/deployments/bpm.ear/bpm.war/WEB-INF/web.xml
13:46:42,727  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeInstallActions - Finished DctmActions.
13:46:44,885  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Start to deploy dars for docbase: DocBase2
13:52:20,931  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - End to deploy dars for repository: DocBase2
13:52:20,932  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Start to deploy dars for docbase: DocBase1
13:57:59,510  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - End to deploy dars for repository: DocBase1
13:57:59,511  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Start to deploy dars for docbase: GR_DOCBASE
14:04:03,231  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - End to deploy dars for repository: GR_DOCBASE
14:04:03,268 ERROR [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Installation of DARs failed
com.documentum.install.shared.common.error.DiException: 3 DAR(s) failed to install.
        at com.documentum.install.shared.common.services.dar.DiDocAppFailureList.report(DiDocAppFailureList.java:39)
        at com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars.deployDars(DiPAPeProcessDars.java:123)
        at com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars.setup(DiPAPeProcessDars.java:71)
        at com.documentum.install.shared.installanywhere.actions.InstallWizardAction.install(InstallWizardAction.java:75)
        at com.zerog.ia.installer.actions.CustomAction.installSelf(Unknown Source)
        at com.zerog.ia.installer.InstallablePiece.install(Unknown Source)
        at com.zerog.ia.installer.InstallablePiece.install(Unknown Source)
        at com.zerog.ia.installer.GhostDirectory.install(Unknown Source)
        at com.zerog.ia.installer.InstallablePiece.install(Unknown Source)
        at com.zerog.ia.installer.Installer.install(Unknown Source)
        at com.zerog.ia.installer.actions.InstallProgressAction.ae(Unknown Source)
        at com.zerog.ia.installer.actions.ProgressPanelAction$1.run(Unknown Source)
14:04:03,269  INFO [installer]  - The INSTALLER_UI value is SWING
14:04:03,269  INFO [installer]  - The env PATH value is: /usr/xpg4/bin:$DOCUMENTUM_SHARED/java64/JAVA_LINK/bin:$DOCUMENTUM/product/7.3/bin:$DOCUMENTUM/dba:$ORACLE_HOME/bin:$DOCUMENTUM_SHARED/java64/JAVA_LINK/bin:$DOCUMENTUM/product/7.3/bin:$DOCUMENTUM/dba:$ORACLE_HOME/bin:$DOCUMENTUM/product/7.3/bin:$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/dmadmin/bin:/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin
[dmadmin@content_server_01 ProcessEngine]$

 

It is mentioned that three DARs failed to be installed but since there are three docbases here, that’s actually one DAR per docbase. The only interesting information we can find from the install log file is that some DARs were installed properly so it’s not a generic issue but more likely an issue with one specific DAR. The next step is therefore to check the log file of the DAR installation:

[dmadmin@content_server_01 ProcessEngine]$ grep -i ERROR logs/dar_logs/GR_DOCBASE/peDars.log | grep -v "^\[INFO\].*ERROR"
[INFO]  dmbasic.exe output : dmbasic: Error 35 in line 585: Sub or Function not defined
[ERROR]  Unable to install dar file $DOCUMENTUM/product/7.3/install/DARsInternal/BPM.dar
com.emc.ide.installer.InstallException: Error handling controllable object Status = New; IsInstalled = true; com.emc.ide.artifact.bpm.model.bpm.impl.ActivityImpl@5e020dd1 (objectTypeName: null) (objectName: DB Inbound - Initiate, title: , subject: , authors: [], keywords: [], applicationType: , isHidden: false, compoundArchitecture: , componentLabel: [], resolutionLabel: , contentType: xml, versionLabel: [1.0, CURRENT], specialApp: DB-IN-IN.GIF, languageCode: , creatorName: null, archive: false, category: , controllingApp: , effectiveDate: [], effectiveFlag: [], effectiveLabel: [], expirationDate: [], extendedProperties: [], fullText: true, isSigned: false, isTemplate: false, lastReviewDate: null, linkResolved: false, publishFormats: [], retentionDate: null, status: , rootObject: true) (isPrivate: false, definitionState: installed, triggerThreshold: 0, triggerEvent: , execType: manual, execSubType: inbound_initiate, execMethodName: null, preTimer: 0, preTimerCalendarFlag: notusebusinesscal, preTimerRepeatLast: 0, postTimer: 0, postTimerCalendarFlag: notusebusinesscal, postTimerRepeatLast: 0, repeatableInvoke: true, execSaveResults: false, execTimeOut: 0, execErrHandling: stopAfterFailure, signOffRequired: false, resolveType: normal, resolvePkgName: , controlFlag: taskAssignedtoSupervisor, taskName: null, taskSubject: , performerType: user, performerFlag: noDeligationOrExtention, transitionMaxOutputCnt: 0, transitionEvalCnt: trigAllSelOutputLinks, transitionFlag: trigAllSelOutputLinks, transitionType: prescribed, execRetryMax: 0, execRetryInterval: 0, groupFlag: 0, template: true, artifactVersion: D65SP1);  Object ID = 4c0f123450002b1e;
Caused by: DfException:: THREAD: main; MSG: Error while making activity uneditable: com.emc.ide.artifactmanager.model.artifact.impl.ArtifactImpl@4bbc02ef (urn: urnd:com.emc.ide.artifact.bpm.activity/DB+Inbound+-+Initiate?location=%2FTemp%2FIntegration&name=DB+Inbound+-+Initiate, locale: null, repoLocation: null, categoryId: com.emc.ide.artifact.bpm.activity, implicitlyCreated: false, modifiedByUser: true); ERRORCODE: ff; NEXT: null
Caused by: DfException:: THREAD: main; MSG: [DM_WORKFLOW_E_NAME_NOT_EXIST]error:  "The dm_user object by the name 'dm_bps_inbound_user' specified in attribute performer_name does not exist."; ERRORCODE: 100; NEXT: null
[ERROR]  Failed to install DAR
Caused by: com.emc.ide.installer.InstallException: Error handling controllable object Status = New; IsInstalled = true; com.emc.ide.artifact.bpm.model.bpm.impl.ActivityImpl@5e020dd1 (objectTypeName: null) (objectName: DB Inbound - Initiate, title: , subject: , authors: [], keywords: [], applicationType: , isHidden: false, compoundArchitecture: , componentLabel: [], resolutionLabel: , contentType: xml, versionLabel: [1.0, CURRENT], specialApp: DB-IN-IN.GIF, languageCode: , creatorName: null, archive: false, category: , controllingApp: , effectiveDate: [], effectiveFlag: [], effectiveLabel: [], expirationDate: [], extendedProperties: [], fullText: true, isSigned: false, isTemplate: false, lastReviewDate: null, linkResolved: false, publishFormats: [], retentionDate: null, status: , rootObject: true) (isPrivate: false, definitionState: installed, triggerThreshold: 0, triggerEvent: , execType: manual, execSubType: inbound_initiate, execMethodName: null, preTimer: 0, preTimerCalendarFlag: notusebusinesscal, preTimerRepeatLast: 0, postTimer: 0, postTimerCalendarFlag: notusebusinesscal, postTimerRepeatLast: 0, repeatableInvoke: true, execSaveResults: false, execTimeOut: 0, execErrHandling: stopAfterFailure, signOffRequired: false, resolveType: normal, resolvePkgName: , controlFlag: taskAssignedtoSupervisor, taskName: null, taskSubject: , performerType: user, performerFlag: noDeligationOrExtention, transitionMaxOutputCnt: 0, transitionEvalCnt: trigAllSelOutputLinks, transitionFlag: trigAllSelOutputLinks, transitionType: prescribed, execRetryMax: 0, execRetryInterval: 0, groupFlag: 0, template: true, artifactVersion: D65SP1);  Object ID = 4c0f123450002b1e;
Caused by: DfException:: THREAD: main; MSG: Error while making activity uneditable: com.emc.ide.artifactmanager.model.artifact.impl.ArtifactImpl@4bbc02ef (urn: urnd:com.emc.ide.artifact.bpm.activity/DB+Inbound+-+Initiate?location=%2FTemp%2FIntegration&name=DB+Inbound+-+Initiate, locale: null, repoLocation: null, categoryId: com.emc.ide.artifact.bpm.activity, implicitlyCreated: false, modifiedByUser: true); ERRORCODE: ff; NEXT: null
Caused by: DfException:: THREAD: main; MSG: [DM_WORKFLOW_E_NAME_NOT_EXIST]error:  "The dm_user object by the name 'dm_bps_inbound_user' specified in attribute performer_name does not exist."; ERRORCODE: 100; NEXT: null
[dmadmin@content_server_01 ProcessEngine]$

 

With the above, we know that the only failed DAR is the BPM.dar and it looks like we have the reason for this: the DAR needs a user named “dm_bps_inbound_user” to proceed with the installation but couldn’t find it and therefore the installation failed. But actually that’s not the root cause, it’s only a consequence. The real reason why the DAR installation failed is displayed in the first line above.

[INFO]  dmbasic.exe output : dmbasic: Error 35 in line 585: Sub or Function not defined

 

For some reason, a function couldn’t be executed because not defined properly. This function is the one that is supposed to create the “dm_bps_inbound_user” user but with a CS 7.3 this function cannot be executed properly. As a result, the user isn’t created and then the DAR installation fail. For more information, you can refer to the BPM-11223.

 

This issue will – according to EMC – not be fixed in any patch of the xCP 2.3, even if this issue has been spotted quickly after the release of the xCP 2.3. Therefore, if you want to avoid this issue, you will have to wait several months for the xCP 2.4 to be released (not really realistic ;)) or you will need to create this user manually before installing the xCP 2.3 on a CS 7.3. You don’t need special permissions for this user and you don’t need to know its password so it’s rather simple to create it for all installed docbases in a few simple commands:

[dmadmin@content_server_01 ProcessEngine]$ echo "?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';" > create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "create,c,dm_user" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "set,c,l,user_name" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "dm_bps_inbound_user" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "set,c,l,user_login_name" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "dm_bps_inbound_user" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "save,c,l" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$ cat create_user.api
?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';
create,c,dm_user
set,c,l,user_name
dm_bps_inbound_user
set,c,l,user_login_name
dm_bps_inbound_user
save,c,l
?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$ sep="***********************"
[dmadmin@content_server_01 ProcessEngine]$ for docbase in `cd $DOCUMENTUM/dba/config; ls`;do echo;echo "$sep";echo "Create User: ${docbase}";echo "$sep";iapi ${docbase} -Udmadmin -Pxxx -Rcreate_user.api;done

***********************
Create User: GR_DOCBASE
***********************


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


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


Connected to Documentum Server running Release 7.3.0000.0214  Linux64.Oracle
Session id is s0
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------

(0 row affected)

API> ...
110f12345000093c
API> SET> ...
OK
API> SET> ...
OK
API> ...
OK
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------
110f12345000093c     dm_bps_inbound_user   dm_bps_inbound_user
(1 row affected)

API> Bye

***********************
Create User: DocBase1
***********************


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


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


Connected to Documentum Server running Release 7.3.0000.0214  Linux64.Oracle
Session id is s0
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------

(0 row affected)

API> ...
110f234560001532
API> SET> ...
OK
API> SET> ...
OK
API> ...
OK
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------
110f234560001532     dm_bps_inbound_user   dm_bps_inbound_user                                                                                                                                                            
(1 row affected)

API> Bye

***********************
Create User: DocBase2
***********************


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


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


Connected to Documentum Server running Release 7.3.0000.0214  Linux64.Oracle
Session id is s0
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------

(0 row affected)

API> ...
110f345670001532
API> SET> ...
OK
API> SET> ...
OK
API> ...
OK
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------
110f345670001532     dm_bps_inbound_user   dm_bps_inbound_user                                                                                                                                                            
(1 row affected)

API> Bye
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$ rm create_user.api
[dmadmin@content_server_01 ProcessEngine]$

 

The users have been created properly in all docbases so just restart the xCP installer and this time the BPM.dar installation will succeed.

 

 

Cet article Documentum – Unable to install xCP 2.3 on a CS 7.3 est apparu en premier sur Blog dbi services.

Documentum – Using DA with Self-Signed SSL Certificate

Sat, 2017-08-05 01:58

A few years ago, I was working on a Documentum project and one of the tasks was to setup all components in SSL. I already published a lot of blogs on this subject but there is one I wanted to do but never really took the time to publish it. In this blog, I will therefore talk about Documentum Administrator in SSL using a Self-Sign SSL Certificate. Recently, a colleague of mine had the same issue at another customer so I provided him the full procedure that I will describe below. However, since the process below requires the signature of a jar file and since this isn’t available for all companies, you might want to check out my colleague’s blog too.

A lot of companies are working with their own SSL Trust Chain, meaning that they provide/create their own SSL Certificate (Self-Signed) including their Root and Intermediate SSL Certificate for the trust. End-users will not really notice the difference but they are actually using Self-Sign SSL Certificate. This has some repercussions when working with Documentum since you need to import the SSL Trust Chain on the various Application Servers (JMS, WebLogic, Dsearch, aso…). This is pretty simple but there is one thing that is a little bit trickier and this is related to Documentum Administrator.

Below, I will use a DA 7.2 P16 (that is therefore pretty recent) but the same applies to all patches of DA 7.2 and 7.3. For information, we didn’t face this issue with DA 7.1 so something most probably changed between DA 7.1 and 7.2. If you are seeing the same thing with a DA 7.1, feel free to put a comment below, I would love to know! When you are accessing DA for the first time, you will actually download a JRE which will be put under C:\Users\<user_name>\Documentum\ucf\<machine_name>, by default. This JRE is used for various stuff including the transfer of files (UCF), display of DA preferences, aso… DA isn’t taking the JRE from the website of Oracle, it is, in fact, taking it from the da.war file. The DA war file always contains two or three different JREs versions. Now if you want to use DA in HTTPS, these JREs will also need to contain your custom SSL Trust Chain. So how can you do that?

Well a simple answer would be: just like for the JMS or WebLogic, just import the custom SSL Trust Chain in the “cacerts” of these JREs. That will actually not work for a very vicious reason: EMC is now signing all the files provided and that also include the JREs inside da.war (well actually they are signing the checksums of the JREs, not the JREs themselves). Because of this signature, if you edit the cacerts file of the JREs, DA will say something like that: “Invalid checksum for the file ‘win-jre1.8.0_91.zip'”. This checksum ensures that the JREs and all the files you are using on your local workstation that have been downloaded from the da.war are the one provided by EMC. This is good from a security point of view since it prevents intruders to exchanges the files during transfer or directly on your workstation but that also prevents you from updating the JREs with your custom SSL Trust Chain.

 

So what I will do below to update the Java cacerts AND still keep a valid signature is:

  1. Extract the JREs and ucfinit.jar file from da.war
  2. Update the cacerts of each JREs with a custom SSL Trust Chain (Root + Intermediate)
  3. Repackage the JREs
  4. Calculate the checksum of the JREs using the ComputeChecksum java class
  5. Extract the old checksum files from ucfinit.jar
  6. Replace the old checksum files for the JREs with the new one generated on step 4
  7. Remove .RSA and .SF files from the META-INF folder and clean the MANIFEST to remove Documentum’s digital signature
  8. Recreate the file ucfinit.jar with the clean manifest and all other files
  9. Ask the company’s dedicated team to sign the new jar file
  10. Repackage da.war with the updated JREs and the updated/signed ucfinit.jar

 

I will use below generic commands that do not specify any version of the JREs or DA because there will be two or three different JREs and the versions will change depending on your DA Patch level, so better stay generic. I will also use my custom SSL Trust Chain which I put under /tmp.

In this first part, I will create a working folder to avoid messing with the deployed applications. Then I will extract the needed files and finally remove all files and folders that I don’t need. That’s the step 1:

[weblogic@weblogic_server_01 ~]$ mkdir /tmp/workspace; cd /tmp/workspace
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ cp $WLS_APPLICATIONS/da.war .
[weblogic@weblogic_server_01 workspace]$ ls
da.war
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ jar -xvf da.war wdk/system/ucfinit.jar wdk/contentXfer/
  created: wdk/contentXfer/
 inflated: wdk/contentXfer/All-MB.jar
 ...
 inflated: wdk/contentXfer/Web/Emc.Documentum.Ucf.Client.Impl.application
 inflated: wdk/contentXfer/win-jre1.7.0_71.zip
 inflated: wdk/contentXfer/win-jre1.7.0_72.zip
 inflated: wdk/contentXfer/win-jre1.8.0_91.zip
 inflated: wdk/system/ucfinit.jar
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ cd ./wdk/contentXfer/
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
All-MB.jar                                    jacob.dll                 libUCFSolarisGNOME.so   ucf-client-installer.zip  win-jre1.8.0_91.zip
Application Files                             jacob.jar                 libUCFSolarisJNI.so     ucf.installer.config.xml
Emc.Documentum.Ucf.Client.Impl.application    libMacOSXForkerIO.jnilib  licenses                UCFWin32JNI.dll
ES1_MRE.msi                                   libUCFLinuxGNOME.so       MacOSXForker.jar        Web
ExJNIAPI.dll                                  libUCFLinuxJNI.so         mac_utilities.jar       win-jre1.7.0_71.zip
ExJNIAPIGateway.jar                           libUCFLinuxKDE.so         ucf-ca-office-auto.jar  win-jre1.7.0_72.zip
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ for i in `ls | grep -v 'win-jre'`; do rm -rf "./${i}"; done
[weblogic@weblogic_server_01 contentXfer]$ rm -rf ./*/
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
win-jre1.7.0_71.zip  win-jre1.7.0_72.zip  win-jre1.8.0_91.zip
[weblogic@weblogic_server_01 contentXfer]$

 

At this point, only the JREs are present in the current folder (wdk/contentXfer) and I also have another file in another folder (wdk/system/ucfinit.jar). Once that is done, I’m creating a list of the JREs available that I will use for the whole blog and I’m also performing the steps 2 and 3, to extract the cacerts from the JREs, update them and finally repackage them (this is where I use the custom SSL Trust Chain):

[weblogic@weblogic_server_01 contentXfer]$ ls win-jre* | sed -e 's/.*win-//' -e 's/.zip//' > /tmp/list_jre.txt
[weblogic@weblogic_server_01 contentXfer]$ cat /tmp/list_jre.txt
jre1.7.0_71
jre1.7.0_72
jre1.8.0_91
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do unzip -x win-${line}.zip ${line}/lib/security/cacerts; done < /tmp/list_jre.txt
Archive:  win-jre1.7.0_71.zip
  inflating: jre1.7.0_71/lib/security/cacerts
Archive:  win-jre1.7.0_72.zip
  inflating: jre1.7.0_72/lib/security/cacerts
Archive:  win-jre1.8.0_91.zip
  inflating: jre1.8.0_91/lib/security/cacerts
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do keytool -import -noprompt -trustcacerts -alias custom_root_ca -keystore ${line}/lib/security/cacerts -file /tmp/Company_Root_CA.cer -storepass changeit; done < /tmp/list_jre.txt
Certificate was added to keystore
Certificate was added to keystore
Certificate was added to keystore
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do keytool -import -noprompt -trustcacerts -alias custom_int_ca -keystore ${line}/lib/security/cacerts -file /tmp/Company_Intermediate_CA.cer -storepass changeit; done < /tmp/list_jre.txt
Certificate was added to keystore
Certificate was added to keystore
Certificate was added to keystore
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do zip -u win-${line}.zip ${line}/lib/security/cacerts; done < /tmp/list_jre.txt
updating: jre1.7.0_71/lib/security/cacerts (deflated 35%)
updating: jre1.7.0_72/lib/security/cacerts (deflated 35%)
updating: jre1.8.0_91/lib/security/cacerts (deflated 33%)
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do rm -rf ./${line}; done < /tmp/list_jre.txt
[weblogic@weblogic_server_01 contentXfer]$

 

At this point, the JREs have been updated with a new “cacerts” and therefore its checksum changed. It doesn’t match the signed checksum anymore so if you try to deploy DA at this point, you will get the error message I put above. So, let’s perform the steps 4, 5 and 6. For that purpose, I will use the file /tmp/ComputeChecksum.class that was provided by EMC. This class is needed in order to recalculate the new checksum of the JREs:

[weblogic@weblogic_server_01 contentXfer]$ pwd
/tmp/workspace/wdk/contentXfer
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ cp /tmp/ComputeChecksum.class .
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
ComputeChecksum.class  win-jre1.7.0_71.zip  win-jre1.7.0_72.zip  win-jre1.8.0_91.zip
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ java ComputeChecksum .
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
ComputeChecksum.class           win-jre1.7.0_71.zip           win-jre1.7.0_72.zip           win-jre1.8.0_91.zip
ComputeChecksum.class.checksum  win-jre1.7.0_71.zip.checksum  win-jre1.7.0_72.zip.checksum  win-jre1.8.0_91.zip.checksum
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ rm ComputeChecksum.class*
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ cd /tmp/workspace/wdk/system/
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ pwd
/tmp/workspace/wdk/system
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ ls
ucfinit.jar
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ jar -xvf ucfinit.jar
 inflated: META-INF/MANIFEST.MF
 inflated: META-INF/COMPANY.SF
 inflated: META-INF/COMPANY.RSA
  created: META-INF/
 inflated: All-MB.jar.checksum
  created: com/
  created: com/documentum/
  ...
 inflated: UCFWin32JNI.dll.checksum
 inflated: win-jre1.7.0_71.zip.checksum
 inflated: win-jre1.7.0_72.zip.checksum
 inflated: win-jre1.8.0_91.zip.checksum
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ mv /tmp/workspace/wdk/contentXfer/win-jre*.checksum .
[weblogic@weblogic_server_01 system]$

 

With this last command, the new checksum have replaced the old ones. The next step is now to remove the old signatures (.RSA and .SF files + content of the manifest) and the repack the ucfinit.jar file (step 7 and 8):

[weblogic@weblogic_server_01 system]$ rm ucfinit.jar META-INF/*.SF META-INF/*.RSA
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ sed -i -e '/^Name:/d' -e '/^SHA/d' -e '/^ /d' -e '/^[[:space:]]*$/d' META-INF/MANIFEST.MF
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ cat META-INF/MANIFEST.MF
Manifest-Version: 1.0
Ant-Version: Apache Ant 1.8.4
Title: Documentum Client File Selector Applet
Bundle-Version: 7.2.0160.0058
Application-Name: Documentum
Built-By: dmadmin
Build-Version: 7.2.0160.0058
Permissions: all-permissions
Created-By: 1.6.0_30-b12 (Sun Microsystems Inc.)
Copyright: Documentum Inc. 2001, 2004
Caller-Allowable-Codebase: *
Build-Date: August 16 2016 06:35 AM
Codebase: *
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ vi META-INF/MANIFEST.MF
    => Add a new empty line at the end of this file with vi, vim, nano or whatever... The file must always end with an empty line.
    => Do NOT use the command "echo '' >> META-INF/MANIFEST.MF" because it will change the fileformat of the file which complicate the signature (usually the FF is DOS...)
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ jar -cmvf META-INF/MANIFEST.MF ucfinit.jar *
added manifest
adding: All-MB.jar.checksum(in = 28) (out= 30)(deflated -7%)
adding: com/(in = 0) (out= 0)(stored 0%)
adding: com/documentum/(in = 0) (out= 0)(stored 0%)
adding: com/documentum/ucf/(in = 0) (out= 0)(stored 0%)
...
adding: UCFWin32JNI.dll.checksum(in = 28) (out= 30)(deflated -7%)
adding: win-jre1.7.0_71.zip.checksum(in = 28) (out= 30)(deflated -7%)
adding: win-jre1.7.0_72.zip.checksum(in = 28) (out= 30)(deflated -7%)
adding: win-jre1.8.0_91.zip.checksum(in = 28) (out= 30)(deflated -7%)
[weblogic@weblogic_server_01 system]$

 

At this point, the file ucfinit.jar has been recreated with an “empty” manifest, without signature but with all the new checksum files. Therefore, it is now time to send this file (ucfinit.jar) to your code signing team (step 9). This is out of scope for this blog but basically what will be done by your signature team is the creation of the .RSA and .SF files inside the folder META-INF as well as the repopulation of the manifest. The .SF and the manifest will contain more or less the same thing: the different files of the ucfinit.jar files will have their entries in these files with a pair filename/signature. At this point, we therefore have re-signed the checksum of the JREs.

 

The last step is now to repack the da.war with the new ucfinit.jar file which has been signed. I put the new signed file under /tmp:

[weblogic@weblogic_server_01 system]$ pwd
/tmp/workspace/wdk/system
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ rm -rf *
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ ll
total 0
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ cp /tmp/ucfinit.jar .
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ cd /tmp/workspace/
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ ls wdk/*
wdk/contentXfer:
win-jre1.7.0_71.zip  win-jre1.7.0_72.zip  win-jre1.8.0_91.zip

wdk/system:
ucfinit.jar
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ jar -uvf da.war wdk
adding: wdk/(in = 0) (out= 0)(stored 0%)
adding: wdk/contentXfer/(in = 0) (out= 0)(stored 0%)
adding: wdk/contentXfer/win-jre1.7.0_71.zip(in = 41373620) (out= 41205241)(deflated 0%)
adding: wdk/contentXfer/win-jre1.7.0_72.zip(in = 41318962) (out= 41137924)(deflated 0%)
adding: wdk/contentXfer/win-jre1.8.0_91.zip(in = 62424686) (out= 62229724)(deflated 0%)
adding: wdk/system/(in = 0) (out= 0)(stored 0%)
adding: wdk/system/ucfinit.jar(in = 317133) (out= 273564)(deflated 13%)
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ mv $WLS_APPLICATIONS/da.war $WLS_APPLICATIONS/da.war_bck_beforeSignature
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ mv da.war $WLS_APPLICATIONS/
[weblogic@weblogic_server_01 workspace]$

 

Once this has been done, simply redeploy the Documentum Administrator and the next time you will access it in HTTPS, you will be able to transfer files, view the DA preferences, aso… The JREs are now trusted automatically because the checksum of the JRE is now signed properly.

 

 

Cet article Documentum – Using DA with Self-Signed SSL Certificate est apparu en premier sur Blog dbi services.

Developer GUI tools for PostgreSQL

Fri, 2017-08-04 13:33

There was a recent thread on the PostgreSQL general mailing list asking for GUI tools for PostgreSQL. This is question we get asked often at customers so I though it might be good idea to summarize some of them in a blog post. When you know other tools than the ones listed here which look promising, let me know so I can add them. There is a list of tools in the PostgreSQL Wiki as well.

Name Linux Windows MacOS Free Screenshot pgAdmin Y Y Y Y pg_gui_pgadmin DBeaver Y Y Y Y pg_gui_dbeaver EMS SQL Manager for PostgreSQL N Y N N pg_gui_ems_sql_manager JET BRAINS DataCrip Y Y Y N pg_gui_datagrip PostgreSQL Studio Y Y Y Y pg_gui_pgstudio Navicat for PostgreSQL Y Y Y N pg_gui_navicat execute Query Y Y Y Y pg_gui_executequery SQuirreL SQL Client Y Y Y Y pg_gui_aquirrel pgModeler Y Y Y Y pg_gui_pgmodeler DbSchema Y Y Y N pg_gui_dbschema Oracle SQL Developer Y Y Y Y pg_gui_sqldeveloper PostgreSQL Maestro N Y N N pg_gui_sqlmaestro SQL workbench Y Y Y Y pg_gui_sqlworkbench Nucleon Database Master N Y N N pg_gui_databasemaster Razor SQL Y Y Y N pg_gui_razorsql Database Workbench N Y N N pg_gui_databaseworkbench  

Cet article Developer GUI tools for PostgreSQL est apparu en premier sur Blog dbi services.

Exadata Capacity on Demand and Elastic Rack

Fri, 2017-08-04 11:18

Since X4 we can do Capacity on Demand on Exadata: disable some CPU cores to lower cost of Oracle Database licenses. Depending on the models, and the configuration, there are different minimums and here is a recap table about those.

Here is the summary of Capacity on Demand minimum, maximum and increment. Those numbers come from the configuration file of OEDA, the Oracle Exadata Deployment Assistant (es.properties) and you can see that it already has an option for Exadata X7-2

Exadata model sockets cores per socket cores per server thread per core Capacity on Demand minimum Cod maximum CoD increment X2-2 2 6 12 2 X3-2 2 8 16 2 X4-2 2 12 24 2 12
(not for 1/8th) 24 2 X5-2 2 18 36 2 14 36 2 X6-2 2 22 44 2 14
(8 for 1/8th) 44 2 X7-2 2 24 48 2 14
(8 for 1/8th) 48 2 X2-8 8 8 12 2 X3-8 8 10 12 2 X4-8 8 15 32 2 48 120 8 X5-8 8 18 32 2 56 144 8 X6-8 8 18 32 2 56 X7-8 8 24 32 2 SL6 2 32 64 8 14
(8 for 1/8th) 64 2 T7-2 2 32 62 8

 

Special minimums for 1/8th of Rack

The smallest configuration (1/8th of Rack) is a bit special. First, because it is physically identical to the 1/4th one with just some processors and disks disabled. But also, for this entry-level, the minimum required is lower – 8 cores per node – in X6.

Here is the Oracle Exadata Deployment Assistant for X6-2 1/8th of Rack:

CaptureOEDAx68002

When having selected 1/8th of Rack we are allowed to enable a minimum of 8 cores per nodes, as mentioned in the table above:

CaptureOEDAx68006

Elastic Rack

Elastic Rack configuration allows to configure any combination of database nodes and storage cells:

CaptureOEDAx68004

With Elastic Rack configuration, the next screen is not only displaying the configuration, but you can customize it.
Here I define the same configuration as an 8th of RAC:

CaptureOEDAx68005

However, because it is not an 1/8th Rack configuration, the minimum is 14 cores per node and not 8:

CaptureOEDAx68001

So be careful. Elastic configuration gives more flexibility, but CoD minimums are is different than the equivalent configuration.

/opt/oracle.SupportTools/resourcecontrol

As I’m talking about elastic configuration here is how the cores are enabled. The configuration assistant calls /opt/oracle.SupportTools/resourcecontrol which displays or updates the BIOS configuration. You may wonder why you can do that here and not in your own servers? Because here Oracle can trace what happened. You will find the log in /var/log/oracleexa/systemconfig.log and here is an example where the Elastic Rack has been deployed with 16 cores per database node Capacity on Demand:

Fri Aug 04 16:12:18 CEST 2017
Executing command: /opt/oracle.SupportTools/resourcecontrol -show
[INFO] Validated hardware and OS. Proceed.
[SHOW] Number of physical cores active per socket: 22
[SHOW] Total number of cores active: 44
 
Mon Aug 07 11:24:31 CEST 2017
Executing command: /opt/oracle.SupportTools/resourcecontrol -core 16 -force
[INFO] Validated hardware and OS. Proceed.
[INFO] Enabling 8 cores on each socket.
[INFO] Import all bios settings
[INFO] All bios settings have been imported with success
[ACTION] Reboot server for settings to take effect
[SHOW] Number of physical cores active per socket: 8
[SHOW] Total number of cores active: 16
 
Mon Aug 07 11:31:24 CEST 2017
Executing command: /opt/oracle.SupportTools/resourcecontrol -show
[INFO] Validated hardware and OS. Proceed.
[SHOW] Number of physical cores active per socket: 8
[SHOW] Total number of cores active: 16

This does not stay on your server. There is a rule that you can do Capacity on Demand only if you have configured Platinum support, or use Oracle Configuration Manager, or Enterprise Manager. All those may store history of the CPU count, which means that it is auditable.

 

Cet article Exadata Capacity on Demand and Elastic Rack est apparu en premier sur Blog dbi services.

A wonderful PostgreSQL feature: default privileges

Fri, 2017-08-04 02:52

Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the user which owns the objects creates new objects? Then you will need to grant those to the second user as well. In PostgreSQL there is an easier solution. Lets go …

Again we start by creating two users each with its own schema:

postgres=# create user a with login password 'a';
CREATE ROLE
postgres=# create schema a authorization a;
CREATE SCHEMA
postgres=# alter user a set search_path=a;
ALTER ROLE
postgres=# create user b with login password 'b';
CREATE ROLE
postgres=# create schema b authorization b;
CREATE SCHEMA
postgres=# alter user b set search_path=b;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 a         |                                                            | {}
 b         |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 a      | a
 b      | b
 public | postgres
(3 rows)

User “a” shall be the one owning the objects:

postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t1 ( a int );
CREATE TABLE
postgres=> create table t2 ( a int );
CREATE TABLE
postgres=> insert into t1 (a) values (1);
INSERT 0 1
postgres=> insert into t2 (a) values (2);
INSERT 0 1
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 a      | t1   | table | a
 a      | t2   | table | a
(2 rows)

When you want to give user “b” access to these tables you could do:

postgres=> grant select on table t1 to b;
GRANT
postgres=> grant select on table t2 to b;
GRANT

From now on user “b” should be able to select from the two tables owned by user “a”, right?:

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t1;
ERROR:  permission denied for schema a
LINE 1: select count(*) from a.t1;

This is not how it works in PostgreSQL. What you need to do is this:

postgres=> \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> grant usage on schema a to b;
GRANT

This allows user “b” access to the schema “a” (remember that a user and a schema are different things in PostgreSQL):

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t1;
 count 
-------
     1
(1 row)

postgres=> select count(*) from a.t2;
 count 
-------
     1
(1 row)

What happens now when user “a” creates another object:

postgres=> \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t3 as select * from t1;
SELECT 1
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 a      | t1   | table | a
 a      | t2   | table | a
 a      | t3   | table | a
(3 rows)

Will user “b” be able to select data from it?

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t3;
ERROR:  permission denied for relation t3

Of course not. The “usage” on a schema grants only access to that schema but not access to the objects in the schema. When we want user “b” being able to select from all tables in schema “a” even when user “a” creates new objects then we can modify the default privileges:

postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter default privileges in schema a grant select on tables to b;
ALTER DEFAULT PRIVILEGES

Should user “b” now be able to select from the “t3″ table in schema “a”?

postgres=> select current_user;
 current_user 
--------------
 b
(1 row)

postgres=> select count(*) from a.t3;
ERROR:  permission denied for relation t3
postgres=> 

No. When you modify the default privileges this will affect only objects created after your modification. Lets create a new table with user “a” in schema “a”:

postgres=> \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t4 as select from t1;
SELECT 1

As this table was created after the modification to the default privileges user “b” is allowed to select from it automatically:

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t4;
 count 
-------
     1
(1 row)

When you check the link to the documentation above you’ll notice that you can not only grant select on tables but much more. Hope this helps …

 

Cet article A wonderful PostgreSQL feature: default privileges est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths III – Partial Index

Thu, 2017-08-03 10:58

In the previous post I said that an Index Only Access needs to find all rows in the index. Here is a case where, with similar data, Postgres can find all rows but Oracle needs additional considerations.

In the previous post I’ve executed:
select sum(n) from demo1
The execution plan was:

Aggregate (cost=295.29..295.30 rows=1 width=8) (actual time=2.192..2.193 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=30
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..270.29 rows=10000 width=4) (actual time=0.150..1.277 rows=10000 loops=1)
Output: n
Heap Fetches: 0
Buffers: shared hit=30

Basically, this reads all values of the column N and then aggregates them to the sum.
If I remove the SUM() I have only the part that reads all values from N:

explain (analyze,verbose,costs,buffers) select n from demo1 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..270.29 rows=10000 width=4) (actual time=0.150..1.284 rows=10000 loops=1)
Output: n
Heap Fetches: 0
Buffers: shared hit=30
Planning time: 0.440 ms
Execution time: 1.972 ms

Oracle

This sounds logical. Now let’s run the same query, a simple ‘select n from demo1′ in Oracle:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ad4z7tpt0dkta, child number 0
-------------------------------------
select /*+ */ n from demo1
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 397 (100)| 10000 |00:00:00.01 | 1451 |
| 1 | TABLE ACCESS FULL| DEMO1 | 1 | 10000 | 397 (0)| 10000 |00:00:00.01 | 1451 |
--------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Here the access path is different: a full table scan instead of an index only access (Index Fast Full Scan). It is not a cost decision. If we try to force an index access, with INDEX_FFS() or INDEX() hints, the query will still do a Full Table Scan. The reason is that and index only access is possible only if all columns and all rows are present in the index. But Oracle does not always index all rows. The Oracle index has no entry for the rows where all the indexed columns are nulls.

Where n is not null

If I run the same query with the purpose of showing only non-null values, with a ‘where n is not null’ predicate, then an index only access is possible:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gbjpw5u0v9cw, child number 0
-------------------------------------
select /*+ */ n from demo1 where n is not null
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 10000 |00:00:00.01 | 28 |
| 1 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 28 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N" IS NOT NULL)

Constraints

An alternative, if we know that we will never have null values here, is to give the information to the optimizer that there are no null values in the column N:
In Oracle:
alter table demo1 modify n not null;
This is the equivalent of the PostgreSQL
alter table demo1 alter column n set not null;
Then, in addition to ensuring the verification of the constraint, the constraint informs the optimizer that there is no null values and that all rows can be find in the index:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ad4z7tpt0dkta, child number 0
-------------------------------------
select /*+ */ n from demo1
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 10000 |00:00:00.01 | 28 |
| 1 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 28 |
-------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Additional columns

Even if the column can have some null values, it is easy to have an index on null values in Oracle, just by adding a non-null column or expression. And if you don’t need this additional column, you can even add a constant, such as in the following index definition:

create unique index demo1_n on demo1(n,0);

This works because all index entries have at least one non null value. But looking at the buffers you can see that this additional byte (0 is stored in 1 byte) has a little overhead (31 blocks read here instead of 28):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ad4z7tpt0dkta, child number 0
-------------------------------------
select /*+ */ n from demo1
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 10000 |00:00:00.01 | 31 |
| 1 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 8 (0)| 10000 |00:00:00.01 | 31 |
-------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Oracle Partial Indexes

In Oracle, all indexes that include a nullable column are partial indexes: not all rows are indexed, and an index access is possible only if the WHERE clause, or a constraint, guarantees that we don’t need the non-indexed rows. Combined with expression, it can be a way to implement partial indexes when the expression returns null for a specific condition. Oracle even provides computed columns (aka virtual columns) so that the expression does not have to be coded in the where clause of the query.

As an example with expressions, the following index has entries only for the values lower than 10:
create index demo_top10 on demo1(case when n<=10 then n end)

However, to use it, we must mention the expression explicitly:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 863drbjwayrt7, child number 0
-------------------------------------
select /*+ */ (case when n<=10 then n end) from demo1 where (case when
n<=10 then n end)<=5
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 4 |00:00:00.01 | 2 |
|* 1 | INDEX RANGE SCAN| DEMO1_N_TOP10 | 1 | 5 | 1 (0)| 4 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEMO1"."SYS_NC00004$"<=5)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEMO1"."SYS_NC00004$"[NUMBER,22]

We can see that internally, a virtual column (“SYS_NC00004$”) has been created for the indexed expression, and is used for the predicate and the projection which uses the same expression. There is another possibility with the ‘partial index’ feature introduced in 12c but it has not the flexibility of a predicate: it is based on partitioning where only some partitions can be indexed.

Postgres Partial Indexes

Postgres does not need those workarounds. An index indexes all rows, including null entries, and partial indexes can be defined with a where clause:
create index demo_top10 on demo1(n) where n<=10

No need to change the query. As long as the result can come from the partial index, we can use the column without an expression on it:

explain (analyze,verbose,costs,buffers) select n from demo1 where n<=5 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n_top10 on public.demo1 (cost=0.14..4.21 rows=4 width=4) (actual time=0.114..0.114 rows=5 loops=1)
Output: n
Index Cond: (demo1.n <= 5)
Heap Fetches: 0
Buffers: shared hit=2
Planning time: 0.557 ms
Execution time: 0.129 ms

Here the smaller partial index (demo1_n_top10) has been chosen by the query planner.

As you see I’ve not used exactly the same condition. The query planner understood that n<=5 (in the WHERE clause) is a subset of n<=10 (in the index definition). However, if the predicate is too different, it cannot use the index:

fpa=# explain (analyze,verbose,costs,buffers) select n from demo1 where 2*n<=10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..320.29 rows=3333 width=4) (actual time=0.020..1.086 rows=5 loops=1)
Output: n
Filter: ((2 * demo1.n) <= 10)
Rows Removed by Filter: 9995
Heap Fetches: 0
Buffers: shared hit=30

Here, instead of “Index Cond” we have a simple “Filter”. The Index Only Scan has read all the rows, and they were filtered afterward (“Rows Removed by Filter”).

Index condition

With the VERBOSE option of EXPLAIN we see the condition used by the index access:
Index Cond: (demo1.n <= 5)
‘Index Cond.’ is not a simple filter removing rows after an operation, but it is the condition which is used for fast access to the index entries in the sorted index structure. We have the equivalent in Oracle with the ‘+predicate’ format of dbms_xplan:

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"<=5)

Before going further on index access for WHERE clause predicate, the next post will show the major characteristic of indexes (besides the fact that it stores a redundant subset of columns and rows): they are maintained sorted and may return the resulting rows in order.

 

Cet article Postgres vs. Oracle access paths III – Partial Index est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths II – IndexOnlyScan

Wed, 2017-08-02 10:00

In the previous post I’ve explained a sequential scan by accident: my query needed only one column which was indexed, and I expected to read the index rather than the table. And I had to hint the Oracle example to get the same because the Oracle optimizer chooses the index scan over the table scan in that case. Here is where I learned a big difference between Postgres and Oracle. They both use MVCC to query without locking, but Postgres MVCC is for table rows (tuples) only whereas Oracle MVCC is for all blocks – tables and indexes.

So this second post is about Index Only Scan and the second constant you find in the documentation for the query planner:
random_page_cost (floating point)
Sets the planner’s estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0.


I am here in the situation after the previous post: created table and index, have run a query which did a sequential scan on the table:

explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1554.00..1554.01 rows=1 width=8) (actual time=17.430..17.430 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=1429
-> Seq Scan on public.demo1 (cost=0.00..1529.00 rows=10000 width=4) (actual time=0.031..13.011 rows=10000 loops=1)
Output: n, a, x
Buffers: shared hit=1429
Planning time: 1.791 ms
Execution time: 17.505 ms

Index Only Scan

I want to understand why the query planner did not choose an access to the index only. This is where hints are useful: force a plan that is not chosen by the optimizer in order to check if this plan is possible, and then check its cost:

/*+ IndexOnlyScan(demo1) */
explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1727.29..1727.30 rows=1 width=8) (actual time=5.424..5.425 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=1429 read=29
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..1702.29 rows=10000 width=4) (actual time=0.177..4.613 rows=10000 loops=1)
Output: n
Heap Fetches: 10000
Buffers: shared hit=1429 read=29
Planning time: 0.390 ms
Execution time: 5.448 ms

From there you see that an Index Only Scan is possible but more expensive. The estimated cost is higher than the Seq Scan (cost=0.29..1702.29 instead of cost=0.00..1529.00). And the execution statistics shows that I’ve read the 1429 table pages in addition to the 29 pages of the index.

From the hit/read statistics we can note that the create table has left all the table pages in the buffer cache, but this is not the case for the create index. But that’s another story. My concern is why and index only access goes to read all table blocks in addition to the index ones, which brings the cost to 1727.30-1554.01=173.29 higher than the sequential scan.

The clue is in this line showing that all my rows were fetched from heap page, which is the table: Heap Fetches: 10000

Tuple visibility

In ACID databases, a modification must not be visible by others until the transaction completion (commit). There are two ways to achieve that. The first way is to read the latest version of data: lock in share mode what you read, so that no concurrent update can happen. The other solution is to query a previous version of data (MVCC – Multi Version Concurrency Control) where uncommitted changes are not visible. Both Oracle and Postgres use MVCC which is great because you can have transactions and queries on the same database. But they do the versioning at a different level.

Oracle MVCC is physical, at block level. Then everything is versioned: tables as well as index, with their transaction information (ITL) which, with the help of the transaction table, give all information about visibility: committed or not, and with the commit SCN. With this architecture, a modified block can be written to disk even with uncommitted changes and there is no need to re-visit it later once the transaction is committed.

Postgres MVCC is logical at row (‘tuple’) level: new version is a new row, and committed changes set the visibility of the row. The table row is versioned but not the index entry. If you access by index, you still need to go to the table to see if the row is visible to you. This is why I had heap fetches here and the table blocks were read.

This explains that the cost of Index Only Scan is high here. In addition to about 30 index blocks to read, I’ve read about 1429 table blocks. But that can be worse. For each index entry, and I have 10000 of them, we need to go to the table row, which is exactly what the 10000 heap fetches are. But I’m lucky because I have a very good clustering factor: I have created the table with increasing values for the column N (generated by generate_series). With a bad clustering factor (physical storage of rows in the table not correlated with the order of index) you would see up to 10000 additional shared hits. Thankfully, the query planner estimates this and has switched to table scan which is cheaper in this case.

Vacuum and Visibility Map

Always going to the table rows to see if they are committed would always be more expensive than a table scan. The Postgres vacuum process maintains a Visibility Map as a bitmap of pages that have been vacuumed and have no more tuples to vacuum. This means that all rows in those pages are visible to all transactions. When there is an update on the page, the flag is unset, and remains unset until the modification is committed and the vacuum runs on it. This visibility flag is used by the Index Only Scan to know if it is needed to get to the page.

Let’s run the vacuum and try again the same query:

vacuum demo1;
VACUUM
 
explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=295.29..295.30 rows=1 width=8) (actual time=2.192..2.193 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=30
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..270.29 rows=10000 width=4) (actual time=0.150..1.277 rows=10000 loops=1)
Output: n
Heap Fetches: 0
Buffers: shared hit=30
Planning time: 0.450 ms
Execution time: 2.213 ms

Here, without any hint, the query planner has chosen the Index Only Scan which is now less expensive than a Seq Scan: cost=0.29..270.29

Cost of Index Only Scan

There is an initial cost of 0.29 is calculated from cpu_operator_cost which defaults 0.0025 which means that about 0.29/0.0025=116 operations were charged here. This cost is minimal and I don’t go into details.
CaptureIndexScanpgora
Then, to get rows we have to

  • read 30 blocks from the index. Those seem to be random scan (with random_page_cost=4) and then the cost for all rows is 4*30=120
  • process the index entries (with cpu_index_tuple_cost=0.005) and then the cost for all 10000 rows is 0.005*10000=50
  • process the result rows (with cpu_tuple_cost=0.01) and then the cost for all 10000 rows is 0.01*10000=100

This brings the cost to the total of 270.29

For the above operation, the SUM(N) this is exactly the same as in the previous post on Seq Scan: cost=25 (cpu_operator_cost=0.0025 for 10000 rows) and is this initial cost because the sum is now only when all rows are processed, and an additional 0.01 for the result row.

Oracle

In the previous post I used the FULL() hint to compare Oracle Full Table Scan to Postgres Seq Scan, but by default, Oracle chose an index only access because the index covers all the rows and columns we need.

All columns that we need:

In the previous post we have seen the column projection (from the +projeciton format of dbms_xplan):

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("N")[22] 2 - (rowset=256) "N"[NUMBER,22]

I need only the column N from the table DEMO1, and this column is in the index DEMO1_N

All rows that we need:

In Oracle an index does not have an entry for every row but only for rows where at least one of the indexed columns is not null. Here because we have no where clause predicate on N, and because we have not declared the column N as NOT NULL, the access by index may not return all rows. However, the SUM() function does not need to know about the null values, because they don’t change the sum and then the optimizer can safely choose to do an index only access.

Here is the query without hints:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6z194712fvcfu, child number 0
-------------------------------------
select /*+ */ sum(n) from demo1
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 1 |00:00:00.01 | 26 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 26 |
| 2 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 26 |
--------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("N")[22] 2 - "N"[NUMBER,22]

This plan looks very similar to the Postgres one after the vacuum: 51 buffers which is approximately the number of blocks in my index here. However, Oracle does not have the ‘vacuum’ requirement because the MVCC applies to the index and Oracle does not need to go to the table to undo the uncommitted changes. But there is something else here. If you remember the previous post, the Oracle cost=1 is equivalent to the cost of a random read (single block) and the cost of reading one block through a larger I/O (multiblock read) is, with default statistics, about 0.278 times cheaper. Here, 7/26= 0.2692 which proves that the cost is based on multiblock reads. Oracle can read indexes with INDEX FAST FULL SCAN in the same way it reads table with FULL TABLE SCAN: with larger I/O. We don’t need any ordering of rows here, because we just do the sum, and then we don’t need to follow the chain of leaf blocks, scattered within the index segment. Just read all blocks as they come, with fast I/O.

Index Fast Full Scan is possible in Oracle because MVCC is at block level for indexes as well as tables. You can just read the blocks as of the point in time of the query, without being concerned by concurrent operations that update the index entries or split the blocks. Postgres Index Only Scan is limited because MVCC is on tables only, and then must scan the index in the order of leaves, and must read the visibility map and maybe the table pages.

In Oracle, an index can be used to partition vertically a table, asa redundant storage of a few columns in order to avoid full table scans on large rows, allowing queries to avoid completely to read the table when the index covers all required rows and columns. We will see more about the ‘all rows’ requirement in the next post.

 

Cet article Postgres vs. Oracle access paths II – IndexOnlyScan est apparu en premier sur Blog dbi services.

Pages