Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

How to generate random names, birth dates and more

Quick demo video showing how to generate random data related to persons, such as names (first, middle and last names), birth dates, job titles and more.

TO_DOG_YEAR

Some members of the Oracle community got well and truly into the April Fools Day spirit this year.

There were plenty of very earnest looking blog posts about a new 18c function – “TO_DOG_YEAR”.  You can read their posts here

http://www.oralytics.com/2018/04/predicting-ibs-in-dogs-using-oracle-18c.html 
https://blog.dbi-services.com/after-iot-iop-makes-its-way-to-the-database/
http://berxblog.blogspot.ie/2018/04/more-fun-with-ages.html
http://vanpupi.stepi.net/2018/04/01/exploring-18c-exadata-functions/

They even enlisted the help of the team here at AskTOM where they posed a question looking for more details here.

But naturally, it was important to get as many puns and hints into our answer as possible – did you spot them all ? Smile

 

image

New day….new month….New AskTOM

It’s a big day here at AskTOM HQ !

After IoT, IoP makes its way to the database

At each new Oracle version, I like to check what’s new, not only from the documentation, but also from exposed internals. I look (and sometimes diff) on catalog views definitions, undocumented parameters, and even the new C functions in the libraries. At last Oak Table World, I was intrigued by this V$SQLFN_METADATA view explained by Vit Spinka when digging into the internals of how execution plans are stored. This view has entries with all SQL functions, and a VERSION column going from ‘V6 Oracle’ to ‘V11R1 Oracle’. The lastest functions has an ‘INVALID’ entry and we also can see some functions with ‘SQL/DS’. Well, now that we have Oracle 18c on the Oracle Cloud, I came back to this view to see if anything is new, listing the highest FUNC_ID at the top and the first row attired my attention:


SQL> select * from V$SQLFN_METADATA order by 1 desc fetch first 10 rows only;
 
FUNC_ID NAME MINARGS MAXARGS DATATYPE VERSION ANALYTIC AGGREGATE OFFLOADABLE DISP_TYPE USAGE DESCR CON_ID
------- ---- ------- ------- -------- ---------- -------- --------- ----------- --------- ----- ----- ------
1148 TO_DOG_YEAR 1 4 NUMERIC V13 Oracle NO NO YES NORMAL TO_DOG_YEAR 0
1147 JSON_MERGEPATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_MERGEPATCH 0
1146 JSON_PATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_PATCH 0
1145 ROUND_TIES_TO_EVEN 1 2 NUMERIC INVALID NO NO YES NORMAL ROUND_TIES_TO_EVEN 0
1144 CON_ID_TO_CON_NAME 1 0 UNKNOWN INVALID NO NO NO NORMAL CON_ID_TO_CON_NAME 0
1143 TIMESTAMP_TO_NUMBER 1 1 UNKNOWN INVALID NO NO YES NORMAL TIMESTAMP_TO_NUMBER 0
1142 TO_UTC_TIMESTAMP_TZ 1 0 UNKNOWN INVALID NO NO YES NORMAL TO_UTC_TIMESTAMP_TZ 0
1141 OPTSYSAPPROXRANK 1 0 UNKNOWN INVALID NO NO NO NORMAL Internal evaluation function for multiple approx_rank's 0
1140 APPROX_RANK 1 1 NUMERIC INVALID NO YES NO NORMAL APPROX_RANK 0
1139 APPROX_SUM 1 2 NUMERIC INVALID NO YES NO NORMAL APPROX_SUM 0

Because those functions are SQL functions, I searched this ‘TO_DOG_YEAR’ on Google to see whether a new ANSI SQL function was implemented. But finally came upon something I didn’t expect: Dog Years Calculator. The trends in databases are really going crazy these times. All focus is on developers. XML, JSON, Docker… and now a function to calculate your age in dog years.
But afterall, it makes sense. IoT (not ‘Index Organized Table’ but ‘Internet Of Things’) is coming with sensors everywhere. And it is not only ‘things’ but it comes to living beings. I have read recently about ‘Internet of Pets’ where collars equipped with sensors detect where your domestic animal go and when he is hungry.

Let’s test it. Tomorrow, my elder kid has his 13th birthday. Now Oracle can tell me that he will be 65 in dog years:

SQL> select to_dog_year(date'2005-04-02') from dual;
 
TO_DOG_YEAR(DATE'2005-04-02')
-----------------------------
65

Yes, here I learn that the calculation is a bit more complex than just multiplying by 7. Of course, adding a SQL standard function would not make sense if it was just a multiplication.

But it seems to be even more complex. I searched for the C functions behind this one:

[oracle@CLOUD18C ~]$ nm /u01/app/oracle/product/18.0.0/dbhome_1/bin/oracle | grep -iE "dog.*year"
000000001452e073 r KNCLG_TODOGYEAR
0000000003ffcf40 T LdiJDaysDogYear
000000000f3170c0 T LdiJulianDogYear
000000000f316fc0 T LdiJulianDogYeararr
000000000f3170f0 t LdiJulianDogYeari
000000000f606e10 T OCIPConvertDateDogYearTime
000000000ebf2380 t qerxjConvertDogYearTime
0000000010de19e0 t qjsngConvStructDogYear
0000000010de0320 T qjsngNumberDogYearDty
0000000010de06f0 T sageStringDogYearDty
0000000010de7110 T sageplsDogYear
000000000bc5cd80 t sagerwAddDogYearTime
0000000010bad3c0 T qmxtgrConvSaxDogYear
0000000010bad400 T qmxtgrConvSaxDogYear_internal
00000000025ae090 T qosDateTimeDogYear
0000000004f22b60 T xsCHDogYeartime
000000000438c230 T nlsBreedDogYear
000000000438bb50 t nlsBreedDogYearCmn
000000000438c060 T nlsBreedDogYearTime
000000000438bc50 T nlsBreedDogYear
00000000044d1da0 T xvopAddDTDurDogYear
00000000044d1ac0 T xvopAddYMDurDogYear

Those ‘nlsBreed’ functions ring a bell and I checked if there are new values in V$NLS_VALID_VALUES

SQL> select distinct parameter from V$NLS_VALID_VALUES;
 
PARAMETER
----------------------------------------------------------------
TERRITORY
CHARACTERSET
BREED
LANGUAGE
SORT

That ‘BREED’ is a new one, with a lot of interesting values:

CaptureBREED

And here is my example using this new NLS parameter.

SQL> select to_dog_year(date'2005-04-02','','NLS_BREED=Saint Bernard') from dual;
 
TO_DOG_YEAR(DATE'2005-04-02',
----------------------------
96

Note that I’ve no idea about the second parameter, I had to put a ‘null’ for it to be able to mention the NLS one, or I got a ‘ORA-00909: invalid number of arguments’.

I have to say that, for a DBA focused on the core database functions, it is hard to understand that new features go on things like this TO_DOG_YEAR function. But being realistic, it is clear that the budget for new features go into the new direction: all for developers, big data, IoT… Of course we can write those functions in PL/SQL or maybe one day with JavaScript thanks to the Multi-Lingual Engine currently in beta. But IoT is also about performance, and a standard function avoids context switches.

Added a few hours later

There are a lot of questions about this new function. Here are some links to go further as many people in the Oracle Community have analyzed it further:

Martin Berger tested performance: http://berxblog.blogspot.ch/2018/04/more-fun-with-ages.html
Pieter Van Puymbroeck realized it was offloaded in Exadata: http://vanpupi.stepi.net/2018/04/01/exploring-18c-exadata-functions/
Brendan Thierney reveald a project he worked on in beta: http://www.oralytics.com/2018/04/predicting-ibs-in-dogs-using-oracle-18c.html
Øyvind Isene provides a way to test it with a cloud discount: http://oisene.blogspot.ch/2018/04/oracle-is-best-database-for-your-pets.html

Update 2-APR-2018

A little update for those who didn’t realize this was posted on 1st of April. It was an April Fool common idea from some Oracle Community buddies on the post-UKOUG_TECH17 trip. And what remains true all the year is how this community is full of awesome people. And special thanks to Connor who added great ideas here :)

 

Cet article After IoT, IoP makes its way to the database est apparu en premier sur Blog dbi services.

BIG NEWS: I’ve Been Selected On “Journey To Mars” Program !!

HAPPY APRIL FOOLS !! After many months of nervous waiting, knowing that my chances were extremely slim at best, I have just received my confirmation letter from NASA saying that I’ve been one of the lucky ones selected to participate in the “Journey To Mars” program !! I’m soooo damn excited !!! Planning is now […]

Whitepaper Announcement: Benchmarking Amazon Aurora.

This is just a quick blog post to inform readers of a good paper that shows some how-to information for benchmarking Amazon Aurora PostgreSQL. This is mostly about sysbench which is used to test transactional capabilities.

As an aside, many readers my have heard that I’m porting SLOB to PostgreSQL and will make that available in May 2018. It’ll be called “pgio” and is an implemention of the SLOB Method as described in the SLOB documentation. Adding pgio, to tools like sysbench, rounds-out the toolkit for testing platform readiness for your PostgreSQL applications.

To get a copy of the benchmarking paper, click here.

#000000;" src="https://kevinclosson.files.wordpress.com/2018/04/apg-paper.png?w=500&h=649" alt="" width="500" height="649" srcset="https://kevinclosson.files.wordpress.com/2018/04/apg-paper.png?w=500&h=649 500w, https://kevinclosson.files.wordpress.com/2018/04/apg-paper.png?w=115&h=150 115w, https://kevinclosson.files.wordpress.com/2018/04/apg-paper.png?w=231&h=300 231w, https://kevinclosson.files.wordpress.com/2018/04/apg-paper.png 676w" sizes="(max-width: 500px) 100vw, 500px" />

Docker: efficiently building images for large software

I see increasing demand to build a Docker image for the Oracle Database. But the installation process for Oracle does not really fit the Docker way to install by layers: you need to unzip the distribution, install from it to the Oracle Home, remove the things that are not needed, strop the binaries,… Before addressing those specific issues, here are the little tests I’ve done to show how the build layers increase the size of the image.

I’m starting with an empty docker repository on XFS filesystem:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 33M 80G 1% /var/lib/docker

add, copy, rename and append

For the example, I create a 100MB file in the context:

[root@VM121 docker]# mkdir -p /var/tmp/demo
[root@VM121 docker]# dd if=/dev/urandom of=/var/tmp/demo/file0.100M count=100 bs=1M

Here his my docker file:

FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M

The 1st step starts with an alpine image
The 2nd step sets the working directory
The 3rd step adds a 100M file from the context
The 4th step copies the file, so that we have 200M in two files
The 5th step removes the previous file, so that we have 100M in one file
The 6th step renames the file, staying with only one 100M file
The 7th step appends 100M to the file, leaving 200M in one file

Here is the build with default option:

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo

The context, my 100M files is send first:

Sending build context to Docker daemon 104.9MB

And here are my 7 steps:

Step 1/7 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/7 : WORKDIR /var/tmp
Removing intermediate container 93d1b5f21bb9
---> 131b3e6f34e7
Step 3/7 : ADD file0.100M .
---> 22ca0b2f6424
Step 4/7 : RUN cp file0.100M file1.100M
---> Running in b4b1b9c7e29b
Removing intermediate container b4b1b9c7e29b
---> 8c7290a5c87e
Step 5/7 : RUN rm file0.100M
---> Running in 606e2c73d456
Removing intermediate container 606e2c73d456
---> 5287e66b019c
Step 6/7 : RUN mv file1.100M file2.100M
---> Running in 10a9b379150e
Removing intermediate container 10a9b379150e
---> f508f426f70e
Step 7/7 : RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Running in 9dcf6d80642c
100+0 records in
100+0 records out
Removing intermediate container 9dcf6d80642c
---> f98304641c54
Successfully built f98304641c54
Successfully tagged franck/demo:latest

So, what’s the size of my docker repository after my image with this 200M file?

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 538M 80G 1% /var/lib/docker

I have more than 500MB here.

Actually, besides the alpine image downloaded, which is only 4MB, the image I have build is 538MB:

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest f98304641c54 Less than a second ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

We can better understand this size by looking at intermediate images:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest f98304641c54 1 second ago 528MB
f508f426f70e 27 seconds ago 319MB
5287e66b019c 36 seconds ago 214MB
8c7290a5c87e 37 seconds ago 214MB
22ca0b2f6424 42 seconds ago 109MB
131b3e6f34e7 47 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

The first one, ’22ca0b2f6424′ is from the step 3 which added the 100MB file
The second one ‘8c7290a5c87e’ is from the 4th step which copied the file, bringing the image to 200MB
The third one ‘5287e66b019c’ is from the 5th step which removed the file. I didn’t increase the size but didn’t remove anything either.
The fourth one ‘f508f426f70e’ is from the 6th step which renamed the file. But this, for docker, is like copying to a new layer and that adds 100MB
Finally, the 7th step appended only 100MB, but this finally resulted to copy the full 200MB file to the new layer

We can see all those operations, and size added at each step, from the image history:

[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
f98304641c54 1 second ago /bin/sh -c dd if=/dev/urandom of=file2.100M … 210MB
f508f426f70e 27 seconds ago /bin/sh -c mv file1.100M file2.100M 105MB
5287e66b019c 36 seconds ago /bin/sh -c rm file0.100M 0B
8c7290a5c87e 37 seconds ago /bin/sh -c cp file0.100M file1.100M 105MB
22ca0b2f6424 42 seconds ago /bin/sh -c #(nop) ADD file:339435a18aeeb1b69… 105MB
131b3e6f34e7 47 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

All in one RUN

One workaround is to run everything in the same layer. Personally, I don’t like it because I don’t get the point of using a Dockerfile for just running one script.
So, here is the Dockerfile with only one RUN command:

FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M \
&& rm file0.100M \
&& mv file1.100M file2.100M \
&& dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M

The build is similar except that there are fewer steps:

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo
Sending build context to Docker daemon 104.9MB
Step 1/4 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/4 : WORKDIR /var/tmp
Removing intermediate container 707644c15547
---> d4528b28c85e
Step 3/4 : ADD file0.100M .
---> e26215766e75
Step 4/4 : RUN cp file0.100M file1.100M && rm file0.100M && mv file1.100M file2.100M && dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Running in 49c2774851f4
100+0 records in
100+0 records out
Removing intermediate container 49c2774851f4
---> df614ac1b6b3
Successfully built df614ac1b6b3
Successfully tagged franck/demo:latest

This leaves us with a smaller space usage::

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 340M 80G 1% /var/lib/docker

The image is smaller, but still larger than the final state (a 300MB image for only one 200MB file):

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest df614ac1b6b3 Less than a second ago 319MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

This is because we have grouped the RUN steps, but the ADD has its own layer, adding a file that is removed later:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest df614ac1b6b3 Less than a second ago 319MB
e26215766e75 20 seconds ago 109MB
d4528b28c85e 22 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
df614ac1b6b3 Less than a second ago /bin/sh -c cp file0.100M file1.100M … 210MB
e26215766e75 20 seconds ago /bin/sh -c #(nop) ADD file:fe0262a4b800bf66d… 105MB
d4528b28c85e 22 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

This is the kind of issue we have when building an Oracle Database image. We need to ADD the zip file for the database distribution, and the latest bundle patch. It is removed later but still takes space on the image. Note that one workaround to avoid the ADD layer can be to get the files from an NFS or HTTP server with wget or curl in a RUN layer rather than an ADD one. There’s an example on Stefan Oehrli blog post.

–squash

With the latest versions of docker, there’s an easy way to flatten all those intermediary images at the end.
Here I’ve 18.03 and enabled experimental features:

[root@VM121 docker]# docker info
Containers: 0
Running: 0
Paused: 0
Stopped: 0
Images: 8
Server Version: 18.03.0-ce
Storage Driver: overlay2
Backing Filesystem: xfs
...
 
[root@VM121 docker]# cat /etc/docker/daemon.json
{
"experimental": true
}

I start with the same as before but just add –squash to the build command

[root@VM121 docker]# docker image build --squash -t franck/demo /var/tmp/demo

The output is similar but the image is an additional one, reduced down to the size of my final state (with one 200MB file):

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 Less than a second ago 214MB
c3058e598b0a 3 seconds ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

The intermediate image list shows that all was done as without ‘–squash’ but with an additional set which reduced the size:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 Less than a second ago 214MB
c3058e598b0a 3 seconds ago 528MB
1f14d93a592e 23 seconds ago 319MB
7563d40b650b 27 seconds ago 214MB
8ed15a5059bd 28 seconds ago 214MB
24b11b9026ce 31 seconds ago 109MB
382bb71a6a4a 33 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

This step is visible in the image history as a ‘merge’ step:

[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
2ab439a723c4 Less than a second ago 210MB merge sha256:c3058e598b0a30c606c1bfae7114957bbc62fca85d6a70c2aff4473726431394 to sha256:3fd9065eaf02feaf94d68376da52541925650b81698c53c6824d92ff63f98353
3 seconds ago /bin/sh -c dd if=/dev/urandom of=file2.100M … 0B
23 seconds ago /bin/sh -c mv file1.100M file2.100M 0B
27 seconds ago /bin/sh -c rm file0.100M 0B
28 seconds ago /bin/sh -c cp file0.100M file1.100M 0B
31 seconds ago /bin/sh -c #(nop) ADD file:14cef588b48ffbbf1… 0B
33 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

However, even if I have a smaller final image, my filesystem usage is even larger with this additional 210MB:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 739M 80G 1% /var/lib/docker

Let’s prune it to get rid of those intermediate images:

[root@VM121 docker]# docker image prune -f
Deleted Images:
deleted: sha256:c3058e598b0a30c606c1bfae7114957bbc62fca85d6a70c2aff4473726431394
deleted: sha256:37ed4826d70def1978f9dc0ddf42618d951f65a79ce30767ac3a5037d514f8af
deleted: sha256:1f14d93a592eb49a210ed73bf65e6886fcec332786d54b55d6b0e16fb8a8beda
deleted: sha256:c65cf4c70aed04e9b57e7a2a4fa454d3c63f43c32af251d8c86f6f85f44b1757
deleted: sha256:7563d40b650b2126866e8072b8df92d5d7516d86b25a2f6f99aa101bb47835ba
deleted: sha256:31ee5456431e903cfd384b1cd7ccb7918d203dc73a131d4ff0b9e6517f0d51cd
deleted: sha256:8ed15a5059bd4c0c4ecb78ad77ed75da143b06923d8a9a9a67268c62257b6534
deleted: sha256:6be91d85dec6e1bda6f1c0d565e98dbf928b4ea139bf9cb666455e77a2d8f0d9
deleted: sha256:24b11b9026ce738a78ce3f7b8b5d86ba3fdeb15523a30a7c22fa1e3712ae679a
deleted: sha256:c0984945970276621780a7888adfde9c6e6ca475c42af6b7c54f664ad86f9c9f
deleted: sha256:382bb71a6a4a7ddec86faa76bb86ea0c1a764e5326ad5ef68ce1a6110ae45754
 
Total reclaimed space: 524.3MB

Now having only the squashed image:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 32 minutes ago 214MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 237M 80G 1% /var/lib/docker

multi-stage build

Finally, you can do something similar to an intermediate squash using multi-stage build.

Here is my Dockerfile:

FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
 
FROM alpine:latest
WORKDIR /var/tmp
COPY --from=staging /var/tmp .

With multi-stage build, we can start the second stage from a different image, and add more steps, but here I just start with the same alpine image and copy the final layer of the previous build.

We see something very similar to the –squash one:

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 55f329385f8c Less than a second ago 214MB
fd26a00db784 8 seconds ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 55f329385f8c 1 second ago 214MB
fd26a00db784 9 seconds ago 528MB
9bf5be367b63 32 seconds ago 319MB
531d78833ba8 35 seconds ago 214MB
05dd68114743 36 seconds ago 214MB
b9e5215a9fc8 39 seconds ago 109MB
ab332f486793 41 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

The history of the last stage shows the copy of 210MB from the previous one:

[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
55f329385f8c 1 second ago /bin/sh -c #(nop) COPY dir:2b66b5c36eff5b51f… 210MB
ab332f486793 41 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

The usage of filesystem is similar to the –squash one. Even if we reduced the final image, all the intermediate states had to be stored:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 737M 80G 1% /var/lib/docker

That looks good, if you accept to use a large intermediate space while building the image, which gives you the possibility to debug without re-running from the beginning, thanks to the layers in cache. However, you have still the inefficiency that each time you try the build, the context will be sent again even when not needed. And that is long with a 3GB .zip in the case of Oracle Database installation. Unfortunately, if you add the file to the .dockerignore once you know you have the ADD steps in cache, the next build attempt will not use the caches anymore. I would love to see a per-stage .dockerignore file for multi-stage builds. Or simply have docker realize that some files in the context will not be needed by the COPY or ADD that are not in cache yet.

Sending the whole context at each build attempt, when debugging your Dockerfile, is not efficient at all and looks like punch-card time compilation where people sent the cards to be compiled during the night. One syntax error on the first line and you go for another day.

One solution is to have all the required files in an NFS or HTTPd server and get them with ADD from the URL as mentioned earlier.

Multi-stage with multi-contexts

Another solution is to put all COPY or ADD from context in one Dockerfile to build the image containing all required files, and then build your image from it (and squash it at the end).

Here is my first Dockerfile, just adding the files from the context:

[root@VM121 docker]# ls /var/tmp/demo
Dockerfile file0.100M nocontext
[root@VM121 docker]# cat /var/tmp/demo/Dockerfile
FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .

I build this ‘staging’ image:

[root@VM121 docker]# docker image build -t franck/stage0 /var/tmp/demo
Sending build context to Docker daemon 104.9MB
Step 1/3 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/3 : WORKDIR /var/tmp
Removing intermediate container 0eeed8e0cfd2
---> a5db3b29c8e1
Step 3/3 : ADD file0.100M .
---> 2a34e1e981be
Successfully built 2a34e1e981be
Successfully tagged franck/stage0:latest

This one is the minimal one:

[root@VM121 docker]# docker image ls
+ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/stage0 latest 2a34e1e981be Less than a second ago 109MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 139M 80G 1% /var/lib/docker

Now, I don’t need to send this context anymore during further development of my Dockerfile.
I’ve added the following steps to a Dockerfile in another directory:

[root@VM121 docker]# ls /var/tmp/demo/nocontext/
Dockerfile
[root@VM121 docker]# cat /var/tmp/demo/nocontext/Dockerfile
FROM franck/stage0 as stage1
WORKDIR /var/tmp
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
FROM alpine:latest
WORKDIR /var/tmp

Here is the build, using multi-stage to get a squashed final image (you can also use –squash)

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo/nocontext
 
Sending build context to Docker daemon 2.048kB
Step 1/9 : FROM franck/stage0 as stage1
---> 2a34e1e981be
Step 2/9 : WORKDIR /var/tmp
Removing intermediate container eabf57a8de05
...
Successfully built 82478bfa260d
Successfully tagged franck/demo:latest

At that point, there’s no advantage on space used as I keep all layers for easy Dockerfile development:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 738M 80G 1% /var/lib/docker
 
[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 82478bfa260d About a minute ago 214MB
5772ad68d208 About a minute ago 528MB
franck/stage0 latest 2a34e1e981be About a minute ago 109MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

But now, if I want to add an additional step:

[root@VM121 docker]# cat >> /var/tmp/demo/nocontext/Dockerfile <<< 'RUN chmod a+x /var/tmp'

I can re-build quickly, using cached layers, and without the need to send the context again:

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo/nocontext
Sending build context to Docker daemon 2.048kB
Step 1/10 : FROM franck/stage0 as stage1
---> 2a34e1e981be
Step 2/10 : WORKDIR /var/tmp
---> Using cache
---> fa562926cc2b
Step 3/10 : RUN cp file0.100M file1.100M
---> Using cache
---> 31ac716f4d61
Step 4/10 : RUN rm file0.100M
---> Using cache
---> d7392cf51ad9
Step 5/10 : RUN mv file1.100M file2.100M
---> Using cache
---> 4854e503885b
Step 6/10 : RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Using cache
---> 5772ad68d208
Step 7/10 : FROM alpine:latest
---> 3fd9065eaf02
Step 8/10 : WORKDIR /var/tmp
---> Using cache
---> a5db3b29c8e1
Step 9/10 : COPY --from=stage1 /var/tmp .
---> Using cache
---> 82478bfa260d
Step 10/10 : RUN chmod a+x /var/tmp
---> 4a69ee40a938
Successfully built 4a69ee40a938
Successfully tagged franck/demo:latest

Once I’m ok with my final image, I can remove the intermediate ones:

[root@VM121 docker]# docker image prune -f
Deleted Images:
deleted: sha256:5772ad68d20841197d1424f7c64edd21704e4c7b470acb2193de51ae8741385d
deleted: sha256:bab572d749684d126625a74be4f01cc738742f9c112a940391e3533e61dd55b9
deleted: sha256:4854e503885b4057809fe2867a743ae7898e3e06b329229519fdb5c9d8b10ac1
deleted: sha256:de4acb90433c30d6a21cc3b4483adbd403d8051f3c7c31e6bc095a304606355a
deleted: sha256:d7392cf51ad99d5d0b7a1a18d8136905c87bc738a5bc94dec03e92f5385bf9c8
deleted: sha256:f037e7f973f4265099402534cd7ba409f35272701166d59a1be8e5e39508b07c
deleted: sha256:31ac716f4d61f0048a75b8de6f18757970cf0670a0a3d711e4386bf098b32041
deleted: sha256:2dccb363c5beb4daf45586383df6454b198f824d52676f70318444c346c0fe9a
deleted: sha256:fa562926cc2b3cb56400e1068984bb4048f56713a3cf6dcfa3cf6d945023ebc4
 
Total reclaimed space: 419.4MB

And the staging one:

[root@VM121 docker]# docker image rm franck/stage0
Untagged: franck/stage0:latest
Deleted: sha256:2a34e1e981be9154c31c5ee7eb942cc121267f4416b6fe502ab93f2dceafd98c
Deleted: sha256:b996a1bdc829167f16dcbe58b717284764470661c3116a6352f15012e1dff07c

Finally, I optimized the developement of the Dockerfile and finished with the minimal size.

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 237M 80G 1% /var/lib/docker

So what?

I’m always surprised by the lack of efficiency when building an image with a Dockerfile. Any serious application deployment involves several intermediate files and the way docker build is layered inflates the size and the time required. Efficient layering and snapshotting work at block level. Here, at file level, any byte of data modified in a file, even metadata such as the file name, is a whole file copy. But for common applications, the installation steps are not as simple adding new files. You may have files appended, object files added to libraries, then compiled, the stripped…

In this post, I tested some recent features, such as multi-stage build and the experimental –squash, as well as a simple manual multi-stage build. Of course, you can do everything in the same layers, and even not use Dockerfiles at all, but then why using Docker? There’s also the Packer approach that I’ve not tested yet. However, I like the Docker approach, but only when used correctly. Deploying an application, like Oracle Database, should use the layered build in the following way: additional steps for new options or new updates. This means that the files must be built elsewhere, in a staging container, and added in one step. And to be efficient, the context should be sent only when needed: when a non-cached ADD or COPY requires it.

 

Cet article Docker: efficiently building images for large software est apparu en premier sur Blog dbi services.

Docker-CE on Oracle Enterprise Linux 7

Here is how I install the latest Docker version on Oracle Linux 7. You find several blog posts about it which all install ‘docker-engine’. But things move fast in this agile world and docker package name has changed. The Community Edition is now ‘docker-ce’ and you want this one to run the latest version.

I’m on OEL 7.4 but should also wotj on RHEL 7:
[root@VM188 yum]# cat /etc/oracle-release
Oracle Linux Server release 7.4

docker-engine

If you enable [ol7_addons] you can install ‘docker-engine:’

# yum-config-manager --enable ol7_addons
# yum info docker-engine
Loaded plugins: ulninfo
Available Packages
Name : docker-engine
Arch : x86_64
Version : 17.06.2.ol
Release : 1.0.1.el7
Size : 21 M
Repo : ol7_addons/x86_64
Summary : The open-source application container engine
URL : https://dockerproject.org
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.

But forget it. That’s 17.06 which is 6 months old. You should consider Docker as a puppy. 6 month in puppy’s years is like 3 human years. So many changes happened.

You can remove all those old things:

# yum remove docker docker-common docker-selinux docker-engine

docker-ce

I’ve not found ‘docker-ce’ on OL7 repositories, as only the Enterprise Edition is there. Then I added the CentOS repo (with yum-config-manager that you can get with yum-utils if you don’t have it already):

yum -y install yum-utils
yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo

Once done, I’ve just installed Docker Community Edition with:

yum -y install docker-ce

And, at that time I got the version 17.12 easily:

[root@VM188 ~]# yum info docker-ce
Loaded plugins: ulninfo
Installed Packages
Name : docker-ce
Arch : x86_64
Version : 17.12.0.ce
Release : 1.el7.centos
Size : 123 M
Repo : installed
From repo : docker-ce-stable
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.
...

But now there’s a new version available:

Available Packages
Name : docker-ce
Arch : x86_64
Version : 18.03.0.ce
Release : 1.el7.centos
Size : 35 M
Repo : docker-ce-stable/x86_64
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.

The problem is that if you want to install docker-ce in this latest version, you will now get:

Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be installed
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Finished Dependency Resolution
Error: Package: docker-ce-18.03.0.ce-1.el7.centos.x86_64 (docker-ce-stable)
Requires: pigz
You could try using --skip-broken to work around the problem
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
2:microcode_ctl-2.1-22.5.0.3.el7_4.x86_64 has missing requires of kernel

(Ok Google, this is what you need to index…)

pigz

Starting from version 18.02 there’s a new dependency on ‘pigz’ for parallel gzip.

To get this ‘pigz’ package from the OL7 repository you need to enable EPEL in /etc/yum.repos.d/public-yum-ol7.repo

[ol7_developer_EPEL]
name=Oracle Linux $releasever Developement Packages ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

Now, I’m able to install the latest docker-ce:

[root@VM188 yum.repos.d]# yum install docker-ce
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:17.12.0.ce-1.el7.centos will be updated
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be an update
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Running transaction check
---> Package pigz.x86_64 0:2.3.4-1.el7 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Updating:
docker-ce x86_64 18.03.0.ce-1.el7.centos docker-ce-stable 35 M
Installing for dependencies:
pigz x86_64 2.3.4-1.el7 ol7_developer_EPEL 80 k
 
Transaction Summary
================================================================================================================================
Install ( 1 Dependent package)
Upgrade 1 Package
 
Total download size: 35 M
Is this ok [y/d/N]: y

Oracle Database on Docker

You may wonder why I install Docker on Oracle Linux rather than CentOS. The MOS Doc ID 2216342.1 mentions that Oracle will support customers running Oracle Database (single instance) in Docker containers running on Oracle Linux 7 with UEK4 or Red Hat Enterprise Linux 7.

If you want to validate your Docker install for running Oracle Database, the easiest is to use the image build script provided by Oracle:

git clone https://github.com/oracle/docker-images.git
cd ./docker-images/OracleDatabase/SingleInstance/dockerfiles/
# download and move linuxx64_12201_database.zip is in 12.2.0.1 subdirectory
sh buildDockerImage.sh -v 12.2.0.1 -e

Those are maintained by Gerald Venzl, Oracle product manager for database development, so they are obviously the best way to run Oracle Database on Docker. You can read all related best practices from the same author. Once you have that running, you have validated your environment and you can customize further if you want.

 

Cet article Docker-CE on Oracle Enterprise Linux 7 est apparu en premier sur Blog dbi services.

Hybrid histograms

Just a quick post here so I could consolidate some information about histograms in 12c.

On my last Office Hours session, one of the questions that was posed was asking for an explanation of the new types of histograms in use in 12c.  So I had a few slides and described conceptually at a high level how they work and what they represent.  If you’re new to 12c, or new to optimizer histograms in general, then take a look at the video below to get up to speed.  But for those who want to dig into a deeper level, I also saw recently two absolutely cracking articles which discuss the algorithms and implementation in much more detail.  So I’ve linked them here as well.

http://www.oracle.com/technetwork/articles/database/histogram-construction-oracle-12c-4426845.html

http://www.oracle.com/technetwork/articles/database/maintenance-histograms-db-12c-4426850.html

On that note…if you celebrate Easter – have a safe and happy break.  So you on the other side of the weekend!

Hare, Easter Bunny, Plush Bunny, Easter

Lock Types

Every now and again I have to check what a particular lock (or enqueue) type is for and what the associated parameter values represent. This often means I have to think about the names of a couple of views and a collection of columns – then create a few column formats to make the output readable (though sometimes I can take advantage of the “print_table()” procedure that Tom Kyte published a long time ago.  It only takes a little time to get the code right but it’s a nuisance when I’m in a hurry so I’ve just scribbled out a few lines of a script that takes a lock type as an input parameter and reports all the information I want.

rem
rem     Script:         lock_types.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2018
rem     Usage:          start lock_types {lock type}
rem

define m_lock_type='&1'

column  display new_value m_display

select
        case when substr(version,1,2) = '12'
        then
                'display_name'
        else
                'name'
        end                     display
from
        v$instance
;

set linesize 160
set pagesize 60
set trimspool on

column type             format a4
column name             format a32
column description      format a132
column id1_tag          format a32
column id2_tag          format a32
column is_user          format a4 heading "User"
column is_recycle       format a4 heading "Rcyc"

set feedback off
break on report skip 1

spool lock_types

select  *
from    V$lock_type
where   type = upper('&m_lock_type')
order by
        type
;

column  name                    format a42
column  parameter1              format a9
column  parameter2              format a24
column  parameter3              format a22
column  wait_class              format a14
column  display_name            format a42

select
        eve.name,
        eve.parameter1,
        eve.parameter2,
        eve.parameter3,
        eve.wait_class,
        nullif(eve.&m_display, eve.name) display_name
from
        v$event_name    eve
where
        eve.name like 'enq: ' || upper('&m_lock_type') || '%'
order by
        nullif(eve.wait_class,'Other'),
        eve.name
;

set feedback on

I’ve included a check (and hack) on the value of the major version because 12c introduced a “display_name” as well as a “name” for events, and the latter is sometimes a little more descriptive than the former, so it’s nice to have a single script that could print two different values for the versions that have them.

Here’s a sample of the output when I pass ‘IV’ as an input parameter:

TYPE NAME                             ID1_TAG                          ID2_TAG                          User Rcyc
---- -------------------------------- -------------------------------- -------------------------------- ---- ----
DESCRIPTION                                                                                                                              CON_ID
------------------------------------------------------------------------------------------------------------------------------------ ----------
IV   Library Cache Invalidation       object #                         time stamp                       NO   NO
Synchronizes library cache object invalidations across instances                                                                              0

NAME                                       PARAMETER PARAMETER2               PARAMETER3             WAIT_CLASS     DISPLAY_NAME
------------------------------------------ --------- ------------------------ ---------------------- -------------- ------------------------------------------
enq: IV -  contention                      type|mode id1                      id2                    Other

As you can see from the presence of the con_id column in v$lock_type this output came from a 12c instance. I picked the IV lock because that’s the one that prompted me to check the meanings of the id[12] and parameter[123] columns when a question about waits for the IV lock appeared recently on Oracle-L. I’ve got two reasons for carrying on with this particular example – first that it demonstrates that the descriptions can be wrong, second that it allows me to demonstrate a quick tip on translation.

The question on Oracle-L related to a 4-node RAC system and reported one instance suffering long waits on the IV enqueue on a fairly regular basis when running a particular batch task. The OP reported the following values as the p1, p2, p3 values from v$session while the wait was going on:


P1    type|mode 1230372869
P2    id1       1398361667
P3    id2                3

According to the details in v$lock_type the enqueue is about library cache invalidation across instances – and that fits the OPs complaint because the system is a RAC system. The id1 value is supposed to be an obj# (object_id), but the OP said it wasn’t; and the id2 value is supposed to be a timestamp, but 3 is an odd value for a timestamp (though it might represent – for example – the 3 second wait that is a common time-out interval for enqueues). So, clearly, the descriptions can be wrong.

Translation

Take another look at p1 and p2, and turn them into Hexadecimal:


1230372869 (dec) = 0x49560005 (hex)
1398361667 (dec) = 0x53594E43 (hex)

If you happen to be good with Hex and ASCII code you’ll know that 2-byte values in the range 41-5F are mostly the capital letters of the Roman alphabet (while 61 – 7f are mostly the lower case letters), so a second translation step gives us:


1230372869 (dec) = 0x49560005 (hex) = 'IV'  5
1398361667 (dec) = 0x53594E43 (hex) = 'SYNC'

The p1 parameter is described (correctly) as “type/mode” – this is an IV enqueue held or requested in mode 5; the p2 parameter is not an object number, it looks more like a text description of why the enqueue is being requested (the enqueue is, after all, described as being used to “synchronize library cache object invalidation”).

I still don’t know what the final parameter represents – I doubt if it’s really about a three second wait (but that could be checked by examining v$session_wait over a period of several seconds or v$session_wait_history), it might be an indication of the instance that the session is trying to synchronize with (though, again, that seems a long shot), or it might just be a “reason-code” describing why the synchronisation is necessary.

Whenever in doubt about the meaning for the various parameters, it’s always worth a quick dec -> hex -> ASCII check, just in case it offers some clues about the function of the enqueue.