Feed aggregator

Because of its implementation of MVCC PostgreSQL needs a way to cleanup old/dead rows and this is the responsibility of vacuum. Up to PostgreSQL 12 this is done table per table and index per index. There are a lot of parameters to fine tune auto vacuum but none of those allowed vacuum to run in parallel against a relation. The only option you had to allow auto vacuum to do more work in parallel was to increase autovacuum_max_workers so that more relations can be worked on at the same time. Working against multiple indexes of the same table was not possible. Once PostgreSQL 13 will be released this will change.

When you have a look at the help of vacuum you will notice a new option:

postgres=# \h vacuum
Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP [ boolean ]
    TRUNCATE [ boolean ]
    PARALLEL integer

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

URL: https://www.postgresql.org/docs/devel/sql-vacuum.html

By providing a positive integer to the “PARALLEL” option you tell vacuum how many background workers should be used to vacuum indexes for a given table (0 will disable parallel processing). Lets do a small test setup to demonstrate this:

postgres=# create table t1 as select i as a, i::text as b, now() as c from generate_series(1,3000000) i;
SELECT 3000000
postgres=# create index i1 on t1(a);
CREATE INDEX
postgres=# 
postgres=# create index i2 on t1(b);
CREATE INDEX
postgres=# create index i3 on t1(c);
CREATE INDEX

One table, four indexes. If we go for parallel 4 we should see four background workers doing the work against the indexes:

postgres=# update t1 set a=5,b='ccc',c=now() where mod(a,5)=0;
UPDATE 600000
postgres=# vacuum (parallel 4) t1;
VACUUM

As the table and the indexes are quite small we need to be fast but at least 2 parallel workers show up in the process list for the vacuum operation:

postgres 16688 15925 13 07:30 ?        00:01:07 postgres: postgres postgres [local] VACUUM
postgres 19184 15925  0 07:39 ?        00:00:00 postgres: parallel worker for PID 16688   
postgres 19185 15925  0 07:39 ?        00:00:00 postgres: parallel worker for PID 16688   

Nice. Please note that indexes are only considered for parallel vacuum when they meet the min_parallel_index_scan_size criteria. For FULL vacuum there will be no parallel processing as well.

Btw: The current maximal value is 1024:

postgres=# vacuum (parallel -4) t1;
ERROR:  parallel vacuum degree must be between 0 and 1024
LINE 1: vacuum (parallel -4) t1;

You can also see the parallel stuff on the verbose output:

postgres=# vacuum (parallel 4, verbose true) t1;
INFO:  vacuuming "public.t1"
INFO:  launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO:  scanned index "i2" to remove 600000 row versions by parallel vacuum worker
DETAIL:  CPU: user: 0.24 s, system: 0.06 s, elapsed: 0.89 s
INFO:  scanned index "i1" to remove 600000 row versions
DETAIL:  CPU: user: 0.17 s, system: 0.10 s, elapsed: 1.83 s
INFO:  scanned index "i3" to remove 600000 row versions by parallel vacuum worker
DETAIL:  CPU: user: 0.16 s, system: 0.14 s, elapsed: 1.69 s
INFO:  scanned index "i4" to remove 600000 row versions by parallel vacuum worker
DETAIL:  CPU: user: 0.25 s, system: 0.09 s, elapsed: 1.17 s
INFO:  "t1": removed 600000 row versions in 20452 pages
DETAIL:  CPU: user: 0.17 s, system: 0.16 s, elapsed: 1.43 s
INFO:  index "i1" now contains 3000000 row versions in 14308 pages
DETAIL:  600000 index row versions were removed.
1852 index pages have been deleted, 640 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "i2" now contains 3000000 row versions in 14305 pages
DETAIL:  600000 index row versions were removed.
1851 index pages have been deleted, 640 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "i3" now contains 3000000 row versions in 14326 pages
DETAIL:  600000 index row versions were removed.
3941 index pages have been deleted, 1603 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "i4" now contains 3000000 row versions in 23391 pages
DETAIL:  600000 index row versions were removed.
5527 index pages have been deleted, 2246 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "t1": found 600000 removable, 3000000 nonremovable row versions in 21835 out of 22072 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 499
There were 132 unused item identifiers.
Skipped 0 pages due to buffer pins, 237 frozen pages.
0 pages are entirely empty.
CPU: user: 0.75 s, system: 0.36 s, elapsed: 5.07 s.
INFO:  vacuuming "pg_toast.pg_toast_16392"
INFO:  index "pg_toast_16392_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_16392": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 499
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

Cet article PostgreSQL 13: parallel vacuum for indexes est apparu en premier sur Blog dbi services.

In the last post I described how you can create your own PostgreSQL image in Nutanix Era. In Nutanix wording this is a “Software profile”. This profile can now be used to deploy PostgreSQL VMs with just a few clicks or by using the API. In this post we’ll look at how this can be done and if it is really as easy as Nutanix promises. We’ll be doing it by using the graphical console first and then by using the API. Lets go.

For deploying new databases we obviously need to go to the database section of Era:

For now we are going to deploy a single instance:

Our new software profile is available and this is what we use. Additionally a public ssh key should be provided for accessing the node:

Provide the details for the new instance:

Time machine allows you to go back in time and creates automatic storage snapshots according to the schedule you define here:

SLAs define retention policies for the snapshots. I will not cover that her.

Kick it off and wait for the task to finish:

A few minutes later it is done:

Our new database is ready:

From a deployment point of view this is super simple: A few clicks and a new PostgreSQL server is ready to use in minutes. The API call to do the same on the command line would be this:

curl -k -X POST \
	https://10.38.11.9/era/v0.8/databases/provision \
	-H 'Content-Type: application/json' \
	-H 'Authorization: Basic YWRtaW46bngyVGVjaDAwNyE=' \
	-d \
	'{"databaseType":"postgres_database","databaseName":"db2","databaseDescription":"db2","clusterId":"ccdab636-2a11-477b-a358-2b8db0382421","softwareProfileId":"aa099964-e17c-4264-b047-00881dc5e2f8","softwareProfileVersionId":"7ae7a44c-d7c5-46bc-bf0f-f9fe7665f537","computeProfileId":"fb1d20bb-38e4-4964-852f-6209990402c6","networkProfileId":"8ed5214c-4e2a-4ce5-a899-a07103bc60cc","dbParameterProfileId":"3679ab5b-7688-41c4-bcf3-9fb4491544ea","newDbServerTimeZone":"Europe/Zurich","timeMachineInfo":{"name":"db2_TM","description":"","slaId":"4d9dcd6d-b6f8-47f0-8015-9e691c1d3cf4","schedule":{"snapshotTimeOfDay":{"hours":1,"minutes":0,"seconds":0},"continuousSchedule":{"enabled":true,"logBackupInterval":30,"snapshotsPerDay":1},"weeklySchedule":{"enabled":true,"dayOfWeek":"FRIDAY"},"monthlySchedule":{"enabled":true,"dayOfMonth":"17"},"quartelySchedule":{"enabled":true,"startMonth":"JANUARY","dayOfMonth":"17"},"yearlySchedule":{"enabled":false,"dayOfMonth":31,"month":"DECEMBER"}},"tags":[],"autoTuneLogDrive":true},"provisionInfo":[{"name":"application_type","value":"postgres_database"},{"name":"nodes","value":"1"},{"name":"listener_port","value":"5432"},{"name":"proxy_read_port","value":"5001"},{"name":"proxy_write_port","value":"5000"},{"name":"database_size","value":10},{"name":"working_dir","value":"/tmp"},{"name":"auto_tune_staging_drive","value":true},{"name":"enable_synchronous_mode","value":false},{"name":"dbserver_name","value":"postgres-2"},{"name":"dbserver_description","value":"postgres-2"},{"name":"ssh_public_key","value":"ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDgJb4co+aaRuyAJv8F6fsz2YgO0ZHldX6qS22c813iDeUGWP/1bGhF598uODlgK5nx29sW2WTlcmorZuCgHC2BJfzhL1xsL5Ca94uAEg48MbA+1+Woe49mF6bPDJWLXqC0YUpCDBZI9VHnrij4QhX2r3G87W0WNnNww1POwIJN3xpVq/DnDWye+9ZL3s+eGR8d5f71iKnBo0BkcvY5gliOtM/DuLT7Cs7KkjPgY+S6l5Cztvcj6hGO96zwlOVN3kjB18RH/4q6B7YkhSxgTDMXCdoOf9F6BIhAjZga4lodHGbkMEBW+BJOnYnFTl+jVB/aY1dALECnh4V+rr0Pd8EL daw@ltdaw"},{"name":"db_password","value":"postgres"}]}'

As said, provisioning is easy. Now lets see how the instance got created. Using the IP Address that was assigned, the postgres user and the public ssh key we provided we can connect to the VM:

ssh postgres@10.38.11.40
The authenticity of host '10.38.11.40 (10.38.11.40)' can't be established.
ECDSA key fingerprint is SHA256:rN4QzdL2y55Lv8oALnW6pBQzBKOInP+X4obiJrqvK8o.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.38.11.40' (ECDSA) to the list of known hosts.
Last login: Fri Jan 17 18:48:26 2020 from 10.38.11.9

The first thing I would try is to connect to PostgreSQL:

-bash-4.2$ psql postgres
psql (11.6 dbi services build)
Type "help" for help.

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

postgres=#

Ok, that works. What I did before I created the Software profile in the last post: I installed our DMK and that gets started automatically as the required stuff is added to “.bash_profile” (you can see that in the last post because PS1 is not the default). But here I did not get our environment because Era has overwritten “.bash_profile”:

-bash-4.2$ cat .bash_profile
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/u01/app/postgres/product/11/db_6/bin:/u01/app/postgres/product/11/db_6/bin
export PGDATA=/pg_data_544474d1_822c_4912_8db4_7c1c7f45df54
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export ANSIBLE_LIBRARY=/opt/era_base/era_engine/drivers/nutanix_era/era_drivers/AnsibleModules/bin

Would be great if the original “.bash_profile” would still be there so custom adjustment are not lost.

Looking at mountpoinzs:

-bash-4.2$ df -h
Filesystem                                                                                                                          Size  Used Avail Use% Mounted on
devtmpfs                                                                                                                            7.8G     0  7.8G   0% /dev
tmpfs                                                                                                                               7.8G  8.0K  7.8G   1% /dev/shm
tmpfs                                                                                                                               7.8G  9.7M  7.8G   1% /run
tmpfs                                                                                                                               7.8G     0  7.8G   0% /sys/fs/cgroup
/dev/mapper/centos_centos7postgres12-root                                                                                            26G  2.4G   24G   9% /
/dev/sda1                                                                                                                          1014M  149M  866M  15% /boot
/dev/sdb                                                                                                                             27G   74M   26G   1% /u01/app/postgres/product/11/db_6
tmpfs                                                                                                                               1.6G     0  1.6G   0% /run/user/1000
/dev/mapper/VG_PG_DATA_544474d1_822c_4912_8db4_7c1c7f45df54-LV_PG_DATA_544474d1_822c_4912_8db4_7c1c7f45df54                          50G  108M   47G   1% /pg_data_544474d1_822c_4912_8db4_7c1c7f45df54
/dev/mapper/VG_PG_DATA_TBLSPC_544474d1_822c_4912_8db4_7c1c7f45df54-LV_PG_DATA_TBLSPC_544474d1_822c_4912_8db4_7c1c7f45df54           9.8G   44M  9.2G   1% /pg_dbidb1_544474d1_822c_4912_8db4_7c1c7f45df54
/dev/mapper/ntnx_era_agent_vg_1700f784395111eab53f506b8d241a39-ntnx_era_agent_lv_era_software_1700f784395111eab53f506b8d241a39      1.5G  534M  821M  40% /opt/era_base/era_engine
/dev/mapper/ntnx_era_agent_vg_1700f784395111eab53f506b8d241a39-ntnx_era_agent_lv_era_logs_1700f784395111eab53f506b8d241a39          3.9G   18M  3.6G   1% /opt/era_base/logs
/dev/mapper/ntnx_era_agent_vg_1700f784395111eab53f506b8d241a39-ntnx_era_agent_lv_era_config_1700f784395111eab53f506b8d241a39         47M  1.1M   42M   3% /opt/era_base/cfg
/dev/mapper/ntnx_era_agent_vg_5c3a7120261b4354a3b38ee168726298-ntnx_era_agent_lv_db_stagging_logs_5c3a7120261b4354a3b38ee168726298   99G   93M   94G   1% /opt/era_base/db_logs

The PostgreSQL binaries got their own mountpoint. PGDATA is on its own mountpoint and it seems we have a tablespace in “/pg_dbidb1_544474d1_822c_4912_8db4_7c1c7f45df54”:

-bash-4.2$ psql -c "\db" postgres
                            List of tablespaces
     Name      |  Owner   |                    Location
---------------+----------+-------------------------------------------------
 pg_default    | postgres |
 pg_global     | postgres |
 tblspc_dbidb1 | postgres | /pg_dbidb1_544474d1_822c_4912_8db4_7c1c7f45df54
(3 rows)

-bash-4.2$ psql -c "\l+" postgres
                                                                     List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   |  Tablespace   |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+---------------+--------------------------------------------
 dbidb1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7717 kB | tblspc_dbidb1 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7717 kB | pg_default    | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7577 kB | pg_default    | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |               |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7577 kB | pg_default    | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |               |
(4 rows)

This confirms that our database “dbidb1” was placed into a separate tablespace. There is another mountpoint which seems to be there for the archived WAL files, and we can confirm that as well:

-bash-4.2$ psql
psql (11.6 dbi services build)
Type "help" for help.

postgres=# show archive_command ;
                      archive_command
-----------------------------------------------------------
  sh /opt/era_base/cfg/postgres/archive_command.sh %p  %f
(1 row)

postgres=# \! cat /opt/era_base/cfg/postgres/archive_command.sh
test ! -f /opt/era_base/db_logs/dbidb1/$2 &&  cp -p $1 /opt/era_base/db_logs/dbidb1//$2
postgres=#

So archiving is enabled and this is what I expected. The costing parameters seem to be the default:

postgres=# select name,setting from pg_settings where name like '%cost%';
             name             | setting
------------------------------+---------
 autovacuum_vacuum_cost_delay | 20
 autovacuum_vacuum_cost_limit | -1
 cpu_index_tuple_cost         | 0.005
 cpu_operator_cost            | 0.0025
 cpu_tuple_cost               | 0.01
 jit_above_cost               | 100000
 jit_inline_above_cost        | 500000
 jit_optimize_above_cost      | 500000
 parallel_setup_cost          | 1000
 parallel_tuple_cost          | 0.1
 random_page_cost             | 4
 seq_page_cost                | 1
 vacuum_cost_delay            | 0
 vacuum_cost_limit            | 200
 vacuum_cost_page_dirty       | 20
 vacuum_cost_page_hit         | 1
 vacuum_cost_page_miss        | 10
(17 rows)

Memory parameters seem to be the default as well:

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

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

postgres=# show maintenance_work_mem ;
 maintenance_work_mem
----------------------
 64MB
(1 row)

postgres=# show effective_cache_size ;
 effective_cache_size
----------------------
 4GB
(1 row)

There are “Parameter profiles” you can use and create but currently there are not that many parameters that can be adjusted:

That’s it for now. Deploying new PostgreSQL instances is really easy. I am currently not sure why there needs to be a tablespace but maybe that becomes clear in a future post when we’ll look at backup and restore. From a PostgreSQL configuration perspective you for sure need to do some adjustments when the databases become bigger and load increases.

Cet article Deploying your own PostgreSQL image on Nutanix Era – 2 – Deploying a new PostgreSQL VM est apparu en premier sur Blog dbi services.

One question I was asking is if the standby_file_management parameter is relevant in a Dbvisit environment with Oracle Standard Edition. I did some tests and I show here what I did.
We suppose that the Dbvisit is already set and that the replication is fine

[oracle@dbvisit1 trace]$ /u01/app/dbvisit/standby/dbvctl -d dbstd -i
=============================================================
Dbvisit Standby Database Technology (9.0.08_0_g99a272b) (pid 19567)
dbvctl started on dbvisit1: Fri Jan 17 16:48:16 2020
=============================================================

Dbvisit Standby log gap report for dbstd at 202001171648:
-------------------------------------------------------------
Description       | SCN          | Timestamp
-------------------------------------------------------------
Source              2041731        2020-01-17:16:48:18 +01:00
Destination         2041718        2020-01-17:16:48:01 +01:00

Standby database time lag (DAYS-HH:MI:SS): +00:00:17

Report for Thread 1
-------------------
SOURCE
Current Sequence 8
Last Archived Sequence 7
Last Transferred Sequence 7
Last Transferred Timestamp 2020-01-17 16:48:07

DESTINATION
Recovery Sequence 8

Transfer Log Gap 0
Apply Log Gap 0

=============================================================
dbvctl ended on dbvisit1: Fri Jan 17 16:48:23 2020
=============================================================

[oracle@dbvisit1 trace]$

While the standby_file_management is set to MANUAL on both servers

[oracle@dbvisit1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 17 16:50:50 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> show parameter standby_file_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>


[oracle@dbvisit2 back_dbvisit]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 17 16:51:15 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>  show parameter standby_file_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>

Let’s create a tablespace MYTAB on the primary database

SQL> create tablespace mytab datafile '/u01/app/oracle/oradata/DBSTD/mytab01.dbf' size 10M;

Tablespace created.

SQL>

SQL> alter system switch logfile;

System altered.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
MYTAB

6 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBSTD/system01.dbf
/u01/app/oracle/oradata/DBSTD/sysaux01.dbf
/u01/app/oracle/oradata/DBSTD/undotbs01.dbf
/u01/app/oracle/oradata/DBSTD/mytab01.dbf
/u01/app/oracle/oradata/DBSTD/users01.dbf

SQL>

A few moment we can see that the new datafile is replicated on the standby

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
MYTAB

6 rows selected.

SQL>  select name from v$datafile
  2  ;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBSTD/system01.dbf
/u01/app/oracle/oradata/DBSTD/sysaux01.dbf
/u01/app/oracle/oradata/DBSTD/undotbs01.dbf
/u01/app/oracle/oradata/DBSTD/mytab01.dbf
/u01/app/oracle/oradata/DBSTD/users01.dbf

SQL>

Now let’s repeat the tablespace creation while the parameter is set to AUTO on both side

SQL> show parameter standby_file_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO
SQL>  create tablespace mytab2 datafile '/u01/app/oracle/oradata/DBSTD/mytab201.dbf' size 10M;

Tablespace created.

SQL>

SQL> alter system switch logfile;

System altered.

SQL>

A few moment later the tablespace mytab2 was also replicated on the standby

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
MYTAB
MYTAB2

7 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBSTD/system01.dbf
/u01/app/oracle/oradata/DBSTD/mytab201.dbf
/u01/app/oracle/oradata/DBSTD/sysaux01.dbf
/u01/app/oracle/oradata/DBSTD/undotbs01.dbf
/u01/app/oracle/oradata/DBSTD/mytab01.dbf
/u01/app/oracle/oradata/DBSTD/users01.dbf

6 rows selected.

In Dbvisit documentation we can find this
Note 2: This feature is independent of the Oracle parameter STANDBY_FILE_MANAGEMENT. Dbvisit Standby will detect if STANDBY_FILE_MANAGEMENT has added the datafile to the standby database, and if so, Dbvisit Standby will not add the datafile.
Note 3: STANDBY_FILE_MANAGEMENT can only be used in Enterprise Edition and should not be set in Standard Edition.

Dbvisit does not use STANDBY_FILE_MANAGEMENT for datafile replication. So I decide to set this value to its default value which is MANUAL.

What about adding tempfile in a dbvisit environment. In the primary I create a new temporary tablespace

SQL> show parameter standby_file_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/DBSTD/temp2_01.dbf' size 10M;

Tablespace created.

SQL> alter system switch logfile;

System altered.

SQL>

We can see on the primary that we now have two tempfiles.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
MYTAB
MYTAB2
TEMP2

8 rows selected.

SQL>  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBSTD/temp01.dbf
/u01/app/oracle/oradata/DBSTD/temp2_01.dbf

SQL>

On standby side, the new temporary tablespace was replicated.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
MYTAB
MYTAB2
TEMP2

8 rows selected.

But the new tempfile is not listed on the standby

SQL>  select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBSTD/temp01.dbf

SQL>

In fact it’s the expected behavior. In the documentation we can find following
If your preference is to have exactly the same number of temp files referenced in the standby control file as your current primary database, then once a new temp file has been added on the primary, you need to recreate a standby control file by running the following command from the primary server:
dbvctl -f create_standby_ctl -d DDC

So let’s recreate the standby control file

[oracle@dbvisit1 ~]$ /u01/app/dbvisit/standby/dbvctl -f create_standby_ctl -d dbstd
=>Replace current standby controfiles on dbvisit2 with new standby control
file?  [No]: yes

>>> Create standby control file... done

>>> Copy standby control file to dbvisit2... done

>>> Recreate standby control file... done

>>> Standby controfile(s) on dbvisit2 recreated. To complete please run dbvctl on the
    primary, then on the standby.
[oracle@dbvisit1 ~]$

And then after we can verify that the new tempfile is now visible at standby side

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBSTD/temp01.dbf
/u01/app/oracle/oradata/DBSTD/temp2_01.dbf

SQL>

Cet article Dbvisit 9: Adding datafiles and or tempfiles est apparu en premier sur Blog dbi services.

Some days ago we had a very good training on Nutanix. Nutanix is a Hyper-converged infrastructure and that means that all is software driven and the system can be deployed on many hardware configurations. I will not go into the details of the system itself but rather look at one component/module which is called Era. Era promises to simplify database deployments by providing a clean and simple user interface (and an API) that provides deployment procedures for PostgreSQL, MS SQL, MySQL, MariaDB and Oracle. There are predefined templates you can use but in this post I’ll look at how you can use Era to deploy your own PostgreSQL image.

Before you can register a software profile with Era there needs to be a VM up and running which already has PostgreSQL installed. For that I’ll import the latest CentOS 7 ISO with Prism (CentOS 8 is not yet supported).

Importing images is done in the “Images Configuration” section under “Settings” of Prism:

Once you start the upload a new task is generated which can be monitored in the tasks section:

Now that the image is ready we need to deploy a new virtual machine which will use the image as installation source:

As the virtual machine is now defined we need to power it on and then launch the console:

Follow your preferred way of doing the CentOS installation and once it is done you need to power off the virtual machine for removing the ISO. Otherwise you will always land in the installation procedure when the virtual machine is started:

After you powered of the virtual machine again you should be able to connect with ssh:

The next step is to install PostgreSQL as you prefer to do it. Here is an example for doing it from source code. We will not create a PostgreSQL instance, the binaries are enough. In my case everything was installed here:

 postgres@centos7postgres12:/home/postgres/ [pg121] echo $PGHOME
/u01/app/postgres/product/12/db_1/
postgres@centos7postgres12:/home/postgres/ [pg121] ls $PGHOME
bin  include  lib  share

Now that we have out PostgreSQL server we need to register the server in Era. Before doing that you should download and execute the pre-check script on the new database server:

postgres@centos7postgres12:/home/postgres/ [pg121] sudo ./era_linux_prechecks.sh

----------------------------------------------------------------------------------
   Error: Database type not specified
   Syntax: $ ./era_linux_prechecks.sh -t|--database_type  [-c|--cluster_ip ] [-p|--cluster_port] [-d|--detailed]
   Database type can be: oracle_database, postgres_database, mariadb_database, mysql_database
----------------------------------------------------------------------------------

postgres@centos7postgres12:/home/postgres/ [pg121] sudo ./era_linux_prechecks.sh -t postgres_database
libselinux-python-2.5-14.1.el7.x86_64


--------------------------------------------------------------------
|              Era Pre-requirements Validation Report              |
--------------------------------------------------------------------

     General Checks:
     ---------------
         1] Username           : root
         2] Package manager    : yum
         2] Database type      : postgres_database

     Era Configuration Dependencies:
     -------------------------------
         1] User has sudo access                         : YES
         2] User has sudo with NOPASS access             : YES
         3] Crontab configured for user                  : YES
         4] Secure paths configured in /etc/sudoers file : YES

     Era Software Dependencies:
     --------------------------
          1] GCC                  : N/A
          2] readline             : YES
          3] libselinux-python    : YES
          4] crontab              : YES
          5] lvcreate             : YES
          6] lvscan               : YES
          7] lvdisplay            : YES
          8] vgcreate             : YES
          9] vgscan               : YES
         10] vgdisplay            : YES
         11] pvcreate             : YES
         12] pvscan               : YES
         13] pvdisplay            : YES
         14] zip                  : NO
         15] unzip                : YES
         16] rsync                : NO

     Summary:
     --------
         This machine does not satisfy all of the dependencies required by Era.
         It can not be onboarded to Era unless all of these are satified.

     **WARNING: Cluster API was not provided. Couldn't go ahead with the Prism API connectivity check.
     Please ensure Prism APIs are callable from the host.
====================================================================
1postgres@centos7postgres12:/home/postgres/ [pg121]

In my case only “zip” and “rsync” are missing which of course is easy to fix:

postgres@centos7postgres12:/home/postgres/ [pg121] sudo yum install -y zip rsync
...
postgres@centos7postgres12:/home/postgres/ [pg121] sudo ./era_linux_prechecks.sh -t postgres_database
libselinux-python-2.5-14.1.el7.x86_64


--------------------------------------------------------------------
|              Era Pre-requirements Validation Report              |
--------------------------------------------------------------------

     General Checks:
     ---------------
         1] Username           : root
         2] Package manager    : yum
         2] Database type      : postgres_database

     Era Configuration Dependencies:
     -------------------------------
         1] User has sudo access                         : YES
         2] User has sudo with NOPASS access             : YES
         3] Crontab configured for user                  : YES
         4] Secure paths configured in /etc/sudoers file : YES

     Era Software Dependencies:
     --------------------------
          1] GCC                  : N/A
          2] readline             : YES
          3] libselinux-python    : YES
          4] crontab              : YES
          5] lvcreate             : YES
          6] lvscan               : YES
          7] lvdisplay            : YES
          8] vgcreate             : YES
          9] vgscan               : YES
         10] vgdisplay            : YES
         11] pvcreate             : YES
         12] pvscan               : YES
         13] pvdisplay            : YES
         14] zip                  : YES
         15] unzip                : YES
         16] rsync                : YES

     Summary:
     --------
         This machine satisfies dependencies required by Era, it can be onboarded.

Looks good and the database server can now be registered:

Era as well has a task list which can be monitored:

… and then it fails because PostgreSQL 12.1 is not supported. That is fine but I would have expected the pre-check script to tell me that. Same procedure again, this time with PostgreSQL 11.6 and that succeeds:

This database server is now the source for a new “Software profile”:

And that’s it: Our new PostgreSQL software profile is ready to use. In the next post we’ll try to deploy a new virtual machine from that profile.

Cet article Deploying your own PostgreSQL image on Nutanix Era est apparu en premier sur Blog dbi services.

Dbvisit snapshot option is a new feature available starting from version 9.0.06. I have tested this option and in this blog I am describing the tasks I have done.
The configuration I am using is following
dbvist1 : primary server
dbvist 2 : standby server
orcl : oracle 19c database
We suppose that the dbvisit environment is already set and the replication is going fine. See previous blog for setting up dbvisit standby
First there are two options
-Snapshot Groups
-Single Snapshots

Snapshot Groups
This option is ideal for companies that are using Oracle Standard Edition (SE,SE1,SE2) where they would like to have a “database that is read-only, but also being kept up to date” . It allows to create 2 or more read-only snapshots of the standby at a time interval. The snapshot will be opened in a read-only mode after its creation. A new service which will point to the latest snapshot is created and will be automatically managed by the listener.
The use of the feature is very easy. As it required managing filesystems like mount and umount, the user which will create the snapshot need some admin privileges.
In our case we have configured the user with full sudo privileges, but you can find all required privileges in the documentation .

oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)] sudo grep oracle /etc/sudoers
oracle ALL=(ALL) NOPASSWD:ALL
oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)]

The use of the option can be done by command line or using the graphical dbvserver console. But It is highly recommended to use the graphical tool.
When the option snapshot is available with your license, you will see following tab in the dbvserver console

To create a snapshot groups we just have to choose the option NEW SNAPSHOT GROUP

And then fill the required information. In this example
-We will create a service named snap_service
-We will generate a snapshot every 15 minutes (900 secondes)
There will be a maximum of 2 snapshots. When the limit is reached, the oldest snapshop is deleted. Just note that the latest snapshot will be deleted only when the new snapshot is successfully created. That’s why we can have sometimes 3 snapshots
-The snapshots will be prefixed by MySna

After the snapshots are created without errors, we have to start the snapshots generation by clicking on the green button

And then we can see the status

On OS level we can verify that the first snapshot is created and that the corresponding instance started

oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)] ps -ef | grep pmon
oracle    2300     1  0 09:05 ?        00:00:00 ora_pmon_orcl
oracle    6765     1  0 09:57 ?        00:00:00 ora_pmon_MySna001
oracle    7794  1892  0 10:00 pts/0    00:00:00 grep --color=auto pmon
oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)]

We can also verify that the service snap_service is registered in the listener. This service will be automatically pointed to the latest snapshot of the group.

oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)] lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2020 10:01:49

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbvisit2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-JAN-2020 09:05:07
Uptime                    0 days 0 hr. 56 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbvisit2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbvisit2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
…
…
…
Service "snap_service" has 1 instance(s).
  Instance "MySna001", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)]

To connect to this service, we just have to create an alias like

snapgroup =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvisit2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = snap_service)
    )
  )

15 minutes later we can see that a new snapshot was generated

oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)] ps -ef | grep pmon
oracle    2300     1  0 09:05 ?        00:00:00 ora_pmon_orcl
oracle    6765     1  0 09:57 ?        00:00:00 ora_pmon_MySna001
oracle   11355     1  0 10:11 ?        00:00:00 ora_pmon_MySna002
oracle   11866  1892  0 10:13 pts/0    00:00:00 grep --color=auto pmon
oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)]

Note that we can only open the snapshot in a read only mode

oracle@dbvisit1:/home/oracle/ [orcl (CDB$ROOT)] sqlplus sys/*****@snapgroup as sysdba

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL> alter pluggable database all open;
alter pluggable database all open
*
ERROR at line 1:
ORA-65054: Cannot open a pluggable database in the desired mode.


SQL>  alter pluggable database all open read only;

Pluggable database altered.

Of course you have probably seen that you can stop, start, pause and remove snapshots from the GUI.

Single Snapshot
This option allows to create read-only as well read-write snapshots for the database.
To create a single snapshot , we just have to choose the NEW SINGLE SNAPSHOT
In the following example the snapshot will be opened in a read write mode

At this end of the creation we can see the status

We can verify that a service SingleSn was also created

oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)] lsnrctl status | grep Singl
  Instance "SingleSn", status READY, has 1 handler(s) for this service...
  Instance "SingleSn", status READY, has 1 handler(s) for this service...
Service "SingleSn" has 1 instance(s).
  Instance "SingleSn", status READY, has 1 handler(s) for this service...
  Instance "SingleSn", status READY, has 1 handler(s) for this service...
  Instance "SingleSn", status READY, has 1 handler(s) for this service...
  Instance "SingleSn", status READY, has 1 handler(s) for this service

And that the instance SinglSn is started

oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)] ps -ef | grep pmon
oracle    3294     1  0 16:04 ?        00:00:00 ora_pmon_SingleSn
oracle    3966  1748  0 16:08 pts/0    00:00:00 grep --color=auto pmon
oracle   14349     1  0 13:57 ?        00:00:00 ora_pmon_orcl
oracle@dbvisit2:/home/oracle/ [orcl (CDB$ROOT)]

We can also remark that at OS level, a filesystem is mounted for the snap. So there must be enough free space at the LVM that host the database.

oracle@dbvisit2:/home/oracle/ [MySna004 (CDB$ROOT)] df -h | grep snap
/dev/mapper/ora_data-SingleSn   25G   18G  6.3G  74% /u01/app/dbvisit/standby/snap/orcl/SingleSn
oracle@dbvisit2:/home/oracle/ [MySna004 (CDB$ROOT)]

Using the alias

singlesnap =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbvisit2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SingleSn)
    )
  )

We can see that new snapshot is opened in read write mode

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
SINGLESN  READ WRITE

SQL>

What we will retain is that a snapshot groups is a group of snapshots taken at a regular time interval. These kinds of snapshots can only be opened in a read only mode.
The single snapshot can be created in a read only or read write mode. When opened in read write mode, it can be compared maybe to Oracle snapshot standby.
The option dbvisit snapshot required a license and is only supported on linux.

Cet article Dbvisit Standby 9 : Do you know the new snapshot feature? est apparu en premier sur Blog dbi services.

The ODC SQL and PL/SQL forum came up with an example a couple of days ago that highlighted an annoying limitation in the optimizer’s handling of table functions. The requirement was for a piece of SQL that would generate “installments” information from a table of contract agreements and insert into another table any installments that were not yet recorded there.

The mechanism to turn a single row of contract data into a set of installments was a (optionally pipelined) table function that involved some business logic that (presumably) dealt with the timing and size of the installments. The final SQL to create the data that needed to be inserted was reported as follows (though it had clearly been somewhat modified):

select 
        y.*,
        trunc(sysdate,'mm'),
        user
from 
        table_a a
join 
        table(
                my_function(
                        a.loan_acct_nbr, 
                        a.start_dt,
                        a.maturity_dt,
                        a.num_of_terms
                )
        ) y
on 
        a.loan_acct_nbr = y.loan_acct_nbr
where 
        a.yr_mnth_cd = to_char(add_months(sysdate,-1),'yyyymm')       -- last month
and     a.loan_typ   = 'ABC'
and     not exists ( 
                select  1 
                from
                        final_load_table l
                where
                        l.loan_acct_nbr = y.loan_acct_nbr
                and     l.yr_mnth_cd    = y.yr_mnth_cd 
        )
;

In this query, table_a is the set of contracts, final_load_table is the set of installments, and my_function() is the pipelined function returning a table of installments derived from the start date, maturity date, and number of installments for a contract. The query needs a “not exists” subquery to eliminate any installments that are already known to the database. Once this query is operating efficiently it could be used either to drive a PL/SQL loop or (generally the better strategy) to do a simple “insert as select”.

We were told that the function would return around 60 rows per contract; that the starting size of the final_load_table would be about 60M rows, and the size of the result set from the initial join would be about 60M or 70M rows (which suggests about 1M rows acquired from table_a).

The owner of this problem seemed to be saying that the query had returned no data after 4 or 5 hours – which suggests (a) the optimizer chose a bad plan and/or (b) the PL/SQL function is working very inefficiently and/or (c) the join had generated a vast amount of data but the effect of the subquery was to discard all of it .

Clearly we needed to see an execution plan (preferably from SQL Monitor) and be given have some idea of how much of the 60M/70M rows predicted for the join would be discarded because it already existed.

The owner did send us an execution plan – which included a very obvious threat and suggested a couple of optimizer errors – but supplied it as a picture rather than a flat text.

You’ll notice, of course, that the tables and columns have changed from the original statement. More significantly, though, there are serious problems with the numbers – the estimated row count for the basic join is only 5,326 rather than 50 Million which, at first sight, is probably why the optimizer has decided that a filter subquery (operation 1) to do an index-only probe (operation 5) is a good way of handling the subquery. Perhaps if the estimates had been a little more accurate (e.g. through the addition of a couple of column groups or, if necessary, by opt_estimate() or cardinality() hints) the subquery would have been unnested and turned into a hash anti-join.

I suggested a quick test of a suitable cardinality() hint – but ran up a little model to check that I’d got the hint right – and found that I had but it wasn’t going to help. So I decided to write up the model (and a possible solution for the owner of the problem) in this blog note.

Here’s the code to create the set of objects I’m going to work with. The naming follows the naming in the original statement of the problem suggested by the owner:

rem
rem     Script:         table_function_plan.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2020
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem

create table table_a (
        loan_acct_nbr   number,
        yr_mnth_cd      varchar2(6),
        start_dt        date,
        maturity_dt     date    ,
        number_of_terms number,
        constraint ta_pk primary key(loan_acct_nbr, yr_mnth_cd)
)
;

execute dbms_random.seed(0)

insert /*+ append */
into    table_a
with generator as (
        select rownum id
        from    dual
        connect by
                level <= 4000 -- > comment to avoid wordpress issue
)
select
        trunc(dbms_random.value(1e9, 2e9)),
        to_char(sysdate-(365-mod(rownum,365)),'yyyymm'),
        sysdate-(365-mod(rownum,365)),
        sysdate+(1500+mod(rownum,365)),
        60
from
        generator       v1,
        generator       v2
where
        rownum <= 1e4 -- > comment to avoid WordPress format issue
;

create table final_load_table_l(
        loan_acct_nbr   number,
        yr_mnth_cd      varchar2(6),
        v1              varchar2(10),
        padding         varchar2(200),
        constraint lt_pk primary key(loan_acct_nbr, yr_mnth_cd)
)
;

insert /*+ append */ into final_load_table_l
with generator as (
        select rownum id
        from    dual
        connect by
                level <= 4000 -- > comment to avoid wordpress issue
)
select
        trunc(dbms_random.value(1e9, 2e9)),
        to_char(sysdate-(365-mod(rownum,365)),'yyyymm'),
        lpad(rownum,10),
        lpad('x',200,'x')
from
        generator       v1,
        generator       v2
where
        rownum <= 1e4 -- > comment to avoid WordPress format issue
;


begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'table_a',
                method_opt  => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'final_load_table_l',
                method_opt  => 'for all columns size 1'
        );
end;
/

create type my_row_type as object (
        loan_acct_nbr   number,
        yr_mnth_cd      varchar2(6),
        v1              varchar2(10),
        padding         varchar2(200)
);
/

create type my_table_type as table of my_row_type;
/

create  or replace function my_function (
        i_loan_acct_nbr         in      number,
        i_yr_mnth_cd            in      varchar2,
        i_start_dt              in      date,
        i_maturity_dt           in      date,
        i_number_of_terms       in      number
)       return  my_table_type pipelined
as
begin
        for i in 1..i_number_of_terms loop
                pipe row (
                        my_row_type(
                                i_loan_acct_nbr,
                                to_char(i_start_dt+32*i,'yyyymm'),
                                i,
                                lpad('x',200,'x')
                        )
                );
        end loop;
        return;
end;
/

I was planning to create some large tables – hence the option to generate 16M rows from my generator CTEs – but I discovered the critical problem almost as soon as I had some data and code in place, so I didn’t need to go large.

I’ve had to create an object type and table type in order to create a pipelined function that returns the table type by piping rows of the object type. The data I’ve created, and the way the function generates data probably doesn’t bear much resemblance to the real system of course, but I don’t think it needs to be very close to make the crucial point.

Here’s the text of the select statement the OP wants to run, with the execution plan I got from my data set after running the query and pulling the plan from memory:

alter session set statistics_level = all;

select 
        /*+ find this 1 */
        y.* 
from 
        table_a a, 
        table(my_function(
                a.loan_acct_nbr,
                a.yr_mnth_cd,
                a.start_dt,
                a.maturity_dt,
                a.number_of_terms
        )) y
where 
        a.yr_mnth_cd = '202001'
and     not exists (
                select  
                        /*+ unnest */
                        null
                from    final_load_table_l l
                where   l.loan_acct_nbr = y.loan_acct_nbr
                and     l.yr_mnth_cd    = y.yr_mnth_cd
        )
;

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |  5059K(100)|  14580 |00:00:00.15 |   16330 |
|*  1 |  FILTER                             |             |      1 |        |            |  14580 |00:00:00.15 |   16330 |
|   2 |   NESTED LOOPS                      |             |      1 |   6283K| 10421   (8)|  14580 |00:00:00.11 |     335 |
|*  3 |    TABLE ACCESS FULL                | TABLE_A     |      1 |    769 |    10  (10)|    243 |00:00:00.01 |     297 |
|   4 |    COLLECTION ITERATOR PICKLER FETCH| MY_FUNCTION |    243 |   8168 |    14   (8)|  14580 |00:00:00.10 |      38 |
|*  5 |   INDEX UNIQUE SCAN                 | LT_PK       |  14580 |      1 |     1   (0)|      0 |00:00:00.02 |   15995 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NULL)
   3 - filter("A"."YR_MNTH_CD"='202001')
   5 - access("L"."LOAN_ACCT_NBR"=:B1 AND "L"."YR_MNTH_CD"=:B2)

I’ve put in a hint to tell the optimizer to unnest the subquery – and it didn’t. Oracle does not ignore hints (unless they’re illegal, or out of context, or the optimizer never got to them, or you’ve found a bug) so why did Oracle appear to ignore this hint? There’s a really nice feature in 19.3 execution plans – you can request a hint report for a plan, and here’s the hint report for this query (ignore the bits about “find this” being an error):

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1), E - Syntax error (2))
---------------------------------------------------------------------------
   0 -  SEL$1
         E -  find
         E -  this

   5 -  SEL$3
         U -  unnest / Invalid correlated predicates

I put in an /*+ unnest */ hint to unnest the subquery, and I’ve been told that the predicates are not valid. The only thing about them that could be invalid is that they come from a pipelined function that has returned an object type. The pipelined function does not behave exactly like a table. But this gives me a clue about forcing the unnest to happen – hide the pipelined function inside a non-mergeable view.

select
        /*+ find this 2 */
        v.*
from    (
        select  /*+ no_merge */
                y.* 
        from 
                table_a a, 
                table(my_function(
                        a.loan_acct_nbr,
                        a.yr_mnth_cd,
                        a.start_dt,
                        a.maturity_dt,
                        a.number_of_terms
                )) y
        where 
                a.yr_mnth_cd = '202001'
        )       v
where   not exists (
                select
                        /*+ unnest */
                        null
                from    final_load_table_l l
                where   l.loan_acct_nbr = v.loan_acct_nbr
                and     l.yr_mnth_cd    = v.yr_mnth_cd
        )
/


------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |        | 10628 (100)|  14580 |00:00:00.12 |     387 |       |       |          |
|*  1 |  HASH JOIN RIGHT ANTI                |             |      1 |   6283K| 10628  (10)|  14580 |00:00:00.12 |     387 |  1878K|  1878K| 2156K (0)|
|   2 |   INDEX FAST FULL SCAN               | LT_PK       |      1 |  10000 |     6  (17)|  10000 |00:00:00.01 |      91 |       |       |          |
|   3 |   VIEW                               |             |      1 |   6283K| 10371   (8)|  14580 |00:00:00.11 |     296 |       |       |          |
|   4 |    NESTED LOOPS                      |             |      1 |   6283K| 10371   (8)|  14580 |00:00:00.10 |     296 |       |       |          |
|*  5 |     TABLE ACCESS FULL                | TABLE_A     |      1 |    769 |    10  (10)|    243 |00:00:00.01 |     296 |       |       |          |
|   6 |     COLLECTION ITERATOR PICKLER FETCH| MY_FUNCTION |    243 |   8168 |    13   (0)|  14580 |00:00:00.10 |       0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("L"."LOAN_ACCT_NBR"="V"."LOAN_ACCT_NBR" AND "L"."YR_MNTH_CD"="V"."YR_MNTH_CD")
   5 - filter("A"."YR_MNTH_CD"='202001')

I’ve wrapped the basic join between table_a and the table function in an inline view called (unimaginatively) v, and I’ve added a /*+ no_merge */ hint to that inline view. So the main query becomes a select from a non-mergeable view with a “not exists” subquery applied to a couple of view columns. And Oracle thinks that that’s okay – and my unnest hint encouraged the optimizer to use a hash anti-join.

So here’s an option for the owner of the problem – but with a few outstanding questions: will a rewrite of their query in this form do the same, will the performance of the select be good enough, and will the subsequent “insert as select” keep the same driving plan.

There’s one significant detail to worry about: the build table in this hash (anti-)join is going to be constructed from 50M (load_acct_bfr, yr_mnth_cd) pairs – which means the workarea requirement is likely to be about 1.2GB for an optimial (i.e. in-memory) hash join; otherwise the join may spill to disc and do a lot of I/O – probably as a one-pass hash join.

(Did you notice,by the way, that the word storage appeared at operation 3 in the original plan?  That suggests a nice big Exadata box; however, there’s no storage predicate in the Predicate Information section for that operation and you would have thought that lease_type = ‘US_SSP’ would be pushed to storage, so maybe this is a ZFS Pillar backing a less exotic server.)

Some (if not all) types of correlated subqueries behave badly if the correlation predicates involve table functions. But you may be able to work around the issue by hiding part of the query, including the table function, inside a non-mergeable inline view before applying the subquery to the view.

When I realised that the table function was causing a problem unnesting I remembered that I had written about a similar problem a few years ago – after searching for a little while I discovered a draft note that I had started in September 2015 but had not quite finished; so I’ll be finishing it off and publishing it some time in the next few days.

What is the difference between the well known sqlplus-setting arraysize and the new sqlplus-12.2.-feature rowprefetch? In Blog
https://blog.dbi-services.com/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch/ I showed a case, which helps to reduce the logical IOs when using rowprefetch.

arraysize:

SET System Variable Summary: Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency

About SQL*Plus Script Tuning: The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.

REMARK: The arraysize setting also has an impact on the COPY-command with the COPYCOMMIT-setting (commits every n arraysize batches of records).

rowprefetch:

SET System Variable Summary: Sets the number of rows that SQL*Plus will prefetch from the database at one time. The default value is 1 (max is 32767).
Note: The amount of data contained in the prefetched rows should not exceed the maximum value of 2147483648 bytes (2 Gigabytes). The setting in the oraaccess.xml file can override the SET ROWPREFETCH setting in SQL*Plus.

When doing my tests one of the important differences between ARRAYSIZE and ROWPREFETCH is that ROWPREFETCH allows Oracle to transfer query results on return from its internal OCI execute call. I.e. in a 10046-trace the first FETCH is showing ROWPREFETCH rows fetched regardless of the ARRAYSIZE setting. E.g. with the default setting of ROWPREFETCH 1, ARRAYSIZE 15 I can see the following number of rows fetched (see the r= in the trace):

FETCH #139623638001936:c=448,e=1120,p=0,cr=8,cu=0,mis=0,r=1,dep=0,og=1,plh=3403427028,tim=110487525476
...
FETCH #139623638001936:c=66,e=66,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3403427028,tim=110487525830
...
FETCH #139623638001936:c=15,e=15,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=3403427028,tim=110487526093
...

I.e. 1, 15, 15,…

With ROWPREFETCH 3, ARRAYSIZE 15 the rows fetched are 3, 15, 15, …

The following table shows the number of rows fetched with different settings of ROWPREFETCH and ARRAYSIZE from a query, which returns 70 rows:

ROWPREFETCH ARRAYSIZE ROWS_FETCH1 ROWS_FETCH2 ROWS_FETCH3 ROWS_FETCH4 ROWS_FETCH5 ROWS_FETCH6 ROWS_FETCH7 ROWS_FETCH8 
 1          15         1          15          15          15          15          9
 2          15         2          15          15          15          15          8
20          15        20          30          20
16          15        16          30          24
 6           5         6          10          10          10          10          10          10          4
 9           5         9          10          10          10          10          10          10          1
10          10        10          20          20          20          0
10           5        10          15          15          15          15          0
16           3        16          18          18          18          0

We can see 3 things here:
- The first FETCH (from the internal OCI execute) contains always the number of rows as defined in the ROWPREFETCH setting
- The second FETCH (and all subsequent fetches) contains a multiple of the ARRAYSIZE setting rows. The following code fragment should show the logic:

2nd_Fetch_Rows = if ROWPREFETCH < ARRAYSIZE 
                 then ARRAYSIZE 
                 else (TRUNC(ROWPREFETCH/ARRAYSIZE)+1)*ARRAYSIZE

- If a fetch does not detect the end of the data in the cursor then an additional fetch is necessary. In 3 cases above a last fetch fetched 0 rows.

With the Linux pmap command I checked how much memory the client requires for different ROWPREFETCH and ARRAYSIZE settings.

Testcase:

SQL> connect cbleile/cbleile@orclpdb1
Connected.
SQL> create table big_type (a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000), e varchar2(2000));
 
Table created.
 
SQL> insert into big_type select 
  2  rpad('X',2000,'Y'),
  3  rpad('X',2000,'Y'),
  4  rpad('X',2000,'Y'),
  5  rpad('X',2000,'Y'),
  6  rpad('X',2000,'Y') from xmltable('1 to 4100');
 
4100 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TYPE');
SQL> select avg_row_len from tabs where table_name='BIG_TYPE';
 
AVG_ROW_LEN
-----------
      10005

Before the test:

oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] ps -ef | grep sqlplus
oracle    31537  31636  3 17:49 pts/2    00:01:20 sqlplus   as sysdba
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000   1580K rw---   [ anon ]
00007f7efcda6000    516K rw---   [ anon ]
...
 
SQL> show rowprefetch arraysize
rowprefetch 1
arraysize 15
SQL> set arraysize 1000 pages 2 pause on lines 20000
SQL> select * from big_type;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000   1580K rw---   [ anon ]
00007f7efc40f000  10336K rw---   [ anon ]
...
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000   1580K rw---   [ anon ]
00007f7efcda6000    516K rw---   [ anon ]
 
SQL> set arraysize 1
SQL> set rowprefetch 1000
SQL> select * from big_type;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000  12664K rw---   [ anon ]
00007f7efcda6000    516K rw---   [ anon ]
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000  12664K rw---   [ anon ]
00007f7efcda6000    516K rw---   [ anon ]
 
SQL> set rowprefetc 1
SQL> set arraysize 1000
SQL> select * from big_type;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000  22472K rw---   [ anon ]
00007f7efcda6000    516K rw---   [ anon ]
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000  12660K rw---   [ anon ]
00007f7efcda6000    516K rw---   [ anon ]
 
SQL> set rowprefetch 501 arraysize 500 pages 502
SQL> select * from big_type;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 31537 | grep anon
0000000000be6000  17568K rw---   [ anon ]
00007f7efcda6000    516K rw---   [ anon ]

New table with just 1 Byte per column:

SQL> create table big_type_small_data as select * from big_type where 1=2;
 
Table created.
 
SQL> insert into  big_type_small_data select 'X','X','X','X','X' from big_type;
 
4100 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats(user,'BIG_TYPE_SMALL_DATA');
 
PL/SQL procedure successfully completed.
 
SQL> select avg_row_len from tabs where table_name='BIG_TYPE_SMALL_DATA';
 
AVG_ROW_LEN
-----------
	 10
 
Client-Memory before the test:
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 14193 | grep anon
00000000014d1000   1580K rw---   [ anon ]
00007f3c3b8d2000    516K rw---   [ anon ]
 
SQL> show rowprefetch
rowprefetch 1
SQL> show array
arraysize 15
SQL> set arraysize 1000 rowprefetch 1 pages 2 pause on lines 20000
SQL> select * from big_type_small_table;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 14193 | grep anon
00000000014d1000   1580K rw---   [ anon ]
00007f3c3af3b000  10336K rw---   [ anon ]
 
--> 9.6MB allocated. 
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 14193 | grep anon
00000000014d1000   1580K rw---   [ anon ]
00007f3c3b8d2000    516K rw---   [ anon ]
 
--> All memory released.
 
SQL> set arraysize 1 rowprefetch 1000
SQL> select * from big_type_snall_data;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 14193 | grep anon
00000000014d1000   1852K rw---   [ anon ]
00007f3c3b8d2000    516K rw---   [ anon ]
 
--> Only 272K allocated.
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 15227 | grep anon
0000000001fba000   1852K rw---   [ anon ]
00007f38c5ef9000    516K rw---   [ anon ]
 
--> Memory not released.
 
Back to previous setting:
SQL> set arraysize 1000 rowprefetch 1
SQL> select * from big_type_small_data;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 15227 | grep anon
0000000001fba000  11644K rw---   [ anon ]
00007f38c5ef9000    516K rw---   [ anon ]
 
--> 9.6MB addtl memory allocated.
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 15227 | grep anon
0000000001fba000   1832K rw---   [ anon ]
00007f38c5ef9000    516K rw---   [ anon ]
 
--> Memory released, but not to the initial value. I.e. it seems the memory for the rowprefetch is still allocated.
 
Back to the settings with rowprefetch:
 
SQL> set arraysize 1 rowprefetch 1000
SQL> select * from big_type_small_data;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 15227 | grep anon
0000000001fba000   1832K rw---   [ anon ]
00007f38c5ef9000    516K rw---   [ anon ]
 
--> It obviously reused the previous memory.
 
SQL> set arraysize 500 rowprefetch 501 pages 503
SQL> select * from big_type_small_data;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 15227 | grep anon
0000000001fba000   6752K rw---   [ anon ]
00007f38c5ef9000    516K rw---   [ anon ]
 
--> Relogin
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 16334 | grep anon
00000000010b0000   1580K rw---   [ anon ]
00007ff8cc272000    516K rw---   [ anon ]
 
SQL> set arraysize 500 rowprefetch 501 pages 503 pause on lines 20000
SQL> select * from big_type_small_data;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 16334 | grep anon
00000000010b0000   1720K rw---   [ anon ]
00007ff8cbda4000   5436K rw---   [ anon ]
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 16334 | grep anon
00000000010b0000   1720K rw---   [ anon ]
00007ff8cc272000    516K rw---   [ anon ]
 
SQL> select * from big_type_small_data;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 16334 | grep anon
00000000010b0000   6608K rw---   [ anon ]
00007ff8cc272000    516K rw---   [ anon ]
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 16334 | grep anon
00000000010b0000   6608K rw---   [ anon ]
00007ff8cc272000    516K rw---   [ anon ]
 
--> This time the memory for the arraysize has not been released.
 
--> Relogin
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 17005 | grep anon
0000000001c40000   1580K rw---   [ anon ]
00007f90ea747000    516K rw---   [ anon ]
 
SQL> set arraysize 1 rowprefetch 32767 pages 3 pause on lines 20000
SQL> select * from big_type_small_data;
 
--> 2 times <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 17005 | grep anon
0000000001c40000   8312K rw---   [ anon ]
00007f90ea6a2000   1176K rw---   [ anon ]
 
Ctrl-C <RETURN>
 
oracle@oracle-19c-vagrant:/home/oracle/Blogs/rowprefetch_test/ [ORCLCDB (CDB$ROOT)] pmap 17005 | grep anon
0000000001c40000   8308K rw---   [ anon ]
00007f90ea6a2000   1176K rw---   [ anon ]
 
--> almost nothing released.

So the tests showed that ARRAYSIZE allocates more memory than ROWPREFETCH (i.e. it allocates according the data-type-size and not according the real data in the column), but in contrast to ROWPREFETCH memory is (often) released with ARRAYSIZE once the SQL finished fetching.

So when should ROWPREFETCH and ARRAYSIZE be used? As with all fetch-size-settings (e.g. for the JDBC-driver), both can be used to reduce the number of network roundtrips and logical IOs on the DB when lots of data has to be transferred between the server and the client. According my tests ROWPREFETCH requires less memory on the client, but does not release the memory after the query has finished. ARRAYSIZE requires more memory, but often releases memory when the query has finished. ROWPREFETCH = 2 is very useful in case only 1 row is returned by a query, because it returns the row with the internal OCI execute call (first fetch in the 10046 trace) and does not require a subsequent fetch to realize that all data has been fetched already. I.e. it saves 1 network roundtrip.

A good compromise is the use of

ROWPREFETCH = 2
ARRAYSIZE = 100

That setting is actually also used when starting sqlplus with -F(AST). If lots of data has to be transferred to the client then higher ROWPREFETCH or ARRAYSIZE settings can be used to reduce the number logical IOs and network roundtrips. But the best setting also depends on the data to transfer per row and client memory requirements may vary with higher ROWPREFETCH or ARRAYSIZE settings if sqlplus runs a batch-job with many queries or only a few queries. As usual, the best setting when transferring lots of data through sqlplus has to be found by testing the queries and scripts of your environment with different settings.

Cet article ARRAYSIZE or ROWPREFETCH in sqlplus? est apparu en premier sur Blog dbi services.

Here at JSMpros, we are super excited to share our new YouTube channel with you: PeopleTools Sound Bytes! Each week we will release a new "Sound Byte" describing a PeopleTools feature, tip, or technique. Some of our sound bytes come from our prerecorded on-demand training. Others are exclusively recorded for our Sound Bytes YouTube channel. For example, we are currently producing a channel-specific series titled, "PeopleTools 8.58 features you can implement today," highlighting new features that are part of 8.58 that you can implement as early as PeopleTools 8.54. Subscribe now so you don't miss an episode!

Do you have a topic you would like us discuss? If so, share your idea with us at https://soundbytes.jsmpros.com/

During my time as a Consultant working on Tuning Tasks I had the feeling that many people think that there is an Oracle-parameter “FAST=TRUE” to speed up the performance and throughput of the database calls. Unfortunately such a parameter is not available, but since version 12cR2 Oracle provided the option “-F” or “-FAST” for sqlplus, which looks like a “FAST=TRUE”-setting. Here an excerpt from the documentation:

The FAST option improves general performance. This command line option changes the values of the following default settings:
 
- ARRAYSIZE = 100
- LOBPREFETCH = 16384
- PAGESIZE = 50000
- ROWPREFETCH = 2
- STATEMENTCACHE = 20

I was interested in where the rowprefetch-setting could result in an improvement.

The documentation about rowprefetch is as follows:

SET ROWPREFETCH {1 | n}
 
Sets the number of rows that SQL*Plus will prefetch from the database at one time. The default value is 1.
 
Example
 
To set the number of prefetched rows to 200, enter
 
SET ROWPREFETCH 200
 
If you do not specify a value for n, the default is 1 row. This means that rowprefetching is off.
 
Note: The amount of data contained in the prefetched rows should not exceed the maximum value of 2147483648 bytes (2 Gigabytes). The  setting in the oraaccess.xml file can override the SET ROWPREFETCH setting in SQL*Plus. For more information about oraaccess.xml, see the Oracle Call Interface Programmer's Guide. 

A simple test where rowprefetch can make a difference is the use of hash clusters (see the Buffers column in the execution plan below). E.g.

SQL> create cluster DEMO_CLUSTER(CUST_ID number) size 4096 single table hashkeys 1000 ;
 
Cluster created.
 
SQL> create table DEMO cluster DEMO_CLUSTER(CUST_ID) as select * from CUSTOMERS;
 
Table created.
 
SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
 
PL/SQL procedure successfully completed.
 
SQL> select num_rows,blocks from user_tables where table_name='DEMO';
 
  NUM_ROWS     BLOCKS
---------- ----------
     55500	 1035
 
SQL> show rowprefetch
rowprefetch 1
SQL> select /*+ gather_plan_statistics */ rowid,cust_id from DEMO where cust_id=101;
 
ROWID		      CUST_ID
------------------ ----------
AAAR4qAAMAAAAedAAA	  101
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID	9g2nyr9h2ytk4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ rowid,cust_id from DEMO where
cust_id=101
 
Plan hash value: 3286081706
 
------------------------------------------------------------------------------------
| Id  | Operation	  | Name | Starts | E-Rows | A-Rows |	A-Time	 | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	1 |	   |	  1 |00:00:00.01 |	 2 |
|*  1 |  TABLE ACCESS HASH| DEMO |	1 |	 1 |	  1 |00:00:00.01 |	 2 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("CUST_ID"=101)
 
SQL> set rowprefetch 2
SQL> select /*+ gather_plan_statistics */ rowid,cust_id from DEMO where cust_id=101;
 
ROWID		      CUST_ID
------------------ ----------
AAAR4qAAMAAAAedAAA	  101
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID	9g2nyr9h2ytk4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ rowid,cust_id from DEMO where
cust_id=101
 
Plan hash value: 3286081706
 
------------------------------------------------------------------------------------
| Id  | Operation	  | Name | Starts | E-Rows | A-Rows |	A-Time	 | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	1 |	   |	  1 |00:00:00.01 |	 1 |
|*  1 |  TABLE ACCESS HASH| DEMO |	1 |	 1 |	  1 |00:00:00.01 |	 1 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("CUST_ID"=101)

Due to the prefetch of 2 rows Oracle detects that there actually is only 1 row and avoids the second logical IO (a second fetch).
If cust_id is unique then I would have created a unique (or primary) key constraint here, which would avoid a second fetch as well (because Oracle knows from the constraint that there can be max 1 row per cust_id), but in that case I have to maintain the created index.

I made a couple of tests, which compared the behaviour with different settings of rowprefetch and arraysize in sqlplus (what is actually the difference between the 2 settings?). That will be a subject of a future Blog.

Cet article Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch est apparu en premier sur Blog dbi services.

Much as I like WordPress, I’m moving all of this stuff over to:

https://mattypenny.github.io/

Recently, I was doing some sanity checks on a Documentum Server and I saw a Java exception stack while logging in using iAPI/iDQL to a Repository. It was reproducible for all Repositories. I’ve never seen something like that before (or at least I don’t remember it) so I was a little bit surprised. Whenever there are errors upon login, it is usually Documentum error messages that are printed and there is no exception stack. Since it took me some efforts finding the root cause, I thought about sharing it.

The exception stack displayed was the following one:

[dmadmin@cs-0 ~]$ echo "quit" | iapi gr_repo -Udmadmin -Pxxx

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

Connecting to Server using docbase gr_repo
DfException:: THREAD: main; MSG: [DM_SESSION_I_SESSION_START]info:  "Session 0112d687800c9214 started for user dmadmin."; ERRORCODE: 100; NEXT: null
        at com.documentum.fc.client.impl.docbase.DocbaseExceptionMapper.newException(DocbaseExceptionMapper.java:57)
        at com.documentum.fc.client.impl.connection.docbase.MessageEntry.getException(MessageEntry.java:39)
        at com.documentum.fc.client.impl.connection.docbase.DocbaseMessageManager.getExceptionForAllMessages(DocbaseMessageManager.java:176)
        at com.documentum.fc.client.impl.connection.docbase.DocbaseConnection.getExceptionForAllMessages(DocbaseConnection.java:1518)
        at com.documentum.fc.client.impl.session.Session.getExceptionsForAllMessages(Session.java:1603)
        at com.documentum.fc.client.impl.session.SessionHandle.getExceptionsForAllMessages(SessionHandle.java:1301)
        at com.documentum.dmcl.impl.ApiContext.addMessages(ApiContext.java:423)
        at com.documentum.dmcl.impl.ApiContext.collectExceptionsForReporting(ApiContext.java:370)
        at com.documentum.dmcl.impl.GetMessageHandler.get(GetMessageHandler.java:23)
        at com.documentum.dmcl.impl.DmclApi.get(DmclApi.java:49)
        at com.documentum.dmcl.impl.DmclApiNativeAdapter.get(DmclApiNativeAdapter.java:145)
        at com.documentum.dmcl.impl.DmclApiNativeAdapter.get(DmclApiNativeAdapter.java:130)


Connected to Documentum Server running Release 7.3.0050.0039  Linux64.Oracle
Session id is s0
API> Bye
[dmadmin@cs-0 ~]$

The login was successful but still, a strange exception stack appeared. The first thing I did was checking the Repository log file but there was nothing out of the ordinary inside it except for one thing:

[dmadmin@cs-0 ~]$ cd $DOCUMENTUM/dba/log
[dmadmin@cs-0 log]$
[dmadmin@cs-0 log]$ grep -A3 "Agent Exec" gr_repo.log
Wed Sep 11 10:38:29 2019 [INFORMATION] [AGENTEXEC 1477] Detected during program initialization: Agent Exec connected to server gr_repo:  DfException:: THREAD: main; MSG: [DM_SESSION_I_SESSION_START]info:  "Session 0112d687800c8904 started for user dmadmin."; ERRORCODE: 100; NEXT: null
        at com.documentum.fc.client.impl.docbase.DocbaseExceptionMapper.newException(DocbaseExceptionMapper.java:57)
        at com.documentum.fc.client.impl.connection.docbase.MessageEntry.getException(MessageEntry.java:39)
        at com.documentum.fc.client.impl.connection.docbase.DocbaseMessageManager.getExceptionForAllMessages(DocbaseMessageManager.java:176)
        at com.documentu
[dmadmin@cs-0 log]$

While starting, the Agent Exec was therefore facing the same behavior with the exact same stack (which is cut at the 4th line but it’s the same stack until then so it’s safe to assume it’s the same). Therefore, to dig deeper and to find when the issue started exactly, I checked the logs from the agentexec/jobs since this will be kept until cleanup from the log purge and since it does login to the Repository:

[dmadmin@cs-0 log]$ cd $DOCUMENTUM/dba/log/gr_repo/agentexec
[dmadmin@cs-0 agentexec]$
[dmadmin@cs-0 agentexec]$ # Check the last file
[dmadmin@cs-0 agentexec]$ cat $(ls -tr job_* | tail -1)
Wed Sep 11 18:00:21 2019 [INFORMATION] [LAUNCHER 3184] Detected while preparing job dm_ConsistencyChecker for execution: Agent Exec connected to server gr_repo:  DfException:: THREAD: main; MSG: [DM_SESSION_I_SESSION_START]info:  "Session 0112d687800c8974 started for user dmadmin."; ERRORCODE: 100; NEXT: null
        at com.documentum.fc.client.impl.docbase.DocbaseExceptionMapper.newException(DocbaseExceptionMapper.java:57)
        at com.documentum.fc.client.impl.connection.docbase.MessageEntry.getException(MessageEntry.java:39)
        at com.documentum.fc.client.impl.connection.docbase.DocbaseMessageManager.getExceptionForAllMessages(DocbaseMessageManager.java:176)
        at com.documentu
[dmadmin@cs-0 agentexec]$
[dmadmin@cs-0 agentexec]$ # Finding the first file with the error
[dmadmin@cs-0 agentexec]$ for f in $(ls -tr); do r=$(grep "_I_SESSION_START.*ERRORCODE" "${f}"); if [[ "${r}" != "" ]]; then echo "${r}"; break; fi; done
Tue Sep 10 18:00:06 2019 [INFORMATION] [LAUNCHER 31113] Detected while preparing job dm_ConsistencyChecker for execution: Agent Exec connected to server gr_repo:  DfException:: THREAD: main; MSG: [DM_SESSION_I_SESSION_START]info:  "Session 0112d687800c8827 started for user dmadmin."; ERRORCODE: 100; NEXT: null
[dmadmin@cs-0 agentexec]$

In all the job’s sessions files, there were the same stack (or rather a piece of the stack). At first, I didn’t understand where this was coming from, all I know was that it was linked somehow to the login inside the Repository and that it appeared for the first time on the date returned by my last command above. It was not really an error message since it wasn’t showing any “_E_” messages but it was still printing an exception.

Knowing when it appeared the first time, I looked at all the files that have been modified on that day and among log files, which are expected and can be ignored, there were the dfc.properties file. This provided me the reason for this message: it was actually due to enabling the diagnostic mode on the dfc.properties of the Documentum Server. To be exact, it was due to the “dfc.diagnostics.exception.include_stack=true” entry:

[dmadmin@cs-0 agentexec]$ tail -5 $DOCUMENTUM_SHARED/config/dfc.properties
dfc.session.secure_connect_default=secure
dfc.time_zone=UTC
dfc.diagnostics.resources.enable=true
dfc.diagnostics.exception.include_stack=true
dfc.tracing.print_exception_stack=true
[dmadmin@cs-0 agentexec]$
[dmadmin@cs-0 agentexec]$ echo "quit" | iapi gr_repo -Udmadmin -Pxxx

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

Connecting to Server using docbase gr_repo
DfException:: THREAD: main; MSG: [DM_SESSION_I_SESSION_START]info:  "Session 0112d687800c9235 started for user dmadmin."; ERRORCODE: 100; NEXT: null
        at com.documentum.fc.client.impl.docbase.DocbaseExceptionMapper.newException(DocbaseExceptionMapper.java:57)
        at com.documentum.fc.client.impl.connection.docbase.MessageEntry.getException(MessageEntry.java:39)
        at com.documentum.fc.client.impl.connection.docbase.DocbaseMessageManager.getExceptionForAllMessages(DocbaseMessageManager.java:176)
        at com.documentum.fc.client.impl.connection.docbase.DocbaseConnection.getExceptionForAllMessages(DocbaseConnection.java:1518)
        at com.documentum.fc.client.impl.session.Session.getExceptionsForAllMessages(Session.java:1603)
        at com.documentum.fc.client.impl.session.SessionHandle.getExceptionsForAllMessages(SessionHandle.java:1301)
        at com.documentum.dmcl.impl.ApiContext.addMessages(ApiContext.java:423)
        at com.documentum.dmcl.impl.ApiContext.collectExceptionsForReporting(ApiContext.java:370)
        at com.documentum.dmcl.impl.GetMessageHandler.get(GetMessageHandler.java:23)
        at com.documentum.dmcl.impl.DmclApi.get(DmclApi.java:49)
        at com.documentum.dmcl.impl.DmclApiNativeAdapter.get(DmclApiNativeAdapter.java:145)
        at com.documentum.dmcl.impl.DmclApiNativeAdapter.get(DmclApiNativeAdapter.java:130)


Connected to Documentum Server running Release 7.3.0050.0039  Linux64.Oracle
Session id is s0
API> Bye
[dmadmin@cs-0 agentexec]$
[dmadmin@cs-0 agentexec]$ sed -i sed 's,^dfc.diagnostics.exception.include_stack,#&,' $DOCUMENTUM_SHARED/config/dfc.properties
[dmadmin@cs-0 agentexec]$
[dmadmin@cs-0 agentexec]$ echo "quit" | iapi gr_repo -Udmadmin -Pxxx

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

Connecting to Server using docbase gr_repo
[DM_SESSION_I_SESSION_START]info:  "Session 0112d687800c9237 started for user dmadmin."

Connected to Documentum Server running Release 7.3.0050.0039  Linux64.Oracle
Session id is s0
API> Bye
[dmadmin@cs-0 agentexec]$

As you can see above, commenting the line “dfc.diagnostics.exception.include_stack=true” (meaning setting it to false, the default value) caused the exception stack to disappear. Since I was curious about this stack and wanted confirmation that this is “expected”, I opened a case with the OpenText Support (#4331438) and they confirmed me after a few days that it wasn’t considered an “ERROR“, it was more of an “INFO” message. It’s a strange way to display informative messages but hey, who am I to judge!

Cet article Documentum – Java exception stack on iAPI/iDQL login est apparu en premier sur Blog dbi services.

How can you connect an external DFC Client to docbrokers and Repositories hosted on Kubernetes Pods? That seems to be a very simple question yet it might prove difficult… Let’s talk about this challenge in this blog and possible solutions/workarounds.

As you all know, Kubernetes is using containers so just like for a basic Docker container, you won’t be able to access it from the outside by default. On Docker, you will need to expose some ports and then you can interact with whatever is running on that port. For Kubernetes, it’s the same principle but it obviously add other layers in addition which makes it even more complicated. Therefore, if you want to be able to connect to a docbroker inside a K8s Pod from the outside of K8s, then you will need to do a few things:

• at the container level, to open the ports 1489/1490 (default ones, you can change them obviously)
• a K8s Service to expose these ports inside K8s
• an Nginx Ingres Controller for which the TCP ports 1489/1490 have been configured for external accesses (or other ports if these are already used for another namespace for example)
• a “Load Balancer” K8s Service (still at the Nginx Ingres Controller level) which exposes these ports using an external IP

Once you have that, you should be able to communicate with a docbroker that is inside a K8s pod. If you want to have a chance to talk to a Repository, then you will also need to do the same thing but for the Repository ports. When you install a repo, you will specify in the /etc/services the ports it should use (just like for the docbroker).

For this example, let’s start simple with the same ports internally and externally:

• DFC Client host: vm
• K8s pod short name (hostname): cs-0
• K8s pod full name (headless service / full hostname): cs-0.cs.dbi-ns01.svc.cluster.local
• K8s pod IP: 1.1.1.100
• K8s pod docbroker port: 1489/1490
• K8s pod Repositories port: gr_repo=49400/49401    //    REPO1=49402/49403
• K8s external hostname/lb: k8s-cs-dbi-ns01.domain.com
• K8s external IP: 2.2.2.200
• K8s external docbroker port: 1489/1490
• K8s external Repositories port: gr_repo=49400/49401    //    REPO1=49402/49403

Considering the above setup (both the docbroker and Repositories ports configured on K8s), you can already talk to the docbroker properly:

[dmadmin@vm ~]$ grep "dfc.docbroker" dfc.properties
dfc.docbroker.host[0]=k8s-cs-dbi-ns01.domain.com
dfc.docbroker.port[0]=1489
[dmadmin@vm ~]$
[dmadmin@vm ~]$ nc -v k8s-cs-dbi-ns01.domain.com 1489
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 2.2.2.200:1489.
^C
[dmadmin@vm ~]$ 
[dmadmin@vm ~]$ nc -v k8s-cs-dbi-ns01.domain.com 49402
Ncat: Version 7.50 ( https://nmap.org/ncat )
Ncat: Connected to 2.2.2.200:49402.
^C
[dmadmin@vm ~]$ 
[dmadmin@vm ~]$ dmqdocbroker -t k8s-cs-dbi-ns01.domain.com -p 1489 -c ping
dmqdocbroker: A DocBroker Query Tool
dmqdocbroker: Documentum Client Library Version: 16.4.0000.0185
Using specified port: 1489
Successful reply from docbroker at host (cs-0) on port(1490) running software version (16.4.0170.0234  Linux64).
[dmadmin@vm ~]$ 
[dmadmin@vm ~]$ dmqdocbroker -t k8s-cs-dbi-ns01.domain.com -p 1489 -c getdocbasemap
dmqdocbroker: A DocBroker Query Tool
dmqdocbroker: Documentum Client Library Version: 16.4.0000.0185
Using specified port: 1489
**************************************************
**     D O C B R O K E R    I N F O             **
**************************************************
Docbroker host            : cs-0
Docbroker port            : 1490
Docbroker network address : INET_ADDR: 01 2d3 01010164 cs-0 1.1.1.100
Docbroker version         : 16.4.0170.0234  Linux64
**************************************************
**     D O C B A S E   I N F O                  **
**************************************************
--------------------------------------------
Docbase name        : gr_repo
Docbase id          : 1234567
Docbase description : dbi-ns01 dev k8s gr
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       : Global Registry
Docbase Dormancy Status     :
--------------------------------------------
Docbase name        : REPO1
Docbase id          : 1234568
Docbase description : dbi-ns01 dev k8s repo1
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       :
Docbase Dormancy Status     :
--------------------------------------------
[dmadmin@vm ~]$

So as you can see above, the docbroker does respond properly with the list of Repositories that it is aware of (Repo name, ID, hostname, …) and for that purpose, there is no need for the Repositories’ ports to be opened, only the docbroker is enough. However, as soon as you want to go further and start talking to the Repositories, you will obviously need to open these additional ports as well. Above, I used 49402/49403 for the REPO1 Repository (both internal and external). When trying to login to a target Repository, it will fail:

[dmadmin@vm ~]$ echo "quit" | iapi REPO1 -Udmadmin -P${dm_pw}

        OpenText Documentum iapi - Interactive API interface
        Copyright (c) 2018. OpenText Corporation
        All rights reserved.
        Client Library Release 16.4.0000.0185

Connecting to Server using docbase REPO1
^C[dmadmin@vm ~]$
[dmadmin@vm ~]$

Why is that? Well the reason is that to connect to a docbroker, a DFC Client will use the value from the well-known “dfc.properties” file. By reading it, it will know where the docbroker can be found: in our case, it’s “k8s-cs-dbi-ns01.domain.com:1489“. When that is done, the docbroker replies with the list of Repositories known and it will also reply with the “host” that should be used to communicate with the Repositories. That’s because the Repositories might not be on the same host as the docbroker and therefore it needs to provides the information to the DFC Client. However, that “host” is actually an IP! When a Repository register itself with a docbroker, the docbroker records the source IP of the request and it will then forward this IP to the DFC Client that wants to talk to this Repository.

The problem here is that the Repositories are installed on K8s Pods and therefore the IP that the docbroker knows is actually the IP of the K8s Pod… Which is, therefore, not reachable from outside of K8s!

If you want to validate a setup or do some testing, it’s pretty simple on Linux, you can quickly setup an IP Forwarding between the IP of the K8s Pod (which points to nothing) and the IP of the K8s LB Service that you configured previously for the docbroker and Repositories ports. Here is an example:

[dmadmin@vm ~]$ nslookup k8s-cs-dbi-ns01.domain.com
Server: 1.1.1.10
Address: 1.1.1.10#53

k8s-cs-dbi-ns01.domain.com     canonical name = k8s-cluster-worker2.domain.com.
Name:   k8s-cluster-worker2.domain.com
Address: 2.2.2.200
[dmadmin@vm ~]$
[dmadmin@vm ~]$ external_ip=2.2.2.200
[dmadmin@vm ~]$ ping -c 1 ${external_ip}
PING 2.2.2.200 (2.2.2.200) 56(84) bytes of data.
64 bytes from 2.2.2.200: icmp_seq=1 ttl=63 time=0.980 ms

--- 2.2.2.200 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.980/0.980/0.980/0.000 ms
[dmadmin@vm ~]$
[dmadmin@vm ~]$ internal_ip=1.1.1.100
[dmadmin@vm ~]$ ping -c 1 ${internal_ip}
PING 1.1.1.100 (1.1.1.100) 56(84) bytes of data.

--- 1.1.1.100 ping statistics ---
1 packets transmitted, 0 received, 100% packet loss, time 0ms
[dmadmin@vm ~]$
[dmadmin@vm ~]$ echo "quit" | iapi REPO1 -Udmadmin -P${dm_pw}

        OpenText Documentum iapi - Interactive API interface
        Copyright (c) 2018. OpenText Corporation
        All rights reserved.
        Client Library Release 16.4.0000.0185

Connecting to Server using docbase REPO1
^C[dmadmin@vm ~]$
[dmadmin@vm ~]$
[dmadmin@vm ~]$
[dmadmin@vm ~]$ sudo sysctl -w net.ipv4.ip_forward=1
net.ipv4.ip_forward = 1
[dmadmin@vm ~]$ 
[dmadmin@vm ~]$ sudo iptables -t nat -A OUTPUT -d ${internal_ip} -j DNAT --to-destination ${external_ip}
[dmadmin@vm ~]$ 
[dmadmin@vm ~]$ echo "quit" | iapi REPO1 -Udmadmin -P${dm_pw}

        OpenText Documentum iapi - Interactive API interface
        Copyright (c) 2018. OpenText Corporation
        All rights reserved.
        Client Library Release 16.4.0000.0185

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

Connected to OpenText Documentum Server running Release 16.4.0170.0234  Linux64.Oracle
Session id is s0
API> Bye
[dmadmin@vm ~]$

As you can see above, as soon as you configure an IP Forwarding from the Pod IP to the K8s LB IP, then the Repository connection is successful. Here, I just executed a “quit” command to close the iAPI session but it shows that the session creation is working so you are sure that the end-to-end communication is fine.

Obviously, that is just for testing… Indeed, your Pod IP is going to change in the future (after each restart of the pod for example) which means that the IP Forwarding will be broken at that time. This also requires being setup on the client directly because the DFC Client will try to communicate with a specific IP… But this IP most probably doesn’t point to anything and therefore the only way to make it happen correctly is either setting that up on the client or on the network layer, which is super annoying and isn’t really reliable anyway so this isn’t a solution.

Several years ago, a feature has been introduced in the docbroker that was initially planned to handle blocking rules on a FireWall: IP and Port Translation. I believe it was introduced for Documentum 6.5 but I might be wrong, it was a long time ago… Since the issue here for K8s is pretty similar to what would happen with a FireWall blocking the IP, we can actually use this feature to help us. Contrary to the IP Forwarding, which is done on the client side, the Translation is done on the server side which is therefore global for all clients. This has an obvious advantage that you can just do it once for all clients (or rather you will need to re-do this configuration at each start of your K8s Pod since the IP will be changed). However, it also has a drawback which is that there is no exception: all communications will be translated, even K8s internal communications… So this might be a problem. There is a KB to describe how it works (KB7701941) and you can also look at the documentation as well. However, the documentation might not be really correct. Indeed, if you look at the CS 7.1 documentation, you will find this definition:

[TRANSLATION]
port=inside_firewall_port=outside_firewall_port
{,inside_firewall_port=outside_firewall_port}
host=inside_firewall_IP=outside_firewall_IP
{,inside_firewall_IP=outside_firewall_IP}

If you look at the CS 16.4 documentation, you will find this definition:

[TRANSLATION]
port=inside_firewall_port=outside_firewall_port
{,inside_firewall_port=outside_firewall_port}
host=outside_firewall_IP=inside_firewall_IP
{,outside_firewall_IP=inside_firewall_IP}

Finally, if you look at the CS 16.7 documentation, you will find yet another definition:

[TRANSLATION]port=["]outside_firewall_port=inside_firewall_port
{,outside_firewall_port=inside_firewall_port}["]
host=["]outside_firewall_ip=inside_firewall_ip
{,outside_firewall_ip=inside_firewall_ip}["]

Three documentations on the same feature, three different definitions :D. In addition to that, there is an example in the documentation which is also wrong, on the three documentations. The real definition is the last one, after fixing the formatting errors that is… So in short, this is what you can do with the docbroker translation:

[TRANSLATION]
port=["]ext_port_1=int_port_1{,ext_port_2=int_port_2}{,ext_port_3=int_port_3}{,...}["]
host=["]ext_ip_1=int_ip_1{,ext_ip_2=int_ip_2}{,ext_ip_3=int_ip_3}{,...}["]

From what I could see, the double quotes aren’t mandatory but you can use them if you want to…

Let’s test all that after removing the IP Forwarding, obviously:

[dmadmin@vm ~]$ dmqdocbroker -t k8s-cs-dbi-ns01.domain.com -p 1489 -c getdocbasemap
dmqdocbroker: A DocBroker Query Tool
dmqdocbroker: Documentum Client Library Version: 16.4.0000.0185
Using specified port: 1489
**************************************************
**     D O C B R O K E R    I N F O             **
**************************************************
Docbroker host            : cs-0
Docbroker port            : 1490
Docbroker network address : INET_ADDR: 01 2d3 01010164 cs-0 1.1.1.100
Docbroker version         : 16.4.0170.0234  Linux64
**************************************************
**     D O C B A S E   I N F O                  **
**************************************************
--------------------------------------------
Docbase name        : gr_repo
Docbase id          : 1234567
Docbase description : dbi-ns01 dev k8s gr
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       : Global Registry
Docbase Dormancy Status     :
--------------------------------------------
Docbase name        : REPO1
Docbase id          : 1234568
Docbase description : dbi-ns01 dev k8s repo1
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       :
Docbase Dormancy Status     :
--------------------------------------------
[dmadmin@vm ~]$
[dmadmin@vm ~]$ echo "quit" | iapi REPO1 -Udmadmin -P${dm_pw}

        OpenText Documentum iapi - Interactive API interface
        Copyright (c) 2018. OpenText Corporation
        All rights reserved.
        Client Library Release 16.4.0000.0185

Connecting to Server using docbase REPO1
^C[dmadmin@vm ~]$
[dmadmin@vm ~]$

On the docbroker side (k8s), let’s configure the translation properly and restart for the new configuration to be applied:

[dmadmin@cs-0 ~]$ cd $DOCUMENTUM/dba
[dmadmin@cs-0 dba]$ cat Docbroker.ini
[DOCBROKER_CONFIGURATION]
secure_connect_mode=dual
[dmadmin@cs-0 dba]$
[dmadmin@cs-0 dba]$ external_ip=2.2.2.200
[dmadmin@cs-0 dba]$ external_port=1489
[dmadmin@cs-0 dba]$ internal_ip=1.1.1.100
[dmadmin@cs-0 dba]$ internal_port=1489
[dmadmin@cs-0 dba]$
[dmadmin@cs-0 dba]$ echo "[TRANSLATION]" >> Docbroker.ini
[dmadmin@cs-0 dba]$ echo "port=${external_port}=${internal_port}" >> Docbroker.ini
[dmadmin@cs-0 dba]$ echo "host=${external_ip}=${internal_ip}" >> Docbroker.ini
[dmadmin@cs-0 dba]$
[dmadmin@cs-0 dba]$ cat Docbroker.ini
[DOCBROKER_CONFIGURATION]
secure_connect_mode=dual
[TRANSLATION]
port=1489=1489
host=2.2.2.200=1.1.1.100
[dmadmin@cs-0 dba]$
[dmadmin@cs-0 dba]$ ./dm_stop_Docbroker; sleep 1; ./dm_launch_Docbroker
./dmshutdown 16.4.0000.0248  Linux64 Copyright (c) 2018. OpenText Corporation.
Shutdown request was processed by Docbroker on host cs-0 (INET_ADDR: 01 2d3 01010164 cs-0 1.1.1.100)
Reply status indicates a success: OK
starting connection broker on current host: [cs-0.cs.dbi-ns01.svc.cluster.local]
with connection broker log: [$DOCUMENTUM/dba/log/docbroker.cs-0.cs.dbi-ns01.svc.cluster.local.1489.log]
connection broker pid: 18219
[dmadmin@cs-0 dba]$
[dmadmin@cs-0 dba]$ head -7 log/docbroker.cs-0.cs.dbi-ns01.svc.cluster.local.1489.log
OpenText Documentum Connection Broker (version 16.4.0170.0234  Linux64)
Copyright (c) 2018. OpenText Corporation
HOST TRANSLATION TABLE:
    [1] From(1.1.1.100), to(2.2.2.200)
PORT TRANSLATION TABLE:
    [1] From(1489), to(1489)
2019-12-15T10:25:22.307379 [DM_DOCBROKER_I_START]info:  "Docbroker has started.  Process id: 18219"
[dmadmin@cs-0 dba]$

Once that is done, back on the DFC Client side, trying to connect to the Repository:

[dmadmin@vm ~]$ dmqdocbroker -t k8s-cs-dbi-ns01.domain.com -p 1489 -c getdocbasemap
dmqdocbroker: A DocBroker Query Tool
dmqdocbroker: Documentum Client Library Version: 16.4.0000.0185
Using specified port: 1489
**************************************************
**     D O C B R O K E R    I N F O             **
**************************************************
Docbroker host            : cs-0
Docbroker port            : 1490
Docbroker network address : INET_ADDR: 01 2d3 01010164 cs-0 1.1.1.100
Docbroker version         : 16.4.0170.0234  Linux64
**************************************************
**     D O C B A S E   I N F O                  **
**************************************************
--------------------------------------------
Docbase name        : gr_repo
Docbase id          : 1234567
Docbase description : dbi-ns01 dev k8s gr
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       : Global Registry
Docbase Dormancy Status     :
--------------------------------------------
Docbase name        : REPO1
Docbase id          : 1234568
Docbase description : dbi-ns01 dev k8s repo1
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       :
Docbase Dormancy Status     :
--------------------------------------------
[dmadmin@vm ~]$

As you can see above, the dmqdocbroker will still print the Internal IP (1.1.1.100), that’s fine/normal. However the Repository connection should now work:

[dmadmin@vm ~]$ echo "quit" | iapi REPO1 -Udmadmin -P${dm_pw}

        OpenText Documentum iapi - Interactive API interface
        Copyright (c) 2018. OpenText Corporation
        All rights reserved.
        Client Library Release 16.4.0000.0185

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

Connected to OpenText Documentum Server running Release 16.4.0170.0234  Linux64.Oracle
Session id is s0
API> Bye
[dmadmin@vm ~]$

So as you can see above, using the docbroker translation mechanisms is indeed a solution to be able to connect to a Repository that is inside a K8s pod. There are drawbacks as mentioned above but at least, that’s a valid workaround.

Above, I have always been using the same ports internally and externally. However, in a real case, you will probably have, in the end, hundreds or even thousands of CS pods. So how do you manage that? Well you saw above that the docbroker translation can be used to translate an external port into an internal port but it’s not just for the docbroker port! You can actually use that for the Repository ports as well.

Let’s say for this example that I have a second namespace (dbi-ns02) with the following:

• DFC Client Host: vm
• K8s pod short name (hostname): cs-0
• K8s pod full name (headless service / full hostname): cs-0.cs.dbi-ns02.svc.cluster.local
• K8s pod IP: 1.1.1.200
• K8s pod docbroker port: 1489/1490
• K8s pod Repositories port: gr_repo=49400/49401    //    REPO2=49402/49403
• K8s external hostname/lb: k8s-cs-dbi-ns02.domain.com
• K8s external IP: 2.2.2.200
• K8s external docbroker port: 1491/1492
• K8s external Repositories port: gr_repo=49404/49405    //    REPO2=49406/49407

The external IP is still the same because it’s the same K8s Cluster but the external ports are now different. The internal IP is also different because it’s another namespace. So with the default docbroker configuration (no translation), then we have the same issue, obviously, where the iAPI session will hang and never respond because of the IP that doesn’t exist.

So if we try to setup the basic docbroker translation just like what we did above, then on the K8s pod, we will have the following:

[dmadmin@cs-0 ~]$ cd $DOCUMENTUM/dba
[dmadmin@cs-0 dba]$
[dmadmin@cs-0 dba]$ ifconfig | grep inet | grep -v 127.0.0.1
        inet 1.1.1.200  netmask 255.255.255.255  broadcast 0.0.0.0
[dmadmin@cs-0 dba]$
[dmadmin@cs-0 dba]$ cat Docbroker.ini
[DOCBROKER_CONFIGURATION]
secure_connect_mode=dual
[TRANSLATION]
port=1491=1489
host=2.2.2.200=1.1.1.200
[dmadmin@cs-0 dba]$

With this configuration, if you are trying to connect from an external DFC Client, then it will be able to talk to the docbroker (assuming you have all the K8s stuff in place for redirecting the ports properly) but won’t be able to talk to the Repository:

[dmadmin@vm ~]$ dmqdocbroker -t k8s-cs-dbi-ns02.domain.com -p 1491 -c getdocbasemap
dmqdocbroker: A DocBroker Query Tool
dmqdocbroker: Documentum Client Library Version: 16.4.0000.0185
Using specified port: 1491
**************************************************
**     D O C B R O K E R    I N F O             **
**************************************************
Docbroker host            : cs-0
Docbroker port            : 1490
Docbroker network address : INET_ADDR: 02 3d4 02020286 cs-0 1.1.1.200
Docbroker version         : 16.4.0170.0234  Linux64
**************************************************
**     D O C B A S E   I N F O                  **
**************************************************
--------------------------------------------
Docbase name        : gr_repo
Docbase id          : 1234569
Docbase description : dbi-ns02 dev k8s gr
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       : Global Registry
Docbase Dormancy Status     :
--------------------------------------------
Docbase name        : REPO2
Docbase id          : 1234570
Docbase description : dbi-ns02 dev k8s repo2
Govern docbase      :
Federation name     :
Server version      : 16.4.0170.0234  Linux64.Oracle
Docbase Roles       :
Docbase Dormancy Status     :
--------------------------------------------
[dmadmin@vm ~]$
[dmadmin@vm ~]$ echo "quit" | iapi REPO2 -Udmadmin -P${dm_pw}

        OpenText Documentum iapi - Interactive API interface
        Copyright (c) 2018. OpenText Corporation
        All rights reserved.
        Client Library Release 16.4.0000.0185

Connecting to Server using docbase REPO2
:Wrong docbase id: (1234570) expecting: (1234568)

Could not connect
[dmadmin@vm ~]$

The reason for that is that I have been talking to the docbroker on the external port 1491, which is therefore the docbroker 1489 of the second namespace (“dbi-ns02“). This docbroker replied to the DFC Client that the Repository is using the port 49402/49403, which is true but only internally… Therefore, my DFC Client has been trying to connect to the Repository REPO2 (from the second namespace) using the port which is actually the one used by the REPO1 (from the first namespace) and therefore there is a mismatch in the Repository ID.

For that purpose, you can update the docbroker translation to include the Repositories ports as well:

[dmadmin@cs-0 dba]$ cat Docbroker.ini
[DOCBROKER_CONFIGURATION]
secure_connect_mode=dual
[TRANSLATION]
port=1491=1489,49404=49400,49405=49401,49406=49402,49407=49403
host=2.2.2.200=1.1.1.200
[dmadmin@cs-0 dba]$

With this new docbroker translation configuration, the external DFC Client should be able to communicate properly with the repository:

[dmadmin@vm ~]$ echo "quit" | iapi REPO2 -Udmadmin -P${dm_pw}

        OpenText Documentum iapi - Interactive API interface
        Copyright (c) 2018. OpenText Corporation
        All rights reserved.
        Client Library Release 16.4.0000.0185

Connecting to Server using docbase REPO2
[DM_SESSION_I_SESSION_START]info:  "Session 0112d68a80001403 started for user dmadmin."

Connected to OpenText Documentum Server running Release 16.4.0170.0234  Linux64.Oracle
Session id is s0
API> Bye
[dmadmin@vm ~]$

Alternatively to all that, you might want to take a look at Traefik or Istio which might also help you to configure the correct communications from the outside of K8s to the inside. I had a case opened with the OpenText Support so that they could correct the documentation for all versions.

Cet article Documentum – Connection to docbrokers and Repositories inside K8s from an external DFC Client est apparu en premier sur Blog dbi services.

The problem with finding something new and fiddling with it and checking to see how you can best use it to advantage is that you sometimes manage to “break” it very quickly. In yesterday’s blog note I introduced the /*+ push_having_to_gby(@qbname) */ hint and explained why it was a useful little enhancement. I also showed a funny little glitch with a missing predicate in the execution plan.

Today I thought I’d do something a little more complex with the example I produced yesterday, and I’ve ended up with a little note that’s not actually about the hint, it’s about something that appeared in my initial testing of the hint, and then broke when I pushed it a little further. Here’s a script to create data for the new test:

rem
rem     Script:         push_having_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2019
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem

create table t1
nologging
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        lpad(rownum,10,'0')             v1,
        lpad('x',50,'x')                padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;

insert into t1 values (2, lpad(2,10,'0'), lpad('x',50,'x'));
commit;

alter table t1 modify id not null;
create index t1_i1 on t1(id) nologging;

create table t2 as select * from t1;
create index t2_i1 on t2(id) nologging;

I’ve created two tables here, one a clone of the other, with one id value out of 1 million having two rows. As we saw yesterday it’s quite simple to write some SQL that uses an index full scan on the t1_i1 index to check for duplicate id values without doing a massive sort or hash aggregation:

set serveroutput off
alter session set statistics_level = all;

select
        /*+
                qb_name(driver)
                index(@driver t1@driver)
        */
        id 
from
        t1
where   id is not null
group by 
        id 
having  
        count(1) > 1
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |      1 |00:00:00.87 |    2229 |   2228 |
|   1 |  SORT GROUP BY NOSORT|       |      1 |  50000 |      1 |00:00:00.87 |    2229 |   2228 |
|   2 |   INDEX FULL SCAN    | T1_I1 |      1 |   1000K|   1000K|00:00:00.40 |    2229 |   2228 |
-------------------------------------------------------------------------------------------------

As we saw yesterday this plan simply walks the index in order keeping track of a “running count” and doesn’t allocate a large PGA to sort a million rows of data, but there’s no asterisk by any operation telling us that there’s a predicate being checked, and no Predicate Information section to report the “count(1) > 1” predicate that we know exists (and is used, since the query produces the right answer).

Having ascertained that there is one duplicated id in the table, let’s join to the (clone) t2 table to list the rows for that id – and lets use the initial query as an inline view:

select
        /*+ 
                qb_name(main)
        */
        t2.v1
from    (
        select
                /*+
                        qb_name(driver)
                        index(@driver t1@driver)
                        no_use_hash_aggregation(@driver)
                */
                id 
        from
                t1
        where   id is not null
        group by 
                id 
        having  
                count(1) > 1
        )                       v1,
        t2
where
        t2.id = v1.id
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |      2 |00:00:00.76 |    2234 |     87 |       |       |          |
|   1 |  NESTED LOOPS                |       |      1 |  50000 |      2 |00:00:00.76 |    2234 |     87 |       |       |          |
|   2 |   NESTED LOOPS               |       |      1 |        |      2 |00:00:00.75 |    2232 |     28 |       |       |          |
|   3 |    VIEW                      |       |      1 |  50000 |      1 |00:00:00.75 |    2228 |      0 |       |       |          |
|*  4 |     SORT GROUP BY            |       |      1 |  50000 |      1 |00:00:00.75 |    2228 |      0 |    53M|  2539K|   47M (0)|
|   5 |      INDEX FULL SCAN         | T1_I1 |      1 |   1000K|   1000K|00:00:00.26 |    2228 |      0 |       |       |          |
|*  6 |    INDEX RANGE SCAN          | T2_I1 |      1 |        |      2 |00:00:00.01 |       4 |     28 |       |       |          |
|   7 |   TABLE ACCESS BY INDEX ROWID| T2    |      2 |      1 |      2 |00:00:00.01 |       2 |     59 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(COUNT(*)>1)
   6 - access("T2"."ID"="V1"."ID")

As you can see from this plan, I didn’t get the “sort group by nosort” that I wanted – even though the inline view was not merged. In fact, you’ll notice the /*+ no_use_hash_aggregation() */ hint I had to include to get a sort group by rather than a hash group by. The logic behind resolving this query block changed significantly when it went into a more complex query.

Having tried adding several other hints (blocking nlj_prefetch, nlj_batching, batched index access, setting cardinality to 1, first_rows(1) optimisation) I finally came down to using a materialized CTE (common table expression / “with” subquery):

with v1 as (
        select
                /*+
                        qb_name(driver)
                        index(@driver t1@driver)
                        materialize
                */
                id 
        from
                t1
        where
                id is not null
        group by 
                id 
        having  
                count(1) > 1
)
select
        /*+ 
                qb_name(main)
        */
        t2.v1
from    
        v1,
        t2
where
        t2.id = v1.id
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |      1 |        |      2 |00:00:00.86 |    2236 |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |      1 |        |      2 |00:00:00.86 |    2236 |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66F8_E3B235A |      1 |        |      0 |00:00:00.86 |    2229 |
|   3 |    SORT GROUP BY NOSORT                  |                            |      1 |  50000 |      1 |00:00:00.86 |    2228 |
|   4 |     INDEX FULL SCAN                      | T1_I1                      |      1 |   1000K|   1000K|00:00:00.39 |    2228 |
|   5 |   NESTED LOOPS                           |                            |      1 |  50000 |      2 |00:00:00.01 |       6 |
|   6 |    NESTED LOOPS                          |                            |      1 |        |      2 |00:00:00.01 |       4 |
|   7 |     VIEW                                 |                            |      1 |  50000 |      1 |00:00:00.01 |       0 |
|   8 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D66F8_E3B235A |      1 |  50000 |      1 |00:00:00.01 |       0 |
|*  9 |     INDEX RANGE SCAN                     | T2_I1                      |      1 |        |      2 |00:00:00.01 |       4 |
|  10 |    TABLE ACCESS BY INDEX ROWID           | T2                         |      2 |      1 |      2 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   9 - access("T2"."ID"="V1"."ID")

You’ll notice that the hinting is back to the bare minimum – with only the addition of the /*+ materialize */ hint in the CTE. You’ll also notice that the “count(1) > 1” predicate is still missing. But critically we do have the index full scan leading into a sort group by nosort and no huge memory allocation.

The price we have to pay is that we do direct path writes to the temporary tablespace to materialize the CTE and db file scattered reads to read the data back. But since this example is aimed at a large data set returning a small result set this may be a highly appropriate trade off.

It’s possible that a detailed examination of the 10053 trace file would give us a clue about why Oracle can find the sort group by nosort when the query block is a materialized CTE but not when it’s an inline view – but I’m happy to leave that investigation to someone else and just leave this here as a warning that sometimes (even in 19c) there’s a difference between a non-merged view path and a materizlied subquery path.

In a previous blog, I explained how it is possible to create an LDAP/LDAPs Authentication Provider on WebLogic. My initial goal wasn’t just to setup an LDAP/LDAPs on WebLogic Server. That was only a first step needed in order to automate the setup of a SAML2 Single Sign-On linked with the authentication from a LDAPs. Therefore, in this blog, we will take a look at that second part. Just like for the LDAP Authentication Provider, there are plenty of examples on the internet to do just that but they are all always using the GUI. When I searched for it, I didn’t find even a single one explaining how it could be done without. Maybe there are some but if so, it looks like they are pretty well hidden. In addition to that, you might think about just recording the steps in the WebLogic Administration Console so that it would create you the needed WLST scripts (just like for the LDAPs provider creation). Unfortunately, it’s not that simple. Indeed, it doesn’t work for everything and in addition to that, most of the steps that will be needed are outside of an edit session and therefore can’t be recorded.

In this blog, I will SAML 2.0 and I will assume that there is already an Identity Provider (“Server side“) that has been configured and I will configure a WebLogic Server (“Client side” = Service Provider) to use this Identity Provider using a WebSSO partner. In the WebLogic examples provided with the OFM full installation, there is a complete example for SAML2 on both Server and Client sides. For the Client side, they are however using a manual creation of the IdP Partner, importing the SSL Certificate, defining the URLs, aso… A simpler & faster approach is to use a metadata file that can be extracted/exported from the Server side which contains all these information and then imported into the Client side. That’s what I will show below, so it is pretty different to what is done in the example.

Alright, so the first thing to be done is to create a new Authentication Provider using the SAML2IdentityAsserter type. Because this change requires a full restart of the WebLogic Server, I usually do it with the LDAP Authentication Provider but for this example, I will split things and only talk about the SAML2 part. Just like in the previous blog, I will use a properties file and a WLST script. You can disregard the LDAP Authentication Providers parameters, they are only used for the LDAP part in the other blog, except ATN_NAME which I still used below but that’s only in case you do have a LDAP/LDAPs Authentication Provider in addition to the SAML2 one that you want to create:

[weblogic@weblogic-server-0 ~]$ cat domain.properties
# AdminServer parameters
CONFIG_FILE=/home/weblogic/secure/configfile.secure
KEY_FILE=/home/weblogic/secure/keyfile.secure
ADMIN_URL=t3s://weblogic-server-0.domain.com:8443
# LDAP Authentication Providers parameters
ATN_NAME=Internal_LDAP
ATN_FLAG=SUFFICIENT
ATN_HOST=ldap.domain.com
ATN_PORT=636
ATN_PRINCIPAL=ou=APP,ou=applications,ou=intranet,dc=dbi services,dc=com
ATN_CREDENTIAL=T3stP4ssw0rd
ATN_SSL=true
ATN_BASE_DN=ou=people,ou=intranet,dc=dbi services,dc=com
ATN_USER_FILTER=(&(uid=%u)(objectclass=person))
ATN_USER_CLASS=person
ATN_USER_AS_PRINCIPAL=true
ATN_GROUP_FILTER=(&(cn=%g)(objectclass=groupofuniquenames))
ATN_TIMEOUT=30
# IdP Partner parameters
IDA_NAME=APP_SAML2_IDAsserter
IDP_NAME=APP_SAML2_IDPartner
IDP_METADATA=/home/weblogic/idp_metadata.xml
IDP_ENABLED=true
IDP_REDIRECT_URIS=['/D2-01/*','/D2-02/*']
# Managed Servers SSO parameters
SSO_MS=msD2-01,msD2-02
SSO_URLS=https://lb_url1/saml2,https://lb_url2/saml2
SSO_ENTITY_IDS=APP_SAML2_Entity_ID_01,APP_SAML2_Entity_ID_02
SSO_SP_ENABLED=true
SSO_SP_BINDING=HTTP/POST
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$ cat createSAML2AuthenticationProviders.wlst
##################################################################
#
# Authors: Morgan Patou    
# Version: 1.4 - 30/08/2019
#
# File: createSAML2AuthenticationProviders.wlst
# Purpose: Script to create SAML2 Authentication Providers
# Parameters: input properties file (optional)
# Output:
#
##################################################################

# Get operating system (for vars)
import os

# Read the domain properties file
try:
  if len(sys.argv) == 2:
    domainProperties=sys.argv[1]
  else:
    domainProperties=os.path.realpath(os.path.dirname(sys.argv[0])) + "/domain.properties"
  loadProperties(domainProperties)
  print ">>> Loaded the properties file: " + domainProperties
  print

except:
  exit(exitcode=1)

try:
  redirect('/dev/null','false')
  # Connect to AdminServer
  connect(userConfigFile=CONFIG_FILE,userKeyFile=KEY_FILE,url=ADMIN_URL)
  print ">>> Connected to the AdminServer."

  # Start Edit Session
  edit()
  startEdit()
  stopRedirect()
  print ">>> Edit Session started."

  # Get default Realm
  realm=cmo.getSecurityConfiguration().getDefaultRealm()

  # Create Authentication Providers
  saml2IdA=realm.lookupAuthenticationProvider(IDA_NAME)
  if saml2IdA != None:
    realm.destroyAuthenticationProvider(saml2IdA)
  saml2IdA=realm.createAuthenticationProvider(IDA_NAME,'com.bea.security.saml2.providers.SAML2IdentityAsserter')
  print ">>> Authentication Provider created."

  # Reorder Authentication Providers
  defaultAtn=realm.lookupAuthenticationProvider('DefaultAuthenticator')
  defaultIdA=realm.lookupAuthenticationProvider('DefaultIdentityAsserter')
  iplanetAtn=realm.lookupAuthenticationProvider(ATN_NAME)
  realm.setAuthenticationProviders(jarray.array([saml2IdA,iplanetAtn,defaultAtn,defaultIdA],weblogic.management.security.authentication.AuthenticationProviderMBean))
  print ">>> Authentication Providers re-ordered."

except Exception, e:
  print "ERROR... check error messages for cause."
  print e
  exit(exitcode=1)

redirect('/dev/null','false')
save()
activate()
disconnect()
exit(exitcode=0)
[weblogic@weblogic-server-0 ~]$

So let’s execute this script then:

[weblogic@weblogic-server-0 ~]$ ls
configServiceProviders.wlst  createSAML2AuthenticationProviders.wlst  createWebSSOIdPPartners.wlst  domain.properties  idp_metadata.xml
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$ $ORACLE_HOME/oracle_common/common/bin/wlst.sh createSAML2AuthenticationProviders.wlst

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

>>> Loaded the properties file: /home/weblogic/domain.properties
>>> Connected to the AdminServer.
>>> Edit Session started.
>>> Authentication Provider created.
>>> Authentication Providers re-ordered.
[weblogic@weblogic-server-0 ~]$

As mentioned previously, you will need to restart the WebLogic Domain at this point. Once done, you can continue with the next part which is to create the IdP Partner. Using the same properties file and another WLST script:

[weblogic@weblogic-server-0 ~]$ cat createWebSSOIdPPartners.wlst
##################################################################
#
# Authors: Morgan Patou    
# Version: 1.4 - 30/08/2019
#
# File: createWebSSOIdPPartners.wlst
# Purpose: Script to create a WebSSO IdP Partner
# Parameters: input properties file (optional)
# Output:
#
##################################################################

# Get operating system (for vars)
import os

# Read the domain properties file
try:
  if len(sys.argv) == 2:
    domainProperties=sys.argv[1]
  else:
    domainProperties=os.path.realpath(os.path.dirname(sys.argv[0])) + "/domain.properties"
  loadProperties(domainProperties)
  print ">>> Loaded the properties file: " + domainProperties
  print

except:
  exit(exitcode=1)

try:
  redirect('/dev/null','false')
  # Connect to AdminServer
  connect(userConfigFile=CONFIG_FILE,userKeyFile=KEY_FILE,url=ADMIN_URL)
  print ">>> Connected to the AdminServer."
  stopRedirect()

  # Get default Realm
  realm=cmo.getSecurityConfiguration().getDefaultRealm()

  # Config Web SSO IdP Partner
  saml2IdA=realm.lookupAuthenticationProvider(IDA_NAME)
  if saml2IdA != None:
    if saml2IdA.idPPartnerExists(IDP_NAME):
      saml2IdA.removeIdPPartner(IDP_NAME)
    idpPartner=saml2IdA.consumeIdPPartnerMetadata(IDP_METADATA)
    idpPartner.setName(IDP_NAME)
    idpPartner.setEnabled(Boolean(IDP_ENABLED))
    idpPartner.setRedirectURIs(array(eval(IDP_REDIRECT_URIS),java.lang.String))
    saml2IdA.addIdPPartner(idpPartner)
  print ">>> Web SSO IdP Partner created."

except Exception, e:
  print "ERROR... check error messages for cause."
  print e
  exit(exitcode=1)

redirect('/dev/null','false')
disconnect()
exit(exitcode=0)
[weblogic@weblogic-server-0 ~]$

As you can see above, this one doesn’t require an edit session and therefore can’t be recorded. The key part above is the “consumeIdPPartnerMetadata(…)” method which is loading the metadata file that was generated by the Identity Provider (“Server side“). It will take care of setting up the SSL Certificate for the Identity Provider as well as all the usable URLs, aso… The path and name of this input metadata file can be found in the properties file. The execution of the WLST is simple and smooth:

[weblogic@weblogic-server-0 ~]$ $ORACLE_HOME/oracle_common/common/bin/wlst.sh createWebSSOIdPPartners.wlst

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

>>> Loaded the properties file: /home/weblogic/domain.properties
>>> Connected to the AdminServer.
>>> Web SSO IdP Partner created.
[weblogic@weblogic-server-0 ~]$

The next step is then to configure your Managed Servers by creating the Service Providers, defining the published URL, the Entity ID and other parameters. Then generate an output metadata file for each of your Managed Servers. These output metadata files will need to be imported into the Identity Provider to close the SAML2 SSO chain. Again a new WLST script for this last part:

[weblogic@weblogic-server-0 ~]$ cat configServiceProviders.wlst
##################################################################
#
# Authors: Morgan Patou    
# Version: 1.4 - 30/08/2019
#
# File: configServiceProviders.wlst
# Purpose: Script to configure SSO Service Providers
# Parameters: input properties file (optional)
# Output:
#
##################################################################

# Get operating system (for vars)
import os

# Read the domain properties file
try:
  if len(sys.argv) == 2:
    domainProperties=sys.argv[1]
  else:
    domainProperties=os.path.realpath(os.path.dirname(sys.argv[0])) + "/domain.properties"
  loadProperties(domainProperties)
  print ">>> Loaded the properties file: " + domainProperties
  print

except:
  exit(exitcode=1)

try:
  redirect('/dev/null','false')
  # Connect to AdminServer
  connect(userConfigFile=CONFIG_FILE,userKeyFile=KEY_FILE,url=ADMIN_URL)
  print ">>> Connected to the AdminServer."

  # Start Edit Session
  edit()
  startEdit()
  stopRedirect()
  print ">>> Edit Session started."

  # Config SSO Service Providers
  publishedSiteURLs=SSO_URLS.split(',')
  entityIDs=SSO_ENTITY_IDS.split(',')
  id=0
  for ssoServerName in SSO_MS.split(','):
    ssoServer=cmo.lookupServer(ssoServerName)
    ssoService=ssoServer.getSingleSignOnServices()
    ssoService.setPublishedSiteURL(publishedSiteURLs[id])
    ssoService.setEntityID(entityIDs[id])
    ssoService.setServiceProviderEnabled(Boolean(SSO_SP_ENABLED))
    ssoService.setServiceProviderPreferredBinding(SSO_SP_BINDING)
    id=id+1
  print ">>> SSO Service Providers configured."

except Exception, e:
  print "ERROR... check error messages for cause."
  print e
  exit(exitcode=1)

redirect('/dev/null','false')
save()
activate()

try:
  # Start Runtime Session
  domainRuntime()
  stopRedirect()
  print ">>> Runtime Session started."

  # Export Service Providers metadata
  for ssoServerName in SSO_MS.split(','):
    cd('/ServerRuntimes/'+ssoServerName)
    cmo.getSingleSignOnServicesRuntime().publish('/tmp/'+ssoServerName+'_sp_metadata.xml',false)
  print ">>> Service Providers metadata files exported."

except Exception, e:
  print "ERROR... check error messages for cause."
  print e
  exit(exitcode=1)

redirect('/dev/null','false')
disconnect()
exit(exitcode=0)
[weblogic@weblogic-server-0 ~]$

So as mentioned above, the first section is looping on the Managed Servers list from the parameters to configure the SAML2 SSO for all of them. This part requires an edit session. The second section is doing the export of the Service Providers metadata files under /tmp and this doesn’t need any edit session, it needs to be done with a runtime session instead. Again, the execution:

[weblogic@weblogic-server-0 ~]$ $ORACLE_HOME/oracle_common/common/bin/wlst.sh configServiceProviders.wlst

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

>>> Loaded the properties file: /home/weblogic/domain.properties
>>> Connected to the AdminServer.
>>> Edit Session started.
>>> SSO Service Providers configured.
>>> Runtime Session started.
>>> Service Providers metadata files exported.
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$ ls /tmp/*metadata.xml
/tmp/msD2-01_sp_metadata.xml  /tmp/msD2-02_sp_metadata.xml
[weblogic@weblogic-server-0 ~]$

At that point, the WebLogic Server acting as Service Provider is fully configured. You can now transfer these metadata files to the Identity Provider side and import them there.

There is one last thing that I didn’t talk about and that’s the configuration of the Application itself, if needed, to use the SAML2 SSO. In the case of Documentum D2, it does support the LDAP + SAML2 SSO, you just have to have some basic configuration in the web.xml and weblogic.xml. There is an example I wrote a little bit more than two years ago: here.

Cet article WebLogic Server – Automatic/Silent setup of a SAML2 SSO est apparu en premier sur Blog dbi services.

In a previous blog, I explained how it is possible to create an LDAP/LDAPs connection from a Documentum Content Server automatically/silently (without any need for a GUI). So I thought I would do the same thing but from a WebLogic Server to have the full chain from the Application to the backend, all connected to the LDAP/LDAPs. This blog isn’t linked to Documentum, it is really just WebLogic Server specific so if you want to do the same but for another application, that’s also what you need to do. There are plenty of blogs on the internet about how to configure WebLogic but they are (almost?) always using the GUI… Which is good because it’s simple, but it is also annoying because you cannot really automate that.

As mentioned in this subsequent blog, my goal was a little bit more than just an LDAP setup so I first searched about any hints on what would be needed to setup everything. The only thing I found that was a little bit helpful was actually the examples that are shipped with the OFM (if you included them). We usually install only the minimal requirements so we don’t have the examples but you can choose to have the examples as well when you install the binaries. In the silent properties file, you can just set the install type to include “… With Examples“. Inside these examples, there is a SAML2 SSO one which seems pretty complex. There is a plethora of files for the purpose of the example obviously but most of that is completely useless outside of this scope. Also, from what I could see, it was designed for a WebLogic Server 9 so that seemed to be pretty old… Since I was using WLS 12c, I obviously expected a lot of things going wrong. It was nonetheless a good starting point to have some details about where can you find the needed elements in WLST but you will still need a lot of knowledge in WLS and WLST to be able to make something out of it. That’s where this blog comes in.

For the LDAP Authentication Provider creation, you can also record the execution from the Administration Console, it will gives you good information about what needs to be done (at least for this part).

The first thing to do to setup a LDAPs (it doesn’t apply to a plain LDAP) is to add the LDAPs SSL Certificate chain into the WebLogic Server’s trust store:

[weblogic@weblogic-server-0 ~]$ cert_location="/tmp/certs"
[weblogic@weblogic-server-0 ~]$ ssl_ldap_root_ca_file="LDAP_Root_CA.cer"
[weblogic@weblogic-server-0 ~]$ ssl_ldap_int_ca_file="LDAP_Int_CA.cer"
[weblogic@weblogic-server-0 ~]$ tks_file="$DOMAIN_HOME/certs/trust.jks"
[weblogic@weblogic-server-0 ~]$ tks_pwd="MyP4ssw0rd"
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$ $JAVA_HOME/bin/keytool -import -trustcacerts -alias ssl_ldap_root_ca -file ${cert_location}/${ssl_ldap_root_ca_file} -keystore ${tks_file} -storepass ${tks_pwd} -noprompt
Certificate was added to keystore
[weblogic@weblogic-server-0 ~]$ $JAVA_HOME/bin/keytool -import -trustcacerts -alias ssl_ldap_int_ca -file ${cert_location}/${ssl_ldap_int_ca_file} -keystore ${tks_file} -storepass ${tks_pwd} -noprompt
Certificate was added to keystore
[weblogic@weblogic-server-0 ~]$

Once that is done, you can start the creation of the LDAP Authentication Provider. To be able to automate that, the best for me is to use a WLST script. Make sure the AdminServer is up, running and reachable before trying to execute a WLST script. I put all my parameters in a properties file and I’m loading this file in the WLST so that it creates the correct object with all the needed parameters. Here are the properties and the WLST script to create the LDAP (you can disregard the IdP and Managed Servers parameters, they are only used for the SAML2 SSO part in the other blog):

[weblogic@weblogic-server-0 ~]$ cat domain.properties
# AdminServer parameters
CONFIG_FILE=/home/weblogic/secure/configfile.secure
KEY_FILE=/home/weblogic/secure/keyfile.secure
ADMIN_URL=t3s://weblogic-server-0.domain.com:8443
# LDAP Authentication Providers parameters
ATN_NAME=Internal_LDAP
ATN_FLAG=SUFFICIENT
ATN_HOST=ldap.domain.com
ATN_PORT=636
ATN_PRINCIPAL=ou=APP,ou=applications,ou=intranet,dc=dbi services,dc=com
ATN_CREDENTIAL=T3stP4ssw0rd
ATN_SSL=true
ATN_BASE_DN=ou=people,ou=intranet,dc=dbi services,dc=com
ATN_USER_FILTER=(&(uid=%u)(objectclass=person))
ATN_USER_CLASS=person
ATN_USER_AS_PRINCIPAL=true
ATN_GROUP_FILTER=(&(cn=%g)(objectclass=groupofuniquenames))
ATN_TIMEOUT=30
# IdP Partner parameters
IDA_NAME=APP_SAML2_IDAsserter
IDP_NAME=APP_SAML2_IDPartner
IDP_METADATA=/home/weblogic/idp_metadata.xml
IDP_ENABLED=true
IDP_REDIRECT_URIS=['/D2-01/*','/D2-02/*']
# Managed Servers SSO parameters
SSO_MS=msD2-01,msD2-02
SSO_URLS=https://lb_url1/saml2,https://lb_url2/saml2
SSO_ENTITY_IDS=APP_SAML2_Entity_ID_01,APP_SAML2_Entity_ID_02
SSO_SP_ENABLED=true
SSO_SP_BINDING=HTTP/POST
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$ cat createLDAPAuthenticationProviders.wlst
##################################################################
#
# Authors: Morgan Patou    
# Version: 1.4 - 30/08/2019
#
# File: createLDAPAuthenticationProviders.wlst
# Purpose: Script to create LDAP/LDAPs Authentication Providers
# Parameters: input properties file (optional)
# Output:
#
##################################################################

# Get operating system (for vars)
import os

# Read the domain properties file
try:
  if len(sys.argv) == 2:
    domainProperties=sys.argv[1]
  else:
    domainProperties=os.path.realpath(os.path.dirname(sys.argv[0])) + "/domain.properties"
  loadProperties(domainProperties)
  print ">>> Loaded the properties file: " + domainProperties
  print

except:
  exit(exitcode=1)

try:
  redirect('/dev/null','false')
  # Connect to AdminServer
  connect(userConfigFile=CONFIG_FILE,userKeyFile=KEY_FILE,url=ADMIN_URL)
  print ">>> Connected to the AdminServer."

  # Start Edit Session
  edit()
  startEdit()
  stopRedirect()
  print ">>> Edit Session started."

  # Get default Realm
  realm=cmo.getSecurityConfiguration().getDefaultRealm()

  # Create Authentication Providers
  iplanetAtn=realm.lookupAuthenticationProvider(ATN_NAME)
  if iplanetAtn != None:
    realm.destroyAuthenticationProvider(iplanetAtn)
  iplanetAtn=realm.createAuthenticationProvider(ATN_NAME,'weblogic.security.providers.authentication.IPlanetAuthenticator')
  print ">>> Authentication Provider created."

  # Config Authentication Providers
  iplanetAtn.setControlFlag(ATN_FLAG)
  iplanetAtn.setHost(ATN_HOST)
  iplanetAtn.setPort(int(ATN_PORT))
  iplanetAtn.setPrincipal(ATN_PRINCIPAL)
  iplanetAtn.setCredential(ATN_CREDENTIAL)
  iplanetAtn.setSSLEnabled(Boolean(ATN_SSL))
  iplanetAtn.setUserBaseDN(ATN_BASE_DN)
  iplanetAtn.setUserFromNameFilter(ATN_USER_FILTER)
  iplanetAtn.setUserObjectClass(ATN_USER_CLASS)
  iplanetAtn.setUseRetrievedUserNameAsPrincipal(Boolean(ATN_USER_AS_PRINCIPAL))
  iplanetAtn.setGroupBaseDN(ATN_PRINCIPAL)
  iplanetAtn.setGroupFromNameFilter(ATN_GROUP_FILTER)
  iplanetAtn.setConnectTimeout(int(ATN_TIMEOUT))
  print ">>> Authentication Provider configured."

  # Reorder Authentication Providers
  defaultAtn=realm.lookupAuthenticationProvider('DefaultAuthenticator')
  defaultIdA=realm.lookupAuthenticationProvider('DefaultIdentityAsserter')
  realm.setAuthenticationProviders(jarray.array([iplanetAtn,defaultAtn,defaultIdA],weblogic.management.security.authentication.AuthenticationProviderMBean))
  print ">>> Authentication Providers re-ordered."

except Exception, e:
  print "ERROR... check error messages for cause."
  print e
  exit(exitcode=1)

redirect('/dev/null','false')
save()
activate()
disconnect()
exit(exitcode=0)
[weblogic@weblogic-server-0 ~]$

With the above, you have everything needed to simply create an LDAP Authentication Provider. I won’t really describe what the WLST script is doing, I believe it is pretty self-explanatory and there is a commented line before each section which describes the use of the commands. If you have any questions, please feel free to ask them in the comments below! I used an IPlanet Authenticator but you can obviously choose something else. I also set the group base dn as my principal because I don’t need the groups but you can set whatever you want/need. There are other properties as well that you can set, just check them in WLST to have the correct method name (or use the record method as mentioned previously). In the above WLST script, the last thing done is also to re-order the Authentication Providers so that the newly created LDAP one is the first to be checked. The control flag is set as “SUFFICIENT“, meaning that if the authentication is successful for the LDAP, then WebLogic can proceed. For the LDAP user’s principal and password, you can also use an encrypted file containing the username and password with the “setEncrypted(…)” method instead.

To execute the WLST script and therefore create the LDAP Authentication Provider, just execute the script:

[weblogic@weblogic-server-0 ~]$ ls
createLDAPAuthenticationProviders.wlst  domain.properties
[weblogic@weblogic-server-0 ~]$
[weblogic@weblogic-server-0 ~]$ $ORACLE_HOME/oracle_common/common/bin/wlst.sh createLDAPAuthenticationProviders.wlst

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

>>> Loaded the properties file: /home/weblogic/domain.properties
>>> Connected to the AdminServer.
>>> Edit Session started.
>>> Authentication Provider created.
>>> Authentication Provider configured.
>>> Authentication Providers re-ordered.
[weblogic@weblogic-server-0 ~]$

As shown above, you can put a parameter to the script with the full path and name of the properties file to be loaded. Alternatively, if you do not provide any parameter, it will assume that the properties file is located just beside the WLST script with a certain name (“domain.properties” by default). In all cases, once the LDAP Authentication Provider has been created, you will need to restart the full Domain. That’s all there is to do to create an LDAP/LDAPs connection on WebLogic Server.

Cet article WebLogic Server – Automatic/Silent creation of an LDAP Authentication Provider est apparu en premier sur Blog dbi services.