Feed aggregator

PostgreSQL partitioning (8): Sub-partitioning

Yann Neuhaus - Tue, 2019-06-18 04:19

We are slowly coming to the end of this little series about partitioning in PostgreSQL. In the last post we had a look at indexing and constraints and today we will have a look at sub partitioning. Sub partitioning means you go one step further and partition the partitions as well. Although it is not required to read all the posts of this series to follow this one: If you want, here they are:

  1. PostgreSQL partitioning (1): Preparing the data set
  2. PostgreSQL partitioning (2): Range partitioning
  3. PostgreSQL partitioning (3): List partitioning
  4. PostgreSQL partitioning (4) : Hash partitioning
  5. PostgreSQL partitioning (5): Partition pruning
  6. PostgreSQL partitioning (6): Attaching and detaching partitions
  7. PostgreSQL partitioning (7): Indexing and constraints

Coming back to our range partitioned table this is how it looks like currently:

postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
            traffic_violations_p_default DEFAULT

Lets assume that you expect that traffic violations will grow exponentially in 2022 because more and more cars will be on the road and when there will be more cars there will be more traffic violations. To be prepared for that you do not only want to partition by year but also by month. In other words: Add a new partition for 2022 but sub partition that by month. First of all you need a new partition for 2022 that itself is partitioned as well:

create table traffic_violations_p_2022
partition of traffic_violations_p
for values from ('2022-01-01') to ('2023-01-01') partition by range(date_of_stop);

Now we can add partitions to the just created partitioned partition:

create table traffic_violations_p_2022_jan
partition of traffic_violations_p_2022
for values from ('2022-01-01') to ('2022-02-01');

create table traffic_violations_p_2022_feb
partition of traffic_violations_p_2022
for values from ('2022-02-01') to ('2022-03-01');

create table traffic_violations_p_2022_mar
partition of traffic_violations_p_2022
for values from ('2022-03-01') to ('2022-04-01');

create table traffic_violations_p_2022_apr
partition of traffic_violations_p_2022
for values from ('2022-04-01') to ('2022-05-01');

create table traffic_violations_p_2022_may
partition of traffic_violations_p_2022
for values from ('2022-05-01') to ('2022-06-01');

create table traffic_violations_p_2022_jun
partition of traffic_violations_p_2022
for values from ('2022-06-01') to ('2022-07-01');

create table traffic_violations_p_2022_jul
partition of traffic_violations_p_2022
for values from ('2022-07-01') to ('2022-08-01');

create table traffic_violations_p_2022_aug
partition of traffic_violations_p_2022
for values from ('2022-08-01') to ('2022-09-01');

create table traffic_violations_p_2022_sep
partition of traffic_violations_p_2022
for values from ('2022-09-01') to ('2022-10-01');

create table traffic_violations_p_2022_oct
partition of traffic_violations_p_2022
for values from ('2022-10-01') to ('2022-11-01');

create table traffic_violations_p_2022_nov
partition of traffic_violations_p_2022
for values from ('2022-11-01') to ('2022-12-01');

create table traffic_violations_p_2022_dec
partition of traffic_violations_p_2022
for values from ('2022-12-01') to ('2023-01-01');

Looking at psql’s output when we describe the partitioned table not very much changed, just the keyword “PARTITIONED” is showing up beside our new partition for 2022:

postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
            traffic_violations_p_2022 FOR VALUES FROM ('2022-01-01') TO ('2023-01-01'), PARTITIONED,
            traffic_violations_p_default DEFAULT

The is where the new functions in PostgreSQL 12 become very handy:

postgres=# select * from pg_partition_tree('traffic_violations_p');
             relid             |        parentrelid        | isleaf | level 
-------------------------------+---------------------------+--------+-------
 traffic_violations_p          |                           | f      |     0
 traffic_violations_p_default  | traffic_violations_p      | t      |     1
 traffic_violations_p_2013     | traffic_violations_p      | t      |     1
 traffic_violations_p_2014     | traffic_violations_p      | t      |     1
 traffic_violations_p_2015     | traffic_violations_p      | t      |     1
 traffic_violations_p_2016     | traffic_violations_p      | t      |     1
 traffic_violations_p_2017     | traffic_violations_p      | t      |     1
 traffic_violations_p_2018     | traffic_violations_p      | t      |     1
 traffic_violations_p_2019     | traffic_violations_p      | t      |     1
 traffic_violations_p_2020     | traffic_violations_p      | t      |     1
 traffic_violations_p_2021     | traffic_violations_p      | t      |     1
 traffic_violations_p_2022     | traffic_violations_p      | f      |     1
 traffic_violations_p_2022_jan | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_feb | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_mar | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_apr | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_may | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_jun | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_jul | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_aug | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_sep | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_oct | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_nov | traffic_violations_p_2022 | t      |     2
 traffic_violations_p_2022_dec | traffic_violations_p_2022 | t      |     2

To verify if data is routed correctly to the sub partitions let’s add some data for 2022:

insert into traffic_violations_p (date_of_stop)
       select * from generate_series ( date('01-01-2022')
                                     , date('12-31-2022')
                                     , interval '1 day' );

If we did the partitioning correctly we should see data in the new partitions:

postgres=# select count(*) from traffic_violations_p_2022_nov;
 count 
-------
    30
(1 row)

postgres=# select count(*) from traffic_violations_p_2022_dec;
 count 
-------
    31
(1 row)

postgres=# select count(*) from traffic_violations_p_2022_feb;
 count 
-------
    28
(1 row)

Here we go. Of course you could go even further and sub-partition the monthly partitions further by day or week. You can also partition by list and then sub-partition the list partitions by range. Or partition by range and then sub-partition by list, e.g.:

postgres=# create table traffic_violations_p_list_dummy partition of traffic_violations_p_list for values in ('dummy') partition by range (date_of_stop);
CREATE TABLE
postgres=# create table traffic_violations_p_list_dummy_2019 partition of traffic_violations_p_list_dummy for values from ('2022-01-01') to ('2023-01-01');
CREATE TABLE
postgres=# insert into traffic_violations_p_list (seqid, violation_type , date_of_stop) values (-1,'dummy',date('2022-12-01'));
INSERT 0 1
postgres=# select date_of_stop,violation_type from traffic_violations_p_list_dummy_2019;
 date_of_stop | violation_type 
--------------+----------------
 2022-12-01   | dummy
(1 row)

That’s it for sub-partitioning. In the final post we will look at some corner cases with partitioning in PostgreSQL.

Cet article PostgreSQL partitioning (8): Sub-partitioning est apparu en premier sur Blog dbi services.

Looking for errors in the Clusterware and RAC logs? Dash through using the TFA Collector

VitalSoftTech - Mon, 2019-06-17 09:49

The Oracle Trace File analyzer utility has been originally developed by Oracle to help collect and bundle up all the pertinent diagnostic data in the log files, tracefiles, os statistics, etc.. This is a very common task when Oracle Support engineers request this information to help troubleshoot issues and bugs.

The post Looking for errors in the Clusterware and RAC logs? Dash through using the TFA Collector appeared first on VitalSoftTech.

Categories: DBA Blogs

Can’t Unnest

Jonathan Lewis - Mon, 2019-06-17 09:35

In an echo of a very old “conditional SQL” posting, a recent posting on the ODC general database discussion forum ran into a few classic errors of trouble-shooting. By a lucky coincidence this allowed me to rediscover and publish an old example of parallel execution gone wild before moving on to talk about the fundamental problem exhibited in the latest query.

The ODC thread started with a question along the lines of “why isn’t Oracle using the index I hinted”, with the minor variation that it said “When I hint my SQL with an index hint it runs quickly so I’ve created a profile that applies the hint, but the hint doesn’t get used in production.”

The query was a bit messy and, as is often the case with ODC, the formatting wasn’t particularly readable, so I’ve extracted the where clause from the SQL that was used to generate the profile and reformatted it below. See if you can spot the hint clue that tells you why there might be a big problem using this SQL to generate a profile to use in the production environment:


WHERE   
        MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' 
AND     MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' 
AND     MSG.SRCH_4_FLD_VAL = '123456'   
AND     (
            (    'INVOICENUMBER' = 'INVOICENUMBER' 
             AND MSG.MSG_ID IN (
                        SELECT  *   
                        FROM    TABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR)))
            ) 
         OR (    'INVOICENUMBER' = 'SIEBELORDERID' 
             AND MSG.SRCH_3_FLD_VAL IN (
                        SELECT  *   
                        FROM    TABLE(CAST(FNM_GN_IN_STRING_LIST('') AS TABLE_OF_VARCHAR)))
            )
        ) 
AND     MSG.MSG_ID = TRK.INV_NUM(+) 
AND     (   TRK.RESEND_DT IS NULL 
         OR TRK.RESEND_DT = (
                        SELECT  MAX(TRK1.RESEND_DT)   
                        FROM    FNM.BCS_INV_RESEND_TRK TRK1   
                        WHERE   TRK1.INV_NUM = TRK.INV_NUM
                )
        )

If the SQL by itself doesn’t give you an inportant clue, compare it with the Predicate Information from the “good” execution plan that it produced:


Predicate Information (identified by operation id):  
---------------------------------------------------  
   2 - filter(("TRK"."RESEND_DT" IS NULL OR "TRK"."RESEND_DT"=))  
   8 - filter(("MSG"."SRCH_4_FLD_VAL"='123456' AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))  
   9 - access("MSG"."MSG_ID"="COLUMN_VALUE" AND "MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')  
       filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')  
  10 - access("MSG"."MSG_ID"="TRK"."INV_NUM")  
  13 - access("TRK1"."INV_NUM"=:B1)  

Have you spotted the thing that isn’t there in the predicate information ?

What happened to the ‘INVOICENUMBER’ = ‘INVOICENUMBER’ predicate and the ‘INVOICENUMBER’ = ‘SIEBELORDERID’ predicate? They’ve disappeared because the optimizer knows that the first predicate is always true and doesn’t need to be tested at run-time and the second one is always false and doesn’t need to be tested at run-time. Moreover both predicates are part of a conjunct (AND) – so in the second case the entire two-part predicate can be eliminated; so the original where clause can immediately be reduced to:


WHERE   
        MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' 
AND     MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' 
AND     MSG.SRCH_4_FLD_VAL = '123456'   
AND     (
                 MSG.MSG_ID IN (
                        SELECT  *   
                        FROM    TABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR)))
        ) 
AND     MSG.MSG_ID = TRK.INV_NUM(+) 
AND     (   TRK.RESEND_DT IS NULL 
         OR TRK.RESEND_DT = (
                        SELECT  MAX(TRK1.RESEND_DT)   
                        FROM    FNM.BCS_INV_RESEND_TRK TRK1   
                        WHERE   TRK1.INV_NUM = TRK.INV_NUM
                )
        )

Looking at this reduced predicate you may note that the IN subquery referencing the fnm_gn_in_string_list() collection could now be unnested and used to drive the final execution plan, and the optimizer will even recognize that it’s a rowsource with at most one row. So here’s the “good” execution plan:


---------------------------------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                               | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |  
---------------------------------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                        |                       |      1 |        |      2 |00:00:00.08 |      12 |      7 |       |       |          |  
|   1 |  SORT ORDER BY                          |                       |      1 |      1 |      2 |00:00:00.08 |      12 |      7 |  2048 |  2048 | 2048  (0)|  
|*  2 |   FILTER                                |                       |      1 |        |      2 |00:00:00.08 |      12 |      7 |       |       |          |  
|   3 |    NESTED LOOPS OUTER                   |                       |      1 |      1 |      2 |00:00:00.08 |      10 |      7 |       |       |          |  
|   4 |     NESTED LOOPS                        |                       |      1 |      1 |      2 |00:00:00.06 |       6 |      5 |       |       |          |  
|   5 |      VIEW                               | VW_NSO_1              |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |       |       |          |  
|   6 |       HASH UNIQUE                       |                       |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |  1697K|  1697K|  487K (0)|  
|   7 |        COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |       |       |          |  
|*  8 |      TABLE ACCESS BY INDEX ROWID        | FNM_VSBL_MSG          |      1 |      1 |      2 |00:00:00.06 |       6 |      5 |       |       |          |  
|*  9 |       INDEX RANGE SCAN                  | XIE2FNM_VSBL_MSG      |      1 |      4 |      4 |00:00:00.04 |       4 |      3 |       |       |          |  
|* 10 |     INDEX RANGE SCAN                    | XPKBCS_INV_RESEND_TRK |      2 |      1 |      2 |00:00:00.01 |       4 |      2 |       |       |          |  
|  11 |    SORT AGGREGATE                       |                       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |  
|  12 |     FIRST ROW                           |                       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |  
|* 13 |      INDEX RANGE SCAN (MIN/MAX)         | XPKBCS_INV_RESEND_TRK |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |  
---------------------------------------------------------------------------------------------------------------------------------------------------------------  

The plan looks great – Oracle predicts a single row driver (operation 5) which can use a very good index (XIE2FNM_VSBL_MSG) in a nested loop, followed by a second nested loop, followed by a filter subquery and a sort of a tiny amount of data. Predictions match actuals all the way down the plan, and the workload is tiny. So what goes wrong in production?

You’ve probably guessed the flaw in this test. Why would anyone include a predicate like ‘INVOICENUMBER’ = ‘INVOICENUMBER’ in production code, or even worse ‘INVOICENUMBER’ = ‘SIEBELORDERID’. The OP has taken a query using bind variables picked up the actual values that were peeked when the query was executed, and substituted them into the test as literals. This has allowed the optimizer to discard two simple predicates and one subquery when the production query would need a plan that catered for the possibility that the second subquery would be the one that had to be executed and the first one bypassed. Here’s the corrected where clause using SQL*Plus variables (not the substitution type, the proper type) for the original bind variables:


WHERE
        MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE'
AND     MSG.MSG_CAPTR_STG_CD = 'PRE_BCS'
AND     MSG.SRCH_4_FLD_VAL = :BindInvoiceTo
AND     (
            (    :BindSearchBy = 'INVOICENUMBER' 
             AND MSG.MSG_ID IN (
                        SELECT  *
                        FROM    TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS TABLE_OF_VARCHAR)))
            )
         OR (    :BindSearchBy = 'SIEBELORDERID' 
             AND MSG.SRCH_3_FLD_VAL IN (
                        SELECT  *
                        FROM    TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS TABLE_OF_VARCHAR)))
            )
        )
AND     MSG.MSG_ID = TRK.INV_NUM(+)
AND     (   TRK.RESEND_DT IS NULL
         OR TRK.RESEND_DT = (
                        SELECT  MAX(TRK1.RESEND_DT)
                        FROM    FNM.BCS_INV_RESEND_TRK TRK1
                        WHERE   TRK1.INV_NUM = TRK.INV_NUM
                )
        )

And this, with the “once good” hint in place to force the use of the XIE2FNM_VSBL_MSG index, is the resulting execution plan


---------------------------------------------------------------------------------------------------------  
| Id  | Operation                           | Name                  | E-Rows |  OMem |  1Mem | Used-Mem |  
---------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                    |                       |        |       |       |          |  
|   1 |  SORT ORDER BY                      |                       |      1 | 73728 | 73728 |          |  
|*  2 |   FILTER                            |                       |        |       |       |          |  
|   3 |    NESTED LOOPS OUTER               |                       |      1 |       |       |          |  
|*  4 |     TABLE ACCESS BY INDEX ROWID     | FNM_VSBL_MSG          |      1 |       |       |          |  
|*  5 |      INDEX FULL SCAN                | XIE2FNM_VSBL_MSG      |   4975K|       |       |          |  
|*  6 |     INDEX RANGE SCAN                | XPKBCS_INV_RESEND_TRK |      1 |       |       |          |  
|*  7 |    COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |      1 |       |       |          |  
|*  8 |    COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |      1 |       |       |          |  
|   9 |    SORT AGGREGATE                   |                       |      1 |       |       |          |  
|  10 |     FIRST ROW                       |                       |      1 |       |       |          |  
|* 11 |      INDEX RANGE SCAN (MIN/MAX)     | XPKBCS_INV_RESEND_TRK |      1 |       |       |          |  
---------------------------------------------------------------------------------------------------------  
 
Predicate Information (identified by operation id):  
---------------------------------------------------  
   2 - filter((((:BINDSEARCHBY='INVOICENUMBER' AND  IS NOT NULL) OR  
              (:BINDSEARCHBY='SIEBELORDERID' AND  IS NOT NULL)) AND ("TRK"."RESEND_DT" IS NULL OR  
              "TRK"."RESEND_DT"=)))  
   4 - filter(("MSG"."SRCH_4_FLD_VAL"=:BINDINVOICETO AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))  
   5 - access("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')  
       filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')  
   6 - access("MSG"."MSG_ID"="TRK"."INV_NUM")  
   7 - filter(VALUE(KOKBF$)=:B1)  
   8 - filter(VALUE(KOKBF$)=:B1)  
  11 - access("TRK1"."INV_NUM"=:B1)  

The “unnested driving subquery” approach can no longer be used – we now start with the fnm_vsbl_msg table (accessing it using a most inefficient execution path because that’s what the hint does for us, and we can obey the hint), and for each row check which of the two subqueries we need to execute. There is, in fact, no way we can hint this query to operate efficiently [at least, that’s my opinion, .I may be wrong].

The story so far

If you’re going to try to use SQL*Plus (or similar) to test a production query with bind variables you can’t just use a sample of literal values in place of the bind variables (though you may get lucky sometimes, of course), you should set up some SQL*Plus variables and assign values to them.

Though I haven’t said it presiously in this article this is an example where a decision that really should have been made by the front-end code has been embedded in the SQL and passed to the database as SQL which cannot be run efficiently. The front end code should have been coded to recognise the choice between invoice numbers and Siebel order ids and sent the appropriate query to the database.

Next Steps

WIthout making a significant change to the front-end mechanism wrapper is it possible to change the SQL so something the optimizer can handle efficiently? Sometimes the answer is yes; so I’ve created a simpler model to demonstrate the basic problem and supply a solution for cases like this one. The key issue is finding a way of working around the OR clauses that are trying to allow the optimizer to choose between two subqueries but make it impossible for either to be unnested into a small driving data set.

First, some tables:


rem
rem     Script:         or_in_twice.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2019
rem
rem     Last tested 
rem             18.3.0.0
rem             12.2.0.1
rem

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

alter table t1 add constraint t1_pk primary key(id);

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

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


Now a query – first setting up a variable in SQL*Plus to allow us to emulate a production query with bind variables. Since I’m only going to use Explain Plan the variable won’t be peekable, so there would still be some scope for this plan not matching a production plan, but it’s adequate to demonstrate the structural problem:


variable v1 varchar2(10)
exec :v1 := 'INVOICE'

explain plan for
select
        t1.v1 
from
        t1
where
        (
            :v1 = 'INVOICE' 
        and t1.id in (select id from t2 where n1 = 0)
        )
or      (
            :v1 = 'ORDERID' 
        and t1.id in (select id from t3 where n1 = 0)
        )
;

select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   150 |    26   (4)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   146K|    26   (4)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     8 |    26   (4)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| T3   |     1 |     8 |    26   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:V1='INVOICE' AND  EXISTS (SELECT 0 FROM "T2" "T2" WHERE
              "ID"=:B1 AND "N1"=0) OR :V1='ORDERID' AND  EXISTS (SELECT 0 FROM "T3"
              "T3" WHERE "ID"=:B2 AND "N1"=0))
   3 - filter("ID"=:B1 AND "N1"=0)
   4 - filter("ID"=:B1 AND "N1"=0)

As you can see, thanks to the OR that effectively gives Oracle the choice between running the subquery against t3 or the one against t2, Oracle is unable to do any unnesting. (In fact different versions of Oracle allow different levels of sophistication with disjuncts (OR) of subqueries, so this is the kind of example that’s always useful to keep for tests against future versions.)

Since we know that we are going to use one of the data sets supplied in one of the subqueries and have no risk of double-counting or eliminating required duplicates, one strategy we could adopt for this query is to rewrite the two subqueries as a single subquery with a union all – because we know the optimizer can usually handle a single IN subquery very nicely. So let’s try the following:


explain plan for
select
        t1.v1
from
        t1
where
        t1.id in (
                select  id 
                from    t2 
                where   n1 = 0
                and     :v1 = 'INVOICE'
                union all
                select  id 
                from    t3 
                where   n1 = 0
                and     :v1 = 'ORDERID'
        )
;

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    54 |  1512 |    77   (3)| 00:00:01 |
|*  1 |  HASH JOIN             |          |    54 |  1512 |    77   (3)| 00:00:01 |
|   2 |   VIEW                 | VW_NSO_1 |    54 |   702 |    51   (2)| 00:00:01 |
|   3 |    HASH UNIQUE         |          |    54 |   432 |    51   (2)| 00:00:01 |
|   4 |     UNION-ALL          |          |       |       |            |          |
|*  5 |      FILTER            |          |       |       |            |          |
|*  6 |       TABLE ACCESS FULL| T2       |    27 |   216 |    26   (4)| 00:00:01 |
|*  7 |      FILTER            |          |       |       |            |          |
|*  8 |       TABLE ACCESS FULL| T3       |    27 |   216 |    26   (4)| 00:00:01 |
|   9 |   TABLE ACCESS FULL    | T1       | 10000 |   146K|    26   (4)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="ID")
   5 - filter(:V1='INVOICE')
   6 - filter("N1"=0)
   7 - filter(:V1='ORDERID')
   8 - filter("N1"=0)


Thanks to the FILTERs at operations 5 and 7 this plan will pick the data from just one of the two subqueries, reduce it to a unique list and then use that as the build table to a hash join. Of course, with different data (or suitable hints) that hash join could become a nested loop using a high precision index.

But there’s an alternative. We manually rewrote the two subqueries as a single union all subquery and as we did so we moved the bind variable comparisons inside their respective subqueries; maybe we don’t need to introduce the union all. What would happen if we simply take the original query and move the “constant” predicates inside their subqueries?


explain plan for
select
        t1.v1
from
        t1
where
        t1.id in (select id from t2 where n1 = 0 and :v1 = 'INVOICE')
or      t1.id in (select id from t3 where n1 = 0 and :v1 = 'ORDERID')
;

select * from table(dbms_xplan.display);


-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    54 |  1512 |    77   (3)| 00:00:01 |
|*  1 |  HASH JOIN             |          |    54 |  1512 |    77   (3)| 00:00:01 |
|   2 |   VIEW                 | VW_NSO_1 |    54 |   702 |    51   (2)| 00:00:01 |
|   3 |    HASH UNIQUE         |          |    54 |   432 |    51   (2)| 00:00:01 |
|   4 |     UNION-ALL          |          |       |       |            |          |
|*  5 |      FILTER            |          |       |       |            |          |
|*  6 |       TABLE ACCESS FULL| T3       |    27 |   216 |    26   (4)| 00:00:01 |
|*  7 |      FILTER            |          |       |       |            |          |
|*  8 |       TABLE ACCESS FULL| T2       |    27 |   216 |    26   (4)| 00:00:01 |
|   9 |   TABLE ACCESS FULL    | T1       | 10000 |   146K|    26   (4)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="ID")
   5 - filter(:V1='ORDERID')
   6 - filter("N1"=0)
   7 - filter(:V1='INVOICE')
   8 - filter("N1"=0)

In 12.2.0.1 and 18.3.0.0 it gets the same plan as we did with our “single subquery” rewrite – the optimizer is able to construct the union all single subquery (although the ordering of the subqueries has been reversed) and unnest without any other manual intervention. (You may find that earlier versions of Oracle don’t manage to do this, but you might have to go all the way back to 10g.

Conclusion

Oracle doesn’t like disjuncts (OR) and finds conjuncts (AND) much easier to cope with. Mixing OR and subqueries is a good way to create inefficient execution plans, especially when you try to force the optimizer to handle a decision that should have been taken in the front-end code. The optimizer, however, gets increasingly skilled at handling the mixture as you move through the newer versions; but you may have to find ways to give it a little help if you see it running subqueries as filter subqueries when you’re expecting it to unnest a subquery to produce a small driving data set.

 

Video : Ranking using RANK, DENSE_RANK and ROW_NUMBER : Problem Solving using Analytic Functions

Tim Hall - Mon, 2019-06-17 02:36

Today’s video is a run through ranking data using the RANK, DENSE_RANK and ROW_NUMBER analytic functions.

There is more information about these and other analytic functions in the following articles.

The star of today’s video is Chris Saxon, who is one of the folks keeping the masses up to speed at AskTom.

Cheers

Tim…

Video : Ranking using RANK, DENSE_RANK and ROW_NUMBER : Problem Solving using Analytic Functions was first posted on June 17, 2019 at 8:36 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Connecting to a Repository via a Dynamically Edited dfc.properties File (part II)

Yann Neuhaus - Sun, 2019-06-16 13:37

This is part II of the 2-part article. See for part I of this article.

Testing

We will test on the host machine named docker that hosts 2 containers, container01 and container011. All 3 machines run a repository. Its name is respectively dmtest on docker (shortly, dmtest@docker:1489), dmtest01@container01:1489 (dmtest01@container01:2489 externally) and dmtest01@container011:1489 (dmtest01@container011:5489 externally). Incidentally, the enhanced syntax is also a good way to uniquely identify the repositories.
The current dfc.properties file on the host docker:

$ grep docbroker /app/dctm/config/dfc.properties
dfc.docbroker.host[0]=docker
dfc.docbroker.port[0]=1489

This is used for the local docbase dmtest.
Let’s tag all the docbases for an easy identification later:

$ iapi dmtest -Udmadmin -Pdmadmin <<eoq
retrieve,c,dm_docbase_config
set,c,l,title
dmtest on docker host VM
save,c,l
eoq

Idem from within container01 with its default dfc.properties file:

$ iapi dmtest01 -Udmadmin -Pdmadmin <<eoq
retrieve,c,dm_docbase_config
set,c,l,title
dmtest01 created silently on container01
save,c,l
eoq

Idem from within container011:

$ iapi dmtest01 -Udmadmin -Pdmadmin <<eoq
retrieve,c,dm_docbase_config
set,c,l,title
dmtest01 created silently on container011
save,c,l
eoq

First, let's access container01.dmtest01 from the containers' host VM with the current dfc.properties file:

$ idql dmtest01 -Udmadmin -Pdmadmin
 
 
OpenText Documentum idql - Interactive document query interface
Copyright (c) 2018. OpenText Corporation
All rights reserved.
Client Library Release 16.4.0070.0035
 
 
Connecting to Server using docbase dmtest01
Could not connect
[DM_DOCBROKER_E_NO_SERVERS_FOR_DOCBASE]error: "The DocBroker running on host (docker:1489) does not know of a server for the specified docbase (dmtest01)"

As expected, it does not work because container01.dmtest01 does not project to the host’s docbroker. Now, let’s turn to widql:

$ ./widql dmtest01@docker:2489 -Udmadmin -Pdmadmin --keep <<eoq
select title from dm_docbase_config
go
eoq
OpenText Documentum idql - Interactive document query interface
Copyright (c) 2018. OpenText Corporation
All rights reserved.
Client Library Release 16.4.0070.0035
 
 
Connecting to Server using docbase dmtest01
[DM_SESSION_I_SESSION_START]info: "Session 0100c350800011bb started for user dmadmin."
 
 
Connected to OpenText Documentum Server running Release 16.4.0000.0248 Linux64.Oracle
title
------------------------------------------
dmtest01 created silently on container01

It works.
We used ––keep, therefore the dfc.properties file has changed:

$ grep docbroker /app/dctm/config/dfc.properties
dfc.docbroker.host[0]=docker
dfc.docbroker.port[0]=2489

Indeed.
That docbase can also be reached by the container’s IP address and internal port 1489:

$ docker exec -it container01 ifconfig eth0 | head -3
eth0: flags=4163 mtu 1500
inet 192.168.33.101 netmask 255.255.255.0 broadcast 192.168.33.255
ether 02:42:c0:a8:21:65 txqueuelen 0 (Ethernet)
 
$ ./widql dmtest01@192.168.33.101:1489 -Udmadmin -Pdmadmin <<eoq
select title from dm_docbase_config
go
eoq
...
Connecting to Server using docbase dmtest01
[DM_SESSION_I_SESSION_START]info: "Session 0100c350800011b5 started for user dmadmin."
...
title
------------------------------------------
dmtest01 created silently on container01

Is the local dmtest docbase still reachable ?:

$ idql dmtest -Udmadmin -Pdmadmin
...
Could not connect
[DM_DOCBROKER_E_NO_SERVERS_FOR_DOCBASE]error: "The DocBroker running on host (docker:2489) does not know of a server for the specified docbase (dmtest)"

Not with that changed dfc.properties file and the standard tools. But by using our nifty little tool:

$ ./widql dmtest@docker:1489 -Udmadmin -Pdmadmin <<eoq
select title from dm_docbase_config
go
eoq
...
Connected to OpenText Documentum Server running Release 16.4.0080.0129 Linux64.Oracle
title
----------------------
dmtest on host VM

Fine !
Is container011.dmtest01 reachable now ?

$ ./widql dmtest01 -Udmadmin -Pdmadmin <<eoq
select title from dm_docbase_config
go
eoq
...
Connecting to Server using docbase dmtest01
...
Connected to OpenText Documentum Server running Release 16.4.0000.0248 Linux64.Oracle
title
-------------------------------------------
dmtest01 created silently on container01

This is container01.dmtest01, not the one we want, i.e. the one on container011.
Note that ./widql was called without the extended syntax so it invoked the standard idql directly.
Let try again:

$ ./widql dmtest01@docker:5489 -Udmadmin -Pdmadmin <<eoq
select title from dm_docbase_config
go
eoq
...
Connecting to Server using docbase dmtest01
[DM_SESSION_I_SESSION_START]info: "Session 0100c3508000059e started for user dmadmin."
...
title
------------------------------------------
dmtest01 created silently on container011

Here we go, it works !
The same using the container’s IP address and its docbroker’s internal port:

$ docker exec -it container011 ifconfig eth0 | head -3
eth0: flags=4163 mtu 1500
inet 192.168.33.104 netmask 255.255.255.0 broadcast 192.168.33.255
ether 02:42:c0:a8:21:68 txqueuelen 0 (Ethernet)
 
$ ./widql dmtest01@192.168.33.104:5489 -Udmadmin -Pdmadmin <<eoq
select title from dm_docbase_config
go
eoq
...
Connecting to Server using docbase dmtest01
[DM_SESSION_I_SESSION_START]info: "Session 0100c35080000598 started for user dmadmin."
...
title
------------------------------------------
dmtest01 created silently on container011

Try now the same connection but with ––append and ––keep:

$ ./widql dmtest01@docker:5489 -Udmadmin -Pdmadmin --append --keep <<eoq
select title from dm_docbase_config
go
eoq
...
Connecting to Server using docbase dmtest01
...
Connected to OpenText Documentum Server running Release 16.4.0000.0248 Linux64.Oracle
title
-------------------------------------------
dmtest01 created silently on container011

What is the content of dfc.properties now ?

$ grep docbroker /app/dctm/config/dfc.properties
dfc.docbroker.host[0]=docker
dfc.docbroker.port[0]=2489
dfc.docbroker.host[1]=docker
dfc.docbroker.port[1]=5489

Both options have been taken into account as expected.
Let’s try to reach the VM host’s repository:

$ ./widql dmtest -Udmadmin -Pdmadmin <<eoq
select title from dm_docbase_config
go
eoq
...
Connecting to Server using docbase dmtest
Could not connect
[DM_DOCBROKER_E_NO_SERVERS_FOR_DOCBASE]error: "The DocBroker running on host (docker:2489) does not know of a server for the specified docbase (dmtest)"

Specify the docbroker’s host and the ––verbose option:

$ ./widql dmtest@docker -Udmadmin -Pdmadmin --verbose <<eoq
select title from dm_docbase_config
go
eoq
 
changing to docker:...
requested changes:
# removed: dfc.docbroker.host[0]=docker
# removed: dfc.docbroker.port[0]=2489
# removed: dfc.docbroker.host[1]=docker
# removed: dfc.docbroker.port[1]=5489
# added: dfc.docbroker.host[0]=docker
# added: dfc.docbroker.port[0]=2489
diffs:
12,13d11
< dfc.docbroker.host[1]=docker
< dfc.docbroker.port[1]=5489
calling original: /app/dctm/product/16.4/bin/idql dmtest -Udmadmin -Pdmadmin
...
Connecting to Server using docbase dmtest
Could not connect
[DM_DOCBROKER_E_NO_SERVERS_FOR_DOCBASE]error: "The DocBroker running on host (docker:2489) does not know of a server for the specified docbase (dmtest)"

Since the port was not specified, the wrapper took the first port found in the dfc.properties to supply the missing value, i.e. 2489 which is incorrect as dmtest@docker only projects to port docker:1489.
Use an unambiguous command now:

$ ./widql dmtest@docker:1489 -Udmadmin -Pdmadmin ––verbose <<eoq
select title from dm_docbase_config
go
eoq
 
changing to docker:1489...
requested changes:
# removed: dfc.docbroker.host[0]=docker
# removed: dfc.docbroker.port[0]=2489
# removed: dfc.docbroker.host[1]=docker
# removed: dfc.docbroker.port[1]=5489
# added: dfc.docbroker.host[0]=docker
# added: dfc.docbroker.port[0]=1489
diffs:
11,13c11
< dfc.docbroker.port[0]=2489
< dfc.docbroker.host[1]=docker
dfc.docbroker.port[0]=1489
calling original: /app/dctm/product/16.4/bin/idql dmtest -Udmadmin -Pdmadmin
...
Connecting to Server using docbase dmtest
...
Connected to OpenText Documentum Server running Release 16.4.0080.0129 Linux64.Oracle
title
--------------------
dmtest on host VM

Looks OK.
Let’s try wdmawk now. But first, here is the test code twdmawk.awk:

$ cat twdmawk.awk 
BEGIN {
   print "repo_target=" repo_target, "docbase=" docbase
   session = dmAPIGet("connect," docbase ",dmadmin,dmadmin")
   print dmAPIGet("getmessage," session)
   dmAPIGet("retrieve," session ",dm_docbase_config")
   print dmAPIGet("get," session ",l,title")
   dmAPIExec("disconnect," session)
   exit(0)
}

Line 3 displays the two variables automatically passed to dmawk by the wrapper, repo_target and docbase.
The test script connects to the docbase which was silently passed as command-line parameter by wdmawk through the -v option after it extracted it from the given target parameter docbase[@host[:port]], as illustrated below with the ––verbose option.
Let’s see the invocation for the repository on the host VM:

$ ./wdmawk dmtest@docker:1489 -f ./twdmawk.awk --verbose
changing to docker:1489...
requested changes:
# removed: dfc.docbroker.host[0]=docker
# removed: dfc.docbroker.port[0]=2489
# removed: dfc.docbroker.host[1]=docker
# removed: dfc.docbroker.port[1]=5489
# added: dfc.docbroker.host[0]=docker
# added: dfc.docbroker.port[0]=1489
diffs:
11,13c11
< dfc.docbroker.port[0]=2489
< dfc.docbroker.host[1]=docker
––
> dfc.docbroker.port[0]=1489
calling original: /app/dctm/product/16.4/bin/dmawk -v docbase=dmtest -f ./twdmawk.awk
repo_target= docbase=dmtest
[DM_SESSION_I_SESSION_START]info: "Session 0100c3508000367b started for user dmadmin."
 
 
dmtest on host VM

Let’s acces the container01’s repository :

$ ./wdmawk dmtest01@docker:2489 -f ./twdmawk.awk
 
[DM_SESSION_I_SESSION_START]info: "Session 0100c35080001202 started for user dmadmin."
 
 
dmtest01 created silently on container01

A small typo in the port number and …

dmadmin@docker:~$ ./wdmawk dmtest01@docker:3489 -f ./twdmawk.awk
 
[DFC_DOCBROKER_REQUEST_FAILED] Request to Docbroker "docker:3489" failed
 
[DM_SESSION_E_RPC_ERROR]error: "Server communication failure"
 
java.net.ConnectException: Connection refused (Connection refused)

Note the stupid error message “… Connection refused …”, very misleading when investigating a problem. It’s just that there nobody listening on that port.
Let’s access the container011’s repository:

dmadmin@docker:~$ ./wdmawk dmtest01@docker:5489 -f ./twdmawk.awk
 
[DM_SESSION_I_SESSION_START]info: "Session 0100c350800005ef started for user dmadmin."
 
 
dmtest01 created silently on container011

Effect of the -v option:

dmadmin@docker:~$ ./wdmawk -v dmtest01@docker:5489 -f ./twdmawk.awk --verbose
...
calling original: /app/dctm/product/16.4/bin/dmawk -v repo_target=dmtest@docker:1489 -v docbase=dmtest -f ./twdmawk.awk
repo_target=dmtest@docker:1489 docbase=dmtest
[DM_SESSION_I_SESSION_START]info: "Session 0100c35080003684 started for user dmadmin."
 
 
dmtest on host VM

A repo_target parameter with the extended syntax has been passed to dmawk.
Let’s now quickly check the wrapper from within the containers.
Container01
The host’s docbase:

[dmadmin@container01 scripts]$ ./wiapi dmtest@docker:1489 -Udmadmin -Pdmadmin<<eoq
retrieve,c,dm_docbase_config
get,c,l,title
eoq
...
Connecting to Server using docbase dmtest
...
dmtest on host VM

The container011’s docbase:

[dmadmin@container01 scripts]$ ./wiapi dmtest01@container011:1489 -Udmadmin -Pdmadmin<<eoq
retrieve,c,dm_docbase_config
get,c,l,title
eoq
...
Connecting to Server using docbase dmtest01
...
dmtest01 created silently on container011
...

Container011
The host’s docbase:

dmadmin@container011 scripts]$ ./wiapi dmtest@docker:1489 -Udmadmin -Pdmadmin<<eoq
retrieve,c,dm_docbase_config
get,c,l,title
eoq
...
Connecting to Server using docbase dmtest
...
Connected to OpenText Documentum Server running Release 16.4.0080.0129 Linux64.Oracle
...
dmtest on host VM
...

The docbase on container01:

dmadmin@container011 scripts]$ ./wiapi dmtest01@container01:1489 -Udmadmin -Pdmadmin<<eoq
retrieve,c,dm_docbase_config
get,c,l,title
eoq
...
...
Connecting to Server using docbase dmtest01
...
dmtest01 created silently on container01
...

Let’s briefly test the usage of the sourced configuration file. Here is a snippet of the file shown earlier in this article:

# repository connection configuration file;
# must be sourced prior so the environment variables can be resolved;
# this is a enhancement over the dfc.properties file syntax used by the dctm_wrapper utility:
# docbroker.host[i]=...
# docbroker.port[i]=...
# it supports several syntaxes:
# docbroker only definition [[docbroker_host]:[port]];
#    usage: ./widql dmtest@$dmtest
# full definition docbase[@[docbroker_host]:[port]]
#    usage: ./widql $test
# alternate ':' separator docbase:[[docbroker_host]:[docroker_port]];
#    usage: ./widql $dmtestVM
# alias literal;
#    usage: ./widql test
# in order to resolve alias literals, the wrapper will source the configuration file by itself;
...
# container011.dmtest01;
# docbroker only definition docbroker_host:port;
d_dmtest011=container011:5489
di_dmtest011=192.168.33.104:1489
# full definition;
f_dmtest011=dmtest01@container011:2489
fip_dmtest011=dmtest01@192.168.33.104:1489

With a good name convention, the variables can be easily remembered which saves a lot of typing too.
Note on lines 9 and 10 how the whole extended target name can be specified, including the repository name.
A few tests:

dmadmin@docker:~$ ./widql dmtest01@$d_dmtest011 -Udmadmin -Pdmadmin --verbose
current_cmd=[./widql dmtest01@container011:5489 -Udmadmin -Pdmadmin --verbose] ...
 
dmadmin@docker:~$ ./widql dmtest01@$dip_dmtest011 -Udmadmin -Pdmadmin --verbose
current_cmd=[./widql dmtest01@192.168.33.104:1489 -Udmadmin -Pdmadmin --verbose] ...
 
dmadmin@docker:~$ ./widql $f_dmtest011 -Udmadmin -Pdmadmin --verbose
current_cmd=[./widql dmtest01@container011:2489 -Udmadmin -Pdmadmin --verbose] ...
 
dmadmin@docker:~$ ./widql $fip_dmtest011 -Udmadmin -Pdmadmin --verbose
current_cmd=[./widql dmtest01@192.168.33.104:1489 -Udmadmin -Pdmadmin --verbose] ...

The variables have been expanded by the shell prior to entering the wrapper, no programming effort was needed here, which is always appreciated.

Possible Enhancements

As shown precedently, the alternate configuration file lists aliases for the couples docbroker:port and even repository@docbroker:port. In passing, the wrapper also supports the version repository:docbroker:port.
Now, in order to better match Documentum syntax, is it possible to be even more transparent by removing dollar signs, colons and at-signs while still accessing the extended syntax ? E.g.:

$ ./widql dmtest -Udmadmin ....

Yes it is. The trick here is to first look up the alias in the configuration file, which incidentally becomes mandatory now, and re-execute the program with the alias resolved. As we are all lazy coders, we will not explicitly code the looking up but instead rely on the shell: the wrapper will source the file, resolve the target and re-execute itself.
If the alias has not been defined in the file, then the wrapper considers it as the name of a repository and falls back to the usual command-line tools.
A good thing is that no new format has to be introduced in the file as the target is still the name of an environment variable.
Since the changes are really minimal, let’s do it. Hereafter, the diff output showing the changes from the listing in part I:

> # this variable points to the target repositories alias file and defaults to repository_connections.aliases;
> REPO_ALIAS=${REPO_ALIAS:-~/repository_connections.aliases}
> 
107a111
> [[ bVerbose -eq 1 ]] && echo "current configuration file=[$REPO_ALIAS]"
225,227c229,241
<    if [[ $bVerbose -eq 1 ]]; then
<       echo "no change to current $dfc_config file"
    [[ -f $REPO_ALIAS ]] && . $REPO_ALIAS
>    definition=${!1}
>    [[ $bVerbose -eq 1 ]] && echo "alias lookup in $REPO_ALIAS: $1 = $definition"
>    if [[ ! -z $definition ]]; then
>       new_cmd=${current_cmd/$1/$definition}
>       [[ $bVerbose -eq 1 ]] && echo "invoking $new_cmd"
>       exec $new_cmd
>    else
>       if [[ $bVerbose -eq 1 ]]; then
>          echo "no change to current $dfc_config file"
>          echo "calling original: $DM_HOME/bin/${dctm_program} $*"
>       fi
>       $DM_HOME/bin/${dctm_program} $*
229d242
<    $DM_HOME/bin/${dctm_program} $*

On line 9, the target configuration file pointed to by the REPO_ALIAS environment variable gets sourced if existing. $REPO_ALIAS defaults to repository_connections.aliases but can be changed before calling the wrapper.
Note on line 10 how bash can dereference a variable 1 containing the name of another variable 2 to get variable 2’s value (indirect expansion), nice touch.
To apply the patch in-place, save the diffs above in diff-file and run the following command:

patch old-file < diff-file

Testing
For conciseness, the tests below only show how the target is resolved. The actual connection has already been tested abundantly earlier.

dmadmin@docker:~$ ./widql f_dmtest -Udmadmin -Pdmadmin --verbose
current_cmd=[./widql f_dmtest -Udmadmin -Pdmadmin --verbose] alias lookup in /home/dmadmin/repository_connections.aliases: f_dmtest = dmtest@docker:1489
invoking ./widql dmtest@docker:1489 -Udmadmin -Pdmadmin --verbose
current_cmd=[/home/dmadmin/widql dmtest@docker:1489 -Udmadmin -Pdmadmin --verbose] ...
dmadmin@docker:~$ ./widql fip_dmtest01 -Udmadmin -Pdmadmin --verbose
current_cmd=[./widql fip_dmtest01 -Udmadmin -Pdmadmin --verbose] alias lookup in /home/dmadmin/repository_connections.aliases: fip_dmtest01 = dmtest01@192.168.33.2:1489
invoking ./widql dmtest01@192.168.33.2:1489 -Udmadmin -Pdmadmin --verbose
current_cmd=[/home/dmadmin/widql dmtest01@192.168.33.2:1489 -Udmadmin -Pdmadmin --verbose] ...
dmadmin@docker:~$ ./widql fip_dmtest011 -Udmadmin -Pdmadmin --verbose
current_cmd=[./widql fip_dmtest011 -Udmadmin -Pdmadmin --verbose] alias lookup in /home/dmadmin/repository_connections.aliases: fip_dmtest011 = dmtest01@192.168.33.3:1489
invoking ./widql dmtest01@192.168.33.3:1489 -Udmadmin -Pdmadmin --verbose
current_cmd=[/home/dmadmin/widql dmtest01@192.168.33.3:1489 -Udmadmin -Pdmadmin --verbose]

Note how the targets are cleaner now, no curly little fancy shell characters in front.

Conclusion

As I was testing this little utility, I was surprised to realize how confortable and natural its usage is. It feels actually better to add the docbroker’s host and port than to stop at the docbase name, probably because it makes the intented repository absolutely unambiguous. The good thing is that is almost invisible, except for its invocation but even this can be smoothed out by using command aliases or renaming the symlinks.
When one has to work with identically named docbases or with clones existing in different environments, dctm-wrapper brings a real relief. And it was quick and easy to put together too.
As it modifies an essential configuration file, it is mainly aimed at developers or administrators on their machine, but then those constitute the targeted audience anyway.
As always, if you have any ideas for some utility that could benefit us all, please do no hesitate to suggest them in the comment section. Feedback is welcome too of course.

Cet article Connecting to a Repository via a Dynamically Edited dfc.properties File (part II) est apparu en premier sur Blog dbi services.

Connecting to a Repository via a Dynamically Edited dfc.properties File (part I)

Yann Neuhaus - Sun, 2019-06-16 13:36
Connecting to a Repository via a Dynamically Edited dfc.properties File

Now that we have containerized content servers, it is very easy, maybe too easy, to create new repositories. Their creation is still not any faster (whether they are containerized or not is irrelevant here) but given a configuration file it just takes one command to instantiate an image into a running container with working repositories in it. Thus, during experimentation and testing, out of laziness or in a hurry, one can quickly finish up having several containers with identically named repositories, e.g. dmtest01, with an identically named docbroker, e.g. docbroker01. Now, suppose one wants to connect to the docbase dmtest01 running on the 3rd such container using the familiar command-line tools idql/iapi/dmawk. How then to select that particular instance of dmtest01 among all the others ?
To precise the test case, let’s say that we are using a custom bridge network to link the containers together on the docker host (appropriately named docker) which is a VirtualBox VM running an Ubuntu flavor. The metal also runs natively the same Ubuntu distro. It looks complicated but actually matches the common on-premises infrastructure type where the metal is an ESX or equivalent, its O/S is the hypervisor and the VMs run a Redhat or Suse distro. As this is a local testing environment, no DNS or network customizations have been introduced save for the custom bridge.
We want to reach a remote repository either from container to container or from container to host or from host to container.
The problem here stems from the lack of flexibility in the docbroker/dfc.properties file mechanism and no network fiddling can work around this.

It’s All in The dfc.properties File

Containers have distinct host names, so suffice it to edit their local dfc.properties file and edit this field only. Their file may all look like the one below:

dfc.docbroker.host[0]=container01
dfc.docbroker.port[0]=1489
dfc.docbroker.host[1]=docker
dfc.docbroker.port[1]=1489
dfc.docbroker.host[3]=container011
dfc.docbroker.port[3]=1489
dfc.docbroker.host[4]=container02
dfc.docbroker.port[4]=1489

In effect, the custom bridge network embeds a DNS for all the attached containers, so their host names are known to each other (but not to the host so IP address must be used from there or the host’s /etc/hosts file must be edited). The docbroker ports are the ones inside the containers and have all the same value 1489 because they were created out of the same configuration files. The docker entry has been added to the containers’ /etc/host file via the ––add-host= clause of the docker run’s command.
For the containers’ host machine, where a Documentum repository has been installed too, the dfc.properties file could look like this one:

dfc.docbroker.host[0]=docker
dfc.docbroker.port[0]=1489
dfc.docbroker.host[1]=docker
dfc.docbroker.port[1]=2489
dfc.docbroker.host[3]=docker
dfc.docbroker.port[3]=3489
dfc.docbroker.host[4]=docker
dfc.docbroker.port[4]=5489

Here, the host name is the one of the VM where the containers sit and is the same for all the containers. The port numbers differ because they are the external container’s port which are published in the host VM and mapped to the respective docbroker’s internal port, 1489. Since the containers share the same custom network, their host names, IP addresses and external ports must all be different when running the image, or docker won’t allow it.
Alternatively, the container’s IP addresses and internal docbroker’s ports could be used directly too if one is too lazy to declare the containers’ host names in the host’s /etc/hosts file, which is generally the case when testing:

dfc.docbroker.host[0]=docker 
dfc.docbroker.port[0]=1489
dfc.docbroker.host[1]=192.168.33.101
dfc.docbroker.port[1]=1489
dfc.docbroker.host[2]=192.168.33.102
dfc.docbroker.port[2]=1489
dfc.docbroker.host[3]=192.168.33.104
dfc.docbroker.port[3]=1489

The host’s custom network will take care of routing the traffic into the respective containers.
Can you spot the problem now ? As all the containers contain identically named repositories (for clarity, let’s say that we are looking for the docbase dmtest01), the first contacted docbroker in that file will always reply successfully because there is indeed a dmtest01 docbase in that container and consequently one will always be directed to the docbase container01.dmtest01. If one wants to contact container03.dmtest01, this configuration won’t let do it. One would need to edit it and move the target container03 host in the first position, which is OK until one wants to access container02.dmtest01 or go back to container01.dmtest01.
This situation has been existing forever but containers make it more obvious because they make it so much easier to have repository homonyms.
So is there a simpler way to work around this limitation than editing back and forth a configuration file or giving different names to the containerized repositories ?

A Few Reminders

Documentum has made quite a lot of design decisions inspired by the Oracle DBMS but their implementation is far from offering the same level of flexibility and power, and this is often irritating. Let’s consider the connectivity for example. Simply speaking, Oracle’s SQL*Net configuration relies mainly on a tnsnames.ora file for the connectivity (it can also use a centralized ldap server but let’s keep it simple). This file contains entries used to contact listeners and get the information needed to connect to the related database. Minimal data to provide in the entries are the listener’s hostname and port, and the database sid or service name, e.g.:

...
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db_service)
    )
  )
...

A connection to the database db_service can simply be requested as follows:

sqlplus scott@orcl

orcl is the SQL*Net alias for the database served by db_service. It works like an index in a lookup table, the tnsnames.ora file.
Compare this with a typical dfc.properties file, e.g. /home/dmadmin/documentum/shared/config/dfc.properties:

...
dfc.docbroker.host[0]=docker
dfc.docbroker.port[0]=1489
dfc.docbroker.host[1]=dmtest
dfc.docbroker.port[1]=1489
...

Similarly, instead of contacting listeners, we have here docbrokers. A connection to the docbase dmtest can be requested as follows:

idql dmtest

dmtest is the target repository. It is not a lookup key in the dfc.properties file. Unlike the tnsnames.ora file and its aliases, there is an indirection here and the dfc.properties file does not directly tell where to find a certain repository, it just lists the docbrokers to be sequentially queried about it until the first one that knows the repository (or an homonym thereof) answers. If the returned target docbase is the wrong homonym, tough luck, it will not be reachable, unless the order of the entries is changed. Repositories announces themselves to the docbrokers by “projecting” themselves. If two repositories by the same name project to the same docbroker, no error is raised but the docbroker can return unexpected results, e.g. one may finish up in the unintended docbase.
Another major difference is that with Oracle but not with Documentum, it is possible to bypass the tnsnames.ora file by specifying the connection data in-line, e.g. on the command-line:

sqlplus scott@'(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db_service)
    )
  )'

This can be very useful when editing the local, official listener.ora file is not allowed, and sometimes faster than setting $TNS_ADMIN to an accessible local directory and editing a private listener.ora file there.
This annoyance is even more frustrating because Documentum’s command-line tools do support a similar syntax but for a different purpose:

idql repository[.service][@machine] [other parameters]

While this syntax is logically useful to access the service (akin to an Oracle’s instance but for a HA Documentum installation), it is used in a distributed repository environment to contact a particular node’s docbroker; however, it still does not work if that docbroker is not first declared in the local dfc.properties file.
Last but not least, one more reason to be frustrated is that the DfCs do allow to choose a specific docbroker when opening a session, as illustrated by the jython snippet below:

import traceback
import com.documentum.fc.client as DFCClient
import com.documentum.fc.common as DFCCommon

docbroker_host = "docker"
docbroker_port = "1489"
docbase = "dmtest"
username = "dmadmin"
password = "dmadmin"
print("attempting to connect to " + docbase + " as " + username + "/" + password + " via docbroker on host " + docbroker_host + ":" + docbroker_port)
try:
  client = DFCClient.DfClient.getLocalClient()

  config = client.getClientConfig()
  config.setString ("primary_host", docbroker_host)
  config.setString ("primary_port", docbroker_port)

  logInfo = DFCCommon.DfLoginInfo()
  logInfo.setUser(username)
  logInfo.setPassword(password)
  docbase_session = client.newSession(docbase, logInfo)

  if docbase_session is not None:
    print("Connected !")
  else:
    print("Couldn't connect !")
except Exception:
  traceback.print_exc()

Content of dfc.properties:

$ cat documentum/shared/config/dfc.properties
dfc.date_format=dd.MM.yyyy HH:mm:ss

Execution:

$ jython ./test.jy
...
attempting to connect to dmtest as dmadmin/dmadmin via docbroker docker
Connected !

Despite a dfc.properties file devoid of any docbroker definition, the connection was successful. Unfortunately, this convenience has not been carried over to the vegetative command-line tools.
While we can dream and hope for those tools to be resurrected and a backport miracle to happen (are you listening OTX ?), the next best thing is to tackle ourselves this shortcoming and implement an as unobtrusive as possible solution. Let’s see how.

A few Proposals

Currently, one has to manually edit the local dfc.properties file, but this is tedious to say the least, because changes must sometimes be done twice, forwards and rolled back if the change is only temporary. To avoid this, we could add at once in our local dfc.properties file all the machines that host repositories of interest but this file could quickly grow large and it won’t solve the case of repository homonyms. The situation would become quite unmanageable although an environment variable such as the late DMCL_CONFIG (appropriately revamped e.g. to DFC_PROPERTIES_CONFIG for the full path name of the dfc.properties file to use) could help here to organize those entries. But there is not such a variable any longer for the command-line tools (those tools have stopped evolving since CS v6.x) although there is a property for the DfCs to pass to the JVM at startup, -Ddfc.properties.file, or even the #include clause in the dfc.properties file, or playing with the $CLASSPATH but there is a better way.
What about an on-the-fly, transparent, behind the scenes dfc.properties file editing to support a connection syntax similar to the Oracle’s in-line one ?
Proposal 1
Let’s specify the address of the docbroker of interest directly on the command-line, as follows:

$ idql dmtest01@container03:3489
or
$ idql dmtest01@192.168.33.104:3489

This is more akin to Oracle in-line connection syntax above.
Proposal 2
An alternative could be to use an Oracle’s tnsnames.ora-like configuration file such as the one below (and (in (keeping (with (the (lisp spirit)))))):

dmtest01 = ((docbroker.host = container01) (docbroker.port = 1489))
dmtest02 = ((docbroker.host = container02) (docbroker.port = 1489))
dmtest03 = ((docbroker.host = container03) (docbroker.port = 1489))

and to use it thusly:

$ idql dmtest01@dmtest03

dmtest03 is looked up in the configuration file and replaced on the command-line by its definition.
Proposal 3
With a more concise configuration file that can also be sourced:

dmtest01=container01:1489
dmtest02=container02:1489
dmtest03=container03:1489

and used as follows:

$ export REPO_ALIAS=~/repository_connections.aliases
$ . $REPO_ALIAS
$ ./widql dmtest01@$dmtest03

$dmtest03 is directly fetched from the environment after the configuration file has been sourced, which is equivalent to a lookup. Since the variable substitution occurs at the shell level, it comes free of charge.
With a bit more generalization, it is possible to merge the three proposals together:

$ idql repository(@host_literal:port_number) | @$target

In other words, one can either provide literally the full connection information or provide a variable which will be resolved by the shell from a configuration file to be sourced preliminarily.
Let’s push the configuration file a bit farther and define complete aliases up to the repository name like this:

dmtest=dmtest@docker:1489
or even so:
dmtest=dmtest:docker:1489

Usage:

$ ./widql $dmtest

The shell will expand the alias with its definition. The good thing is the definition styles can be mixed and matched to suit one’s fantasy. Example of a configuration file:

# must be sourced prior so the environment variables can be resolved;
# this is a enhancement over the dfc.properties file syntax used by the dctm_wrapper utility:
# docbroker.host[i]=...
# docbroker.port[i]=...
# it supports several syntaxes:
# docbroker only definition docbroker_host:port;
#    usage: ./widql dmtest@$dmtest
# full definition docbase[@[docbroker_host]:[port]]
#    usage: ./widql $test
# alternate ':' separator docbase:[docbroker_host]:[port];
#    usage: ./widql $dmtestVM
# alias literal;
#    usage: ./widql test
# in order to resolve alias literals, the wrapper will source the configuration file by itself;

# docker.dmtest;
# docbroker only definition;
d_dmtest=docker:1489
# full definition;
f_dmtest=dmtest@docker:1489
# alternate ':' separator;
a_dmtest=dmtest:docker:1489

# container01.dmtest01;
# docbroker only definition;
d_dmtest01=container01:2489
dip_dmtest01=192.168.33.101:1489
# full definition;
f_dmtest01=dmtest01@container01:2489
fip_dmtest01c=dmtest01@192.168.33.101:1489
# alternate ':' separator;
a_dmtest01=dmtest01:container01:2489
aip_dmtest01=dmtest01:192.168.33.101:2489

# container011.dmtest01;
# docbroker only definition;
d_dmtest011=container011:5489
dip_dmtest011=192.168.33.104:1489
# full definition;
f_dmtest011=dmtest01@container011:2489
fip_dmtest011=dmtest01@192.168.33.104:1489
# alternate ':' separator;
a_dmtest011=dmtest01:container011:2489
aip_dmtest011=dmtest01:192.168.33.104:2489

Lines 5 to 14 explains all the supported target syntaxes with a new one presented on lines 12 to 14, which will be explained later in the paragraph entitled Possible Enhancements.
Using lookup variables in a configuration file makes things easier when the host names are hard to remember because better mnemonic aliases can be defined for them. Also, as they are looked up, the entries can be in any order. They must obviously be unique or they will mask each other. A consistent naming convention may be required to easily find one own’s way into this file.
Whenever the enhanced syntax is used, it triggers an automatic editing of the dfc.properties file and the specified connection information is inserted as dfc.docbroker.host and dfc.docbroker.port entries. Then, the corresponding Documentum tool gets invoked and finally the original dfc.properties file is restored when the tool exits. The trigger here is the presence of the @ or : characters in the first command-line parameter.
This would also cover the case when an entry is simply missing from the dfc.properties file. Actually, from the point of view of the command-line tools, all the connection definitions could be handled over to the new configuration file and even removed from dfc.properties as they are dynamically added to and deleted from the latter file as needed.

The Implementation

The above proposal looks pretty easy and fun to implement, so let’s give it a shot. In this article, I’ll present a little script, dctm_wrapper, that builds upon the above @syntax to first edit the configuration file on demand (that’s the dynamic part of the article’s title) and then invoke the standard idql, iapi or dmawk utilities, with an optional rollback of the change on exiting.
Since it is not possible to bypass the dfc.properties files, we will dynamically modify it whenever the @host syntax is used from a command-line tool. As we do no want to replace the official idql, iapi and dmawk tools, yet, we will create new ones, say widql, wiapi and wdmawk (where w stands for wrapper). Those will be symlinks to the real script, dctm-wrapper.sh, which will be able to invoke either idql, iapi or dmawk according to how it was called (bash’s $0 contains the name of the symlink that was invoked, even though its target is always dctm-wrapper.sh, see the script’s source at the next paragraph).
The script dctm-wrapper.sh will support the following syntax:

$ ./widql docbase[@[host][:port]] [other standard parameters] [--verbose] [--append] [--keep] $ ./wiapi docbase[@[host][:port]] [other standard parameters] [--verbose] [--append] [--keep] $ ./wdmawk [-v] docbase[@[host][:port]] [dmawk parameters] [--verbose] [--append] [--keep]

The custom parameters ––verbose, ––append and ––keep are processed by the script and stripped off before invoking the official tools.
wdmawk is a bit special in that the native tool, dmawk, is invoked differently from iapi/idql but I felt that it too could benefit from this little hack. Therefore, in addition to the non-interactive editing of the dfc.properties file, wdmawk also passes on the target docbase name as a -v docbase=… command-line parameter (the standard way to pass parameters in awk) and removes the extended target parameter docbase[@[host][:port]] unless it is prefixed by the -v option in which case it gets forwarded through the -v repo_target= parameter. The dmawk program is then free to use them the way it likes. The repo_target parameter could have been specified on the command-line independently but the -v option can still be useful in cases such as the one below:

$ ./wdmawk docbase@docker:1489 -v repo_target=docbase@docker:1489 '{....}'

which can be shortened to

$ ./wdmawk -v docbase@docker:1489 '{....}'

If the extended target docbase parameter is present, it must be the first one.
If the ‘@’ or ‘:’ characters are missing, it means the enhanced syntax is not used and the script will not attempt to modify dfc.properties; it will pass on all the remaining parameters to the matching official tools.
When @[host][:port] is present, the dfc.properties file will be edited to accommodate the new docbroker’s parameters; all the existing couples dfc.docbroker.host/dfc.docbroker.port will either be removed (if ––append is missing) or preserved (if ––append is present) and a new couple entry will be appended with the given values. Obviously, if one want to avoid the homonym trap, ––append should not be used in order to let the given docbroker be picked up as the sole entry in the property file.
When ––append and ––keep are present, we end up with a convenient way to add docbroker entries into the property file without manually editing it.
As the host is optional, it can be omitted and the one from the first dfc.docbroker.host[] entry will be used instead. Ditto for the port.
Normally, upon returning from the invocation of the original tools, the former dfc.properties file is restored to its original content. However, if ––keep is mentioned, the rollback will not be performed and the modified file will replace the original file. The latter will still be there though but renamed to $DOCUMENTUM_SHARED/config/dfc.properties_saved_YY-MM-DD_HH:MI:SS so it will still be possible to manually roll back. ––keep is mostly useful in conjunction with ––append so that new docbrokers get permanently added to the configuration file.
Finally, when ––verbose is specified, the changes to the dfc.properties file will be sent to stdout; a diff of both the original and the new configuration file will also be shown, along with the final command-line used to invoke the selected original tool. This helps troubleshooting possible command-line parsing issues because, as it can be seen from the code, no extra-effort has been put into this section.

The Code

The script below shows a possible implementation:

#!/bin/bash
# Installation:
# it should not be called directly but through one of the aliases below for the standard tools instead:
# ln -s dctm-wrapper wiapi
# ln -s dctm-wrapper widql
# ln -s dctm-wrapper wdmawk
# where the initial w stands for wrapper;
# and then:
#    ./widql ...
# $DOCUMENTUM_SHARED must obviously exist;
# Since there is no \$DOCUMENTUM_SHARED in eCS ≥ 16.4, set it to $DOCUMENTUM as follows:
#    export DOCUMENTUM_SHARED=$DOCUMENTUM
# See Usage() for details;

Usage() {
   cat - >>EoU
./widql docbase[@[host][:port]] [other standard parameters] [--verbose] [--append] [--keep]
./wiapi docbase[@[host][:port]] [other standard parameters] [--verbose] [--append] [--keep]
./wdmawk [-v] docbase[@[host][:port]] [dmawk -v parameters] [--verbose] [--append] [--keep]
E.g.:
   wiapi dmtest
or:
   widql dmtest@remote_host
or:
   widql dmtest@remote_host:1491 -Udmadmin -Pxxxx
or:
   wiapi dmtest@:1491 --append
or:
   wdmawk -v dmtest01@docker:5489 -f ./twdmawk.awk -v ...
or:
   wdmawk dmtest01@docker:2489 -f ./twdmawk.awk -v ...
or:
   wiapi dmtest@remote_host:1491 --append --keep
etc...
If --verbose is present, the changes applied to \$DOCUMENTUM[_SHARED]/config/dfc.properties are displayed.
If --append is present, a new entry is appended to the dfc.properties file, the value couple dfc.docbroker.host and dfc.docbroker.port, and the existing ones are not commented out so they are still usable;
If --append is not present, all the entries are removed prior to inserting the new one;
If --keep is present, the changed dfc.properties file is not reverted to the changed one, i.e. the changes are made permanent;
If a change of configuration has been requested, the original config file is first saved with a timestamp appended and restored on return from the standard tools, unless --keep is present in which case
the backup file is also kept so it is still possible to manually revert to the original configuration;
wdmawk invokes dmawk passing it the -v docbase=$docbase command-line parameter;
In addition, if -v docbase[@[host][:port]] is used, -v repo_target=docbase[@[host][:port]] is also passed to dmawk;
Instead of a in-line target definition, environment variables can also be used, e.g.:
   widql dmtest@$dmtestVM ...
where $dmtestVM resolves to e.g. docker:1489
or even:
   widql $test01c ...
where $test01c resolves to e.g. dmtest01@container01:1489
As the environment variable is resolved by the shell before it invokes the program, make sure it has a definition, e.g. source a configuration file;
EoU
   exit 0
}

if [[ $# -eq 0 ]]; then
   Usage
fi

# save command;
current_cmd="$0 $*"

# which original program shall possibly be called ?
dctm_program=$(basename $0); dctm_program=${dctm_program:1}
if [[ $dctm_program == "dmawk" ]]; then
   bFordmawk=1 
else
   bFordmawk=0 
fi

# look for the --verbose, --append or --keep options;
# remove them from the command-line if found so they are not passed to the standard Documentum's tools;
# the goal is to clean up the command-line from the enhancements options so it can be passed to the official tools;
bVerbose=0
bAppend=0
bKeep=0
posTarget=1
passTarget2awk=0
while true; do
   index=-1
   bChanged=0
   for i in "$@"; do
      (( index += 1 ))
      if [[ "$i" == "--verbose" ]]; then
         bVerbose=1
         bChanged=1
         break
      elif [[ "$i" == "--append" ]]; then
         bAppend=1
         bChanged=1
         break
      elif [[ "$i" == "--keep" ]]; then
         bKeep=1
         bChanged=1
         break
      elif [[ "$i" == "-v" && $bFordmawk -eq 1 && $index -eq 0 ]]; then
	 passTarget2awk=1
         bChanged=1
         break
      fi
   done
   if [[ $bChanged -eq 1 ]]; then
      set -- ${@:1:index} ${@:index+2:$#-index-1}
   else
      break
   fi
done

[[ bVerbose -eq 1 ]] && echo "current_cmd=[$current_cmd]"

target=$1
remote_info=$(echo $1 | gawk '{
   docbase = ""; hostname = ""; port = ""
   if (match($0, /@[^ \t:]*/)) {
      docbase = substr($0, 1, RSTART - 1)
      hostname = substr($0, RSTART + 1, RLENGTH - 1)
      rest = substr($0, RSTART + RLENGTH)
      if (1 == match(rest, /:[0-9]+/))
         port = substr(rest, 2, RLENGTH - 1)
   }
   else docbase = $0
}
END {
   printf("%s:%s:%s", docbase, hostname, port)
}')
docbase=$(echo $remote_info | cut -d: -f1)
hostname=$(echo $remote_info | cut -d: -f2)
port=$(echo $remote_info | cut -d: -f3)

# any modifications to the config file requested ?
if [[ ! -z $hostname || ! -z $port ]]; then
   # the dfc.properties file must be changed for the new target repository;
   dfc_config=$DOCUMENTUM_SHARED/config/dfc.properties
   if [[ ! -f $dfc_config ]]; then
      echo "$dfc_config not found"
      echo "check the \$DOCUMENTUM_SHARED environment variable"
      echo " in ≥ 16.4, set it to \$DOCUMENTUM"
      exit 1
   fi
   
   # save the current config file;
   backup_file=${dfc_config}_saved_$(date +"%Y-%m-%d_%H:%M:%S")
   cp $dfc_config ${backup_file}

   [[ $bVerbose -eq 1 ]] && echo "changing to $hostname:$port..."
   pid=$$; gawk -v hostname="$hostname" -v port="$port" -v bAppend=$bAppend -v bVerbose=$bVerbose -v bKeep=$bKeep -v pid=$$ 'BEGIN {
      bFirst_hostname = 0; first_hostname = ""
      bFirst_port     = 0 ;    first_port = ""
      max_index = -1
   }
   {
      if (match($0, /^dfc.docbroker.host\[[0-9]+\]=/)) {
         if (!hostname && !bFirst_hostname) {
            # save the first host name to be used if command-line hostname was omitted;
            bFirst_hostname = 1
            first_hostname = substr($0, RLENGTH +1)
         }
         match($0, /\[[0-9]+\]/); index_number = substr($0, RSTART + 1, RLENGTH - 2)
         if (bAppend) {
            # leave the entry;
            print $0
            if (index_number > max_index)
               max_index = index_number
         }
         else {
            # do not, which will remove the entry;
            if (bVerbose)
               print "# removed:", $0 > ("/tmp/tmp_" pid)
         }
      }
      else if (match($0, /^dfc.docbroker.port\[[0-9]+\]=/)) {
         if (!port && !bFirst_port) {
            # save the first port to be used if command-line port was omitted;
            bFirst_port = 1
            first_port = substr($0, RLENGTH +1)
         }
         if (bAppend)
            # leave the entry;
            print $0
         else {
            # do nothing, which will remove the entry;
            if (bVerbose)
               print "# removed:", $0 > ("/tmp/tmp_" pid)
         }
      }
      else print
   }
   END {
      if (!hostname)
         hostname = first_hostname
      if (!port)
         port = first_port
      if (bAppend)
         index_number = max_index + 1
      else
         index_number = 0
      print "dfc.docbroker.host[" index_number "]=" hostname
      print "dfc.docbroker.port[" index_number "]=" port
      if (bVerbose) {
         print "# added: dfc.docbroker.host[" index_number "]=" hostname > ("/tmp/tmp_" pid)
         print "# added: dfc.docbroker.port[" index_number "]=" port     > ("/tmp/tmp_" pid)
      }
      close("/tmp/tmp_" pid)
   }' $dfc_config > ${dfc_config}_new

   if [[ $bVerbose -eq 1 ]]; then
      echo "requested changes:"
      cat /tmp/tmp_$$
      rm /tmp/tmp_$$
      echo "diffs:"
      diff $dfc_config ${dfc_config}_new
   fi 

   mv ${dfc_config}_new $dfc_config
   shift

   if [[ $bFordmawk -eq 1 ]]; then
      docbase="-v docbase=$docbase"
      [[ $passTarget2awk -eq 1 ]] && docbase="-v repo_target=$target $docbase"
   fi
   [[ $bVerbose -eq 1 ]] && echo "calling original: $DM_HOME/bin/${dctm_program} $docbase $*"
   $DM_HOME/bin/${dctm_program} $docbase $*

   # restore original config file;
   [[ $bKeep -eq 0 ]] && mv ${backup_file} $dfc_config
else
   if [[ $bVerbose -eq 1 ]]; then
      echo "no change to current $dfc_config file"
      echo "calling original: $DM_HOME/bin/${dctm_program} $*"
   fi
   $DM_HOME/bin/${dctm_program} $*
fi

The original configuration file is always saved on entry by appending a timestamp precise to the second which, unless you’re the Flash running the command twice in the background with the option ––keep but without ––append, should be enough to preserve the original content.
To make the command-line parsing simpler, the script relies on the final invoked command for checking any syntax errors. Feel free to modify it and make it more robust if you need that. As said earlier, the ––verbose option can help troubleshooting unexpected results here.
See part II of this article for the tests.

Cet article Connecting to a Repository via a Dynamically Edited dfc.properties File (part I) est apparu en premier sur Blog dbi services.

An exotic feature in the content server: check_client_version

Yann Neuhaus - Sun, 2019-06-16 12:26
An exotic feature in the content server: check_client_version

A few months ago, I tripped over a very mysterious problem while attempting to connect to a 7.3 CS docbase from within dqMan.
We had 3 docbases and we could connect using this client to all of them but one ! Moreover, we could connect to all three using a remote Documentum Administrator or the local idql/iapi command-line tools. Since we could connect to at least one of them with dqMan, this utility was not guilty. Also, since all three docbases accepted connections, they were all OK in this respect. Ditto for the account used, dmadmin or nominative ones; local connections were possible hence the accounts were all active and, as they could be used from within the remote DA, their identification method and password were correct too.
We tried connecting from different workstations in order to check the dqMan side, we cleared its caches, we reinstalled it, but to no avail. We checked the content server’s log, as usual nothing relevant. It was just the combination of this particular docbase AND dqMan. How strange !
So what the heck was wrong here ?
As we weren’t the only administrators of those repositories, we more or less suspecting someone else change some setting but which one ? Ok, I sort of gave it away in the title but please bear with me and read on.
I don’t remember exactly how, we were probably working in panic mode, but we eventually decided to compare the docbases’ dm_docbase_config object side by side as shown below (with some obfuscation):

paste <(iapi bad_boy -Udmadmin -Pxxx <<eoq | awk '{print substr($0, 1, 80)}'
retrieve,c,dm_docbase_config
dump,c,l
quit
eoq
) <(iapi good_boy -Udmadmin -Pxxx <<eoq | awk '{print substr($0, 1, 80)}'
retrieve,c,dm_docbase_config
dump,c,l
quit
eoq
) | column -c 30 -s $'\t' -t | tail +11 | head -n 48
USER ATTRIBUTES                                          USER ATTRIBUTES
  object_name                     : bad_boy                object_name                     : good_boy
  title                           : bad_boy Repository     title                           : good_boy Global Repository
  subject                         :                        subject                         :
  authors                       []:                        authors                       []: 
  keywords                      []:                        keywords                      []: 
  resolution_label                :                        resolution_label                :
  owner_name                      : bad_boy                owner_name                      : good_boy
  owner_permit                    : 7                      owner_permit                    : 7
  group_name                      : docu                   group_name                      : docu
  group_permit                    : 5                      group_permit                    : 5
  world_permit                    : 3                      world_permit                    : 3
  log_entry                       :                        log_entry                       :
  acl_domain                      : bad_boy                acl_domain                      : good_boy
  acl_name                        : dm_450xxxxx80000100    acl_name                        : dm_450xxxxx580000100
  language_code                   :                        language_code                   :
  mac_access_protocol             : nt                     mac_access_protocol             : nt
  security_mode                   : acl                    security_mode                   : acl
  auth_protocol                   :                        auth_protocol                   :
  index_store                     : DM_bad_boy_INDEX       index_store                     : DM_good_boy_INDEX
  folder_security                 : T                      folder_security                 : T
  effective_date                  : nulldate               effective_date                  : nulldate
  richmedia_enabled               : T                      richmedia_enabled               : T
  dd_locales                   [0]: en                     dd_locales                   [0]: en
  default_app_permit              : 3                      default_app_permit              : 3
  oldest_client_version           :                        oldest_client_version           :
  max_auth_attempt                : 0                      max_auth_attempt                : 0
  client_pcaching_disabled        : F                      client_pcaching_disabled        : F
  client_pcaching_change          : 1                      client_pcaching_change          : 1
  fulltext_install_locs        [0]: dsearch                fulltext_install_locs        [0]: dsearch
  offline_sync_level              : 0                      offline_sync_level              : 0
  offline_checkin_flag            : 0                      offline_checkin_flag            : 0
  wf_package_control_enabled      : F                      wf_package_control_enabled      : F
  macl_security_disabled          : F                      macl_security_disabled          : F
  trust_by_default                : T                      trust_by_default                : T
  trusted_docbases              []:                        trusted_docbases              []: 
  login_ticket_cutoff             : nulldate               login_ticket_cutoff             : nulldate
  auth_failure_interval           : 0                      auth_failure_interval           : 0
  auth_deactivation_interval      : 0                      auth_deactivation_interval      : 0
  dir_user_sync_on_demand         : F                      dir_user_sync_on_demand         : F
  check_client_version            : T                      check_client_version            : F
  audit_old_values                : T                      audit_old_values                : T
  docbase_roles                 []:                        docbase_roles                [0]: Global Registry
  approved_clients_only           : F                      approved_clients_only           : F
  minimum_owner_permit            : 2                      minimum_owner_permit            : 0
  minimum_owner_xpermit           :                        minimum_owner_xpermit           :
  dormancy_status                 :                        dormancy_status                 :

The only significant differences were the highlighted ones and the most obvious one was the attribute check_client_version, it was turned on in the bad_boy repository. Now that we finally had something to blame, the universe started making sense again ! We quickly turned this setting to false and could eventually connect to that recalcitrant docbase. But the question is still open: check against what ? What criteria was applied to refuse dqman access to bad_boy but to allow it to good_boy ? That was still not clear, even though we could work around it.
Now, who and why turned it on, that had to remain a mystery.
While we were at it, we also noticed another attribute which seemed to be related to the previous one: oldest_client_version.
Was there any other client_% attribute in dm_docbase_config ?

paste <(iapi good_boy -Udmadmin -Pdmadmin <<eoq | grep client
retrieve,c,dm_docbase_config
dump,c,l
quit
eoq) <(iapi bad_boy -Udmadmin -Pdmadmin <<eoq | grep client
retrieve,c,dm_docbase_config
dump,c,l
quit
eoq) | column -s $'\t' -t
  oldest_client_version           :      oldest_client_version           : 
  client_pcaching_disabled        : F    client_pcaching_disabled        : F
  client_pcaching_change          : 1    client_pcaching_change          : 1
  check_client_version            : F    check_client_version            : T
  approved_clients_only           : F    approved_clients_only           : F

Yes, but they looked quite harmless in the current context.
Thus, the relevant attributes here are check_client_version and oldest_client_version. Let’s discover a bit more about them.

Digging

As usual, the documentation is a bit scketchy about these attributes:

check_client_version Boolean S T means that the repository
                               servers will not accept connections
                               from clients older than the
                               version level specified in the
                               oldest_client_version property.
                               F means that the servers accept
                               connections from any client version.
                               The default is F.

oldest_client _version string(32) S Version number of the oldest
                                    Documentum client that will access
                                    this repository.
                                    This must be set manually. It is used
                                    by the DFC to determine how to
                                    store chunked XML documents. If
                                    check_client_version is set to T,then
                                    this value is also used to identify the
                                    oldest client version level that may
                                    connect to the repository.

But what is the client version ? Logically, it is the version of its DfCs or, for older clients, the version of the dmcl shared library.
So, if check_client_version is true, the client version is checked and if it is older than the one defined in oldest_client_version, the client is forbidden to connect. That makes sense except that in our case, oldest_client_version was empty. Maybe in such a case, the client has to match exactly the content server’s DfC version ? As dqMan was either using the dmcl40.dll library or an old Dfc version, it was rejected. Let’s verify these hypothesis with a 16.4 target repository.
Connecting from an ancient 5.3 client
We exhumed an old 5.3 CS installation to use its client part with the default configuration in the target docbase:

dmadmin@osboxes:~/documentum53$ idql dmtest -Udmadmin -Pdmadmin
 
 
Documentum idql - Interactive document query interface
(c) Copyright Documentum, Inc., 1992 - 2004
All rights reserved.
Client Library Release 5.3.0.115 Linux
 
 
Connecting to Server using docbase dmtest
[DM_SESSION_I_SESSION_START]info: "Session 0100c35080003913 started for user dmadmin."
 
 
Connected to Documentum Server running Release 16.4.0080.0129 Linux64.Oracle

Fine so far.
Let’s activate the dm_docbase_config.check_client_version in the target:

retrieve,c,dm_docbase_config
...
set,c,l,check_client_version
SET> T
...
OK
API> save,c,l
...
[DM_DCNFG_E_CANT_SAVE]error: "Cannot save dmtest docbase_config."
 
[DM_DCNFG_E_SET_OLDEST_CLIENT_VERSION_FIRST]error: "The docbase_config object attribute oldest_client_version has to be set before setting attribute check_client_version to T."

Interesting. At that time, this attribute was empty and yet the check_client_version was active. Is this constraint new in 16.4 or did the unknow administrator hack around this ? As I don’t have a 7.x repository available right now, I cannot test this point.
Let’s play by the rules and set oldest_client_version:

reset,c,l
set,c,l,oldest_client_version
16.4
save,c,l
OK
set,c,l,check_client_version
SET> T
...
OK
API> save,c,l
...
OK

Try connecting from the 5.3 client: still OK.
Maybe a reinit is necessary to actuate the changes:

reinit,c

Try again:

dmadmin@osboxes:~/documentum53$ idql dmtest -Udmadmin -Pdmadmin
&nbps;
&nbps;
Documentum idql - Interactive document query interface
(c) Copyright Documentum, Inc., 1992 - 2004
All rights reserved.
Client Library Release 5.3.0.115 Linux
 
 
Connecting to Server using docbase dmtest
Could not connect
[DM_SESSION_E_START_FAIL]error: "Server did not start session. Please see your system administrator or check the server log.
Error message from server was:
[DM_SESSION_E_AUTH_FAIL]error: "Authentication failed for user dmadmin with docbase dmtest."
 
"

So a reinit it required indeed.
Note the misleading error, it is not the authentication that is wrong but the client version validation. It is such wrong messages that make diagnosis of Documentum problems so hard and time-consuming. Anyway, let’s revert the check_client_version to F:

set,c,l,check_client_version
F
save,c,l
reinit,c

Try connecting: OK. So, the client version filtering is effective. Let’s try it with a 5.3 client version:

API> set,c,l,oldest_client_version
SET> 5.3
...
OK
API> save,c,l
...
OK
API> set,c,l,check_client_version
SET> T
...
OK
API> save,c,l
...
OK
API> reinit,c
...
OK

Try connecting: OK, that’s expected.
Let’s try it for a minimum 5.2 client version: it still works, which is expected too since the test client’s version is 5.3 and in my books 5.3 > 5.2.
Let’s try it for a miminum a 5.4 client version: the connection fails, so client version checking works as expected here.
Let’s try it for a miminum a 20.0 client version: the connection fails as expected. No check on the version’s value is done, which is quite understandable programmatically speaking, although a bit optimistic in the context of the turmoil Documentum went through lately.
Let’s go back to a more realistic value:

API> set,c,l,oldest_client_version
SET> 7.2
...
[DM_SESSION_E_AUTH_FAIL]error: "Authentication failed for user dmadmin with docbase dmtest."
 
 
API> save,c,l

Oops, interestingly, the last change did not make it because with the current setting so down the way into the future, the present client’s session was disconnected and there is no way to reconnect in order to revert it !
Let’s do the rollback from the database level directly:

sqlplus dmtest@orcl
 
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 10 16:25:56 2019
 
Copyright (c) 1982, 2016, Oracle. All rights reserved.
 
Enter password:
Last Successful login time: Mon Jun 10 2019 16:25:40 +02:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> update dm_docbase_config_s set check_client_version = 0;
 
1 row updated.
SQL> commit;
 
Commit complete.
 
quit;

Try to connect:

iapi dmtest@docker:1489
Please enter a user (dmadmin):
Please enter password for dmadmin:
 
 
OpenText Documentum iapi - Interactive API interface
Copyright (c) 2018. OpenText Corporation
All rights reserved.
Client Library Release 16.4.0070.0035
 
 
Connecting to Server using docbase dmtest
[DM_SESSION_E_AUTH_FAIL]error: "Authentication failed for user dmadmin with docbase dmtest."

Still not ok because the reinit is missing but for this we need to connect which we still cannot because of the missing reinit. To break this catch-22 situation, let’s cut the gordian knot and kill the dmtest docbase’s processes:

dmadmin@docker:~$ ps ajxf | grep dmtest
1 27843 27843 27843 ? -1 Ss 1001 0:00 ./documentum -docbase_name dmtest -security acl -init_file /app/dctm/dba/config/dmtest/server.ini
27843 27849 27843 27843 ? -1 S 1001 0:00 \_ /app/dctm/product/16.4/bin/mthdsvr master 0xe901fd2f, 0x7f8a50658000, 0x223000 50000 5 27843 dmtest /app/dctm/dba/log
27849 27850 27843 27843 ? -1 Sl 1001 0:03 | \_ /app/dctm/product/16.4/bin/mthdsvr worker 0xe901fd2f, 0x7f8a50658000, 0x223000 50000 5 0 dmtest /app/dctm/dba/log
27849 27861 27843 27843 ? -1 Sl 1001 0:03 | \_ /app/dctm/product/16.4/bin/mthdsvr worker 0xe901fd2f, 0x7f8a50658000, 0x223000 50000 5 1 dmtest /app/dctm/dba/log
27849 27874 27843 27843 ? -1 Sl 1001 0:03 | \_ /app/dctm/product/16.4/bin/mthdsvr worker 0xe901fd2f, 0x7f8a50658000, 0x223000 50000 5 2 dmtest /app/dctm/dba/log
27849 27886 27843 27843 ? -1 Sl 1001 0:03 | \_ /app/dctm/product/16.4/bin/mthdsvr worker 0xe901fd2f, 0x7f8a50658000, 0x223000 50000 5 3 dmtest /app/dctm/dba/log
27849 27899 27843 27843 ? -1 Sl 1001 0:03 | \_ /app/dctm/product/16.4/bin/mthdsvr worker 0xe901fd2f, 0x7f8a50658000, 0x223000 50000 5 4 dmtest /app/dctm/dba/log
27843 27862 27843 27843 ? -1 S 1001 0:00 \_ ./documentum -docbase_name dmtest -security acl -init_file /app/dctm/dba/config/dmtest/server.ini
27843 27863 27843 27843 ? -1 S 1001 0:00 \_ ./documentum -docbase_name dmtest -security acl -init_file /app/dctm/dba/config/dmtest/server.ini
27843 27875 27843 27843 ? -1 S 1001 0:00 \_ ./documentum -docbase_name dmtest -security acl -init_file /app/dctm/dba/config/dmtest/server.ini
27843 27887 27843 27843 ? -1 S 1001 0:00 \_ ./documentum -docbase_name dmtest -security acl -init_file /app/dctm/dba/config/dmtest/server.ini
27843 27901 27843 27843 ? -1 S 1001 0:00 \_ ./documentum -docbase_name dmtest -security acl -init_file /app/dctm/dba/config/dmtest/server.ini
27843 27944 27843 27843 ? -1 Sl 1001 0:06 \_ ./dm_agent_exec -docbase_name dmtest.dmtest -docbase_owner dmadmin -sleep_duration 0
27843 27962 27843 27843 ? -1 S 1001 0:00 \_ ./documentum -docbase_name dmtest -security acl -init_file /app/dctm/dba/config/dmtest/server.ini

and:

kill -9 -27843

After restarting the docbase, the connectivity was restored.
So, be cautious while experimenting ! Needless to say, avoid doing it in a production docbase or in any heavily used development docbase for that matter, or the wrath of the multiverses and beyond will fall upon you and you will be miserable for ever.
Connecting from a 7.3 client
The same behavior and error messages as with the precedent 5.3 client were observed with a more recent 7.3 client and, inferring from the incident above, later clients behave the same way.

Conclusion

We never stop learning stuff with Documentum ! While this client version limiting feature looks quite exotic, it may make sense in order to avoid surprises or even corruptions when using newly implemented extensions or existing but changed areas of the content server. It is possible that new versions of the DfCs behave differently from older ones in dealing with the same functionalities and Documentum had no better choice but to cut the older versions off to prevent any conflict. As usual, the implementation looks a bit hasty with inapt error messages costing hours of investigation and the risk to cut oneself off a repository.

Cet article An exotic feature in the content server: check_client_version est apparu en premier sur Blog dbi services.

Installing the OE demo schema on 18cXE

The Anti-Kyte - Sat, 2019-06-15 13:18

It’s always a good idea to be wary of assuming too much.
Looking at the current Conservative Party Leadership contest, you might assume that a fantasy Brexit policy and a history of class A drug use were pre-requisites for the job of Prime Minister.
You may further assume that one is a result of the other.
That last assumption is unlikely however, unless the smoking, snorting and otherwise ingesting of illicit substances is widespread across all of the other major political parties. Then again…

For my part, I’ve had to correct some of my assumptions about the process for installing the OE sample schema into Oracle 18cXE running on CentOS 7.
What follows is a quick guide on how to accomplish this…without all the head-scratching over apparently spurious errors.
Specifically, I will be covering :

  • getting the OE schema installation scripts
  • checking the pre-requisites for the OE schema installation have been met
  • preparing the scripts for execution
  • performing the installation

Before we go too much further though, it’s probably wise to state some assumptions…

Assumptions

These steps assume that you’re running 18cXE on CentOS or some other Red Hat compatible distro ( e.g. Oracle Linux, Fedora).
We’re only installing the OE schema here. I already have the HR schema installed on the database and I do not want to drop and re-create it.
If you want to install all of the demo schemas then you’ll need to check the instructions in the README.md file once you’ve downloaded the installation scripts.
Speaking of which…

Finding the demo scripts

As stated in the documentation, only the scripts for the HR schema are included in the Oracle software.
If you want the other schemas, you need to download them from Oracle’s GitHub repo.

Although we’re only interested in the OE schema at the moment, the source code is provided in a single archive file.
Download the zip for the appropriate database release ( 18c in my case) and we should now have a file looking something like :

-rw-rw-r-- 1 mike mike 28882177 Jun  9 17:03 db-sample-schemas-18c.zip
Pre-requisites for installing the OE schema

Before I run off and start playing with my new zip file, I should really check that I’ve got everything I need to ensure that the setup will go smoothly.
The pre-requisites are :

  • the HR schema must already be installed and unlocked in the PDB that you are installing into
  • Oracle Spatial must be enabled
  • the installation scripts need to run on the database server (or a filesystem visible to it)

To check that HR is already available in the PDB (xepdb1 in my case) :

alter session set container = xepdb1;

select username, account_status
from cdb_users
where username = 'HR'
and con_id = sys_context('userenv', 'con_id');

USERNAME   ACCOUNT_STATUS 
---------- ---------------
HR         OPEN           

If the query does not return any rows then you will need to install the HR schema. This can be done following the instructions in the aforementioned documentation.

NOTE – before you do this it’s a good idea to double check to make sure that you are in the correct container database :

select sys_context('userenv', 'con_name') from dual;

If the ACCOUNT_STATUS is LOCKED then you need to unlock the HR user as the OE creation script will attempt to connect to the database as HR. To do this, connect to the target PDB as a user with the ALTER USER privilege (e.g. SYSTEM) and run :

alter user hr account unlock;

User HR altered

As I’m on 18cXE, Oracle Spatial should be enabled. Thanks to Norman, I know that I can confirm this by running :

select parameter, value       
from v$option 
where regexp_like(parameter, 'spatial', 'i')
/

PARAMETER            VALUE     
-------------------- ----------
Spatial              TRUE      

Next, we need to upload the zip file to the Database Server (which I’ve done using sftp).

Preparing the Install scripts

Now it’s on the correct machine, I need to change the ownership to oracle…

sudo chown oracle:oinstall db-sample-schemas-18c.zip

ls -l db-sample-schemas-18c.zip 
-rw-rw-r--. 1 oracle oinstall 28882177 Jun 10 12:34 db-sample-schemas-18c.zip

…because I want to move it to the appropriate directory in ORACLE_HOME…

sudo mv db-sample-schemas-18c.zip $ORACLE_HOME/demo/schema
cd $ORACLE_HOME/demo/schema

…and extract it as oracle…

sudo su oracle
unzip db-sample-schemas-18c.zip

cd db-sample-schemas-18c

… and create a directory to hold the log file that’s output when we run the installation…

mkdir order_entry/logs

Now we’ve extracted the files, you may be tempted to have a quick peek at the code.

Initially you may be somewhat surprised. For example, in order_entry/oe_main.sql you can see :

DEFINE vscript = __SUB__CWD__/order_entry/coe_&vrs

Is __SUB__CWD__ some SQL*Plus magic that has eluded you all these years ?

Well, no. Looking that the README.md file, we can confirm that it’s simply a placeholder that we need to replace with a valid absolute path to the scripts.

Fortunately, the README.md file also contains the code required to achieve this.
Simply ensure that we’re in the db-sample-schemas-18c directory and run :

perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat 

After running this, we can see that the line in oe_main.sql now reads :

DEFINE vscript = /opt/oracle/product/18c/dbhomeXE/demo/schema/db-sample-schemas-18c/order_entry/coe_&vrs
Performing the installation

The main order entry script is in the order_entry sub-directory and is called oe_main.sql.
This script accepts 9 positional parameters :

  1. the password for the OE user that will be created as part of this installation
  2. the default tablespeace for OE
  3. the temporary tablespace for OE
  4. the password for the pre-existing HR user
  5. the SYS password
  6. the directory path for the data files (these are in the current directory as they are included in the zip file)
  7. the absolute path for the log directory we just created ( including a trailing slash)
  8. the version (it’s v3 in this case)
  9. the SQL*Plus connect string for the target database

The “version” the script asks for is appended to the basename of some of the scripts that will be run as part of the install.
To obtain this, simply run :

ls -1 *_v?.sql

ccus_v3.sql
cidx_v3.sql
cmnt_v3.sql
coe_v3.sql
cord_v3.sql
cwhs_v3.sql
doe_v3.sql
loe_v3.sql
pcus_v3.sql
poe_v3.sql
pord_v3.sql
pwhs_v3.sql

From this, I can see that the value of version that the script needs is “v3”.

Now, connect to the database via SQL*Plus as SYSTEM. Note that, as I’ve not setup an Oracle client on the server, I need to specify the server name, tns port and pdb name in the connect string :

sqlplus system@frea.virtualbox:1522/xepdb1

Finally, we can run the install script :

@oe_main.sql oe_user_password users temp hr_user_password sys_password . /opt/oracle/product/18c/dbhomeXE/demo/schema/db-sample-schemas-18c/order_entry/logs/ v3 frea.virtualbox:1522/xepdb1

The log file generated during the installation will contain everything that now whizzes up the screen.
You can find it after the run in the logs directory we created earlier :

ls -l logs
total 8
-rw-r--r--. 1 oracle oinstall 6244 Jun 10 13:50 oe_oc_v3.log

Even if there aren’t any particularly alarming errors in the logfile, it would be nice to verify that all has gone as intended.
First we can check that the OE schema now contains the expected number of objects of each type :

select object_type, 
    count(object_name) as "Number of objects"
from dba_objects
where owner = 'OE'
group by object_type
order by 2 desc;

OBJECT_TYPE          Number of objects
-------------------- -----------------
INDEX                               48
TYPE                                37
LOB                                 15
TABLE                               14
VIEW                                13
SYNONYM                              6
TRIGGER                              4
TYPE BODY                            3
SEQUENCE                             1
FUNCTION                             1

10 rows selected. 

We can also confirm that data has been loaded into the tables :

set serverout on size unlimited
declare
    l_count pls_integer;
begin
    for r_tab in (
        select dbms_assert.sql_object_name(owner||'.'||table_name) oe_table
        from dba_tables
        where owner = 'OE'
        and nested = 'NO'
        order by table_name)
    loop
        execute immediate 'select count(*)  from '||r_tab.oe_table 
            into l_count;
        dbms_output.put_line(upper(r_tab.oe_table)||' contains '||l_count||' records.');
    end loop;
end;
/

OE.CUSTOMERS contains 319 records.
OE.INVENTORIES contains 1112 records.
OE.ORDERS contains 105 records.
OE.ORDER_ITEMS contains 665 records.
OE.PRODUCT_DESCRIPTIONS contains 8640 records.
OE.PRODUCT_INFORMATION contains 288 records.
OE.PROMOTIONS contains 2 records.
OE.WAREHOUSES contains 9 records.


PL/SQL procedure successfully completed.

The Country may be descending into chaos but at least we’ve got a working OE schema to play around with.

Running Oracle JET as Progressive Web App

Andrejus Baranovski - Sat, 2019-06-15 09:44
Progressive Web Apps (PWA) topic is a hot thing in web development these days. Read more about it - Progressive Web Apps. The beauty and power behind PWA - user can install a web app to his mobile device, without going through the app store. This simplifies update process too, when a new version of the app is available, the user will get it straight away, because it is essentially a Web page, wrapped to look like an installed app.

Inspired by this post - A Simple Progressive Web App Tutorial, I decided to add PWA config into Oracle JET app and test how it works (on Android, didn't test on iOS, but there is nothing JET specific, if PWA is supported on iOS, it should work).

Oracle JET PWA sample app is deployed on Heroku (PWA will work only if the app is coming through HTTPS) and available under this URL. The sample app is available on GitHub repo. Node.js wrapper for this sample is available in another GitHub repo, you can use it to deploy on Heroku or another service.

Access JET app URL, the app will be loaded and you should see Android notification in the bottom. Google Chrome mobile browser automatically is recognizing PWA app by manifest and offers to "install" it by adding to the home screen:


Select notification and you will get a confirmation message:


Select "Add" and Web app will be added to the home screen. It will look like a real mobile app for the user. For example, the user could get runtime stats for the app, check storage and data usage metrics:


The app is added to the home screen (look for Oracle  JET icon):


Select the app icon and app will be opened. There is no URL address bar in the header and indeed it looks like a mobile app, not a Web page:


The app will be recognized as PWA, if certain config steps were implemented. One of them - you need to add manifest file (add it in Oracle JET in the same folder as index.html) and provide app icons, name, etc.:


The manifest file must be included through a reference in the app entry point - index page (Oracle JET index.html page for example):


In addition to manifest, the app must define a service worker (same as manifest file, you can create this file in the same directory as Oracle JET index.html). PWA doesn't only bring the visual experience of the real app to the Web application. You can define a cache store for the app files, this means next time when offline - app files will load from local cache storage, there will be no need to download them from the internet:


Service worker can be registered from main.js file where Oracle JET context is initialized on the application initial load. Add service worker registration at the bottom of main.js:


The idea of this post was to share a simple example of PWA for Oracle JET. This should help you to get started quickly with PWA support config for Oracle JET app.

Shell Scripting Using set -v

Michael Dinh - Sat, 2019-06-15 08:22

set -v : Print shell input lines as they are read.

show_gds_status.sh

#!/bin/sh
##############################
# GDSCTL> configure -width 132
# GDSCTL> configure -save_config
##############################
. ~/gsm1.sh
set -evx
gdsctl -show << END
status
databases
services
exit
END
exit

Excute show_gds_status.sh

[oracle@SLC02PNY GDS]$ ./show_gds_status.sh
gdsctl -show << END
status
databases
services
exit
END
+ gdsctl -show
gsm       : GSM1
TNS_ADMIN : /u01/app/oracle/product/18.0.0/gsmhome_1/network/admin
driver    : jdbc:oracle:thin:
resolve   : QUAL_HOSTNAME
timeout   : 150
log_level : OFF
version   : 18.0.0.0.0
width     : 132
verbose   : ON
spool     : OFF
showtime  : OFF
GDSCTL: Version 18.0.0.0.0 - Production on Sat Jun 15 13:01:21 UTC 2019

Copyright (c) 2011, 2018, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to GSM1
GDSCTL>
Alias                     GSM1
Version                   18.0.0.0.0
Start Date                15-JUN-2019 12:22:28
Trace Level               off
Listener Log File         /u01/app/oracle/diag/gsm/SLC02PNY/gsm1/alert/log.xml
Listener Trace File       /u01/app/oracle/diag/gsm/SLC02PNY/gsm1/trace/ora_9504_140547635764096.trc
Endpoint summary          (ADDRESS=(HOST=SLC02PNY.localdomain)(PORT=1571)(PROTOCOL=tcp))
GSMOCI Version            2.2.1
Mastership                Y
Connected to GDS catalog  Y
Process Id                9507
Number of reconnections   0
Pending tasks.     Total  0
Tasks in  process. Total  0
Regional Mastership       TRUE
Total messages published  152
Time Zone                 +00:00
Orphaned Buddy Regions:
     None
GDS region                region1
Network metrics:
   Region: region2 Network factor:0

GDSCTL>
Database: "chi" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region2
   Service: "prim" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Service: "stby" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     sales%11
Database: "sfo" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1
   Service: "prim" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Service: "stby" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     sales%1

GDSCTL>
Service "prim.sales.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "sales%1", name: "sales", db: "sfo", region: "region1", status: ready.
Service "stby.sales.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "sales%11", name: "sales", db: "chi", region: "region2", status: ready.

GDSCTL>
exit
+ exit
[oracle@SLC02PNY GDS]$

help set

[oracle@SLC02PNY GDS]$ help set
set: set [-abefhkmnptuvxBCHP] [-o option-name] [--] [arg ...]
    Set or unset values of shell options and positional parameters.

    Change the value of shell attributes and positional parameters, or
    display the names and values of shell variables.

    Options:
      -a  Mark variables which are modified or created for export.
      -b  Notify of job termination immediately.
      -e  Exit immediately if a command exits with a non-zero status.
      -f  Disable file name generation (globbing).
      -h  Remember the location of commands as they are looked up.
      -k  All assignment arguments are placed in the environment for a
          command, not just those that precede the command name.
      -m  Job control is enabled.
      -n  Read commands but do not execute them.
      -o option-name
          Set the variable corresponding to option-name:
              allexport    same as -a
              braceexpand  same as -B
              emacs        use an emacs-style line editing interface
              errexit      same as -e
              errtrace     same as -E
              functrace    same as -T
              hashall      same as -h
              histexpand   same as -H
              history      enable command history
              ignoreeof    the shell will not exit upon reading EOF
              interactive-comments
                           allow comments to appear in interactive commands
              keyword      same as -k
              monitor      same as -m
              noclobber    same as -C
              noexec       same as -n
              noglob       same as -f
              nolog        currently accepted but ignored
              notify       same as -b
              nounset      same as -u
              onecmd       same as -t
              physical     same as -P
              pipefail     the return value of a pipeline is the status of
                           the last command to exit with a non-zero status,
                           or zero if no command exited with a non-zero status
              posix        change the behavior of bash where the default
                           operation differs from the Posix standard to
                           match the standard
              privileged   same as -p
              verbose      same as -v
              vi           use a vi-style line editing interface
              xtrace       same as -x
      -p  Turned on whenever the real and effective user ids do not match.
          Disables processing of the $ENV file and importing of shell
          functions.  Turning this option off causes the effective uid and
          gid to be set to the real uid and gid.
      -t  Exit after reading and executing one command.
      -u  Treat unset variables as an error when substituting.
================================================================================
      -v  Print shell input lines as they are read.
================================================================================
      -x  Print commands and their arguments as they are executed.
      -B  the shell will perform brace expansion
      -C  If set, disallow existing regular files to be overwritten
          by redirection of output.
      -E  If set, the ERR trap is inherited by shell functions.
      -H  Enable ! style history substitution.  This flag is on
          by default when the shell is interactive.
      -P  If set, do not follow symbolic links when executing commands
          such as cd which change the current directory.
      -T  If set, the DEBUG trap is inherited by shell functions.
      --  Assign any remaining arguments to the positional parameters.
          If there are no remaining arguments, the positional parameters
          are unset.
      -   Assign any remaining arguments to the positional parameters.
          The -x and -v options are turned off.

    Using + rather than - causes these flags to be turned off.  The
    flags can also be used upon invocation of the shell.  The current
    set of flags may be found in $-.  The remaining n ARGs are positional
    parameters and are assigned, in order, to $1, $2, .. $n.  If no
    ARGs are given, all shell variables are printed.

    Exit Status:
    Returns success unless an invalid option is given.
[oracle@SLC02PNY GDS]$

HIUG Interact 2019 Conference Schedule

Jim Marion - Fri, 2019-06-14 11:15

Tomorrow I fly to Orlando for the 2019 HIUG Interact conference. I'm almost packed. I wanted to share my schedule with anyone attending. As usual, we start the conference off right with a session Sunday afternoon.

I am on site for the whole conference and don't leave until Thursday morning. I plan to be in sessions all day every day. I look forward to seeing you there!

Are you presenting? If so, add your session to the comments below

Trouble-shooting

Jonathan Lewis - Fri, 2019-06-14 03:19

Here’s an answer I’ve just offered on the ODC database forum to a fairly generic type of problem.

The question was about finding out why a “program” that used to take only 10 minutes to complete is currently taking significantly longer. The system is running Standard Edition, and the program runs once per day. There’s some emphasis on the desirability of taking action while the program is still running with the following as the most recent statement of the requirements:

We have a program which run daily 10minutes and suddenly one day,it is running for more than 10minutes…in this case,we are asked to look into the backend session to check what exactly the session is doing.I understand we have to check the events,last sql etc..but we need to get the work done by that session in terms of buffergets or physical reads(in case of standard edition)

1)In this case,we cannot enable trace to the session as it already started running and 10 minutes passed way.If we enable trace at this point,will it gives the required info?

2)To check the statistics of this specific session,what is the best way to proceed and this is my initial question.One of my friend pointed out to check v$sess_io and he is not sure whether it will return cumulative values because this view has only sid and not having serial#..and sid is not unique

So, answering the questions as stated, with a little bit of padding:

1) In this case, we cannot enable trace to the session as it already started running and 10 minutes passed way.If we enable trace at this point,will it gives the required info?

If the session has started and has been running for 10 minutes it’s still possible to force tracing into the session and, depending what the program does, you may be lucky enough to get enough information in the trace/tkprof file to help you. The “most-approved” way of doing this for a session is probably through a call to dbms_monitor.session_trace_enable(), but if that’s a package that Standard Edition is not licensed to use then there’s dbms_system.set_sql_trace_in_session().

If this doesn’t help, and if the session is still live and running, you could also look at v$open_cursor for that SID to see what SQL statements (sql_id, child_address, last_active_time and first 60 characters of the text) are still open for that session, then query v$sql for more details about those statements (using sql_id and child_address). The stats you find in those statements are accumulated across all executions by all sessions from the moment the cursor went into the library cache, but if this is a program that runs once per day then it’s possible that the critical statement will only be run by that one session, and the cursor from the previous day will have aged out of the library cache so that what you see is just today’s run.

Since you’re on Standard Edition and don’t have access to the AWR you should have installed Statspack – which gives you nearly everything that you can get from the AWR reports (the most significant difference being the absence of the v$active_session_history – but there are open-source emulators that you can install as a fairly good substitute for that functionality). If there is one statement in your program that does a lot of work then it’s possible that it might be one of the top N statements in a Statspack snapshot.

If this program is a known modular piece of code could you alter the mechanism that calls it to include calls to enable session tracing at the start of the program (and disable it, perhaps, at the end of the progam).  This might be by modifying the code directly, or by modifying the wrapper that drive the program, or by adding a logon trigger if there’s a mechanism that would allow Oracle to recognise the specific session that runs this particular program, or if something sets an identifiable (and unambiguous) module and/or action as part of calling the program then you could use the dbms_monitor package to enable tracing for (e.g.) a particular module and action on a permanent basis.

2) To check the statistics of this specific session,what is the best way to proceed and this is my initial question.One of my friend pointed out to check v$sess_io and he is not sure whether it will return cumulative values because this view has only sid and not having serial#..and sid is not unique

An answer: the stats are for one SID and SERIALl#, whether you’re looking at v$sess_io, v$sesstat, v$session_event, v$sess_time_model and any other v$sesXXX views that I can’t recall off-hand.  In passing, if you can add a wrapper to the calling code, capturing sessions activity stats (v$sesstat) wait time (v$session_event) and time model summary (v$sess_time_model) is a very useful debugging aid.

And an explanation: the “session” array is a fixed size array, and the SID is the array subscript of the entry your session acquired at logon. Since the array is fixed size Oracle has to keep re-using the array entries so each time it re-uses an array entry it increments the serial# so that (sid, serial#) becomes the unique identifier across history[1]. As it acquires the array entry it also resets/re-initializes all the other v$sesXXX arrays/linked list/structures.

The one thing to watch out for when you try to capture any of the session numbers is that you don’t query these v$sesXXX things twice to find a difference unless you also capture the serial# at the same time so that you can be sure that the first session didn’t log off and a second session log on and reuse the same SID between your two snapshots.  (It’s quite possible that this will be obvious even if you haven’t captured the serial#, because you may spot that some of the values that should only increase with time have decreased)

 

Footnote

[1] I think there was a time when restarting an instance would reset the serial# to 1 and the serial# has to wrap eventually and the wrap used to be at 65536 because it was stored as a two-byte number – which doesn’t seem to have changed.  Nowadays the serial# seems to be maintained across instance restart (I wonder how that works with carefully timed instance crashes), and given the amount of history that Oracle could maintain in the AWR I suppose there could be some scope for connect two pieces of history that were really from two different sessions.

 

“Before Header” Branch

Jeff Kemp - Thu, 2019-06-13 23:27

It’s well known that after processing a page one often needs to add a Branch so the user is taken to another page, e.g. to start the next step in a process. It’s less common to need a Branch that is evaluated before the page is shown. This is a “Before Header” Branch and I use it when the user might open a page but need to be redirected to a different one.

A good example is a page that is designed to direct the user to two or more different pages depending on what data they requested.

Another way that a branch like this can be useful is where a user might navigate to a page that should show a record, but if the record ID is not set, I might want the page to automatically redirect the user back to a report page instead.

Of course, you have to take some care with these sorts of branches; if the target page also has its own “Before Header” branch, that will also be evaluated; if the user ends up in a loop of branches the page will fail to load (with a “ERR_TOO_MANY_REDIRECTS” error).

This is the order in which various steps are done before APEX shows a page:

  1. Authentication check
  2. Verify session
  3. “Before Header” Branches
  4. Authorization check
  5. Computations
  6. “Before Header” Processes
  7. Regions, etc.

One of the implications of the above order is that any computations or processes that set or change application state (e.g. an application item) are not run before it evaluates conditions or attributes for any “Before Header” Branches. This applies regardless of whether the computations or processes are defined on the page, or defined globally in Shared Components. This little detail tripped me up today, because I had a branch that I needed to run based on a condition that relied on state that should have been set by a “Before Header” process defined globally.

If you need to redirect a user to a different page on the basis of any application state that is set by a computation or process, you can use a PL/SQL Process instead that does the redirect. The PL/SQL code to use is something like:

apex_util.redirect_url(p_url => apex_page.get_url(p_page => 1));

Warning: redirect_url raises the exception ORA-20876: Stop APEX Engine, so it’s probably preferable to avoid combining this with other PL/SQL code that might need to be committed first.

Batch Query Reduced from 12 hours to 45 Minutes

Bobby Durrett's DBA Blog - Thu, 2019-06-13 16:38

I was asked to look at a batch SQL query that ran for 12 hours on June 4th. I messed around with some other ideas and then went back to my best method for tuning long running batch queries. I ripped it apart. My code ran in 45 minutes.

This only works if you have the code and can change it. I work with a lot of PL/SQL code in Unix shell scripts running SQL*Plus. If someone comes to me with a SQL statement in this kind of code, I do not tear my hair out trying to tune it in some clever way. Also, this assumes that I cannot use a SQL Profile. SQL Profiles are my first choice for production performance problems. But for long running SQL that I have full control over and cannot use a SQL Profile I tear it apart.

Ripping or tearing a SQL statement apart means that I become the optimizer. In this case the problem query joined 5 tables. It summarized 3 months of data from a large fact table and the other 4 tables were joined together with the fact table. I replaced the one query with 5 queries each of which saved their results in a table. This first query summarized the fact table and the remaining four joined one more table to the current results. Something like this:

  • Summarize 3 months of fact table data – table 1
  • Join table 2 on surrogate key
  • Join table 3 on surrogate key
  • Join table 4 on natural keys
  • Join table 5 on natural keys

So, I created 5 tables each of which held the results of the previous joins. I dropped the tables as I was done with them to save space.

I have a feeling that I could use some clever hint to force the join order and access methods to match my 5 queries. But my time is short and this works, so I did not bother trying. When you have a query that runs for 12 hours it’s not very motivating to try various hints to get it to run faster. How long do you wait for each try before you give up? Working on one table at a time is nice. I have had this approach work for me time and time again. It is almost a mechanical process without a lot of painful thinking.

Anyway, I pass this on to the internet. People may think that breaking up a 5 table join into 5 queries is ugly, but it works.

Bobby

Categories: DBA Blogs

Another On Call Week, Another SQL Profile (or two)

Bobby Durrett's DBA Blog - Thu, 2019-06-13 16:14

I was on call again last week and Sunday night I used a SQL Profile to fix a performance problem. We rolled out some updates Saturday night and the modified query ran fine in some cases, but others ran for hours. When I got on there were 60 sessions running the bad plan and the load on the system was very high. It took me a while to identify the good plan, but then I found it, put in a SQL Profile, killed the 60 bad sessions, and the problem jobs ran in 2 minutes each. A similar situation came up Monday morning after I went off on call and a coworker took over and he ended up applying another SQL Profile on a similar query.

I spent the past couple of hours doing my typical SQL tuning exercise to see if I could figure out why Sunday’s query sometimes chose the bad plan.

The typical scenario includes these elements:

  1. Partitioned table with some near empty partitions and a lot of full partitions
  2. Bind variables used to determine partition choice

In our case we have certain tables that partition data by a code number. Certain numbers were used in the past or for other reasons are not active now. My guess is that the bad plans that we see come from bind variable values that point to the empty partitions. The bad plan works fine with empty partitions but then the optimizer uses it on full partitions and the query spins for hours.

I started to research this further to see if I could come up with a better fix than putting in SQL Profiles but did not get very far. I thought I would just pass this post along as is.

Bobby

P.S. I originally wrote this June 4th, but decided to just publish as is today.

Categories: DBA Blogs

Parallel Fun – 2

Jonathan Lewis - Thu, 2019-06-13 12:24

I started writing this note in March 2015 with the following introductory comment:

A little while ago I wrote a few notes about a very resource-intensive parallel query. One of the points I made about it was that it was easy to model, and then interesting to run on later versions of Oracle. So today I’m going to treat you to a few of the observations and notes I made after modelling the problem; and here’s the SQL to create the underlying objects:

Unfortunately I failed to do anything more with the model I had created until a few days ago (June 2019 – in case I stall again) when a related question came up on the ODC database forum. This time I’m ready to go a little further – so I’ll start with a bait-and-switch approach. Here are the first few lines (omitting the SQL) of an SQL Monitor report from an instance of 18.3 – is this a power-crazed machine or what ?


Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  TEST_USER (169:11324)
 SQL ID              :  73y5quma4jnw4
 SQL Execution ID    :  16777216
 Execution Started   :  06/13/2019 22:06:32
 First Refresh Time  :  06/13/2019 22:06:32
 Last Refresh Time   :  06/13/2019 22:07:03
 Duration            :  31s
 Module/Action       :  MyModule/MyAction
 Service             :  SYS$USERS
 Program             :  sqlplus@linux183.localdomain (TNS V1-V3)
 Fetch Calls         :  591

Global Stats
=========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes |
=========================================================================================
|      14 |    3.18 |     0.00 |        0.05 |       11 |   591 |  25978 |   62 |  13MB |
=========================================================================================

Parallel Execution Details (DOP=3 , Servers Allocated=6730)
==========================================================================================

It didn’t take long to run the query, only about 31 seconds. But the thing to notice in the report is that while the DOP is reported as 3, the number of “Servers Allocated” is a massive 6,730. So the big question – before I show you more of the report, explain what’s happening, and supply the code to build the model: how many PX processes did I actually start.

Here’s a little more of the output:


Parallel Execution Details (DOP=3 , Servers Allocated=6730)
==========================================================================================================================================================
|      Name      | Type  | Group# | Server# | Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  |        Wait Events         |
|                |       |        |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes |         (sample #)         |
==========================================================================================================================================================
| PX Coordinator | QC    |        |         |      14 |    3.13 |          |        0.05 |       11 |  23727 |      |     . | PX Deq: Join ACK (5)       |
|                |       |        |         |         |         |          |             |          |        |      |       | PX Deq: Signal ACK EXT (2) |
|                |       |        |         |         |         |          |             |          |        |      |       | sql_id: 6405a2hc50bt4 (1)  |
| p004           | Set 1 |      1 |       1 |    0.00 |    0.00 |          |             |          |    180 |      |     . | library cache: mutex X (1) |
|                |       |        |         |         |         |          |             |          |        |      |       |                            |
| p005           | Set 1 |      1 |       2 |    0.00 |    0.00 |          |             |          |    100 |      |     . |                            |
| p006           | Set 1 |      1 |       3 |    0.00 |    0.00 |          |             |          |     90 |      |     . |                            |
| p000           | Set 1 |      2 |       1 |    0.01 |    0.01 |          |             |          |        |      |     . |                            |
| p001           | Set 1 |      2 |       2 |    0.02 |    0.02 |          |             |          |        |      |     . |                            |
| p002           | Set 2 |      2 |       1 |    0.01 |    0.01 |     0.00 |             |          |    944 |   32 |   7MB |                            |
| p003           | Set 2 |      2 |       2 |    0.01 |    0.01 |     0.00 |             |          |    937 |   30 |   7MB |                            |
==========================================================================================================================================================

Despite “allocating” 6,730 servers Oracle is only admitting to having used 7 of them -so let’s take a closer look at how they’re used. There are two groups, and we have one set of 3 slaves in group 1, and two sets of two slaves in group 2. (It looks to me as if the Group# and Type columns should be the other way around given the hierarchy of group / type / server#). We can understand a little more of what these numbers mean if we look at the execution plan – particularly the special columns relating to Data Flow Operations (DFOs) and “DFO trees”.


SQL Plan Monitoring Details (Plan Hash Value=3398913290)
========================================================================================================================================================================
| Id |          Operation           |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity |      Activity Detail       |
|    |                              |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |        (# samples)         |
========================================================================================================================================================================
|  0 | SELECT STATEMENT             |          |         |      |        32 |     +0 |     1 |     8846 |      |       |     . |     2.70 | Cpu (1)                    |
|  1 |   FILTER                     |          |         |      |        32 |     +0 |     1 |     8846 |      |       |     . |     5.41 | PX Deq: Signal ACK EXT (2) |
|  2 |    PX COORDINATOR            |          |         |      |        32 |     +0 |     5 |     8846 |      |       |     . |          |                            |
|  3 |     PX SEND QC (RANDOM)      | :TQ20002 |    9146 |  128 |        29 |     +2 |     2 |     8846 |      |       |     . |          |                            |
|  4 |      HASH JOIN BUFFERED      |          |    9146 |  128 |        29 |     +2 |     2 |     8846 |      |       |   9MB |          |                            |
|  5 |       PX RECEIVE             |          |    8846 |   11 |        14 |     +2 |     2 |     8846 |      |       |     . |          |                            |
|  6 |        PX SEND HYBRID HASH   | :TQ20000 |    8846 |   11 |         1 |     +0 |     2 |     8846 |      |       |     . |          |                            |
|  7 |         STATISTICS COLLECTOR |          |         |      |         1 |     +0 |     2 |     8846 |      |       |     . |          |                            |
|  8 |          PX BLOCK ITERATOR   |          |    8846 |   11 |         1 |     +0 |     2 |     8846 |      |       |     . |          |                            |
|  9 |           TABLE ACCESS FULL  | T2       |    8846 |   11 |         1 |     +0 |    23 |     8846 |   24 |   1MB |     . |          |                            |
| 10 |       PX RECEIVE             |          |   50000 |  116 |        14 |     +2 |     2 |     2509 |      |       |     . |          |                            |
| 11 |        PX SEND HYBRID HASH   | :TQ20001 |   50000 |  116 |         1 |     +0 |     2 |     2509 |      |       |     . |          |                            |
| 12 |         PX BLOCK ITERATOR    |          |   50000 |  116 |         1 |     +0 |     2 |     2509 |      |       |     . |          |                            |
| 13 |          TABLE ACCESS FULL   | T1       |   50000 |  116 |         1 |     +0 |    26 |     2509 |   38 |  12MB |     . |          |                            |
| 14 |    PX COORDINATOR            |          |         |      |        31 |     +1 |  8978 |     2252 |      |       |     . |    13.51 | PX Deq: Join ACK (5)       |
| 15 |     PX SEND QC (RANDOM)      | :TQ10000 |       1 |   77 |        32 |     +0 |  6667 |     3692 |      |       |     . |          |                            |
| 16 |      PX BLOCK ITERATOR       |          |       1 |   77 |        32 |     +0 |  6667 |    92478 |      |       |     . |     2.70 | Cpu (1)                    |
| 17 |       TABLE ACCESS FULL      | T3       |       1 |   77 |        32 |     +0 | 53118 |    92478 |   32 |   8MB |     . |    67.57 | Cpu (25)                   |
========================================================================================================================================================================

The “Name” column shows us that we have two DFO trees (:TQ2nnnn, and :TQ1nnnn) – this is why we see two “groups” in PX server detail, and why those groups can have difference deggrees of parallelism.

Looking at the general shape of the plan you can see that operation 1 is a FILTER operation with two child operations, one at operation 2 the other at operation 14. So we probably have a filter subquery in place operated as DFO tree #1 while the main query is operated as DFO tree #2. This means the main query is running at DOP = 2 (it’s a hash join with hash distribution so it needs two sets of slave processes so all the details agree with what we’ve learned abaout Group# 2 above); and the subquery is operating a DOP = 3 – and it’s using only one set of slave processes.

There is a little anomaly in the number of Execs of operation 14 – at some point I will examine this more closely, but it might simply be a reporting error that has added the number of Execs of its child operations to its own Execs, it might be something to do with counting in Exec calls by its parent, it might be a side effect of scalar subquery caching. I’ll worry about it when I have a good reason to do so. What I want to look at is the Execs of operations 15/16, the PX Block Iterator / PX Send QC. There are 6,667 reports of PX slave executing, and that matches up quite nicely with the 6,730 reported “Servers Allocated” – so it would appear that Oracle says it’s allocating a server whenever it uses a server. But does it really “allocate” (and, presumably, de-allocate).

Here’s how you find out – you run the query again, taking various snapshot and looking for cross-references. I’ve got some results from v$pq_tqstat and v$pq_slace for the run that produced the SQL Monitor report above, and some of the QC session stats and enqueue stats for a subsequent run. This is what we see:


select  process, count(*) 
from    v$pq_tqstat 
group by 
        process 
order by 
        process
;


PROCESS                    COUNT(*)
------------------------ ----------
P000                              3
P001                              3
P002                              2
P003                              2
P004                           2225
P005                           2214
P006                           2218
QC                             2243


SQL> select slave_name, sessions from V$pq_slave order by slave_name;

SLAV   SESSIONS
---- ----------
P000          1
P001          1
P002          1
P003          1
P004       2242
P005       2242
P006       2242

Key Session Stats
=================
Name                                                                         Value                                                                          
----                                                                         -----                                                                          
opened cursors cumulative                                                    6,955                                                                          
user calls                                                                  20,631                                                                          
recursive calls                                                             20,895                                                                          
enqueue requests                                                            22,699                                                                          
enqueue conversions                                                         13,610                                                                          
enqueue releases                                                            15,894                                                                          
parse count (total)                                                          6,857                                                                          
execute count                                                                6,966                                                                          
DFO trees parallelized                                                           2
Parallel operations not downgraded                                           2,268

Key Enqueue Stats
=================
Type Short name                   Requests       Waits     Success      Failed    Wait m/s                                                                  
---- ----------                   --------       -----     -------      ------    --------                                                                  
DA   Slave Process Array             2,272          13       2,272           0          43                                                                  
PS   contention                     27,160       1,586      27,080           7         415                                                                  
SE   contention                      6,784           0       6,785           0           0                                                                  

TYPE                 DESCRIPTION
-------------------- ------------------------------------------------------------------------
PS                   Parallel Execution Server Process reservation and synchronization
DA                   Slave Process Spawn reservation and synchronization
SE                   Lock used by transparent session migration

Oracle really did start and stop something like 6,700 PX session (constantly re-using the same small set of PX slave processes) for each execution of the filter subquery. This is definitely a performance threat – we keep acquiring and releasing PX slaves, we keep creating new sessions (yes, really), and we keep searching for cursors in the library cache. All these activities are highly contentious. If you start running multiple queries that do this sort of thing you find that you see increasing amounts of time being spent on latch contention, PX slave allocation, mutex waits, and all the other problems you get with sessions that log on, do virtually nothing, then log off in rapid succession.

So how do you write SQL that does this type of thing. Here’s my data model (you may want to limit the number of rows in the tables:


create table t1 as
select * from all_source;

create table t2 as
select * from all_source where mod(line,20) = 1;

create table t3 as
select * from all_source;

And here’s all you have to do to start creating problems – I’ve added explicit hints to force parallelism (particularly for the subquery), it’s more likely that it has been introduced accidentally by table or index definitions, or by an “alter session” to “force parallel”:


set feedback only

select
        /*+ 
                parallel(t1 2) 
                parallel(t2 2)
                leading(t1 t2)
                use_hash(t2)
                swap_join_inputs(t2)
                pq_distribute(t2 hash hash)
                cardinality(t1,50000)
        */
        t1.owner,
        t1.name,
        t1.type
from
        t1
join
        t2
on      t2.owner = t1.owner
and     t2.name = t1.name
and     t2.type = t1.type
where
        t1.line = 1
and     (
           mod(length(t1.text), 10) = 0
        or exists (
                select --+ parallel(t3 3) 
                        null
                from    t3
                where   t3.owner = t1.owner
                and     t3.name = t1.name
                and     t3.type = t1.type
                and     t3.line >= t1.line
                )
        )
;

set feedback on

I’ve written notes in the past about SQL that forces the optimizer to run subqueries as filter subqueries instead of unnesting them – this is just an example of that type of query, pushed into parallelism. It’s not the only way (see comment #1 from Dmitry Remizov below) to end up with scalar subqueries being executed many times as separate DFO trees even though Oracle has enhanced the optimizer several times over the years in ways that bypass the threat – but the probalm can still appear and it’s important to notice in development that you’ve got a query that Oracle can’t work around.

 

Showing image thumbnails in Icon View

Jeff Kemp - Thu, 2019-06-13 02:23

I had an interactive report an an old APEX application that I’ve kept maintained for quite a few years, which is able to show an Icon View that shows a thumbnail of the image for each item.

The problem was that the layout sometimes went wonky depending on the size of the image or the size of the label text. How the items were laid out depended on the width of the viewing window as well. I have set Columns Per Row to 5, and I’m using a Custom Icon View with the following Custom Link:

<a href="#WORK_URL#">
<img src="#IMAGE_URL#" width="140px">
<p>
#FULL_TITLE_SORT#
</a>

Each item shows an image, scaled down to width 140 pixels (my images come in all sorts of sizes), plus the title; either the title or the image may be clicked to open the details for it.

This is how it looked:

Depending on a number of variables (length of the label text, and the width of the viewing window) some rows would show their first item in the 5th column instead of over on the left; this would have a run-on effect to following rows. In addition, I wasn’t quite happy with the left-justified layout for each item.

To fix this I added some DIVs and some CSS. My Custom Link is now:

<div class="workcontainer">
  <div class="workicon">
    <a href="#WORK_URL#">
      <img src="#IMAGE_URL#" class="workimg">
    </a>
  </div>
  <div class="worktitle">
    <a href="#WORK_URL#">#FULL_TITLE_SORT#</a>
  </div>
</div>

Each record gets a container div, within which are nested a div for the image and a div for the label.

The width attribute is no longer needed directly on the img; instead, I added the following CSS to the region Header Text:

<style>
.workcontainer {
    text-align:center;
    width:160px;
    height:200px;
  }
.workicon { display:block; }
.workimg {
    max-width:160px;
    max-height:160px;
    width:auto;
    height:auto;
  }
.worktitle {
    display:block;
    font-weight:bold;
    height:40px;
    overflow:hidden;
  }
</style>

Some of the key bits here are that the container div has width and height attributes, and the image is constrained using max-width, max-height, width:auto and height:auto. Also, the work title is constrained to a 40 pixel high block, with overflow:hidden.

My report now looks like this:

The images are scaled down automatically to fit within a consistent size, and both the images and the labels are horizontally centered leading to a more pleasing layout. If an image is already small enough, it won’t be scaled up but will be shown full-size within the available area. Also, the label height is constrained (if an item label is too high the overflow will be hidden) which solves the layout problem I had before. If the image is not very tall, the label appears directly beneath it which is what I wanted.

The only remaining issue is that the icon view feature of the interactive report generates a table with rows of a fixed number of columns (5, in my case), which doesn’t wrap so nicely on a small screen when it can’t fit 5 in a row. I’ve set Columns Per Row to 1 now, and it seems to wrap perfectly; it shows up to a maximum of 6 items per row depending on the viewing window width.

POSTSCRIPT – Lazy Load

Thanks to Matt (see comment below) who pointed out that a report like this would benefit greatly from a Lazy Load feature to reduce the amount of data pulled to the client – for example, if the user requests 5,000 records per page and starts paging through the results, each page view could potentially download a large volume of data, especially if the images are quite large.
I’ve implemented this now and it was quite straightforward:

  1. Download the latest release of the jQuery plugin “Lazyload Anyhttps://github.com/emn178/jquery-lazyload-any
  2. Upload the file jquery.lazyload-any.js to Static Application Files
  3. Add the following to the page attribute File URLs:
    #APP_IMAGES#jquery.lazyload-any.js
  4. Add a Dynamic Action to the report region:
    Event = After Refresh
    Action = Execute JavaScript Code
    Code = $(".workicon").lazyload()
    Fire on Initialization = Yes
  5. Modify the Icon View Custom Link code to put the bits to lazy load within a script tag of type “text/lazyload” (in my case, all the html contents within the “workicon” div), e.g.:
<div class="workcontainer">
  <div class="workicon">
    <script type="text/lazyload">
      <a href="#WORK_URL#">
        <img src="#IMAGE_URL#" class="workimg">
      </a>
    </script>
  </div>
  <div class="worktitle">
    <a href="#WORK_URL#">#FULL_TITLE_SORT#</a>
  </div>
</div>

This was an important addition in my cases as some of the images were quite large; I don’t have to worry about load on my server because they are hosted on Amazon S3, but I do have to pay a little bit for the transfer of data.

Using Cloud Native Buildpacks (CNB) on a local registry to speed up the building of images for test purposes

Pas Apicella - Tue, 2019-06-11 20:58
I previously blogged about the CNCF project known as Cloud Native Buildpacks previously on this blog entry below.

Building PivotalMySQLWeb using Cloud Native Buildpacks (CNB)
http://theblasfrompas.blogspot.com/2019/06/building-pivotalmysqlweb-using-cloud.html

In the steps below I will show how to use a local docker registry on your laptop or desktop to enable faster builds of your OCI compliant images using CNB's. Here is how using the same application.

Pre Steps:

1. Ensure you have Docker CE installed if not use this link

  https://hub.docker.com/search/?type=edition&offering=community

Steps:

1. Start by running a local registry on your own laptop. The guide shows how to get a container running which will be our local registry and then how you verify it's running.

https://docs.docker.com/registry/

$ docker run -d -p 5000:5000 --restart=always --name registry registry:2

Verify it's running:

$ netstat -an | grep 5000
tcp6       0      0  ::1.5000               *.*                    LISTEN
tcp4       0      0  *.5000                 *.*                    LISTEN

2. Then pull the CNB images versions of the "official" build and run images from the GCR as follows. Those images exist here

https://console.cloud.google.com/gcr/images/cncf-buildpacks-ci/GLOBAL/packs/run?gcrImageListsize=30

Here I am using the latest build/run images which at the time of this post was "run:0.2.0-build.12"

papicella@papicella:~$ docker pull gcr.io:443/cncf-buildpacks-ci/packs/run:0.2.0-build.12
0.2.0-build.12: Pulling from cncf-buildpacks-ci/packs/run
Digest: sha256:ebd42c0228f776804f2e99733076216592c5a1117f1b3dde7688cf3bd0bbe7b9
Status: Downloaded newer image for gcr.io:443/cncf-buildpacks-ci/packs/run:0.2.0-build.12

papicella@papicella:~$ docker tag gcr.io:443/cncf-buildpacks-ci/packs/run:0.2.0-build.12 localhost:5000/run:0.2.0-build.12

papicella@papicella:~$ docker rmi gcr.io:443/cncf-buildpacks-ci/packs/run:0.2.0-build.12
Untagged: gcr.io:443/cncf-buildpacks-ci/packs/run:0.2.0-build.12
Untagged: gcr.io:443/cncf-buildpacks-ci/packs/run@sha256:ebd42c0228f776804f2e99733076216592c5a1117f1b3dde7688cf3bd0bbe7b9

papicella@papicella:~$ docker push localhost:5000/run:0.2.0-build.12
The push refers to repository [localhost:5000/run]
1315c94f2536: Layer already exists
63696cbb6c17: Layer already exists
30ede08f8231: Layer already exists
b57c79f4a9f3: Layer already exists
d60e01b37e74: Layer already exists
e45cfbc98a50: Layer already exists
762d8e1a6054: Layer already exists
0.2.0-build.12: digest: sha256:ebd42c0228f776804f2e99733076216592c5a1117f1b3dde7688cf3bd0bbe7b9 size: 1780

3. Now lets use our local registry and build/run images which will be much faster for local development

papicella@papicella:~/pivotal/PCF/APJ/PPTX/CNCF/buildpacks.io/demos$ docker tag localhost:5000/run:0.2.0-build.12 localhost:5000/run

papicella@papicella:~/pivotal/PCF/APJ/PPTX/CNCF/buildpacks.io/demos$ docker push localhost:5000/run:latest
The push refers to repository [localhost:5000/run]
1315c94f2536: Layer already exists
63696cbb6c17: Layer already exists
30ede08f8231: Layer already exists
b57c79f4a9f3: Layer already exists
d60e01b37e74: Layer already exists
e45cfbc98a50: Layer already exists
762d8e1a6054: Layer already exists
latest: digest: sha256:ebd42c0228f776804f2e99733076216592c5a1117f1b3dde7688cf3bd0bbe7b9 size: 1780

papicella@papicella:~/pivotal/PCF/APJ/PPTX/CNCF/buildpacks.io/demos$ pack build localhost:5000/pivotal-mysql-web --path ./PivotalMySQLWeb --no-pull --publish
Using default builder image cloudfoundry/cnb:cflinuxfs3
Selected run image cloudfoundry/cnb-run:cflinuxfs3 from builder
Using build cache volume pack-cache-65bb470893c1.build
Executing lifecycle version 0.2.1
===> DETECTING
[detector] Trying group 1 out of 4 with 8 buildpacks...
[detector] ======== Results ========
[detector] pass: Cloud Foundry OpenJDK Buildpack
[detector] skip: Cloud Foundry Build System Buildpack
[detector] pass: Cloud Foundry JVM Application Buildpack
[detector] skip: Cloud Foundry Azure Application Insights Buildpack
[detector] skip: Cloud Foundry Debug Buildpack
[detector] skip: Cloud Foundry Google Stackdriver Buildpack
[detector] skip: Cloud Foundry JMX Buildpack
[detector] skip: Cloud Foundry Procfile Buildpack
===> RESTORING
[restorer] restoring cached layer 'org.cloudfoundry.openjdk:d2df8bc799b09c8375f79bf646747afac3d933bb1f65de71d6c78e7466ff8fe4'
===> ANALYZING
[analyzer] using cached layer 'org.cloudfoundry.openjdk:d2df8bc799b09c8375f79bf646747afac3d933bb1f65de71d6c78e7466ff8fe4'
[analyzer] writing metadata for uncached layer 'org.cloudfoundry.openjdk:openjdk-jre'
[analyzer] writing metadata for uncached layer 'org.cloudfoundry.jvmapplication:main-class'
===> BUILDING
[builder] -----> Cloud Foundry OpenJDK Buildpack 1.0.0-M8
[builder] -----> OpenJDK JRE 11.0.3: Reusing cached layer
[builder]
[builder] -----> Cloud Foundry JVM Application Buildpack 1.0.0-M8
[builder] -----> Main-Class Classpath: Reusing cached layer
[builder] -----> Process types:
[builder]        task: java -cp $CLASSPATH $JAVA_OPTS org.springframework.boot.loader.JarLauncher
[builder]        web:  java -cp $CLASSPATH $JAVA_OPTS org.springframework.boot.loader.JarLauncher
[builder]
===> EXPORTING
[exporter] Reusing layer 'app' with SHA sha256:b32618ed6b86fb496a4ce33db9df49fdd4ef16c5646b174b5643c8befcb7408a
[exporter] Reusing layer 'config' with SHA sha256:9538e967fa10f23b3415c382a3754ebf4c2645c20b6d76af519236c1181e7639
[exporter] Reusing layer 'launcher' with SHA sha256:04ca7957074763290a9abe6a067ce8c902a2ab51ed6c55102964e3f3294cdebd
[exporter] Reusing layer 'org.cloudfoundry.openjdk:openjdk-jre' with SHA sha256:e540f1464509ac673a25bd2f24c7dd6875f805c0dd35e9af84dd4669e2fd0c93
[exporter] Reusing layer 'org.cloudfoundry.jvmapplication:main-class' with SHA sha256:8537197b3f57d86a59397b89b4fbdd14900a602cc12961eae338b9ef2513cdc0
[exporter]
[exporter] *** Image: localhost:5000/pivotal-mysql-web:latest@sha256:f1d7a25fc5159ceb668c26b595dcffb00ef54ada31cbb52eaa8319dc143fc9d8
===> CACHING
[cacher] Reusing layer 'org.cloudfoundry.openjdk:d2df8bc799b09c8375f79bf646747afac3d933bb1f65de71d6c78e7466ff8fe4' with SHA sha256:11439713b023be71211cb83ecd56a1be63e0c0be3e4814a18cc4c71d2264dea5
Successfully built image localhost:5000/pivotal-mysql-web

papicella@papicella:~/pivotal/PCF/APJ/PPTX/CNCF/buildpacks.io/demos$ docker pull localhost:5000/pivotal-mysql-web
Using default tag: latest
latest: Pulling from pivotal-mysql-web
410238d178d0: Already exists
a00e90b544bc: Already exists
9de264eecc08: Already exists
4acedf754175: Already exists
d5a72fc0c7a1: Already exists
4066d2d744ac: Already exists
dba1ef680b99: Already exists
Digest: sha256:f1d7a25fc5159ceb668c26b595dcffb00ef54ada31cbb52eaa8319dc143fc9d8
Status: Downloaded newer image for localhost:5000/pivotal-mysql-web:latest

papicella@papicella:~/pivotal/PCF/APJ/PPTX/CNCF/buildpacks.io/demos$ docker run -m 1G -p 8080:8080 localhost:5000/pivotal-mysql-web

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.0.RELEASE)

2019-06-12 01:02:16.174  INFO 1 --- [           main] c.p.p.m.PivotalMySqlWebApplication       : Starting PivotalMySqlWebApplication on a018f17d6121 with PID 1 (/workspace/BOOT-INF/classes started by vcap in /workspace)
2019-06-12 01:02:16.179  INFO 1 --- [           main] c.p.p.m.PivotalMySqlWebApplication       : No active profile set, falling back to default profiles: default
2019-06-12 01:02:18.336  INFO 1 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2019-06-12 01:02:18.374  INFO 1 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2019-06-12 01:02:18.375  INFO 1 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/9.0.12
2019-06-12 01:02:18.391  INFO 1 --- [           main] o.a.catalina.core.AprLifecycleListener   : The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/layers/org.cloudfoundry.openjdk/openjdk-jre/lib:/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib]
2019-06-12 01:02:18.512  INFO 1 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2019-06-12 01:02:18.512  INFO 1 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 2270 ms
2019-06-12 01:02:19.019  INFO 1 --- [           main] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
2019-06-12 01:02:19.020  INFO 1 --- [           main] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'webMvcMetricsFilter' to: [/*]
2019-06-12 01:02:19.020  INFO 1 --- [           main] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2019-06-12 01:02:19.020  INFO 1 --- [           main] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'formContentFilter' to: [/*]
2019-06-12 01:02:19.021  INFO 1 --- [           main] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
2019-06-12 01:02:19.021  INFO 1 --- [           main] .s.DelegatingFilterProxyRegistrationBean : Mapping filter: 'springSecurityFilterChain' to: [/*]
2019-06-12 01:02:19.022  INFO 1 --- [           main] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpTraceFilter' to: [/*]
2019-06-12 01:02:19.022  INFO 1 --- [           main] o.s.b.w.servlet.ServletRegistrationBean  : Servlet dispatcherServlet mapped to [/]
2019-06-12 01:02:19.374  INFO 1 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2019-06-12 01:02:19.918  INFO 1 --- [           main] .s.s.UserDetailsServiceAutoConfiguration :

Using generated security password: 42d4ec01-6459-4205-a66b-1b49d333121e

2019-06-12 01:02:20.043  INFO 1 --- [           main] o.s.s.web.DefaultSecurityFilterChain     : Creating filter chain: Ant [pattern='/**'], []
2019-06-12 01:02:20.092  INFO 1 --- [           main] o.s.s.web.DefaultSecurityFilterChain     : Creating filter chain: any request, [org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter@47e4d9d0, org.springframework.security.web.context.SecurityContextPersistenceFilter@5e4fa1da, org.springframework.security.web.header.HeaderWriterFilter@4ae263bf, org.springframework.security.web.csrf.CsrfFilter@2788d0fe, org.springframework.security.web.authentication.logout.LogoutFilter@15fdd1f2, org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter@2d746ce4, org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter@70e02081, org.springframework.security.web.authentication.ui.DefaultLogoutPageGeneratingFilter@49798e84, org.springframework.security.web.authentication.www.BasicAuthenticationFilter@1948ea69, org.springframework.security.web.savedrequest.RequestCacheAwareFilter@3f92c349, org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter@66ba7e45, org.springframework.security.web.authentication.AnonymousAuthenticationFilter@6ed06f69, org.springframework.security.web.session.SessionManagementFilter@19ccca5, org.springframework.security.web.access.ExceptionTranslationFilter@57aa341b, org.springframework.security.web.access.intercept.FilterSecurityInterceptor@7c6442c2]
2019-06-12 01:02:20.138  INFO 1 --- [           main] o.s.b.a.e.web.EndpointLinksResolver      : Exposing 9 endpoint(s) beneath base path '/actuator'
2019-06-12 01:02:20.259  INFO 1 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2019-06-12 01:02:20.265  INFO 1 --- [           main] c.p.p.m.PivotalMySqlWebApplication       : Started PivotalMySqlWebApplication in 4.841 seconds (JVM running for 5.646)



And that's it a locally built OCI image (Built very fast all locally) you have run locally from your local image registry.

Here is how to view your local registry using HTTPie showing our locally built "pivotal-mysql-web" OCI image we created above

papicella@papicella:~$ http http://localhost:5000/v2/_catalog
HTTP/1.1 200 OK
Content-Length: 63
Content-Type: application/json; charset=utf-8
Date: Wed, 12 Jun 2019 01:53:40 GMT
Docker-Distribution-Api-Version: registry/2.0
X-Content-Type-Options: nosniff

{
    "repositories": [
        "pivotal-mysql-web",
        "run",
        "sample-java-app"
    ]
}


More Information

1. Cloud Native Buildpacks: an Industry-Standard Build Process for Kubernetes and Beyond.
https://content.pivotal.io/blog/cloud-native-buildpacks-for-kubernetes-and-beyond

2. buildspacks.io Home Page
https://buildpacks.io/

Categories: Fusion Middleware

Slides and My Impressions from May 17th AZORA Meetup

Bobby Durrett's DBA Blog - Tue, 2019-06-11 18:44

We have the slides from the two talks at our May 17th AZORA Meetup.

Here are Stephen Andert’s slides: Networking is NOT just cables and fiber!

Here are Doug Hood’s slides in three parts:

  1. Using SQL and PLSQL for Mid-Tier Database Caching
  2. Oracle TimesTen Scaleout – World’s Fastest OLTP DB
  3. Oracle In-Memory Data Processing

I wanted to share my impressions of the two talks and the meeting as well as share the slides. Of course, these are my opinions shaped by my own experiences and not universally true of everyone!

This meetup had two very different types of talks. I thought of Stephen’s talk as a career development or soft skills sort of talk. I have been to a number of talks like that at national Oracle user group conferences such as Collaborate. They help balance out the purely technical Oracle database content. Once Stephen got into his talk, I really started to appreciate the quality of the content. To me he was talking about keeping in touch with people in an intentional but sincere way. I like the idea of planning on contacting people a certain number of times per year for example.

Years ago, in my first job I worked for Campus Crusade for Christ (now Cru) and I raised money to support myself and my family. I networked with people that I met through churches and friends and family members. It was different than networking as part of a DBA career because I was directly asking for money instead of making career-oriented connections. But the core idea that I remember from Stephen’s talk applied then. Stephen’s idea was to genuinely seek to help the folks in your network without focusing on what they could do for you. In my CCC days the support raising training told us that we were not “fundraising” but instead “friend raising”. I had some great experiences meeting people and getting to know them and I think it was best when my focus was on how to inspire and encourage the people I met rather than to anxiously think about whether they could give money to support what I did.

The other less serious connection I saw between Stephen’s presentation and my Cru days is that Stephen has a hand-written database setup to keep track of his people. Back in the day I had a Paradox database from Borland running on MS-DOS to do a lot of the same things. So, hearing Stephen talk about his contact database was a blast from the past for me.

I am not really doing much in the way of networking myself these days. I write this blog. I speak at conferences every couple of years or so. I help with the local Oracle user group AZORA. But I am not intentionally making and maintaining relationships with other technical people in the way Stephen described so his talk gave me something to think about.

Doug Hood’s talk was at the other end of the spectrum with some cool technology. Doug spoke on several things and with a lot of detail so I cannot do a good job of summarizing what he said. Check the slides for more details. But I do want to write down my impressions. Listening to Doug’s talk reminded me of some of the computer science study that I have been doing on my own recently. I have sort of gone back to school as an independent learner. When Doug talked about the memory hierarchy and caching it tied right back to the assembly language and algorithms study I have been doing.

Doug presented some cool hardware that puts persistent memory close enough to the CPU that it changes the way we think about memory hierarchy. What if you replace your RAM with persistent RAM that did not get cleared when you power off your computer? Evidently in some architectures (maybe all the modern ones these days I don’t know) the RAM is closely connected to the CPU and does not have to be accessed over the bus in the way I/O is. So, persistent RAM would be much faster than some solid-state disk being read over the bus no matter how fast the SSD is. Anyway, see Doug’s slides. I am sure that I am butchering the details, but I am giving my impression and my memory so keep that in mind.

In general database work and database performance has a lot to do with caching. I have heard a little bit about how algorithms can be designed to work well with CPU caches. I recently read a chapter about the B-Tree data structure that is used in databases and it was a big contrast to the other data structures I had studied because it took disk reads and memory accesses into consideration. Anyway, at a high level I took away from Doug’s talk notions about memory and caching and different ways people can tweak the memory hierarchy to get better database performance.

I had to leave a little early to head for the mountains for the weekend but as always, I valued the time I spent at AZORA, my local Oracle user group. I appreciate Stephen and Doug stepping up and giving their presentations. I hope that my links and the way I characterized their talks is accurate enough. I am sure that I made mistakes, but I got something out of my time and appreciate their efforts.

AZORA is taking a break for the hot Arizona summer but coming back strong with our next meeting on September 27th. The details are being finalized so keep an eye on our Meetup page.

Bobby

Categories: DBA Blogs

RAMBleed DRAM Vulnerabilities

Oracle Security Team - Tue, 2019-06-11 12:00

On June 11th, security researchers published a paper titled “RAMBleed Reading Bits in Memory without Accessing Them”.  This paper describes attacks against Dynamic Random Access Memory (DRAM) modules that are already susceptible to Rowhammer-style attacks.

The new attack methods described in this paper are not microprocessor-specific, they leverage known issues in DRAM memory.  These attacks only impact DDR4 and DDR3 memory modules, and older generations DDR2 and DDR1 memory modules are not vulnerable to these attacks.

While the RAMBleed issues leverage RowHammer, RAMBleed is different in that confidentiality of data may be compromised: RAMBleed uses RowHammer as a side channel to discover the values of adjacent memory. 

Please note that successfully leveraging RAMBleed exploits require that the malicious attacker be able to locally execute malicious code against the targeted system. 

At this point in time, Oracle believes that:

  • All current and many older families of Oracle x86 (X5, X6, X7, X8, E1) and Oracle SPARC servers (S7, T7, T8, M7, M8) employing DDR4 DIMMs are not expected to be impacted by RAMBleed.  This is because Oracle only employs DDR4 DIMMs that have implemented the Target Row Refresh (TRR) defense mechanism against RowHammer.  Oracle’s memory suppliers have stated that these implementations have been designed to be effective against RowHammer. 
  • Older systems making use of DDR3 memory are also not expected to be impacted by RAMBleed because they are making use of a combination of other RowHammer mitigations (e.g., pseudo-TRR and increased DIMM refresh rates in addition to Error-Correcting Code (ECC)).  Oracle is currently not aware of any research that would indicate that the combination of these mechanisms would not be effective against RAMBleed. 
  • Oracle Cloud Infrastructure (OCI) is not impacted by the RAMBleed issues because OCI servers only use DDR4 memory with built-in defenses as previously described.  Exadata Engineered Systems use DDR4 memory (X5 family and newer) and DDR3 memory (X4 family and older).
  • Finally, Oracle does not believe that additional software patches will need to be produced to address the RAMBleed issues, as these memory issues can be only be addressed through hardware configuration changes.  In other words, no additional security patches are expected for Oracle product distributions.
For more information about Oracle Corporate Security Practices, see https://www.oracle.com/corporate/security-practices/

Pages

Subscribe to Oracle FAQ aggregator