Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 16 hours 48 min ago

OUD – Oracle Unified Directory 11.1.2.3 Tuning, It is not always the servers fault

Tue, 2017-04-04 03:41

The default configuration which is shipped with OUD is not meant to be ready for enterprise usage. The default settings of OUD are targeted at evaluators and developers who run equipment with limited resources, and so it is quite likely that you run into performance issues if you don’t change anything, before going into production. The OUD performance depends on a lot of things like

  • Network configuration/routing/firewalls/bonding
  • OUD version and configuration (Replication, TLS)
  • Java version and Java runtime memory configuration
  • DNS Lookup times
  • Name Service Cache Daemon
  • And many more …

However, it is not always the servers fault. Sometimes the client is causing the issue. But how do I know, if it is the client or the server. In the following example it takes about 10 seconds to resolve the connect string DBIT122_LDAP. That is enormous. Far too long from being acceptable. Where is the tnsping spending so much time?

oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122] time tnsping DBIT122_LDAP

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 04-APR-2017 08:43:06

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DBIT122)))
OK (10 msec)

real    0m10.177s
user    0m0.017s
sys     0m0.018s

To exclude, that it is the servers fault, just check the OUD access log where you can see any ldap request against the OUD.

[dbafmw@dbidg01 logs]$ tail -50f /u01/app/oracle/product/Middleware/11.1.2.3/asinst_1/OUD/logs/access
...
...
[04/Apr/2017:08:43:39 +0200] CONNECT conn=5 from=192.168.56.202:30826 to=192.168.56.201:1389 protocol=LDAP
[04/Apr/2017:08:43:39 +0200] BIND REQ conn=5 op=0 msgID=1 type=SIMPLE dn="" version=3
[04/Apr/2017:08:43:39 +0200] BIND RES conn=5 op=0 msgID=1 result=0 authDN="" etime=0
[04/Apr/2017:08:43:39 +0200] SEARCH REQ conn=5 op=1 msgID=2 base="cn=DBIT122_LDAP,cn=OracleContext,dc=dbi,dc=com" scope=base filter="(objectclass=*)" attrs="objectclass,orclNetDescString,orclNetDescName,orclVersion"
[04/Apr/2017:08:43:39 +0200] SEARCH RES conn=5 op=1 msgID=2 result=0 nentries=1 etime=2
[04/Apr/2017:08:43:39 +0200] UNBIND REQ conn=5 op=2 msgID=3
[04/Apr/2017:08:43:39 +0200] DISCONNECT conn=5 reason="Client Disconnect"
...
...

The important entry to look for is the etime after the search request. The etime filed is the elapsed time in milliseconds which the server spent processing the request. In the above case, it is 2 milliseconds, so quite fast. If you would see here large elapsed times here, then this would be a good indicator for issues on the server side.

Now, that we know that the server is ok, let’s move to client side. The first thing I am trying to do, is to see how fast the ldapsearch is. I am using the ldapsearch which comes with 12cR2 and I will use the same search criteria which tnsping is using to search for the connect string. The ldapsearch syntax from the OUD binaries differs a little bit with ldapsearch syntax which is shipped with 12cR2. Why should Oracle make them the same, it would be too easy. ;-) Ok, let’s check the ldapsearch.

oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122] time ldapsearch -v -h dbidg01 -p 1389 -b "cn=DBIT122_LDAP,cn=OracleContext,dc=dbi,dc=com" \
-s base "(objectclass=*)" "objectclass,orclNetDescString,orclNetDescName,orclVersion"

ldap_open( dbidg01, 1389 )
filter pattern: (objectclass=*)
returning: objectclass,orclNetDescString,orclNetDescName,orclVersion
filter is: ((objectclass=*))
cn=dbit122_ldap,cn=OracleContext,dc=dbi,dc=com
1 matches

real    0m0.020s
user    0m0.005s
sys     0m0.004s

I don’t see any issues here. My ldapsearch came back in a blink of an eye. So .. where are the other 10 seconds? We need more information. We can either use strace or we can activate tracing on the client side. Something less known in the Oracle world is the tnsping tracing, which can be activated too. My tnsping is slow, and so I want only the tnsping to be traced and nothing else. To do so, we need to specify two parameters in the sqlnet.ora file. The TNSPING.TRACE_DIRECTORY and the TNSPING.TRACE_LEVEL. The tnsping trace level can have 4 different values like the sqlnet tracing.

  • 0 or OFF – No Trace output
  • 4 or USER – User trace information
  • 10 or ADMIN – Administration trace information
  • 16 or SUPPORT – Worldwide Customer Support trace information

Because I want to have the full trace output, I go for level 16 which is the support tracing.

oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122] cat sqlnet.ora | grep TNSPING

TNSPING.TRACE_DIRECTORY = /u01/app/oracle/network/trc
TNSPING.TRACE_LEVEL = SUPPORT

Ok. Let’s do it again and see the outcome.

oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122] time tnsping DBIT122_LDAP

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 04-APR-2017 09:44:44

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DBIT122)))
OK (10 msec)

real    0m10.191s
user    0m0.013s
sys     0m0.016s
oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122]

If we look at the trace file, we see that Oracle found 3 directory paths in the following order, TNSNAMES, EZCONNECT and LDAP.

[04-APR-2017 09:44:44:569] nnfgsrsp: Obtaining path parameter from names.directory_path or native_names.directory_path
[04-APR-2017 09:44:44:569] nnfgsrdp: entry
[04-APR-2017 09:44:44:569] nnfgsrdp: Setting path:
[04-APR-2017 09:44:44:569] nnfgsrdp: checking element TNSNAMES
[04-APR-2017 09:44:44:569] nnfgsrdp: checking element EZCONNECT
[04-APR-2017 09:44:44:569] nnfgsrdp: checking element LDAP

Switching to the TNSNAMES adapter is very fast and Oracle see’s it immediately that the query is unsuccessful, and so it is switching to the next adapter.

[04-APR-2017 09:44:44:569] nnfgrne: Switching to TNSNAMES adapter
[04-APR-2017 09:44:44:569] nnftboot: entry
[04-APR-2017 09:44:44:569] nlpaxini: entry
[04-APR-2017 09:44:44:569] nlpaxini: exit
[04-APR-2017 09:44:44:569] nnftmlf_make_local_addrfile: entry
[04-APR-2017 09:44:44:569] nnftmlf_make_local_addrfile: construction of local names file failed
[04-APR-2017 09:44:44:569] nnftmlf_make_local_addrfile: exit
[04-APR-2017 09:44:44:569] nlpaxini: entry
[04-APR-2017 09:44:44:569] nlpaxini: exit
[04-APR-2017 09:44:44:569] nnftmlf_make_system_addrfile: entry
[04-APR-2017 09:44:44:569] nnftmlf_make_system_addrfile: system names file is /u01/app/oracle/network/admin/tnsnames.ora
[04-APR-2017 09:44:44:569] nnftmlf_make_system_addrfile: exit
[04-APR-2017 09:44:44:569] nnftboot: exit
[04-APR-2017 09:44:44:569] nnftrne: entry
[04-APR-2017 09:44:44:569] nnftrne: Original name: DBIT122_LDAP
[04-APR-2017 09:44:44:569] nnfttran: entry
[04-APR-2017 09:44:44:569] nnfttran: Error querying DBIT122_LDAP of attribute A.SMD errcode 408
[04-APR-2017 09:44:44:569] nnfgrne: Query unsuccessful, skipping to next adapter

Now, Oracle is switching to the EZCONNECT adapter.

[04-APR-2017 09:44:44:569] nnfgrne: Switching to EZCONNECT adapter
[04-APR-2017 09:44:44:569] nnfhboot: entry
[04-APR-2017 09:44:44:569] nnfhboot: exit
[04-APR-2017 09:44:44:569] snlinGetAddrInfo: entry
[04-APR-2017 09:44:54:664] snlinGetAddrInfo: getaddrinfo() failed with error -2
[04-APR-2017 09:44:54:664] snlinGetAddrInfo: exit
[04-APR-2017 09:44:54:665] snlinGetAddrInfo: entry
[04-APR-2017 09:44:54:727] snlinGetAddrInfo: getaddrinfo() failed with error -2
[04-APR-2017 09:44:54:727] snlinGetAddrInfo: exit
[04-APR-2017 09:44:54:727] nnfhrne: Error forming address for DBIT122_LDAP, errcode 406
[04-APR-2017 09:44:54:727] nnfgrne: Query unsuccessful, skipping to next adapter

Ok. Here we go. Between “snlinGetAddrInfo: entry” and “snlinGetAddrInfo: getaddrinfo() failed with error -2″  10 seconds have been gone. Oracle thinks that the DBIT122_LDAP is an easy connect string, and tries to resolve the name, which fails.

So I need to switch the entries in the directory path in the sqlnet.ora file, to NAMES.DIRECTORY_PATH= (TNSNAMES,LDAP,EZCONNECT). After I have done that, the tnsping comes back successfully and very fast.

oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122] time tnsping DBIT122_LDAP

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 04-APR-2017 10:25:39

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DBIT122)))
OK (0 msec)

real    0m0.018s
user    0m0.007s
sys     0m0.006s
Conclusion

It is not always the OUD servers fault when you hit performance issues. I might be on the client as well and it can have a severe impact.

 

Cet article OUD – Oracle Unified Directory 11.1.2.3 Tuning, It is not always the servers fault est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 14 – optimizer hints

Tue, 2017-04-04 01:23

This is a question that comes up quite often: How can I use optimizer hints in PostgreSQL as I can do it in Oracle? Well, you cant, and the reasons are this:

  • Poor application code maintainability: hints in queries require massive refactoring.
  • Interference with upgrades: today’s helpful hints become anti-performance after an upgrade.
  • Encouraging bad DBA habits slap a hint on instead of figuring out the real issue.
  • Does not scale with data size: the hint that’s right when a table is small is likely to be wrong when it gets larger.
  • Failure to actually improve query performance: most of the time, the optimizer is actually right.
  • Interfering with improving the query planner: people who use hints seldom report the query problem to the project.


But this does not mean that you cant influence the optimizer (or “planner” in PostgreSQL wording), it is just not working in the same way. Lets have a look.

On of the reasons that the planner does not choose an index over a sequential scan is that the parameter effective_cache_size is not set properly. To understand what it does you have to know that PostgreSQL works together with the operating system file cache/disk cache very well. It is not required, as you do it in Oracle, to give most of the available memory of the server to the database. Usually you start with 25% of the total available memory and give that to PostgreSQL by setting the parameter shared_buffers to that value. When pages fall out of that region it is still likely that they are available in the disk cache and can be retrieved from there without going down to disk. And this is what effective_cache_size is about: Setting this parameter does not consume more memory but is telling PostgreSQL how big the total cache of the system really is, so shared_buffers plus disk cache. This gets taken into consideration by the planner. A good starting point is 50 to 75% of the available memory. Lets do a quick test to show how this behaves. Lets generate some data:

postgres=# \! cat a.sql
drop table if exists t1;
create table t1 ( a int );
with generator as 
 ( select a.*
     from generate_series ( 1, 5000000 ) a
    order by random()
 )
insert into t1 ( a ) 
     select a
       from generator;
create index i1 on t1(a);
analyze verbose t1;
select * from pg_size_pretty ( pg_relation_size ('t1' ));
select * from pg_size_pretty ( pg_total_relation_size('t1'));
postgres=# \i a.sql
DROP TABLE
CREATE TABLE
INSERT 0 5000000
CREATE INDEX
psql:a.sql:12: INFO:  analyzing "public.t1"
psql:a.sql:12: INFO:  "t1": scanned 22124 of 22124 pages, containing 5000000 live rows and 0 dead rows; 30000 rows in sample, 5000000 estimated total rows
ANALYZE
 pg_size_pretty 
----------------
 173 MB
(1 row)
 pg_size_pretty 
----------------
 280 MB
(1 row)
postgres=# show shared_buffers ;
 shared_buffers 
----------------
 128MB
(1 row)

The table without the index is big enough to not fit into shared_buffers (173MB) and even bigger of course including the index (280MB). When we set effective_cache_size to a very low value we get costs of 40.55 for the statement below (almost no disk cache):

postgres=# SET effective_cache_size TO '1 MB';
SET
postgres=# explain SELECT * FROM t1 ORDER BY  a limit 10;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Limit  (cost=0.43..40.55 rows=10 width=4)
   ->  Index Only Scan using i1 on t1  (cost=0.43..20057243.41 rows=5000000 width=4)
(2 rows)

Setting this to a more realistic value decreases the costs because it is expected to find the index in the disk cache:

postgres=# SET effective_cache_size TO '5 GB';
SET
postgres=# explain SELECT * FROM t1 ORDER BY  a limit 10;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Limit  (cost=0.43..0.87 rows=10 width=4)
   ->  Index Only Scan using i1 on t1  (cost=0.43..218347.46 rows=5000000 width=4)
(2 rows)

This is the first “hint” you can set to influence the optimizer/planner. But there are many others. What PostgreSQL allows you to do is to enable or disable features of the planner:

postgres=# select name from pg_settings where name like 'enable%';
         name         
----------------------
 enable_bitmapscan
 enable_hashagg
 enable_hashjoin
 enable_indexonlyscan
 enable_indexscan
 enable_material
 enable_mergejoin
 enable_nestloop
 enable_seqscan
 enable_sort
 enable_tidscan

Using the same data from above we could disable the index only scan:

postgres=# set enable_indexonlyscan=false;
SET
postgres=# explain (analyze,buffers) SELECT * FROM t1 ORDER BY  a limit 10;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.87 rows=10 width=4) (actual time=0.019..0.058 rows=10 loops=1)
   Buffers: shared hit=13
   ->  Index Scan using i1 on t1  (cost=0.43..218347.46 rows=5000000 width=4) (actual time=0.017..0.036 rows=10 loops=1)
         Buffers: shared hit=13
 Planning time: 0.057 ms
 Execution time: 0.084 ms
(6 rows)

postgres=# set enable_indexonlyscan=true;
SET
postgres=# explain (analyze,buffers) SELECT * FROM t1 ORDER BY  a limit 10;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.87 rows=10 width=4) (actual time=0.025..0.072 rows=10 loops=1)
   Buffers: shared hit=13
   ->  Index Only Scan using i1 on t1  (cost=0.43..218347.46 rows=5000000 width=4) (actual time=0.023..0.048 rows=10 loops=1)
         Heap Fetches: 10
         Buffers: shared hit=13
 Planning time: 0.068 ms
 Execution time: 0.105 ms
(7 rows)

But the documentation clearly states: “If the default plan chosen by the optimizer for a particular query is not optimal, a temporary solution is to use one of these configuration parameters to force the optimizer to choose a different plan”. For testing and troubleshooting this can be handy.

Another way to influence the optimizer/planner is to set the planner cost constants:

 postgres=# select name from pg_settings where name like '%cost%' and name not like '%vacuum%';
         name         
----------------------
 cpu_index_tuple_cost
 cpu_operator_cost
 cpu_tuple_cost
 parallel_setup_cost
 parallel_tuple_cost
 random_page_cost
 seq_page_cost"
(7 rows)

What they mean is pretty well documented and how you need to set them (if you need to change them at all) depends on your hardware and application. There are others as well, such as the *collapse_limit* parameters and the parameters for the Genetic Query Optimizer.

Conclusion: There are several ways you can influence the optimizer/planner in PostgreSQL it is just not by using hints.

 

Cet article Can I do it with PostgreSQL? – 14 – optimizer hints est apparu en premier sur Blog dbi services.

12cR2 DBCA, Automatic Memory Management, and -databaseType

Mon, 2017-04-03 15:52

This post explains the following error encountered when creating a 12.2 database with DBCA:
[DBT-11211] The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.
or when creating the database directly with the installer:
[INS-35178]The Automatic Memory Management option is not allowed when the total physical memory is greater than 4GB.
If you used Automatic Memory Management (AMM) you will have to think differently and size the SGA and PGA separately.

ASMM

Automatic Shared Memory Management, or ASMM is what you do when setting SGA_TARGET and not setting MEMORY_TARGET. Basically, you define the size of the SGA you want to allocate at startup and that will be available for the instance, most of it being buffer cache and shared pool. I’ll not go into the detail of SGA_TARGET and SGA_MAX_SIZE because on the most common platforms, all is allocated at instance startup. Then, in addition to this shared area used by all instance processes, each processes can allocate private memory, and you control this with PGA_AGGREGATE_TARGET.
The total size of SGA and PGA for all instances in a system must reside in physical memory for the simple reason that they are mostly used to avoid I/O (a large buffer cache avoids physical reads and optimizes physical writes, a large PGA avoids reads and writes to tempfiles).

AMM

Because you don’t always know how much to allocate to each (SGA and PGA) Oracle came with a feature where you define the whole MEMORY_TARGET, part of this will be dynamically allocated to SGA or PGA. This is called Automatic Memory Management (AMM). It’s a good idea on the paper: it is automatic, which means that you don’t have to think about it, and it is dynamic, which means that you don’t waste physical memory because of bad sizing.

But it is actually a bad idea when going to implementation, at least on the most common platforms.
SGA and PGA are different beasts that should not be put in the same cage:

  • SGA is big, static, shared, allocated once at startup
  • PGA is small chunks constantly allocated and deallocated, private to processes

First, it is not so easy because you have to size the /dev/shm correctly or you will get the following at startup:
ORA-00845: MEMORY_TARGET not supported on this system
In addition to that, because the whole memory is prepared to contain the whole SGA you see misleading numbers in ‘show sga’.

Second there are lot of bugs, resizing overhead, etc.

And finally, you cannot use large pages when you are in AMM, and in modern system (lot of RAM, lot of processes) having all processes mapping the SGA with small pages of 4k is a big overhead.

So, as long as you have more than few GB on a system, you should avoid AMM and set SGA_TARGET and PGA_AGGREGATE_TARGET independently. Forget MEMORY_TARGET. Forget /dev/shm. Forget also the following documentation at http://docs.oracle.com/database/122/ADMIN/managing-memory.htm#ADMIN00207 which mentions that Oracle recommends that you enable the method known as automatic memory management.
Actually, AMM is not recommended for systems with more than a few GB of physical memory, and most system have more than few GB of physical memory. If you try to use AMM on a system with less than 4GB you get a warning in 12cR1 and it is an error in 12cR2:
CaptureAMM002
I got this when trying to create a database with AMM on a system with more than 4GB of physical memory.

CaptureAMM001
This does not depend on the size of MEMORY_TARGET you choose, or the size of /dev/shm, but only the size of available physical memory:
[oracle@VM104 ~]$ df -h /dev/shm
Filesystem Size Used Avail Use% Mounted on
tmpfs 3.9G 0 3.9G 0% /dev/shm
 
[oracle@VM104 ~]$ free -h
total used free shared buff/cache available
Mem: 7.8G 755M 5.0G 776M 2.1G 6.2G
Swap: 411M 0B 411M

No choice: it is a hard stop

If you are not convinced, then please have a look at MOS Doc ID 2244817.1 which explains this decision:

  • It is not something new: DBCA used to give similar warning message but in 12.2.0.1 it is an error message
  • Reason behind: Because database creation fails some times and in some cases database wont be functional after some times

So, do you want to create a database which may not be functional after some times?

So, what size for SGA and PGA?

Then, if you were thinking that AMM was cool, your next question not is: what size to allocate to SGA and PGA?

Don’t panic.

You are in this situation because you have several GB of RAM. Current servers have lot of memory. You don’t have to size it to the near 100MB. Start with some values, run with it. Look at the performance and the memory advisors. Are you doing too much physical I/O on tables where you expect data to be in cache? Then increase the SGA, and maybe set a minimum for the buffer cache. Do you see lot of hard parse because your application runs lot of statements and procedures? Then increase the SGA and maybe set a minimum for the shared pool. Do you run lot of analytic queries that full scan tables and have to hash and sort huge amount of data? Then decrease the SGA and increase the PGA_AGGREGATE_TARGET.

Where to start?

If you don’t know where to start, look at the DBCA database types:

#-----------------------------------------------------------------------------
# Name : databaseType
# Datatype : String
# Description : used for memory distribution when memoryPercentage specified
# Valid values : MULTIPURPOSE|DATA_WAREHOUSING|OLTP
# Default value : MULTIPURPOSE
# Mandatory : NO
#-----------------------------------------------------------------------------

Those types define the ratio between SGA and PGA. Then why not start with what is recommended by Oracle?

I’ve created the 3 types of instances with the following:
dbca -silent -totalMemory 10000 -databaseType MULTIPURPOSE -generateScripts -scriptDest /tmp/MULT ...
dbca -silent -totalMemory 10000 -databaseType DATA_WAREHOUSING -generateScripts -scriptDest /tmp/DWHG ...
dbca -silent -totalMemory 10000 -databaseType OLTP -generateScripts -scriptDest /tmp/OLTP ...

And here are the settings generated by DBCA
$ grep target /tmp/*/init.ora
DWHG/init.ora:sga_target=6000m
DWHG/init.ora:pga_aggregate_target=4000m
MULT/init.ora:sga_target=7500m
MMULT/init.ora:pga_aggregate_target=2500m
OLTP/init.ora:sga_target=8000m
OLTP/init.ora:pga_aggregate_target=2000m

Here is the summary:

SGA PGA OLTP 80% 20% Multi-Purpose 75% 25% Data Warehousing 60% 40%

(percentages are relative to eachother, here. Donc’ use 100% of physical memory for the Oracle instances because the system needs some memory as well)

This gives an idea where to start. Servers have lot of memory but you don’t have to use all of it. If you have a doubt, leave some free memory to be available for the filesystem cache. Usually, we recommend to used direct i/o (filesystemio_options=setall) to avoid the filesystem overhead. But when you start and want to lower the risks sub-sizing SGA or PGA, then you may prefer to keep that second level of cache (filesystemio_options=async) which uses all the physical memory available. This may improve the reads from tempfiles in case your PGA is too small. This is just an idea, not a recommendation.

So what?

If you have a server with more than few GB, then set SGA and PGA separately. Start with the ratios above, and then monitor performance and advisors. Physical servers today have at least 32GB. Even with a small VM with 1GB for my labs, I prefer to set them separately, because in that case I want to be sure to have a minimum size for buffer cache and shared pool. You may have lot of small VMs with 3GB and think about setting MEMORY_TARGET. But using large pages is a recommendation here because the hypervisor will have lot of memory to map, so ASMM is still the recommandation.

Once you know the size of all SGA, look at Hugepagesize in /proc/meminfo, set the number of hugepages in /etc/sysctl.conf, run sysctl -p and your instances will use available large pages for the SGA.

 

Cet article 12cR2 DBCA, Automatic Memory Management, and -databaseType est apparu en premier sur Blog dbi services.

When automatic reoptimization plan is less efficient

Sun, 2017-04-02 05:05

11gR2 started to have the optimizer react at execution time when a misestimate is encountered. Then the next executions are re-optimized with more accurate estimation, derived from the execution statistics. This was called cardinality feedback. Unfortunately, in rare cases we had a fast execution plan with bad estimations, and better estimations lead to worse execution plan. This is rare, but even when 9999 queries are faster, the one that takes too long will gives a bad perception of this optimizer feature.
This feature has been improved in 12cR1 with new names: auto-reoptimization and statistics feedback. I’m showing an example here in 12.1.0.2 without adaptive statistics (the 12.2 backport) and I’ve also disabled adaptive plan because they show the wrong numbers (similar to what I described in this post). I’ll show that at one point, the re-optimization can go back to the initial plan if it was the best in execution time.

V$SQL

Basically, here is what happened: first execution was fast, but with actual number of rows far from the estimated ones. Auto-reoptimisation kicks in for next execution and get a new plan, but with longer execution time. Third execution is another re-optimization, leading to same bad plan. Finally starting at 4th execution, the time is back to reasonable and we see the same as the first plan is used:

SQL> select sql_id,child_number,plan_hash_value,is_reoptimizable,is_resolved_adaptive_plan,parse_calls,executions,elapsed_time/1e6
from v$sql where sql_id='b4rhzfw7d6vdp';
 
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE I I PARSE_CALLS EXECUTIONS ELAPSED_TIME/1E6
------------- ------------ --------------- - - ----------- ---------- ----------------
b4rhzfw7d6vdp 0 1894156093 Y 1 1 .346571
b4rhzfw7d6vdp 1 955499861 Y 1 1 5.173733
b4rhzfw7d6vdp 2 955499861 Y 1 1 4.772258
b4rhzfw7d6vdp 3 1894156093 N 7 7 .5008

The scope of statistic feedback is not to get optimal execution from the first execution. This requires accurate statistics, static or dynamic, and SQL Plan Directives is a try to get that. Statistics feedback goal is to try to get a better plan rather than re-use one that is based on misestimates. But sometimes the better is the enemy of the good and we have an example here in child cursors 1 and 2. But the good thing is that finally we are back to acceptable execution time, with a final plan that can be re-used without re-optimization.

What surprised me here is that the final plan has the same hash value than the initial one. Is it a coincidence that different estimations gives the same plan? Or did the optimizer finally gave up to try to find better?

V$SQL_REOPTIMIZATION_HINTS

In 12c the statistics feedback are exposed in V$SQL_REOPTIMIZATION_HINTS.

SQL> select sql_id,child_number,hint_text,client_id,reparse from v$sql_reoptimization_hints where sql_id='b4rhzfw7d6vdp';
 
SQL_ID CHILD_NUMBER HINT_TEXT CLIENT_ID REPARSE
------------- ------------ ---------------------------------------------------------------------------------------------------- ---------- ----------
b4rhzfw7d6vdp 0 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" ROWS=1517.000000 ) 1 1
b4rhzfw7d6vdp 0 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" MIN=1517.000000 ) 1 1
b4rhzfw7d6vdp 0 OPT_ESTIMATE (@"SEL$1" TABLE "DM_FOLDER_R1"@"SEL$1" ROWS=1517.000000 ) 1 1
b4rhzfw7d6vdp 0 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) 1 0
b4rhzfw7d6vdp 0 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) 1 0
b4rhzfw7d6vdp 0 OPT_ESTIMATE (@"SEL$1" TABLE "DM_SYSOBJECT_R2"@"SEL$1" MIN=3.000000 ) 1 0
b4rhzfw7d6vdp 1 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" ROWS=1517.000000 ) 1 0
b4rhzfw7d6vdp 1 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" MIN=1517.000000 ) 1 0
b4rhzfw7d6vdp 1 OPT_ESTIMATE (@"SEL$1" TABLE "DM_FOLDER_R1"@"SEL$1" ROWS=1517.000000 ) 1 0
b4rhzfw7d6vdp 1 OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) 1 0
b4rhzfw7d6vdp 1 OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) 1 0
b4rhzfw7d6vdp 1 OPT_ESTIMATE (@"SEL$1" TABLE "DM_SYSOBJECT_R2"@"SEL$1" MIN=3.000000 ) 1 0
b4rhzfw7d6vdp 1 OPT_ESTIMATE (@"SEL$582FA660" QUERY_BLOCK ROWS=1491.000000 ) 1 1

The child cursor 0 was re-optimized to cursor 1 with different number of rows for “DM_FOLDER_R1″ and “DM_SYSOBJECT_R2″
The child cursor 1 has the same values, but an additional number of row correction for a query block.

But we don’t see anything about cursor 2. It was re-optimizable, and was actually re-optimized into cursor 3 but no statistics corrections are displayed here.

Trace

As it is a reproducible case, I’ve run the same while tracing 10046, 10053 and 10507 (level 512) to get all information about SQL execution, Optimiser compilation, and statistics feedback. For each child cursor, I’ll show the execution plan with estimated and actual number of rows (E-Rows and A-Rows) and then some interesting lines from the trace, mainly those returned by:
grep -E "KKSMEC|^atom_hint|^@"

Child cursor 0 – plan 1894156093 – 0.34 seconds

Plan hash value: 1894156093
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 171 (100)| 1 |00:00:00.04 | 17679 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.04 | 17679 |
| 2 | NESTED LOOPS | | 1 | 1 | 116 (0)| 1491 |00:00:00.04 | 17679 |
| 3 | NESTED LOOPS | | 1 | 1 | 115 (0)| 1491 |00:00:00.04 | 17456 |
| 4 | NESTED LOOPS | | 1 | 49 | 17 (0)| 5648 |00:00:00.01 | 537 |
|* 5 | INDEX RANGE SCAN | D_1F0049A880000016 | 1 | 3 | 3 (0)| 1517 |00:00:00.01 | 13 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | DM_SYSOBJECT_R | 1517 | 16 | 10 (0)| 5648 |00:00:00.01 | 524 |
|* 7 | INDEX RANGE SCAN | D_1F0049A880000010 | 1517 | 71 | 2 (0)| 5648 |00:00:00.01 | 249 |
|* 8 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 5648 | 1 | 2 (0)| 1491 |00:00:00.03 | 16919 |
|* 9 | INDEX UNIQUE SCAN | D_1F0049A880000108 | 5648 | 1 | 1 (0)| 1491 |00:00:00.03 | 15428 |
| 10 | NESTED LOOPS SEMI | | 5648 | 2 | 25 (0)| 1491 |00:00:00.02 | 14828 |
| 11 | NESTED LOOPS | | 5648 | 7 | 18 (0)| 2981 |00:00:00.02 | 12869 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| DM_SYSOBJECT_R | 5648 | 71 | 4 (0)| 2981 |00:00:00.01 | 7747 |
|* 13 | INDEX RANGE SCAN | D_1F0049A880000010 | 5648 | 16 | 3 (0)| 2981 |00:00:00.01 | 6145 |
|* 14 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 2981 | 1 | 2 (0)| 2981 |00:00:00.01 | 5122 |
|* 15 | INDEX UNIQUE SCAN | D_1F0049A880000108 | 2981 | 1 | 1 (0)| 2981 |00:00:00.01 | 2140 |
|* 16 | INDEX UNIQUE SCAN | D_1F0049A880000145 | 2981 | 52759 | 1 (0)| 1491 |00:00:00.01 | 1959 |
|* 17 | INDEX UNIQUE SCAN | D_1F0049A880000142 | 1491 | 1 | 1 (0)| 1491 |00:00:00.01 | 223 |
----------------------------------------------------------------------------------------------------------------------------------------

Because of low cardinality estimation of DM_SYSOBJECT_R predicate (E-Rows=3) the optimizer goes to NESTED LOOP. This plan has good execution time here because all blocks are in buffer cache. Reading 17679 blocks from buffer cache takes less than one second. It would have been much longer if those were physical I/O.

This is a case where the optimizer detects misestimate at execution time. Here is what is recorded in the trace:

Reparsing due to card est...
@=0x63a56820 type=3 nodeid=5 monitor=Y halias="DM_FOLDER_R1" loc="SEL$1" oname="SEL$F5BB74E1" act=1517 min=0 est=3 next=(nil)
Reparsing due to card est...
@=0x638fe2b0 type=5 nodeid=4 monitor=Y halias="" loc="SEL$F5BB74E1" onames="SEL$07BDC5B4"@"SEL$5" "SEL$2"@"SEL$5" act=5648 min=0 est=49 next=0x638fe250
Reparsing due to card est...
@=0x638fe4c0 type=5 nodeid=3 monitor=Y halias="" loc="SEL$F5BB74E1" onames="SEL$07BDC5B4"@"SEL$5" "SEL$2"@"SEL$5" "SEL$3"@"SEL$1" act=1491 min=0 est=1 next=0x638fe460
Reparsing due to card est...
@=0x638fe688 type=5 nodeid=2 monitor=Y halias="" loc="SEL$F5BB74E1" onames="SEL$07BDC5B4"@"SEL$5" "SEL$2"@"SEL$5" "SEL$3"@"SEL$1" "R_OBJECT_ID"@"SEL$1" act=1491 min=0 est=1 next=0x638fe5f8
kkocfbCheckCardEst [sql_id=b4rhzfw7d6vdp] reparse=y ecs=n efb=n ost=n fbs=n

Those are the misestimates which triggers re-optimization.

And here are all statistics feedback.

*********** Begin Dump Context (kkocfbCheckCardEst) [sql_id=b4rhzfw7d6vdp cpcnt=0] ***********
@=0x638fe688 type=5 nodeid=2 monitor=Y halias="" loc="SEL$F5BB74E1" onames="DM_FOLDER_R1"@"SEL$1" "DM_SYSOBJECT_R2"@"SEL$1" "TE_"@"SEL$2" "LJ_"@"SEL$2" act=1491 min=0 est=1 next=0x638fe5f8
@=0x638fe5f8 type=3 nodeid=17 monitor=Y halias="LJ_" loc="SEL$2" oname="D_1F0049A880000142" act=0 min=1 est=1 next=0x638fe4c0
@=0x638fe4c0 type=5 nodeid=3 monitor=Y halias="" loc="SEL$F5BB74E1" onames="DM_FOLDER_R1"@"SEL$1" "DM_SYSOBJECT_R2"@"SEL$1" "TE_"@"SEL$2" act=1491 min=0 est=1 next=0x638fe460
@=0x638fe460 type=1 nodeid=8 monitor=Y halias="TE_" loc="SEL$2" act=0 min=1 est=1 next=0x638fe3d0
@=0x638fe3d0 type=3 nodeid=9 monitor=Y halias="TE_" loc="SEL$2" oname="D_1F0049A880000108" act=0 min=1 est=1 next=0x638fe2b0
@=0x638fe2b0 type=5 nodeid=4 monitor=Y halias="" loc="SEL$F5BB74E1" onames="DM_FOLDER_R1"@"SEL$1" "DM_SYSOBJECT_R2"@"SEL$1" act=5648 min=0 est=49 next=0x638fe250
@=0x638fe250 type=1 nodeid=6 monitor=Y halias="DM_SYSOBJECT_R2" loc="SEL$1" act=3 min=1 est=16 next=0x638fe1c0
@=0x638fe1c0 type=3 nodeid=7 monitor=Y halias="DM_SYSOBJECT_R2" loc="SEL$1" oname="D_1F0049A880000010" act=3 min=1 est=71 next=0x63a56820
@=0x63a56820 type=3 nodeid=5 monitor=Y halias="DM_FOLDER_R1" loc="SEL$1" oname="D_1F0049A880000016" act=1517 min=0 est=3 next=(nil)
*********** End Dump Context ***********

We also see some information about execution performance:

kkoarCopyCtx: [sql_id=b4rhzfw7d6vdp] origin=CFB old=0x63a565d0 new=0x7fe74e2153f0 copyCnt=1 copyClient=y
**************************************************************
kkocfbCopyBestEst: Best Stats
Exec count: 1
CR gets: 17679
CU gets: 0
Disk Reads: 0
Disk Writes: 0
IO Read Requests: 0
IO Write Requests: 0
Bytes Read: 0
Bytes Written: 0
Bytes Exchanged with Storage: 0
Bytes Exchanged with Disk: 0
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 51 (ms)
CPU Time: 51 (ms)
User I/O Time: 15 (us)
*********** Begin Dump Context (kkocfbCopyBestEst) **********
*********** End Dump Context ***********

They are labeled as ‘Best Stats’ because we had only one execution at that time.

Finally, the hints are dumped:

******** Begin CFB Hints [sql_id=b4rhzfw7d6vdp] xsc=0x7fe74e215748 ********
Dumping Hints
=============
atom_hint=(@=0x7fe74e21ebf0 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" TABLE "DM_SYSOBJECT_R2"@"SEL$1" MIN=3.000000 ) )
atom_hint=(@=0x7fe74e21e758 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) )
atom_hint=(@=0x7fe74e21e3f0 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) )
atom_hint=(@=0x7fe74e21dfd0 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" ROWS=1517.000000 ) )
atom_hint=(@=0x7fe74e21dc68 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" MIN=1517.000000 ) )
atom_hint=(@=0x7fe74e21d8c8 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" TABLE "DM_FOLDER_R1"@"SEL$1" ROWS=1517.000000 ) )
********** End CFB Hints **********

Those are exactly what we see in V$SQL_REOPTIMIZATION_HINTS

This is all what we see for this first execution. The next execution starts with:

KKSMEC: Invalidating old cursor 0 with hash val = 1894156093
KKSMEC: Produced New cursor 1 with hash val = 955499861

As a consequence of child cursor 0 marked as reoptimizable, the next execution invalidates it and creates a new child cursor 1.

Child cursor 1 – new plan 955499861 – 5.17 seconds

Here is the new plan we see after that second execution:

Plan hash value: 955499861
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 30996 (100)| 1 |00:00:04.58 | 102K| 101K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:04.58 | 102K| 101K| | | |
| 2 | VIEW | VM_NWVW_2 | 1 | 12039 | 30996 (1)| 1491 |00:00:04.58 | 102K| 101K| | | |
| 3 | HASH UNIQUE | | 1 | 12039 | 30996 (1)| 1491 |00:00:04.58 | 102K| 101K| 941K| 941K| 2597K (0)|
|* 4 | HASH JOIN RIGHT SEMI | | 1 | 12039 | 30490 (1)| 4132 |00:00:04.57 | 102K| 101K| 12M| 3867K| 14M (0)|
| 5 | TABLE ACCESS FULL | DM_DOCUMENT_S | 1 | 213K| 210 (1)| 213K|00:00:00.01 | 741 | 0 | | | |
|* 6 | HASH JOIN | | 1 | 36463 | 29665 (1)| 5622 |00:00:04.51 | 101K| 101K| 1405K| 1183K| 2026K (0)|
|* 7 | HASH JOIN | | 1 | 36463 | 18397 (1)| 5622 |00:00:02.23 | 65103 | 65050 | 940K| 940K| 1339K (0)|
|* 8 | HASH JOIN | | 1 | 2222 | 14489 (1)| 1499 |00:00:01.58 | 51413 | 51369 | 992K| 992K| 1377K (0)|
|* 9 | HASH JOIN | | 1 | 2222 | 14120 (1)| 1499 |00:00:01.46 | 50088 | 50057 | 3494K| 1598K| 4145K (0)|
|* 10 | TABLE ACCESS FULL | DM_SYSOBJECT_S | 1 | 39235 | 10003 (1)| 39235 |00:00:00.83 | 36385 | 36376 | | | |
|* 11 | HASH JOIN | | 1 | 24899 | 3920 (1)| 5648 |00:00:00.62 | 13703 | 13681 | 1199K| 1199K| 1344K (0)|
|* 12 | INDEX RANGE SCAN | D_1F0049A880000016 | 1 | 1517 | 12 (0)| 1517 |00:00:00.01 | 13 | 0 | | | |
|* 13 | TABLE ACCESS FULL| DM_SYSOBJECT_R | 1 | 646K| 3906 (1)| 646K|00:00:00.50 | 13690 | 13681 | | | |
| 14 | TABLE ACCESS FULL | DM_FOLDER_S | 1 | 431K| 367 (1)| 431K|00:00:00.04 | 1325 | 1312 | | | |
|* 15 | TABLE ACCESS FULL | DM_SYSOBJECT_R | 1 | 646K| 3906 (1)| 646K|00:00:00.51 | 13690 | 13681 | | | |
|* 16 | TABLE ACCESS FULL | DM_SYSOBJECT_S | 1 | 646K| 10000 (1)| 646K|00:00:02.14 | 36385 | 36376 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- statistics feedback used for this statement

The notes makes it clear that the estimations comes from previous run (statistics feedback) and we see that for most operations E-Rows = A-Rows. With those a new plan has been chosen, with complex view merging: VM_NWWM. You can find clues about those internal view names on Jonathan Lewis blog. Here probably because the estimated number of rows is high, the subquery has been unnested. It is an ‘EXISTS’ subquery, which is transformed to semi join and merged to apply a distinct at the end.

So, we have a different plan, which is supposed to be better because it has been costed with more accurate cardinalities. .The goal of this post is not to detail the reason why the execution time is longer with a ‘better’ plan. If you look at ‘Reads’ column you can see that the first one has read all blocks from buffer cache but second one had to do physical I/O for all. With nothing from buffer cache, reading 101K blocks in multiblock reads may be faster than reading 17679 so the optimizer decision was not bad. I’ll have to estimate if it is expected to have most of the blocks in buffer cache in real production life as behavior in UAT is different. Some people will stop here, say that cardinality feedback is bad, disable it or even set optimizer_cost_adj to get the nested loop, but things are more complex than that.

The important thing is that the optimizer doesn’t stop there and compares the new execution statistics with the previous one.

**************************************************************
kkocfbCompareExecStats : Current
Exec count: 1
CR gets: 102226
CU gets: 3
Disk Reads: 101426
Disk Writes: 0
IO Read Requests: 1633
IO Write Requests: 0
Bytes Read: 830881792
Bytes Written: 0
Bytes Exchanged with Storage: 830881792
Bytes Exchanged with Disk: 830881792
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 4586 (ms)
CPU Time: 1305 (ms)
User I/O Time: 3040 (ms)
**************************************************************
kkocfbCompareExecStats : Best
Exec count: 1
CR gets: 17679
CU gets: 0
Disk Reads: 0
Disk Writes: 0
IO Read Requests: 0
IO Write Requests: 0
Bytes Read: 0
Bytes Written: 0
Bytes Exchanged with Storage: 0
Bytes Exchanged with Disk: 0
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 51 (ms)
CPU Time: 51 (ms)
User I/O Time: 15 (us)
kkocfbCompareExecStats: improvement BG: 0.172935 CPU: 0.039555

The first execution, with ‘bad’ statistics, is still the best one and this new execution has an improvement of 0.17, which means 5 times slower.

Then in the trace we see again that re-optimisation (reparsing) is considered:

Reparsing due to card est...
@=0x6a368338 type=5 nodeid=11 monitor=Y halias="" loc="SEL$582FA660" onames="SEL$608EC1F7"@"SEL$582FA660" "SEL$04458B50"@"SEL$582FA660" act=5648 min=0 est=24899 next=0x6a3682d8
Reparsing due to card est...
@=0x6a3687b0 type=5 nodeid=7 monitor=Y halias="" loc="SEL$582FA660" onames="SEL$608EC1F7"@"SEL$582FA660" "SEL$04458B50"@"SEL$582FA660" "SEL$FB0FE72C"@"SEL$33802F1B" "SEL$5"@"SEL$33802F1B" "SEL$07BDC5B4"@"SEL$636B5685" act=5622 min=0 est=36463 next=0x6a368750
Reparsing due to card est...
@=0x6a368990 type=5 nodeid=6 monitor=Y halias="" loc="SEL$582FA660" onames="SEL$608EC1F7"@"SEL$582FA660" "SEL$04458B50"@"SEL$582FA660" "SEL$FB0FE72C"@"SEL$33802F1B" "SEL$5"@"SEL$33802F1B" "SEL$07BDC5B4"@"SEL$636B5685" "SEL$FB0FE72C"@"SEL$4" act=5622 min=0 est=36463 next=0x6a368930
Reparsing due to card est...
@=0x6a368b90 type=5 nodeid=4 monitor=Y halias="" loc="SEL$582FA660" onames="SEL$608EC1F7"@"SEL$582FA660" "SEL$04458B50"@"SEL$582FA660" "SEL$FB0FE72C"@"SEL$33802F1B" "SEL$5"@"SEL$33802F1B" "SEL$07BDC5B4"@"SEL$636B5685" "SEL$FB0FE72C"@"SEL$4" "SEL$F5BB74E1"
@"SEL$4" act=4132 min=0 est=12039 next=0x6a368b30
Reparsing due to card est...
@=0x6a368d60 type=4 nodeid=3 monitor=Y halias="" loc="SEL$582FA660" act=1491 min=0 est=12039 next=0x6a368b90

An additional OPT_ESTIMATE hint is generated for the complext view merging view query block:

atom_hint=(@=0x7fe74e21eb90 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" ROWS=1517.000000 ) )
atom_hint=(@=0x7fe74e21e7b0 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_FOLDER_R1"@"SEL$1" "D_1F0049A880000016" MIN=1517.000000 ) )
atom_hint=(@=0x7fe74e21e470 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" TABLE "DM_FOLDER_R1"@"SEL$1" ROWS=1517.000000 ) )
atom_hint=(@=0x7fe74e21e050 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_FILTER "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) )
atom_hint=(@=0x7fe74e21dce8 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" INDEX_SCAN "DM_SYSOBJECT_R2"@"SEL$1" "D_1F0049A880000010" MIN=3.000000 ) )
atom_hint=(@=0x7fe74e21da38 err=0 resol=0 used=0 token=1018 org=6 lvl=2 txt=OPT_ESTIMATE (@"SEL$582FA660" QUERY_BLOCK ROWS=1491.000000 ) )
atom_hint=(@=0x7fe74e21d600 err=0 resol=0 used=0 token=1018 org=6 lvl=3 txt=OPT_ESTIMATE (@"SEL$1" TABLE "DM_SYSOBJECT_R2"@"SEL$1" MIN=3.000000 ) )

Whith this new cardinality estimation, the next execution will try to get a better execution, but it doesn’t change the optimizer choice and the new child cursor gets the same execution plan:
KKSMEC: Invalidating old cursor 1 with hash val = 955499861
KKSMEC: Produced New cursor 2 with hash val = 955499861

Child cursor 2 – plan 955499861 again – 4.77 seconds

This the third execution:

Plan hash value: 955499861
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 30996 (100)| 1 |00:00:04.19 | 102K| 101K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:04.19 | 102K| 101K| | | |
| 2 | VIEW | VM_NWVW_2 | 1 | 1491 | 30996 (1)| 1491 |00:00:04.19 | 102K| 101K| | | |
| 3 | HASH UNIQUE | | 1 | 1491 | 30996 (1)| 1491 |00:00:04.19 | 102K| 101K| 941K| 941K| 1355K (0)|
|* 4 | HASH JOIN RIGHT SEMI | | 1 | 12039 | 30490 (1)| 4132 |00:00:04.19 | 102K| 101K| 12M| 3867K| 14M (0)|
| 5 | TABLE ACCESS FULL | DM_DOCUMENT_S | 1 | 213K| 210 (1)| 213K|00:00:00.01 | 740 | 0 | | | |
|* 6 | HASH JOIN | | 1 | 36463 | 29665 (1)| 5622 |00:00:04.12 | 101K| 101K| 1405K| 1183K| 2021K (0)|
|* 7 | HASH JOIN | | 1 | 36463 | 18397 (1)| 5622 |00:00:03.39 | 65102 | 65050 | 940K| 940K| 1359K (0)|
|* 8 | HASH JOIN | | 1 | 2222 | 14489 (1)| 1499 |00:00:02.94 | 51412 | 51369 | 992K| 992K| 1331K (0)|
|* 9 | HASH JOIN | | 1 | 2222 | 14120 (1)| 1499 |00:00:01.04 | 50088 | 50057 | 3494K| 1598K| 4145K (0)|
|* 10 | TABLE ACCESS FULL | DM_SYSOBJECT_S | 1 | 39235 | 10003 (1)| 39235 |00:00:00.47 | 36385 | 36376 | | | |
|* 11 | HASH JOIN | | 1 | 24899 | 3920 (1)| 5648 |00:00:00.55 | 13703 | 13681 | 1199K| 1199K| 1344K (0)|
|* 12 | INDEX RANGE SCAN | D_1F0049A880000016 | 1 | 1517 | 12 (0)| 1517 |00:00:00.01 | 13 | 0 | | | |
|* 13 | TABLE ACCESS FULL| DM_SYSOBJECT_R | 1 | 646K| 3906 (1)| 646K|00:00:00.43 | 13690 | 13681 | | | |
| 14 | TABLE ACCESS FULL | DM_FOLDER_S | 1 | 431K| 367 (1)| 431K|00:00:01.82 | 1324 | 1312 | | | |
|* 15 | TABLE ACCESS FULL | DM_SYSOBJECT_R | 1 | 646K| 3906 (1)| 646K|00:00:00.33 | 13690 | 13681 | | | |
|* 16 | TABLE ACCESS FULL | DM_SYSOBJECT_S | 1 | 646K| 10000 (1)| 646K|00:00:00.60 | 36385 | 36376 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- statistics feedback used for this statement

Same plan and same execution time here. Tables are large and SGA is small here.

*********** Begin Dump Context: best estimates ***********
 
**************************************************************
kkocfbCompareExecStats : Current
Exec count: 1
CR gets: 102224
CU gets: 3
Disk Reads: 101426
Disk Writes: 0
IO Read Requests: 1633
IO Write Requests: 0
Bytes Read: 830881792
Bytes Written: 0
Bytes Exchanged with Storage: 830881792
Bytes Exchanged with Disk: 830881792
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 4206 (ms)
CPU Time: 1279 (ms)
User I/O Time: 3084 (ms)
**************************************************************
kkocfbCompareExecStats : Best
Exec count: 1
CR gets: 17679
CU gets: 0
Disk Reads: 0
Disk Writes: 0
IO Read Requests: 0
IO Write Requests: 0
Bytes Read: 0
Bytes Written: 0
Bytes Exchanged with Storage: 0
Bytes Exchanged with Disk: 0
Bytes Simulated Read: 0
Bytes Simulated Returned: 0
Elapsed Time: 51 (ms)
CPU Time: 51 (ms)
User I/O Time: 15 (us)
kkocfbCompareExecStats: improvement BG: 0.172939 CPU: 0.040363

So where we are here? We had an execution which was based on bad estimations. Then two tries on good estimations, but because of different buffer cache behavior they are finally 5 times slower. Nothing else to try.

The good thing is that the optimizer admits it cannot do better and falls back to the best execution time, now considered as the best estimate:

kkocfbCheckCardEst: reparse using best estimates
...
kkocfbCopyCardCtx: No best stats found

We see no OPT_ESTIMATE hints here, reason why there was noting in V$SQL_REOPTIMIZATION_HINTS for cursor 2, but this cursor is still marked as re-optimizable and next execution invalidates it:

KKSMEC: Invalidating old cursor 2 with hash val = 955499861
KKSMEC: Produced New cursor 3 with hash val = 1894156093

We see that we are back to the original plan, which is expected because the static statistics have not changed, and there are no statistics feedback this time.

Child cursor 3 – back to plan 1894156093 – 0.5 seconds

This is the plan that si used for all subsequent executions now.

Plan hash value: 1894156093
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 171 (100)| 1 |00:00:00.04 | 17677 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.04 | 17677 |
| 2 | NESTED LOOPS | | 1 | 1 | 116 (0)| 1491 |00:00:00.04 | 17677 |
| 3 | NESTED LOOPS | | 1 | 1 | 115 (0)| 1491 |00:00:00.04 | 17454 |
| 4 | NESTED LOOPS | | 1 | 49 | 17 (0)| 5648 |00:00:00.01 | 536 |
|* 5 | INDEX RANGE SCAN | D_1F0049A880000016 | 1 | 3 | 3 (0)| 1517 |00:00:00.01 | 13 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | DM_SYSOBJECT_R | 1517 | 16 | 10 (0)| 5648 |00:00:00.01 | 523 |
|* 7 | INDEX RANGE SCAN | D_1F0049A880000010 | 1517 | 71 | 2 (0)| 5648 |00:00:00.01 | 249 |
|* 8 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 5648 | 1 | 2 (0)| 1491 |00:00:00.03 | 16918 |
|* 9 | INDEX UNIQUE SCAN | D_1F0049A880000108 | 5648 | 1 | 1 (0)| 1491 |00:00:00.03 | 15427 |
| 10 | NESTED LOOPS SEMI | | 5648 | 2 | 25 (0)| 1491 |00:00:00.02 | 14827 |
| 11 | NESTED LOOPS | | 5648 | 7 | 18 (0)| 2981 |00:00:00.02 | 12868 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| DM_SYSOBJECT_R | 5648 | 71 | 4 (0)| 2981 |00:00:00.01 | 7747 |
|* 13 | INDEX RANGE SCAN | D_1F0049A880000010 | 5648 | 16 | 3 (0)| 2981 |00:00:00.01 | 6145 |
|* 14 | TABLE ACCESS BY INDEX ROWID | DM_SYSOBJECT_S | 2981 | 1 | 2 (0)| 2981 |00:00:00.01 | 5121 |
|* 15 | INDEX UNIQUE SCAN | D_1F0049A880000108 | 2981 | 1 | 1 (0)| 2981 |00:00:00.01 | 2140 |
|* 16 | INDEX UNIQUE SCAN | D_1F0049A880000145 | 2981 | 52759 | 1 (0)| 1491 |00:00:00.01 | 1959 |
|* 17 | INDEX UNIQUE SCAN | D_1F0049A880000142 | 1491 | 1 | 1 (0)| 1491 |00:00:00.01 | 223 |
----------------------------------------------------------------------------------------------------------------------------------------

After a few tries to get a better plan, the optimizer finally switched back to the first one because it was the best in term of response time (I don’t know exactly which execution statistics are used for this decision, elapsed time is just my guess here).

The interesting point here is to understand that you can see a reoptimized cursor without statistics feedback:

  • No rows for the previous cursor in V$SQL_REOPTIMIZATION_HINTS
  • No ‘statistics feedback’ not in the new cursor plan
  • Difference between E-Rows and A-Rows in the new plan
So what?

SQL optimization is a complex task and there is nothing like an execution is ‘fast’ or ‘slow’, an execution plan is ‘good’ or ‘bad’, an optimizer decision is ‘right’ or ‘wrong’. What is fast after several similar executions can be slow on a busy system because less blocks remains in cache. What is slow at a time where the storage is busy may be fast at another time of the day. What is fast with one single user may raise more concurrency contention on a busy system. Cardinality feedback is a reactive attempt to improve an execution plan. On average, things go better with it, but it is not abnormal that few cases can go wrong for a few executions. You can’t blame the optimizer for that, and fast conclusions or optimizer parameter tweaking are not sustainable solutions. And don’t forget that if your data model is well designed, then the critical queries should have one clear optimal access path which will not depend on a small difference in estimated number of rows.

The only thing I can always conclude when I see cardinality feedback going wrong is that there is something to fix in the design of data model, the statistics gathering and/or the query design. When statistics feedback gives a worse execution plan, it is the consequence of the combination of:

  • mis-estimation of cardinalities: bad, insufficient, or stale statistics
  • mis-estimation of response time: bad system statistics, untypical memory sizing, unrepresentative execution context
  • no clear optimal access path: sub-optimal indexing, lack of partitioning,…

It is a good thing to have the auto-reoptimization coming back to the initial plan when nothing better has been observed. I would love to see more control about it. For example, a hint that sets a threshold of execution time where the optimizer should not try to find better. I filled this idea in https://community.oracle.com/ideas/17514 and you can vote for it.

Update 2-APR-2017

I was not clear in this post, but this is the first time I observed this behavior (multiple reoptimization and then back to original plan), so I’m not sure about the reasons and the different conditions required. This was on 12.1.0.2 with JAN17 PSU and the two Adaptive Statistics backport from 12cR2, adaptive plans set to false and no bind variables.

 

Cet article When automatic reoptimization plan is less efficient est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 13 – create database

Sun, 2017-04-02 04:02

Following the last post about which tools you can use to create your PostgreSQL instance in this post we’ll look at how you can do the “create database” part that you would do in Oracle when not using the database configuration assistant. Of course can can create a database in PostgreSQL but it is not the same as it is in Oracle. To actually create your Oracle database you would do something like this once you have the instance started in nomount mode:

startup nomount pfile="/u01/app/oracle/admin/DB4/create/init.ora";
CREATE DATABASE "DB4"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '/u02/oradata/DB4/system01DB4.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/oradata/DB4/sysaux01DB4.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE 2048M
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/DB4/temp01DB4.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '/u02/oradata/DB4/undotbs01DB4.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE 2048M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u03/oradata/DB4/redog1m1DB4.dbf','/u04/oradata/DB4/redog1m2DB4.dbf') SIZE 50M,
GROUP 2 ('/u03/oradata/DB4/redog2m1DB4.dbf','/u04/oradata/DB4/redog2m2DB4.dbf') SIZE 50M,
GROUP 3 ('/u03/oradata/DB4/redog3m1DB4.dbf','/u04/oradata/DB4/redog3m2DB4.dbf') SIZE 50M,
GROUP 4 ('/u03/oradata/DB4/redog4m1DB4.dbf','/u04/oradata/DB4/redog4m2DB4.dbf') SIZE 50M,
GROUP 5 ('/u03/oradata/DB4/redog5m1DB4.dbf','/u04/oradata/DB4/redog5m2DB4.dbf') SIZE 50M,
GROUP 6 ('/u03/oradata/DB4/redog6m1DB4.dbf','/u04/oradata/DB4/redog6m2DB4.dbf') SIZE 50M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert=('/u02/oradata/DB4/system01DB4.dbf','/u02/oradata/DB4/pdbseed/system01DB4.dbf','/u02/oradata/DB4/sysaux01DB4.dbf','/u02/oradata/DB4/pdbseed/sysaux01DB4.dbf','/u02/oradata/DB4/temp01DB4.dbf','/u02/oradata/DB4/pdbseed/temp01DB4.dbf','/u02/oradata/DB4/undotbs01DB4.dbf','/u02/oradata/DB4/pdbseed/undotbs01DB4.dbf') LOCAL UNDO ON;

Once this completed you start creating the catalog and install additional stuff that you need for your application (e.g. Oracle Text or Oracle Spatial). How does that work in PostgreSQL?

In the last post we had a very quick look at initdb. To create the PostgreSQL database cluster you have to use initdb, there is no other possibility. When you take a look at the options you can provide to initdb there are not too much. The only mandatory parameter is “-D” or “–pgdata”. This tells initdb where you want to have the files created on disk:

postgres@pgbox:/home/postgres/ [pg962final] initdb -D /home/postgres/test/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: de_CH.UTF-8
  NUMERIC:  de_CH.UTF-8
  TIME:     en_US.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /home/postgres/test ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /home/postgres/test/ -l logfile start

There are some important messages which got printed to the screen. The first two lines tell us that all the files will be owned by the operating system user which invoked initdb, postgres, and that the same user must be used to start the instance. Then it gets more interesting as it is about the default encoding/characterset for the template database. When you do not specify what you want you get the default of you operating system session:

 
postgres@pgbox:/home/postgres/ [pg962final] locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC=de_CH.UTF-8
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY=de_CH.UTF-8
LC_MESSAGES="en_US.UTF-8"
LC_PAPER=de_CH.UTF-8
LC_NAME=de_CH.UTF-8
LC_ADDRESS=de_CH.UTF-8
LC_TELEPHONE=de_CH.UTF-8
LC_MEASUREMENT=de_CH.UTF-8
LC_IDENTIFICATION=de_CH.UTF-8
LC_ALL=

Of course you can override that by passing any of the supported character sets to initdb, e.g.:

 
postgres@pgbox:/home/postgres/ [pg962final] rm -rf test
postgres@pgbox:/home/postgres/ [pg962final] mkdir test
postgres@pgbox:/home/postgres/ [pg962final] initdb -D test --encoding=LATIN1 --locale=de_DE

You can control how sorting and the display for numbers, money and so on shall happen by specifying the various “–lc” parameters, e.g.:

 
postgres@pgbox:/home/postgres/ [pg962final] rm -rf test
postgres@pgbox:/home/postgres/ [pg962final] mkdir test
postgres@pgbox:/home/postgres/ [pg962final] initdb -D test --encoding=LATIN1 --locale=de_DE --lc-messages=en_US --lc-monetary=de_DE

PostgreSQL comes with build-in full text search and the line below the encoding stuff tells you that the default will be English. Can overwrite this as well with the “–text-search-config” parameter of initdb.

Maybe the most important message is this:”Data page checksums are disabled.”. This means that PostgreSQL will not use checksums to detect silent data corruptions. Of course this introduces overhead when enabled but your data usually is important, isn’t it? You can enable this by using the “–data-checksums” switch of initdb and this cannot be changed afterwards.

The last message we will look at for now is this one: “fixing permissions on existing directory /home/postgres/test … ok”. What does that mean? When you look at the permissions of the files and directories that got created by initdb you’ll notice that only the owner of the files and directories has permission (no permission for group and world):

drwx------. 19 postgres postgres      4096 Mar 31 11:07 test
postgres@pgbox:/home/postgres/ [pg962final] ls -al test/
total 56
drwx------. 19 postgres postgres  4096 Mar 31 11:07 .
drwx------. 10 postgres postgres  4096 Mar 31 10:51 ..
drwx------.  5 postgres postgres    38 Mar 31 11:07 base
drwx------.  2 postgres postgres  4096 Mar 31 11:07 global
drwx------.  2 postgres postgres    17 Mar 31 11:07 pg_clog
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_commit_ts
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_dynshmem
-rw-------.  1 postgres postgres  4468 Mar 31 11:07 pg_hba.conf
-rw-------.  1 postgres postgres  1636 Mar 31 11:07 pg_ident.conf
drwx------.  4 postgres postgres    37 Mar 31 11:07 pg_logical
drwx------.  4 postgres postgres    34 Mar 31 11:07 pg_multixact
drwx------.  2 postgres postgres    17 Mar 31 11:07 pg_notify
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_replslot
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_serial
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_snapshots
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_stat
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_stat_tmp
drwx------.  2 postgres postgres    17 Mar 31 11:07 pg_subtrans
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_tblspc
drwx------.  2 postgres postgres     6 Mar 31 11:07 pg_twophase
-rw-------.  1 postgres postgres     4 Mar 31 11:07 PG_VERSION
drwx------.  3 postgres postgres    58 Mar 31 11:07 pg_xlog
-rw-------.  1 postgres postgres    88 Mar 31 11:07 postgresql.auto.conf
-rw-------.  1 postgres postgres 22258 Mar 31 11:07 postgresql.conf

When you change that PostgreSQL will refuse to start:

postgres@pgbox:/home/postgres/ [pg962final] chmod 770 test/
postgres@pgbox:/home/postgres/ [pg962final] pg_ctl -D test/ start
server starting
postgres@pgbox:/home/postgres/ [pg962final] FATAL:  data directory "/home/postgres/test" has group or world access
DETAIL:  Permissions should be u=rwx (0700).

Now that we have everything initialized on disk we are ready to start the instance:

postgres@pgbox:/home/postgres/ [pg962final] pg_ctl -D /home/postgres/test/ start
postgres@pgbox:/home/postgres/ [pg962final] LOG:  database system was shut down at 2017-03-31 11:07:05 CEST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
postgres@pgbox:/home/postgres/ [pg962final] psql postgres
psql (9.6.2 dbi services build)
Type "help" for help.

postgres=

From now on you can create a database:

postgres=# create database mydb;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

Check here if you want to know what the other databases are for. When you compare that to the Oracle “create database” statement it creates something like a pluggable database and you can create as many as you want. Inside the database you have the usual objects (schemata,tables,views,…). You can even change the encoding for new databases:

postgres=# create database mydb2 encoding='LATIN1' LC_COLLATE='de_CH.iso88591' LC_CTYPE='de_CH.iso88591' template=template0;
CREATE DATABASE
postgres=# \l
                                     List of databases
   Name    |  Owner   | Encoding |    Collate     |     Ctype      |   Access privileges   
-----------+----------+----------+----------------+----------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8    | 
 mydb2     | postgres | LATIN1   | de_CH.iso88591 | de_CH.iso88591 | 
 postgres  | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8    | 
 template0 | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8    | =c/postgres          +
           |          |          |                |                | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8    | =c/postgres          +
           |          |          |                |                | postgres=CTc/postgres
(5 rows)

Important to know is that users are global so you can not create users inside databases but of course you can grant access to databases to different users and users can be owners of databases:

postgres=# alter database mydb owner to myusr;
ALTER DATABASE
postgres=# \l
                                     List of databases
   Name    |  Owner   | Encoding |    Collate     |     Ctype      |   Access privileges   
-----------+----------+----------+----------------+----------------+-----------------------
 mydb      | myusr    | UTF8     | en_US.UTF-8    | en_US.UTF-8    | 
 mydb2     | postgres | LATIN1   | de_CH.iso88591 | de_CH.iso88591 | 
 postgres  | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8    | 
 template0 | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8    | =c/postgres          +
           |          |          |                |                | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8    | en_US.UTF-8    | =c/postgres          +
           |          |          |                |                | postgres=CTc/postgres
(5 rows)

How can you then install options into the databases? There are none, all is included. What maybe comes closest to what Oracle calls options are extensions or modules. Some of them are provided by default and you can find them usually in the “share/extension” directory where you installed the PostgreSQL binaries:

postgres@pgbox:/u01/app/postgres/product/96/db_2/ [pg962final] ls
bin  include  lib  share
postgres@pgbox:/u01/app/postgres/product/96/db_2/ [pg962final] ls share/extension/
adminpack--1.0.sql                  hstore--1.3--1.4.sql                  pageinspect.control                      plperlu--unpackaged--1.0.sql
adminpack.control                   hstore--1.4.sql                       pageinspect--unpackaged--1.0.sql         plpgsql--1.0.sql
...

All of those can be installed per database, e.g.:

postgres@pgbox:/home/postgres/ [pg962final] psql postgres
psql (9.6.2 dbi services build)
Type "help" for help.

postgres=# \connect mydb
You are now connected to database "mydb" as user "postgres".
mydb=# create extension hstore;
CREATE EXTENSION
mydb=# \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.4     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

mydb=# 

Others are not available by default and you usually need to download them from github, e.g.
cstore

For an overview can you check the PostgreSQL Extension Network.

Conclusion: You need to use initdb to initialize all the files for your PostgreSQL instance on disk. Use pg_ctl to start your instance and then you are ready to create databases. Hope this helps for starting with PostgreSQL.

 

Cet article Can I do it with PostgreSQL? – 13 – create database est apparu en premier sur Blog dbi services.

Documentum – Increase the number of concurrent_sessions

Sat, 2017-04-01 07:01

In Documentum, there is a parameter named concurrent_sessions which basically defines how many sessions a Content Server can open simultaneously. This parameter is defined in the server.ini file (or server_<dm_server_config.object_name>.ini on a Remote Content Server) of each docbase and it has a default value of 100.

An empty Content Server with an IndexAgent and D2 installed (without user using it) will usually take around 10 sessions for the jobs, for the searches, aso… As soon as there are users in the environment, the number of concurrent sessions will quickly grow and therefore depending on how many users you have, you may (will) need to increase this limit. To be more precise, the concurrent_sessions controls the number of connections the server can handle concurrently. This number must take into account not only the number of users who are using the repository concurrently, but also the operations those users are executing. Some operations require a separate connection to complete. For example:

  • Issuing an IDfQuery.execute method with the readquery flag set to FALSE causes an internal connection request
  • Executing an apply method or an EXECUTE DQL statement starts another connection
  • When the agent exec executes a job, it generally requires two additional connections
  • Issuing a full-text query requires an additional connection
  • aso…

 

Do you already know how to increase the number of allowed concurrent sessions? I’m sure you do, it’s pretty easy:

  1. Calculate the appropriate number of concurrent sessions needed based on the information provided above
  2. Open the file $DOCUMENTUM/dba/config/DOCBASE/server.ini and replace “concurrent_sessions = 100″ with the desired value (“concurrent_sessions = 500″ for example)
  3. Restart the docbase DOCBASE using your custom script of the default Documentum scripts under $DOCUMENTUM/dba
  4. Ensure that the Database used can handle the new number of sessions properly and see if you need to increase the sessions/processes for that

 

To know how many sessions are currently used, it’s pretty simple, you can just execute the DQL “execute show_sessions” but be aware that all sessions will be listed and that’s not exactly what we want. Therefore you need to keep only the ones with a dormancy_status that is Active in the final count otherwise the value will be wrong. The number of active sessions is not only linked to a docbase but also to a Content Server (to be more precise, it is only linked to a dm_server_config object). This means that if you have a High Availability environment, each Content Server (each dm_server_config object) will have its own number of active sessions. This also means that you need to take this into account when calculating how many concurrent sessions you need.

For example if you calculated that you will need a total of 500 concurrent sessions (again it’s not the number of concurrent users!) for a docbase, then:

  • If you have only one Content Server, you will need to set “concurrent_sessions = 500″ on the server.ini file of this docbase.
  • If you have two Content Servers (two dm_server_config objects) for the docbase, then you can just set “concurrent_sessions = 275″ on each server.ini files. Yes I know 2*275 isn’t really equal to 500 but that’s because each Content Server will need its internal sessions for the jobs, searches, aso… In addition to that, the Content Servers might need to talk to each other so these 25 additional sessions wouldn’t really hurt.

 

Now is the above procedure working for any value of the concurrent_sessions? Well the answer to this question is actually the purpose of this blog: yes and no. From a logical point of view, there is no restriction to this value but from a technical point of view, there is… A few months ago at one of our customer, I was configuring a new Application which had a requirement of 2150 concurrent_sessions accross a High Availability environment composed of two Content Servers. Based on the information provided above, I started the configuration with 1100 concurrent sessions on each Content Server to match the requirement. But then when I tried to start the docbase again, I got the following error inside the docbase log file ($DOCUMENTUM/dba/log/DOCBASE.log):

***********************************************************

Program error: Illegal parameter value for concurrent_sessions: 1100

***********************************************************

Usage: ./documentum -docbase_name <docbase name> -init_file <filename> [-o<option>]

    -docbase_name : name of docbase
    -init_file    : name of server.ini file (including path) which
                    contains the server startup parameters

Usage: ./documentum -h

 

As you can see, the docbase refuses to start with a number of concurrent sessions set to 1100. What’s the reason behind that? There is an artificial limit set to 1020. This is actually mentioned in the documentation:
The maximum number of concurrent sessions is dependent on the operating system of the server host machine. The limit is:

  • 3275 for Windows systems
  • 1020 for Linux, Solaris, and AIX systems

 

So why is there a limit? Why 1020? This limit is linked to the FD_SETSIZE value. The documentation on FD_SETSIZE says the following:

An fd_set is a fixed size buffer. Executing FD_CLR() or FD_SET() with a value of fd that is negative or is equal to or larger than FD_SETSIZE will result in undefined behavior. Moreover, POSIX requires fd to be a valid file descriptor.

 

Thus FD_SETSIZE doesn’t explicitly limit the number of file descriptors that can be worked on with the system select() call. Inside every UNIX process, for its PID, Documentum maintain a corresponding list (of pointers) of file descriptors. In UNIX based systems, every Documentum session is created as a separate process. Since the number of sessions created directly depends on the number of file descriptors in an OS, each of these processes will be having a list of the file descriptors within their process which will be taking a good chunk of physical memory. With this technical reasoning the value 1020 has been set to be the max concurrent sessions available by default in Documentum.

So basically this limit of 1020 has been set arbitrary by EMC to stay within the default OS (kernel) value which is set to 1024 (can be checked with “ulimit -Sa” for example). An EMC internal task (CS-40186) was opened to discuss this point and to discuss the possibility to increase this maximum number. Since the current default limit is set only in regards to the default OS value of 1024, if this value is increased to 4096 for example (which was our case since the beginning), then there is no real reason to be stuck at 1020 on Documentum side. The Engineering Team implemented a change in the binaries that allows changing the limit. This is done by adding the environment variable DM_FD_SETSIZE.

Therefore to change the concurrent sessions above 1020 (1100 in this example) and in addition to the steps already mentioned before, you also need to do the following (depending on your OS, you might need to update the .bashrc or .profile files instead):

echo "export DM_FD_SETSIZE=1200" >> ~/.bash_profile
source ~/.bash_profile
$DOCUMENTUM/dba/dm_start_DOCBASE

 

With this environment variable DM_FD_SETSIZE now set to 1200, we can use 1100 concurrent sessions without issue. The value that will be used for the concurrent_sessions will be the one from the server.ini file. We just need to define a DM_FD_SETSIZE variable with a value equal or bigger than what we want. Also, I didn’t mention the ulimit but of course, you also need to set the limits of your OS accordingly (this is done in the file /etc/limits.conf or inside any file under /etc/limits.d/).

 

 

Cet article Documentum – Increase the number of concurrent_sessions est apparu en premier sur Blog dbi services.

Documentum – Wrong dfc versions after installation, upgrade or patch

Sat, 2017-04-01 06:09

If you are familiar with Documentum or if you already read some of my blogs, then you probably already know that EMC has sometimes issues with libraries. In a previous blog (this one), I talked about the Lockbox versions which caused us an issue and in this blog, I will talk about DFC versions.

Whenever you install a CS patch or another patch, it will probably have its own DFC libraries simply because EMC fixed something in it or because it was needed. Whenever you install D2, it will also have its own DFC libraries in the JMS and the WAR files. The problem is that the DFC libraries are everywhere… Each and every DFC client has its own DFC libraries which come when you install it, patch it, aso… Basically that’s not a wrong approach, it ensure that the components will work wherever they are installed so it can always talk to the Content Server.

The problem here is that the DFC libraries are changing at every patch almost and therefore it is kind of complicated to keep a clean environment. It already happened to us that two different patches (CS and D2 for example), released on the exact same day, were using different DFC versions and you will see below another example coming from the same package…  You can live with a server having five different DFC versions but this also means that whenever a bug impact one of your DFC library, it will be hard to fix that because you then need to deploy the next official patch which is always a pain. It also multiplies the number of issues that impact your DFC versions since you are running several versions at the same time.

I’m not saying that you absolutely need to always use only the latest DFC version but if you can properly and quickly perform the appropriate testing, I believe it can brings you something. A few weeks ago for example, one of the Application Teams we are supporting had an issue with some search functionalities in D2. This was actually caused by the DFC version bundled with D2 (DFC 7.2P03 I think) and we solved this issue by simply using the DFC version coming from our Content Server (DFC 7.2P05) which was only two patch above.

To quickly and efficiently see which versions of the DFC libraries you are using and where, you can use:

find <WHERE_TO_FIND> -type f -name dfc.jar -print0 | while read -d $'' file; do echo "DFC: `$JAVA_HOME/bin/java -cp "$file" DfShowVersion`  ===  Size: `ls -ks "$file" | awk '{print $1}'` kb  ===  Location: $file"; done

or

find <WHERE_TO_FIND> -type f -name dfc.jar -print0 | while IFS= read -r -d '' file; do echo "DFC: `$JAVA_HOME/bin/java -cp "$file" DfShowVersion`  ===  Size: `ls -ks "$file" | awk '{print $1}'` kb  ===  Location: $file"; done

 

You can execute these commands on a Content Server, Application Server (Note: dfc.jar files might be on the D2/DA war files if you aren’t using exploded deployments), Full Text Server or any other Linux Servers for what it matters. These commands handle the spaces in the paths even if normally you shouldn’t have any for the dfc files. To use them, you can just replace <WHERE_TO_FIND> with the base folder of your installation. This can be $DOCUMENTUM for a Content Server, $XPLORE_HOME for a Full Text Server, aso… Of course you still need to have the proper permissions to see the files otherwise it will be quite useless to execute this command.

A small example on a Content Server 7.3 (no patches are available yet) including xCP 2.3 P05 (End of February 2017 patch which is supposed to be for CS 7.3):

[dmadmin@content_server_01 ~]$ find $DOCUMENTUM -type f -name dfc.jar -print0 | while read -d $'' file; do echo "DFC: `$JAVA_HOME/bin/java -cp "$file" DfShowVersion`  ===  Size: `ls -ks "$file" | awk '{print $1}'` kb  ===  Location: $file"; done
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/product/7.3/install/composer/ComposerHeadless/plugins/com.emc.ide.external.dfc_1.0.0/lib/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/shared/temp/installer/wildfly/dctmutils/templates/dfc/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/shared/dfc/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/shared/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/dfc.jar
DFC: 7.2.0210.0184  ===  Size: 15212 kb  ===  Location: $DOCUMENTUM/shared/wildfly9.0.1/server/DctmServer_MethodServer/deployments/bpm.ear/lib/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/shared/wildfly9.0.1/server/DctmServer_MethodServer/deployments/ServerApps.ear/lib/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/shared/wildfly9.0.1/server/DctmServer_MethodServer/deployments/acs.ear/lib/dfc.jar
DFC: 7.2.0210.0184  ===  Size: 15212 kb  ===  Location: $DOCUMENTUM/shared/wildfly9.0.1/server/DctmServer_MethodServer/tmp/vfs/deployment/deploymente7c710bab402b3f7/dfc.jar-7ac143a725d0471/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/shared/wildfly9.0.1/server/DctmServer_MethodServer/tmp/vfs/deployment/deploymente7c710bab402b3f7/dfc.jar-bc760ece35b05a08/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $DOCUMENTUM/shared/wildfly9.0.1/server/DctmServer_MethodServer/tmp/vfs/deployment/deploymente7c710bab402b3f7/dfc.jar-35c79cfe4b79d974/dfc.jar

 

As you can see above, it looks like there are two different versions of the DFC library on this Content Server which has just been installed: one coming from the CS 7.3 which is therefore in 7.3 P00 build number 205 and another version which is still in 7.2 P21 build number 184. This second version has been put on the Content Server by the xCP 2.3 P05 installer. Therefore using a 7.2 library on a 7.3 Content Server is a little bit ugly but the good news is that they are both in a pretty recent version since these two libraries were released almost at the same time (end of 2016/beginning of 2017). Therefore here I don’t think it would be a big problem even if as soon as the CS 7.3 P01 is out (normally end of this month), we will replace all dfc.jar files with the 7.3 P01 versions.

Another example on a Full Text Server using xPlore 1.6 (same as before, no patches are available yet for xPlore 1.6) including one Primary Dsearch and two IndexAgents for DOCBASE1 and DOCBASE2:

[xplore@fulltext_server_01 ~]$ find $XPLORE_HOME -type f -name dfc.jar -print0 | while read -d $'' file; do echo "DFC: `$JAVA_HOME/bin/java -cp "$file" DfShowVersion`  ===  Size: `ls -ks "$file" | awk '{print $1}'` kb  ===  Location: $file"; done
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/temp/installer/wildfly/dctmutils/templates/dfc/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/dfc/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/wildfly9.0.1/server/DctmServer_Indexagent_DOCBASE1/tmp/vfs/deployment/deployment5417db9ca7307cfc/dfc.jar-aa1927b943be418f/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/wildfly9.0.1/server/DctmServer_Indexagent_DOCBASE1/deployments/IndexAgent.war/WEB-INF/lib/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/wildfly9.0.1/server/DctmServer_Indexagent_DOCBASE2/tmp/vfs/deployment/deploymentbb9811e18d147b6a/dfc.jar-7347e3d3bbd8ffd/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/wildfly9.0.1/server/DctmServer_Indexagent_DOCBASE2/deployments/IndexAgent.war/WEB-INF/lib/dfc.jar
DFC: 7.3.0000.0196  ===  Size: 15220 kb  ===  Location: $XPLORE_HOME/wildfly9.0.1/server/DctmServer_PrimaryDsearch/tmp/vfs/deployment/deployment5fd2cff2d805ceb2/dfc.jar-29edda1355c549b8/dfc.jar
DFC: 7.3.0000.0196  ===  Size: 15220 kb  ===  Location: $XPLORE_HOME/wildfly9.0.1/server/DctmServer_PrimaryDsearch/deployments/dsearchadmin.war/WEB-INF/lib/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/watchdog/lib/dfc.jar
DFC: 7.3.0000.0205  ===  Size: 15236 kb  ===  Location: $XPLORE_HOME/setup/qbs/tool/lib/dfc.jar

 

Do you see something strange here? Because I do! This is a completely new xPlore 1.6 server which has just been installed and yet we have two different versions of the DFC libraries… It’s not a difference on the minor version but it’s a difference on the build number! As you can see above, it looks like the PrimaryDsearch uses a DFC 7.3 P00 build number 196 while all other DFC versions are in 7.3 P00 build number 205 (so just like the Content Server). The problem here is that each xPlore modules (IA, Dsearch, aso…) are built by different xPlore Teams. Therefore the team that package the Dsearch libraries isn’t the same that the one that package the IndexAgent libraries.

Since there is a difference here, it probably means that the Dsearch team built their package some days/weeks before the other teams (from IA, CS, aso…) and therefore the DFC libraries included in the Dsearch are older… Is it an issue or not? According to EMC, it’s not, BUT I wouldn’t be so categorical. If EMC built this library 9 additional times, it’s not for nothing… There must be a reason behind those builds and therefore not having the latest build seems a little bit risky to me. Since this is just a sandbox environment, I will most probably just wait for the P01 of the xPlore 1.6 which will be release in a few days and I will implement it to have an aligned version of the DFC for all components.

 

Have fun finding issues in the EMC releases :).

 

 

Cet article Documentum – Wrong dfc versions after installation, upgrade or patch est apparu en premier sur Blog dbi services.

Documentum – Thumbnail not working with TCS

Sat, 2017-04-01 04:41

A few months ago and right after a migration of around 0.5TB of documents, we enabled the TCS for one of our Application. We were using a Content Server 7.2 P05 with the associated D2 4.5 P03. As already mentioned in a previous blog, D2 4.5 doesn’t handle the previews of the documents and therefore there were also a Thumbnail Server used by this Application. The setup of the TCS for the document filestores went well without issue but when we tried to do the same thing for the Thumbnail filestore, the previews weren’t working anymore.

Basically when you configure the Thumbnail to use a TCS filestore, you need to request new renditions for existing documents otherwise they will continue to use the non-TCS filestore.

If you access D2 while inspecting the network traffic or using the browser dev/debug feature, you will find that D2 is building the Thumbnail preview URL using the value of the “dm_document.thumbnail_url”. This thumbnail_url is actually – by default – a concatenation of several things: thumbnail_url = base_url + path + store.

Therefore if you define:

  • the Thumbnail base_url to be “https://thumbnail_alias/thumbsrv/getThumbnail?”
  • the Thumbnail TCS filestore to be “trusted_thumbnail_store_01″
  • a test document 09012345801a8f56 with a file system path (get_path) to be: “00012345/80/00/10/4d.jpg”

Then D2 will retrieve a thumbnail_url that is:

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4d.jpg&store=trusted_thumbnail_store_01

 

When accessing this URL, if the filestore mentioned above is indeed a TCS filestore, this is the kind of logs that will be generated:

Jul 05, 2016 9:42:15 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_RETRIEVE_DOCBASE_THUMB] Retrieving docbase thumbnail...
Jul 05, 2016 9:42:15 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : the ETag received is the path - will return 304 response
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_NEW_REQUEST] New request: path=00012345/80/00/10/4d.jpg&store=trusted_thumbnail_store_01
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_RETRIEVE_DOCBASE_THUMB] Retrieving docbase thumbnail...
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_RETRIEVE_STORE] Retrieving storage area...
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : getting storage area for docbase: 00012345 store: trusted_thumbnail_store_01
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_CHECK_SECURITY] Checking security...
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : About to start reading files...
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : Trusted content store found, will use DFC for decryption
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : Content paramters are: format= null; page=null; page modifier=null
Jul 05, 2016 9:45:12 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: Object ID missing from request...

 

When using a non-TCS filestore, this url will retrieve the document preview properly (of course the name of the store was “thumbnail_store_01″ and not “trusted_thumbnail_store_01″) but as you can see above, with a TCS filestore, the Thumbnail is trying to use the DFC for decryption but isn’t able to because the Object ID is missing on the request… With a non-TCS filestore, the Thumbnail is retrieving the content directly from the file system but it’s not possible to do that with a TCS filestore because of the encryption. Therefore a TCS-enabled Thumbnail has to use the “getContent” method of the DFC and this method requires the Object ID. That’s the issue here.

When we faced this issue last summer, we contacted EMC because that was clearly a bug in how D2 is constructing the URLs to request the preview and not how the Thumbnail Server is processing the requested URLs. After several days, the D2 engineering team provided a hotfix to enable D2 to provide the correct parameters. Basically with the new hotfix deployed, D2 was now able to generate the following URL:

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4d.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres

 

Note: In this case, we also had to specify the format (jpeg_lres). While waiting for the EMC hotfix, we performed some tests on our side by trying to add missing parameters and trying to understand what is needed for the Thumbnail Server in the requests. At some point in time, we found out that the Thumbnail was trying to use “jpeg_th” as a default value for the format if you don’t specify any. As far as I know, we have a pretty default Thumbnail configuration but we don’t have any “jpeg_th” formats. Only jpeg_lres and jpeg_story are used and therefore I don’t know where this jpeg_th is coming from. I believe that’s an issue of the Thumbnail Server because EMC included the format in the D2 hotfix too, after we mentioned that to them.

 

So using this new URL, the generated log files were:

Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_NEW_REQUEST] New request: path=00012345/80/00/10/4d.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_RETRIEVE_DOCBASE_THUMB] Retrieving docbase thumbnail...
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_RETRIEVE_STORE] Retrieving storage area...
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : getting storage area for docbase: 00012345 store: trusted_thumbnail_store_01
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : [DM_TS_T_CHECK_SECURITY] Checking security...
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : About to start reading files...
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : Trusted content store found, will use DFC for decryption
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : Content paramters are: format= jpeg_lres; page=null; page modifier=null
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : will be retrieving object 09012345801a8f56
Jul 12, 2016 6:29:53 AM org.apache.catalina.core.ApplicationContext log
INFO: getThumbnail: [DEBUG] : Session ok for 00012345

 

And it worked; the preview of the first page of this document has been displayed. The hotfix seemed to work so we started to deploy it in our DEV environments while waiting for a confirmation from the Application Team that it was also working from D2 (because of SoD, our admin accounts don’t have access to documents in D2). But then after a few tests, The App Team found out that the hotfix was actually only partially working: only for the first page! Indeed EMC created this hotfix using a one-page document and they never tried to retrieve the previews of a multi-page document. The thing with the Thumbnail Server is that if you have a 25-pages document, then this document will have 25 previews. I already talked about that in another previous blog so you can take a look at this blog to have more information on how to manage that.

I will suppose that 09012345801a8f56 is a 3-pages document. I gathered some information from this document and also got the path of the thumbnail previews that are related to the 3 pages:

API> ?,c,select r_object_id, full_format, parent_id, content_size, full_content_size, set_time, set_file from dmr_content where any parent_id='09012345801a8f56'
r_object_id       full_format  parent_id         content_size  full_content_size  set_time           set_file
----------------  -----------  ----------------  ------------  -----------------  -----------------  --------------------------------------------------------------------
06012345801d95cc  jpeg_lres    09012345801a8f56  60467         60467              7/4/2016 13:22:39  C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
	get_path => /data/DOCBASE/trusted_thumbnail_storage_01/00012345/80/00/10/4d.jpg

06012345801d95cd  jpeg_lres    09012345801a8f56  138862        138862             7/4/2016 13:22:39  C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
	get_path => /data/DOCBASE/trusted_thumbnail_storage_01/00012345/80/00/10/4e.jpg

06012345801d95ce  jpeg_lres    09012345801a8f56  29596         29596              7/4/2016 13:22:39  C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
	get_path => /data/DOCBASE/trusted_thumbnail_storage_01/00012345/80/00/10/4f.jpg

 

So here we have three different jpg files for the same “parent_id” (the document) and each one is a preview of a specific page. These previews have a different size (content_size/full_content_size), therefore the previews should be different!

With the information provided above, the previews URLs generated by D2 (using the new hotfix) are:

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4d.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4e.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres


https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4f.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres

 

Three different URLs for three different pages. That looks fine but actually if you access all these three URLs, you will always see the preview of the first page of the document… It goes even beyond that! Actually you can put any path in the URL and it will always show the first page of the document (09012345801a8f56). To confirm that, you can access the following URLs and see by yourself that they will also display the same preview:

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/zz.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/zz/zz/zz/zz.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres


https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres

 

Based on all the above information, you can understand that the way the Thumbnail Server is working is really different when using a non-TCS or a TCS filestore… For non-TCS filestores, the URL points directly to the actual file and that’s the reason why the full path is really important: it leads directly to the right file and each page has its own path. For TCS filestores, since all the files are encrypted, the Thumbnail Server can’t directly use the file path. Therefore it relies on the DFC to first decrypt the file and then return the result. That’s the reason why only the docbase ID is needed inside the “path” when using a TCS and that everything else in the path is completely useless. On the other end and as seen previously in this blog, you of course also need some additional information to specify which preview you want.

In addition to the parameters we saw above and in order to uniquely identify different renditions, page and/or page_modifier is also required with a TCS filestore. Both attributes are part of dmr_content table and page is the position of the content when the object has multiple contents (generally it will always be zero by default) while page_modifier uniquely identifies a rendition within the same page number and format for a document. If no/null page_modifier is passed in the URL then the rendition with an empty page_modifier value is returned. If there isn’t any rendition in the docbase without a page_modifier value, then the one with the smallest page_modifier (in alphabetical order) will be returned instead: that’s the reason why all previous URLs always returned the preview of the first page only… In short, for non-TCS filestores, the path pretty much does the job, but for TCS filestores we need additional parameters to uniquely identify the rendition.

 

So to summarize the investigation we did with EMC (because yes we found a definitive solution), the minimum elements that should be present in the Thumbnail URLs are:

  • non-TCS filestore:
    • Full path to the right file
    • Store used
  • TCS filestore:
    • Path containing at least the Docbase ID
    • Store used
    • Document ID (parent_id)
    • Format of the preview
    • page and/or page_modifier (from the dmr_content table)

 

In the end, EMC provided another hotfix which fix the first hotfix. The complete URLs are now generated by D2 and the previews are working properly even with a TCS Thumbnail filestore. Here are the examples for the 3-pages document:

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4d.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres&page_modifier=000000001

https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4e.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres&page_modifier=000000002


https://thumbnail_alias/thumbsrv/getThumbnail?path=00012345/80/00/10/4f.jpg&store=trusted_thumbnail_store_01&did=09012345801a8f56&format=jpeg_lres&page_modifier=000000003

 

The definitive hotfix should normally be included in all the 2017 releases according to EMC.

 

 

Cet article Documentum – Thumbnail not working with TCS est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 12 – dbca (database configuration assistant)

Fri, 2017-03-31 12:59

Short answer: No. Long answer: No. Every other possible answer: No. There is no such tool in PostgreSQL.
In Oracle you can use the database configuration assistant to create your Oracle database. But this is not a must, you can do the same using scripts. Either you use dbca to generate scripts based on the questions you answered in the several screens or you use your own set of scripts to create an Oracle database (You can use dbca templates in addition or you invoke dbca in silent mode and pass your options on the command line). In PostgreSQL the steps to create a PostgreSQL instance are totally different, no surprise here. It already starts with the wording: In PostgreSQL you do not create a database you create a database cluster. Confused? This has nothing to do with multiple nodes are multiple instances as you know it from Oracle (e.g. Real Application Clusters). What a database cluster means are the files on disk (a collection of databases) that are managed by a set of background processes. Cluster just means: belonging together (there are many of types clusters, actually). I’ll use the term database instance from now on. So how can you create a database instance after you installed the PostgreSQL binaries?

When you use the graphical installer provided to the community by EnterpriseDB a PostgreSQL instance will be created automatically after the binaries have been installed. This will create a service on Windows and startup/shutdown scripts on Linux based operating systems (not sure about Mac OS). On Linux when you install the standard way the installer will require root privileges as it needs to add the scripts to startup and shutdown PostgreSQL automatically when then server starts up or does down to the system configuration. Once you started the installer it is just a matter of clicking through the screens:

pg_install_1
pg_install_2
pg_install_3
pg_install_4
pg_install_5
pg_install_6
pg_install_7
pg_install_8
pg_install_9

What happened in the background is that the PostgreSQL instance was created and started:

postgres@pgbox:/home/postgres/ [pg960final] ps -ef | grep postgres | egrep -v "ssh|bash|ps"
postgres  3412     1  0 07:30 ?        00:00:00 /opt/PostgreSQL/9.6/bin/postgres -D /opt/PostgreSQL/9.6/data
postgres  3413  3412  0 07:30 ?        00:00:00 postgres: logger process   
postgres  3415  3412  0 07:30 ?        00:00:00 postgres: checkpointer process   
postgres  3416  3412  0 07:30 ?        00:00:00 postgres: writer process   
postgres  3417  3412  0 07:30 ?        00:00:00 postgres: wal writer process   
postgres  3418  3412  0 07:30 ?        00:00:00 postgres: autovacuum launcher process   
postgres  3419  3412  0 07:30 ?        00:00:00 postgres: stats collector process   
postgres  3805  3579  0 07:56 pts/0    00:00:00 grep --color=auto postgres

In addition the installer created a systemd service (I am on CentOS) so your instance will start up and shutdown automatically (and a postgres user and group, of course):

postgres@pgbox:/home/postgres/ [pg960final] systemctl status postgresql-9.6.service
● postgresql-9.6.service - PostgreSQL 9.6 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-03-31 07:30:01 CEST; 28min ago
  Process: 3410 ExecStart=/opt/PostgreSQL/9.6/bin/pg_ctl start -w -t ${TimeoutSec} -D /opt/PostgreSQL/9.6/data -l /opt/PostgreSQL/9.6/data/pg_log/startup.log (code=exited, status=0/SUCCESS)
 Main PID: 3412 (postgres)
   CGroup: /system.slice/postgresql-9.6.service
           ├─3412 /opt/PostgreSQL/9.6/bin/postgres -D /opt/PostgreSQL/9.6/data
           ├─3413 postgres: logger process   
           ├─3415 postgres: checkpointer process   
           ├─3416 postgres: writer process   
           ├─3417 postgres: wal writer process   
           ├─3418 postgres: autovacuum launcher process   
           └─3419 postgres: stats collector process   

From now on you can connect to the instance and start your journey:

postgres@pgbox:/home/postgres/ [pg960final] /opt/PostgreSQL/9.6/bin/psql postgres
Password: 
psql.bin (9.6.2)
Type "help" for help.

postgres= \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=

But this is not the only method for creating a PostgreSQL instance. On Linux most of the distributions provide PostgreSQL packages in their repositories which you then can install using yum (when you are on RedHat based systems) or apt (when you are on Debian based systems (There are others as well, such as YaST on SuSE but yum and apt are the most popular, I believe). In my case, using the official CentOS repositories, I can use yum and check what CentOS provides:

postgres@pgbox:/home/postgres/ [pg960final] yum search postgresql
...
postgresql.i686 : PostgreSQL client programs
postgresql.x86_64 : PostgreSQL client programs
postgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql-devel.i686 : PostgreSQL development header files and libraries
postgresql-devel.x86_64 : PostgreSQL development header files and libraries
postgresql-docs.x86_64 : Extra documentation for PostgreSQL
postgresql-jdbc.noarch : JDBC driver for PostgreSQL
postgresql-jdbc-javadoc.noarch : API docs for postgresql-jdbc
postgresql-libs.i686 : The shared libraries required for any PostgreSQL clients
postgresql-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql-odbc.x86_64 : PostgreSQL ODBC driver
postgresql-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql-plpython.x86_64 : The Python2 procedural language for PostgreSQL
postgresql-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql-test.x86_64 : The test suite distributed with PostgreSQL
postgresql-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL
...

The packagers decided to split PostgreSQL into several packages as you can see above. If you only want to install the core server you would:

[root@centos7 ~] yum install postgresql-server.x86_64

In this case the instance will not be created automatically:

[root@centos7 ~] ps -ef | grep postgres
root      9981  4558  0 08:18 pts/0    00:00:00 grep --color=auto postgres

But, as with the graphical installer above, a systemd service was created for you:

[root@centos7 ~] systemctl list-unit-files | grep postgres
postgresql.service                            disabled

Then we can just enable and start the service?

[root@centos7 ~] systemctl enable postgresql.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[root@centos7 ~] systemctl start postgresql.service
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.

Not really. What the installation with yum did not do for you is to create the instance. The good thing with the CentOS provided packages is that you get helpers to create the instance which are not there by default. For creating the instance you would:

-bash-4.2$ sudo postgresql-setup initdb
Initializing database ... OK

… and your instance got created but not yet started. To start it:

-bash-4.2$ sudo systemctl start postgresql.service
-bash-4.2$ psql postgres
psql (9.2.18)
Type "help" for help.

Quite easy. But here you can see one issue with the packages provided by the Linux distributions. What was installed in my case is PostgreSQL 9.2.18 and there are some points to consider with that: The latest PostgreSQL 9.2 release is 9.2.20 (as of today). So you miss 2 minor versions containing bug fixes. Even more important PostgreSQL 9.2 will go out of support this September because it was released almost 5 years ago, the current release is 9.6.2 (supported until September 2021). Not a good choice for running a production server.

Luckily there is another possibility: The PostgreSQL community provides repositories as well. There is one for yum based systems and one for apt based systems. For getting the yum based repositories into CentOS you would (for PostgreSQL 9.6):

[root@centos7 ~] wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
[root@centos7 ~] yum localinstall pgdg-centos96-9.6-3.noarch.rpm 

When you check what is available now you’ll find this:

postgresql96.x86_64 : PostgreSQL client programs and libraries
postgresql96-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
postgresql96-debuginfo.x86_64 : Debug information for package postgresql96
postgresql96-devel.x86_64 : PostgreSQL development header files and libraries
postgresql96-docs.x86_64 : Extra documentation for PostgreSQL
postgresql96-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql96-odbc.x86_64 : PostgreSQL ODBC driver
postgresql96-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql96-plpython.x86_64 : The Python procedural language for PostgreSQL
postgresql96-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql96-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql96-tcl.x86_64 : A Tcl client library for PostgreSQL
postgresql96-tcl-debuginfo.x86_64 : Debug information for package postgresql96-tcl
postgresql96-test.x86_64 : The test suite distributed with PostgreSQL

Installation is the same as with the CentOS repositories above:

[root@centos7 ~] yum install postgresql96-server.x86_64
[root@centos7 ~] systemctl list-unit-files | grep postgres
postgresql-9.6.service    
[root@centos7 ~] systemctl enable postgresql-9.6.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.
[root@centos7 ~] ls /usr/pgsql-9.6/bin/initdb ^C
[root@centos7 ~] su - postgres
Last login: Fri Mar 31 08:29:10 CEST 2017 on pts/1
-bash-4.2$ /usr/pgsql-9.6/bin/initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/9.6/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data -l logfile start

-bash-4.2$ sudo systemctl start postgresql-9.6.service
-bash-4.2$ /usr/pgsql-9.6/bin/psql postgres
psql (9.6.2)
Type "help" for help.

postgres= \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

And ready. The packages provided by the PostgreSQL community are a much better choice than the ones provided by your distributions because here you get the latest ones.

What we did above as an extra step was calling a utility which is named “initdb”. initdb is the tool you use to create the PostgreSQL instance after you installed the binaries. The graphical installer uses initdb in the background as well as there is no other way to do it. When you compile and install PostgreSQL from source you would need to use initdb to create your PostgreSQL instance as well.

Conclusion: Depending on how you install PostgreSQL onto your system the PostgreSQL instance is created automatically (when you use the installer provided by EnterpriseDB), almost automatically when you use the packages provided by your distribution and almost automatically when you use the packages provided directly by the PostgreSQL community. In the next post we’ll look at initdb in more detail.

 

Cet article Can I do it with PostgreSQL? – 12 – dbca (database configuration assistant) est apparu en premier sur Blog dbi services.

SQL Server on Linux and experimental Flexible Architecture

Fri, 2017-03-31 06:28

In this blog post let’s talk a little bit about SQL Server optimal installation on the Linux file system. This is probably one of the first considerations we will have as a database administrator. Actually basic installation of SQL Server can be resumed to use yum command and let the SQL Server installer place all related files on the file system. But in a production environment, the story will be different and we will have to follow best practices. In fact, Microsoft guidelines already exist in Windows world and concern mainly placement of system / user database files and tempdb as well.

But what about Linux? In my opinion, the game is not so different and existing database file placement rules also apply on Linux. We don’t work with drive letters on Linux but it doesn’t matter because in some cases we already use the same mount point concept.

How to start and how to achieve an optimal placement were the questions in my head since the last meetup I attended about MongoDB and organized by my colleagues Medhi Bada and Eric Dusquesnoy.

During the meeting, they talked about MongoDB installation according the OFA architecture initiated by Oracle and which has many similar aspects with FHS on Unix system. Another interesting thing I’ve realized after discussing with my colleagues is that we also propose the same based-model for other SGBDs like MySQL or PostgreSQL (with some adjustments in the respect of their respective context).

In a nutshell, OFA architecture provides the following interesting things:

  • A logical layout for the databases that helps DBAs to manage their system. The idea here is to provide a predictable and consistent file naming structure to manage and to deal easily with issues such as adding hardware, installing new instance etc…
  • Preventing performance bottlenecks by distributing I/O across enough disks. For instance, we may separate group of entities that will contend for disk resources. We may think here about separating data and log files, data files in different filegroups or dedicated tempdb to its own storage path as well.
  • Maximizing database reliability in case of disk failure

But you may wonder why to scale the existing SQL Server best practices to the OFA standard?

First of all, let’s say that existing Microsoft best practices, in many respects, resembles to OFA. Then providing a uniform approach to customers (Oracle, MySQL, PostgreSQL, MongoDB and SQL Server) about best practices may help to contribute to a true immersion of SQL Server on Linux. From a customer perspective, we may provide guidelines on an already widely architecture standard known in Linux world. This is, at least, the way I see it.

So just to be clear, the next part of this blog is purely experimental and it doesn’t pretend to replace any future guideline that will be provided by Microsoft.

Before getting to the heart of the matter, let’s get the files associated to the mssql-server package. I continue to use the CentOS distribution for my demo but you may easy apply the same with other distributions.

[root@sql mssqlserver]# rpm -qpl mssql-server-14.0.405.200-1.x86_64.rpm
/opt/mssql/bin
/opt/mssql/bin/compress-dump.sh
/opt/mssql/bin/generate-core.sh
/opt/mssql/bin/mssql-conf
/opt/mssql/bin/paldumper
/opt/mssql/bin/sqlpackage
/opt/mssql/bin/sqlservr
/opt/mssql/lib
/opt/mssql/lib/libc++.so.1
/opt/mssql/lib/libc++abi.so.1
/opt/mssql/lib/libjemalloc.so.1
/opt/mssql/lib/libsqlvdi.so
/opt/mssql/lib/libunwind.so.8
/opt/mssql/lib/mssql-conf
/opt/mssql/lib/mssql-conf/checkinstall.sh
/opt/mssql/lib/mssql-conf/checkrunninginstance.sh
/opt/mssql/lib/mssql-conf/collations.txt
/opt/mssql/lib/mssql-conf/disableservice.sh
/opt/mssql/lib/mssql-conf/enableservice.sh
/opt/mssql/lib/mssql-conf/mssql-conf.py
/opt/mssql/lib/mssql-conf/mssqlconfhelper.py
/opt/mssql/lib/mssql-conf/mssqlsettings.py
/opt/mssql/lib/mssql-conf/mssqlsettingsmanager.py
/opt/mssql/lib/mssql-conf/sapassword.sh
/opt/mssql/lib/mssql-conf/set-collation.sh
/opt/mssql/lib/mssql-conf/startservice.sh
/opt/mssql/lib/mssql-conf/stopservice.sh
/opt/mssql/lib/sqlpackage.sfp
/opt/mssql/lib/sqlservr.sfp
/opt/mssql/lib/system.certificates.sfp
/opt/mssql/lib/system.common.sfp
/opt/mssql/lib/system.netfx.sfp
/opt/mssql/lib/system.sfp
/usr/lib/systemd/system/mssql-server.service
/usr/share/doc/mssql-server/LICENSE.TXT
/usr/share/doc/mssql-server/THIRDPARTYNOTICES.TXT
/usr/share/man/man1/mssql-conf.1.gz
/usr/share/man/man1/sqlpackage.1.gz
/usr/share/man/man1/sqlservr.1.gz

From the above output we may find out a bunch of files that are part of the proper functioning of SQL Server. For example, we may notice different script files (either bash and python scripts files), libraries and sfp files (which are part of the new drawbridge architecture?), man page files and finally the sqlservr binary.

Most of these files are installed in the main hierarchy /opt/mssql/ with the following specific items

  • /opt/mssql/bin/ (binary files and SQL Server crash dump generation scripts)
  • /opt/mssql/lib (sqlserver libraries and sfp files)
  • /opt/mssql/lib/mssql-conf (python and bash scripts)

In addition, installing a SQL Server instance reveals an additional hierarchy /var/opt/mssql with the followings items :

  • data folder = default folder for database data and transaction log files. By the way, system and tempdb database files are located here by default. At the moment of writing this blog, SQL Server is in CTP 1.4 and moving system databases are not supported but we may expect to see this limit removed in the future
  • log folder = log files are stored here. We may retrieve logs related to SQL Server engine (errorlog files), to the SQL Server agent (SQLAGENT.OUT), to the default SQL Server trace and HK engine extended event.
  • secret folder = contains the machine.key file used by the SQL Server engine or potential other files that come with high-availability architectures to store pacemaker credentials for example.
  • conf file = Stores the default location files for user database files and dump files as well.

The last one – mssql.conf – has drawn my attention. According to my tests, it is possible to modify this file manually without getting sides effects but maybe to take with a pinch of salt at this stage.

So here my flexible architecture version for SQL Server:

  • Binary structure
mkdir -p /u00/app/sqlserver
mkdir -p /u00/app/sqlserver/product/14/mssqlserver
mkdir -p /u00/app/sqlserver/local/dmk
mkdir -p /u00/app/sqlserver/local/mssqltools
mkdir -p /u00/app/sqlserver/admin/mssqlserver/etc
mkdir -p /u00/app/sqlserver/admin/mssqlserver/log
mkdir -p /u00/app/sqlserver/admin/mssqlserver/dump

ln -s /opt/mssql/bin /u00/app/sqlserver/product/14/mssqlserver
ln -s /var/opt/mssql/mssql.conf /u00/app/sqlserver/admin/mssqlserver/etc/
ln -s /var/opt/mssql/log/ /u00/app/sqlserver/admin/mssqlserver/log 

Products file consists of SQL Server software that are supplied on the media / package provided by Microsoft.

Administrative files are files containing data about the instance, including SQL Server and SQL Agent error log files, default traces or HK engine extended event files server process diagnostic and finally instance parameter files like mssql.conf file.

  • Database structure
mkdir -p /u01/sqlserverdata/mssqlserver
mkdir -p /u02/sqlserverlog/mssqlserver
mkdir -p /u03/sqlservertempdb/mssqlserver
mkdir -p /u98/sqlserver/backup/mssqlserver

Then let’s configure the correct permissions on the new hierarchy folders

chown -R mssql:mssql /u01/sqlserverdata/mssqlserver
chmod -R 750 /u01/sqlserverdata/mssqlserver
chown -R mssql:mssql /u02/sqlserverlog/mssqlserver
chmod -R 750 /u02/sqlserverlog/mssqlserver
chown -R mssql:mssql /u03/sqlservertempdb/mssqlserver
chmod -R 750 /u03/sqlservertempdb/mssqlserver
chown -R mssql:mssql /u98/sqlserver/backup/mssqlserver
chmod -R 750 /u98/sqlserver/backup/mssqlserver

After configuring the permissions let’s change default path parameters from the mssql-config utility

/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /u01/sqlserverdata/mssqlserver 
/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /u02/sqlserverlog/mssqlserver 
/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /u98/sqlserver/backup/mssqlserver
/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /u00/app/sqlserver/admin/mssqlserver/dump

And let’s move tempdb database files by connecting to SQL Server (moving tempdb is supported since the CTP 1.4).

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @path_name NVARCHAR(MAX) = N'/u03/sqlservertempdb/mssqlserver/';

USE tempdb;


SELECT 
	@sql += N'ALTER DATABASE [tempdb] MODIFY FILE (NAME = ' + name + ', FILENAME = ''' 
	        + @path_name + REVERSE(SUBSTRING(REVERSE(REPLACE(physical_name, 'C:\', '')), 1 , CHARINDEX('\', REVERSE(REPLACE(physical_name, 'C:\', ''))) - 1)) + ''
			+ ''')' + CHAR(13)
FROM sys.database_files;


--PRINT @sql

EXEC sp_executesql @sql;
GO

And finally let’s restart the SQL Server instance:

systemctl restart mssql-server

Moreover, we may also add to the system path the binary structure in place and then change it when a new product is installed

echo 'PATH=”$PATH:/u00/app/sqlserver/product/14/mssqlserver/bin”' >> ~/.bashrc
source ~/.bashrc

Invoking mssql-conf is pretty easy in this case as shown below:

[root@sql home]# mssql-conf [options]

Finally let’s have a look at the database files placement :

SELECT 
	DB_NAME(database_id) AS database_name,
	name AS logical_name,
	physical_name
FROM sys.master_files
ORDER BY database_id

blog 120 - SQL Server and Flexible architecture

Drive letters and Windows fashion paths are still present for system databases but no doubt this issue will disappear in a next release.

Bottom line

Keep in mind that OFA architecture is very flexible and provides only guidelines and best practices. Therefore, we will be able to make adjustments over time as the version evolves. Please feel free to comment. It always be appreciated!

See you

 

 

Cet article SQL Server on Linux and experimental Flexible Architecture est apparu en premier sur Blog dbi services.

GoldenGate 12.2: ERROR: Unable to create a new wallet

Thu, 2017-03-30 10:42

Today I had a very strange issue when setting up a brand new GoldenGate 12.2 installation. Installation went fine, patch went fine (PSU 170221) and then I started to configure GoldenGate. One of the first commands we usually do is:

GGSCI (xxxxx) 2> create wallet

I really never had any issues with this, but today I got:

GGSCI (psrdbap03n1) 2> create wallet

ERROR: Unable to create a new wallet.

What a great error message and it was not much more verbose in the GoldenGate logfile:

2017-03-30 15:20:06  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  ERROR: Unable to create a new wallet.

So, what to do? My first thought was:

oracle@xxx]~[xxx]> ps -ef | grep ggscs
oracle    7683 30606  0 15:29 pts/2    00:00:00 grep ggscs
[oracle@xxx]~[xxx]> ps -ef | grep ggs
oracle    1261 24160  0 15:19 pts/0    00:00:00 ./ggsci
oracle    7698 30606  0 15:29 pts/2    00:00:00 grep ggs
[oracle@xxx]~[xxx]> strace -fp 1261

… and then do the command again and check the strace output. But there was not much to look at. GoldenGate tried to stat the wallet files but as they did not exist yet failed to, which is fine:

[pid  1261] stat("dirwlt", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
[pid  1261] stat("dirwlt/cwallet.sso", 0x7fff88bd2f30) = -1 ENOENT (No such file or directory)
[pid  1261] stat("dirwlt/cwallet.lck", 0x7fff88bd28f0) = -1 ENOENT (No such file or directory)
[pid  1261] open("dirwlt/cwallet.lck", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 6
[pid  1261] close(6)                    = 0
[pid  1261] open("dirwlt/cwallet.lck", O_RDWR) = 6

Some lines later:

[pid  1262] futex(0x1e68a64, FUTEX_WAIT_PRIVATE, 123, {0, 982142} 
[pid  1261] write(1, "ERROR: Unable to create a new wa"..., 37) = 37
[pid  1261] write(1, "\n", 1)    

And then many, many of these:

[pid  1262] futex(0x1e68a64, FUTEX_WAIT_PRIVATE, 163, {0, 63977012}) = -1 ETIMEDOUT (Connection timed out)
[pid  1262] futex(0x1e68a38, FUTEX_WAKE_PRIVATE, 1) = 0

In short, I had no clue what was the issue (the many futex calls did not help me) . The next idea was to enable debugging for Goldengate. This can be done by creating a file like this in the GoldenGate Home (a picture again, sorry, the xml tags destroy the formatting. Let me know when you want to have the file):
ogg-debug

This will create a file called “gglog-ggsci.log” in the GoldenGate Home which contains more information what was going on. But the only two lines which showed “ERROR” were these:

2017-03-30 16:09:31.015 ERROR|ggsec.CWallet                 |main| 1090 ggsec/CWallet.cpp        | initNZCtx: Error initializing the nz context. NZERROR 43490
2017-03-30 16:09:31.015 ERROR|ggsec.CWallet                 |main|  439 ggsec/CWallet.cpp        | create: Could not initialize network security context.

Hm, this does not help me either (mos did not come up with a single note about this error). To make it short: Finally this happened because I had the ORACLE_HOME environment set to the Goldengate Home:

[oracle@xxxxxxx]/u01/app/ogg/ch_src/product/12.2.0.1.170221/via[p1viach1]> echo $ORACLE_HOME
/u01/app/ogg/ch_src/product/12.2.0.1.170221/via

As soon as I changed this to a database home everything was fine again:

[oracle@xxx]/u01/app/ogg/ch_src/product/12.2.0.1.170221/via[p1viach1]> export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1/
[oracle@xxx]/u01/app/ogg/ch_src/product/12.2.0.1.170221/via[p1viach1]> ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (xxx) 1> create wallet

Created wallet at location 'dirwlt'.

Opened wallet at location 'dirwlt'.

GGSCI (xxx) 2> 

When you ask yourself why I had the ORACLE_HOME pointed to the GoldenGate Home: I applied the latest PSU just before and before invoking opatch I did set the ORACLE_HOME to point to the GoldenGate Home. When you do the same in GoldenGate 12.1 and even in the GoldenGate 12.2 base release you will not hit this. Currently there is bug open with Oracle, lets see what happens :)

 

Cet article GoldenGate 12.2: ERROR: Unable to create a new wallet est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 11 – NLS_DATE_FORMAT

Thu, 2017-03-30 00:36

Following the last post about time zones this post is about the display and default format of date and time values. What people usually do to get a consistent display of date and time values in Oracle is to set NLS_DATE_FORMAT:

SQL> show parameter nls_date_format;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
nls_date_format 		     string	 YYYY-MON-DD HH24:MI:SS

SQL> alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
29.03.2017 16:26:11

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
2017-MAR-29 16:31:01

Obviously PostgreSQL does not know anything about NLS_DATE_FORMAT, so what does it provide for doing the same?

First of all (and this is true for Oracle as well as for PostgreSQL) this is all about displaying date and time values and interpreting strings that shall be converted to date and time values. This has nothing to do with the internal storage.

What controls the display of date and time specific values in PostgreSQL is the parameter datestyle, the default is ISO:

postgres=# show datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

MDY here represents: MONTH-DAY-YEAR. If you want to change this on the session level you can:

postgres=# set datestyle='SQL, MDY';
SET
postgres=# select now();
              now               
--------------------------------
 03/29/2017 18:13:15.475693 GMT
(1 row)

You can do the same by setting the PGDATESTYLE environment variable before starting a new connection:

postgres@pgbox:/home/postgres/ [PG962] export PGDATESTYLE='SQL, MDY'
postgres@pgbox:/home/postgres/ [PG962] psql postgres
psql (9.6.2 dbi services build)
Type "help" for help.

postgres=# select now();
              now               
--------------------------------
 03/29/2017 18:15:29.765396 GMT
(1 row)

When you have a string and you want to insert that string into a column which is defined as date, how can you do this? PostgreSQL is very flexible here:

postgres=# create table t1 ( a date );
CREATE TABLE
postgres=# insert into t1 (a) values ('2017.01.01');
INSERT 0 1
postgres=# insert into t1 (a) values ('2017-01-01');
INSERT 0 1
postgres=# insert into t1 (a) values ('2017-JAN-01');
INSERT 0 1
postgres=# insert into t1 (a) values ('2017-JAN-01 11:11:11');
INSERT 0 1
postgres=# insert into t1 (a) values ('2017/01/01 11:11:11');
INSERT 0 1
postgres=# insert into t1 (a) values ('20170101 111111');
INSERT 0 1

All these work without specifying a format mask as you would need to do it in Oracle. The rules how a string is interpreted are documented here.

When you want to convert a string into a date or timestamp you can use one of the make_* functions:

postgres=# select make_date(2017,01,01);
 make_date  
------------
 2017-01-01
(1 row)
postgres=# select make_timestamp (2017,01,01,00,00,00);
   make_timestamp    
---------------------
 2017-01-01 00:00:00
(1 row)

… or use the data type formatting functions:

postgres=# select to_date('2017.01.01','YYYY.MM.DD');
  to_date   
------------
 2017-01-01
(1 row)

postgres=# select to_date('2017-JAN-01','YYYY.Mon.DD');
  to_date   
------------
 2017-01-01
(1 row)

Hope this helps …

 

Cet article Can I do it with PostgreSQL? – 11 – NLS_DATE_FORMAT est apparu en premier sur Blog dbi services.

OUD – Oracle Unified Directory 11.1.2.3 Backups Tips and Tricks

Wed, 2017-03-29 09:51

Backing up an OUD consists of several components

  • The OUD software itself
  • The OUD back end data
  • The OUD configuration
  • The OUD logical export as a ldif file

However, in this post I would like to take a closer look at the back end data. Unlike the old OID, the OUD directory server uses the Berkeley DB Java Edition (JE) as its primary back end. The OUD backup command allows you to backup all back ends in one shot, or a single back end, you can do full or incremental backups, you can compress it and you can even encrypt your back end data, if you like too.

One of the first questions that comes up is where to put the backup files. In a replicated environment, it makes a lot of sense to put them on a NFS share. In case you should loose one OUD host, you still have the access to backups on the other host.

I choose to backup the back end data to /u99/backup/OUD, which is a directory on a NFSv4 mount.

[dbafmw@dbidg01 OUD]$ mount | grep u99
dbidg03:/u99 on /u99 type nfs4 (rw,relatime,vers=4.1,rsize=32768,wsize=32768,namlen=255,hard,proto=tcp,port=0,timeo=600,retrans=2,sec=sys,clientaddr=192.168.56.201,local_lock=none,addr=192.168.56.203)

Before we issue the first backup command, it is good to know which back ends we have. Some back ends change quite frequently and others might not. I am using the OUD only for TNS name resolution, so, the most important for me is the OracleContext0.

[dbafmw@dbidg01 ~]$ list-backends
Backend ID        : Base DN
------------------:----------------------------------------
Eus0              :
EusContext0       :
Fa0               :
OIDCompatibility  : cn=OracleContext,cn=OracleSchemaVersion
OracleContext0    : "cn=OracleContext,dc=dbi,dc=com"
adminRoot         : cn=admin data
ads-truststore    : cn=ads-truststore
backup            : cn=backups
monitor           : cn=monitor
schema            : cn=schema
subschemasubentry :
tasks             : cn=tasks
userRoot          : "dc=dbi,dc=com"
virtualAcis       : cn=virtual acis


[dbafmw@dbidg01 ~]$ list-backends -n OracleContext0
Backend ID     : Base DN
---------------:---------------------------------
OracleContext0 : "cn=OracleContext,dc=dbi,dc=com"

Ok. Lets start a full backup of all back ends to the backup directory /u99/backup/OUDand and compress them.

[dbafmw@dbidg01 ~]$ backup --backUpAll --compress --backupDirectory=/u99/backup/OUD
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend virtualAcis
[29/Mar/2017:08:55:49 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend OracleContext0
[29/Mar/2017:08:55:49 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend tasks
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend schema
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend OIDCompatibility
[29/Mar/2017:08:55:49 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend userRoot
[29/Mar/2017:08:55:49 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend replicationChanges
[29/Mar/2017:08:55:49 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:55:49 +0200] category=TOOLS severity=NOTICE msgID=10944795 msg=The backup process completed successfully

For backing up your OUD server back ends, the OUD itself does not have to be up and running. You can back it up while it is offline too.

[dbafmw@dbidg01 ~]$ stop-ds
Stopping Server...

[29/Mar/2017:08:57:46 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=OIDCompatibility,cn=Workflow Elements,cn=config is now taken offline
[29/Mar/2017:08:57:46 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=OracleContext0,cn=Workflow elements,cn=config is now taken offline
[29/Mar/2017:08:57:46 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=userRoot,cn=Workflow Elements,cn=config is now taken offline
[29/Mar/2017:08:57:46 +0200] category=BACKEND severity=NOTICE msgID=9896306 msg=The backend cn=virtualAcis,cn=Workflow Elements,cn=config is now taken offline
[29/Mar/2017:08:57:46 +0200] category=CORE severity=NOTICE msgID=458955 msg=The Directory Server is now stopped


[dbafmw@dbidg01 ~]$ backup --backUpAll --compress --backupDirectory=/u99/backup/OUD
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend virtualAcis
[29/Mar/2017:08:58:06 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend OracleContext0
[29/Mar/2017:08:58:06 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend tasks
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend schema
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend OIDCompatibility
[29/Mar/2017:08:58:06 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend userRoot
[29/Mar/2017:08:58:06 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend replicationChanges
[29/Mar/2017:08:58:06 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:08:58:06 +0200] category=TOOLS severity=NOTICE msgID=10944795 msg=The backup process completed successfully
[dbafmw@dbidg01 ~]$

Backing up a single back end is done by the following command.

[dbafmw@dbidg01 ~]$ backup --backendID OracleContext0 --compress --backupDirectory=/u99/backup/OUD
[29/Mar/2017:15:14:22 +0200] category=TOOLS severity=NOTICE msgID=10944792 msg=Starting backup for backend OracleContext0
[29/Mar/2017:15:14:22 +0200] category=JEB severity=NOTICE msgID=8847446 msg=Archived: 00000000.jdb
[29/Mar/2017:15:14:22 +0200] category=TOOLS severity=NOTICE msgID=10944795 msg=The backup process completed successfully

The backup which I have done is reflected now in the following directory.

[dbafmw@dbidg01 OUD]$ ls -rtl /u99/backup/OUD/backup-OracleContext0*
-rw-r--r-- 1 dbafmw oinstall 19193 Mar 28 15:11 /u99/backup/OUD/backup-OracleContext0-20170328131137Z
-rw-r--r-- 1 dbafmw oinstall 56904 Mar 28 15:20 /u99/backup/OUD/backup-OracleContext0-20170328132004Z
-rw-r--r-- 1 dbafmw oinstall 27357 Mar 29 15:14 /u99/backup/OUD/backup-OracleContext0-20170329131419Z
-rw-r--r-- 1 dbafmw oinstall 27357 Mar 29 15:15 /u99/backup/OUD/backup-OracleContext0-20170329131552Z
-rw-r--r-- 1 dbafmw oinstall 84556 Mar 29 15:16 /u99/backup/OUD/backup-OracleContext0-20170329131622Z

The backups done via the OUD backup command are documented in a file called backup.info. If you grep for the last backup piece, you will find it there as the last entry.

[dbafmw@dbidg01 OUD]$ cat /u99/backup/OUD/backup.info | grep -B 8 backup-OracleContext0-20170329131622Z

backup_id=20170329131622Z
backup_date=20170329131625Z
incremental=false
compressed=false
encrypted=false
property.last_logfile_name=00000000.jdb
property.last_logfile_size=84330
property.archive_file=backup-OracleContext0-20170329131622Z

There is another method to find the last backup which was done. Before the backup command starts, it copies the current backup.info to backup.info.save, and so, you just need to do a diff, and then you know which is the latest backup.

[dbafmw@dbidg01 OUD]$ diff backup.info backup.info.save
48,56d47
< backup_id=20170329131622Z
< backup_date=20170329131625Z
< incremental=false
< compressed=false
< encrypted=false
< property.last_logfile_name=00000000.jdb
< property.last_logfile_size=84330
< property.archive_file=backup-OracleContext0-20170329131622Z
<

But what happens if you don’t need some old backup anymore. e.g. the backup-OracleContext0-20170328131137Z.

[dbafmw@dbidg01 OUD]$ cat backup.info | grep backup-OracleContext0-20170328131137Z
property.archive_file=backup-OracleContext0-20170328131137Z

Unfortunately, there is no purge procedure delivered with OUD to clean up old backups. You have to clean it up yourself. e.g. in case you want to clean up Oracle Context backups older than 2 days, you could do like this.

[dbafmw@dbidg01 OUD]$ find /u99/backup/OUD -maxdepth 1 -type f -name "backup-OracleContext0*" -mtime +2 | awk -F "/" '{ print $5 }' | awk -F "-" '{ print $3 }'
20170328132004Z

[dbafmw@dbidg01 OUD]$ find /u99/backup/OUD -maxdepth 1 -type f -name "backup-OracleContext0*" -mtime +2 | awk -F "/" '{ print $5 }' | awk -F "-" '{ print $3 }' | while read i
do
echo /u99/backup/OUD/backup-OracleContext0-${i}
rm /u99/backup/OUD/backup-OracleContext0-${i}
sed -i "/backup_id=${i}/,/property.archive_file=backup-OracleContext0-${i}/d" /u99/backup/OUD/backup.info
done
[dbafmw@dbidg01 OUD]$ cat backup.info | grep 20170328132004Z
[dbafmw@dbidg01 OUD]$

This script is of course not baby save, but you got the idea. ;-)

Conclusion

The Oracle OUD delivers quite a lot good options regarding backups. However, regarding purging the old stuff, you have to handle it yourself.

 

 

Cet article OUD – Oracle Unified Directory 11.1.2.3 Backups Tips and Tricks est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 10 – Timezones

Wed, 2017-03-29 08:59

This post is inspired by a question we received from a customer: In Oracle there is the sessiontimezone which returns the time zone of the session. Asking for the time zone of the session in Oracle returns you the offset to the UTC time:

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+02:00

This is fine as I am based in Switzerland and we skipped one hour in the night from last Saturday to Sunday :)

How can we do something similar in PostgreSQL? To check the current time zone of your session:

(postgres@[local]:4445) [postgres] > show timezone;
   TimeZone   
--------------
 Europe/Vaduz
(1 row)

Or:

(postgres@[local]:4445) [postgres] > select current_setting('timezone');
 current_setting 
-----------------
 Europe/Vaduz
(1 row)

So, PostgreSQL will not show you the offset to UTC but the name of the time zone as specified by the Internet Assigned Numbers Authority (IANA). When you want to have the offset to UTC you can do something like this:

(postgres@[local]:4445) [postgres] > select age(now(),now() at time zone 'UTC');
   age    
----------
 02:00:00
(1 row)

You can do it using the extract function as well:

(postgres@[local]:4445) [postgres] > select extract( timezone from now() ) / 60 /60;
 ?column? 
----------
        2
(1 row)

How can you change the session time zone? One way is to set the PGTZ environment variable before starting a new session when you use a libpq client:

postgres@centos7:/home/postgres/ [PG3] export PGTZ=Europe/Berlin
postgres@centos7:/home/postgres/ [PG3] psql postgres
psql.bin (9.6.2.7)
Type "help" for help.

(postgres@[local]:4445) [postgres] > show timezone;
   TimeZone    
---------------
 Europe/Berlin
(1 row)

The other way is to directly set it in the session:

Time: 1.048 ms
(postgres@[local]:4445) [postgres] > set time zone 'Europe/Paris';
SET
Time: 82.903 ms
(postgres@[local]:4445) [postgres] > show timezone;
   TimeZone   
--------------
 Europe/Paris
(1 row)

Of course you can also set the timezone parameter in postgresql.conf.

To get the current timestamp you can use:

(postgres@[local]:4445) [postgres] > SELECT current_timestamp;
        current_timestamp         
----------------------------------
 29-MAR-17 15:41:59.203485 +02:00
(1 row)

And finally, to calculate the current time in another time zone you can do something like this:

(postgres@[local]:4445) [postgres] > SELECT current_time AT TIME ZONE 'Europe/Zurich', current_time AT TIME ZONE 'US/Samoa';
      timezone      |      timezone      
--------------------+--------------------
 15:43:05.634124+02 | 02:43:05.634124-11
(1 row)

All the time zone names can be found in pg_timezone_names:

(postgres@[local]:4445) [postgres] > select * from pg_timezone_names;
               name               | abbrev | utc_offset | is_dst 
----------------------------------+--------+------------+--------
 America/North_Dakota/Beulah      | CDT    | -05:00:00  | t
 America/North_Dakota/Center      | CDT    | -05:00:00  | t
 America/North_Dakota/New_Salem   | CDT    | -05:00:00  | t
 America/Argentina/Ushuaia        | ART    | -03:00:00  | f
...

Hope this helps…

 

Cet article Can I do it with PostgreSQL? – 10 – Timezones est apparu en premier sur Blog dbi services.

Vertically scale your PostgreSQL infrastructure with pgpool – 3 – Adding another standby

Tue, 2017-03-28 08:52

In the first and second post in this series we did the basic pgpool setup including the watchdog configuration and then did a simple setup for automatically failover from a PostgreSQL master instance in case it goes down for any reason. In addition we told pgpool how an old master instance can be reconfigured as a new standby instance that follows the new master. In this post we’ll add another standby instance and then teach pgpool how a standby can be made aware of a new master when the master fails. Sound interesting? Lets go…

As reminder this is how the system looks like right now:

pgpool-architecture

What we want to have is:

pgpool-extended

The idea behind a third node is that we always will have at least one standby server up and running in case the master node goes down. What do we need to do to bring in another instance? Once the operating system is up and running, PostgreSQL is installed it is actually quite easy. As a first step lets create the new standby database on the third node using exactly the same layout as on the other nodes:

postgres@pgpool3:/home/postgres/ [pg962] mkdir -p /u02/pgdata/PG1
postgres@pgpool3:/home/postgres/ [pg962] cd /u02/pgdata/PG1
postgres@pgpool3:/u02/pgdata/PG1/ [pg962] pg_basebackup -h 192.168.22.34 -x -D /u02/pgdata/PG1/
postgres@pgpool3:/u02/pgdata/PG1/ [pg962] echo "standby_mode = 'on'
primary_conninfo = 'host=pgpool1 user=postgres'
primary_slot_name = 'standby2'
recovery_target_timeline = 'latest'
trigger_file='/u02/pgdata/PG1/failover_trigger'" > recovery.conf
postgres@pgpool3:/u02/pgdata/PG1/ [pg962] psql -h pgpool1 -c "select * from pg_create_physical_replication_slot('standby2')" postgres
postgres@pgpool3:/u02/pgdata/PG1/ [PG1] pg_ctl -D /u02/pgdata/PG1/ start

Now we have one master instance with two standby instances attached. Lets configure the third instance into pool (the configuration change needs to be done on both pgpool nodes, of course). The lines we need to add to pgpool.conf are:

backend_hostname2 = '192.168.22.40'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/u02/pgdata/PG1'
backend_flag2 = 'ALLOW_TO_FAILOVER'

Reload pgpool (a stop is not necessary) and check the current status:

[postgres@centos7_pgpool_m1 etc]$ pgpool reload
[postgres@centos7_pgpool_m1 etc]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.22.34 | 5432 | up     | 0.333333  | primary | 0          | true              | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
(3 rows)

We have a second standby database which is used to load balance read requests. In case the master fails now what we want is that one of the standby instances gets promoted and the remaining standby instance should be reconfigured to follow the new master. What do we need to do?

As we are using replication slots in this setup we need a way to make the failover scripts independent of the name of the replication slot. The first script that we need to change is “promote.sh” on all the PostgreSQL nodes because currently there is a hard coded request to create the replication slot:

#!/bin/bash
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
export PATH PGDATA
pg_ctl promote -D ${PGDATA} >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby1')" postgres >> /var/tmp/failover.log

The easiest way to do this is to create as many replication slots as you plan to add standby instances, e.g.:

#!/bin/bash
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
export PATH PGDATA
pg_ctl promote -D ${PGDATA} >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby1')" postgres >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby2')" postgres >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby3')" postgres >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby4')" postgres >> /var/tmp/failover.log

Of course this is not a good way to do it as you would need to adjust the script every time the amount of standby instances changes. One better way to do it is to centrally manage the amount of standby instances and the relation of the standby instances to the replication slots in a configuration in the $PGDATA directory of each PostgreSQL node and on each pgpool node in the HOME directory of the postgres user:

postgres@pgpool1:/u02/pgdata/PG1/ [PG1] cat pgpool_local.conf 
# the total amount of instances that
# participate in this configuration
INSTANCE_COUNT=3
# the mapping of the hostname to
# to the replication slot it uses for
# the PostgreSQL instance it is running
# in recovery mode
192.168.22.34=standby1
192.168.22.35=standby2
192.168.22.40=standby3

Having this we can adjust the promote.sh script (sorry I have to use a screenshot as the source code destroys the formatting of this post. let me know if you want to have the script):
pgp-promote-sh

Now the script will create all the replication slots on a promoted instance and in addition drops the replication slot it used before being promoted. What else do we need? As we now have a third instance in the configuration there is another point we need to take care of: When the master fails a new standby is promoted, so far so good. But in addition we want the second standby to follow the new master automatically, don’t we? For this we need to tell pgpool to call another script which is executed on the active pgpool node after failover:

[postgres@centos7_pgpool_m1 ~]$ grep follow /u01/app/postgres/product/pgpool-II/etc/pgpool.conf
follow_master_command = '/home/postgres/follow_new_master.sh "%h" "%H"' 

This will be executed when there is failover (all pgpool nodes need to have this script)

#!/bin/sh
set -x
master_node_host_name=$2
detached_node_host_name=$1
tmp=/tmp/mytemp$$
trap "rm -f $tmp" 0 1 2 3 15
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
RECOVERY_NODE_REPLICATION_SLOT=`cat /home/postgres/pgpool_local.conf | grep ${detached_node_host_name} | awk -F '=' '{print $2}'`
export PATH PGDATA
# make sure the instance is down
ssh -T $detached_node_host_name /home/postgres/stop_instance.sh
cat > $tmp <<EOF
standby_mode = 'on'
primary_conninfo = 'host=$master_node_host_name user=postgres'
primary_slot_name = '${RECOVERY_NODE_REPLICATION_SLOT}'
recovery_target_timeline = 'latest'
trigger_file='/u02/pgdata/PG1/failover_trigger'
EOF
scp $tmp $detached_node_host_name:$PGDATA/recovery.conf
ssh ${detached_node_host_name} /home/postgres/start_instance.sh
psql -c "select 'done'" postgres

So, this is the status now:

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_dela
y 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------
 0       | 192.168.22.34 | 5432 | up     | 0.333333  | primary | 4          | true              | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | standby | 2          | false             | 0
 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
(3 rows)

Lets shutdown the primary and see what happens:

postgres@pgpool1:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/PG1/ stop -m immediate
waiting for server to shut down.... done
server stopped

Pgpool is telling this:

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_dela
y 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------
--
 0       | 192.168.22.34 | 5432 | down   | 0.333333  | standby | 4          | false             | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | primary | 4          | true              | 0
 2       | 192.168.22.40 | 5432 | down   | 0.333333  | standby | 0          | false             | 0
(3 rows)

Re-attach:

[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -w -n 0
pcp_attach_node -- Command Successful
[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -w -n 2
pcp_attach_node -- Command Successful
[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_dela
y 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------
--
 0       | 192.168.22.34 | 5432 | up     | 0.333333  | standby | 4          | false             | 0
 1       | 192.168.22.35 | 5432 | up     | 0.333333  | primary | 4          | true              | 0
 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0
(3 rows)

Perfect. The only pain point is that we need to manually re-attach the nodes, everything else is automated. But, luckily there is way to get around this: As we are on the pgpool nodes when the script is executed we can just use pcp_attach_node at the end of the follow_new_master.sh script (and pass the node id %d into the script):

[postgres@centos7_pgpool_m1 ~]$ grep follow /u01/app/postgres/product/pgpool-II/etc/pgpool.conf
follow_master_command = '/home/postgres/follow_new_master.sh "%h" "%H" %d' 
[postgres@centos7_pgpool_m1 ~]$ cat follow_new_master.sh 
#!/bin/sh
set -x
master_node_host_name=$2
detached_node_host_name=$1
detached_node_id=$3
tmp=/tmp/mytemp$$
trap "rm -f $tmp" 0 1 2 3 15
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
RECOVERY_NODE_REPLICATION_SLOT=`cat /home/postgres/pgpool_local.conf | grep ${detached_node_host_name} | awk -F '=' '{print $2}'`
export PATH PGDATA
# make sure the old master is down
ssh -T $detached_node_host_name /home/postgres/stop_instance.sh
cat > $tmp <<EOF
standby_mode = 'on'
primary_conninfo = 'host=$master_node_host_name user=postgres'
primary_slot_name = '${RECOVERY_NODE_REPLICATION_SLOT}'
recovery_target_timeline = 'latest'
trigger_file='/u02/pgdata/PG1/failover_trigger'
EOF
scp $tmp $detached_node_host_name:$PGDATA/recovery.conf
ssh ${detached_node_host_name} /home/postgres/start_instance.sh
psql -c "select 'done'" postgres
pcp_attach_node -w -n ${detached_node_id}

And now, when you shutdown the master everything is automatic. Hope this helps.

 

Cet article Vertically scale your PostgreSQL infrastructure with pgpool – 3 – Adding another standby est apparu en premier sur Blog dbi services.

JAN17 Proactive Bundle Patch + Adaptive Statistics control

Mon, 2017-03-27 02:30

If you have to create a new database now (I’m writing this in March 2017) for a migration of OLTP database with minimal risks of regression, I would recommend:

  • The latest patchset of Oracle Database 12cR1
  • The latest Proactive Bundle Patch
  • The two patches to get full control over Adaptive statistics

This post gives more detail about it and which patches to apply. It would be nice to have those patches merged into each bundle patch, but it is not.

dbi services 12cR2 free event

This Month, the 12cR2 was released and we immediately informed our customers about the new features that we think are important to know in order to plan when to go to 12.2 and for which projects. We started with the most important one, the Adaptive Statistics which helps to avoid all those performance issues encountered after migrating to 12cR1. We also mentioned that this new behavior has been backported to 12.2 with two patches, as explained here by Clemens Bleile:
IMG_3833

12cR1

This event was to inform about 12.2 but lot of customers waited for this to plan their 12.1 migration. Knowing the roadmap and the new features helps to plan what can wait for a stable 12cR2 (after few PSUs) and what must be migrated now to 12cR1. This is why we did everything to rollout this event as soon as possible once the main platform (Linux and Windows) were publicly available.

What to install for 12.1.0.2

Our recommendation for new installations of 12c for current migration with minimal risks of regression is

  • The latest patchset of Oracle Database 12cR1: 12.1.0.2
  • The latest Proactive Bundle Patch: JAN2017 BP
  • The two patches to get full control over Adaptive statistics: patches 22652097 and 21171382

Nothing is easy with patching, so I’ll detail how to find exactly what to install.

Latest patchset of Oracle Database 12cR1: 12.1.0.2

Now that the latest version is 12.2.0.1 you cannot find anymore the 12.1.0.2 on the oracle.com download page.
You can download 12.1.0.2 from the Patchset 21419221: https://updates.oracle.com/download/21419221.html

Latest Proactive Bundle Patch: JAN2017 BP

Finding the latest patch set update is easy if you follow the MOS Note Quick Reference to Patch Numbers for Database/GI PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)

You will download https://updates.oracle.com/download/24968615.html for Linux or https://updates.oracle.com/download/25115951.html for Windows.

Adaptive statistics: patches 22652097 and 21171382

Then you have to find the two patches to apply them on top of the JAN17 ProactiveBP.

The first one is for separate parameters to enable adaptive plans and adaptive statistics separately: https://updates.oracle.com/download/22652097.html and you can find it for Oracle 12.1.0.2.170117 Proactive BP

The second one is for dbms_stats preference to control statistics extensions creation and you will have two suprises when following the link:
The name is AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY because the change has been merged with some other changes on concurrent statistics
It is available only for (listed in the non-chronological order of the platform list on MOS):

  • Oracle 12.1.0.2.13 Proactive BP
  • Oracle 12.1.0.2.0
  • Oracle 12.1.0.2.5
  • Oracle 12.1.0.2.160119 Proactive BP
  • Oracle 12.1.0.2.7 Proactive BP

If you want to go further, you have to open a SR, provide an opatch lsinventory (because it seems that Oracle Support Engineers are not able to get the lsinventory for the latest Proactive BP – the recommended one). And this is where the nightmare starts. The lastest we have here is for JAN16 Proactive Bundle Patch – 12.1.0.2.160119 Proactive BP.

I can’t wait for a relevant answer from MOS support engineers, so I got to look at the patch. It is very simple change actually.In DBMS_STATS it has to check whether AUTO_STAT_EXTENSIONS is ON before creating the column group. This is all in prvtstat.plb and if we are lucky there were no changes on it since the JAN16.

Before trying it, we can check conflicts in MOS. Here are the 3 patches I would like to apply, in their most recent release for Linux:

CaptureOpatchConflict

Yes, this is a nice feature of My Oracle Support: you can analyze the conflicts online.

The result of conflict analysis shows that we are lucky:
CaptureOpatchConflict003

Ready now to download the files:
CaptureOpatchConflict004

So the last patch we need, https://updates.oracle.com/download/21171382.html, can be downloaded in its latest Proactive BP version, even if it is 1 year old. And don’t worry about its name: p21171382_12102160119DBEngSysandDBIM_Generic.zip

datapatch and verification

Don’t forget to run datapatch on your databases to be sure that the dictionary is patched.

$ORACLE_HOME/OPatch/datapatch
 
SQL Patching tool version 12.1.0.2.0 Production on Mon Mar 27 09:18:47 2017
Copyright (c) 2012, 2017, Oracle. All rights reserved.
 
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
Nothing to roll back
The following patches will be applied:
24732088 (DATABASE BUNDLE PATCH 12.1.0.2.170117)
21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)
22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES)
 
Installing patches...
Patch installation complete. Total patches installed: 3
 
Validating logfiles...done
SQL Patching tool complete on Mon Mar 27 09:21:33 2017

And then connect to check the new Adaptive Statistics behavior:

SQL> show parameter optimizer_adaptive
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_plans boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_adaptive_statistics boolean FALSE
 
SQL> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;
 
DBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')
--------------------------------------------------------------------------------
OFF

Summary for 12.1.0.2 new install

Don’t install a 12c database with only the software and documentation that was released 3 years ago. Lot of migration feedback has improved the stability of such migration, and this is implemented in patchset updates and MOS notes.
A good source of information is Mike Dietrich blog (the best source you can find to benefit from lot of migration projects experience):

Then, get the latest recommended software.

  1. The patchset: https://updates.oracle.com/download/21419221.html for your platform
  2. The patchset update https://updates.oracle.com/download/24968615.html for Unix/Linux
    or https://updates.oracle.com/download/25115951.html for Windows
  3. The Adaptive Statistics instance parameters: https://updates.oracle.com/download/22652097.html for Oracle 12.1.0.2.170117 Proactive BP
  4. The Adaptive Statistics stat preference https://updates.oracle.com/download/21171382.html for Oracle 12.1.0.2.160119 Proactive BP

Don’t forget to run datapatch on all databases, even the newly created ones.

 

Cet article JAN17 Proactive Bundle Patch + Adaptive Statistics control est apparu en premier sur Blog dbi services.

Vertically scale your PostgreSQL infrastructure with pgpool – 2 – Automatic failover and reconfiguration

Fri, 2017-03-24 10:18

In the first post of this little series we did the basic setup of pgpool and configured the watchdog. So, as of now, pgpool uses a VIP which is active on one of the pgpool nodes and failovers to the other node in case the node where the VIP currently runs on goes down for any reason. This provides the very same address for the application or clients to connect and eliminates the single point of failure that you have when there is only one pgpool instance running. What is still missing is a mechanism on how we can automatically failover to a promoted standby instance in case the PostgreSQL master goes down. We even want to be able to reconfigure the old master as new standby that follows the new master automatically. This is what this post is about …

Lets take a look at the picture again:

pgpool-architecture

What happens when we stop the standby instance? Currently both instances are in status “up” when you ask pgpool (notice that I connect to the 192.168.22.38 address which is the VIP that is managed by pgpool):

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 0          | true              | 0
 1       | 192.168.22.35 | 5432 | up     | 0.500000  | standby | 0          | false             | 0
(2 rows)

Lets create a new connection first to see what happens to existing connections:

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 postgres
psql (9.6.2 dbi services build)
Type "help" for help.

postgres= select now();
              now              
-------------------------------
 2017-03-24 10:53:16.077351+01
(1 row)


postgres=#

Shutdown the standby instance:

postgres@pgpool2:/home/postgres/ [PG1] psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 t
(1 row)

postgres@pgpool2:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/PG1/ stop -m fast
waiting for server to shut down.... done
server stopped

What happened to our connection?

postgres=# select now();
              now              
-------------------------------
 2017-03-24 10:54:46.802984+01
(1 row)

Nothing, we can just keep using it. What is pgpool telling us about our instances?

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 4          | true              | 0
 1       | 192.168.22.35 | 5432 | down   | 0.500000  | standby | 0          | false             | 0
(2 rows)

As expected the standby node is now down. This is the easy case, just start the standby again and you are fine:

postgres@pgpool2:/home/postgres/ [PG1] pg_ctl -D /u02/pgdata/PG1/ start -m fast
server starting
postgres@pgpool2:/home/postgres/ [PG1] psql -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 t
(1 row)

… and pgpool should detect this as well:

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 5          | true              | 0
 1       | 192.168.22.35 | 5432 | down   | 0.500000  | standby | 0          | false             | 0
(2 rows)

No? What we need to do is to attach the node again:

[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -n 1
Password: 
pcp_attach_node -- Command Successful
[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 5          | true              | 0
 1       | 192.168.22.35 | 5432 | up     | 0.500000  | standby | 0          | false             | 0

Now comes the not so easy part: What happens if the primary instance goes down? This is where the failover_command specified in pgpool.conf comes into the game:

[postgres@centos7_pgpool_m1 etc]$ cat pgpool.conf | grep failover_command
failover_command = '/home/postgres/failover.sh %d "%h" %p %D %m %M "%H" %P'

When pgpool detects that the master is gone the failover_command will be executed. This is a very simple failover script (located on the pgpool hosts). The parameters are passed into the script by pgpool automatically:

#!/bin/sh -x
# Execute command by failover.
# special values:  %d = node id
#                  %h = host name
#                  %p = port number
#                  %D = database cluster path
#                  %m = new master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %% = '%' character
failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8

if [ $failed_node_id = $old_primary_node_id ];then	# master failed
    ssh -T postgres@$new_master_host_name /home/postgres/promote.sh
fi 

The promote.sh script is very simple as well and must be available on all PostgreSQL nodes:

#!/bin/bash
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
export PATH PGDATA
pg_ctl promote -D ${PGDATA} >> /var/tmp/failover.log
psql -c "select * from pg_create_physical_replication_slot('standby1')" postgres >> /var/tmp/failover.log

So once we shutdown the master this gets triggered and the standby is promoted. How can we re-attach the old master as a new standby? The magic is in here:

[postgres@centos7_pgpool_m1 ~]$ grep recovery_1st_stage_command /u01/app/postgres/product/pgpool-II/etc/pgpool.conf
recovery_1st_stage_command = 'recover.sh'

In the pgpool.conf file a script is specified which is called when a recovery is triggered. This script needs to be in PGDATA, otherwise it will not work. It looks like this (on both nodes, of course):

postgres@pgpool1:/u02/pgdata/PG1/ [PG1] cat $PGDATA/recover.sh 
#!/bin/sh
set -x
master_node_host_name=`hostname`
master_db_cluster=$1
recovery_node_hostname=$2
recovery_db_cluster=$3
tmp=/tmp/mytemp$$
trap "rm -f $tmp" 0 1 2 3 15

PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"

export PATH PGDATA

# make sure the old master is down
ssh -T $recovery_node_hostname /home/postgres/stop_instance.sh
# rewind the old master
ssh -T ${recovery_node_hostname} /home/postgres/rewind.sh ${master_node_host_name}

cat > $tmp <<EOF
standby_mode = 'on'
primary_conninfo = 'host=$master_node_host_name user=postgres'
primary_slot_name = 'standby1'
recovery_target_timeline = 'latest'
trigger_file='/u02/pgdata/PG1/failover_trigger'
EOF

scp $tmp $recovery_node_host_name:$recovery_db_cluster/recovery.conf

ssh -T ${recovery_node_hostname} /home/postgres/start_instance.sh

The “set -x” on the top is quite important because this script is execute from inside PostgreSQL and you’ll see the output in the PostgreSQL logfile. The scripts executed within the recovery.sh script here are:

postgres@pgpool1:/home/postgres/ [PG1] cat stop_instance.sh 
#!/bin/bash
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
pg_ctl -D $PGDATA stop -m immediate 

postgres@pgpool1:/home/postgres/ [PG1] cat rewind.sh 
#!/bin/bash
master_node_host_name=$1
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
pg_rewind --target-pgdata=${PGDATA} --source-server="port=5432 user=postgres dbname=postgres host=${master_node_host_name}" >> /var/tmp/recover.log

postgres@pgpool1:/home/postgres/ [PG1] cat start_instance.sh 
#!/bin/bash
PGDATA="/u02/pgdata/PG1"
PATH="/u01/app/postgres/product/96/db_2/bin/:$PATH"
pg_ctl -w -s -D $PGDATA start 2>/dev/null 1>/dev/null < /dev/null &

Having this in place we can rebuild the old master with:

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | 192.168.22.34 | 5432 | down   | 0.500000  | standby | 0          | false             | 0
 1       | 192.168.22.35 | 5432 | up     | 0.500000  | primary | 0          | true              | 0

[postgres@centos7_pgpool_m1 ~]$ pcp_recovery_node -n 0 -w
pcp_recovery_node -- Command Successful

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c "show pool_nodes" postgres
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
 
---------+---------------+------+--------+-----------+---------+------------+-------------------+------------------
-
 0       | 192.168.22.34 | 5432 | up     | 0.500000  | standby | 0          | false             | 0
 1       | 192.168.22.35 | 5432 | up     | 0.500000  | primary | 0          | true              | 0

The output in the PostgreSQL logfile of the master looks then similar to this:

++ hostname
+ master_node_host_name=pgpool2
+ master_db_cluster=/u02/pgdata/PG1
+ recovery_node_hostname=192.168.22.34
+ recovery_db_cluster=/u02/pgdata/PG1
+ tmp=/tmp/mytemp10901
+ trap 'rm -f /tmp/mytemp10901' 0 1 2 3 15
+ PGDATA=/u02/pgdata/PG1
+ PATH=/u01/app/postgres/product/96/db_2/bin/:/u01/app/postgres/product/96/db_2bin:/u01/app/postgres/product/96/db_2/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/postgres/.local/bin:/home/postgres/bin:/sbin:/usr/edb-bart/bin:/usr/edb-efm/bin:/u01/app/postgres/product/pgpool-II/bin:/u01/app/postgres/local/dmk/bin
+ export PATH PGDATA
+ ssh -T 192.168.22.34 /home/postgres/stop_instance.sh
pg_ctl: PID file "/u02/pgdata/PG1/postmaster.pid" does not exist
Is server running?
+ ssh -T 192.168.22.34 /home/postgres/rewind.sh pgpool2
+ cat
+ scp /tmp/mytemp10901 pgpool1:/u02/pgdata/PG1/recovery.conf
+ ssh 192.168.22.34 /home/postgres/start_instance.sh
+ psql -c 'select '\''done'\''' postgres
 ?column? 
----------
 done
(1 row)

Time: 0.140 ms
+ rm -f /tmp/mytemp10901

I have to admit that I needed a small hack: At the end of the recovery pgpool tries to execute $PGDATA/pgpool_remote_start and I do not want to do it that way so I just created this file on both nodes:

postgres@pgpool1:/u02/pgdata/PG1/ [PG1] cat $PGDATA/pgpool_remote_start
exit 0

In the next post we’ll add a second standby system and then teach pgpool how a remaining standby can be told to follow a new master automatically. We’ll need adjust the scripts here to be more dynamic as well then.

 

Cet article Vertically scale your PostgreSQL infrastructure with pgpool – 2 – Automatic failover and reconfiguration est apparu en premier sur Blog dbi services.

SQL Server 2016: Does Dynamic Data Masking works with INSERT INTO and SELECT INTO commands?

Tue, 2017-03-21 02:55

I wonder how works Dynamic Data Masking (DDM) with these two commands INSERT INTO  and SELECT INTO.

First, I create a table and add some “sensitive data”:

USE [DDM_TEST]
GO

CREATE TABLE [dbo].[Confidential](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Name] [nvarchar](70)NULL,
[CreditCard] [nvarchar](16)NULL,
[Salary] [int] NULL,
[Email] [nvarchar](60)NULL)  


insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N'Stephane',N'3546748598467584',113459,N'sts@dbi-services.com')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N'David',N'3546746598450989',143576,'dab@dbi-services.com')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Nathan',N'3890098321457893',118900,'nac@dbi-services.com')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Olivier',N'3564890234785612',98000,'olt@dbi-services.com')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Alain',N'9897436900989342',85900,'ala@dbi-services.com')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Fabrice',N'908323468902134',102345,'fad@dbi-services.com')

select * from [dbo].[Confidential]

DDM_Into01

After, I create all masking rules and add a user:

Use DDM_TEST
ALTER Table Confidential
ALTER COLUMN NAME ADD MASKED WITH (FUNCTION='default()')
ALTER Table Confidential
ALTER COLUMN SALARY ADD MASKED WITH (FUNCTION='default()')
ALTER Table Confidential
ALTER COLUMN creditcard ADD MASKED WITH (FUNCTION='partial(1,"XXXX",2)')
ALTER Table Confidential
ALTER COLUMN email ADD MASKED WITH (FUNCTION='email()')

CREATE USER TestDemo WITHOUT LOGIN
GRANT SELECT ON Confidential TO TestDemo

-- Execute a select statement as TestDemo 
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential] 
REVERT

DDM_Into02

INSERT INTO

This command is used to copy a table.
What’s happens when I copy data from a table with masked columns to a table without mask?
First, I create a second table [dbo].[Confidential2] and give permissions SELECT and INSERT to the user “TestDemo”

USE [DDM_TEST]
GO

CREATE TABLE [dbo].[Confidential2](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Name] [nvarchar](70)NULL,
[CreditCard] [nvarchar](16)NULL,
[Salary] [int] NULL,
[Email] [nvarchar](60)NULL)  

GRANT SELECT ON Confidential2 TO TestDemo
GRANT INSERT ON Confidential2 TO TestDemo

I execute the query to insert data from [dbo].[Confidential] to [dbo].[Confidential2] with the INSERT INTO command:

USE [DDM_TEST]
GO
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential]
INSERT INTO [dbo].[Confidential2]([Name],[CreditCard],[Salary],[Email])
	SELECT [Name],[CreditCard],[Salary],[Email] FROM [dbo].[Confidential]
SELECT * FROM [dbo].[Confidential]
REVERT

DDM_Into03
As you can see data are also masked in the second table [dbo].[Confidential2].
But are they really?
I execute the query with the activation on the query plan.
DDM_Into04
As you can see the masking step is missing in the query plan from the select on [dbo].[Confidential2].
If I Select data from [dbo].[Confidential2] with my admin account, data are “masked data” and not real data…
DDM_Into05
Finally, the goal is reached, you cannot read sensitive data if you copy data from a table to another.
Keep in mind that the copied data are not masked for the user.
These data are copied as “masked data” and guarantee the anonymization and a good security for your sensitive data.
But on the other side, if you use the same WHERE clause in [dbo].[Confidential2], you don’t have the same result… :-(
DDM_Into05a

SELECT INTO

With this command, I test also the copy to a temporary table.
These two cases will be interesting…
I recreate the same table [dbo].[Confidential] with the same masking rules, the user with create table and alter any schema permissions to do the select into:

EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential] 
SELECT * INTO [dbo].[Confidential2] FROM [dbo].[Confidential] ;
REVERT

DDM_Into06
In the query plan, you can see that the masking is between the select and the insert.
We are in the same case as previously: copied data are “masked data”.
To see it, I read data from the table [dbo].[Confidential2] with my sysadmin login:
DDM_Into07
And the result is that all copied data are masked. The data remain anonymous.

Finally, let’s test it with a temporary table and let’s see what happens:

USE [DDM_TEST]
GO
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential] 
SELECT * INTO #Confidential2 FROM [dbo].[Confidential] ;
REVERT
EXECUTE AS USER='TestDemo'
SELECT * FROM #Confidential2 
REVERT

DDM_Into08

The same query plan is applied and masked data are copied and remained anonymous.

Finally, these two commands INSERT INTO and SELECT INTO keep your data anonymous in the case of a Table copy.

Sorry but cheat mode is disabled … :evil:

 

Cet article SQL Server 2016: Does Dynamic Data Masking works with INSERT INTO and SELECT INTO commands? est apparu en premier sur Blog dbi services.

Purging Unified Audit Trail in 12cR2

Sun, 2017-03-19 06:12

A good thing from 12.2 is that the implementation of Unified Audit Trail has changed a little. It was stored in a weird CLI_SWP$ table but now we have a normal partitioned table named AUD$UNIFIED. In a previous post I traced the two purge method: purge all before a timestamp, or purge all. Here is the same in 12.2

Purge old

I have quite a few record in Unified Audit Trail here.


SQL> select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name;
 
UNIFIED_AUDIT_POLICIES ACTION_NAME COUNT(*)
---------------------------------------- -------------------- ----------
EXECUTE 3
ORA_LOGON_FAILURES LOGON 408275

I set the timestamp to 6 hours before now
SQL> exec dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified
,last_archive_time=>sysdate-6/24);
PL/SQL procedure successfully completed.

And call the clean procedure:
SQL> exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified
,use_last_arch_timestamp=TRUE);
PL/SQL procedure successfully completed.

Here is what I see in the trace:

select sys.dbms_audit_mgmt.is_droppable_partition(:1, :2)
from
dual

This is interesting. The Unified Audit Trail is partitioned on timestamp and the purge procedure checks it the partition can be dropped instead of running a long delete statement.

Here is the documentation we have about it in ?/rdbms/admin/dbmsamgt.sql

-- is_droppable_partition - IS aud$unified table PARTITION DROPPABLE?
--
--
-- INPUT PARAMETERS
-- partname - aud$unified table's Partition Name
-- lat - Last Archive Timestamp mentioned during cleanup

In my case, I traced the bind variables and the is_droppable_partition procedure was run only once with partname=>'”SYS_P348″‘ and lat=>’03-MAR-17 03.07.56 PM’. The timestamp is the ‘last timestamp’ I’ve set, and I have only one partition here because my database was created recently.

As we can guess, this checks the high value of the partition:

select high_value
from
dba_tab_partitions where table_owner = 'AUDSYS' and table_name =
'AUD$UNIFIED' and partition_name = :1

Because I have only one partition, which is the current one, my ‘last timestamp’ is below the high_value so it is not possible to truncate this partition and keep the records from after the ‘last timestamp’.

Then a delete is run, which deletes all rows from before my last timestamp (bind variable :1 is ’03-MAR-17 03.07.56 PM’). Note that I don’t know (yet) why we can have DBID=0.

delete from audsys.aud$unified
where
event_timestamp < :1 and (dbid = :2 or dbid = 0)
 
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 10.68 31.25 16885 24367 437518 346517
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 10.68 31.25 16885 24367 437518 346517
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE AUD$UNIFIED (cr=12219 pr=16885 pw=0 time=31219023 us starts=1)
346517 346517 346517 PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=12148 pr=0 pw=0 time=1161311 us starts=1 cost=547 size=1231218 card=68401)
346517 346517 346517 TABLE ACCESS FULL AUD$UNIFIED PARTITION: 1 KEY (cr=12148 pr=0 pw=0 time=788043 us starts=1 cost=547 size=1231218 card=68401)
 
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 5 0.00 0.00
db file sequential read 16885 0.03 21.03

All my rows are deleted with conventional updates here. I had 400000 rows, deleted 340000 so 60000 remains.

Purge old with old partitions

I had only one partition here but AUDSYS.AUD$UNIFIED is partitioned by month. Here is what I can see in my alert.log about the creation of this partition:
TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P348 (33) VALUES LESS THAN (TIMESTAMP' 2017-04-01 00:00:00')

Actually, this is automatically partitioned by months. Here is an excerpt of the table’s DDL as displayed by dbms_metadata:
PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH)
(PARTITION "SYS_P348" VALUES LESS THAN (TIMESTAMP' 2017-04-01 00:00:00') SEGMENT CREATION IMMEDIATE

When running the same as before but on a database with few older partitions (because there were no scheduled purge) I can see that the ‘is_droppable_partition’ and the related query is run 4 times:

select high_value
from
dba_tab_partitions where table_owner = 'AUDSYS' and table_name =
'AUD$UNIFIED' and partition_name = :1
 
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 4 0.05 0.05 0 0 0 0
Fetch 4 0.00 0.00 0 143 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.05 0.06 0 143 0 4

and I see a drop partition for the 3 old partitions:

ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION AUD_UNIFIED_P0
ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P221
ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P781

Note that this is called by:
CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P781')
which runs it internally as an oracle script because this DDL is not allowed otherwise.

In summary, purging with a timestamp is optimized to run conventional deletes only on latest partition. Older partitions are dropped. If you schedule a job to regularly set the timestamp and then have the purge job doing the cleaning, then better to set a timestamp at the beginning of the month. If you have to purge a large audit trail, then better to wait the beginning of the next month.

Purge all

If you don’t need to keep recent records and want to truncate all, then just call the purge without timestamp.

Here I have about 60000 rows remaining from the previous test, all in the current partition.

SQL> select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name;
 
UNIFIED_AUDIT_POLICIES ACTION_NAME COUNT(*)
---------------------------------------- -------------------- ----------
EXECUTE 6
ORA_LOGON_FAILURES LOGON 62152

I call the clean

SQL> exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified
,use_last_arch_timestamp=>FALSE);
PL/SQL procedure successfully completed.

And I can see directly in the trace a truncate of the whole table:

TRUNCATE TABLE AUDSYS.AUD$UNIFIED
 
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.04 4.42 67 6 919 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.04 4.42 67 6 919 0
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
 
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 67 0.00 0.07
enq: RO - fast object reuse 5 0.94 1.76
local write wait 36 0.14 2.54

This is the fastest way to empty the Unified Audit Trail.

So what?

We don’t have long experience on 12.2 production yet, but from what I see here, this new implementation is a good thing. There were many problems with the 12.1 implementations that are probably solved by having a normal table with normal interval partitioning, purged with normal deletes and normal truncates.
Of course, the next question is what happens when you upgrade a 12.1 database with a huge audit trail? That’s for a future post.
And don’t forget that by default you are in mixed mode. More info here.

 

Cet article Purging Unified Audit Trail in 12cR2 est apparu en premier sur Blog dbi services.

How many members for standby redo logs?

Sat, 2017-03-18 11:43

I see lot of databases with two members for redo logs and also two members for standby redo logs. Why not, but when asking I realized that there are some mis-comprehension about it. And what was recommended 10 years ago may be different today.

Online and Stanbdy redo logs

Your transactions happen on the primary database and are written to the online redo logs before the modification is done on datafiles. And when you commit you wait to be sure that the redo is on persistence storage. If you loose the current redo log group, then your database crashes and you loose the latest transactions. This is why we multiplex the online redo logs. Even if you are 100% confident on your storage high availability the risk of human error dropping a file exists and is considerably lower if there a two files.
For additional protection, in case you loose all the primary redo members, Data Guard synchronizes the transaction to a second site by shipping the redo stream. There, on the standby site, the redo is written to the standby redo logs.

The online redo logs are used only on the primary site, and should better be named primary redo logs. You create them on the standby site only to be prepared for failover, when it will become the primary and opened read-write. But let’s be clear: online redo logs are not used when database is not online, and mount is not online.

The standby redo logs are not standby at all. They are actively used on the standby site and this is why thew are called ‘standby. On the primary, they are not used, just there to be ready when the primary becomes a standby after a failover.

Members

We have seen why we multiplex the online redo logs:

  • it protects the transactions because without multiplexing you loose transactions when loosing one group
  • it protects the instance availability because without multiplexing you crash the instance when loosing one group

But this is different with standby redo logs.

  • it is an additional protection. Transactions are still persistent on the primary even if you loose a standby log group.
  • the primary is still available even if one standby cannot be SYNC

Of course, if in Maximum Protection mode the availability of the primary is compromised when the standby cannot apply the redo in SYNC. But in this protection mode you probably have multiple standby and the loss of one standby redo log on one standby site it not a problem.

Redo transport and redo apply

I said that transactions are still persisted on the primary, but even without standby redo logs they are still shipped to standby site, but in ASYNC mode. This means that in order to loose transactions in case of the loss of a standby redo log group, you need to experience this file loss, and primary site failure and network failure at the same time. The probability for this is very low and having an additional member do not lower the risk.

Of course, I’ve tested what happens. I have two standby redo log members and I removed all of them one minute ago:

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Mar 17 14:47:45 2017
 
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "ORCLA"
Connected as SYSDBA.
 
Database - orclb
 
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 minute 30 seconds (computed 0 seconds ago)
Average Apply Rate: 0 Byte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
ORCLB
 
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
ORA-16857: member disconnected from redo source for longer than specified threshold
ORA-16826: apply service state is inconsistent with the DelayMins property
ORA-16789: standby redo logs configured incorrectly

As you can see, when there is no member remaining, the APPLY is stuck but transport still happens, in ASYNC to archived logs.
The standby alert log mentions the failure:
2017-03-17T14:51:21.209611+01:00
Errors in file /u01/app/oracle/diag/rdbms/orclb/ORCLB/trace/ORCLB_rsm0_6568.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/oradata/ORCLB/onlinelog/m5.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
ORA-00312: online log 5 thread 1: '/u01/oradata/ORCLB/onlinelog/o1_mf_5_dbvmxd52_.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2

and the SYNC mode cannot continue without standby redo logs:

03/17/2017 14:58:15
Failed to open SRL files. ORA-313
Redo Apply is running without NODELAY option while DelayMins=0
Failed to open SRL files. ORA-313
Error: LogXptMode value 'SYNC' of requires this database to have standby redo logs, but they are not configured correctly.
03/17/2017 14:59:15
Failed to open SRL files. ORA-313
Redo Apply is running without NODELAY option while DelayMins=0
Failed to open SRL files. ORA-313
Error: LogXptMode value 'SYNC' of requires this database to have standby redo logs, but they are not configured correctly.

Sure, you don’t want to loose the standby redo member. But the risk is not higher than loosing any other files, and this is why there is no reason to multiplex it. Standby redo logs are not the same as the primary online redo logs. On similar hardware, you need same size and you need one more group, but no reason to multiplex the same.

Documentation

The confusion may come from old documentation. The 10g documentation says:
For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.
This documentation dates from 2005 and systems have changed about availability of files.

More recent documentation is the white paper on Best Practices for Synchronous Redo Transport which mentions: It is critical for performance that standby redo log groups only contain a single member

So what?

At the time of 10g we had a LUN for redo logs and were not concerned by the size, but more by its unavailability. Things change. Losing a file, and only one file, today is extremely rare. We are more concerned about consolidation and performance. Having 4 online groups, 200MB or 500MB, and 5 standby groups, all multiplexed, for many databases will take space. And this space you want to allocate it on the fastest disks because user commits wait on log writes (on primary and standby except in Max Performance). You don’t want to over-allocate the space here. Better have larger online redo logs. And your goal is that network shipping + standby log writing takes not longer than local write to online redo logs, so that Data Guard protection do not increase commit latency. Multiplexing standby redo logs increases the risk to get longer writes on standby site.

So if you have your standby redo logs multiplexed, it’s not wrong. But things change and today you may prefer to save space and performance overhead with only one member.

Before writing this blog post, my poll on twitter had 40 votes. Only 28% mentioned no multiplexing. But twitter poll is not exact science as you can see that 8 people answered 42 members ;)

Because I've seen lot of misunderstanding about it, I'm curious to know how many members you have in your standby redo logs

— Franck Pachot (@FranckPachot) March 17, 2017

 

Cet article How many members for standby redo logs? est apparu en premier sur Blog dbi services.

Pages