Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 9 hours 14 min ago

SQL Saturday Paris 2018 – Pré-conférence SQL Server Linux, Docker et Azure

Mon, 2018-05-28 14:41

The next SQL Saturday in Paris is coming soon and the agenda has been published by the @GUSS on twitter. Le prochain SQL Saturday Paris arrive bientôt et l’agenda a été publié sur Twitter par le @GUSS

blog 135 - 0 - SQL Sat Paris 2018

Cette année, un savant mélange de speakers francophones et internationaux, un plus pour les participants …

Un rapide coup d’œil à l’agenda – si on se focalise sur la partie moteur SQL Server – montre également qu’une bonne partie du stream rouge concerne Docker et l’automatisation. Intéressant :)

Bien entendu les sujets sur la performance et l’architecture auront toujours leur popularité et vous pourrez continuer à y assister :) Ce n’est pas moi qui vais m’en plaindre :)

 

Pré-conférence SQL Server au delà de Windows : installation, déploiement et gestion

sqlsatparis2018

Comme vous le savez déjà, Microsoft s’est ouvert à l’Open Source depuis SQL Server 2017 et a également renforcé son offre Azure avec notamment les instances managées. Par conséquent l’écho-système autour de SQL Server s’est considérablement agrandi. Cette année j’aurai l’opportunité de partager quelques expériences à ce sujet lors d’une pré- conférence qui se déroulera le vendredi 6 juin 2017.

Nous aborderons ensemble des thématiques bien connus par tout administrateur de données comme l’installation, la configuration, le monitoring, la performance ou encore la haute disponibilité dans des environnements autre que Windows.

Pour vous inscrire c’est par ici.

Au plaisir de vous y retrouver!

 

 

 

Cet article SQL Saturday Paris 2018 – Pré-conférence SQL Server Linux, Docker et Azure est apparu en premier sur Blog dbi services.

EDB Failover Manager in EDB containers in Minishift/OpenShift

Mon, 2018-05-28 12:47

In the last three posts we deployed an EDB database container and two pgpool instances, scaled that up to include a read only replica and finally customized the PostgreSQL instance with ConfigMaps. In this post will we look at how the EDB Failover Manager is configured in the database containers.

This are the pods currently running in my environment, two pgpool containers and two PostgreSQL containers:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-gk8dt   1/1       Running   1          9d
edb-as10-0-1-n5z4w   1/1       Running   0          3m
edb-pgpool-1-h5psk   1/1       Running   1          9d
edb-pgpool-1-tq95s   1/1       Running   1          9d

The first one (edb-as10-0-1-gk8dt) is the primary instance and EFM should be running there as well:

dwe@dwe:~$ oc rsh edb-as10-0-1-gk8dt
sh-4.2$ psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 f
(1 row)

sh-4.2$ ps -ef | grep efm
edbuser    202     1  0 08:45 ?        00:00:04 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-7.b10.el7.x86_64/jre/bin/java -cp /usr/edb/efm-3.0/lib/EFM-3.0.0.jar -Xmx32m com.enterprisedb.efm.main.ServiceCommand __int_start /etc/edb/efm-3.0/edb.properties
sh-4.2$ 

Looking at the configuration there are some interesting points:

sh-4.2$ cat /etc/edb/efm-3.0/edb.properties | egrep -v "^$|^#"
db.user=edbuser
db.password.encrypted=ca78865e0f85d15edc6c51b2e5c0a58f
db.port=5444
db.database=edb
db.service.owner=edbuser
db.service.name=
db.bin=/usr/edb/as10/bin
db.recovery.conf.dir=/edbvolume/edb/edb-as10-0-1-gk8dt/pgdata
jdbc.sslmode=disable
user.email=none@none.com
script.notification=
bind.address=172.17.0.6:5430
admin.port=5431
is.witness=false
local.period=10
local.timeout=60
local.timeout.final=10
remote.timeout=10
node.timeout=10
stop.isolated.master=false
pingServerIp=8.8.8.8
pingServerCommand=/bin/ping -q -c3 -w5
auto.allow.hosts=false
db.reuse.connection.count=0
auto.failover=true
auto.reconfigure=true
promotable=true
minimum.standbys=0
recovery.check.period=2
auto.resume.period=0
virtualIp=
virtualIp.interface=
virtualIp.prefix=
script.fence=
script.post.promotion=/var/efm/post_promotion_steps.sh %f
script.resumed=
script.db.failure=/var/efm/stopEFM
script.master.isolated=
script.remote.pre.promotion=
script.remote.post.promotion=
script.custom.monitor=
custom.monitor.interval=
custom.monitor.timeout=
custom.monitor.safe.mode=
sudo.command=sudo
sudo.user.command=sudo -u %u
log.dir=/var/log/efm-3.0
jgroups.loglevel=
efm.loglevel=
jvm.options=-Xmx32m
kubernetes.port.range=1
kubernetes.namespace=myproject
kubernetes.pod.labels=cluster=edb
kubernetes.master.host=172.30.0.1
kubernetes.master.httpsPort=443
create.database.master=/var/lib/edb/bin/createmasterdb.sh
create.database.standby=/var/lib/edb/bin/createstandbydb.sh
kubernetes.is.init.master=true

The last 8 lines are not there when you do a manual EFM installation so this is something specific in the container deployment. Apparently it is EFM that creates the master and the replica instance(s). The rest is more or less the default setup. The cluster status should be fine then:

sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Master      172.17.0.6           UP     UP        
	Standby     172.17.0.8           UP     UP        

Allowed node host list:
	172.17.0.6

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.8

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.6           0/5000140        
	Standby     172.17.0.8           0/5000140        

	Standby database(s) in sync with master. It is safe to promote.

We should be able to do a switchover:

sh-4.2$ /usr/edb/efm-3.0/bin/efm promote edb -switchover    
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.
sh-4.2$ /usr/edb/efm-3.0/bin/efm cluster-status edb
Cluster Status: edb
VIP: 

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     172.17.0.6           UP     UP        
	Master      172.17.0.8           UP     UP        

Allowed node host list:
	172.17.0.6

Membership coordinator: 172.17.0.6

Standby priority host list:
	172.17.0.6

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      172.17.0.8           0/60001A8        
	Standby     172.17.0.6           0/60001A8        

	Standby database(s) in sync with master. It is safe to promote.

Seems it worked so the instances should have switched the roles and the current instance must be in recovery:

sh-4.2$ psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 t
(1 row)

Fine. This works as expected. So far for the first look at EFM inside the containers. It is not the same setup you’ll find when you install EFM on your own and EFM is doing more here than it does usually. A lot of stuff happens in the scripts provided by EDB here:

sh-4.2$ ls -la /var/lib/edb/bin/
total 72
drwxrwx---  2 enterprisedb root  4096 May 11 20:40 .
drwxrwx--- 24 enterprisedb root  4096 May 28 18:03 ..
-rwxrwx---  1 enterprisedb root  1907 Feb 17 17:14 cleanup.sh
-rwxrwx---  1 enterprisedb root  4219 May 10 22:11 createmasterdb.sh
-rwxrwx---  1 enterprisedb root  2582 May 11 03:30 createstandbydb.sh
-rwxrwx---  1 enterprisedb root  1491 May 10 22:12 dbcommon.sh
-rwxrwx---  1 enterprisedb root 10187 May 10 22:28 dbfunctions.sh
-rwxrwx---  1 enterprisedb root   621 May 10 22:15 dbsettings.sh
-rwxrwx---  1 enterprisedb root  5778 Apr 26 22:55 helperfunctions.sh
-rwxrwx---  1 enterprisedb root    33 Feb 18 03:43 killPgAgent
-rwxrwx---  1 enterprisedb root  5431 May 10 22:29 launcher.sh
-rwxrwx---  1 enterprisedb root   179 May 10 22:12 startPgAgent
-rwxrwx---  1 enterprisedb root   504 May 11 12:32 startPgPool

These scripts are referenced in the EFM configuration in several places and contain all the logic for initializing the cluster, starting it up, stopping and restarting the cluster, setting up replication and so on. To understand what really is going on one needs to understand the scripts (which is out of scope of this post).

 

Cet article EDB Failover Manager in EDB containers in Minishift/OpenShift est apparu en premier sur Blog dbi services.

Which Bitnami service to choose in the Oracle Cloud Infrastructure?

Fri, 2018-05-25 15:40

In the Oracle Cloud PaaS you have a marketplace where you can choose your service. Some are packaged from Bitnami and available on multiple OS. My first idea is that in PaaS you do not care about the OS. But Oracle Cloud has this very nice feature where you still have full access to the OS, as root, even in PaaS. Then, you choose the Linux distribution of your preference. Except if performance is different. They run on different Linux kernels. Is Oracle Linux Unbreakable Kernel more efficient?

We need to compare with relevant and reliable measures. And for that I’ve created a Postgres service and used Kevin Closson SLOB method, as I’m a lucky beta tester for pgio. I’ll post later about the creation of the Postgres service in the Oracle Cloud.

Cached IO

First, I’ve run cached IO to measure CPU performance.

I’ve run with the following settings in pgio.conf:

UPDATE_PCT=0
RUN_TIME=300
NUM_SCHEMAS=4
NUM_THREADS=1
WORK_UNIT=255
SCALE=1G

This is 4 schemas with 1GB of data. This fit in my 30GB host. Actually, here is a vmstat sample during the run showing 5GB cached and large free memory:

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
4 0 0 15508168 12444 5495652 0 0 0 0 1530 1631 28 22 50 0 0
4 0 0 15508292 12452 5495648 0 0 0 4 1506 1564 27 23 50 0 0

There’s no I/O to block device here, which is my goal.

I’ve run 10 times the runit.sh and here is the summary of main result with IOPS:


$ for i in allopc@144.21.82.244.out allopc@144.21.82.255.out allopc@144.21.82.252.out ; do grep os-release $i | grep PRETTY | tail -1 ; grep x86 $i | tail -1 ; grep -E "runit.out.*DBNAME" $i | tail -10 | nl ; done
 
Fri May 25 11:56:11 UTC 2018 /etc/os-release : PRETTY_NAME="Ubuntu 16.04.4 LTS"
Linux ubuntu 4.4.0-127-generic #153-Ubuntu SMP Sat May 19 10:58:46 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 11:11:05 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >393232< CACHE_HITS/s >15711<
2 Fri May 25 11:16:06 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398179< CACHE_HITS/s >16000<
3 Fri May 25 11:21:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >402080< CACHE_HITS/s >16019<
4 Fri May 25 11:26:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >404682< CACHE_HITS/s >16086<
5 Fri May 25 11:31:07 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >408524< CACHE_HITS/s >16327<
6 Fri May 25 11:36:08 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >409209< CACHE_HITS/s >16390<
7 Fri May 25 11:41:08 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >403647< CACHE_HITS/s >16327<
8 Fri May 25 11:46:09 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397440< CACHE_HITS/s >15894<
9 Fri May 25 11:51:09 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397273< CACHE_HITS/s >15956<
10 Fri May 25 11:56:11 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >396906< CACHE_HITS/s >15904<
 
Fri May 25 11:54:56 UTC 2018 /etc/os-release : PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
Linux debian 4.9.0-6-amd64 #1 SMP Debian 4.9.88-1+deb9u1 (2018-05-07) x86_64 GNU/Linux
1 Fri May 25 11:09:53 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395672< CACHE_HITS/s >15882<
2 Fri May 25 11:14:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >401400< CACHE_HITS/s >16188<
3 Fri May 25 11:19:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >391551< CACHE_HITS/s >15764<
4 Fri May 25 11:24:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >393827< CACHE_HITS/s >15802<
5 Fri May 25 11:29:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >404462< CACHE_HITS/s >16198<
6 Fri May 25 11:34:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >392712< CACHE_HITS/s >15732<
7 Fri May 25 11:39:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >399389< CACHE_HITS/s >16063<
8 Fri May 25 11:44:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >390283< CACHE_HITS/s >15567<
9 Fri May 25 11:49:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >391426< CACHE_HITS/s >15771<
10 Fri May 25 11:54:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >392760< CACHE_HITS/s >15874<
 
Fri May 25 11:53:58 UTC 2018 /etc/os-release : PRETTY_NAME="Oracle Linux Server 7.5"
Linux b5e501 4.1.12-124.15.2.el7uek.x86_64 #2 SMP Tue May 22 11:52:31 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 11:08:54 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395815< CACHE_HITS/s >15759<
2 Fri May 25 11:13:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395346< CACHE_HITS/s >16009<
3 Fri May 25 11:18:55 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395340< CACHE_HITS/s >15898<
4 Fri May 25 11:23:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >402556< CACHE_HITS/s >16200<
5 Fri May 25 11:28:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >397847< CACHE_HITS/s >16039<
6 Fri May 25 11:33:56 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398848< CACHE_HITS/s >16027<
7 Fri May 25 11:38:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398817< CACHE_HITS/s >16089<
8 Fri May 25 11:43:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >398878< CACHE_HITS/s >15961<
9 Fri May 25 11:48:57 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395885< CACHE_HITS/s >15606<
10 Fri May 25 11:53:58 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >395965< CACHE_HITS/s >15893<

I’ve put this data in Excel to show the picture where, basically, all 3 environments perform exactly the same:

CapturePGLIOPS

Physical IO

For the second test, I wanted to test physical IOPS. But not to measure disk performance, which is the same for my 3 services. I want to do physical I/O only to see if there’s a difference in context switches when doing non-blocking I/O – which we do not see in the previous test because they were filesystem cache hits. Then I kept the small scale of 4 sessions with 1GB so that there’s a good chance that it remains in the storage cache. But I reduced the memory in order to have less than 4GB in filesystem cache.

pgio comes with an utility (pgio_reduce_free_memory.sh) to allocate enough huge page to limit the filesystem cache:


$ sudo bash pgio/pgio_reduce_free_memory.sh 2
 
Taking action to reduce free memory down to 2GB available.
total used free shared buff/cache available
Mem: 30886100 124676 30698952 19088 62472 30469900
Swap: 0 0 0
 
Attempting to allocate 13966 huge pages
MemAvailable: 1869148 kB
HugePages_Total: 13966

This is perfect: 13966 huge pages, that’s 27 GB in my 30GB VM that cannot be used by the filesystem, so that my 4x1GB will need I/O calls to the disk.

Here is a vmstat extract to confirm that the filesystem cache is less than 2GB

procs -----------memory---------- ---swap-- -----io---- ---system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 4 0 154416 2344 2015204 0 0 93025 0 32425 24295 2 3 51 44 1
0 4 0 153816 2428 2015848 0 0 94923 21 32400 24414 2 3 51 44 1

runit.sh calls vmstat with a 3 seconds delay so you can see a higher number of block/s and context switches.

Here are the interesting lines from the runit.sh output:

$ for i in allopc@144.21.89.85.out allopc@144.21.89.53.out allopc@144.21.89.26.out ; do grep os-release $i | grep PRETTY | tail -1 ; grep x86 $i | tail -1 ; grep -E "runit.out.*DBNAME" $i | tail -10 | nl ; done
 
Fri May 25 14:58:47 UTC 2018 /etc/os-release : PRETTY_NAME="Ubuntu 16.04.4 LTS"
Linux ubuntu 4.4.0-127-generic #153-Ubuntu SMP Sat May 19 10:58:46 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 14:13:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >1214< CACHE_HITS/s >55<
2 Fri May 25 14:18:42 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >4545< CACHE_HITS/s >195<
3 Fri May 25 14:23:43 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >17053< CACHE_HITS/s >682<
4 Fri May 25 14:28:43 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18865< CACHE_HITS/s >801<
5 Fri May 25 14:33:44 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18933< CACHE_HITS/s >794<
6 Fri May 25 14:38:44 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18832< CACHE_HITS/s >777<
7 Fri May 25 14:43:45 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18826< CACHE_HITS/s >757<
8 Fri May 25 14:48:46 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >19229< CACHE_HITS/s >819<
9 Fri May 25 14:53:46 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19408< CACHE_HITS/s >835<
10 Fri May 25 14:58:47 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19524< CACHE_HITS/s >799<
 
Fri May 25 14:58:41 UTC 2018 /etc/os-release : PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
Linux debian 4.9.0-6-amd64 #1 SMP Debian 4.9.88-1+deb9u1 (2018-05-07) x86_64 GNU/Linux
1 Fri May 25 14:13:35 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >1727< CACHE_HITS/s >82<
2 Fri May 25 14:18:36 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >10743< CACHE_HITS/s >534<
3 Fri May 25 14:23:37 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18660< CACHE_HITS/s >763<
4 Fri May 25 14:28:37 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18496< CACHE_HITS/s >811<
5 Fri May 25 14:33:38 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18537< CACHE_HITS/s >757<
6 Fri May 25 14:38:38 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18647< CACHE_HITS/s >774<
7 Fri May 25 14:43:39 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18838< CACHE_HITS/s >775<
8 Fri May 25 14:48:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18686< CACHE_HITS/s >786<
9 Fri May 25 14:53:40 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18742< CACHE_HITS/s >782<
10 Fri May 25 14:58:41 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 301 seconds. RIOPS >18634< CACHE_HITS/s >793<
 
Fri May 25 14:57:25 UTC 2018 /etc/os-release : PRETTY_NAME="Oracle Linux Server 7.5"
Linux b5e501 4.1.12-124.15.2.el7uek.x86_64 #2 SMP Tue May 22 11:52:31 PDT 2018 x86_64 x86_64 x86_64 GNU/Linux
1 Fri May 25 14:12:20 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >17025< CACHE_HITS/s >721<
2 Fri May 25 14:17:21 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19331< CACHE_HITS/s >792<
3 Fri May 25 14:22:21 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19271< CACHE_HITS/s >770<
4 Fri May 25 14:27:22 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19324< CACHE_HITS/s >802<
5 Fri May 25 14:32:22 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18943< CACHE_HITS/s >802<
6 Fri May 25 14:37:23 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19202< CACHE_HITS/s >818<
7 Fri May 25 14:42:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18999< CACHE_HITS/s >803<
8 Fri May 25 14:47:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19459< CACHE_HITS/s >823<
9 Fri May 25 14:52:24 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >19138< CACHE_HITS/s >836<
10 Fri May 25 14:57:25 UTC 2018 runit.out : DBNAME: pgio. 4 schemas, 1 threads(each). Run time: 300 seconds. RIOPS >18958< CACHE_HITS/s >781<

And I’ve graphed them:
CapturePGPIOPS

As I hit the storage here, I needed a few runs to warm up the storage cache and get the I/O I wanted: low latency rfom storage cache, but involving context switches on the server. And basically, all 3 alternatives (Ubuntu with Linux 4.4, Debian with Linux 4.9 and OEL with the unbreakable kernel 4.1) behave the same. From these tests, I can say that the performance is not the major criteria to choose one of the PaaS alternatives. Just choose the distribution you like.

About pgio, it is great to get performance measures that are reliable and focused on what I want to test. I had no problems to run it on all 3 platforms. Just needed to apt-get / yum to install systat and bc which are not there by default.

 

Cet article Which Bitnami service to choose in the Oracle Cloud Infrastructure? est apparu en premier sur Blog dbi services.

OpenSSH is now available on Windows 10

Fri, 2018-05-25 06:26

A couple of days ago, the Windows 10 april 2018 update was installed on my laptop. And what, you may say? Well, surprisingly this update provides some interesting “hidden” features and one of them concerns OpenSSH that is now available on-demand in Windows 10 (and likely available soon on Windows Server).

blog 134 - 0 - openSSH - Win10

This a obviously a good news because so far, I used either putty or directly a bash environment from my Windows 10 laptop available since the Anniversary Update on 2016 august 2. I know that some of my colleagues use Cygwin as well. An quick example of using the new bash environment from my Win10 Pro laptop:

C:\Users\dab>bash
Performing one-time upgrade of the Windows Subsystem for Linux file system for this distribution...

mikedavem@DBI-LT-DAB:/mnt/c/Users/dab$ cat /proc/version
Linux version 4.4.0-17134-Microsoft (Microsoft@Microsoft.com) (gcc version 5.4.0 (GCC) ) #48-Microsoft Fri Apr 27 18:06:00 PST 2018

mikedavem@DBI-LT-DAB:/mnt/c/Users/dab$ ssh
usage: ssh [-1246AaCfGgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]
           [-D [bind_address:]port] [-E log_file] [-e escape_char]
           [-F configfile] [-I pkcs11] [-i identity_file] [-L address]
           [-l login_name] [-m mac_spec] [-O ctl_cmd] [-o option] [-p port]
           [-Q query_option] [-R address] [-S ctl_path] [-W host:port]
           [-w local_tun[:remote_tun]] [user@]hostname [command]

 

After applying the corresponding update, OpenSSH client is already installed and available to use.

[dab@DBI-LT-DAB:#]> Get-WindowsCapability -Online | ? Name -like 'OpenSSH*'


Name  : OpenSSH.Client~~~~0.0.1.0
State : Installed

Name  : OpenSSH.Server~~~~0.0.1.0
State : NotPresent

 

If you want to also install the server you just have to go through the Add-WindowsCapability cmdlet as follows:

[dab@DBI-LT-DAB:#]> Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0


Path          :
Online        : True
RestartNeeded : False
[dab@DBI-LT-DAB:#]> Get-WindowsCapability -Online | ? Name -like 'OpenSSH*'


Name  : OpenSSH.Client~~~~0.0.1.0
State : Installed

Name  : OpenSSH.Server~~~~0.0.1.0
State : Installed

 

From now on, I may use directly a ssh command from both my PowerShell or my command line environment as follows:

C:\
[dab@DBI-LT-DAB:#]> ssh
usage: ssh [-46AaCfGgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]
           [-D [bind_address:]port] [-E log_file] [-e escape_char]
           [-F configfile] [-I pkcs11] [-i identity_file]
           [-J [user@]host[:port]] [-L address] [-l login_name] [-m mac_spec]
           [-O ctl_cmd] [-o option] [-p port] [-Q query_option] [-R address]
           [-S ctl_path] [-W host:port] [-w local_tun[:remote_tun]]
           destination [command]

 

We will also be able to access a Linux server from either Password-based or Key-based authentication. Let’s try with the first one (Password-based authentication) against my Linux docker private registry:

[dab@DBI-LT-DAB:#]> ssh dab@xxx.xxx.xxx.xxx
The authenticity of host 'xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx)' can't be established.
ECDSA key fingerprint is SHA256:7HwUjHowFNEJ3ILErsmBmgr8sqxossLV+fFt71YsBtA.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'xxx.xxx.xxx.xxx' (ECDSA) to the list of known hosts.
dab@xxx.xxx.xxx.xxx's password:

 

Not a big suprise here! It works as expected (assuming your SSH server is configured to accept authentication with password)! Let’s try now the second method (Key-based authentication). In fact, I already have an .ssh folder from a previous request to connect to our GitLab environment. For the demo, let’s use the same public/private key pairs.

C:\Users\dab
[dab@DBI-LT-DAB:#]> dir .ssh


    Directory: C:\Users\dab\.ssh


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       09.05.2018     11:25           3243 id_rsa
-a----       09.05.2018     11:25            757 id_rsa.pub
-a----       25.05.2018     10:24            380 known_hosts

 

The next step will consist in copying my public key (id_rsa.pub) to the remote Linux server folder .ssh as authorized_keys file.

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> scp .\id_rsa.pub dab@xxx.xxx.xxx.xxx:/home/dab/.ssh/authorized_keys
id_rsa.pub

 

To avoid retyping the secret phrase for each connection, let’s start the ssh-agent service on my Windows 10 machine.

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> Start-Service -Name ssh-agent
C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> Get-Service ssh-agent

Status   Name               DisplayName
------   ----               -----------
Running  ssh-agent          OpenSSH Authentication Agent

 

Then I just have to add the private key to this agent …

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> ssh-add.exe id_rsa
Identity added: id_rsa (id_rsa)

 

… and finally to try a connection to my Linux Server as follows:

C:\Users\dab\.ssh
[dab@DBI-LT-DAB:#]> ssh dab@xxx.xxx.xxx.xxx
Last login: Fri May 25 09:43:16 2018 from gateway

 

It works like a charm! I’m now connecting to my Linux server as dab user. I can get a picture of my docker containers, Note the bash prompt has changed here (server name) even it is pretty similar to my PowerShell prompt. Indeed, I customized my PowerShell profile to be similar to a bash shell in apparence and in some behaviors as well :)

[dab@localhost ~]$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
a60f3412b864        registry:2          "/entrypoint.sh /e..."   9 months ago        Up 37 minutes       0.0.0.0:5000->5000/tcp   registry

 

See you!

 

 

 

 

 

Cet article OpenSSH is now available on Windows 10 est apparu en premier sur Blog dbi services.

Windocks – a different way to use SQL Server on Docker

Thu, 2018-05-24 13:14

I spent some times to write blog posts about SQL Server on Docker since few months and you likely noticed it concerned mainly SQL Server on Linux. But what about SQL Server on Docker and Windows? If you take a look at the Docker images provided by Microsoft, only 2017 version is available and it is only intended for development and testing use. Versions prior 2017 seem to not be supported so far (but I don’t have in mind the Microsoft plan on this topic) and if you want to use containers with older SQL Server versions you have to start from scratch by yourself. This is not a hard task but I have in mind a discussion with one of my customers about the maintenance of SQL Server images prior 2017 and he told me he didn’t allocate resources to create and maintain such images by himself.

In addition, I recently presented at different events including our dbi services internal event  for customers the internal project we are managing about our DMK maintenance module tool for SQL Server. To cut the story short, this tool aims to provide smart maintenance capabilities for customer databases including backups, index, statistics etc …

Over the time, the code grew up and we had to support different versions of SQL Server from 2008 to SQL Server 2017. In the same time, the number of scenarios we have to manage and to test regarding the new features shipped with service packs and cumulative updates as well increased drastically over the time. So we decided to move on docker containers from different obvious reasons:

  • Containers are easy to provision for unit testing
  • Lower footprint on our local machine
  • We can share easily SQL images between members of our “development” team in a private registry
  • We will able to integer containers in a potential “future” CI pipeline

In our context, each developer has a local docker engine installed on a Windows 10 Pro laptop but for medium/large companies, internal rules may prevent installing such product on each individual developer laptop. Therefore, they prefer likely to provide a dedicated environment for developers that meet the internal rules and to keep the focus on flexibility.

In such shared infrastructure a main concern will be probably disk space issues because of the potential testing database(s) size and the number of containers provisioned at the same time. Let’s say that several developers would like to provision one SQL Server instance each with a testing database attached on it and that comes from the production through a sanitized database pipeline. The total size of this database is enough big to warn about a potential disk space issue because each instanced container will include its own image size (roughly 10GB with SQL Server containers on Windows) + the size of the testing database – let’s say more than 50 GBs and likely more for usual cases.

If you’re in one of the aforementioned scenarios, Windocks may be a good alternative scenario.

During this month I had the opportunity to test the product in the context of our internal project and after some experimentation stuff I admit I was agreeably surprised by some built-in capabilities as:

  • Windows authentication mode supported
  • SQL Server database cloning capabilities
  • The ability to use both Docker CLI and Web UI (even if I’m in favor of Docker CLI)
  • The ability to refresh a cloned database image from a differential backup

Before to dig further into the above features let’s just take few seconds to see the Windocks architecture design

blog 133 - 1 - windocks - architecture

We may notice some differences here. With a traditional Docker architecture, containers run on shared operating system kernel (either Windows or Linux) whereas Windocks is a strictly application construct-oriented solution and requires installing a SQL Server instance on the host as base instance for SQL Server containers. This is a fundamental change that provides some interesting advantages:

  • We don’t have to rebuild the containers to get OS & Framework patches through base image updates because containers rely only on the SQL Server base instance
  • We don’t break compatibility with some Windows storage functionalities as VSS and SQL Writer as well if you rely on them with third-party backup tools
  • We benefit from the underlying security and configuration of the SQL Server based instance meaning we may use Windows authentication for instance

Let’s talk now about one interesting feature shipped with Windocks that is cloned databases. Here a picture (from Windocks) that shows a high-level workflow of using SQL Server containers with cloned databases.

blog 133 - 2 - windocks - cloned db architecture

The starting point is a full database backup or a snapshot and Windocks will generate the corresponding VHD parent image of the backup media. Then each SQL Server container generated will use a writable clone of this parent image reducing drastically the disk footprint of each container (at least when the SQL Server container is generated). This is a common workflow that customers may implement for unit testing or for CI/CD pipeline to refresh development environments. The production database may be big in size and in this context the interest of using cloned databases becomes obvious. Another way provided would be to rely on mounted snapshot-based volumes from the storage provider but at this stage I didn’t test it. Maybe a next time!

To give a better picture of what it is possible to do with cloned databases, let me show you one way to implement it in the context of our DMK maintenance tool project. The development workflow of the development project is as follows:

blog 133 - 3 - windocks - dev workflow

We use SSDT and GitLab to manage our DMK maintenance tool sources and we perform unit testing by provisioning one or several SQL Server containers regarding the target version we want to validate at this moment. Furthermore we developed testing scenarios based on tSQLt framework we run after provisioning the SQL Server containers. With such architecture, we initially have to maintain images of different SQL Server versions and each time we want to create a container we have to attach one copy of the customized AdventureWorks_dbi database. Let’s say we want to work with 4 containers (SQL Server 2016) at time. We must to ensure we have sufficient disk space for 4 copies of this database (5 x 4 = 20GB) + space required for each container (10GB x 4 = 40GB) = 60GB. The dbi_tools database is intended to stay very small (512MB up to 1GB) comparing to other testing components, this is way I didn’t include it to the math.

Let’s now apply Windocks with the above context and the new architecture becomes as follows:

blog 133 - 4 - windocks - dev workflow

In this second scenario, we may include both the AdventureWorks_dbi and dbi_tools databases (including tSQLt framework objects) in a VHD parent image. For the demo, I used a SQL Server 2016 instance installed on the host that will be controlled by Windocks service during the container generation.

As I said previously we may rely on the configuration and the security of the base instance. Thus, I configured my security based on both Windows Authentication (dbi-services\clustadmin domain account) for SQL Server sysadmins and SQL authentication for development purpose (windock user).

In terms of configuration, tSQLt framework requires enabling CLR on the server level, so I changed the configuration directly on the SQL based instance to allow all my containers to inherit this configuration change.

SELECT 
	[name],
	principal_id,
	[type_desc]
FROM 
	sys.server_principals 
WHERE 
	[type] IN ('U', 'S')
	AND [name] NOT LIKE '##MS_%##'
	AND [name] NOT LIKE 'NT SERVICE%'

 

blog 133 - 41- windocks - base instance security

Here the content of my docker file.

FROM mssql-2016
SETUPCLONING FULL AdventureWorks_dbi \\Win2012sql16ls\windock\AdventureWorks_dbi_2008.bak
SETUPCLONING FULL dbi_tools \\Win2012sql16ls\windock\dbi_tools.bak

 

You may notice some new commands here:

  • FROM mssql-2016 indicates we will use the SQL Server 2016 instance as base image.
  • SETUPCLONING FULL indicates to generate the VHD parent image that we will include both the AdventureWorks_dbi and dbi_tools databases in a remote network path

Go ahead and let’s generate the corresponding SQL Server docker cloned image with the special -t flag

docker build -t 2016withdmk C:\DMK\WithClone\BasedImage\

 

blog 133 - 5- windocks - cloned database image

The process may take some times to generate the VHD parent image depending on the different database sizes and the location (local fast disk, network share bandwidth etc …)

blog 133 - 6- windocks - cloned db size

As expected, because the VHD parent image is a full byte copy of the data, the size is basically the sum of both AdventureWorks_dbi and dbi_tools database sizes.

Compared to a traditional approach, the provisioning of the associated containers will be faster irrespective to database size. Let’s create 2 containers from the image generated previously with the following docker commands:

PS C:\DMK\WithClone> docker run --name sql20161 -d 2016withdmk
PS C:\DMK\WithClone> docker run --name sql20162 -d 2016withdmk

 

Note that by default, Windocks will choose a random port between a configured range in the node.conf (START_PORT/ PORTS_PER_USER) unless you override the default behavior using the -p parameter.

blog 133 - 7- windocks - create containers

Let’s get a picture of the existing containers. My 2 containers have been generated correctly from the 2016withdmk base image.

PS C:\DMK\WithClone> docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS
89344d99758b        2016withdmk         ""                  5 minutes ago       Started             1433/
4dfd1a198626        2016withdmk         ""                  4 minutes ago       Started             1434/

 

Let’s take another look at the storage side:

blog 133 - 8 - windocks - cloned databases diff

The interesting point here is the two differential disks created during the creation of my 2 previous containers are very small size (~70MB per container).

Let’s try to connect from SSMS to the new fresh containers:

blog 133 - 9 - windocks - ssms connection

It works and did you notice I was connected with my domain account? :)

Last topic I wanted to share with you is the Windocks capability to update a base image from differential backups. In a real context, you may need to refresh frequently your environment with recent data for developers and regarding your full backup size it might be advantageous to deal with differential backups.

Let’s consider this process with my internal project environment. During the development process we already had to scale the database schema of our testing database with the features shipped with new versions, service packs or cumulative update over the time. Let’s say we have to add a new dbo.bigTransactionhistory_cci table to test columnstore index scenarios. We first add the concerned table. Then we will perform a differential backup and finally we will update the 2016withDMK base image with it. Obviously in the context of my demo, the database size is likely not big enough to take full advantage of this feature but I trust you to draw a comparison with a more realistic scenario.

The image I want to update is named 2016withdmk. Note the mssql-xxx images that are in fact images from SQL Server base instances installed on my host server.

PS C:\DMK\WithClone> docker images
REPOSITORY            TAG                 IMAGE ID            CREATED                  VIRTUAL SIZE
2016withdmk           none                b3a249ba-2cf        Less than a second ago   0 B
agitated_heisenberg   none                bbd0ce26-4bb        Less than a second ago   0 B
dotnet-4.5            none                dotnet-4.5          3 years ago              0 B
windows               none                windows             3 years ago              0 B
mssql-2008r2          none                mssql-2008r2        3 years ago              0 B
mssql-2016            none                mssql-2016          3 years ago              0 B
mssql-2014            none                mssql-2014          3 years ago              0 B

 

My new docker file content to update the 2016withdmk base image is as follows.

FROM 2016withdmk

SETUPCLONING DIFF AdventureWorks_dbi \\Win2012sql16ls\windock\AdventureWorks_dbi_2008.DIFF
SETUPCLONING DIFF dbi_tools \\Win2012sql16ls\windock\dbi_tools.DIFF

 

I used the differential backup of the AdventureWorks_dbi database with the SETUPCLONNING DIFF command.

Let’s start updating the 2016withDMK base image with the following docker command (I tagged my new image with 1.2 suffix):

docker build -t 2016withdmk1.2 C:\DMK\WithClone\DiffImage\

 

blog 133 - 10 - windocks - cloned database diff backup

Although using a differential backup may allow for substantial productivity keep in mind that updating a VHD parent image will require creating an additional VHD parent image that will become another full byte copy of the environment as shown below:

blog 133 - 11- windocks - cloned db size diff

After running the same docker run command exposed before, let’s get a picture of the running on my server. The container id 789ce49562d0 is the new fresh container with updated data (2016withdmk1.2 image).

PS C:\DMK\WithClone> docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS
89344d99758b        2016withdmk         ""                  About an hour ago   Started             1433/
4dfd1a198626        2016withdmk         ""                  About an hour ago   Started             1434/
789ce49562d0        2016withdmk1.2      ""                  2 minutes ago       Started             1436/

 

I may retrieve the new dbo.bigTransactionHistory_cci after connecting to the concerned SQL Server instance. I’m now able to update and to execute tSQLt scenarios to test columnstore index maintenance scenarios!

blog 133 - 12- windocks - ssms connection 2

 

In this blog post we’ve just surfaced some capabilities and possibilities provided by Windocks. There are other interesting features as encrypted password in the docker file (as we may use with Docker Swarm for instance), the support of TDE databases or lastly the ability to manage SQL Server on Linux. I will probably blog about it in the near future. Stay tuned!

 

 

 

 

Cet article Windocks – a different way to use SQL Server on Docker est apparu en premier sur Blog dbi services.

Postgres, the fsync() issue, and ‘pgio’ (the SLOB method for PostgreSQL)

Thu, 2018-05-24 08:05

That’s a long blog post title, which is actually just a good pretext to play with Kevin Closson SLOB method for PostgreSQL: pgio
I use the beta version of pgio here. If you want to read more about it, you can start on https://kevinclosson.net/2018/05/22/sneak-preview-of-pgio-the-slob-method-for-postgressql-part-i-the-beta-pgio-readme-file/. If you are used to the SLOB for Oracle (https://kevinclosson.net/slob/) you will quickly understand the ‘why’ and ‘how’ of pgio.

PostgreSQL’s fsync() surprise

You may have read about the fsync() issue. Postgres, from the beginning, relies a lot on the filesystem buffering to optimize I/O. So they write() to the data files but fsync() only at checkpoints. This is ok when everything goes well because the writes since the last checkpoints are protected by the Write Ahead Logging, where fsync() occurs for each writes at commit (if you didn’t change the default parameters for WAL). But when a problem occurs, such as power outage, some writes may be lost, or partially lost, and that’s not easy to detect at checkpoint time with fsync().

So, basically, there’s a risk of corruption and there are no easy ways to detect it.

You can read the details from https://lwn.net/Articles/752063/ and that’s not the subject of this post.

Most of the other databases are opening files with O_DSYNC, which means that the write() call will detect the error immediately. And the major ones are doing direct I/O anyway because they have their own buffer cache and do not need the performance overhead and corruption risk of double buffering.

Why is this so hard to fix?

So why is it so hard to do the same with Postgres? Just because it was not initially designed to optimize I/O and postgres relied heavily on the OS filesystem for that. The database systems which sync at each write, and which can do direct I/O, have implemented many optimizations to reduce the overhead of a disk latency at each write. They have their own buffer cache, with a background database writer which re-orders the writes in the optimal way. And they have multiblock writes for large contiguous writes which bypass the buffer cache.

However, you may have a storage system where write latency is minimal, and you may have an application where the overhead here is not a big problem. This means that you should measure it in order to balance between performance and prevention of corruption. And this is where the SLOB method is awesome: reliable and predictable metrics to measure IOPS.

pgio to the rescue

This is my first trial of pgio, in beta version. It cannot be easier. I’ve just un-tar-ed it:

tar -xvf pgio-0.9.tar
cd pgio

I’ve setup the pgio.conf with 4 schemas and 2 threads per schema:

UPDATE_PCT=10
RUN_TIME=300
NUM_SCHEMAS=4
NUM_THREADS=2
WORK_UNIT=255
UPDATE_WORK_UNIT=8
SCALE=200M
DBNAME=pgio
CONNECT_STRING="pgio"
CREATE_BASE_TABLE=TRUE

Because I want to test writes, I’ve set the UPDATE_PCT so that 10% of calls will do an UPDATE. And I kept the default work unit to read 255 blocks and, for those 10% updates, update 8 blocks only. I’ll run that with 2 threads per schemas, which means 8 concurrent sessions. And they will run for 300 seconds.

In this test I didn’t want to set different values. I just want to see what happens in IOPS for a common workload of lot of reads and small changes. the scale is 200M here. My workload sessions will find their buffers in memory.

On each test, I’ve created the pgio database:

create tablespace pgio location '/u01/pgdata';
CREATE TABLESPACE
create database pgio tablespace pgio;
CREATE DATABASE

Then run the setup.sh to load data in those schemas:

Job info: Loading 200M scale into 4 schemas as per pgio.conf->NUM_SCHEMAS.
Batching info: Loading 2 schemas per batch as per pgio.conf->NUM_THREADS.
Base table loading time: 15 seconds.
Waiting for batch. Global schema count: 2. Elapsed: 0 seconds.
Waiting for batch. Global schema count: 3. Elapsed: 103 seconds.
Waiting for batch. Global schema count: 4. Elapsed: 177 seconds.
Waiting for batch. Global schema count: 4. Elapsed: 249 seconds.
 
Group data loading phase complete. Elapsed: 249 seconds.
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+--------+-------------
public | pgio1 | table | postgres | 200 MB |
public | pgio2 | table | postgres | 200 MB |
public | pgio3 | table | postgres | 200 MB |
public | pgio4 | table | postgres | 200 MB |
public | pgio_base | table | postgres | 29 MB |
(5 rows)

And then I’m ready to run the runit.sh

ext4 mount option

My tablespace is on an ext4 filesystem:

-bash-4.2$ df -HT /u01/pgdata
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdb ext4 32G 1.2G 29G 4% /u01

I’ll run the same workload, several times, with changing only one mount option:

async All I/O to the filesystem should be done asynchronously. (See also the sync option.)
sync All I/O to the filesystem should be done synchronously. In case of media with limited number of write cycles (e.g. some flash drives) "sync" may cause life-cycle shortening.

Which means that some runs will run with /u01 mounted as:

/dev/sdb on /u01 type ext4 (rw,nosuid,nodev,relatime,sync,seclabel,data=ordered)

and some others will run with the default (async):

/dev/sdb on /u01 type ext4 (rw,nosuid,nodev,relatime,seclabel,data=ordered)

I did multiple runs and checked that the result is consistent among them. I’ll show only one result for each configuration.

Run it with async

Here is the output of one ‘runit.sh’ when /u01 was in async:

Date: Thu May 24 10:56:57 CEST 2018
Database connect string: "pgio".
Shared buffers: 128MB.
Testing 4 schemas with 2 thread(s) accessing 200M (25600 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 4 schema(s) will be accessed by 2 thread(s) each.
pg_stat_database stats:
datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE: pgio | 252209 | 118112 | 110420 | 6788 | 18
AFTER: pgio | 25189171 | 136972696 | 159128092 | 147250205 | 573216
DBNAME: pgio. 4 schemas, 2 threads(each). Run time: 300 seconds. RIOPS >456181< CACHE_HITS/s >83123<

This shows that, within those 5 minutes, I’ve fetched 147243417 tuples and updated 573198 ones.

pgio takes snapshots of iostat, vmstat and mpstat. Here is a sample after 1 minute of run where we show that all CPU are busy in user or kernel, but not waiting on I/O latency:

10:57:51 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
10:57:54 AM all 52.82 0.00 42.22 0.09 0.00 1.11 0.00 0.00 0.00 3.76
10:57:54 AM 0 54.11 0.00 40.75 0.00 0.00 1.37 0.00 0.00 0.00 3.77
10:57:54 AM 1 54.42 0.00 40.14 0.34 0.00 1.02 0.00 0.00 0.00 4.08
10:57:54 AM 2 51.19 0.00 43.34 0.34 0.00 0.68 0.00 0.00 0.00 4.44
10:57:54 AM 3 51.02 0.00 44.22 0.34 0.00 1.36 0.00 0.00 0.00 3.06
10:57:54 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
10:57:57 AM all 53.33 0.00 42.15 0.00 0.00 1.02 0.00 0.00 0.00 3.50
10:57:57 AM 0 53.95 0.00 42.27 0.00 0.00 0.69 0.00 0.00 0.00 3.09
10:57:57 AM 1 52.56 0.00 42.66 0.00 0.00 0.68 0.00 0.00 0.00 4.10
10:57:57 AM 2 54.27 0.00 40.27 0.00 0.00 1.37 0.00 0.00 0.00 4.10
10:57:57 AM 3 52.72 0.00 43.54 0.00 0.00 1.36 0.00 0.00 0.00 2.38
10:57:57 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
10:58:00 AM all 54.10 0.00 41.54 0.09 0.00 0.77 0.00 0.00 0.00 3.50
10:58:00 AM 0 55.14 0.00 39.38 0.34 0.00 1.03 0.00 0.00 0.00 4.11
10:58:00 AM 1 54.95 0.00 40.96 0.00 0.00 0.68 0.00 0.00 0.00 3.41
10:58:00 AM 2 54.11 0.00 41.10 0.00 0.00 0.68 0.00 0.00 0.00 4.11
10:58:00 AM 3 52.05 0.00 44.86 0.00 0.00 0.68 0.00 0.00 0.00 2.40
10:58:00 AM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle

Run it with sync

Here is the output of one ‘runit.sh’ when /u01 was in sync:

Date: Thu May 24 12:18:54 CEST 2018
Database connect string: "pgio".
Shared buffers: 128MB.
Testing 4 schemas with 2 thread(s) accessing 200M (25600 blocks) of each schema.
Running iostat, vmstat and mpstat on current host--in background.
Launching sessions. 4 schema(s) will be accessed by 2 thread(s) each.
pg_stat_database stats:
datname| blks_hit| blks_read|tup_returned|tup_fetched|tup_updated
BEFORE: pgio | 255169 | 119163 | 112734 | 6945 | 18
AFTER: pgio | 15040938 | 74598977 | 87775490 | 86742056 | 337889
DBNAME: pgio. 4 schemas, 2 threads(each). Run time: 300 seconds. RIOPS >248266< CACHE_HITS/s >49285<

This shows that, within those 5 minutes, I’ve fetched 86735111 tuples and updated 337871 ones. So, basically the IOPS have been divided by two here in this example when waiting on each writes to be synced to disk.

pgio takes snapshots of iostat, vmstat and mpstat. Here is a sample after 1 minute of run where we show that all CPU are 30% idle waiting on I/O completion:

12:19:51 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
12:19:54 PM all 29.95 0.00 27.79 10.88 0.00 1.26 0.00 0.00 0.00 30.13
12:19:54 PM 0 30.63 0.00 27.46 11.27 0.00 0.70 0.00 0.00 0.00 29.93
12:19:54 PM 1 30.07 0.00 27.62 12.24 0.00 0.70 0.00 0.00 0.00 29.37
12:19:54 PM 2 30.28 0.00 27.82 10.92 0.00 0.35 0.00 0.00 0.00 30.63
12:19:54 PM 3 28.02 0.00 28.02 8.56 0.39 3.89 0.00 0.00 0.00 31.13
12:19:54 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
12:19:57 PM all 30.10 0.00 27.92 11.24 0.00 1.00 0.00 0.00 0.00 29.74
12:19:57 PM 0 29.29 0.00 28.57 10.71 0.00 0.36 0.00 0.00 0.00 31.07
12:19:57 PM 1 30.88 0.00 28.07 11.93 0.00 0.35 0.00 0.00 0.00 28.77
12:19:57 PM 2 30.31 0.00 27.18 12.54 0.00 0.70 0.00 0.00 0.00 29.27
12:19:57 PM 3 30.43 0.00 27.67 9.88 0.00 2.77 0.00 0.00 0.00 29.25
12:19:57 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle
12:20:00 PM all 29.51 0.00 27.00 10.76 0.00 1.08 0.00 0.00 0.00 31.66
12:20:00 PM 0 29.58 0.00 28.17 10.56 0.00 0.35 0.00 0.00 0.00 31.34
12:20:00 PM 1 29.72 0.00 26.22 12.24 0.00 0.70 0.00 0.00 0.00 31.12
12:20:00 PM 2 29.12 0.00 26.32 10.88 0.00 0.35 0.00 0.00 0.00 33.33
12:20:00 PM 3 29.34 0.00 27.80 8.88 0.00 3.09 0.00 0.00 0.00 30.89
12:20:00 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle

So what?

Currently, Postgres relies on the filesystem to optimize the I/O, but there’s a risk of corruption in case of failure. We can force to wait for I/O completion with the ‘sync’ mount options of the filesystems, or even with some file attributes (chattr -R +S) for ext4 or xfs, but there’s a performance penalty. The important thing is to measure this penalty, and this is where pgio is great: measure the performance penalty with a workload that is customizable (amount of changes, amount of data,…) but also predictable (does not depend on other parameters like an application benchmark). When you know how being in ‘sync’ impacts your system, you can choose. And we can bet that future versions of Postgres will improve and offer ways to stay efficient without compromising the data at first power outage.

 

Cet article Postgres, the fsync() issue, and ‘pgio’ (the SLOB method for PostgreSQL) est apparu en premier sur Blog dbi services.

ADWC – System and session settings (DWCS lockdown profile)

Thu, 2018-05-24 05:04

The Autonomous Data Warehouse Cloud service is a PaaS managed service where we have a PDB and an ADMIN user which has most of the system privileges. For example, we have the privilege to change initialization parameters:
SQL> select * from dba_sys_privs where grantee=user and privilege like 'ALTER S%';
 
GRANTEE PRIVILEGE ADMIN_OPTION COMMON INHERITED
------- --------- ------------ ------ ---------
ADMIN ALTER SESSION YES NO NO
ADMIN ALTER SYSTEM YES NO NO

Still, not everything is allowed for several reasons: ensure that we cannot break the Oracle managed CDB and force us to use only the features allowed in the ‘autonomous’ service. This is limited with a lockdown profile:
SQL> show parameter pdb_lockdown
 
NAME TYPE VALUE
------------ ------ -----
pdb_lockdown string DWCS

DWCS means Data Warehouse Cloud Service which was the name of the Autonomous Data Warehouse Cloud service until Larry Ellison announces this self-driven-no-human trend under the marketing umbrella of ‘autonomous’.

The limitations are all documented but I like to see them by myself and in 18c we have a mean to see the lockdown rules from the PDB itself, through V$LOCKDOWN_RULES.

ALTER SYSTEM

Basically, in this ADWC all ALTER SYSTEM statements are disallowed and then they add the few exceptions for what we are allowed to:

SQL> select * from v$lockdown_rules where rule in ('ALTER SYSTEM') and clause_option is null;
RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS CON_ID
--------- ---- ------ ------------- ------ ----- ------
STATEMENT ALTER SYSTEM DISABLE ALL 73
STATEMENT ALTER SYSTEM SET ENABLE COMMON 73
STATEMENT ALTER SYSTEM KILL SESSION ENABLE ALL 73

You can ignore what is enabled for COMMON users because we have no common user to connect to our PDB. We will see which ALTER SYSTEM SET clauses are allowed. But in addition to those, only the ‘KILL SESSION’ is allowed for ALTER SYSTEM.

Here is the detail about the parameters we can set:

SQL> select * from v$lockdown_rules left outer join (select upper(name) clause_option,display_value,description from v$parameter) using (clause_option) where rule in ('ALTER SYSTEM') and clause_option is not null and status='ENABLE';
 
CLAUSE_OPTION RULE_TYPE RULE CLAUSE STATUS USERS CON_ID DISPLAY_VALUE DESCRIPTION
------------- --------- ---- ------ ------ ----- ------ ------------- -----------
APPROX_FOR_AGGREGATION STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE Replace exact aggregation with approximate aggregation
APPROX_FOR_COUNT_DISTINCT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE Replace count distinct with approx_count_distinct
APPROX_FOR_PERCENTILE STATEMENT ALTER SYSTEM SET ENABLE ALL 73 none Replace percentile_* with approx_percentile
AWR_PDB_AUTOFLUSH_ENABLED STATEMENT ALTER SYSTEM SET ENABLE ALL 73 TRUE Enable/Disable AWR automatic PDB flushing
NLS_LANGUAGE STATEMENT ALTER SYSTEM SET ENABLE ALL 73 AMERICAN NLS language name
NLS_SORT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS linguistic definition name
NLS_TERRITORY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 AMERICA NLS territory name
NLS_CALENDAR STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS calendar system name
NLS_COMP STATEMENT ALTER SYSTEM SET ENABLE ALL 73 BINARY NLS comparison
NLS_CURRENCY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS local currency symbol
NLS_DATE_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 DD-MON-YYYY HH24:MI:ss NLS Oracle date format
NLS_DATE_LANGUAGE STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS date language name
NLS_DUAL_CURRENCY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 Dual currency symbol
NLS_ISO_CURRENCY STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS ISO currency territory name
NLS_LENGTH_SEMANTICS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 BYTE create columns using byte or char semantics by default
NLS_NCHAR_CONV_EXCP STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE NLS raise an exception instead of allowing implicit conversion
NLS_NUMERIC_CHARACTERS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 NLS numeric characters
NLS_TIMESTAMP_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 time stamp format
NLS_TIMESTAMP_TZ_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 timestamp with timezone format
NLS_TIME_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 time format
NLS_TIME_TZ_FORMAT STATEMENT ALTER SYSTEM SET ENABLE ALL 73 time with timezone format
OPTIMIZER_IGNORE_HINTS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 TRUE enables the embedded hints to be ignored
OPTIMIZER_IGNORE_PARALLEL_HINTS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 TRUE enables embedded parallel hints to be ignored
PLSCOPE_SETTINGS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 identifiers:all plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL source code identifier and SQL statement data
PLSQL_CCFLAGS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 PL/SQL ccflags
PLSQL_DEBUG STATEMENT ALTER SYSTEM SET ENABLE ALL 73 FALSE PL/SQL debug
PLSQL_OPTIMIZE_LEVEL STATEMENT ALTER SYSTEM SET ENABLE ALL 73 1 PL/SQL optimize level
PLSQL_WARNINGS STATEMENT ALTER SYSTEM SET ENABLE ALL 73 DISABLE:ALL PL/SQL compiler warnings settings

The APPROX_ ones, disable by default, can be used to transparently use approximations for faster results.
The NLS_ ones can be used to set NLS defaults for our sessions.
OPTIMIZER_IGNORE_ are new in 18c and are set by default here to ignore embedded hints. However, we can set then to false.
PLSQL_ are the defaults for sessions and I don’t understand why warnings are not enabled by default. Fortunately, we are able to change that at PDB level.

There are also some rules to disable some ALTER SYSTEM SET. They are there for the common users only (which have ALTER SYSTEM SET enabled) but they are interesting to see what Oracle choose to set in the ADWC service which cannot be changed in the PDB even by their common users:

SQL> select * from v$lockdown_rules left outer join (select upper(name) clause_option,display_value,description from v$parameter) using (clause_option) where rule in ('ALTER SYSTEM') and clause_option is not null and status='DISABLE';
CLAUSE_OPTION RULE_TYPE RULE CLAUSE STATUS USERS CON_ID DISPLAY_VALUE DESCRIPTION
------------- --------- ---- ------ ------ ----- ------ ------------- -----------
DB_FILES STATEMENT ALTER SYSTEM SET DISABLE ALL 73 25 max allowable # db files
"_PDB_INHERIT_CFD" STATEMENT ALTER SYSTEM SET DISABLE ALL 73
"_PDB_MAX_AUDIT_SIZE" STATEMENT ALTER SYSTEM SET DISABLE ALL 73
"_PDB_MAX_DIAG_SIZE" STATEMENT ALTER SYSTEM SET DISABLE ALL 73
MAX_IDLE_TIME STATEMENT ALTER SYSTEM SET DISABLE ALL 73 60 maximum session idle time in minutes
PARALLEL_DEGREE_POLICY STATEMENT ALTER SYSTEM SET DISABLE ALL 73 AUTO policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE)
_PARALLEL_CLUSTER_CACHE_POLICY STATEMENT ALTER SYSTEM SET DISABLE ALL 73 ADAPTIVE policy used for parallel execution on cluster(ADAPTIVE/CACHED)
_ENABLE_PARALLEL_DML STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enables or disables parallel dml
RESULT_CACHE_MODE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FORCE result cache operator usage mode
RESULT_CACHE_MAX_RESULT STATEMENT ALTER SYSTEM SET DISABLE ALL 73 1 maximum result size as percent of cache size
RESOURCE_MANAGER_PLAN STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FORCE:DWCS_PLAN resource mgr top plan
_CELL_OFFLOAD_VECTOR_GROUPBY STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FALSE enable SQL processing offload of vector group by
PARALLEL_MIN_DEGREE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 CPU controls the minimum DOP computed by Auto DOP
_MAX_IO_SIZE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 33554432 Maximum I/O size in bytes for sequential file accesses
_LDR_IO_SIZE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 33554432 size of write IOs used during a load operation
_LDR_IO_SIZE2 STATEMENT ALTER SYSTEM SET DISABLE ALL 73 33554432 size of write IOs used during a load operation of EHCC with HWMB
_OPTIMIZER_GATHER_STATS_ON_LOAD_ALL STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enable/disable online statistics gathering for nonempty segments
_OPTIMIZER_GATHER_STATS_ON_LOAD_HIST STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enable/disable online histogram gathering for loads
_DATAPUMP_GATHER_STATS_ON_LOAD STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE Gather table statistics during Data Pump load rather thanimporting statistics from the dump file. This should be set to TRUE in the lockdown profile in a DWCS environment.
_OPTIMIZER_ANSWERING_QUERY_USING_STATS STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE enable statistics-based query transformation
_PX_XTGRANULE_SIZE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 128000 default size of a external table granule (in KB)
_OPTIMIZER_ALLOW_ALL_ACCESS_PATHS STATEMENT ALTER SYSTEM SET DISABLE ALL 73 FALSE allow all access paths
_DATAPUMP_INHERIT_SVCNAME STATEMENT ALTER SYSTEM SET DISABLE ALL 73 TRUE Inherit and propagate service name throughout job
_DEFAULT_PCT_FREE STATEMENT ALTER SYSTEM SET DISABLE ALL 73 1 Default value of PCT_FREE enforced by DWCS lockdown

So, among the interesting ones, Result Cache is forced for all results (RESULT_CACHE_MODE=FORCE), Parallel DML is enabled for all sessions (but we will see that we can disable it at session level), PCTFREE will always be 1 (_DEFAULT_PCT_FREE=1), statistics are gathered during load (this is a 18c feature). And we cannot change that.

There are only few additional ALTER SYSTEM SET which are allowed at session level:

SQL> select * from v$lockdown_rules where rule in ('ALTER SESSION') and clause is not null and clause_option is not null
and (clause_option,status,users) not in (select clause_option,status,users from v$lockdown_rules where rule in ('ALTER SYSTEM') and clause is not null and clause_option is not null)
;
RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS CON_ID
--------- ---- ------ ------------- ------ ----- ------
STATEMENT ALTER SESSION SET CONTAINER ENABLE ALL 73
STATEMENT ALTER SESSION SET CURRENT_SCHEMA ENABLE ALL 73
STATEMENT ALTER SESSION SET EDITION ENABLE ALL 73
STATEMENT ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES ENABLE ALL 73
STATEMENT ALTER SESSION SET DEFAULT_COLLATION ENABLE ALL 73
STATEMENT ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE ENABLE ALL 73
STATEMENT ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL ENABLE ALL 73
STATEMENT ALTER SESSION SET TIME_ZONE ENABLE ALL 73

Besides the parameters here are what we can do with ALTER SESSION:

SQL> select * from v$lockdown_rules where rule='ALTER SESSION' and clause_option is null;
 
RULE_TYPE RULE CLAUSE CLAUSE_OPTION STATUS USERS CON_ID
--------- ---- ------ ------------- ------ ----- ------
STATEMENT ALTER SESSION DISABLE ALL 73
STATEMENT ALTER SESSION SET ENABLE COMMON 73
STATEMENT ALTER SESSION ADVISE COMMIT ENABLE ALL 73
STATEMENT ALTER SESSION CLOSE DATABASE LINK ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE COMMIT IN PROCEDURE ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE PARALLEL DDL ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE PARALLEL DML ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE PARALLEL QUERY ENABLE ALL 73
STATEMENT ALTER SESSION DISABLE RESUMABLE ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE COMMIT IN PROCEDURE ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE PARALLEL DDL ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE PARALLEL DML ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE PARALLEL QUERY ENABLE ALL 73
STATEMENT ALTER SESSION ENABLE RESUMABLE ENABLE ALL 73
STATEMENT ALTER SESSION FORCE PARALLEL DDL ENABLE ALL 73
STATEMENT ALTER SESSION FORCE PARALLEL DML ENABLE ALL 73
STATEMENT ALTER SESSION FORCE PARALLEL QUERY ENABLE ALL 73

I’ll show other rules (other than ALTER SYSTEM and ALTER SESSION statements) in a future post. Lockdown profiles is a great feature because they have very fine granularity and makes it easy to document what is allowed or not. Oracle introduced them for their own usage in the public cloud. You can use the same on-premises for your private cloud. This requires multitenant architecture, but the option is not mandatory.

 

Cet article ADWC – System and session settings (DWCS lockdown profile) est apparu en premier sur Blog dbi services.

Adding a Documentum extension into python

Tue, 2018-05-22 08:15

Many years ago, out of frustration by the poorness of scripting tools in Documentum, I realized a Documentum binding for python using the distutils and I remember how easy and straightforward it had been, even for someone not really into these things on a daily basis. Recently, I wanted to reuse that work but couldn’t find the source files, not that they were many, but I did not want to do it over again. Finally, I had to give up and admit it: I lost them for good somewhere among the tens of machines, O/Ses, disks and USB drives I played with during that period. Those were sunny, cloudless times (got it ?). Anyway, I decided to do it again but, as I hate repeating work, by using another method of extending python: this time I went for ctypes (cf. documentation here: https://docs.python.org/3/library/ctypes.html).
One of the advantages of ctypes over distutils is that no compilation is needed, even when changing the version of python or of the O/S because the interface is python, and it gets interpreted at run-time. Thanks to ctypes, there isn’t much to do and interfacing to a run-time library such as libdmcl40.so is a no brainer.
There was however a big change in the evolution from python 2 to python 3: strings no longer are arrays of bytes but are now a distinct, uncompatible type storing unicode characters. Transformation functions are of course provided to go from one type to the other and back. For low-level work such as interfacing a C/C++ shared library, the distinction is important because in C, strings are accessed as “char *”, i.e. array of bytes and one cannot just pass around python text strings with 1 to 4 bytes per character. Fortunately, there was no need to produce two versions of the interface because python 2.7, the last version of python 2, understands the type conversion functions used here:

string.encode('ascii', 'ignore') to convert python 3's strings to python 2's arrays of bytes compatible with C/C++ char*
b.decode() to convert python 2's arrays of bytes to python3 unicode strings

(see https://docs.python.org/3/howto/unicode.html). Thus, it was sufficient to just write one version for python 3 and it would also be compatible with python 2.
I started my work in a venerable 32-bit Ubuntu 14.04 with python 2.7. The Documentum library I used there was the 32-bit libdmcl40.so included in the Content server v5.3 binaries. Later, I installed python 3 on the same VM and made the necessary changes to the interface so it would be accepted by this interpreter. Later on, I copied the interface file to another VM running a 64-bit Ubuntu 16.04 and v7.3 Documentum ContentServer binaries but I couldn’t make it work with the included 64-bit libdmcl40.so. I kept receiving SIGSEGV and core dumps from both python2 and python3. A case for a gdb session sometime… Fortunately, with the java-enabled libdmcl.so library, both pythons worked well, albeit with a perceptible delay at startup because of all the jars to load, a small price to pay though.

The interface

The C functions libdmcl*.so exports are the following:

int dmAPIInit();
int dmAPIDeInit();
int dmAPIExec(const char *str);
char* dmAPIGet(const char *str);
int dmAPISet(const char *str, const char *arg);
char* dmAPIDesc(const char *str, int *code, int *type, int *sess);
char* dmAPIEval(const char *str, const char *arg);
char* dmGetPassword(const char *str);
int dmGetVersion( int *, int * );

However, the last 2 functions don’t seem to really be available from the library. Also, dmAPIDesc() (not to be confused with the describe server method) and dmAPIEval() are not documented in the API reference manual. Therefore, I’ve only considered the first 5 functions, the ones that really do the job as the building blocks of any Documentum script.
From within python, those functions are accessed through the wrapper functions below:

def dmInit()
def dmAPIDeInit()
def dmAPIGet(s)
def dmAPISet(s, value)
def dmAPIExec(stmt)

Those take care of the string conversions operations so you don’t have to; they are the only ones who directly talk to the Documentum API and the only ones to use to do API stuff. Generally, they return True or a string if successful, and False or None if not.
Every Documentum client should start with a call do dmInit() in order to load and initialize the libdmcl*.so library’s internal state. To guarantee that, the interface does it itself at load time. As this function is idempotent, further calls at script start up don’t have any effect. On the other hand, dmAPIDeInit() is not really necessary, just exiting the script will do.
Here, I named the proxy function dmInit() instead of dmAPIInit() for a reason. This function does not just invoke the library’s dmAPIInit() but also initializes the python interface and its usage of ctypes: it loads the shared library and describes the types of the API functions’ arguments (argtypes) and return values (restype). Here is a snippet of its the main part:

dmlib = 'libdmcl40.so'
...
dm = ctypes.cdll.LoadLibrary(dmlib); dm.restype = ctypes.c_char_p
...
dm.dmAPIInit.restype = ctypes.c_int;
dm.dmAPIDeInit.restype = ctypes.c_int;
dm.dmAPIGet.restype = ctypes.c_char_p; dm.dmAPIGet.argtypes = [ctypes.c_char_p] dm.dmAPISet.restype = ctypes.c_int; dm.dmAPISet.argtypes = [ctypes.c_char_p, ctypes.c_char_p] dm.dmAPIExec.restype = ctypes.c_int; dm.dmAPIExec.argtypes = [ctypes.c_char_p] status = dm.dmAPIInit()

The shared library whose name is in dmlib must be in the LD_LIBRARY_PATH (or SHLIB_PATH or LIBPATH, depending on the Unix flavor); specifying its full path name does work too.  As I wrote it before, if the script crashes, try to set it to libdmcl.so instead if it’s available.
The wrapper functions are used by all the verbs documented in the API Reference Manual. When the manual says for example:

Fetch
Purpose Fetches an object from the repository without placing a lock on the object.
Syntax
dmAPIExec("fetch,session,object_id[,type][,persistent_cache][,consistency_check_value]")
...
Return value
The Fetch method returns TRUE if successful or FALSE if unsuccessful.
...

it is the function dmAPIExec() that conveys the verb “fetch” and its arguments to the shared library. It takes just one argument, a string, and return, None if the call failed, a positive integer if it succeeded.

Another example:

Getservermap
Purpose Returns information about the servers known to a connection broker.
Syntax
dmAPIGet("getservermap,session,repository_name[,protocol][,host_name][,port_number]")
...
Return value
The Getservermap method returns a non-persistent ID for a server locator object.
...

Here, it’s dmAPIGet() that does it for the verb “getservermap”. It returns an empty string if the call failed (remapped to None to be more pythonic), a non-empty one with an ID if it succeeded.

For more usage comfort, a few functions have been added in the interface:

def connect(docbase, user_name, password):
"""
connects to given docbase as user_name/password;
returns a session id if OK, None otherwise
"""
def execute(session, dql_stmt):
"""
execute non-SELECT DQL statements;
returns TRUE if OK, False otherwise;
"""
def select(session, dql_stmt, attribute_names):
"""
execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
attributes_names is a list of attributes to extract from the result set;
return True if OK, False otherwise;
"""
def disconnect(session):
"""
closes the given session;
returns True if no error, False otherwise;
"""

Basically, they only wrap some error handling code around the calls to dmAPIGet()/dmAPIExec(). execute() and select() are just examples of how to use the interface and could be removed from it. Let’s give a look at the latter one for instance:

def select(session, dql_stmt, attribute_names):
   """
   execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
   attributes_names is a list of attributes to extract from the result set;
   return True if OK, False otherwise;
   """
   show("in select(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)

      s = ""
      for attr in attribute_names:
         s += "[" + attr + "]\t"
      print(s)
      resp_cntr = 0
      while dmAPIExec("next," + session + "," + query_id):
         s = ""
         for attr in attribute_names:
            value = dmAPIGet("get," + session + "," + query_id + "," + attr)
            if "r_object_id" == attr and value is None:
               raise(getOutOfHere)
            s += "[" + (value if value else "None") + "]\t"
         resp_cntr += 1
         show(str(resp_cntr) + ": " + s)
      show(str(resp_cntr) + " rows iterated")

      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)

      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in select():")
      print(e)
      traceback.print_stack()
      print(resp_cntr); print(attr); print(s); print("[" + value + "]")
      status = False
   finally:
      show("exiting select()")
      return status

If it weren’t for the error handling, it really looks like dmawk code fresh from the API manual !
And here are two invocations:

   print("")
   stmt = "select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document"
   status = DctmAPI.select(session, stmt, ("r_object_id", "object_name", "owner_name", "acl_domain", "acl_name"))
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful")

   print("")
   stmt = "select count(*) from dm_document"
   status = DctmAPI.select(session, stmt,  ["count(*)"])
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful"

Resulting in the following output:

in select(), dql_stmt=select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document
[r_object_id] [object_name] [owner_name] [acl_domain] [acl_name] 1: [0900c350800001d0] [Default Signature Page Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 2: [6700c35080000100] [CSEC Plugin] [dmadmin] [dmadmin] [dm_4500c35080000101] 3: [6700c35080000101] [Snaplock Connector] [dmadmin] [dmadmin] [dm_4500c35080000101] 4: [0900c350800001ff] [Blank Word 2007 / 2010 Document] [dmadmin] [dmadmin] [dm_4500c35080000101] 5: [0900c35080000200] [Blank Word 2007 / 2010 Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 6: [0900c35080000201] [Blank Word 2007 / 2010 Macro-enabled Document] [dmadmin] [dmadmin] [dm_4500c35080000101] 7: [0900c35080000202] [Blank Word 2007 / 2010 Macro-enabled Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 8: [0900c35080000203] [Blank Excel 2007 / 2010 Workbook] [dmadmin] [dmadmin] [dm_4500c35080000101] 9: [0900c35080000204] [Blank Excel 2007 / 2010 Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 10: [0900c350800001da] [11/21/2017 16:31:10 dm_PostUpgradeAction] [dmadmin] [dmadmin] [dm_4500c35080000101] 11: [0900c35080000205] [Blank Excel 2007 / 2010 Macro-enabled Workbook] [dmadmin] [dmadmin] [dm_4500c35080000101] 12: [0900c35080000206] [Blank Excel 2007 / 2010 Macro-enabled Template] [dmadmin] [dmadmin] [dm_4500c35080000101] 13: [0900c35080000207] [Blank Excel 2007 / 2010 Binary Workbook] [dmadmin] [dmadmin] [dm_4500c35080000101] 14: [0900c35080000208] [Blank PowerPoint 2007 / 2010 Presentation] [dmadmin] [dmadmin] [dm_4500c35080000101] 15: [0900c35080000209] [Blank PowerPoint 2007 / 2010 Slide Show] [dmadmin] [dmadmin] [dm_4500c35080000101] ...
880 rows iterated
exiting select()
select [select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document] was successful
in select(), dql_stmt=select count(*) from dm_document
[count(*)] 1: [880] 1 rows iterated
exiting select()
select [select count(*) from dm_document] was successful

Admittedly, the above select() function could be more clever and find by itself the queried attributes by inspecting the returned collection; also, the output could be more structured. Stay tuned on this channel, it’s coming up !

The packaging

In order to make the interface easily usable, it has been packaged into a module named DctmAPI. To use it, just add an “import DctmAPI” statement in the client script and prefix the functions from the module with “DctmAPI”, the module namespace, when calling them, as shown in the example above.
I’ve given some thoughts about making a class out of it but the benefits were not so obvious because many functions are so generic that most of them would have been @staticmethod of the class anyway. Moreover, the only state variable would have been the session id, so instead of carrying it around, an instance of the class would have to be used instead, no real improvement here. Even worse, as the session id would have been hidden in the instance, the statements passed to an instance object would have to be changed not to include it and leave that to the instance, which would hurt the habits of using the standard API argument format; also, as a few API verbs don’t need a session id, exceptions to the rule would need to be introduced, which would mess the class even more. Therefore, I chose to stick as closer as possible to the syntax documented in the API manual, at the only cost of introducing a namespace with the module.

The source

Without further ado, here is the full interface module DctmAPI.py:

"""
This module is a python - Documentum binding based on ctypes;
requires libdmcl40.so/libdmcl.so to be reachable through LD_LIBRARY_PATH;
C. Cervini - dbi-services.com

The binding works as-is for both python2 and python3; no recompilation required; that's the good thing with ctypes compared to e.g. distutils/SWIG;
Under a 32-bit O/S, it must use the libdmcl40.so, whereas under a 64-bit Linux it must use the java backed one, libdmcl.so;

For compatibility with python3 (where strings are now unicode ones and no longer arrays of bytes, ctypes strings parameters are always converted to unicode, either by prefixing them
with a b if litteral or by invoking their encode('ascii', 'ignore') method; to get back to text from bytes, b.decode() is used;these works in python2 as well as in python3 so the source is compatible with these two versions of the language;
"""

import os
import ctypes
import sys, traceback

# use foreign C library;
# use this library in Content server = v6.x, 64-bit Linux;
#dmlib = 'libdmcl.so'

dm = 0
logLevel = 1

class getOutOfHere(Exception):
   pass

def show(mesg):
   "displays the message mesg if allowed"
   if logLevel > 0:
      print(mesg)

def dmInit():
   """
   initializes the Documentum part;
   returns True if successfull, False otherwise;
   """

   show("in dmInit()")
   global dm

   try:
      dm = ctypes.cdll.LoadLibrary(dmlib);  dm.restype = ctypes.c_char_p
      show("dm=" + str(dm) + " after loading library " + dmlib)
      dm.dmAPIInit.restype    = ctypes.c_int;
      dm.dmAPIDeInit.restype  = ctypes.c_int;
      dm.dmAPIGet.restype     = ctypes.c_char_p;      dm.dmAPIGet.argtypes  = [ctypes.c_char_p]
      dm.dmAPISet.restype     = ctypes.c_int;         dm.dmAPISet.argtypes  = [ctypes.c_char_p, ctypes.c_char_p]
      dm.dmAPIExec.restype    = ctypes.c_int;         dm.dmAPIExec.argtypes = [ctypes.c_char_p]
      status  = dm.dmAPIInit()
   except Exception as e:
      print("exception in dminit(): ")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show("exiting dmInit()")
      return True if 0 != status else False
   
def dmAPIDeInit():
   """
   releases the memory structures in documentum's library;
   returns True if no error, False otherwise;
   """
   status = dm.dmAPIDeInit()
   return True if 0 != status else False
   
def dmAPIGet(s):
   """
   passes the string s to dmAPIGet() method;
   returns a non-empty string if OK, None otherwise;
   """
   value = dm.dmAPIGet(s.encode('ascii', 'ignore'))
   return value.decode() if value is not None else None

def dmAPISet(s, value):
   """
   passes the string s to dmAPISet() method;
   returns TRUE if OK, False otherwise;
   """
   status = dm.dmAPISet(s.encode('ascii', 'ignore'), value.encode('ascii', 'ignore'))
   return True if 0 != status else False

def dmAPIExec(stmt):
   """
   passes the string s to dmAPIExec() method;
   returns TRUE if OK, False otherwise;
   """
   status = dm.dmAPIExec(stmt.encode('ascii', 'ignore'))
   return True if 0 != status else False

def connect(docbase, user_name, password):
   """
   connects to given docbase as user_name/password;
   returns a session id if OK, None otherwise
   """
   show("in connect(), docbase = " + docbase + ", user_name = " + user_name + ", password = " + password) 
   try:
      session = dmAPIGet("connect," + docbase + "," + user_name + "," + password)
      if session is None or not session:
         raise(getOutOfHere)
      else:
         show("successful session " + session)
         show(dmAPIGet("getmessage," + session).rstrip())
   except getOutOfHere:
      print("unsuccessful connection to docbase " + docbase + " as user " + user_name)
      session = None
   except Exception as e:
      print("Exception in connect():")
      print(e)
      traceback.print_stack()
      session = None
   finally:
      show("exiting connect()")
      return session

def execute(session, dql_stmt):
   """
   execute non-SELECT DQL statements;
   returns TRUE if OK, False otherwise;
   """
   show("in execute(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)
      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)
      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in execute():")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show(dmAPIGet("getmessage," + session).rstrip())
      show("exiting execute()")
      return status

def select(session, dql_stmt, attribute_names):
   """
   execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
   attributes_names is a list of attributes to extract from the result set;
   return True if OK, False otherwise;
   """
   show("in select(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)

      s = ""
      for attr in attribute_names:
         s += "[" + attr + "]\t"
      print(s)
      resp_cntr = 0
      while dmAPIExec("next," + session + "," + query_id):
         s = ""
         for attr in attribute_names:
            value = dmAPIGet("get," + session + "," + query_id + "," + attr)
            if "r_object_id" == attr and value is None:
               raise(getOutOfHere)
            s += "[" + (value if value else "None") + "]\t"
         resp_cntr += 1
         show(str(resp_cntr) + ": " + s)
      show(str(resp_cntr) + " rows iterated")

      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)

      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in select():")
      print(e)
      traceback.print_stack()
      print(resp_cntr); print(attr); print(s); print("[" + value + "]")
      status = False
   finally:
      show("exiting select()")
      return status

def disconnect(session):
   """
   closes the given session;
   returns True if no error, False otherwise;
   """
   show("in disconnect()")
   try:
      status = dmAPIExec("disconnect," + session)
   except Exception as e:
      print("Exception in disconnect():")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show("exiting disconnect()")
      return status

# initializes the interface;
dmInit()
The test script

Here is an example of script showing how to use the interface:

#!/usr/bin/env python

"""
Test the ctypes-based python interface to Documentum API;
"""

import DctmAPI

# -----------------
# main;
if __name__ == "__main__":
   DctmAPI.logLevel = 1

   # not really needed as it is done in the module itself;
   status = DctmAPI.dmInit()
   if status:
      print("dmInit() was successful")
   else:
      print("dmInit() was not successful")

   print("")
   session = DctmAPI.connect(docbase = "dmtest", user_name = "dmadmin", password = "dmadmin")
   if session is None:
      print("no session opened, exiting ...")
      exit(1)
   
   print("")
   dump = DctmAPI.dmAPIGet("dump," + session + "," + "0900c35080008107")
   print("object 0900c35080008107 dumped:\n" + dump)
   
   print("")
   stmt = "update dm_document object set language_code = 'FR' where r_object_id = '0900c35080008107'"
   status = DctmAPI.execute(session, stmt)
   if status:
      print("execute [" + stmt + "] was successful")
   else:
      print("execute [" + stmt + "] was not successful")

   print("")
   stmt = "select r_object_id, object_name, owner_name, acl_domain, acl_name from dm_document"
   status = DctmAPI.select(session, stmt, ("r_object_id", "object_name", "owner_name", "acl_domain", "acl_name"))
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful")

   print("")
   stmt = "select count(*) from dm_document"
   status = DctmAPI.select(session, stmt,  ["count(*)"])
   if status:
      print("select [" + stmt + "] was successful")
   else:
      print("select [" + stmt + "] was not successful")

   print("")
   status = DctmAPI.disconnect(session)
   if status:
      print("successfully disconnected")
   else:
      print("error while  disconnecting")

   print("")
   status = DctmAPI.dmAPIDeInit()
   if status:
      print("successfully deInited")
   else:
      print("error while  deInited")

I’m not a day to day user of python so I guess there are ways to make the interface more idiomatic, or pythonic as they say. Feel free to adapt it to your tastes and needs. Comments and suggestions are welcome of course.

 

Cet article Adding a Documentum extension into python est apparu en premier sur Blog dbi services.

Customizing PostgreSQL parameters in EDB containers in MiniShift/OpenShift

Fri, 2018-05-18 13:06

In the last two posts we deployed an EDB database container and two pgpool instances and then scaled that up to include a read only replica. In this post will use a ConfigMap to adjust parameters in postgresql.conf as you will probably need to do that when you start using the EDB containers in your environment.

A ConfigMap is an object that can be used to provide parameter/values pairs to the container which then will be added to postgresql.conf file of the database containers. Creating a ConfigMap is quite easy, all you need to do is to create a file called “postgresql.conf.in” which lists all the parameters you want to get adjusted:

dwe@dwe:~$ cat /opt/ConfigMaps/postgresql.conf.in
work_mem='12MB'
shared_buffers='56MB'

In that case we want to adjust work_mem and shared_buffers, that’s it. To load that into OpenShift by using the oc command line utility:

dwe@dwe:~$ oc create configmap postgres-map --from-file=/opt/ConfigMaps/postgresql.conf.in
configmap "postgres-map" created
11:01:22 dwe@dwe:~$ oc get configmaps postgres-map
NAME           DATA      AGE
postgres-map   1         12m
dwe@dwe:~$ oc get configmaps postgres-map -o yaml
apiVersion: v1
data:
  postgresql.conf.in: |+
    work_mem='12MB'
    shared_buffers='56MB'

kind: ConfigMap
metadata:
  creationTimestamp: 2018-05-18T08:49:35Z
  name: postgres-map
  namespace: myproject
  resourceVersion: "16618"
  selfLink: /api/v1/namespaces/myproject/configmaps/postgres-map
  uid: 63c3a154-5a78-11e8-992f-ca15bcd30222

The issue is now that our current template does not know anything about that ConfigMap. So either adjust it or create a new one like this (changes are highlighted):

cat edb-as10-0-edb-cust.yaml
apiVersion: v1
kind: Template
metadata:
   name: edb-as10-custom
   annotations:
    description: "Custom EDB Postgres Advanced Server 10.0 Deployment Config"
    tags: "database,epas,postgres,postgresql"
    iconClass: "icon-postgresql"
objects:
- apiVersion: v1 
  kind: Service
  metadata:
    name: ${DATABASE_NAME}-service 
    labels:
      role: loadbalancer
      cluster: ${DATABASE_NAME}
  spec:
    selector:                  
      lb: ${DATABASE_NAME}-pgpool
    ports:
    - name: lb 
      port: ${PGPORT}
      targetPort: 9999
    sessionAffinity: None
    type: LoadBalancer
- apiVersion: v1 
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-pgpool
  spec:
    replicas: 2
    selector:
      lb: ${DATABASE_NAME}-pgpool
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        labels:
          lb: ${DATABASE_NAME}-pgpool
          role: queryrouter
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-pgpool
          env:
          - name: DATABASE_NAME
            value: ${DATABASE_NAME} 
          - name: PGPORT
            value: ${PGPORT} 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/edb/edb-pgpool:v3.5
          imagePullPolicy: IfNotPresent
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5
    triggers:
    - type: ConfigChange
- apiVersion: v1
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-as10-0
  spec:
    replicas: 1
    selector:
      db: ${DATABASE_NAME}-as10-0 
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        creationTimestamp: null
        labels:
          db: ${DATABASE_NAME}-as10-0 
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-as10 
          env:
          - name: DATABASE_NAME 
            value: ${DATABASE_NAME} 
          - name: DATABASE_USER 
            value: ${DATABASE_USER} 
          - name: DATABASE_USER_PASSWORD
            value: 'postgres' 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: PGPORT
            value: ${PGPORT} 
          - name: RESTORE_FILE
            value: ${RESTORE_FILE} 
          - name: LOCALEPARAMETER
            value: ${LOCALEPARAMETER}
          - name: CLEANUP_SCHEDULE
            value: ${CLEANUP_SCHEDULE}
          - name: EFM_EMAIL
            value: ${EFM_EMAIL}
          - name: NAMESERVER
            value: ${NAMESERVER}
          - name: POD_NAMESPACE
            valueFrom:
              fieldRef:
                fieldPath: metadata.namespace
          - name: POD_NODE
            valueFrom:
              fieldRef:
                fieldPath: spec.nodeName 
          - name: POD_IP
            valueFrom:
              fieldRef:
                fieldPath: status.podIP 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/edb/edb-as:v10.3
          imagePullPolicy: IfNotPresent 
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5 
          livenessProbe:
            exec:
              command:
              - /var/lib/edb/testIsHealthy.sh
            initialDelaySeconds: 600 
            timeoutSeconds: 60 
          ports:
          - containerPort: ${PGPORT} 
          volumeMounts:
          - name: ${PERSISTENT_VOLUME}
            mountPath: /edbvolume
          - name: pg-initconf
            mountPath: /initconf
        dnsPolicy: ClusterFirst
        restartPolicy: Always
        volumes:
        - name: ${PERSISTENT_VOLUME}
          persistentVolumeClaim:
            claimName: ${PERSISTENT_VOLUME_CLAIM}
        - name: pg-initconf
          configMap:
            name: postgres-map
             
    triggers:
    - type: ConfigChange
parameters:
- name: DATABASE_NAME
  displayName: Database Name
  description: Name of Postgres database (leave edb for default)
  value: 'edb'
- name: DATABASE_USER
  displayName: Default database user (leave enterprisedb for default)
  description: Default database user
  value: 'enterprisedb'
- name: REPL_USER
  displayName: Repl user
  description: repl database user
  value: 'repl'
- name: PGPORT
  displayName: Database Port
  description: Database Port (leave 5444 for default)
  value: "5444"
- name: LOCALEPARAMETER
  displayName: Locale
  description: Locale of database
  value: ''
- name: CLEANUP_SCHEDULE
  displayName: Host Cleanup Schedule
  description: Standard cron schedule - min (0 - 59), hour (0 - 23), day of month (1 - 31), month (1 - 12), day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0). Leave it empty if you dont want to cleanup.
  value: '0:0:*:*:*'
- name: EFM_EMAIL
  displayName: Email
  description: Email for EFM
  value: 'none@none.com'
- name: NAMESERVER
  displayName: Name Server for Email
  description: Name Server for Email
  value: '8.8.8.8'
- name: PERSISTENT_VOLUME
  displayName: Persistent Volume
  description: Persistent volume name
  value: ''
  required: true
- name: PERSISTENT_VOLUME_CLAIM 
  displayName: Persistent Volume Claim
  description: Persistent volume claim name
  value: ''
  required: true
- name: RESTORE_FILE
  displayName: Restore File
  description: Restore file location
  value: ''
- name: ACCEPT_EULA
  displayName: Accept end-user license agreement (leave 'Yes' for default)
  description: Indicates whether user accepts the end-user license agreement
  value: 'Yes'
  required: true

Once you imported that into OpenShift (check here if you don’t know how to do that) you get a new template you can deploy from:

Selection_025

When you create a new deployment of that one (again, check here if you don’t know how to do that) you will notice several things when you login to the container once it is up and running:

dwe@dwe:~$ oc rsh edb-as10-0-1-gk8dt
sh-4.2$ psql postgres
psql.bin (10.3.8)
Type "help" for help.

postgres=# show work_mem;
 work_mem 
----------
 12MB
(1 row)

postgres=# show shared_buffers ;
 shared_buffers 
----------------
 56MB
(1 row)

First of all and this is what we wanted: The PostgreSQL instance came up with the parameters we specified in the ConfigMap. When you look at the volumes present in the container there is a new one named after what we specified in the template:

sh-4.2$ df -h
Filesystem      Size  Used Avail Use% Mounted on
none             18G  4.0G   14G  24% /
tmpfs          1002M     0 1002M   0% /dev
tmpfs          1002M     0 1002M   0% /sys/fs/cgroup
/dev/sda1        18G  4.0G   14G  24% /initconf
shm              64M   12K   64M   1% /dev/shm
tmpfs          1002M   16K 1002M   1% /run/secrets/kubernetes.io/serviceaccount

Inside that volume there is the postgresql.conf.in file we also specified in the template and that is linked to $PGDATA:

sh-4.2$ ls -la /initconf
total 12
drwxrwsrwx  3 root 1000070000 4096 May 18 09:55 .
drwxr-xr-x 85 root root       4096 May 18 09:55 ..
drwxr-sr-x  2 root 1000070000 4096 May 18 09:55 ..2018_05_18_09_55_19.162613490
lrwxrwxrwx  1 root root         31 May 18 09:55 ..data -> ..2018_05_18_09_55_19.162613490
lrwxrwxrwx  1 root root         25 May 18 09:55 postgresql.conf.in -> ..data/postgresql.conf.in

And finally we can confirm the content of that file:

sh-4.2$ cat /initconf/postgresql.conf.in 
work_mem='12MB'
shared_buffers='56MB'

You can do the same for pg_hba.conf by creating a new ConfigMap for pg_hba.conf.in. In the next post we’ll look at how EDB Failover Manager is configured inside the containers.

 

Cet article Customizing PostgreSQL parameters in EDB containers in MiniShift/OpenShift est apparu en premier sur Blog dbi services.

Scaling the EDB containers in MiniShift/OpenShift

Fri, 2018-05-18 03:21

When you followed the last post you should have two pgpool instances and one EDB Postgres instance running in OpenShift. pgpool is responsible for the load balancing, meaning: Send write requests to the master instance and spread read requests over all instances. In the current setup this does not make much sense as we only have one instance so in this post we will scale the setup to finally consist of one master and one read only replica container.

Lets check what pods are currently running:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-vldkj   1/1       Running   2          5d
edb-pgpool-1-699vh   1/1       Running   2          5d
edb-pgpool-1-nsgrm   1/1       Running   2          5d

As expected, two pgpool containers and one database container are up an running. When we login to one of the pgpool container we should see the pgpool processes:

dwe@dwe:~$ oc rsh edb-pgpool-1-699vh
sh-4.2$ ps -ef | grep pool
edbuser     63     0  0 15:27 ?        00:00:00 pgpool -D -d -f /etc/sysconfig/edb/pgpool3.5/pgpool.conf -a /etc/sysconfig/edb/pgpool3.5/pool_hba.conf
edbuser     64    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     65    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     66    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     67    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     68    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     69    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     70    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     71    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     72    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     73    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     74    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     75    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     76    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     77    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     78    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     79    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     80    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     81    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     82    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     83    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     84    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     85    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     86    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     87    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     88    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     89    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     90    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     91    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     92    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     93    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     94    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     95    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     96    63  0 15:27 ?        00:00:00 pgpool: PCP: wait for connection request
edbuser     97    63  0 15:27 ?        00:00:00 pgpool: worker process
edbuser    365   342  0 15:30 ?        00:00:00 grep pool

What I am interested in is the backend configuration. As we do have only one database container there should be only one backend configured:

sh-4.2$ cat /etc/sysconfig/edb/pgpool3.5/pgpool.conf | grep backend | egrep -v "^ |^#"
backend_hostname0 = '172.17.0.7'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
								   # but also for ordinary conection to backend.
fail_over_on_backend_error = off

Fine, this is what is expected. The same is of course true for the other container. The question is: We have two pgpool instances running against one database instance, how can we connect then? What in addition happened when we deployed the setup is that a service was created:

dwe@dwe:~$ oc get svc -o wide
NAME          TYPE           CLUSTER-IP      EXTERNAL-IP                     PORT(S)          AGE       SELECTOR
edb-service   LoadBalancer   172.30.162.55   172.29.228.247,172.29.228.247   5444:30726/TCP   5d        lb=edb-pgpool

This services proxies the request to one of the pgpool instances which then routes the connection to the database instance. Lets try to connect from outside:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U postgres -W
Password for user postgres: 
psql: FATAL:  md5 authentication failed
DETAIL:  pool_passwd file does not contain an entry for "postgres"

Hm. Looking at the available databases and users in the database containers:

dwe@dwe:~$ oc rsh edb-as10-0-1-vldkj
sh-4.2$ psql edb
psql.bin (10.3.8)
Type "help" for help.

edb=# \l
                                 List of databases
   Name    |    Owner     | Encoding  | Collate | Ctype | ICU |  Access privileges  
-----------+--------------+-----------+---------+-------+-----+---------------------
 edb       | enterprisedb | SQL_ASCII | C       | C     |     | 
 postgres  | edbuser      | SQL_ASCII | C       | C     |     | 
 template0 | edbuser      | SQL_ASCII | C       | C     |     | =c/edbuser         +
           |              |           |         |       |     | edbuser=CTc/edbuser
 template1 | edbuser      | SQL_ASCII | C       | C     |     | =c/edbuser         +
           |              |           |         |       |     | edbuser=CTc/edbuser
(4 rows)

edb=# \du
                                         List of roles
       Role name       |                         Attributes                         | Member of 
-----------------------+------------------------------------------------------------+-----------
 aq_administrator_role | No inheritance, Cannot login                              +| {}
                       | Profile default                                            | 
 edbuser               | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                       | Profile default                                            | 
 enterprisedb          | Superuser                                                 +| {}
                       | Profile default                                            | 
 repl                  | Replication                                               +| {}
                       | Profile default                                            | 

… it becomes clear: There is no user postgres. Using the edbuser it works fine:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# 

How can we verify that the service actually is routing the connection to both pgpool instances? PostgreSQL comes with a set of administration functions and one of those can be used to verify that.

Do a first connection and ask for the client address (the client is one of the pgpool instances in this case):

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# select inet_client_addr();
 inet_client_addr 
------------------
 172.17.0.6
(1 row)

Do a second one:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# select inet_client_addr();
 inet_client_addr 
------------------
 172.17.0.7
(1 row)

postgres=# 

This two IP addresses are my two pgpool instances so this confirms that the service is behaving as expected. As having a single instance in that setup is somehow useless lets scale the setup by adding another database container. We’ll be doing that with the console for now:

Selection_022

After a few seconds another pod is running:
Selection_023

What I expect is that the pgpool instances got re-configured to include the new backend. Looking at the pods:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-frc99   1/1       Running   0          15m
edb-as10-0-1-gkpgq   1/1       Running   0          31m
edb-pgpool-1-lvwsq   1/1       Running   0          31m
edb-pgpool-1-nh4qb   1/1       Running   0          31m

Lets check the configuration of the first pgpool instance:

dwe@dwe:~$ oc rsh edb-pgpool-1-lvwsq
sh-4.2$ ps -ef | grep conf
edbuser   1918     0  0 07:43 ?        00:00:00 pgpool -D -d -f /etc/sysconfig/edb/pgpool3.5/pgpool.conf -a /etc/sysconfig/edb/pgpool3.5/pool_hba.conf
edbuser   2508  2483  0 07:48 ?        00:00:00 grep conf
sh-4.2$ cat /etc/sysconfig/edb/pgpool3.5/pgpool.conf | grep backend | egrep -v "^ |^#"
backend_hostname0 = '172.17.0.7'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
								   # but also for ordinary conection to backend.
fail_over_on_backend_error = off
backend_hostname1 = '172.17.0.5'
backend_port1 = 5444
backend_weight1 = 1
backend_data_directory1 = '/data1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
sh-4.2$ 

Here we go, works as expected. What is a bit strange is that $PGDATA on the second backend is in /data1 where it is in /var/lib/edb/as9.6/data on the first backend. From now on read only queries should land on either the slave or the master node node and statements that modify data should land on the master node, lets check what is master and what is replica:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "show pool_nodes" postgres
Password for user edbuser: 
 node_id |  hostname  | port | status | lb_weight |  role   | select_cnt 
---------+------------+------+--------+-----------+---------+------------
 0       | 172.17.0.7 | 5444 | 2      | 0.500000  | primary | 1
 1       | 172.17.0.5 | 5444 | 2      | 0.500000  | standby | 0
(2 rows)

When I do two read only statements I should be round-robined:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.7
(1 row)

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

Disabling load balancing should bring me to the master:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select /*NO LOAD BALANCE*/ inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select /*NO LOAD BALANCE*/ inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

Works quite fine. In the next post we’ll add ConfigMaps to customize our deployment. Usually you want to pre-configure the deployment so it fits for your requirements and that is what ConfigMaps are for.

 

Cet article Scaling the EDB containers in MiniShift/OpenShift est apparu en premier sur Blog dbi services.

12c upuserxt.lst, upobjxt.lst & Oracle Maintained objects/users

Tue, 2018-05-15 16:24

Mike Dietrich has blogged recently about upuserxt.lst and upobjxt.lst and how to query them with external table. The first time I’ve seen those ‘.lst’ files, the default extension for sqlplus spool files, I wondered whether they were provided in ?/rdbms/admin on purpose, or if they were just some leftovers from some tests Oracle did before packaging the Oracle Home. Finally, I realized that they were there on purpose and that those ‘.lst’ are important files when upgrading to 12c.

I’ll look at an 18c Oracle Home (/rdbms/admin) in the Oracle Cloud but that applies to all 12c (and 18c is a 12cR2 patchset). One of the most important little feature of 12c is the tagging of Oracle Supplied objects and users. Before 12c it was a nightmare to distinguish system users from application ones. I detailed that in a previous post.

At database creation: _oracle_script

In a newly created 12c database, all the objects and users belonging to the system are flagged with ORACLE_MAINTAINED=Y

Here is an example listing system users and roles:

SQL> select listagg(username,',' on overflow truncate) within group (order by username) from dba_users where oracle_maintained='Y';LISTAGG(USERNAME,','ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYUSERNAME)
 
ANONYMOUS,APPQOSSYS,AUDSYS,CTXSYS,DBSFWUSER,DBSNMP,DIP,DVF,DVSYS,GGSYS,GSMADMIN_INTERNAL,GSMCATUSER,GSMUSER,LBACSYS,MDDATA,MDSYS,OJVMSYS,OLAPSYS,ORACLE_OCM,ORDDATA,ORDPLUGINS,ORDSYS,OUTLN,REMOTE_SCHEDULER_AGENT,SI_INFORMTN_SCHEMA,SYS,SYS$UMF,SYSBACKUP,SYSDG,SYSKM,SYSRAC,SYSTEM,WMSYS,XDB,XS$NULL
 
SQL> select listagg(role,',' on overflow truncate) within group (order by role) from dba_roles where oracle_maintained='Y';
LISTAGG(ROLE,','ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYROLE)
 
ADM_PARALLEL_EXECUTE_TASK,APPLICATION_TRACE_VIEWER,AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE,AUDIT_ADMIN,AUDIT_VIEWER,AUTHENTICATEDUSER,CAPTURE_ADMIN,CDB_DBA,CONNECT,CTXAPP,DATAPATCH_ROLE,DATAPUMP_EXP_FULL_DATABASE,DATAPUMP_IMP_FULL_DATABASE,DBA,DBFS_ROLE,DBJAVASCRIPT,DBMS_MDX_INTERNAL,DV_ACCTMGR,DV_ADMIN,DV_AUDIT_CLEANUP,DV_DATAPUMP_NETWORK_LINK,DV_GOLDENGATE_ADMIN,DV_GOLDENGATE_REDO_ACCESS,DV_MONITOR,DV_OWNER,DV_PATCH_ADMIN,DV_POLICY_OWNER,DV_PUBLIC,DV_REALM_OWNER,DV_REALM_RESOURCE,DV_SECANALYST,DV_STREAMS_ADMIN,DV_XSTREAM_ADMIN,EJBCLIENT,EM_EXPRESS_ALL,EM_EXPRESS_BASIC,EXECUTE_CATALOG_ROLE,EXP_FULL_DATABASE,GATHER_SYSTEM_STATISTICS,GDS_CATALOG_SELECT,GGSYS_ROLE,GLOBAL_AQ_USER_ROLE,GSMADMIN_ROLE,GSMUSER_ROLE,GSM_POOLADMIN_ROLE,HS_ADMIN_EXECUTE_ROLE,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,IMP_FULL_DATABASE,JAVADEBUGPRIV,JAVAIDPRIV,JAVASYSPRIV,JAVAUSERPRIV,JAVA_ADMIN,JMXSERVER,LBAC_DBA,LOGSTDBY_ADMINISTRATOR,OEM_ADVISOR,OEM_MONITOR,OLAP_DBA,OLAP_USER,OLAP_XS_ADMIN,OPTIMIZER_PROCESSING_RATE,ORDADMIN,PDB_DBA,PROVISIONER,RDFCTX_ADMIN,RECOVERY_CATALOG_OWNER,RECOVERY_CATALOG_OWNER_VPD,RECOVERY_CATALOG_USER,RESOURCE,SCHEDULER_ADMIN,SELECT_CATALOG_ROLE,SODA_APP,SYSUMF_ROLE,WM_ADMIN_ROLE,XDBADMIN,XDB_SET_INVOKER,XDB_WEBSERVICES,XDB_WEBSERVICES_OVER_HTTP,XDB_WEBSERVICES_WITH_PUBLIC,XS_CACHE_ADMIN,XS_CONNECT,XS_NAMESPACE_ADMIN,XS_SESSION_ADMIN

And here is an exemple listing the owners of system objects flagged with ORACLE_MAINTAINED=Y

SQL> select listagg(num||' '||owner,',' on overflow truncate) within group (order by num) from (select owner,count(*) num from dba_objects where oracle_maintained='Y' group by owner);LISTAGG(NUM||''||OWNER,','ONOVERFLOWTRUNCATE)WITHINGROUP(ORDERBYNUM)
 
6 APPQOSSYS,8 DBSFWUSER,8 ORACLE_OCM,8 SI_INFORMTN_SCHEMA,10 ORDPLUGINS,10 OUTLN,13 REMOTE_SCHEDULER_AGENT,22 DVF,24 OJVMSYS,25 OLAPSYS,35 AUDSYS,55 DBSNMP,209 GSMADMIN_INTERNAL,239 LBACSYS,292 ORDDATA,398 DVSYS,399 WMSYS,412 CTXSYS,466 SYSTEM,1029 XDB,2574 MDSYS,3171 ORDSYS,12173 PUBLIC,51069 SYS

How this is done? That’s easy. All system objects are created by Oracle scripts, such as those called by catalog.sql and catproc.sql during database creation. Those scripts set “_oracle_script”=true before running the DDL and all object created while “_oracle_script”=true is flagged as Oracle Maintained.

If, in a lab (not in prod), you create your own object in the same way, they will also be flagged as Oracle Maintained:

SQL> connect / as sysdba
Connected.
SQL> alter session set "_oracle_script"=true;
Session altered.
 
SQL> create user FRANCK identified by myself;
User FRANCK created.
SQL> alter user FRANCK quota unlimited on users;
User FRANCK altered.
 
SQL> create table FRANCK.DEMO(n primary key) as select 1 from dual;
Table FRANCK.DEMO created.
SQL> alter session set "_oracle_script"=false;
Session altered.
 
SQL> select username,oracle_maintained from dba_users where username='FRANCK';
 
USERNAME ORACLE_MAINTAINED
-------- -----------------
FRANCK Y
SQL> select owner,object_name,object_type,oracle_maintained from dba_objects where owner='FRANCK';
 
OWNER OBJECT_NAME OBJECT_TYPE ORACLE_MAINTAINED
------ ---- ----- -
FRANCK DEMO TABLE Y
FRANCK SYS_C007409 INDEX Y

So, this one is easy. Database creation runs with “_oracle_script”=true and objects and users created when this parameter is set to true are flagged as Oracle Supplied objects.

And during upgrades?

When you upgrade from 11g to 12c you don’t have this Oracle Maintained information. The catupgrd runs with “_oracle_script”=true but this script does not create all objects. However the upgraded database has all system objects flagged as Oracle Maintained. This is where upuserxt.lst upobjxt.lst are used.

When Oracle developers build a new Oracle Home to be shipped, they create a database (including all options I suppose) and then run the utlupox.sql script. This script will list all Oracle Maintained users and objects, just relying on the flag that has been set during creation, and spool to the upuserxt.lst upobjxt.lst files. And those files will be shipped in the Oracle Home (all that under /rdbms/admin).

These .lst files will be used when upgrading from pre-12c in order to set the flags for Oracle Maintained objects. The external tables SYS.USERXT on upuserxt.lst and SYS.OBJXT on upobjxt.lst are created by catupcox.sql and, finally, those tables are read by catuposb.sql to set Oracle Maintained in USER$ and OBJ$. The catuposb.sql is a bit more complex that that because there are objects that can have different name when a database is created.

Note that this information about Oracle Maintained objects, in addition to being very useful for us, is crucial when you further convert the non-CDB to a PDB because those will become metadata links.

 

Cet article 12c upuserxt.lst, upobjxt.lst & Oracle Maintained objects/users est apparu en premier sur Blog dbi services.

Configuring WebLogic Server 12.2.1.3 Diagnostics Framework policies and actions

Tue, 2018-05-15 09:28

Configure a Policy-action (new name for watch-notification) to send a JMX notification when the monitored WebLogic Server is having stuck threads or high memory usage. We will use a simple JMX notification listener program which can process WLDF JMX notifications. In this case, it will simply print the contents of the notification.
For simplicity, we will have only two servers in the domain, AdminServer (admin-server) and Server1 (managed-server). We will configure WLDF on the Server1 to send a JMX notification when the Heap Free of the managed-server Server1 (actually, any server it is targeted to within the domain) is less than 20% or as soon as there is a Stuck thread.
We will use WebLogic Server Administration Console to configure WLDF. It can also be done with WLST. Console provides a nice feature to Create Repeatable Configuration Scripts, which can be handy for building such scripts. Configuration steps for this case consist of:
1. Create a Diagnostic Module.
2. Enable periodic metrics collection in the Diagnostic module.
3. Create a JMX notification type.
4. Create a policy to detect if the WebLogic Server is having stuck threads
5. Create a policy to detect high memory usage on the WebLogic Server
6. Target the Diagnostic module to the WebLogic Server server1
We will go over these steps and see how to configure WLDF using Console for this task.

1. Create a Diagnostic Module.

a) Log into Console and acquire Edit Lock so we can add new configuration to WebLogic Server.
b) From the left navigation tree, open the Diagnostics node and click on Diagnostic Modules. Console will show a table of existing Diagnostic modules within the domain. Click the New button to create a new diagnostic module. Call it myWLDF. Click OK to create the module. At this point, we have an empty myWLDF diagnostic module.

2. Enable periodic metrics collection in the Diagnostic module.

a) Click on the myWLDF module link in the table of Diagnostics modules.
b) Click on Collected Metrics sub-tab under Configuration tab.
c) Check the Enabled checkbox and set the Sampling Period to 10000 (10 seconds). Click Save.

3. Create a JMX notification type.

a) Configuring a policy/actions (Watch and Notifications in earlier versions) has two aspects. The first aspect is a policy (watch rule) which specifies the condition that WLDF will check. The second aspect is the set of actions (notifications) that will be sent when the rule condition is met. Console provides configuration assistants to make the configuration task easier. To create an action type:
b) Click Policies and Actions sub-tab under Configuration tab.
On the Actions sub-tab, click New in the Actions table.
c) Select “JMX Notification” for the notification type from the drop down list and click Next.
d) Give a name to the notification type (myJMX)
e) Keep the Notification Type to its default value
f) Check the Enable Notification checkbox and click OK to create the notification type.

4. Create a policy to detect servers having stuck threads

Now, we will create the policy rule based on runtime mbean data. Specifically, we will use the StuckThreadCount attribute on the WorkManagerRuntime mbeans. For each server within the domain, there is a WorkManagerRuntime mbean in the domain runtime mbean server. The StuckThreadCount attribute reflects the current number of stuck thread on the server. We will configure a rule which will fire as soon one server in the targets list is having a stuck thread.
a) Click on the Policies sub-tab -> Configuration. Click New in the Policies table.
b) Set Policy Name to WatchServerStuckThread. Select Collected Metrics for Policy Type, check Enabled checkbox and click Next.
c) Set the Policy Expression as below and Click Next:

wls.runtime.query('com.bea:Type=WorkManagerRuntime,*','StuckThreadCount').stream().anyMatch( x -> x > 0 )

Note that the syntax of Policy Expressions has changed from the previous WebLogic Versions. Starting from this version, the policy expression is to be provided in Java Expression Language (EL).

d) Keep The Frequency to “Every N Seconds” and Click Next
e) Set the Repeat parameter to 5 and click Next
f) In this wizard keep the default and click Next
g) In the diagnostic Actions part, in the available actions, select the myJMX created earlier and move it to the “chosen” actions.
h) Click Finish

5. Create a policy to detect high memory usage on the WebLogic Server

Now, we will create the policy rule based on runtime mbean data. Specifically, we will use the StuckThreadCount attribute on the WorkManagerRuntime mbeans. For each server within the domain, there is a WorkManagerRuntime mbean in the domain runtime mbean server. The StuckThreadCount attribute reflects the current number of stuck thread on the server. We will configure a rule which will fire as soon one server in the targets list is having a stuck thread.
a) Click on the Policies sub-tab -> Configuration. Click New in the Policies table.
b) Set Policy Name to WatchServerStuckThread. Select Collected Metrics for Policy Type, check Enabled checkbox and click Next.
c) Set the Policy Expression as below and Click Next:

wls.runtime.serverRuntime.JVMRuntime.heapFreePercent < 20

d) Keep The Frequency to “Every N Seconds” and Click Next
e) Set the Repeat parameter to 5 and click Next
f) In this wizard keep the default and click Next
g) In the diagnostic Actions part, in the available actions, select the myJMX created earlier and move it to the “chosen” actions.
h) Click Finish
6. Target the Diagnostic module to the WebLogic Server server1
a) Click on the Targets TAB
b) In the list of possible Targets, select the Server1 and click on Save
c) Activate Changes

Receiving Notifications

WLDF sends a JMX notification on a specific WLDF runtime mbean, whose ObjectName is of the form:
com.bea:Name=DiagnosticsJMXNotificationSource,ServerRuntime=$SERVER,Type=WLDFWatchJMXNotificationRuntime,WLDFRuntime=WLDFRuntime,WLDFWatchNotificationRuntime=WatchNotification
where $SERVER is the name of the WebLogic Server instance. For our case (Server1), it is:
com.bea:Name=DiagnosticsJMXNotificationSource,ServerRuntime=Server1,Type=WLDFWatchJMXNotificationRuntime,WLDFRuntime=WLDFRuntime,WLDFWatchNotificationRuntime=WatchNotification
By registering for JMX notifications on this mbean, a client program can listen to generated notifications.
We will use the JMXWatchNotificationListener.java provided in the Oracle WLDF documentation (see references). It is a simple notification listener for WLDF JMX notifications. It simply prints the contents of received notification, but can be easily adapted to perform other actions.

A sample Java code of such listener can be downloaded from the Oracle WebLogic Diagnostic Framework documentation (here)

To run it for this blog sample run:

java JMXWatchNotificationListener vm01 7006 weblogic Welcome1 Server1

Note: The WebLogic Managed Server named Server1 is listening on port 7006.

Sample WLDF message sent when the WebLogic Server is having Stuck Threads

Notification name: myJMXNotif called. Count= 79.
Watch severity: Notice
Watch time: Apr 24, 2018 12:08:35 PM CEST
Watch ServerName: Server1
Watch RuleType: Harvester
Watch Rule: wls.runtime.query('com.bea:Type=WorkManagerRuntime,*','StuckThreadCount').stream().anyMatch( x -> x > 0 )
Watch Name: WatchServerStuckThread
Watch DomainName: wldf_domain
Watch AlarmType: None
Watch AlarmResetPeriod: 60000

Sample WLDF message sent when the WebLogic Server heap free becomes low (less than 20%)

Notification name: myJMXNotif called. Count= 114.
Watch severity: Notice
Watch time: Apr 24, 2018 12:11:45 PM CEST
Watch ServerName: Server1
Watch RuleType: Harvester
Watch Rule: wls.runtime.serverRuntime.JVMRuntime.heapFreePercent < 20
Watch Name: WatchServerLowHeapFreePercent
Watch DomainName: wldf_domain
Watch AlarmType: None
Watch AlarmResetPeriod: 60000

 

Cet article Configuring WebLogic Server 12.2.1.3 Diagnostics Framework policies and actions est apparu en premier sur Blog dbi services.

PDB RMAN backups available after plugging in on a new CDB with Oracle 18c

Tue, 2018-05-15 08:53

With Oracle 18c, it is possible to use PDB rman backups created on the source CDB (they are called PREPLUGIN backups) when the PDB has been relocated to a target CDB.

In my environment, my original CDB is DB18, with the PDB named pdborig. The target CDB is PSI18.

The first step consist in running a rman backup on pdborig:

oracle@localhost:/u00/app/oracle/ [DB18] rman target sys/manager@pdborig
Recovery Manager: Release 18.0.0.0.0 - Production on Tue May 15 10:57:38 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB18:PDBORIG (DBID=3031125269)

RMAN> backup pluggable database pdborig plus archivelog;

Starting backup at 15-MAY-2018 10:57:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 device type=DISK
skipping archived logs when connected to a PDB
backup cancelled because there are no files to backup
Finished backup at 15-MAY-2018 10:57:56

Starting backup at 15-MAY-2018 10:57:56
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00029 name=/u00/app/oracle/oradata/DB18/pdborig/undotbs01.dbf
input datafile file number=00027 name=/u00/app/oracle/oradata/DB18/pdborig/system01.dbf
input datafile file number=00028 name=/u00/app/oracle/oradata/DB18/pdborig/sysaux01.dbf
input datafile file number=00030 name=/u00/app/oracle/oradata/DB18/pdborig/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-MAY-2018 10:57:56
channel ORA_DISK_1: finished piece 1 at 15-MAY-2018 10:57:59
piece handle=/u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/
2018_05_15/o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp 
tag=TAG20180515T105756 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-MAY-2018 10:57:59

Starting backup at 15-MAY-2018 10:57:59
using channel ORA_DISK_1
skipping archived logs when connected to a PDB
backup cancelled because there are no files to backup
Finished backup at 15-MAY-2018 10:57:59

Then we have to export the RMAN backup metadata for the non CDB into its dictionary using dbms_pdb.exportrmanbackup()

oracle@localhost:/u00/app/oracle/ [DB18] sqlplus sys/manager@pdborig as sysdba

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:00:38 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> execute dbms_pdb.exportrmanbackup();

PL/SQL procedure successfully completed.

As my environment is configured with TDE, in order to unplug the pdborig, we need to export the master key of the container database otherwise we will receive the following error message:

SQL> alter pluggable database pdborig 
unplug into '/home/oracle/pdborig.xml';
alter pluggable database pdborig unplug into '/home/oracle/pdborig.xml'
*
ERROR at line 1:
ORA-46680: master keys of the container database must be exported

We export the master key:

SQL> alter session set container=PDBORIG;
Session altered

SQL> administer key management 
  2  export encryption keys with secret "manager_cdb" 
  3  to '/home/oracle/pdborig.p12'
  4  identified by manager_cdb;

keystore altered.

SQL> alter pluggable database PDBORIG close immediate;

Pluggable database altered.

SQL> alter pluggable database PDBORIG unplug into '/home/oracle/pdborig.xml';

Pluggable database altered.

Finallly on the target CDB named PSI18, we first have to create a wallet and 
open the keystore. Just remember you have to define wallet_root and 
tde_configuration in your CDB environment in order to use TDE:


SQL> show parameter wallet

NAME		       TYPE	    VALUE
-------------------- ----------- -------------------------------------
wallet_root	       string	 /u00/app/oracle/admin/PSI18/walletcdb

SQL> alter system set tde_configuration="keystore_configuration=file";
System altered.

We create and open the keystore on the target CDB and we import the master key:

SQL> administer key management create keystore identified by manager_cdb;

keystore altered.

SQL> administer key management set keystore open 
identified by manager_cdb container=all;

keystore altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> administer key management import encryption keys 
     with secret "manager_cdb" from '/home/oracle/pdborig.p12'
     identified by "manager_cdb" with backup;

keystore altered.

We create pdbnew on the target CDB using pdborig.xml:

SQL> create pluggable database pdbnew using '/home/oracle/pdborig.xml'
file_name_convert=
('/u00/app/oracle/oradata/DB18/pdborig','/home/oracle/oradata/PSI18/pdbnew');

Pluggable database created.

We open the pluggable database pdbnew:

SQL> alter pluggable database pdbnew open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBNEW			  READ WRITE NO

And now the non CDB PDBNEW has been plugged in the target CDB, we can ask if the rman backups are visible because we had exported the rman metadata backup. To visualize that we have to use the preplugin clause:

RMAN> list preplugin backup of pluggable database pdbnew;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
9       Full    463.15M    DISK        00:00:01     15-MAY-2018 10:56:51
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105650
        Piece Name: /u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105650_fho86ltx_.bkp
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
10      Full    463.15M    DISK        00:00:01     15-MAY-2018 10:57:57
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105756
        Piece Name: /u00/app/oracle/fast_recovery_area/DB18/DB18/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

We can also display the archive logs preplugin backups:

RMAN> list preplugin archivelog all;

List of Archived Log Copies for database with db_unique_name PSI18
=====================================================================

Key     Thrd Seq     S Low Time            
------- ---- ------- - --------------------
.....

6       1    16      A 15-MAY-2018 10:08:53
/u00/app/oracle/fast_recovery_area/archivelog/2018_05_15/o1_mf_1_16_fho5r944_.a
...

So let’s see if we can make a restore and recover test:

We delete the user01.dbf datafile:

oracle@localhost:/u00/app/oracle/ [PSI18] rm /home/oracle/oradata/PSI18/pdbnew/users01.dbf

oracle@localhost:/u00/app/oracle/oradata/DB18/pdbseed/ [PSI18] sq

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:20:47 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> alter pluggable database pdbnew close;

Pluggable database altered.

SQL> alter pluggable database pdbnew open;
alter pluggable database pdbnew open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 23 - see DBWR trace file

Now we try to restore: we connect with rman to the target CDB and we set the PDB that needs to be restored with the command set preplugin container=pdbnew:

oracle@localhost:/u00/app/oracle/ [PSI18] rman target sys/manager@psi18

Recovery Manager: Release 18.0.0.0.0 - Production on Tue May 15 11:25:06 2018
Version 18.1.0.0.0

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

connected to target database: PSI18 (DBID=3896993764)

RMAN> set preplugin container=pdbnew;

executing command: SET PREPLUGIN CONTAINER
using target database control file instead of recovery catalog

RMAN> list preplugin backup of pluggable database pdbnew;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
9       Full    463.15M    DISK        00:00:01     15-MAY-2018 10:56:51
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105650
        Piece Name: /u00/app/oracle/fast_recovery_area/
/6C3BAD3B7C73354AE0530100007F9AD9/backupset/
2018_05_15/o1_mf_nnndf_TAG20180515T105650_fho86ltx_.bkp
  List of Datafiles in backup set 9
  Container ID: 4, PDB Name: PDBNEW
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081248 15-MAY-2018 10:56 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time     
------- ---- -- ---------- ----------- ------------ --------------------
10      Full    463.15M    DISK        00:00:01     15-MAY-2018 10:57:57
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20180515T105756
        Piece Name: /u00/app/oracle/fast_recovery_area
/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
  List of Datafiles in backup set 10
  Container ID: 4, PDB Name: PDBNEW
  File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- -------------------- ----------- ------ ----
  20 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/system01.dbf
  21 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
  22 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
  23 Full 1081326 15-MAY-2018 10:57 /home/oracle/oradata/PSI18/pdbnew/users01.dbf

We run the restore command with the preplugin clause:

RMAN> restore pluggable database pdbnew from preplugin;

Starting restore at 15-MAY-2018 11:26:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=108 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00020 to 
/home/oracle/oradata/PSI18/pdbnew/system01.dbf
channel ORA_DISK_1: restoring datafile 00021 to 
/home/oracle/oradata/PSI18/pdbnew/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00022 to 
/home/oracle/oradata/PSI18/pdbnew/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00023 to 
/home/oracle/oradata/PSI18/pdbnew/users01.dbf
channel ORA_DISK_1: reading from backup piece /u00/app/oracle/fast_recovery_area/
DB18/DB18/6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp
channel ORA_DISK_1: piece handle=/u00/app/oracle/fast_recovery_area/DB18/DB18/
6C3BAD3B7C73354AE0530100007F9AD9/backupset/2018_05_15/
o1_mf_nnndf_TAG20180515T105756_fho88nn0_.bkp tag=TAG20180515T105756
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 15-MAY-2018 11:26:28

We run the recover command with the preplugin clause:

RMAN> recover pluggable database pdbnew from preplugin;

Starting recover at 15-MAY-2018 11:27:02
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/15/2018 11:27:03
RMAN-06054: media recovery requesting unknown archived log 
for thread 1 with sequence 17 and starting SCN of 1081326

We have to catalog the archive logs generated after the backup into the target CDB by issuing the catalog preplugin archivelog command :

RMAN> catalog preplugin archivelog '/u00/app/oracle/fast_recovery_area/
DB18/DB18/archivelog/2018_05_15/o1_mf_1_17_fhob69t7_.arc';

cataloged archived log
archived log file name=/u00/app/oracle/fast_recovery_area/DB18/DB18/
archivelog/2018_05_15/o1_mf_1_17_fhob69t7_.arc RECID=7 STAMP=0

Finally the recover command runs successfully:

RMAN> recover pluggable database pdbnew from preplugin;

Starting recover at 15-MAY-2018 11:32:25
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 17 is already on disk as file 
/u00/app/oracle/fast_recovery_area/DB18/DB18/archivelog/2018_05_15/
o1_mf_1_17_fhob69t7_.arc
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-MAY-2018 11:32:26

We finish the recover and open the target PDB:

RMAN> recover pluggable database pdbnew;

Starting recover at 15-MAY-2018 11:33:10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 15-MAY-2018 11:33:10

RMAN> alter pluggable database pdbnew open;

Statement processed

RMAN> exit


Recovery Manager complete.

As far we can see, the target PDB has been successfully restored and recovered:

oracle@localhost:/u00/app/oracle/oradata/DB18// [PSI18] sq

SQL*Plus: Release 18.0.0.0.0 Production on Tue May 15 11:33:37 2018
Version 18.1.0.0.0

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDBNEW			  READ WRITE NO

The preplugin clause can be used on the target CDB with rman commands like restore, recover, crosscheck, list and delete.

This new feature helps to maintain the backup compliance after moving to a new target CDB.

 

Cet article PDB RMAN backups available after plugging in on a new CDB with Oracle 18c est apparu en premier sur Blog dbi services.

ADWC – connect from your premises

Mon, 2018-05-14 05:44

In the previous post about the Autonomous Data Warehouse Service, I’ve run queries though the Machine Learning Notebooks. But you obviously want to connect to it from your premises, with SQL*Net.

CaptureADWCconnect001Of course the connection, going through the public internet, must be secured. If you already use a managed service like the Oracle Exadata Express Cloud Service, you already know how to do: download a .zip containing the connection string and the wallet and certificate for SQL*Net encryption.

You get it from the Service Console, logged as the ADMIN user, and Administration tab. The Download Client Credentials asks you for the wallet password. However, this is not a password to protect the .zip file and the .zip file contains an auto-login wallet, so keep it secured.

SQL Developer

CaptureADWCconnect002 The simplest use of this file is with SQL Developer because you don’t even have to unzip it. Just choose a ‘Cloud PDB’ connection type, enter the path of the .zip file as Configuration File, the password as Keystore Password and the ADMIN user (or any user you have created with the Oracle ML Users).

In the tnsnames.ora provided in the .zip file there are 3 network service names connecting to 3 different services: _low, _medium and _high. They map to the resource manager plan so that you can run your queries with different priorities.

SQLcl thin

With SQLcl you do not need to unzip the credentials file, at least when you are using thin JDBC (the default).
You just register it with:

18:53:12 SQL> set cloudconfig /media/sf_share/ADWC/wallet_ADWC.zip
Using temp directory:/tmp/oracle_cloud_config4174171941677611695

and you are ready to connect to the _low, _medium and _high services.

As you see, it unzips the file into a temporary directory so you have to do it each time you run SQLcl. You can add this to login.sql and may add some housekeeping as this temporary directory may remain. Or run all this in a docker container.

This is simple, at least if you are running the latest Java 8 which includes the Java Cryptography Extension (JCE). If it is not the case, as when you use the Java Home provided with 18c (1.8.0_152), you have to add the jars yourself. But don’t worry, all is explained:

SQL> set cloudconfig /media/sf_share/ADWC/wallet_ADWC.zip
***** JCE NOT INSTALLED ****
***** CAN NOT CONNECT TO PDB Service without it ****
Current Java: /u01/app/oracle/product/18.0.0/dbhome_1/jdk/jre
Follow instructions on http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
SQL>

Of course the alternative is to install the latest Java

$ sudo rpm -i jre-8u171-linux-x64.rpm
Unpacking JAR files...
plugin.jar...
javaws.jar...
deploy.jar...
rt.jar...
jsse.jar...
charsets.jar...
localedata.jar...

and set JAVA_HOME to it before starting SQLcl

$ export JAVA_HOME=/usr/java/jre1.8.0_171-amd64
$ SQLPATH=~/sql bash $ORACLE_HOME/sqldeveloper/sqlcl/bin/sql /nolog

Anyway, in all cases, once the credential .zip is provided you can connect with Thin JDBC (the default) with a user/password that has been created in the ADWC:

SQL> connect admin@adwc_high
Password? (**********?) ****************
AArray = [B@24959ca4
AArray = [B@10289886
AArray = [B@32115b28
AArray = [B@2ad48653
Connected.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE BLOCKED CON_ID INSTANCE_MODE EDITION FAMILY DATABASE_TYPE
--------------- ------------- --------- ------- ------------ ------ -------- ------- -------- --------------- ------ ---------------- --------------- ------------- ------------ ------- ------ ------------- ------- ------ -------------
6 esj1pod6 12.2.0.1.0 13-MAY-18 OPEN YES 6 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE RAC

What’s in the .zip

The tnsnames.ora has entries for the low, medium, high services.

adwc_high = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )
 
adwc_low = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_low.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )
 
adwc_medium = (description= (address=(protocol=tcps)(port=1522)(host=adwc.uscom-east-1.oraclecloud.com))(connect_data=(service_name=p7zyfbmcnl4kjy3_adwc_medium.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )

The sqlnet.ora mentions the wallet used for SQL*Net encryption:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

Note that the directory is an absolute path and you probably want to change it to your TNS_ADMIN one where you unzip the file.

In the wallet location, you find the ewallet.p12 that contain the certificate and private keys, protected with password, and the cwallet.sso which do not need to provide the password to open it, so protect them with file permissions.

You find also keystore.jks which also contains the Self-signed certificate but in JKS truststore format. and referenced from ojdbc.properties properties:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

Once you have unzipped the credentials, you can use them to connect with OCI.

SQL*Plus or SQLcl -oci

If you want to connect with an OCI client, you have to unzip this file to your TNS_ADMIN directory. That can be the $ORACLE_HOME/network/admin, but be careful to overwrite existing files, or it can be a new directory you will use by setting the TNS_ADMIN environment variable (or registry entry) to it.

Here are some examples where I set TNS_ADMIN to the directory where I unzipped the credentials:

TNS_ADMIN=/media/sf_share/ADWC/wallet_ADWC sqlcl -oci /nolog
TNS_ADMIN=/media/sf_share/ADWC/wallet_ADWC sqlplus /nolog

Any application using OCI (the oracle client, which can be the InstantClient or a full database installation) can use this without providing any password.

Databas Link

With the unzipped credentials you can access through OCI which means that you can also have a database link to the ADWC database. The credentials must be unzipped (or merged) in the TNS_ADMIN (or default ?/rnetwork/admin) of the instance:

SQL> create database link ADWC connect to ADMIN identified by "Ach1z0#dAch1z0#d" using 'adwc_high';
Database link created.
 
SQL> select banner from v$version@ADWC;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Client Credential password

When downloading the .zip you are asked for a password to ‘protect this file to prevent unauthorized database access‘. But that is misleading. The .zip is not password protected. Anyone can open it. And it contains an auto-login wallet, so anybody can use it. You can access the database without this password. Of course, you cannot connect if you don’t have a user/password with a create session privileges, but you access to it for user credentials verification.

So what is this password used for? We have seen that SQL Developer needs the password (or you will get a files as java.io.IOException: Keystore was tampered with, or password was incorrect). Then, you may remove the .sso auto-login wallet from the .zip file when it is used only by SQL Developer. But of course, you have to think about where the password is stored in SQL Developer. Is is more secured than the .sso ?

As long as the auto-login wallet is there, you do not need to store the wallet password. But of course, you will protect credential files.

 

Cet article ADWC – connect from your premises est apparu en premier sur Blog dbi services.

Deploying EDB containers in MiniShift/OpenShift

Fri, 2018-05-11 10:15

In this post we’ll look at how we can deploy EnterpriseDB containers in MiniShift. When you need to setup MiniShift have a look here. In this post we’ll do the setup with the MiniShift console, in a next post we’ll do the same by using the command line tools.

As a few containers will be running at the end MiniShift got more resources when it was started:

dwe@dwe:/opt$ minishift delete
dwe@dwe:/opt$ minishift start --cpus 4 --disk-size 30GB --memory 4GB

Once MiniShift is up and running open the MiniShift console and login as developer/admin:

dwe@dwe:/opt$ minishift console

Selection_001

The first thing we need to do is to grant the necessary permissions after we stepped into “My Project”:
Selection_002

The permission are in Resources->Membership. Add admin,edit and view to the default account:
Selection_004

For accessing the EnterpriseDB container repository a new secret needs to be created which contains the connection details. Secrets are under Resources->Secrets:
Selection_005
Selection_006

As databases are happy when they can store their data on persistent storage we need a volume. Volumes can be created under “Storage”:
Selection_007
Selection_008

Now we need a local registry where we can push the EnterpriseDB containers to:

dwe@dwe:~$ minishift ssh
                        ##         .
                  ## ## ##        ==
               ## ## ## ## ##    ===
           /"""""""""""""""""\___/ ===
      ~~~ {~~ ~~~~ ~~~ ~~~~ ~~~ ~ /  ===- ~~~
           \______ o           __/
             \    \         __/
              \____\_______/
 _                 _   ____     _            _
| |__   ___   ___ | |_|___ \ __| | ___   ___| | _____ _ __
| '_ \ / _ \ / _ \| __| __) / _` |/ _ \ / __| |/ / _ \ '__|
| |_) | (_) | (_) | |_ / __/ (_| | (_) | (__|   <  __/ |
|_.__/ \___/ \___/ \__|_____\__,_|\___/ \___|_|\_\___|_|
Boot2Docker version 1.12.6, build HEAD : 5ab2289 - Wed Jan 11 03:20:40 UTC 2017
Docker version 1.12.6, build 78d1802
docker@minishift:~$ docker run -d -p 5000:5000 --restart=always --name registry registry:2
Unable to find image 'registry:2' locally
2: Pulling from library/registry
81033e7c1d6a: Pull complete 
...
Status: Downloaded newer image for registry:2
14e85f4e2a36e727a0584803e49bbd690ffdb092c02238a241bd2ad003680625
docker@minishift:~$ docker login containers.enterprisedb.com
Username: dbi-services
Password: 
Login Succeeded
docker@minishift:~$ docker pull containers.enterprisedb.com/test/edb-as:v10.3
v10.3: Pulling from test/edb-as
d9aaf4d82f24: Pulling fs layer 
...
Status: Downloaded newer image for containers.enterprisedb.com/test/edb-as:v10.3
docker@minishift:~$ docker tag containers.enterprisedb.com/test/edb-as:v10.3 localhost:5000/test/edb-as:v10.3
docker@minishift:~$ docker push localhost:5000/test/edb-as:v10.3
The push refers to a repository [localhost:5000/test/edb-as]
274db5c4ff47: Preparing 
...
docker@minishift:~$ docker pull containers.enterprisedb.com/test/edb-pgpool:v3.5
v3.5: Pulling from test/edb-pgpool
...
docker@minishift:~$ docker tag containers.enterprisedb.com/test/edb-pgpool:v3.5 localhost:5000/test/edb-pgpool:v3.5
docker@minishift:~$ docker push localhost:5000/test/edb-pgpool:v3.5
The push refers to a repository [localhost:5000/test/edb-pgpool]
8a7df26eb139: Pushed 
...

This is all what is required for the preparation. The next step is to import to the template which specifies the setup. For this little demo we’ll use this one:

apiVersion: v1
kind: Template
metadata:
   name: edb-as10-0
   annotations:
    description: "Standard EDB Postgres Advanced Server 10.0 Deployment Config"
    tags: "database,epas,postgres,postgresql"
    iconClass: "icon-postgresql"
objects:
- apiVersion: v1 
  kind: Service
  metadata:
    name: ${DATABASE_NAME}-service 
    labels:
      role: loadbalancer
      cluster: ${DATABASE_NAME}
  spec:
    selector:                  
      lb: ${DATABASE_NAME}-pgpool
    ports:
    - name: lb 
      port: ${PGPORT}
      targetPort: 9999
    sessionAffinity: None
    type: LoadBalancer
- apiVersion: v1 
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-pgpool
  spec:
    replicas: 2
    selector:
      lb: ${DATABASE_NAME}-pgpool
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        labels:
          lb: ${DATABASE_NAME}-pgpool
          role: queryrouter
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-pgpool
          env:
          - name: DATABASE_NAME
            value: ${DATABASE_NAME} 
          - name: PGPORT
            value: ${PGPORT} 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/test/edb-pgpool:v3.5
          imagePullPolicy: IfNotPresent
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5
    triggers:
    - type: ConfigChange
- apiVersion: v1
  kind: DeploymentConfig
  metadata:
    name: ${DATABASE_NAME}-as10-0
  spec:
    replicas: 1
    selector:
      db: ${DATABASE_NAME}-as10-0 
    strategy:
      resources: {}
      rollingParams:
        intervalSeconds: 1
        maxSurge: 25%
        maxUnavailable: 25%
        timeoutSeconds: 600
        updatePeriodSeconds: 1
      type: Rolling
    template:
      metadata:
        creationTimestamp: null
        labels:
          db: ${DATABASE_NAME}-as10-0 
          cluster: ${DATABASE_NAME}
      spec:
        containers:
        - name: edb-as10 
          env:
          - name: DATABASE_NAME 
            value: ${DATABASE_NAME} 
          - name: DATABASE_USER 
            value: ${DATABASE_USER} 
          - name: DATABASE_USER_PASSWORD
            value: 'postgres' 
          - name: ENTERPRISEDB_PASSWORD
            value: 'postgres' 
          - name: REPL_USER
            value: ${REPL_USER} 
          - name: REPL_PASSWORD
            value: 'postgres' 
          - name: PGPORT
            value: ${PGPORT} 
          - name: RESTORE_FILE
            value: ${RESTORE_FILE} 
          - name: LOCALEPARAMETER
            value: ${LOCALEPARAMETER}
          - name: CLEANUP_SCHEDULE
            value: ${CLEANUP_SCHEDULE}
          - name: EFM_EMAIL
            value: ${EFM_EMAIL}
          - name: NAMESERVER
            value: ${NAMESERVER}
          - name: POD_NAMESPACE
            valueFrom:
              fieldRef:
                fieldPath: metadata.namespace
          - name: POD_NODE
            valueFrom:
              fieldRef:
                fieldPath: spec.nodeName 
          - name: POD_IP
            valueFrom:
              fieldRef:
                fieldPath: status.podIP 
          - name: ACCEPT_EULA
            value: ${ACCEPT_EULA}
          image: containers.enterprisedb.com/test/edb-as:v10.3
          imagePullPolicy: IfNotPresent 
          readinessProbe:
            exec:
              command:
              - /var/lib/edb/testIsReady.sh
            initialDelaySeconds: 60
            timeoutSeconds: 5 
          livenessProbe:
            exec:
              command:
              - /var/lib/edb/testIsHealthy.sh
            initialDelaySeconds: 600 
            timeoutSeconds: 60 
          ports:
          - containerPort: ${PGPORT} 
          volumeMounts:
          - name: ${PERSISTENT_VOLUME}
            mountPath: /edbvolume
        dnsPolicy: ClusterFirst
        restartPolicy: Always
        volumes:
        - name: ${PERSISTENT_VOLUME}
          persistentVolumeClaim:
            claimName: ${PERSISTENT_VOLUME_CLAIM}
    triggers:
    - type: ConfigChange
parameters:
- name: DATABASE_NAME
  displayName: Database Name
  description: Name of Postgres database (leave edb for default)
  value: 'edb'
- name: DATABASE_USER
  displayName: Default database user (leave enterprisedb for default)
  description: Default database user
  value: 'enterprisedb'
- name: REPL_USER
  displayName: Repl user
  description: repl database user
  value: 'repl'
- name: PGPORT
  displayName: Database Port
  description: Database Port (leave 5444 for default)
  value: "5444"
- name: LOCALEPARAMETER
  displayName: Locale
  description: Locale of database
  value: ''
- name: CLEANUP_SCHEDULE
  displayName: Host Cleanup Schedule
  description: Standard cron schedule - min (0 - 59), hour (0 - 23), day of month (1 - 31), month (1 - 12), day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0). Leave it empty if you dont want to cleanup.
  value: '0:0:*:*:*'
- name: EFM_EMAIL
  displayName: Email
  description: Email for EFM
  value: 'none@none.com'
- name: NAMESERVER
  displayName: Name Server for Email
  description: Name Server for Email
  value: '8.8.8.8'
- name: PERSISTENT_VOLUME
  displayName: Persistent Volume
  description: Persistent volume name
  value: ''
  required: true
- name: PERSISTENT_VOLUME_CLAIM 
  displayName: Persistent Volume Claim
  description: Persistent volume claim name
  value: ''
  required: true
- name: RESTORE_FILE
  displayName: Restore File
  description: Restore file location
  value: ''
- name: ACCEPT_EULA
  displayName: Accept end-user license agreement (leave 'Yes' for default)
  description: Indicates whether user accepts the end-user license agreement
  value: 'Yes'
  required: true

For importing that into OpenShift go to “Overview” and select “Import YAML/JSON”:
Selection_010
Selection_011
Selection_012

This imports the template but does not process it right now. When you go back to “Overview” you should see a new template which you can provision:
Selection_013
Selection_014

Selecting the new template brings you to the specification of the variables. The only bits you need to adjust are the values for the volume and the volume claim:
Selection_015
Selection_016

A few moments later the EDB containers are up and running:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-fdr5j   1/1       Running   0          1m
edb-pgpool-1-9twmc   1/1       Running   0          1m
edb-pgpool-1-m5x44   1/1       Running   0          1m

Current there are two pgpool instances and one database instance container. You can double check that the instance is really running with:

dwe@dwe:~$ oc rsh edb-as10-0-1-fdr5j
sh-4.2$ psql postgres
psql.bin (10.3.8)
Type "help" for help.

postgres=# select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 EnterpriseDB 10.3.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

Going back to the “Overview” page in the console shows the same information:
Selection_019

In the next post we’ll scale up the deployment by adding two replicas and configure access from outside the cluster.

 

Cet article Deploying EDB containers in MiniShift/OpenShift est apparu en premier sur Blog dbi services.

SP2 for SQL Server 2016 is available with new helpful DMVs

Fri, 2018-05-11 09:23

Last month (April 24, 2018), the Service Pack 2 for SQL Server 2016 was released and distributed.
This Service Pack has new DMVs, already available in SQL Server 2017 RTM.

In this article, I will just write few words about 2 DMVs (sys.dm_db_log_stats & sys.dm_db_log_info) and a new column (modified_extent_page_count) in the DMV sys.dm_db_file_space_usage that I presented during our last event about SQL Server 2017. I think they are really helpful for DBA.
It’s also the opportunity to present you the demo that I create for our Event.

Preparation

First, I create the database smart_backup_2016 and a table Herge_Heros

CREATE DATABASE [smart_backup_2016]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'smart_backup_2016', FILENAME = N'G:\MSSQL\Data\smart_backup_2016.mdf' )
 LOG ON
( NAME = N'smart_backup_2016_log', FILENAME = N'G:\MSSQL\Log\smart_backup_2016_log.ldf' )
GO

USE smart_backup_2016
GO

CREATE TABLE [dbo].[Herge_Heros]
   (
   [ID] [int] NULL,
   [Name] [nchar](10) NULL
   ) ON [PRIMARY]
GO

I do a little insert and run a first Full and a first TLog Backup

INSERT INTO [Herge_Heros] VALUES(1,'Tintin') -- Tim
INSERT INTO [Herge_Heros] VALUES(2,'Milou') -- Struppi


BACKUP DATABASE [smart_backup_2016] TO  DISK = N'C:\Temp\smart_backup.bak' WITH NOFORMAT, NOINIT,  NAME = N'smart_backup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP Log [smart_backup_2016] TO  DISK = N'C:\Temp\smart_backup.log' WITH NOFORMAT, NOINIT,  NAME = N'smart_backup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

After, I insert a lot of line to have more than 50% modified pages

INSERT INTO [Herge_Heros] VALUES(3,'Quick') --Strups
INSERT INTO [Herge_Heros] VALUES(4,'Flupke')  --Stepppke
GO 100000

Now, the demo is ready!

new column modified_extent_page_count in sys.dm_db_file_space_usage

smart_backup01
As you can see in this screenshot, the column is really existing in SQL Server 2016 SP2 (13.0.5026.0).
After, you can, like us in our DMK maintenance, create an adapted Backup Strategy depending from changes and no more depending from the time.
In this stored procedure, if the modified pages are greater than 50% of the total pages, it will do a Full Backup and if the modified pages are less than 50%, it will do a Differential Backup.

USE [dbi_tools]
GO

CREATE or ALTER PROCEDURE [maintenance].[dbi_smart_backup] @database_name sysname
as
DECLARE @pages_changes Numeric(10,0)
DECLARE @full_backup_threshold INT
DECLARE @diff_backup_threshold INT
DECLARE @sql_query nvarchar(max)
DECLARE @page_change_text nvarchar(20)
DECLARE @param nvarchar(50)
DECLARE @backupfile nvarchar(2000)
SET @full_backup_threshold=50
SET @diff_backup_threshold=0
SET @param = N'@pages_changesOUT nvarchar(20) OUTPUT'
SET @sql_query =N'SELECT @pages_changesOUT=( 100 * Sum(modified_extent_page_count) / Sum(total_page_count) ) FROM ['+@database_name+'].sys.dm_db_file_space_usage'

EXECUTE sp_executesql @sql_query,@param ,@pages_changesOUT=@page_change_text OUTPUT; 
SET @pages_changes = CAST(@page_change_text AS Numeric(10,0)) 
IF @pages_changes > @full_backup_threshold
  BEGIN
     --Full Backup threshold exceeded, take a full backup
     Print 'Full Backup Threshold exceeded, take a full backup'
     SET @backupfile = N'C:\Temp\'+@database_name+N'_' + replace(convert(nvarchar(50), GETDATE(), 120), ':','_') + N'.bak'
   BACKUP DATABASE @database_name TO DISK=@backupfile
  END
  ELSE
  BEGIN
	   IF @pages_changes >= @diff_backup_threshold
		BEGIN
			-- Diff Backup threshold exceeded, take a differential backup
			Print 'Diff Backup threshold exceeded, take a differential backup'
			SET @backupfile = N'C:\Temp\'+@database_name+N'_' + replace(convert(nvarchar(50), GETDATE(), 120), ':','_') + N'.dif'
			BACKUP DATABASE @database_name TO DISK=@backupfile WITH differential
		END
	ELSE
		BEGIN
			-- No threshold exceeded, No backup
		PRINT 'No threshold exceeded, No backup'   
		END
  END
GO

Now, I run the stored procedure [maintenance].[dbi_smart_backup] in the dbi_tool

USE smart_backup_2016;
GO
EXEC [dbi_tools].[maintenance].[dbi_smart_backup] @database_name = N'smart_backup_2016'

smart_backup02
The dbi backup Stored Procedure in this case do a Full Backup because the modified pages are 64%.
I check the status of the modified pages and the modified pages are at 5%.
smart_backup03
If I restart the stored procedure, I do a differential backup.
smart_backup04
My backup strategy is really adapted to the change of pages in the database and no more based on the time (RTO vs RPO).
Let’s go to the new DMV sys.dm_db_log_stats do to the same with the TLog backup.

DMV sys.dm_db_log_stats

This DMV gives really good information about the transaction log files and can help to adapt the backup strategy and also control the growth of the file.
The DMV is very easy to use and for example, if you want to have the growth of the size since the last TLog backup, use the column log_since_last_log_backup_mb

SELECT log_since_last_log_backup_mb from sys.dm_db_log_stats(DB_ID('smart_backup_2016'))
GO

smart_backup05
Like below, I create in our DMK maintenance an adapted TLOG Backup [dbi_smart_tlog_backup] smart_backup06
If the TLOG is growing more that 5 MB from the last TLOG backup, It will do a TLOG Backup and if not, no TLOG Backup.
In my example, the growth is 548 MB, then a TLOG Backup is necessary.
smart_backup07
After, I control the size and as you can see the size since last TLOG Backup is 0.07MB
smart_backup08
As you can see, no TLOG backup… My backup strategy is adapted to the load! ;-)
smart_backup09

DMV sys.dm_db_log_info

This DMV will help us to have all VLF(Virtual Log File) information and no more using the DBCC Loginfo.
You can use this DMV very easily like this:

SELECT [name] AS 'Database Name', COUNT(l.database_id) AS 'VLF Count'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]

smart_backup10

These DMVs are very helpful and it is a good thing to have it also in SQL Server 2016 now.

 

Cet article SP2 for SQL Server 2016 is available with new helpful DMVs est apparu en premier sur Blog dbi services.

SQL Developer Web on the Oracle Cloud

Thu, 2018-05-10 12:21

You like SQL Developer because it is easy to install (just unzip a jar) and has a lot of features? Me too. It can be even easier if it is provided as a web application: no installation, and no java to take all my laptop RAM…
When I say no installation, you will see that you have some little things to setup here in DBaaS. That will probably be done for you in the managed services (PDBaaS) such as ‘Express’ and ‘Autonomous’ ones.

CaptureSDW010
Be careful, Oracle is a Top-Down deployment company. It seems that new products are announced first and then people have to work hard to make them available. Which means that if, like me, you want to test them immediately you may encounter some disappointment.
The announce was there. The documentation was there, mentioning that the Cloud Tooling must be upgraded to 18.2.3. But 18.2.3 was there only a few days later. You can check it from the place where the DBaaS looks for its software. Check from https://storage.us2.oraclecloud.com/v1/dbcsswlibp-usoracle29538/dbaas_patch if you a are not sure.

So, before being able to see SQL Developer in the colorful DBaaS landing page (where you can also access APEX for example) there’s a bit of command line stuff to do as root.

Install the latest Cloud Tooling

SQL Developer Web needs to be installed with the latest version of ORDS, which is installed with the latest version of Cloud Tooling aka dbaastools.rpm

You need to connect as root, so opc and then sudo

ssh opc@144.21.89.223
sudo su

Check if there is a new version to install:

dbaascli dbpatchm --run -list_tools | awk '/Patchid/{id=$3}END{print id}'

If something is returned (such as 18.2.3.1.0_180505.1604) you install it:

dbaascli dbpatchm --run -toolsinst -rpmversion=$(dbaascli dbpatchm --run -list_tools | awk '/Patchid/{id=$3}END{print id}')

Actually I got an error, and I had to ^C:

[root@DB18c opc]# dbaascli dbpatchm --run -toolsinst -rpmversion=$(dbaascli dbpatchm --run -list_tools | awk '/Patchid/{id=$3}END{print id}')
DBAAS CLI version 1.0.0
Executing command dbpatchm --run -toolsinst -rpmversion=18.2.3.1.0_180505.1604 -cli
/var/opt/oracle/patch/dbpatchm -toolsinst -rpmversion=18.2.3.1.0_180505.1604 -cli
Use of uninitialized value in concatenation (.) or string at /var/opt/oracle/patch/dbpatchm line 4773.
^C

But finally, it was installed because the ‘list_tools’ above returns nothing.

Enable SQL Developer Web

SQL Developer Web (SDW) is running in ORDS (Oracle REST Data Services) and must be enabled with the ORDS Assistant with the enable_schema_for_sdw action.
Here I’ll enable it at CDB level. I provide a password for the SDW schema. I create it in a file:

cat > password.txt <<<'Ach1z0#d'

You may secure that better than I do, as I’m putting the password on command line here. But this is only a test.

Then, still as root, I call the ORDS assistant to install SDW in C##SQLDEVWEB (as I’m installing it in CDB$ROOT I need a common user name).


/var/opt/oracle/ocde/assistants/ords/ords -ords_action=enable_schema_for_sdw -ords_sdw_schema="C##SQLDEVWEB" -ords_sdw_schema_password=$PWD/password.txt -ords_sdw_schema_enable_dba=true

Here is the output. The last lines are important:

WARNING: Couldn't obtain the "dbname" value from the assistant parameters nor the "$OCDE_DBNAME" environment variable
Starting ORDS
Logfile is /var/opt/oracle/log/ords/ords_2018-05-10_10:44:12.log
Config file is /var/opt/oracle/ocde/assistants/ords/ords.cfg
INFO: Starting environment summary checks...
INFO: Database version : 18000
INFO: Database CDB : yes
INFO: Original DBaaS Tools RPM installed : dbaastools-1.0-1+18.1.4.0.0_180123.1336.x86_64
INFO: Actual DBaaS Tools RPM installed : dbaastools-1.0-1+18.2.3.1.0_180505.1604.x86_64
INFO: DBTools JDK RPM installed : dbtools_jdk-1.8.0-2.74.el6.x86_64
INFO: DBTools JDK RPM "/var/opt/oracle/rpms/dbtools/dbtools_jdk-1.8.0-2.74.el6.x86_64.rpm" MD5 : 48f13bb401677bfc7cf0748eb1a6990d
INFO: DBTools ORDS Standalone RPM installed : dbtools_ords_standalone-18.1.0.11.22.15-1.el6.x86_64
INFO: DBTools ORDS Standalone RPM "/var/opt/oracle/rpms/dbtools/dbtools_ords_standalone-18.1.0.11.22.15-1.el6.x86_64.rpm" MD5 : 480355ac3ce0f357d5741c2c2f688901
INFO: DBTools DBaaS Landing Page RPM installed : dbtools_dbaas_landing_page-2.0.0-1.el6.x86_64
INFO: DBTools DBaaS Landing Page RPM "/var/opt/oracle/rpms/dbtools/dbtools_dbaas_landing_page-2.0.0-1.el6.x86_64.rpm" MD5 : af79e128a56b38de1c3406cfcec966db
INFO: Environment summary completed...
INFO: Action mode is "full"
INFO: Database Role is "PRIMARY"
INFO: Enabling "C##SQLDEVWEB" schema in "CDB$ROOT" container for SQL Developer Web...
 
SQL*Plus: Release 18.0.0.0.0 Production on Thu May 10 10:44:27 2018
Version 18.1.0.0.0
 
Copyright (c) 1982, 2017, Oracle. All rights reserved.
 
 
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
 
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL Developer Web user enable starting...
Enabling "C##SQLDEVWEB" user for SQL Developer Web...
 
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Creating "C##SQLDEVWEB" user
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Call completed.
Commit complete.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
 
"C##SQLDEVWEB" user enabled successfully. The schema to access SQL Developer Web
is "c_sqldevweb"...
 
PL/SQL procedure successfully completed.
 
SQL Developer Web user enable finished...
Disconnected from Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
INFO: To access SQL Developer Web through DBaaS Landing Page, the schema "c_sqldevweb" needs to be provided...
INFO: "C##SQLDEVWEB" schema in the "CDB$ROOT" container for SQL Developer Web was enabled successfully...
 

The information to remember here is that I will have to provide the c_sqldevweb schema name (which is the schema name I’ve provided but lowercased and with sequences of ‘special’ characters replaced by an underscore). It is lowercased, but it seems that the schemaname has to be provided in uppercase.

Basically what has been done is quite simple: create the C##SQLDEVWEB user and call ORDS.ENABLE_SCHEMA to enable it and map it to the url.

DBCS Landing Page 2.0.0

Now I’m ready to see SQL Developer on the DBCS Landing Page. You access this page by:

  1. Enabling https access from internet (in Access Rules, enable ora_p2_httpssl)
  2. going to default web page for your service, in my case https://144.21.89.223

You may have to accept some self-signed certificates

And here it is with SQL Developer Web in the middle:
CaptureSDW011

The above shows PDB1/pdbadmin for the schema but I installed it at CDB level and the log above tells me that the schema is c_sqldevweb, so given the input, I change the schema to c_sqldevweb then on the login page. Finally, the direct url in my example is https://144.21.89.223/ords/c_sqldevweb/_sdw.

I enter C##SQLDEVWEB (uppercase here) as the user and Ach1z0#d as the password.

And here is the Dashboard:
CaptureSDW012

Do not worry about the 97% storage used which tells me that SYSTEM is full. My datafiles are autoextensible.

Just go to the SQL Worksheet and check your files:

select tablespace_name,bytes/1024/1024 "MBytes", maxbytes/1024/1024/1024 "MaxGB", autoextensible from dba_data_files

Enable SDW for local PDB user

To enable a PDB local user, I run ORDS assistant with a local user name (PDBADMIN here) and an additional parameter with the PDB name (PDB1 here).


cat > password.txt <<<'Ach1z0#d'
/var/opt/oracle/ocde/assistants/ords/ords -ords_action=enable_schema_for_sdw -ords_sdw_schema=PDBADMIN -ords_sdw_schema_password=$PWD/password.txt -ords_sdw_schema_enable_dba=true -ords_sdw_schema_container=PDB1

Now, I can connect to it with PDB1/pdbadmin as schema name.

Error handling

CaptureRestCallFail
If, like me, you are not used to ORDS applications, you may waste some minutes looking at a splash screen waiting for the result. Always look at the message bar. All actions are REST calls and the message bar will show if a call is running or completed successfully or not. The example on the right shows ‘call failed’. You can click on it to see the REST call, and the error.

 

Cet article SQL Developer Web on the Oracle Cloud est apparu en premier sur Blog dbi services.

Some ideas about Oracle Database on Docker

Tue, 2018-05-08 15:55

This is not a ‘best practice’ but just some ideas about building Docker images to provide an Oracle Database. I started with the images provided by Oracle: https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance and this is great to validate the docker environment. Then I customized for my needs and here are the different points about this customization.

Do not send a huge context at each build attempt

I work by iteration. Sending a 3GB context each time I try a build is a no-go for me. Then I quickly stopped to put the Oracle installation .zip in the context of my build. I already blogged about this.

There are several ways to avoid to send a big context, such as having the .zip in an NFS or HTTP server and ADD it or RUN wget from there. I prefer to build one small container with this .zip that I’ll use later

In my current directory I have the linuxx64_12201_database.zip which I explicitly send to the context with this .dockerignore:

*
!linuxx64_12201_database.zip

And I build a franck/oracle122/zip image with it:

FROM oraclelinux:7-slim
ADD linuxx64_12201_database.zip /var/tmp

When done, I’ll not have to send the context again and I will build my container from this one with another Dockerfile:

FROM franck/oracle122/zip
RUN yum -y install oracle-database-server-12cR2-preinstall unzip tar wget openssl vi && rm -rf /var/cache/yum

Do not yum install at each build attempt

In the same idea, I build another intermediate image with the yum install above. The reason is that once I have it, I don’t need internet access anymore. I did that before boarding for an 8 hours flight. I build the above Dockerfile as franck/oracle122/prereq while on airport wifi and will use it later as the base for the final Dockerfile:

.dockerignore:

*

Dockerfile:

FROM franck/oracle122/prereq
# then follow all the work which do not need large context or internet connection
...

Even if you are not on a plane, it is always good to avoid internet access. You probably had to get some doors opened in the firewall in order to pull the base image. Now that you have it, you should keep it. Or one day, the security team will close the door again and you will waste a few hours. That also means that you do not start with a :latest image but with a specific version.

Do the long steps first

The Dockerfile provided by Oracle starts with all ENV and a COPY to add all scripts into the container. The problem is that each time you want to change a script, the build has to start from this step. And then the long operations have to be done again: unzip, install,…

I have a small context here (only the scripts and configuration files) but I ADD or COPY them only when needed. For example, here, a modification in install.rsp will re-do the runInstaller step, but the unzip one will not have to be done again because the cache is re-used:

WORKDIR /var/tmp
RUN unzip linuxx64_12201_database.zip
COPY install.rsp /var/tmp
RUN ./database/runInstaller -silent -force -waitforcompletion -responsefile /var/tmp/install.rsp -ignoresysprereqs -ignoreprereq ; true

The script that will run the container is added only at the end so that I can modify and re-build quickly without re-doing the previous steps.

VOLUME ["/opt/oracle/pdbs"] EXPOSE 1521 5500
COPY docker_cmd.sh /opt/oracle
CMD exec /opt/oracle/docker_cmd.sh ${CDB} ${PDB

Another step that I do at the end is removing the files I do not need in the container. Because that’s a guess and try approach and I want to build quickly. Of course, this may not be optimized for the size of all those layers, but I can reduce the final image later. The main feature of Docker build are the layers and I use them to develop the Dockerfile without wasting my time. For the waste of storage, I use ZFS with block level Cow, dedup and compression. For the final image, I’ll –squash it.

Remove all unnecessary files

The detail will probably go into a future blog post. But, as soon as runInstaller is done, and latest bundle patch applied, you can remove a lot of directories that I do not need anymore:

rm -rf $ORACLE_HOME/inventory $ORACLE_HOME/.patch_storage

As soon as the database has been created with DBCA, I do not need the DBCA templates anymore:

rm -rf $ORACLE_HOME/assistants

As this container will run only the instance, I can remove:

rm -rf $ORACLE_HOME/sqldeveloper $ORACLE_HOME/suptools $ORACLE_HOME/jdk

And depending on the options I will provide in the database, I remove the big ones:

rm -rf $ORACLE_HOME/apex $ORACLE_HOME/javavm $ORACLE_HOME/md

There is also a lot to remove from $ORACLE_HOME/lib (I need only a few *.so* that I can determine with strace, perf, lsof, ldd) and from $ORACLE_HOME/bin (basically, I need oracle, tnslsnr, lsnrctl, and sqlplus). Those are executables and you can strip them to reduce the size further. Definitely remove the last relink ones renamed as oracleO, …

Those are just examples, your list will depend on your version and usage, but this may reduce the image to 1GB or less. Of course, this is not supported. But the goal is to provide a small development database. Not an reliable and efficient one for production.

Use ZFS for the storage driver

An Oracle Database is full of large files that are updated sparsely. Just forget about OVERLAY and OVERLAY2 which copies the whole file to the new layer when you update a single byte of a file. I do not consider BTRFS seriously. In my opinion, ZFS is the only filesystem to consider for storing Docker images with large files. Enforce deduplication and compression to overcome the inflation of layering and the ignorance of sparse files. I think that recordsize=32k is a good idea from what I’ve seen about how docker applies writes to layers. More detail in a future blog post.

Note that layering issues are not only for build efficiency but also for container run. You will see that I put some datafiles in the image. Then, at database open, some blocks are changed (at least the headers) and I do not want a full file copy to the runnable layer. Block level CoW is required for that.

Create the CDB in the container

The container is the place to store all the software, and most of CDB$ROOT and PDB$SEED is part of the software distribution. This is what takes time when creating a database (catalog, catproc,…) and I definitely refuse to give a container to a developer where he will have to wait 10 minutes at run because the database has to be created on the external volume. A ‘docker run’ must be fast. And the external volume must contain only the data that has to be persisted, not 500MB of dbms_% package code, which will be all the same for all containers from the same image.

This means that I create the CDB during the build:

RUN /opt/oracle/product/12.2.0.1/dbhome_1/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName ${CDB} -sid ${CDB} -initParams db_unique_name=${CDB},service_names=${CDB},shared_pool_size=600M,local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))' -createAsContainerDatabase true -numberOfPdbs 0 -sysPassword oracle -systemPassword oracle -emConfiguration NONE -datafileDestination /opt/oracle/oradata -recoveryAreaDestination /opt/oracle/fast_recovery_area -recoveryAreaSize 1024 -storageType FS -sampleSchema false -automaticMemoryManagement false -totalMemory 1024 -databaseType OLTP -enableArchive false -redoLogFileSize 10 -useLocalUndoForPDBs true -createListener LISTENER:1521 -useOMF true -dbOptions OMS:false,JSERVER:false,SPATIAL:false,IMEDIA:false,ORACLE_TEXT:false,SAMPLE_SCHEMA:false,CWMLITE:false,DV:false,APEX:false
RUN rm -rf /opt/oracle/product/12.2.0.1/dbhome_1/assistants/dbca/*

No PDB here, as the PDB will be created at ‘docker run’ time into the external volume. I use a template with datafiles here, but I may prefer to run the whole creation to control the creation. I may even hack some .bsq and .sql files in RDBMS admin to reduce the size. I’m in archivelog mode here because I want to allow to flashback the PDB. The container is ephemeral. If it becomes too large (archive logs, audit, …) just remove it and start another one. Or add a script to remove the old ones (those not required by guarantee restore points).

Create the PDB in the external volume

The PDB is the only thing that must be persistent, and controlled by the developer. I create it with the following in my docker_cmd.sh (which is called from the Dockerfile CMD line providing CDB name and PDB name as arguments) script:

create pluggable database $2 admin user admin identified by oracle create_file_dest='/opt/oracle/pdbs';
alter pluggable database $2 open;
show pdbs

The PDB is bound to the external volume ( VOLUME [“/opt/oracle/pdbs”] ) thanks to 12.2 CREATE_FILE_DEST clause so that the developer can create datafiles only there. Then the ‘docker run’ is fast as a clone of PDB$SEED.

The developer will connect only to the PDB. He has nothing to do in CDB$ROOT. If there is a need to change something in CDB$ROOT, I’ll provide a new image. I may even define lockdown profiles rules to limit the PDB and define a listener where only the PDB registers.

Unplug the PDB at container stop

When the developer stops the container, I want to leave something consistent in the external volume. The way to do that quickly is a PDB unplug. An unplug to a PDB archive (a .pdb zip with all datafiles) would be nicer, but that takes too much time to create. I unplug to a .xml file. This is what I do on stop (SIGTERM and SIGSTOP):

alter pluggable database all close;
column pdb_name format a30
select pdb_id,pdb_name,status from dba_pdbs;
begin
for c in (select pdb_name from dba_pdbs where pdb_id>2) loop
dbms_output.put_line('-- Unpluging '||c.pdb_name);
execute immediate 'alter pluggable database "'||c.pdb_name||'" unplug into ''/opt/oracle/pdbs/'||c.pdb_name||'.xml''';
end loop;
for c in (select pdb_name from dba_pdbs where pdb_id>2 and status='UNPLUGGED') loop
dbms_output.put_line('-- Dropping '||c.pdb_name);
execute immediate 'drop pluggable database "'||c.pdb_name||'" keep datafiles';
end loop;
end;
/
-- All pluggable databases have been unplugged:
host ls /opt/oracle/pdbs/*.xml
-- Shutdown the CDB:
shutdown immediate;
-- You can plug those PDBs by passing the volume to a new container

The script iterates on all PDBs but I see no reason to create more than one. I unplug the PDB and drop it, and then shutdown the instance. We need the unplug to be completed before the stop timeout. The container may be killed before the drop or shutdown, but as long as we have the .xml we can plug the PDB into a new container.

Plug the PDB at container re-start

I mentioned earlier that at start I create the pluggable database mentioned by ${PDB}. But this is only when there is no /opt/oracle/pdbs/${PDB}.xml
If this file is found, this means that we provide a PDB that was unplugged by a previous container stop.
Actually, when the start detects this file, the following will be run:

whenever sqlerror exit failure;
create pluggable database "${PDB}" using '/opt/oracle/pdbs/${PDB}.xml';
host rm /opt/oracle/pdbs/${PDB}.xml
alter pluggable database "${PDB}" open;
select message from pdb_plug_in_violations;

Finally, because I may start a container which has a newer Release Update than the one which unplugged my PDB, I run:

$ORACLE_HOME/OPatch/datapatch

One PDB per container

My scripts process all PDBs but I think that in most cases we need to have a one-to-one relationship between the container and the PDB. The idea is to provide a database that is ready to use and where no administration/troubleshooting is required. The key here is to keep it simple. If you need to provide a large CDB with several PDBs, then Docker is not the solution to your problem. A virtual machine is a better answer for that.

SPfile? Password file?

The image build provided by Oracle stores the persistent configuration files with the database, in the external volume, through symbolic links from ?/dbs. But with my design, I don’t need to. The configuration of the instance, running in the container, is within the container. The passwords for SYS is in the container. Then SPfile and password files stay in the container. The runnable image is not read-only. It is writeable. We can write here as long as the changes do not have to persist beyond the container end of life.

The ‘scope=spfile’ parameters that can be modified by the developer will be PDB parameters. They are persisted because they go to the .xml file at unplug. Only in case of crash, without a clean unplug, those parameters may be stored only in the container. That’s a special case. A crashed container is not dead and jsut waits to be re-started.

Crash recovery needs the same container

There’s one big flaw with my CDB-in-container/PDB-in-volume design. The whole database datafiles must be consistent, are checkpointed together, and are all protected by the same redo stream, which is located in the container. But what’s the real problem about that? If the container is cleanly stopped, the PDB is unplugged and there is a clear separation between my external volume and the container. And both are consistent.

However, if the container crashes, the datafiles in my external volume are fuzzy and need recovery. This cannot be done without the CDB files which are on the container. This has only one consequence: the user must know that if the container was not cleanly stopped, she will need to start the PDB with the same container. I don’t think this is a real problem. I just ensure that the user gets the warning (a big README file in the external volume for example, created at start and removed at clean stop) and that the container will always be able to recover (no 100% full filesystem at start – anyway I have some log cleanups at start).

Handle all errors and signals

My startup script handle 3 situations.
The first one is the first start after creation of the container. This creates the pluggable database.
The second one is the re-start after a clean stop. This plugs the existing pluggable database.
The third one is crash-recovery after a kill. This just runs the automatic instance recovery.

Then the startup script will run in a loop, either tailing the alert.log or displaying some status info every minutes.

But before all of that, the startup script must handle the termination signals.

The clean stop is handled by the following signals:

trap 'shutdown SIGINT' SIGINT
trap 'shutdown SIGTERM' SIGTERM
trap 'shutdown EXIT' 0

SIGINT is for ^C when running the container, SIGTERM is when ‘docker stop’, and the signal 0 is when the container exits by itself. This can happen when my ‘tail -f’ on alert log is killed for example. All of them call my shutdown() procedure which is trying a clean stop (unplug the PDBs).

When the stop timout is expired or when we do a ‘docker kill’, there’s no time for that. The only thing I do here before a shutdown abort is an ‘alter system checkpoint’ to try to reduce the recovery needed. And display a WARNING message saying that the container that was killed must not be removed but be re-started asap to recover the PDB in the external volume. Maybe explicitly name the container and the command to re-start.

I do that with an abort() function called by the following:

trap 'abort SIGKILL' SIGKILL

The kill -9 of the instance, or container crash, cannot be handled. Recovery is needed as for the SIGKILL one. Here is the reason for keeping a permanent README file near the PDB to explain that the container which crashed should be restarted as soon as possible to recover this.

Conclusion

This is not a recipe of how to build an Oracle Database Docker image, but just some ideas. The most important is to know the requirement. If you provide Oracle on Docker just because the developers want that, the solution will probably be wrong: too large, too long, inefficient, and too complex,… They will not use it and they will tell everybody that Oracle is not cool because it cannot be dockerized.
CaptureDockerCDBPDB
With my PDB-in-volume / CDB-in-container design, I have :

  • Docker Images with the ephemeral software, one per version (down to patches), and with different set of component installed
  • External volume (personal directory in a NFS share, or a local one) with the persistent data and settings
  • Containers running the software on the data, linking them together for the whole lifecycle of the container

Think of them as 2 USB sticks, one with the software (binaries and packages), and one with the data (user metadata and data). When plugged together on the same container, it runs one version of software with one state of data. If the container crashes, you just run it again without unplugging any of the sticks. When you are done with your test or development iteration, you stop the container and remove it. Then you have unplugged the sticks to run another combination of data and software.

 

Cet article Some ideas about Oracle Database on Docker est apparu en premier sur Blog dbi services.

idql and its column output

Mon, 2018-05-07 03:23
idql and its column output

A few days ago, I was reading an interesting blog from distinguished colleague Clemens Bleile with the title “sqlplus and its column output” (link here https://blog.dbi-services.com/sqlplus-and-its-column-output/) and I said to myself: the lucky Oracle administrators and developers have sqlplus, a rather good, out of the box command-line tool to talk to their databases. What equivalent tool do we have with Documentum ? Well, we have mainly idql, which, to put it mildly, sucks. Unlike sqlplus, idql has no column formatting, no reporting, no variable substitution, no error trapping, actually almost nothing, not even command editing or command history (at least, under Unix/Linux). It just reads DQL statements, passes them to the content server and displays back the received answer. Pretty basic. However, for their defense, the Documentum creators gave away the source code of an ancient version (look for $DOCUMENTUM/share/sdk/example/code/idql.c), so it is relatively easy to enhance it the way you like.
Needless to say, having a nicely displayed output is not possible within idql. Whereas sqlplus’ column formatting allows to control the column width to make it narrower and thusly avoids those unending lines filled with spaces, in idql a query is displayed as is. For example, in sqlplus, “column mycol format A10″ tells sqlplus to display the column mycol as an alphanumeric value in a field not larger than 10 characters; exceeding characters are wrapped around on the next line(s). However, if, many columns are SELECTed, long result lines are unvoidable in both sqlplus and idql and the solution proposed in Clemens’ blog can help with idql too since it applies to the terminal as a whole.
Hereafter though, I’d like to propose a few alternatives that don’t require another terminal software, although they may somewhat lack in interactivity. One of them uses the less command, another uses less + a named pipe and a third one a simple awk script to compact and reflow a query’s output. Here we go.
1. Use less command
If reading the output can be done separately from entering the commands into idql, “less -S” is pretty cool:

# run the select and output the result into a text file;
idql dmtest -Udmadmin -Pdmadmin -w100 > tmp_file
select * from dm_sysobject
go
quit
EoQ
# now use less -S to examine the result;
less -S tmp_file

blog1blog2blog3
Now, it is possible to scroll left and right and have a good look at the result.
Some columns, though, are so wide and filled with trailing blanks that it is quite distracting. This will be taken care of later in the last alternative.

2. A more interactive variant with less -S
It is possible to stay in idql while the output is being redirected into a named pipe which is read by “less -S” and displayed in a second terminal. To do this, follow the steps below:
a. Create a named pipe named idqlp:

mknod -p idqlp

Contrary to the usual anonymous pipes, named pipes have, well, a name, and are created like files in a filesystem. As expected, like their counterparts, they can be written to and read from.
b. copy/paste the following command, it will create a pre-processor script for the less command:

cat - <<EoScript > lesspipe.sh
#! /bin/sh

# must define LESSOPEN environment variable to be used;
# export LESSOPEN="|~/lesspipe.sh %s"

case "$1" in
   idqlp)
      cat idqlp
      ;;
   *) exit 1
      ;;
esac
exit $?
EoScript

The preprocessor script will be invoked when less is launched, right before it. This is a nifty feature of less which allows to play tricks with binary files, e.g. decompressing them before viewing them (if they are compressed files). I guess less-ing a java class file could first invoke a decompiler and then pass the result to less. There is also a postprocessor for tasks to be performed after less exits, such as cleaning up the intermediate file created by the preprocessor. All this is very well presented in less’ man page.
c. make it executable:

chmod +x lesspipe.sh

d. copy/paste the following command in a terminal, it will create the consumer script that will continuously be reading from the named pipe idqlp:

cat - <<EoScript > readpipe.sh
#! /bin/bash

export LESSOPEN="|~/lesspipe.sh %s"

while [ true ]; do
   less -S idqlp
done
EoScript

e. make it executable:

chmod +x readpipe.sh

f. in the first terminal, run this script in the foreground:

./readpipe.sh

g. in the second terminal, run idql with a redirection into the named pipe idqlp:

idql dmtest -Udmadmin -Pdmadmin -w100 > idqlp

f. now, type your DQL statements with or without those endless lines:

execute show_sessions

go

select * from dm_sysobject
go

select * from dm_document
go

Result:

blog7

The DQL output of the second terminal (bottom) is displayed in the first terminal (top) where it can be browsed by less.
This trick works quite well as long as a few precautions are respected. As you know, a pipe blocks the producer when it gets full until the consumer starts reading it at the other end. Thus, idql is blocked as long as less has not finished reading its output; for short outputs, no special action is required but for those long listing, in order to force less to reach the end of the output, type shift-g in the less window; give it enough time so the DQL statement completes its output, then ctrl-C. idql is then released whereas the output can be quietly navigated from within less in the first terminal. Once done, BEFORE entering any new command in idql, quit less (command q) so the next cycle begins. Now, the next command can be typed in idql. Failure to do this can hang the consumer in the first terminal and the commands below must be used to get it back on track:

ctrl-Z         # send the consumer into the background;
jobs -l        # identify the pid that's messed up;
kill -9 pid    # send it ad patres;
./readpipe.sh  # restart the consumer;

Or use the one-liner:

jobs -l | grep readpipe.sh | cut -d\ -f3 | xargs kill -9

Sometimes, even the producer process must be restarted. If all this looks clumsy at first, once you get the habit of it, it becomes quite automatic.
This alternative is nice because it avoids cluttering the command window: the DQL commands are separated from their output and therefore stay visible in the second terminal. Moreover, as illustrated, error messages don’t show in the less-ed output.

3. The third alternative: compact the output
Eventhough those pesky long lines are now tamed, the issue of those extra-wide columns mostly filled with blanks remains and this alternative is aimed at it.
Firstly, here is where we take our inspiration from, Oracle sqlplus. Consider the SQL query below:
blog5
— The USER_PASSWORD column is still too wide, let’s narrow it:
blog6
See what happened here ? Column USER_PASSWORD’s text has been reflowed inside the column’s width, not truncated.
OK, we want all these 3 things:
. compact the columns by removing trainling blanks;
. control the columns width by resizing the way we like;
. introduce column wrapping if our width is too narrow;
Obviously, since we don’t have the source code of idql, we cannot enhance the way it displays the query results so we will do this outside idql and in 2 steps, execute the query and capture its output to process it.
The output processing is performed by the following awk script:

-- compact_wwa.awk;
# Usage:
#    gawk -v maxw=nn -f compact_wwa.awk file
# or:
#    cmd | gawk -v maxw=nn -f compact_wwa.awk
# where:
#     maxw is the maximum column width; characters outside this limit are wrapped around in their own column;
# example:
#     gawk -v maxw=50 -f compact_wwa.awk tmp_file  | less -S
# C. Cervini, dbi-services.com
BEGIN {
   while (getline && !match($0, /^([0-9]+> )+/));
   header = substr($0, RLENGTH + 1)
   getline
   nbFields = NF
   fs[0] = 0; fw[0] = -1 # just so that fs[1] = 1, see below;
   headerLine = ""; sepLine = ""
   for (i = 1; i <= NF; i++) {
      fs[i] = fs[i - 1] + fw[i - 1] + 2
      fw[i] = length($i)
      sepLine = sepLine sprintf("%s  ", substr($0, fs[i], min(fw[i], maxw)))
   }
   printWithWA(header)
   printf("%s\n", sepLine)
}
{
   if (match($0, /^\([0-9]+ rows? affected\)/)) {
      print
      exit
   }
   printWithWA($0)
}
function printWithWA(S) {
   do {
      left_over = ""
      for (i = 1; i <= nbFields; i++) {
         Min = min(fw[i], maxw)
         printf("%s  ", substr(S, fs[i], Min))
         subS = substr(S, fs[i] + Min, fw[i] - Min)
         if (length(subS) > 0) {
            left_over = left_over sprintf("%-*s  ", fw[i], subS)
         }
         else
            left_over = left_over sprintf("%*s  ", fw[i], "")
      }
      printf "\n"
      gsub(/ +$/, "", left_over)
      S = left_over
   } while (left_over)
}
function min(x, y) {
   return(x <= y ? x : y)
}

Now, let’s put it to use:

idql dmtest -Udmadmin -Pdmadmin <<EoQ | gawk -v maxw=35 -f compact_wwa.awk | less -S
select r_object_id, user_name, user_os_name, user_address, user_group_name, user_privileges, owner_def_permit, world_def_permit, group_def_permit, default_folder, user_db_name, description,
acl_domain, acl_name, user_os_domain, home_docbase, user_state, client_capability, globally_managed, user_delegation, workflow_disabled, alias_set_id, user_source, user_ldap_dn, user_xprivileges,
failed_auth_attempt, user_admin, user_global_unique_id, user_login_name, user_login_domain, user_initials, USER_PASSWORD, user_web_page, first_failed_auth_utc_time, last_login_utc_time,
deactivated_utc_time, deactivated_ip_addr, root_log_dir
from
   dm_user
go
exit
EoQ

By the way, funny thing, here “select user_password” is not the same as “select USER_PASSWORD”. The first returns a sequence of asterisks while the second an ASCII representation of the encrypted password. The generated SQL explains why.
“select user_password” gets compiled into the SQL statement below:

select all '****************' as user_password from dm_user_sp dm_user

whereas “select USER_PASSWORD” is the real one:

select all dm_user.USER_PASSWORD from dm_user_sp dm_user

Unlike unquoted column names in Oracle SQL, attribute names in DQL are case-sensitive !
And here is the result:

blog4

The script takes one parameter, maxw, the maximum column width. If the columns have too many characters, they are wrapped around on the next line(s) until the whole column has been displayed.

4. The altogether

What if we want the above line compaction and column wrapping around but interactively like in alternative 2 ? Easy. Just edit the script readpipe.sh and change line

cat idqlp

to

cat idqlp | gawk -v maxw=35 -f compact_wwa.awk

Said otherwise, we are preprocessing idql’s output through the awk filter before giving it to less.

A final alternative

We can achieve the same result with dmawk by writing a generic procedure that takes a DQL query to execute and a maximum column width, or a list of columns name and width (like if we entered a sequence of “col mycol format …” in sqlplus) so everything is done on the fly, but, as they say, this is left as an exercise to the reader. Or, why not, maybe in a blog to come.

 

Cet article idql and its column output est apparu en premier sur Blog dbi services.

ADWC – the hidden gem: Zepplin Notebook

Fri, 2018-05-04 14:22

IMG_5339
In the previous blog posts I explained how to create, and stop/start the Autonomous Data Warehouse Cloud service. And I didn’t show yet how to connect to it. It is easy, from sqlplus or SQL Developer, or SQLcl.

But there’s something more exciting to run some SQL queries: the Oracle Machine Learning Notebooks based on Apache Zepplin. At first, I didn’t realize why the administration menu entry to create users in the ADWC service was named ‘Manage Oracle ML Users’, and didn’t realize that the ‘Autonomous Data Warehouse Cloud’ header was replaced by ‘Machine Learning’.

But last week at IOUG Collaborate 18, I visited the Demo Grounds and thanks to Charlie Berger I realized all the power of this: we are in the ‘Machine Learning’ interface here and the home button opens all the features available to query the ADWC database, including the SQL Notebooks based on Apache Zepplin.

Here is the path to this hidden Gem. From your ADWC service, you go to the Service Console:
CaptureZepplin001

Here you log as the ADMIN user with the >12 characters password that you have defined at service creation. Don’t worry if you forgot it, you can reset it from here:
CaptureZepplin002

Once connected, you go to the Administration tab and choose the ‘Manage Oracle ML Users':

CaptureZepplin003

Here you have to create a user because the ADMIN user not a Machine Learning user. Machine Learning users need one of the following roles: OML_DEVELOPER, OML_APP_ADMIN, OML_SYS_ADMIN. The user you will create here will have OML_DEVELOPER which is required to use SQL Notebooks.

Now that you have a user created from here, you can click on this little house icon, which is your home in the Machine Learning part of the ADWC:

CaptureZepplin004

Here you connect with the user you have created from the Oracle ML User page (not the ADMIN one as it has no OML role granted).

CaptureZepplin005

Then you are in your OML home, ready to run SQL from a Notebook:

CaptureZepplin006

I’ll show what you can do in future post. But just to give you an idea, you have a Notebook where you can type a query, execute it, and have the result displayed as a table, or as a graph. Here I was looking at I/O latency and the following shows me that the ‘cell single block physical read’, which are nothing else than the buffered one-block-at-a-time reads that are called ‘db file sequential read’ when not on Exadata, in dark green here, have most of their I/O call time between 128 and 512 microseconds.

CaptureZepplin007

I like this way to have the result just under the query, with easy formatting. The code, documented, is at the same place as the result, in a notebook that is easy to refresh, or share. And you can export the whole in a simple JSON file.

 

Cet article ADWC – the hidden gem: Zepplin Notebook est apparu en premier sur Blog dbi services.

Pages