Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 7 hours 31 min ago

PostgreSQL – logical replication with pglogical

Fri, 2018-03-23 11:31

Although PostgreSQL 10 integrate native logical replication (have a look here or here), it is always interesting to be aware of alternative solutions to the available in-core features.
One of those is called pglogical. It’s a Postgres extension developed by 2ndQuadrant, major contributor to PostgreSQL development.
pg_logical
The goal of this blog post is to discover how to install pglogical and configure it in order to generate simple logical replication cases.

Installation

To get the extension we should start with RPM repository installation :
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10] sudo yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm
Loaded plugins: fastestmirror
pglogical-rhel-1.0-3.noarch.rpm | 8.3 kB 00:00:00
Examining /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm: pglogical-rhel-1.0-3.noarch
Marking /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm to be installed
...
...
...
Installed:
pglogical-rhel.noarch 0:1.0-3


Complete!
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10]

Once installed, we can add the extension inside our Postgres instance :
postgres=# CREATE EXTENSION pglogical;
2017-12-18 16:24:39.079 CET [4327] ERROR: pglogical is not in shared_preload_libraries
2017-12-18 16:24:39.079 CET [4327] STATEMENT: CREATE EXTENSION pglogical;
ERROR: pglogical is not in shared_preload_libraries
postgres=#

Oops… the pglogical library must be loaded when the cluster starts :
postgres=# alter system set shared_preload_libraries = 'pglogical';
ALTER SYSTEM
postgres=#

Restart the cluster to take it in account :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ stop
waiting for server to shut down....2017-12-18 16:28:03.895 CET [4447] LOG: received fast shutdown request
2017-12-18 16:28:03.902 CET [4447] LOG: aborting any active transactions
2017-12-18 16:28:03.923 CET [4447] LOG: worker process: logical replication launcher (PID 4455) exited with exit code 1
2017-12-18 16:28:03.923 CET [4449] LOG: shutting down
2017-12-18 16:28:03.940 CET [4447] LOG: database system is shut down
done
server stopped
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ start
waiting for server to start....2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv4 address "0.0.0.0", port 5420
2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv6 address "::", port 5420
2017-12-18 16:28:10.407 CET [4654] LOG: listening on Unix socket "/tmp/.s.PGSQL.5420"
2017-12-18 16:28:10.416 CET [4655] LOG: database system was shut down at 2017-12-18 16:28:03 CET
2017-12-18 16:28:10.426 CET [4654] LOG: database system is ready to accept connections
2017-12-18 16:28:10.430 CET [4661] LOG: starting pglogical supervisor
2017-12-18 16:28:10.435 CET [4663] LOG: manager worker [4663] at slot 0 generation 1 detaching cleanly
2017-12-18 16:28:10.439 CET [4664] LOG: manager worker [4664] at slot 0 generation 2 detaching cleanly
2017-12-18 16:28:10.444 CET [4665] LOG: manager worker [4665] at slot 0 generation 3 detaching cleanly
done
server started
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10]

Once the cluster restarted with the library, we may noticed a new OS process :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] ps -ef | grep pglogical
postgres 5001 4994 0 16:33 ? 00:00:00 postgres: bgworker: pglogical supervisor

We should now be able to create the extension :
postgres=# CREATE EXTENSION pglogical;
CREATE EXTENSION
postgres=#
postgres=# \dx+ pglogical
Objects in extension "pglogical"
Object description
------------------------------------------------------------------------------------------
function pglogical.alter_node_add_interface(name,name,text)
function pglogical.alter_node_drop_interface(name,name)
function pglogical.alter_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.alter_subscription_add_replication_set(name,name)
function pglogical.alter_subscription_disable(name,boolean)
function pglogical.alter_subscription_enable(name,boolean)
function pglogical.alter_subscription_interface(name,name)
function pglogical.alter_subscription_remove_replication_set(name,name)
function pglogical.alter_subscription_resynchronize_table(name,regclass,boolean)
function pglogical.alter_subscription_synchronize(name,boolean)
function pglogical.create_node(name,text)
function pglogical.create_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.create_subscription(name,text,text[],boolean,boolean,text[],interval)
function pglogical.drop_node(name,boolean)
function pglogical.drop_replication_set(name,boolean)
function pglogical.drop_subscription(name,boolean)
function pglogical.pglogical_gen_slot_name(name,name,name)
function pglogical.pglogical_max_proto_version()
function pglogical.pglogical_min_proto_version()
function pglogical.pglogical_node_info()
function pglogical.pglogical_version()
function pglogical.pglogical_version_num()
function pglogical.queue_truncate()
function pglogical.replicate_ddl_command(text,text[])
function pglogical.replication_set_add_all_sequences(name,text[],boolean)
function pglogical.replication_set_add_all_tables(name,text[],boolean)
function pglogical.replication_set_add_sequence(name,regclass,boolean)
function pglogical.replication_set_add_table(name,regclass,boolean,text[],text)
function pglogical.replication_set_remove_sequence(name,regclass)
function pglogical.replication_set_remove_table(name,regclass)
function pglogical.show_repset_table_info(regclass,text[])
function pglogical.show_subscription_status(name)
function pglogical.show_subscription_table(name,regclass)
function pglogical.synchronize_sequence(regclass)
function pglogical.table_data_filtered(anyelement,regclass,text[])
function pglogical.wait_slot_confirm_lsn(name,pg_lsn)
table pglogical.depend
table pglogical.local_node
table pglogical.local_sync_status
table pglogical.node
table pglogical.node_interface
table pglogical.queue
table pglogical.replication_set
table pglogical.replication_set_seq
table pglogical.replication_set_table
table pglogical.sequence_state
table pglogical.subscription
view pglogical.tables
(48 rows)

The wal_level parameter must be set to ‘logical’ for logical replication :
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)

And the pg_hba file must contains an entry to allow connections for replication purpose.
Important : all the steps we have seen until now must be done on the standby side as well.

What’s next ?

Logical replication with Postgres is based on several components :
– Provider : the primary node/cluster
– Subscriber : the standby node/cluster, on which data will be replicated
– Replication set : collection of table you want to replicate
– Subscription : runs the replication, based on the replication set

We’ll start by creating the provider on the primary side :
postgres=# select pglogical.create_node (node_name := 'provider1', dsn := 'host=192.168.22.37 port=5420 dbname=postgres');
create_node
-------------
2976894835
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-----------+------------+-----------------------------------------------
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)

Notice again a new OS process :
postgres@ppas01:/home/postgres/ [PG10] ps -ef | grep pglogical
postgres 1796 1788 0 15:13 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4566 1788 0 16:05 ? 00:00:00 postgres: bgworker: pglogical manager 13211

On the standby side, we create the subscriber :
postgres=# select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=192.168.22.38 port=5420 dbname=postgres');
create_node
-------------
330520249
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-------------+-----------+----------------------------------------------
2049915666 | subscriber1 | 330520249 | host=192.168.22.38 port=5420 dbname=postgres
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)

What about replication set ? There is 3 existing by default with a different DML replication behavior for each :
postgres=# select * from pglogical.replication_set;
set_id | set_nodeid | set_name | replicate_insert | replicate_update | replicate_delete | replicate_truncate
------------+------------+---------------------+------------------+------------------+------------------+--------------------
290045701 | 2976894835 | default | t | t | t | t
3461245231 | 2976894835 | default_insert_only | t | f | f | t
2465411902 | 2976894835 | ddl_sql | t | f | f | f

We can easily add our own :
postgres=# select pglogical.create_replication_set('my_set', true, true, true, true);
create_replication_set
------------------------
1521346531

To start the replication we have to create a subscription (using the replication set we created) from the standby side :
postgres=# select pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=192.168.22.37 port=5420 dbname=postgres', replication_sets := '{my_set}');
create_subscription
---------------------
1763399739
(1 row)


postgres=# select subscription_name, status FROM pglogical.show_subscription_status();
subscription_name | status
-------------------+-------------
subscription1 | replicating

Have a look in the log file… replication started with the correct provider and subscription :
2018-02-02 15:16:14.234 CET [5488] LOG: starting apply for subscription subscription1
2018-02-02 15:17:54.547 CET [5502] LOG: starting pglogical database manager for database postgres

postgres@ppas02:/u02/pgdata/PG10/ [PG10] ps -ef | grep pglogical
postgres 3113 3105 0 10:01 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4587 3105 0 13:47 ? 00:00:00 postgres: bgworker: pglogical manager 13211
postgres 5060 3105 0 15:06 ? 00:00:00 postgres: bgworker: pglogical apply 13211:1763399739

The last step is to add the tables in the replication set we created :
postgres=# select pglogical.replication_set_add_all_tables('my_set', ARRAY['public']);
replication_set_add_all_tables
--------------------------------
t
(1 row)

The insert/update/delete operations run against the primary server are now replicated to the standby one.

 

Cet article PostgreSQL – logical replication with pglogical est apparu en premier sur Blog dbi services.

Updating the partition key now works in PostgreSQL 11

Fri, 2018-03-23 11:00

In the last post about partitioning improvements in PostgreSQL 11 we talked about unique indexes and primary keys on partitioned tables. Both did not work in PostgreSQL 10 but now do in PostgreSQL 11. Another operation that did not work in PostgreSQL 10 and does now in PostgreSQL 11 is: Updating the partition key, which means that a row will move from one partition to another. Lets have a look at that.

We’ll use the same little list partitioned table as in the last post and start with PostgreSQL 10:

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

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

Insert some data:

postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i;
INSERT 0 10
postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i;
INSERT 0 10

So we have ten beers and ten wines.

postgres=# select count(*) from only part_1;
 count 
-------
    10
(1 row)

postgres=# select count(*) from only part_2;
 count 
-------
    10
(1 row)

What happens if we update the partition key because we like more beer than wine in PostgreSQL 10?

postgres=# update part set list = 'beer' where a = 15;
ERROR:  new row for relation "part_2" violates partition constraint
DETAIL:  Failing row contains (15, beer).

Not so good. Lets try the same in PostgreSQL 11:

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

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i;
INSERT 0 10
postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i;
INSERT 0 10
postgres=# update part set list = 'beer' where a = 15;
UPDATE 1

Excellent, just works.

 

Cet article Updating the partition key now works in PostgreSQL 11 est apparu en premier sur Blog dbi services.

Hash Partitioning in PostgreSQL 11

Fri, 2018-03-23 07:45

PostgreSQL 10 comes with partition support. But Hash Partitioning was not supported. PostgreSQL 11 will support Hash Partition.
In the documentation we can read
The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.
For this demonstration let’s create a table with a hash partition

drop table dept;
create table dept (id  int primary key) partition by hash(id) ; 

Now let’s create for example 10 partitions

create table dept_1 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 0);
create table dept_2 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 1);
create table dept_3 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 2);
create table dept_4 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 3);
create table dept_5 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 4);
create table dept_6 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 5);
create table dept_7 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 6);
create table dept_8 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 7);
create table dept_9 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 8);
create table dept_10 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 9);

We can verify that partitions are created using the \d+ command

(postgres@[local]:5432) [test] > \d+ dept
                                   Table "public.dept"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | De
scription
--------+---------+-----------+----------+---------+---------+--------------+---
----------
 id     | integer |           | not null |         | plain   |              |
Partition key: HASH (id)
Indexes:
    "dept_pkey" PRIMARY KEY, btree (id)
Partitions: dept_1 FOR VALUES WITH (modulus 10, remainder 0),
            dept_10 FOR VALUES WITH (modulus 10, remainder 9),
            dept_2 FOR VALUES WITH (modulus 10, remainder 1),
            dept_3 FOR VALUES WITH (modulus 10, remainder 2),
            dept_4 FOR VALUES WITH (modulus 10, remainder 3),
            dept_5 FOR VALUES WITH (modulus 10, remainder 4),
            dept_6 FOR VALUES WITH (modulus 10, remainder 5),
            dept_7 FOR VALUES WITH (modulus 10, remainder 6),
            dept_8 FOR VALUES WITH (modulus 10, remainder 7),
            dept_9 FOR VALUES WITH (modulus 10, remainder 8)

(postgres@[local]:5432) [test] >

Now let’s insert some rows in the table dept

(postgres@[local]:5432) [test] > insert into dept (select generate_series(0,200000));
INSERT 0 200001
(postgres@[local]:5432) [test] >

We can verify that rows are not in the base table

(postgres@[local]:5432) [test] > select count(*) from  only dept ;
 count
-------
     0
(1 row)

(postgres@[local]:5432) [test] >

But that row are in the partitions

(postgres@[local]:5432) [test] > select count(*) from  dept ;
 count
--------
 200001
(1 row)

What we can also observe it that rows are uniformly distributed among partitions. This distribution is automatically done by the hash algorithm.

(postgres@[local]:5432) [test] > select count(*) from  only dept_1 ;
 count
-------
 19982
(1 row)
                                   
(postgres@[local]:5432) [test] > select count(*) from  only dept_2 ;
 count
-------
 20199
(1 row)

(postgres@[local]:5432) [test] > select count(*) from  only dept_3 ;
 count
-------
 19770
(1 row)

(postgres@[local]:5432) [test] > select count(*) from  only dept_5 ;
 count
-------
 20068
(1 row)

(postgres@[local]:5432) [test] >
 

Cet article Hash Partitioning in PostgreSQL 11 est apparu en premier sur Blog dbi services.

What is the maximum in list size in PostgreSQL?

Fri, 2018-03-23 04:53

Yesterday, while being at a customer, an interesting question popped up: What is the maximum of in list values in PostgreSQL? I couldn’t answer although I never read somewhere that there is a limit. The following is for fun only and I am not saying that creating huge in lists is a good idea. Lets go.

The version I tested is PostgreSQL 10:

postgres=# select version(), now();
                                                  version                                                   |              now              
------------------------------------------------------------------------------------------------------------+-------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit | 2018-03-21 18:29:50.269369+01
(1 row)

postgres=# create table t1 ( a varchar(10));
CREATE TABLE

We will use a very simple bash script to build the in list and execute the result in PostgreSQL:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

statement='select * from t1 where a in ('

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        statement="${statement} '${i}',"
    elif [ "${i}" == "${count}" ]; then
        statement="${statement} '${i}');"
    fi
done

psql -c "${statement}" postgres

Lets start with 100:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100
 a 
---
(0 rows)

Time: 0.983 ms

1000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000
 a 
---
(0 rows)

Time: 1.525 ms

10000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 10000
 a 
---
(0 rows)

Time: 11.549 ms

… takes a much longer (because of the bash script which fully occupies my virtual core):

./t.sh: line 15: /u01/app/postgres/product/10/db_0/bin/psql: Argument list too long

So there at least is a limit with psql. Lets try by creating a sql script and execute that instead:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

echo 'select * from t1 where a in (' > ttt.sql

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        echo  "'${i}'," >> ttt.sql
    elif [ "${i}" == "${count}" ]; then
        echo "'${i}');" >> ttt.sql
    fi
done

psql -f ttt.sql postgres

This way of stringing together the statement is much more efficient than building the list by concatenating everything into one variable. Does it still work?

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100000
 a 
---
(0 rows)

Time: 155.986 ms

Not a problem, one more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000000
 a 
---
(0 rows)

Time: 14211.766 ms (00:14.212)

Still works. So now we could say: lets stop, who in the world will pass one million values into an in list. On the other hand, lets have fun and double:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 2000000
 a 
---
(0 rows)

Time: 3993.091 ms (00:03.993)

One more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 3000000
psql:ttt.sql:3000001: ERROR:  out of memory
DETAIL:  Failed on request of size 524288.
Time: 3026.925 ms (00:03.027)

Ok, now I am hitting some limits, but probably not those of PostgreSQL. I’ll test further when I have more time for that :)

 

Cet article What is the maximum in list size in PostgreSQL? est apparu en premier sur Blog dbi services.

Local partitioned indexes in PostgreSQL 11

Thu, 2018-03-22 02:23

When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in PostgreSQL 10.

A very simple list partitioned table:

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

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

In PostgreSQL 10 what happens when we create an index on the partitioned table?

postgres=# create index i_test on part (a);
ERROR:  cannot create index on partitioned table "part"

You just can not do it. But you can create indexes on the partitions directly:

postgres=# create index i_test_1 on part_1 (a);
CREATE INDEX
postgres=# create index i_test_2 on part_2 (a);
CREATE INDEX

Lets do the same test with PostgreSQL 11:

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

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

Try to create the index on the partitioned table:

postgres=# create index i_test on part (a);
CREATE INDEX
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_a_idx" btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_a_idx" btree (a)

The index is cascaded down to all the partitions in PostgreSQL 11 which is really nice. As a side effect of this, when you try this in PostgreSQL 10:

postgres=# alter table part add constraint part_pk primary key(a,list);
ERROR:  primary key constraints are not supported on partitioned tables
LINE 1: alter table part add constraint part_pk primary key(a,list);
                             ^

… you will get an error message telling you that primary keys are not supported on partitioned tables. The same applies here, you can do that on the partitions directly:

postgres=# alter table part_1 add constraint part1_pk primary key(a,list);
ALTER TABLE
postgres=# alter table part_2 add constraint part2_pk primary key(a,list);
ALTER TABLE

Now in PostgreSQL 11 this works as well:

postgres=# alter table part add constraint part_pk primary key(a,list);
ALTER TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "part_pk" PRIMARY KEY, btree (a, list)
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_pkey" PRIMARY KEY, btree (a, list)
    "part_1_a_idx" btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_pkey" PRIMARY KEY, btree (a, list)
    "part_2_a_idx" btree (a)

Quite some improvements to show up in PostgreSQL 11.

 

Cet article Local partitioned indexes in PostgreSQL 11 est apparu en premier sur Blog dbi services.

pg_basebackup and redirecting progress messages to a file

Wed, 2018-03-21 11:32

Recently I came over that commit and wondered what that is about. The answer is quite simple but I didn’t know that this issue existed. Basically it is about how progress messages are written to screen and how they are written to a file. Lets have a look.

When your run pg_basebackup with progress messages and in verbose mode the output looks like this:

postgres@pgbox:/home/postgres/ [PG10] pg_basebackup --pgdata=/var/tmp/aa --verbose --progress 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 4/30000028 on timeline 1
pg_basebackup: starting background WAL receiver
593320/593320 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 4/30000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

You’ll notice that the highlighted line always is overwritten on screen until we reach one hundred percent. Looking at that line when pg_basebackup is running will give you an estimate on how long it will take and you will see which file it is currently working on. When you do the same thing but kick it in the background like this:

postgres@pgbox:/home/postgres/ [PG10] pg_basebackup --version
pg_basebackup (PostgreSQL) 10.0 
postgres@pgbox:/home/postgres/ [PG10] mkdir /var/tmp/aa
postgres@pgbox:/home/postgres/ [PG10] nohup pg_basebackup --pgdata=/var/tmp/aa --verbose --progress  > /tmp/a.log 2>&1  &

… you will have the same output in the log file:

postgres@pgbox:/home/postgres/ [PG10] cat -f /tmp/a.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 4/28000028 on timeline 1
pg_basebackup: starting background WAL receiver
593315/593315 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 4/28000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

Somehow that was not considered very useful so the commit mentioned above changed that:

postgres@pgbox:/home/postgres/ [PGDEV] pg_basebackup --version
pg_basebackup (PostgreSQL) 11devel
postgres@pgbox:/home/postgres/ [PGDEV] nohup pg_basebackup --pgdata=/var/tmp/aa --verbose --progress  > /tmp/a.log 2>&1  &
postgres@pgbox:/home/postgres/ [PGDEV] cat /tmp/a.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_29846"
     0/184659 kB (0%), 0/1 tablespace (/var/tmp/aa/backup_label           )
  1705/184659 kB (0%), 0/1 tablespace (/var/tmp/aa/base/1/1249            )
  4697/184659 kB (2%), 0/1 tablespace (/var/tmp/aa/base/1/2657            )
  8395/184659 kB (4%), 0/1 tablespace (/var/tmp/aa/base/13276/1255        )
 20601/184659 kB (11%), 0/1 tablespace (/var/tmp/aa/base/13277/2670        )
 30614/184659 kB (16%), 0/1 tablespace (/var/tmp/aa/base/16395/2607_vm     )
 45367/184659 kB (24%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
 54743/184659 kB (29%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
 74327/184659 kB (40%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
118807/184659 kB (64%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
146647/184659 kB (79%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
175197/184659 kB (94%), 0/1 tablespace (/var/tmp/aa/base/16395/16432       )
184668/184668 kB (100%), 0/1 tablespace (/var/tmp/aa/global/pg_control      )
184668/184668 kB (100%), 0/1 tablespace (/var/tmp/aa/global/pg_control      )
184668/184668 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/E000168
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

When you redirect the output into a file you can see much more steps in the log file compared to what we saw before (only a single line which is overwritten all the time). Seems to be a good change.

 

Cet article pg_basebackup and redirecting progress messages to a file est apparu en premier sur Blog dbi services.

PostgreSQL 11: Procedures and transaction control

Tue, 2018-03-20 11:58

Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top of that there was another commit which enables transaction control inside procedures which can be quite interesting. Lets see how that works.

Up to PostgreSQL 10 the only choice to have something like a procedure is to create a function returning void:

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

postgres=# CREATE FUNCTION dummy_func (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   RAISE NOTICE 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# set client_min_messages = 'NOTICE';
SET
postgres=# select dummy_func(1);
NOTICE:  id is 1
 dummy_func 
------------
 
(1 row)

When you tried to do something like this it was not possible:

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE dummy_proc (id int) AS $$

But now in the PostgreSQL development version you can do it (CREATE PROCEDURE):

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

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call dummy_proc(1);
NOTICE:  id is 1
CALL

Also notice that you need to use call to execute a procedure. Using select as you would do it for a function will not work:

postgres=# select dummy_proc(1);
ERROR:  dummy_proc(integer) is a procedure
LINE 1: select dummy_proc(1);
               ^
HINT:  To call a procedure, use CALL.

On top of the commit which introduced procedures there was another one (see the beginning of this post) which introduced transaction control for procedures. As the name implies this can be useful when you want to control transactions inside the procedure, e.g.:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE OR REPLACE PROCEDURE dummy_proc2 (id int) AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;

When you execute this it will violate the primary key with the third insert:

postgres=# call dummy_proc2(1);
NOTICE:  id is 1
NOTICE:  id is 2
NOTICE:  id is 2
ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(2) already exists.
CONTEXT:  SQL statement "insert into t1 (a) values (l_id)"
PL/pgSQL function dummy_proc2(integer) line 13 at SQL statement

… but because we can now commit (or rollback) in between we did not lose all the records:

postgres=# select * from t1;
 a 
---
 1
 2
(2 rows)

Trying to do the same with a function in PostgreSQL 10 will not work:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE FUNCTION dummy_func2 (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select dummy_func2(1);
NOTICE:  id is 1
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function dummy_func2(integer) line 7 at SQL statement
postgres=# select * from t1;
 a 
---
(0 rows)

Nice features …

 

Cet article PostgreSQL 11: Procedures and transaction control est apparu en premier sur Blog dbi services.

redhat event Ansible Automates Zurich

Tue, 2018-03-20 10:08

Here are some fresh personal impressions from the “Ansible Automates Zurich” half day event organized by redhat.
There was a overwhelming interest for this event. About 200 people filled the room and there were many more on the waiting list.
The event started with a networking lunch. My impression was, that the visitors were not only interested in the food, but also gaining some practical knowledge about Ansible. Most of them are already using Ansible.

Eduard Modalek from redhat opened the Event. He was very impressed with the interest in the event.
AnsibleAutomates ZurichSacha Dubois who is a great speaker with solid technical background gave an introduction about Ansible and the impact to your team and enterprise. “Ansible loves the repetitive work your people hate.”

Ansible loves the repetitive work your people hate

Ansible community downloads

The strength of Ansible is the community. Enjoy the  statistics. Ansible has

  • more than 27’000 stars on GitHub
  • over 1250 modules and
  • more than 500’000 downloads a month

 

Christian Sanabria and Marc Frauenfelder from ipt – Innovation Process Technology showed how they do Continuous Delivery by combining Jenkins Pipeline with the Ansible Tower API for provisioning. They published the code in GitHub:
https://github.com/mfrauenfelder/ansible/blob/master/Jenkinsfile

After the break Rob McMahon, redhat Director Cloud EMEA, who had already presented a day before at the Ansible Automates Netherlands event, showed his visions about empowering customers with Ansible.  The future are automated organizations and Ansible is the right tool to automate everything and help to overcome the silo character of cross functional teams.

Ansible Automates Organizations
Another customer case has been shown by Nicolas Christener and Lukas Grossar from Adfinis SyGroup AG. They fully automated the roll out of desktop clients.

Roland Wolters from redhat reveals what is coming in March 2018 with Ansible 2.5

  • Networking now first level connection “plugin”- credentials just like with any other connections
  • Role versioning
  • become on Windows features NT AUTHORITY\System and others
  • new loop mechanism ( no with_loops anymore)
  • fact namespaces

He also explained the differences between Ansible Tower and  the open source project AWX.

The highlight at the end was a live demo which deployed a WordPress environment in the Microsoft Azure cloud. Roland Wolters and Sacha Dubois demonstrated it successfully. Congratulations ! If you have a valid subscription at Microsoft Azure you can try it out https://github.com/sa-ch/ansible-azure-deployment

Ansible deploy WordPress on Microsoft AzureThere has been time for drinks and networking afterwards.
I spoke with Eduard Modalek. The organization was a big challenge, so many people wanted to attend this event and this was the biggest room they could get at such short notice in Zürich. He said that in future there will be more events like this, and perhaps some workshops with time for the audience to exchange ideas.

 

Cet article redhat event Ansible Automates Zurich est apparu en premier sur Blog dbi services.

SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended?

Tue, 2018-03-20 07:18

This article is an additional article to my precedent about “SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

The goal is to see after I suspend the rebuild, if I can run a traditional rebuild without the option RESUME.

 

I start an index rebuild with the option Resumable:

ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction]
REBUILD WITH (RESUMABLE = ON, ONLINE = ON)
GO

 

In a new query window, I stopped the rebuild:

USE [WideWorldImportersDW]
GO
ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
PAUSE
GO

 

As you can see, the index rebuild is stopped:
riro2_00

 

I have a look on the index state with the DMV sys.index_resumable_operations:
riro2_01

 

To restart, I run the rebuild without the RESUME or ABORT options:
riro2_02

As you can see, the query does not work and you have this error message:
Msg 10637, Level 16, State 1, Line 14
Cannot perform this operation on ‘object’ with ID 430624577 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

The standard rebuild will not change the status of the resumable rebuild and begin from scratch in order to do the rebuild from beginning.
Finally, the only way to rebuild the index is really to use the RESUME option and continue where it’s stopped.

 

Cet article SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended? est apparu en premier sur Blog dbi services.

SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

Tue, 2018-03-20 06:14

Last week, I presented this option in our internal event, the dbi xChange.

My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild?

I was not able to answer because I forgot to do this test.

The new enhancement for an index rebuild provides the capability to suspend, resume or abort an online index operation (msdn reference). I always use the option PAUSE to see how it works but never with a break.

Now I do the test to have the answer. It’s never too late! ;-)

To do my demo, I downloaded the Microsoft example database WideWorldImportersDW from github.

I choose the index NCI_dbo_Transaction in the Transaction table.

riro_00

As you can see with this query, the index is big enough to have the time to interrupt the rebuild.

I run the rebuild with this command:

ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
REBUILD WITH (RESUMABLE = ON, ONLINE = ON)
GO

After killing the SQL Server task in the task manager, I have this message in SSMS:
riro_01
Of course, the server is no more available…

I start the SQL Server service and have a look on the index state with the DMV sys.index_resumable_operations:
riro_02
As you can see the index is on state PAUSE with 19% completed.

To restart, I run the rebuild with the RESUME option:
riro_03

If the index rebuild is interrupted, you don’t need to restart from scratch.
The rebuild is in a PAUSE state and then you can decide or not to continue the rebuild.
Very clever option, isn’t it?

Don’t hesitate to go on David’s blog to read how this option works with AlwaysOn:
https://blog.dbi-services.com/sql-server-2017-high-availability-and-resumable-online-indexes/

 

Cet article SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild? est apparu en premier sur Blog dbi services.

Finally you will be able to use exit and quit in psql

Tue, 2018-03-20 05:39

When giving the PostgreSQL DBA Essentials workshop one of the main issues people have is how they can exit psql. Even on stackoverflow this is a popular topic. The good news for people who still don’t like to use “\q” here is the commit that will add additional options to quit/exit from psql.

Up to PostgreSQL 10 what you can usually see is something like this:

postgres@pgbox:/home/postgres/ [PG10] psql -X postgres
psql (10.0)
Type "help" for help.

postgres=# exit
postgres-# exit
postgres-# quit
postgres-# let me out, what do I need to to?
postgres-# 

Starting with PostgreSQL 11 you can either use “quit”:

postgres@pgbox:/home/postgres/ [PGDEV] psql -X postgres
psql (11devel)
Type "help" for help.

postgres=# quit
postgres@pgbox:/home/postgres/ [PGDEV] 

… or “exit”:

postgres@pgbox:/home/postgres/ [PGDEV] psql -X postgres
psql (11devel)
Type "help" for help.

postgres=# exit
postgres@pgbox:/home/postgres/ [PGDEV] 

I am pretty sure MacBook users will love that :)

 

Cet article Finally you will be able to use exit and quit in psql est apparu en premier sur Blog dbi services.

Result cache invalidation caused by DML locks

Sun, 2018-03-18 16:03

In a previous post about Result Cache, I was using ‘set autotrace on’ in my session to see when the result cache hit saves the logical reads. I got a comment, by e-mail and with full test case, mentioning that the invalidation I had when doing some DML does not occur when autotrace is set to on. I reproduced the problem, on 18c because I use my cloud services to do my tests, but it works the same way in 12c.

I’ll focus here on DML which does not modify any rows from the table the result depends on, because this is the case that depends on the session transaction context. When rows are modified, the result is always invalidated.

For each test here I’ll flush the result cache:

SQL> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
no rows selected

And I begin each test with the result cache loaded by a query.

DML on the dependent table

The result or the query is loaded into the cache, with a dependency on the SCOTT.DEPT table:

SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

Now, I’m executing some DML on this DEPT table:

SQL> connect scott/tiger@//localhost/PDB1
Connected.
SQL> delete from DEPT where DEPTNO>40;
0 rows deleted.

This delete does not touch any row, but declares the intention to modify the table with a Row eXclusive lock (TM mode=3):

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 0 0 0 21 SCOTT oracle 7772 3 5 DEPT

Note that the transaction ID is all zeros. Logically, we are in a transaction, as we have a lock that will be released only at the end of the transaction (commit or rollback). But, as we didn’t modify any block yet, there is no entry in the transaction table:

SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
no rows selected

I commit this transaction:

SQL> commit;
Commit complete.

and check the result cache objects:

SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

This look good: nothing was modified so there is no reason to invalidate the result. However, in the previous post I referenced, a similar test invalidated the cache even when no rows were touched. And a reader remarked that this occured only when I previously run a query with ‘autotrace on’. Without autotrace, the behaviour is like what I show here above: no invalidation when no rows is modified.

Same in an already started transaction

The difference is that the ‘autotrace on’ runs an ‘explain plan’, filling the plan table, which means that a transaction was already started. Here I run autotrace on a completely different query:

SQL> set autotrace on explain
Autotrace Enabled
Displays the execution plan only.
SQL> select * from dual;
DUMMY
-----
X
 
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
Plan hash value: 272002086
 
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
 
SQL> set autotrace off;
Autotrace Disabled

Following this, I have no locked objects, but a transaction has been initiated:

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
no rows selected
 
SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
XIDUSN XIDSLOT XIDSQN START_TIME USED_UREC
------ ------- ------ ---------- ---------
9 18 709 03/18/18 20:30:09 2

Here is the big difference: I have a transaction ID here.
Now doing the same as before, a delete touching no rows:

SQL> delete from DEPT where DEPTNO>40;
0 rows deleted.

When I query the lock objects, they are now associated to a non-zero transaction ID:

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 9 18 709 21 SCOTT oracle 7772 3 5 DEPT

Here is the difference, now the result cache has been invalidated:

SQL> commit;
Commit complete.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 1
1 Result Invalid select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

DML on another table – RS though referential integrity

CaptureLocksThis gives the idea that the invalidation is not really triggered by actual modifications, but at commit time from the DML locks when associated with a transaction.
And some DML on tables may acquire a Row-S or Row-X lock on the tables linked by referential integrity. This has changed a lot though versions – look at the slide on the right.

I start in the same situation, with the result cache loaded, no locked objects, but a transaction that has started:

SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0
 
SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
no rows selected
 
SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
 
XIDUSN XIDSLOT XIDSQN START_TIME USED_UREC
------ ------- ------ ---------- ---------
8 31 766 03/18/18 20:30:10 2

I delete from the EMP table and if you are familiar with the SCOTT schema, you know that it has a foreign key to DEPT.

SQL> delete from EMP where DEPTNO>=40;
0 rows deleted.

The delete acquires a Row eXclusive lock on EMP even when there are no rows deleted (DML locks are about the intention to modify rows). And from the table above, it acquires a Row Share (mode=2) on the parent table:

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 8 31 766 21 SCOTT oracle 7772 2 5 DEPT
73378 8 31 766 21 SCOTT oracle 7772 3 5 EMP

I commit and check the result cache:

SQL> commit;
Commit complete.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

No invalidation here, which makes sense because Row Share is not an intention to write.

DML on another table – RS though referential integrity

I do the same here, but with an insert on EMP which acquires a Row eXclusive on the parent table.

SQL> insert into EMP(EMPNO,DEPTNO)values(9999,40);
1 row inserted.
 
SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 10 32 560 21 SCOTT oracle 7772 3 5 DEPT
73378 10 32 560 21 SCOTT oracle 7772 3 5 EMP
 
SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
 
XIDUSN XIDSLOT XIDSQN START_TIME USED_UREC
------ ------- ------ ---------- ---------
10 32 560 03/18/18 20:30:10 4
 
SQL> commit;
Commit complete.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
---- ------ ------------------------------------------ ---- ---------------------- ----- -- --
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 1
1 Result Invalid select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

Here, DEPT has been invalidated after the insert on EMP. There were no modifications on DEPT, but the result cache is not directly tracking the modifications, but rather the intention of modification. And then, the implementation of the result cache invalidation tracks, at commit, the Row eXclusive locks when they are related to a know transaction. You can have the same result if, from a transaction that has already started, you run a simple:

SQL> lock table DEPT in row exclusive mode;
Lock succeeded.

This means that there are many reasons why the result cache may be invalid even when the objects in the ‘Dependency’ are not modified. Be careful, invalidations and cache misses are expensive and do not scale on high load. And because of locks through referential integrity, this can happen even on static tables. Let’s take an example. in an order entry system, you may think that the ‘products’ table is a good candidate for result cache – updated twice a year but read all the times. But now that you know that inserts on child tables, such as the order table, will invalidate this cache, you may think again about it.

 

Cet article Result cache invalidation caused by DML locks est apparu en premier sur Blog dbi services.

Automatic Block Media Recovery in a DataGuard

Sat, 2018-03-17 13:39

With Oracle 12.2, in a Data Guard environment corrupted data blocks can be automatically replaced with uncorrupted copies of those blocks.
There are just some requirements:
• The physical standby database must be operating in real-time query mode, which requires an Oracle Active Data Guard license.
• The physical standby database must be running real-time apply.
Automatic block media recovery works in two directions depending on whether the corrupted blocks are encountered on the primary or on the standby.
This recovery can happen if corrupted data are encountered in the primary database or in the secondary database.
In this blog we are going to see how this feature works.
Below the configuration we are using

DGMGRL> show configuration;

Configuration - ORCL_DR

  Protection Mode: MaxAvailability
  Members:
  ORCL_SITE  - Primary database
    ORCL_SITE1 - Physical standby database
    ORCL_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 19 seconds ago)

DGMGRL>

We can verifiy the status of our databases

DGMGRL> show database  'ORCL_SITE' ;

Database - ORCL_SITE

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORCL

Database Status:
SUCCESS

DGMGRL> show database  'ORCL_SITE1' ;

Database - ORCL_SITE1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 5.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORCL

Database Status:
SUCCESS

DGMGRL> show database  'ORCL_SITE2' ;

Database - ORCL_SITE2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 4.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORCL

Database Status:
SUCCESS

DGMGRL>

The feature works for any protection mode, but in our case the tests are done with a MaxAvailability mode

If corrupt data blocks are on the primary database, then the primary automatically searches for good copies of those blocks on a standby and, if they are found, has them shipped back to the primary.
This only requirement is that the primary requires a LOG_ARCHIVE_DEST_n to the standby. That already should be the case in a Data Guard environment.

If corrupted block is located on the standby, the standby will automatically request uncorrupted copies of those blocks to the primary. The condition for this mechanism to work is
• The LOG_ARCHIVE_CONFIG parameter is configured with a DG_CONFIG list and a LOG_ARCHIVE_DEST_n parameter is configured for the primary database.
or
• The FAL_SERVER parameter is configured and its value contains an Oracle Net service name for the primary database.

In this demonstration we will simulate data corruption in the primary database. But the scenario is the same for a corrupted blocks at standby side and will work same.
From the primary we can verify that LOG_ARCHIVE_DEST_n is set.

SQL> select dest_name,DESTINATION,status from v$archive_dest where destination is not null;

DEST_NAME            DESTINATION                    STATUS
-------------------- ------------------------------ ---------
LOG_ARCHIVE_DEST_1   USE_DB_RECOVERY_FILE_DEST      VALID
LOG_ARCHIVE_DEST_2   ORCL_SITE1                     VALID
LOG_ARCHIVE_DEST_3   ORCL_SITE2                     VALID

For the demonstration let’s consider a table of user SCOTT in a tablespace mytab

SQL> select table_name,tablespace_name from dba_tables where owner='SCOTT' and table_name='EMPBIS';

TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
EMPBIS          MYTAB

SQL>

Now let’s identify blocks for the table on the primary and let’s corrupt them.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid)  from scott.empbis);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 131

SQL>

And then let’s run following command to corrupt corresponding blocks.

[oracle@primaserver ORCL]$ dd of=/u01/app/oracle/oradata/ORCL/mytab01.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000315116 s, 26.0 MB/s
[oracle@primaserver ORCL]$

In a normal environment, accessing to corrupted data by a SELECT will return errors.
But in our case on the primary if we flush the buffer_cache, and and we do a select on the table, rows are returned without errors.

13:41:18 SQL> alter system flush buffer_cache;

System altered.

13:41:22 SQL> select * from scott.empbis;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

13:41:27 SQL>

Indeed Oracle automatically recovered corrupted blocks. And in the primary alert log at the same time we can see that a recovery was done.

ALTER SYSTEM: Flushing buffer cache inst=0 container=0 global
2018-01-26T13:41:26.540640+01:00
Hex dump of (file 2, block 131) in trace file /u01/app/oracle/diag/rdbms/orcl_site/ORCL/trace/ORCL_ora_3359.trc

Corrupt block relative dba: 0x00800083 (file 2, block 131)
Completely zero block found during multiblock buffer read

Reading datafile '/u01/app/oracle/oradata/ORCL/mytab01.dbf' for corruption at rdba: 0x00800083 (file 2, block 131)
Reread (file 2, block 131) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 2, block# 131)
2018-01-26T13:41:26.545798+01:00
Corrupt Block Found
         TIME STAMP (GMT) = 01/26/2018 13:41:25
         CONT = 0, TSN = 7, TSNAME = MYTAB
         RFN = 2, BLK = 131, RDBA = 8388739
         OBJN = 74352, OBJD = 74352, OBJECT = EMPBIS, SUBOBJECT =
         SEGMENT OWNER = SCOTT, SEGMENT TYPE = Table Segment
2018-01-26T13:41:27.002934+01:00
Automatic block media recovery successful for (file# 2, block# 131)
2018-01-26T13:41:27.005015+01:00
Automatic block media recovery successful for (file# 2, block# 131)

Conclusion:
We have seen, that an active DataGuard environment may help with corrupted data.

 

Cet article Automatic Block Media Recovery in a DataGuard est apparu en premier sur Blog dbi services.

Connect-times to the DB suddenly become very slow using sqlcl

Mon, 2018-03-12 06:26

I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html), because there is no need to “install” something then. All I needed was an installed JRE on the Linux-machine. Fortunately that was available. So I downloaded the newest version of sqlcl and unzipped it. Initially I had an issue with the timezone when connecting:


ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found

I could workaround this by adding “AddVMOption -Doracle.jdbc.timezoneAsRegion=false” in <sqlcl-home>/bin/sql:


#
# set up the main arguments for java.
#
function setupArgs {
...
AddVMOption -Doracle.jdbc.timezoneAsRegion=false
....

When looping over the databases the connect-times suddenly became very slow. I had to wait for up to 2 minutes to setup a connection to the database. After doing strace on the sqlcl-connects I saw the reason for the slow connects:

The Oracle JDBC driver requires a random number for encrypting the connect string. By default this random number is generated from /dev/random. If the number of entropy-entries (cat /proc/sys/kernel/random/entropy_avail) becomes too low, the access to /dev/random is blocked until enough entropy-entries are available again. The number of entropy-entries is generated through some random noise on the machine (drivers for keyboard, mouse, network, etc. generate the entries). If there is no driver-activity then the entropy-pool may become empty.

The workaround is to
– either artificially generate some driver load (examples on how to do it are in referenced links at the end of the blog)
– or use /dev/urandom instead of /dev/random

The first workaround was not feasible in my case. So I had to use the more unsecure workaround of using /dev/urandom. I.e. I updated <sqlcl-home>/bin/sql again and added “AddVMOption -Djava.security.egd=file:///dev/urandom”:


#
# set up the main arguments for java.
#
function setupArgs {
...
AddVMOption -Djava.security.egd=file:///dev/urandom
....

Afterwards my sqlcl-connects were fast again.

See the following links for more info:

http://www.usn-it.de/index.php/2009/02/20/oracle-11g-jdbc-driver-hangs-blocked-by-devrandom-entropy-pool-empty
https://stackoverflow.com/questions/2327220/oracle-jdbc-intermittent-connection-issue
https://oraganism.wordpress.com/2015/09/20/slow-jdbc-connections-strace-and-random-numbers
http://www.oracle.com/technetwork/database/database-appliance/documentation/oda-eval-comparing-performance-1895230.pdf –> see page 7: “Specify non-blocking random number generators”

Remark: I updated the Oracle Community entry https://community.oracle.com/thread/4073170 with that information as well.

 

Cet article Connect-times to the DB suddenly become very slow using sqlcl est apparu en premier sur Blog dbi services.

18c PDB switchover

Sun, 2018-03-11 08:58

In multitenant, the recovery and availability are at CDB level. But customers asked for a switchover at PDB level so Oracle has done that in 18c, based on refreshable PDBs.

For this test I have two multitenant database on an Oracle Cloud service in 18c: CDB1 and CDB2. The only special thing I did was disable the mandatory TDE encryption, because I was not able to have the switchover working. With TDE encryption, I got the “ORA-46697: Keystore password required”. But there is no ‘keystore identified by’ option in the ‘alter pluggable database’. Then If you came upon this post from a search on this error, I’ve no solution yet (SR 3-17001228251 opened on the Oracle Cloud Support ).

Creating the source PDB

In CDB1 I create CDB1DEMO pluggable database:

21:06:06 //localhost/CDB1 SQL>create pluggable database CDB1DEMO admin user admin identified by oracle file_name_convert=('/pdbseed/','/CDB1DEMO/');
Pluggable database CDB1DEMO created.

I could use ‘show pdb’ but I use SQLcl and the current version (17.4) does not recognize Oracle 18c as a container database, so I’ve created my own alias to run a query onV$PDBS and DBA_PDBS:

21:06:12 //localhost/CDB1 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right outer join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id;
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:43:46 NONE 944121613 1524290
3 CDB1DEMO MOUNTED NEW 944121613 2 21:06:06 NONE 944121613 1524290

CDB1DEMO is in mount state in V$PDBS and status is NEW because just created. You may wonder why I ‘right join’ here as PDBs known by the database should always be known by the instance. But I said that I opened a SR for switchover with TDE and here I got an inconsistency between V$PDBS and DBA_PDBS.


21:06:12 //localhost/CDB1 SQL>alter pluggable database CDB1DEMO open;
Pluggable database CDB1DEMO altered.
21:06:14 //localhost/CDB1 SQL>select * from pdb_plug_in_violations;
no rows selected
 
21:06:15 //localhost/CDB1 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:43:46 NONE 944121613 1524741
3 CDB1DEMO READ WRITE NO NORMAL 944121613 2 21:06:06 NONE 944121613 1524741

The CDB1DEMO PDB is opened READ WRITE. As my goal is to show refreshes, I need to have database with updates. To do it autonomously ;) I create a materialized view refreshing its timestamp every second.


21:06:15 //localhost/CDB1 SQL>create materialized view DEMO refresh complete start with (sysdate) next (sysdate+1/24/60/60) as select current_timestamp "refresh timestamp",current_scn "refresh scn" from v$database;
Materialized view DEMO created.

Here is how I’ll query this autonomous ;) materialized view, comparing the timestamp and SCN at the time of refresh with the current ones:

21:06:16 //localhost/CDB1 SQL>select demo.*,ora_rowscn,current_timestamp,(select current_scn from v$database) current_scn from demo;
 
refresh timestamp refresh scn ORA_ROWSCN CURRENT_TIMESTAMP CURRENT_SCN
------- --------- ------- --- ---------- ----------------- -----------
10-MAR-18 09.06.16.010529000 PM EUROPE/ZURICH 1524747 1524749 10-MAR-18 09.06.16.560146000 PM EUROPE/ZURICH 1524848

Refreshable PDB refreshing every minute

On CDB1 I create the user I’ll use for the remote clone: C##SYSOPER which needs either SYSOPER or CREATE PLUGGABLE DATABASE privilege.

21:06:17 //localhost/CDB1 SQL>connect sys/oracle@//localhost/CDB1 as sysdba
Connected.
21:06:17 //localhost/CDB1 SQL>grant create session, sysoper to C##SYSOPER identified by oracle1 container=all;
Grant succeeded.
 
21:06:18 //localhost/CDB1 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:43:46 NONE 944121613 1524907
3 CDB1DEMO READ WRITE NO NORMAL 944121613 2 21:06:06 NONE 944121613 1524907

On CDB2 I create a database link to connect to this CDB1 user.

21:06:18 //localhost/CDB1 SQL>connect sys/oracle@//localhost/CDB2 as sysdba
Connected.
21:06:18 //localhost/CDB2 SQL>create database link CDB1@SYSOPER connect to C##SYSOPER identified by oracle1 using '//localhost/CDB1'
Database link CDB1@SYSOPER created.
 
21:06:18 //localhost/CDB2 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:53:21 NONE 717451787 1522015

Here is the remote clone creating CDB2DEMO from CDB1DEMO, as a refreshable PDB, automatically refreshed every 1 minute (when it is in MOUNT):

21:06:18 //localhost/CDB2 SQL>create pluggable database CDB2DEMO from CDB1DEMO@CDB1@SYSOPER file_name_convert=('/CDB1/','/CDB2/','/CDB1DEMO/','/CDB2DEMO/') refresh mode every 1 minutes;
Pluggable database CDB2DEMO created.

This is not new, we got it in 12cR2 and If you go to Collaborate 18 in Las Vegas next month I’ll demo it: https://app.attendcollaborate.com/event/member/448410 , with all transportable tablespace and pluggable databases data movement.

From the alert.log you can see the clone and one first refresh:

21:06:28 //localhost/CDB2 SQL>host ssh oracle@localhost 'ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1 /u01/app/oracle/product/18.0.0/dbhome_1/bin/adrci exec="set home diag/rdbms/cdb2/CDB2; show alert -tail 30" '| grep --color -E "(^Completed:|^alter pluggable.*|^create pluggable.*|Media Recovery|onlined Undo|KILL|^Pluggable.*|^)"
2018-03-10 21:06:18.317000 +01:00
create pluggable database CDB2DEMO from CDB1DEMO@CDB1@SYSOPER file_name_convert=('/CDB1/','/CDB2/','/CDB1DEMO/','/CDB2DEMO/') refresh mode every 1 minutes keystore identified by *
Opatch validation is skipped for PDB CDB2DEMO (con_id=4)
2018-03-10 21:06:25.942000 +01:00
Endian type of dictionary set to little
****************************************************************
Pluggable Database CDB2DEMO with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000000000fb
****************************************************************
2018-03-10 21:06:27.413000 +01:00
Applying media recovery for pdb-4099 from SCN 1524926 to SCN 1525064
Remote log information: count-1
thr-1, seq-2, logfile-/u01/fast_recovery_area/CDB1/foreign_archivelog/CDB1DEMO/2018_03_10/o1_mf_1_2_212315018_.arc, los-1497297, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
max_pdb is 4
Media Recovery Log /u01/fast_recovery_area/CDB1/foreign_archivelog/CDB1DEMO/2018_03_10/o1_mf_1_2_212315018_.arc
Incomplete Recovery applied until change 1525064 time 03/10/2018 21:06:26
Media Recovery Complete (CDB2)
Completed: create pluggable database CDB2DEMO from CDB1DEMO@CDB1@SYSOPER file_name_convert=('/CDB1/','/CDB2/','/CDB1DEMO/','/CDB2DEMO/') refresh mode every 1 minutes keystore identified by *
alter pluggable database refresh

You can’t open this one in READ WRITE as it is refreshed with the redo from the source, as you see in the alert.log, but you can open it in READ ONLY to query it or to clone it further:

21:06:28 //localhost/CDB2 SQL>alter pluggable database CDB2DEMO open read only;
Pluggable database CDB2DEMO altered.
 
21:06:32 //localhost/CDB2 SQL>alter session set container=CDB2DEMO;
Session altered.
 
21:06:32 //localhost/CDB2 SQL>select demo.*,ora_rowscn,current_timestamp,(select current_scn from v$database) current_scn from demo;
 
refresh timestamp refresh scn ORA_ROWSCN CURRENT_TIMESTAMP CURRENT_SCN
------- --------- ------- --- ---------- ----------------- -----------
10-MAR-18 09.06.27.140229000 PM +01:00 1525071 1525072 10-MAR-18 09.06.32.565600000 PM EUROPE/ZURICH 1525100

if you look at the timestamps, you can see that it is in sync from the source as of the time of the end of creation. The alert.log shows that a refresh happened just after the creation completion.

21:06:39 //localhost/CDB2 SQL>host ssh oracle@localhost 'ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1 /u01/app/oracle/product/18.0.0/dbhome_1/bin/adrci exec="set home diag/rdbms/cdb2/CDB2; show alert -tail 30" '| grep --color -E "(^Completed:|^alter pluggable.*|^create pluggable.*|Media Recovery|onlined Undo|KILL|^Pluggable.*|^)"
2018-03-10 21:06:28.674000 +01:00
alter pluggable database CDB2DEMO open read only
Applying media recovery for pdb-4099 from SCN 1525064 to SCN 1525083
Remote log information: count-1
thr-1, seq-2, logfile-/u01/fast_recovery_area/CDB1/foreign_archivelog/CDB1DEMO/2018_03_10/o1_mf_1_2_212315018_.arc, los-1497297, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
2018-03-10 21:06:29.721000 +01:00
max_pdb is 4
Media Recovery Log /u01/fast_recovery_area/CDB1/foreign_archivelog/CDB1DEMO/2018_03_10/o1_mf_1_2_212315018_.arc
Incomplete Recovery applied until change 1525083 time 03/10/2018 21:06:28
Media Recovery Complete (CDB2)
Completed: alter pluggable database refresh
Autotune of undo retention is turned on.
2018-03-10 21:06:30.880000 +01:00
Undo initialization finished serial:0 start:4386360 end:4386360 diff:0 ms (0.0 seconds)
Database Characterset for CDB2DEMO is AL32UTF8
2018-03-10 21:06:32.305000 +01:00
Opening pdb with no Resource Manager plan active
Pluggable database CDB2DEMO opened read only
Completed: alter pluggable database CDB2DEMO open read only

The refresh can happen only when the PDB is in MOUNT. If it was opened (READ ONLY) for a long time, it will have to retreive some archive logs. This is why you can see FOREIGN ARCHIVED LOG in your recovery area in 12cR2.

So, basically here you have a kind of standby database at PDB level refreshed asynchonously, that you can open when you want:

21:06:32 //localhost/CDB2 SQL>alter session set container=CDB$ROOT;
Session altered.
21:06:32 //localhost/CDB2 SQL>alter pluggable database CDB2DEMO close;
Pluggable database CDB2DEMO altered.
21:06:39 //localhost/CDB2 SQL>alter pluggable database CDB2DEMO open read only;
Pluggable database CDB2DEMO altered.
21:06:40 //localhost/CDB2 SQL>alter session set container=CDB2DEMO;
Session altered.
21:06:40 //localhost/CDB2 SQL>select demo.*,ora_rowscn,current_timestamp,(select current_scn from v$database) current_scn from demo;
 
refresh timestamp refresh scn ORA_ROWSCN CURRENT_TIMESTAMP CURRENT_SCN
------- --------- ------- --- ---------- ----------------- -----------
10-MAR-18 09.06.27.140229000 PM +01:00 1525071 1525072 10-MAR-18 09.06.40.159432000 PM EUROPE/ZURICH 1525117
 
21:06:40 //localhost/CDB2 SQL>alter session set container=CDB$ROOT;
Session altered.

You will probably do a last refresh before opening it, but I left it on purpose to show that the switchover will ensure that they are in sync.

In 12cR2 if you want to ‘switchover’, you need to stop modifications to CDB1DEMO, alter CDB2DEMO in REFRESH NONE, then open it READ WRITE to be the new ‘primary’ after a last refresh, and drop CDB1DEMO to create it as a refreshable clone from CDB2DEMO. And this is where comes the new 18c PDB Switchover: one command to do all this.

Note that I leave the CDB2DEMO in opened (READ ONLY) or the switchover will fail with ORA-17628: Oracle error 65036 returned by remote Oracle server, ORA-65036: pluggable database not open in required mode.


21:06:40 //localhost/CDB2 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:53:21 NONE 717451787 1525131
4 CDB2DEMO READ ONLY NO REFRESHING 944121613 3 21:06:18 AUTO 1 1525083 717451787 1525131

PDB Switchover

As the goal is to change roles, I need a SYSOPER user and a database link on the other sides:

A user to connect to CDB2 which is the source we will refresh from after the switchover:

21:06:40 //localhost/CDB2 SQL>connect sys/oracle@//localhost/CDB2 as sysdba
Connected.
21:06:40 //localhost/CDB2 SQL>grant create session, sysoper to C##SYSOPER identified by oracle2 container=all;
Grant succeeded.

A database link from the CDB1 which will become the refreshing side:

21:06:41 //localhost/CDB2 SQL>connect sys/oracle@//localhost/CDB1 as sysdba
Connected.
21:06:41 //localhost/CDB1 SQL> create database link CDB2@SYSOPER connect to C##SYSOPER identified by oracle2 using '//localhost/CDB2'
Database link CDB2@SYSOPER created.

For the moment this side is in READ WRITE as it is the current ‘primary’

21:06:41 //localhost/CDB1 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:43:46 NONE 944121613 1525456
3 CDB1DEMO READ WRITE NO NORMAL 944121613 2 21:06:06 NONE 944121613 1525456

Here is the one-command refresh in 18c. We alter CDB1DEMO to be refreshable from CDB2DEMO, and we add ‘switchover’ to stop refreshing the remote PDB as it will be now the ‘primary’.

21:06:41 //localhost/CDB1 SQL>alter pluggable database CDB1DEMO refresh mode every 1 minutes from CDB2DEMO@CDB2@SYSOPER switchover;
Pluggable database CDB1DEMO altered.

The alert.log here from CDB1 shows ‘Deleted file’ as in a DROP PLUGGABLE DATABASE, then ‘created as UNUSABLE’ as in CREATE PLUGGABLE DATABASE, then ‘Applying media recovery’ as in refreshable clone:

21:06:59 //localhost/CDB1 SQL>host ssh oracle@localhost 'ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1 /u01/app/oracle/product/18.0.0/dbhome_1/bin/adrci exec="set home diag/rdbms/cdb1/CDB1; show alert -tail 30" '| grep --color -E "(^Completed:|^alter pluggable.*|^create pluggable.*|Media Recovery|onlined Undo|KILL|^Pluggable.*|^)"
2018-03-10 21:06:41.354000 +01:00
alter pluggable database CDB1DEMO refresh mode every 1 minutes from CDB2DEMO@CDB2@SYSOPER switchover
JIT: pid 11896 requesting stop
Buffer Cache flush started: 3
Buffer Cache flush finished: 3
While transitioning the pdb 3 to clean state, clearing all its abort bits in the control file.
Pluggable database CDB1DEMO closed
2018-03-10 21:06:45.734000 +01:00
Deleted file /u01/oradata/CDB1/CDB1DEMO/temp012018-03-10_12-43-46-436-PM.dbf
Deleted file /u01/oradata/CDB1/CDB1DEMO/undotbs01.dbf
Deleted file /u01/oradata/CDB1/CDB1DEMO/sysaux01.dbf
Deleted file /u01/oradata/CDB1/CDB1DEMO/system01.dbf
2018-03-10 21:06:48.199000 +01:00
Opatch validation is skipped for PDB CDB1DEMO (con_id=3)
2018-03-10 21:06:55.321000 +01:00
Endian type of dictionary set to little
****************************************************************
Pluggable Database CDB1DEMO with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000000000fb
****************************************************************
2018-03-10 21:06:59.142000 +01:00
Applying media recovery for pdb-4099 from SCN 1526441 to SCN 1526451
Remote log information: count-1
thr-1, seq-2, logfile-/u01/fast_recovery_area/CDB2/foreign_archivelog/CDB2DEMO/2018_03_10/o1_mf_1_2_2195948769_.arc, los-1497207, nxs-18446744073709551615
Media Recovery Start
Serial Media Recovery started
max_pdb is 4
Media Recovery Log /u01/fast_recovery_area/CDB2/foreign_archivelog/CDB2DEMO/2018_03_10/o1_mf_1_2_2195948769_.arc
Incomplete Recovery applied until change 1526451 time 03/10/2018 21:06:55
Media Recovery Complete (CDB1)
Completed: alter pluggable database CDB1DEMO refresh mode every 1 minutes from CDB2DEMO@CDB2@SYSOPER switchover
alter pluggable database refresh

The CDB1DEMO which was the ‘primary’ in READ WRITE is now the ‘standby’ in MOUNT, automatically refreshing every minute as mentioned in the switchover command:

21:07:00 //localhost/CDB1 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:43:46 NONE 944121613 1526489
3 CDB1DEMO MOUNTED REFRESHING 717451787 4 21:06:06 AUTO 1 1526451 944121613 1526489

As any refreshable clone, I can open it READ ONLY and query it:

21:07:00 //localhost/CDB1 SQL>alter pluggable database CDB1DEMO open read only;
Pluggable database CDB1DEMO altered.
 
21:07:02 //localhost/CDB1 SQL>alter session set container=CDB1DEMO;
Session altered.
 
21:07:02 //localhost/CDB1 SQL>select demo.*,ora_rowscn,current_timestamp,(select current_scn from v$database) current_scn from demo;
 
refresh timestamp refresh scn ORA_ROWSCN CURRENT_TIMESTAMP CURRENT_SCN
------- --------- ------- --- ---------- ----------------- -----------
10-MAR-18 09.06.41.175918000 PM +01:00 1525436 1525594 10-MAR-18 09.07.02.875782000 PM EUROPE/ZURICH 1526520

Look at the timestamp: the data is freshed as of the switchover. No data is lost: the transactions that were committed on the source at the time of switchover are applied on the clone.

Another switchover

I’m now doing a switchover on the opposite way. Same as before: the destination is READ ONLY and the source is READ WRITE:

21:07:20 //localhost/CDB1 SQL>connect sys/oracle@//localhost/CDB2 as sysdba
Connected.
21:07:20 //localhost/CDB2 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:53:21 NONE 717451787 1526851
4 CDB2DEMO READ WRITE NO NORMAL 944121613 3 21:06:18 NONE 717451787 1526851

Here is the switchover

21:07:20 //localhost/CDB2 SQL>alter pluggable database CDB2DEMO refresh mode every 1 minutes from CDB1DEMO@CDB1@SYSOPER switchover;
Pluggable database CDB2DEMO altered.

The alert.log shows an error at the end on the last refresh after the switchover:

21:07:33 //localhost/CDB2 SQL>host ssh oracle@localhost 'ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1 /u01/app/oracle/product/18.0.0/dbhome_1/bin/adrci exec="set home diag/rdbms/cdb2/CDB2; show alert -tail 30" '| grep --color -E "(^Completed:|^alter pluggable.*|^create pluggable.*|Media Recovery|onlined Undo|KILL|^Pluggable.*|^)"
2018-03-10 21:07:32.707000 +01:00
Incomplete Recovery applied until change 1527253 time 03/10/2018 21:07:31
Media Recovery Complete (CDB2)
Completed: alter pluggable database CDB2DEMO refresh mode every 1 minutes from CDB1DEMO@CDB1@SYSOPER switchover
alter pluggable database refresh
ORA-65376 signalled during: alter pluggable database refresh...
Errors in file /u01/app/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_j000_12081.trc:
ORA-12012: error on auto execute of job "SYS"."CDB2DEMO_510111146_REFRESH"
ORA-65376: unable to refresh the PDB
ORA-06512: at "SYS.DBMS_SQL", line 2995
ORA-06512: at line 1

The content of the tracefile doesn’t tell a lot more:

ORA-12012: error on auto execute of job "SYS"."CDB2DEMO_510111146_REFRESH"
at 0x7ffd3c59af38 placed jslv.c@1659
ORA-65376: unable to refresh the PDB
ORA-06512: at "SYS.DBMS_SQL", line 2995
ORA-06512: at line 1

However, the switchover was ok, so nothing was lost and I’ll be able to run new refreshes later.

The CDB2DEMO is now the ‘standby’ again:

21:07:33 //localhost/CDB2 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:53:21 NONE 717451787 1527275
4 CDB2DEMO MOUNTED REFRESHING 944121613 3 21:06:18 AUTO 1 1527253 717451787 1527275

The CDB1 DEMO is now the ‘primary’ in READ WRITE':

21:07:34 //localhost/CDB2 SQL>connect sys/oracle@//localhost/CDB1 as sysdba
Connected.
21:07:34 //localhost/CDB1 SQL>pdbs
21:07:34 //localhost/CDB1 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:43:46 NONE 944121613 1527257
3 CDB1DEMO READ WRITE NO NORMAL 717451787 4 21:06:06 NONE 944121613 1527257

The standby is refreshing, containing the data as-of the time of switchover, until ne next refresh:

21:07:34 //localhost/CDB1 SQL>connect sys/oracle@//localhost/CDB2 as sysdba
Connected.
21:07:35 //localhost/CDB2 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:53:21 NONE 717451787 1527276
4 CDB2DEMO MOUNTED REFRESHING 944121613 3 21:06:18 AUTO 1 1527253 717451787 1527276
 
21:07:35 //localhost/CDB2 SQL>alter session set container=CDB2DEMO;
Session altered.
 
21:07:36 //localhost/CDB2 SQL>select demo.*,ora_rowscn,current_timestamp,(select current_scn from v$database) current_scn from demo;
 
refresh timestamp refresh scn ORA_ROWSCN CURRENT_TIMESTAMP CURRENT_SCN
------- --------- ------- --- ---------- ----------------- -----------
10-MAR-18 09.07.20.108426000 PM +01:00 1526838 1526839 10-MAR-18 09.07.36.114424000 PM EUROPE/ZURICH 1527282

I’ve checked the state the next day. The ‘primary’ PDB had its materlialized view still refreshing every second:

12:44:48 //localhost/CDB2 SQL>connect sys/oracle@//localhost/CDB1 as sysdba
Connected.
12:44:48 //localhost/CDB1 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:43:46 NONE 944121613 1767409
4 CDB1DEMO READ WRITE NO NORMAL 717451787 3 12:43:31 NONE 944121613 1767409
 
12:44:48 //localhost/CDB1 SQL>alter session set container=CDB1DEMO;
Session altered.
 
12:44:49 //localhost/CDB1 SQL>select demo.*,ora_rowscn,current_timestamp,(select current_scn from v$database) current_scn from demo;
 
refresh timestamp refresh scn ORA_ROWSCN CURRENT_TIMESTAMP CURRENT_SCN
------- --------- ------- --- ---------- ----------------- -----------
11-MAR-18 12.44.33.078430000 PM +01:00 1766975 1766976 11-MAR-18 12.44.49.085200000 PM EUROPE/ZURICH 1767410

And the ‘standby’ PDB is updating every minute:

12:44:49 //localhost/CDB1 SQL>connect sys/oracle@//localhost/CDB2 as sysdba
Connected.
 
12:44:49 //localhost/CDB2 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:53:21 NONE 717451787 1767422
3 CDB2DEMO MOUNTED REFRESHING 944121613 4 12:43:42 AUTO 1 1767399 717451787 1767422
 
12:44:49 //localhost/CDB2 SQL>alter session set container=CDB2DEMO;
Session altered.
 
12:44:49 //localhost/CDB2 SQL>alter pluggable database open read only;
Pluggable database OPEN altered.
 
12:44:50 //localhost/CDB2 SQL>select demo.*,ora_rowscn,current_timestamp,(select current_scn from v$database) current_scn from demo;
 
refresh timestamp refresh scn ORA_ROWSCN CURRENT_TIMESTAMP CURRENT_SCN
------- --------- ------- --- ---------- ----------------- -----------
11-MAR-18 12.44.33.078430000 PM +01:00 1766975 1766976 11-MAR-18 12.44.50.205050000 PM EUROPE/ZURICH 1767432

But no failover

When we are talking about ‘standby’ at PDB level, we want to be able to do a failover. Of course, we accept to loose some transactions as the refresh is every minutes, but 1 minute RPO is still an interesting solution for a feature that is available in all editions.

However, as in 12.2.0.1, this doesn’t work because you cannot alter ‘standby’ PDB to REFRESH MODE NONE when the ‘primary’ is not available.

Here is my ‘standby’ PDB CDB2DEMO:

12:44:50 //localhost/CDB2 SQL>connect sys/oracle@//localhost/CDB2 as sysdba
Connected.
 
12:44:50 //localhost/CDB2 SQL>select con_id,name con_name,open_mode,restricted,status,foreign_cdb_dbid,foreign_pdb_id,to_char(creation_time, 'hh24:mi:ss') creation_time,refresh_mode,refresh_interval,last_refresh_scn,cdb_dbid,current_scn from v$pdbs right join dba_pdbs using(con_id,creation_time,dbid) cross join (select dbid cdb_dbid,current_scn from v$database) order by pdb_id
 
CON_ID CON_NAME OPEN_MODE RESTRICTED STATUS FOREIGN_CDB_DBID FOREIGN_PDB_ID CREATION_TIME REFRESH_MODE REFRESH_INTERVAL LAST_REFRESH_SCN CDB_DBID CURRENT_SCN
------ -------- --------- ---------- ------ ---------------- -------------- ------------- ------------ ---------------- ---------------- -------- -----------
2 PDB$SEED READ ONLY NO NORMAL 1214140 2 12:53:21 NONE 717451787 1767433
3 CDB2DEMO READ ONLY NO REFRESHING 944121613 4 12:43:42 AUTO 1 1767405 717451787 1767433

I stop the listener which listens for the ‘primary’ CDB1DEMO:

12:44:50 //localhost/CDB2 SQL>host lsnrctl stop
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 11-MAR-2018 12:44:50
 
Copyright (c) 1991, 2017, Oracle. All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

You can see in the alert.log that the refresh fails (it is running from a scheduler job):

Errors in file /u01/app/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_j000_25443.trc:
ORA-12012: error on auto execute of job "SYS"."CDB2DEMO_1159316120_REFRESH"
ORA-17627: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-17629: Cannot connect to the remote database server
ORA-06512: at "SYS.DBMS_SQL", line 2995
ORA-06512: at line 1

Then, an attempt to stop the refreshing mode of the ‘standby’ fails with ‘Cannot connect to the remote database server':

12:44:50 //localhost/CDB2 SQL>alter pluggable database CDB2DEMO close;
Pluggable database CDB2DEMO altered.
 
12:44:50 //localhost/CDB2 SQL>alter pluggable database CDB2DEMO refresh mode none;
 
Error starting at line : 180 File @ ~/PDB-switchover.sql
In command -
alter pluggable database CDB2DEMO refresh mode none
Error report -
ORA-17627: ORA-12541: TNS:no listener
ORA-17629: Cannot connect to the remote database server
17627. 00000 - "%s"
*Cause: An error returned by OCI while sending/receiving message from remote instance
*Action: Look at error message and take appropriate action or contact Oracle Support Services for further assistance

So nothing new here about failover. I already explained how to do something like a failover by cloning the standby, which can be a snapshot clone to be faster: https://blog.dbi-services.com/12cr2-pdb-refresh-as-a-poor-man-standby/.

Note that this new feature is leashed to specific platforms only – Oracle Cloud PaaS and Oracle Exadata machine, so most of Oracle customers paying for software update will not be able to use it. However, Don’t worry, you can do the same with a few commands, as in 12cR2.

 

Cet article 18c PDB switchover est apparu en premier sur Blog dbi services.

Enabled, Accepted, Fixed SQL Plan Baselines

Fri, 2018-03-09 15:44

When the documentation is not always clear, I prefer to build a test case to be sure about the behavior in different context and different versions. Here is a test on SQL Plan Management to show which plan is chosen among the different states of SQL Plan Baselines: Enabled, Accepted, Fixed. Thanks to Oracle ACE program, I have some Oracle Cloud credits to quickly provision a database, so I tested that on Oracle 18c.

For this test, I’ve created a table:

create table DEMO as select rownum n from xmltable('1 to 10000');

with 8 indexes:

exec for i in 1..8 loop execute immediate 'create index DEMO'||i||' on DEMO(n,'||i||')'; end loop;

and a procedure to query it several times, setting random costs for the indexes, with only one cheapest:

create or replace procedure runplans(n number) as
dummy number;
begin
-- run all this 30 times
for k in 1..30 loop
run from index DEMO1 to DEMOt with one of them cheaper each time
for t in 1..n loop
-- set random cost for all indexes
for i in 1..8 loop dbms_stats.set_index_stats(user,'DEMO'||i,indlevel=>round(dbms_random.value(10,100)),no_invalidate=>true); end loop;
-- set cheap cost for index DEMOt
dbms_stats.set_index_stats(user,'DEMO'||t,indlevel=>1,no_invalidate=>true);
-- change some parameters to parse new child
execute immediate 'alter session set optimizer_index_caching='||(t*8+k);
-- query with an index hint but not specifying the index so the cheaper is chosen
select /*+ index(DEMO) */ n into dummy from DEMO where n=1;
end loop;
end loop;
end;
/

So you understand the idea: have 8 possible execution plans, with one cheaper than the others. And the goal is to see which one is chosen depending of the state of the SQL Plan Baseline.

I will play with the baselines and will display the cursor execution with the following SQLcl alias:

SQL> alias sqlpb=select sql_id,plan_hash_value,sum(executions),sql_plan_baseline,id,object_name from v$sql join v$sql_plan using(sql_id,plan_hash_value,child_number) where sql_text like 'SELECT % FROM DEMO%' and id=1 group by sql_id,plan_hash_value,sql_plan_baseline,id,object_name order by 3;

So, I call my procedure to run the query 30 times when index DEMO1 is the cheapest:

SQL> exec runplans(1)
PL/SQL procedure successfully completed.

Here is my alias to show the 30 executions using DEMO1 (object_name from v$sqlplan):

SQL> sqlpb
 
SQL_ID PLAN_HASH_VALUE SUM(EXECUTIONS) SQL_PLAN_BASELINE ID OBJECT_NAME
------------- --------------- --------------- -------------------- -- -----------
gqnkn2d17zjvv 3739632713 30 1 DEMO1

I load this to SPM

SQL> exec dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id=>'gqnkn2d17zjvv'));
PL/SQL procedure successfully completed.

Here is my SQL Plan Baseline, enabled and accepted:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS
------------------------------ ------- -------- ----- ----------
SQL_PLAN_dcc9d14j7k1vu97e16a35 YES YES NO 30

Now I run my procedure to run 30 times the cursor and for each, 8 times with one different index being the cheapest:

SQL> exec runplans(8)
PL/SQL procedure successfully completed.

So all executions have used the only one SQL Plan Baseline which is enabled and accepted:

SQL> sqlpb
 
SQL_ID PLAN_HASH_VALUE SUM(EXECUTIONS) SQL_PLAN_BASELINE ID OBJECT_NAME
------------- --------------- --------------- --------------------------------- -- -----------
gqnkn2d17zjvv 3739632713 232 SQL_PLAN_dcc9d14j7k1vu97e16a35 1 DEMO1

And the other plans (because only 8 of them had this DEMO1 plan being the cheapest) were loaded, enabled but not accepted:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS
------------------------------ ------- -------- ----- ----------
SQL_PLAN_dcc9d14j7k1vu287d1344 YES NO NO 0
SQL_PLAN_dcc9d14j7k1vu452ab005 YES NO NO 0
SQL_PLAN_dcc9d14j7k1vu4564f9cd YES NO NO 0
SQL_PLAN_dcc9d14j7k1vu4cdc9ee7 YES NO NO 0
SQL_PLAN_dcc9d14j7k1vu5353a77e YES NO NO 0
SQL_PLAN_dcc9d14j7k1vu97e16a35 YES YES NO 30
SQL_PLAN_dcc9d14j7k1vuc6a3f323 YES NO NO 0
SQL_PLAN_dcc9d14j7k1vufb8f9e5a YES NO NO 0

Now, I change the status of the baselines to get all combinations of enabled, fixed and accepted, and in addition to that change the plan name to tne line of plan which differs:

SQL> begin
2 for i in (select rownum n,plan_name from dba_sql_plan_baselines) loop
3 if i.n in (2,4,6,8) then dbms_output.put_line(dbms_spm.evolve_sql_plan_baseline(plan_name=>i.plan_name,verify=>'no',commit=>'yes')); end if;
4 if i.n in (1,2,5,6) then dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'enabled',attribute_value=>'no')); end if;
5 if i.n in (5,6,7,8) then dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'fixed',attribute_value=>'yes')); end if;
6 for p in ( select plan_table_output from dbms_xplan.display_sql_plan_baseline(plan_name=>i.plan_name,format=>'basic') where plan_table_output like '%| DEMO%' ) loop
7 dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'plan_name',attribute_value=>p.plan_table_output));
8 end loop;
9 end loop;
10 end;
11 /

So here they are, with their new name:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS
---------------------------------- ------- -------- ----- ----------
| 1 | INDEX RANGE SCAN| DEMO4 | NO NO NO 0
| 1 | INDEX RANGE SCAN| DEMO3 | NO YES NO 0
| 1 | INDEX RANGE SCAN| DEMO2 | YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO6 | YES YES NO 0
| 1 | INDEX RANGE SCAN| DEMO5 | NO NO YES 0
| 1 | INDEX RANGE SCAN| DEMO1 | NO YES YES 30
| 1 | INDEX RANGE SCAN| DEMO8 | YES NO YES 0
| 1 | INDEX RANGE SCAN| DEMO7 | YES YES YES 0

Fixed plans have priority

I flush the shared pool and run my 240 statements again:

SQL> alter system flush shared_pool;
System FLUSH altered
SQL> exec runplans(8)
PL/SQL procedure successfully completed.

Here is the result in V$SQL, only one plan used for all those executions:

SQL> sqlpb
 
SQL_ID PLAN_HASH_VALUE SUM(EXECUTIONS) SQL_PLAN_BASELINE ID OBJECT_NAME
------------- --------------- --------------- ------------------------------------- -- -----------
gqnkn2d17zjvv 1698325646 240 | 1 | INDEX RANGE SCAN| DEMO7 | 1 DEMO7

The only possible plans are those that are fixed and accepted, which are the DEMO6 and DEMO7 ones. However, fixed plans have a priority, so even when the CBO came with the DEMO6 plan it was not used. When there are fixed enabled accepted SQL Plan Baseline, those are the only one considered.

Enabled and Accepted are used

Now setting all fixed attribute to no:

SQL> begin
2 for i in (select rownum n,plan_name from dba_sql_plan_baselines) loop
3 dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'fixed',attribute_value=>'no'));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
 
SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS
---------------------------------- ------- -------- ----- ----------
| 1 | INDEX RANGE SCAN| DEMO4 | NO NO NO 0
| 1 | INDEX RANGE SCAN| DEMO3 | NO YES NO 0
| 1 | INDEX RANGE SCAN| DEMO2 | YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO6 | YES YES NO 0
| 1 | INDEX RANGE SCAN| DEMO5 | NO NO NO 0
| 1 | INDEX RANGE SCAN| DEMO1 | NO YES NO 30
| 1 | INDEX RANGE SCAN| DEMO8 | YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO7 | YES YES NO 0

Here is another run:

SQL> alter system flush shared_pool;
System FLUSH altered.
SQL> exec runplans(8)
PL/SQL procedure successfully completed.
 
SQL> sqlpb
 
SQL_ID PLAN_HASH_VALUE SUM(EXECUTIONS) SQL_PLAN_BASELINE ID OBJECT_NAME
------------- --------------- --------------- ------------------------------------- -- -----------
gqnkn2d17zjvv 1698325646 95 | 1 | INDEX RANGE SCAN| DEMO7 | 1 DEMO7
gqnkn2d17zjvv 3449379882 145 | 1 | INDEX RANGE SCAN| DEMO6 | 1 DEMO6

Now that there are no fixed plans taking the priority, all enabled and accepted plans are possible, but only them.

All possible plans in the baseline but none enabled and accepted

Then what happens when all possible plans are in the SQL Plan Baseline but none of them are both enabled and accepted?

SQL> begin
2 for i in (select rownum n,plan_name from dba_sql_plan_baselines where accepted='YES') loop
3 dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'enabled',attribute_value=>'no'));
4 end loop;
5 end;
6 /
 
SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS
---------------------------------- ------- -------- ----- ----------
| 1 | INDEX RANGE SCAN| DEMO4 | NO NO NO 0
| 1 | INDEX RANGE SCAN| DEMO3 | NO YES NO 0
| 1 | INDEX RANGE SCAN| DEMO2 | YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO6 | NO YES NO 0
| 1 | INDEX RANGE SCAN| DEMO5 | NO NO NO 0
| 1 | INDEX RANGE SCAN| DEMO1 | NO YES NO 30
| 1 | INDEX RANGE SCAN| DEMO8 | YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO7 | NO YES NO 0

So all combinations of indexes are there (and my query forces index access with a hint) but none are accepted and enabled.

SQL> alter system flush shared_pool;
System FLUSH altered.
SQL> exec runplans(8)
PL/SQL procedure successfully completed.
 
SQL> sqlpb
SQL_ID PLAN_HASH_VALUE SUM(EXECUTIONS) SQL_PLAN_BASELINE ID OBJECT_NAME
------------- --------------- --------------- -------------------- -- -----------
gqnkn2d17zjvv 3739632713 8 1 DEMO1
gqnkn2d17zjvv 4234411015 16 1 DEMO2
gqnkn2d17zjvv 2199479965 24 1 DEMO3
gqnkn2d17zjvv 1698325646 30 1 DEMO7
gqnkn2d17zjvv 3449379882 30 1 DEMO6
gqnkn2d17zjvv 2144220082 30 1 DEMO5
gqnkn2d17zjvv 918903766 30 1 DEMO4
gqnkn2d17zjvv 39208404 72 1 DEMO8

When there are no enabled and accepted plans, then anything is possible and each execution keeps the one the CBO came with.

None enabled and accepted, but new plan possible

Now, in order to have a new plan possible I’ll still run the same query but after dropping all indexes.

SQL> exec for i in 1..8 loop execute immediate 'drop index DEMO'||i; end loop;
PL/SQL procedure successfully completed.

I’ve run the same as before but without the dbms_stats calls.

Here all executions have run with the only possible plan: a full table scan:

SQL> select sql_id,plan_hash_value,sum(executions),sql_plan_baseline,id,object_name from v$sql join v$sql_plan using(sql_id,plan_hash_value,child_number) where sql_text like 'SELECT % FROM DEMO%' and id=1 group by sql_id,plan_hash_value,sql_plan_baseline,id,object_name order by 3
 
SQL_ID PLAN_HASH_VALUE SUM(EXECUTIONS) SQL_PLAN_BASELINE ID OBJECT_NAME
------------- --------------- --------------- -------------------- -- -----------
gqnkn2d17zjvv 4000794843 29 1 DEMO

this plan has been added, enabled but not accepted, to the SQL Plan Baseline:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME ENABLED ACCEPTED FIXED EXECUTIONS
------------- --------------- --------------- -------------------- -- -----------
| 1 | INDEX RANGE SCAN| DEMO4 | NO NO NO 0
| 1 | INDEX RANGE SCAN| DEMO3 | NO YES NO 0
| 1 | INDEX RANGE SCAN| DEMO2 | YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO6 | NO YES NO 0
| 1 | INDEX RANGE SCAN| DEMO5 | NO NO NO 0
SQL_PLAN_dcc9d14j7k1vu838f84a8 YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO1 | NO YES NO 30
| 1 | INDEX RANGE SCAN| DEMO8 | YES NO NO 0
| 1 | INDEX RANGE SCAN| DEMO7 | NO YES NO 0

Not accepted means that it cannot be used. But as there are no other plan possible, it will be used anyway.

In summary:
Fixed plans are like telling to the optimizer: You must use one of these.
Enabled accepted plans are like telling the optimizer: You should use one of these.
Disabled or non-accepted plans are like telling the optimizer: Try to find another plan.
The optimizer will always come with a plan, so if the rules cannot be applied, the optimizer best-estimated plan is used. It may be a non-enabled or non-accepted plan. Or it can be a new plan, which will then be added as non accepted.

 

Cet article Enabled, Accepted, Fixed SQL Plan Baselines est apparu en premier sur Blog dbi services.

Parallel pg_dump is slow by default?

Fri, 2018-03-09 00:25

Short answer: Yes, it is. Being at a customer the last days we wanted to parallel pg_dump a 2TB database. We were quite surprised that it was quite slow and it was not immediately clear why it was. Well, the answer is in the documentation: When you go for parallel dumps you need to use the directory format and this comes with: “This format is compressed by default and also supports parallel dumps.”. Compression takes time, so the question was if we could disable compression which was not clear from the statement: Does “compressed by default” mean that it per default is compressed and you can not change that or does it just mean it is the default, but you can change it?

As always, lets setup a short test case:

postgres=# create table dmp1 as 
           select a,a::varchar b,now() c 
             from generate_series ( 1, 1000000) a;
SELECT 1000000
postgres=# create table dmp2 as select * from dmp1;
SELECT 1000000
postgres=# create table dmp3 as select * from dmp1;
SELECT 1000000
postgres=# create table dmp4 as select * from dmp1;
SELECT 1000000
postgres=# \d dmp*
                        Table "public.dmp1"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 a      | integer                  |           |          | 
 b      | character varying        |           |          | 
 c      | timestamp with time zone |           |          | 

                        Table "public.dmp2"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 a      | integer                  |           |          | 
 b      | character varying        |           |          | 
 c      | timestamp with time zone |           |          | 

                        Table "public.dmp3"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 a      | integer                  |           |          | 
 b      | character varying        |           |          | 
 c      | timestamp with time zone |           |          | 

                        Table "public.dmp4"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 a      | integer                  |           |          | 
 b      | character varying        |           |          | 
 c      | timestamp with time zone |           |          | 

We have four tables each containing 1’000’000 rows. When we use pg_dump in parallel with the default it looks like this:

postgres@pgbox:/home/postgres/ [PG10] mkdir /var/tmp/dmp
postgres@pgbox:/home/postgres/ [PG10] time pg_dump --format=d --jobs=4 --file=/var/tmp/dmp/ postgres

real	0m2.788s
user	0m2.459s
sys	0m0.597s
postgres@pgbox:/home/postgres/ [PG10] ls -la /var/tmp/dmp/
total 19528
drwxr-xr-x. 2 postgres postgres    4096 Mar  9 07:16 .
drwxrwxrwt. 4 root     root          51 Mar  9 07:15 ..
-rw-r--r--. 1 postgres postgres      25 Mar  9 07:16 3113.dat.gz
-rw-r--r--. 1 postgres postgres      25 Mar  9 07:16 3114.dat.gz
-rw-r--r--. 1 postgres postgres      25 Mar  9 07:16 3115.dat.gz
-rw-r--r--. 1 postgres postgres 4991138 Mar  9 07:16 3116.dat.gz
-rw-r--r--. 1 postgres postgres 4991138 Mar  9 07:16 3117.dat.gz
-rw-r--r--. 1 postgres postgres 4991138 Mar  9 07:16 3118.dat.gz
-rw-r--r--. 1 postgres postgres 4991138 Mar  9 07:16 3119.dat.gz
-rw-r--r--. 1 postgres postgres    5819 Mar  9 07:16 toc.dat

As stated in the documentation the result is compressed. When speed is more important then the size on disk you can however disable the compression:

postgres@pgbox:/home/postgres/ [PG10] rm -rf /var/tmp/dmp/*
postgres@pgbox:/home/postgres/ [PG10] time pg_dump --format=d --jobs=4 --file=/var/tmp/dmp/ --compress=0 postgres

real	0m5.357s
user	0m0.065s
sys	0m0.460s
postgres@pgbox:/home/postgres/ [PG10] ls -la /var/tmp/dmp/
total 171040
drwxr-xr-x. 2 postgres postgres     4096 Mar  9 07:18 .
drwxrwxrwt. 4 root     root           51 Mar  9 07:15 ..
-rw-r--r--. 1 postgres postgres        5 Mar  9 07:18 3113.dat
-rw-r--r--. 1 postgres postgres        5 Mar  9 07:18 3114.dat
-rw-r--r--. 1 postgres postgres        5 Mar  9 07:18 3115.dat
-rw-r--r--. 1 postgres postgres 43777797 Mar  9 07:18 3116.dat
-rw-r--r--. 1 postgres postgres 43777797 Mar  9 07:18 3117.dat
-rw-r--r--. 1 postgres postgres 43777797 Mar  9 07:18 3118.dat
-rw-r--r--. 1 postgres postgres 43777797 Mar  9 07:18 3119.dat
-rw-r--r--. 1 postgres postgres     5819 Mar  9 07:18 toc.dat

In my case it got slower than the compressed dump but this is because I do not really have fast disks on my little VM. When you have a good storage solution disabling compression should bring you more speed.

 

Cet article Parallel pg_dump is slow by default? est apparu en premier sur Blog dbi services.

MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error

Thu, 2018-03-08 08:55

As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys.

In our example, we have the following parent table in a MySQL 5.7.21 server:

mysqld3-(root@localhost) [sakila]> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

and a foreign key is defined on the child table by using the “FOREIGN KEY… REFERENCES” syntax:

mysqld3-(root@localhost) [sakila]> show create table film_actor\G
*************************** 1. row ***************************
       Table: film_actor
Create Table: CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

The “ON UPDATE CASCADE” clause means that if we update values in the parent table (‘actor’ in our example),

mysqld3-(root@localhost) [sakila]> select * from actor where actor_id=1;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from film_actor where actor_id=1;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|        1 |       1 | 2006-02-15 05:05:03 |
|        1 |      23 | 2006-02-15 05:05:03 |
|        1 |      25 | 2006-02-15 05:05:03 |
|        1 |     106 | 2006-02-15 05:05:03 |
|        1 |     140 | 2006-02-15 05:05:03 |
|        1 |     166 | 2006-02-15 05:05:03 |
|        1 |     277 | 2006-02-15 05:05:03 |
|        1 |     361 | 2006-02-15 05:05:03 |
|        1 |     438 | 2006-02-15 05:05:03 |
|        1 |     499 | 2006-02-15 05:05:03 |
|        1 |     506 | 2006-02-15 05:05:03 |
|        1 |     509 | 2006-02-15 05:05:03 |
|        1 |     605 | 2006-02-15 05:05:03 |
|        1 |     635 | 2006-02-15 05:05:03 |
|        1 |     749 | 2006-02-15 05:05:03 |
|        1 |     832 | 2006-02-15 05:05:03 |
|        1 |     939 | 2006-02-15 05:05:03 |
|        1 |     970 | 2006-02-15 05:05:03 |
|        1 |     980 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+
19 rows in set (0.00 sec)
mysqld3-(root@localhost) [sakila]> update actor set actor_id=300 where actor_id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

then this will cause an automatic update of the matching rows in the child table (‘film_actor’):

mysqld3-(root@localhost) [sakila]> select * from actor where actor_id=1;
Empty set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from film_actor where actor_id=1;
Empty set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from actor where actor_id=300;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      300 | PENELOPE   | GUINESS   | 2018-02-07 15:41:45 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from film_actor where actor_id=300;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|      300 |       1 | 2006-02-15 05:05:03 |
|      300 |      23 | 2006-02-15 05:05:03 |
|      300 |      25 | 2006-02-15 05:05:03 |
|      300 |     106 | 2006-02-15 05:05:03 |
|      300 |     140 | 2006-02-15 05:05:03 |
|      300 |     166 | 2006-02-15 05:05:03 |
|      300 |     277 | 2006-02-15 05:05:03 |
|      300 |     361 | 2006-02-15 05:05:03 |
|      300 |     438 | 2006-02-15 05:05:03 |
|      300 |     499 | 2006-02-15 05:05:03 |
|      300 |     506 | 2006-02-15 05:05:03 |
|      300 |     509 | 2006-02-15 05:05:03 |
|      300 |     605 | 2006-02-15 05:05:03 |
|      300 |     635 | 2006-02-15 05:05:03 |
|      300 |     749 | 2006-02-15 05:05:03 |
|      300 |     832 | 2006-02-15 05:05:03 |
|      300 |     939 | 2006-02-15 05:05:03 |
|      300 |     970 | 2006-02-15 05:05:03 |
|      300 |     980 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+

 

Sometimes, when we must drop an InnoDB table in MySQL, we could encounter the following error due to foreign keys:

mysqld3-(root@localhost) [sakila]> drop table actor;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

 

In this example, the ‘actor’ table is referenced by the ‘film_actor’ one:

mysqld3-(root@localhost) [sakila]> SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA = 'sakila'
    -> AND REFERENCED_TABLE_NAME = 'actor';
+------------+-------------+---------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME     | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+---------------------+-----------------------+------------------------+
| film_actor | actor_id    | fk_film_actor_actor | actor                 | actor_id               |
+------------+-------------+---------------------+-----------------------+------------------------+
1 row in set (0.01 sec)

 

This foreign key constraint let data being consistent over different tables and that’s also the reason why we could not drop the parent table.
We can find this same information and the error cause displaying the state of the InnoDB storage engine through the “SHOW ENGINE INNODB STATUS” command:

mysqld3-(root@localhost) [sakila]> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-02-07 15:44:34 0x7fb734174700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 84 srv_active, 0 srv_shutdown, 85720 srv_idle
srv_master_thread log flush and writes: 85803
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1607
OS WAIT ARRAY INFO: signal count 1552
RW-shared spins 0, rounds 757, OS waits 384
RW-excl spins 0, rounds 342, OS waits 11
RW-sx spins 2, rounds 60, OS waits 1
Spin rounds per wait: 757.00 RW-shared, 342.00 RW-excl, 30.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-02-07 15:42:45 0x7fb734174700  Cannot drop table `sakila`.`actor`
because it is referenced by `sakila`.`film_actor`
...

 

To avoid these constraint errors during table deletion, there are different solutions:
– Drop table in the correct order (child table first, parent table as the last one)
– In case of a loop in foreign keys, remove this loop and redefine tables structure before dropping tables
– You can also temporarily set “FOREIGN_KEY_CHECKS=0”, drop the table and put again “FOREIGN_KEY_CHECKS=1”, but I don’t recommend using this method (especially in a production environment!)

 

Cet article MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error est apparu en premier sur Blog dbi services.

EDB Failover Manager 3.0 and PostgreSQL 10.1

Wed, 2018-03-07 10:31

Edb Failover Manager edb-efm30 is now released and supports PostgreSQL 10.1.
Efm is an EDB tool which can be used to perform a switchover and a failover in a PostgreSQL standby environment.
In this blog we are going to see how we can install and configure efm in a standby environment. We suppose that the standby is already confugured and is running. We also have not configured any VIP. I just use 3 virtual machines to test. Below the configuration we are using 3 servers with Oracle Linux 7.2

pgservertools: 192.168.56.30 which is the witness
pgserver1: 192.168.56.36 which is the primary server
pgserver2: 192.168.56.37 which is the standby
config
EFM must be installed on both 3 nodes.To install EDB EFM,I used the rpm provided by EnterpriseDB. Note that you will need an Edb account.We will show the installation on only one node, but is the same for both nodes.

[root@host tmp]# wget http://yum.enterprisedb.com/edbrepos/edb-repo-latest.noarch.rpm
[root@host tmp]# yum localinstall -y edb-repo-latest.noarch.rpm

After we have to enable the corresponding edb repository (You will need a login and password)

[root@pgservertools yum.repos.d]# cat /etc/yum.repos.d/edb.repo
[edbas10]
name=EnterpriseDB Advanced Server 10 $releasever - $basearch
baseurl=http://login:password@yum.enterprisedb.com/10/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/ENTERPRISEDB-GPG-KEY

And then we can search for the package

[root@pgservertools ~]# yum search efm
Loaded plugins: langpacks, ulninfo
=============================== N/S matched: efm ===============================
edb-efm30.x86_64 : EnterpriseDB Failover Manager
efm20.x86_64 : EnterpriseDB Failover Manager
efm21.x86_64 : EnterpriseDB Failover Manager

And then install it (ouputs are truncated)

[root@pgservertools ~]# yum install edb-efm30.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package edb-efm30.x86_64 0:3.0.0-1.rhel7 will be installed
--> Finished Dependency Resolution
…
…
Installed:
  edb-efm30.x86_64 0:3.0.0-1.rhel7
Complete!

efm requires also openjdk. So we also have installed openjdk 1.8 on all nodes:

root@host tmp]# yum install java-1.8.0-openjdk.x86_64

To manage efm we create a dedicated user in the primary cluster

postgres=# create user efm with login password 'root' superuser;
CREATE ROLE
postgres=#

We have to add following entries to pg_hba.conf of all databases clusters to allow connection for user efm.

##for efm
host  postgres    efm  192.168.56.36/32     md5
host  postgres    efm  192.168.56.37/32     md5
host  postgres    efm  192.168.56.30/32     md5

The configuration of efm consists of editing 2 main configuration files: efm.nodes and efm.properties. In my case these files are located in /etc/edb/efm-3.0. There are already two sample files that we can copy and then edit.
First we need to encrypt the password of user efm and after we have to configure efm.nodes and efm.properties on both nodes.

[root@pgserver1 efm-3.0]# /usr/edb/efm-3.0/bin/efm encrypt efm
This utility will generate an encrypted password for you to place in your
EFM cluster property file.
Please enter the password and hit enter:
Please enter the password again to confirm:
The encrypted password is: ff7f041651e5e864013c1102d26a5e08
Please paste this into your cluster properties file.
        db.password.encrypted= ff7f041651e5e864013c1102d26a5e08

Below we show the contents of our two configuration files
On pgserver1 which is the primary
efm.nodes

root@pgserver1 101]# cat /etc/edb/efm-3.0/efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
192.168.56.30:9998 192.168.56.37:9998
[root@pgserver1 101]#

efm.properties

[root@pgserver1 101]# cat /etc/edb/efm-3.0/efm.properties | grep -v ^#
db.user=efm
db.password.encrypted=ff7f041651e5e864013c1102d26a5e08
db.port=5436
db.database=postgres
db.service.owner=postgres
db.service.name=
db.bin=/u01/app/postgres/product/10/db_1/bin
db.recovery.conf.dir=/u90/mydata/101
jdbc.sslmode=disable
user.email=xxx@xxx.fr
script.notification=
bind.address=192.168.56.36:9998
admin.port=9999
is.witness=false
local.period=10
local.timeout=60
local.timeout.final=10
remote.timeout=10
node.timeout=50
stop.isolated.master=false
pingServerIp=192.168.56.30
pingServerCommand=/bin/ping -q -c3 -w5
auto.allow.hosts=false
db.reuse.connection.count=0
auto.failover=true
auto.reconfigure=true
promotable=true
minimum.standbys=0
recovery.check.period=2
auto.resume.period=0
script.fence=
script.post.promotion=
script.resumed=
script.db.failure=
script.master.isolated=
script.remote.pre.promotion=
script.remote.post.promotion=
script.custom.monitor=
custom.monitor.interval=
custom.monitor.timeout=
custom.monitor.safe.mode=
sudo.command=sudo
sudo.user.command=sudo -u %u
log.dir=/var/log/efm-3.0
jgroups.loglevel=INFO
efm.loglevel=INFO
jvm.options=-Xmx32m
[root@pgserver1 101]#

On pgserver2 which is the standby
efm.nodes

[root@pgserver2 tmp]# cat /etc/edb/efm-3.0/efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
192.168.56.30:9998 192.168.56.36:9998
[root@pgserver2 tmp]#

efm.properties

[root@pgserver2 tmp]# cat /etc/edb/efm-3.0/efm.properties | grep -v ^#
db.user=efm
db.password.encrypted=ff7f041651e5e864013c1102d26a5e08
db.port=5436
db.database=postgres
db.service.owner=postgres
db.service.name=
db.bin=/u01/app/postgres/product/10/db_1/bin
db.recovery.conf.dir=/u90/mydata/101
jdbc.sslmode=disable
user.email=xxx@xxx.fr
script.notification=
bind.address=192.168.56.37:9998
admin.port=9999
is.witness=false
local.period=10
local.timeout=60
local.timeout.final=10
remote.timeout=10
node.timeout=50
stop.isolated.master=false
pingServerIp=192.168.56.30
pingServerCommand=/bin/ping -q -c3 -w5
auto.allow.hosts=true
db.reuse.connection.count=0
auto.failover=true
auto.reconfigure=true
promotable=true
minimum.standbys=0
recovery.check.period=2
auto.resume.period=0
script.fence=
script.post.promotion=
script.resumed=
script.db.failure=
script.master.isolated=
script.remote.pre.promotion=
script.remote.post.promotion=
script.custom.monitor=
custom.monitor.interval=
custom.monitor.timeout=
custom.monitor.safe.mode=
sudo.command=sudo
sudo.user.command=sudo -u %u
log.dir=/var/log/efm-3.0
jgroups.loglevel=INFO
efm.loglevel=INFO
jvm.options=-Xmx32m
[root@pgserver2 tmp]#

On pgservertools which is the witness
efm.nodes

[root@pgservertools efm-3.0]# cat /etc/edb/efm-3.0/efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
192.168.56.36:9998 192.168.56.37:9998
[root@pgservertools efm-3.0]#

efm.properties

[root@pgservertools efm-3.0]# cat /etc/edb/efm-3.0/efm.properties | grep -v ^#
db.user=efm
db.password.encrypted=ff7f041651e5e864013c1102d26a5e08
db.port=5436
db.database=postgres
db.service.owner=postgres
db.service.name=
db.bin=/u01/app/postgres/product/10/db_1/bin
db.recovery.conf.dir=/u90/mydata/101
jdbc.sslmode=disable
user.email=xxx@xxx.fr
script.notification=
bind.address=192.168.56.30:9998
admin.port=9999
is.witness=true
local.period=10
local.timeout=60
local.timeout.final=10
remote.timeout=10
node.timeout=50
stop.isolated.master=false
pingServerIp=192.168.56.30
pingServerCommand=/bin/ping -q -c3 -w5
auto.allow.hosts=false
db.reuse.connection.count=0
auto.failover=true
auto.reconfigure=true
promotable=true
minimum.standbys=0
recovery.check.period=2
auto.resume.period=0
script.fence=
script.post.promotion=
script.resumed=
script.db.failure=
script.master.isolated=
script.remote.pre.promotion=
script.remote.post.promotion=
script.custom.monitor=
custom.monitor.interval=
custom.monitor.timeout=
custom.monitor.safe.mode=
sudo.command=sudo
sudo.user.command=sudo -u %u
log.dir=/var/log/efm-3.0
jgroups.loglevel=INFO
efm.loglevel=INFO
jvm.options=-Xmx32m
[root@pgservertools efm-3.0]#

Now let’s start efm on both nodes. If there is any error check logs on /var/log/efm-3.0/.
I started on following order: pgserver1, pgserver2 and pgservertools. Services can be configured to be started automatically when the server starts.
Below an example on pgserverools

[root@pgservertools efm-3.0]# service efm-3.0 start
.
[root@pgservertools efm-3.0]# service efm-3.0 status
Redirecting to /bin/systemctl status  efm-3.0.service
● efm-3.0.service - EnterpriseDB Failover Manager 3.0
   Loaded: loaded (/usr/lib/systemd/system/efm-3.0.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2018-03-06 15:58:51 CET; 1h 46min ago
  Process: 22260 ExecStart=/bin/bash -c /usr/edb/efm-3.0/bin/runefm.sh start ${CLUSTER} (code=exited, status=0/SUCCESS)
 Main PID: 22321 (java)
   CGroup: /system.slice/efm-3.0.service
           └─22321 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.151-1.b12.el7_4.x86_64/jre/bin/java -cp /usr/edb/efm-3.0/lib/EFM-3.0.0.jar -Xmx32m com.enterprisedb.efm.main.ServiceCommand __int_start /e...
Mar 06 15:58:45 pgservertools.localdomain systemd[1]: Starting EnterpriseDB Failover Manager 3.0...
Mar 06 15:58:51 pgservertools.localdomain systemd[1]: Started EnterpriseDB Failover Manager 3.0.
[root@pgservertools efm-3.0]#

Once started we can verified from any node the status of our cluster

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm cluster-status efm
Cluster Status: efm
VIP:
        Agent Type  Address              Agent  DB       Info
        --------------------------------------------------------------
        Witness     192.168.56.30        UP     N/A
        Master      192.168.56.36        UP     UP
        Standby     192.168.56.37        UP     UP
Allowed node host list:
        192.168.56.36 192.168.56.37 192.168.56.30
Membership coordinator: 192.168.56.30
Standby priority host list:
        192.168.56.37
Promote Status:
        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.56.36        0/430001A8
        Standby     192.168.56.37        0/430001A8
        Standby database(s) in sync with master. It is safe to promote.
[root@pgservertools efm-3.0]#

Now that everything is ok, let’s do a switchover

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm promote efm -switchover
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.

And if we run the cluster-status command during the switchover

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm cluster-status efm
Cluster Status: efm
VIP:
        Agent Type  Address              Agent  DB       Info
        --------------------------------------------------------------
        Witness     192.168.56.30        UP     N/A
        Idle        192.168.56.36        UP     UNKNOWN
        Standby     192.168.56.37        UP     UP
Allowed node host list:
        192.168.56.36 192.168.56.37 192.168.56.30
Membership coordinator: 192.168.56.30
Standby priority host list:
        192.168.56.37
Promote Status:
        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Standby     192.168.56.37        0/44000098
        No master database was found.
Idle Node Status (idle nodes ignored in XLog location comparisons):
        Address              XLog Loc         Info
        --------------------------------------------------------------
        192.168.56.36        UNKNOWN          Connection to 192.168.56.36:5436 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

And we can see the promoting phase

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm cluster-status efm
Cluster Status: efm
VIP:
        Agent Type  Address              Agent  DB       Info
        --------------------------------------------------------------
        Witness     192.168.56.30        UP     N/A
        Idle        192.168.56.36        UP     UNKNOWN
        Promoting   192.168.56.37        UP     UP
Allowed node host list:
        192.168.56.36 192.168.56.37 192.168.56.30
Membership coordinator: 192.168.56.30
Standby priority host list:
        (List is empty.)
Promote Status:
        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.56.37        0/44000170
        No standby databases were found.
Idle Node Status (idle nodes ignored in XLog location comparisons):
        Address              XLog Loc         Info
        --------------------------------------------------------------
        192.168.56.36        UNKNOWN          Connection to 192.168.56.36:5436 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

And after a few time we can see that the new master is on pgserver2

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm cluster-status efm
Cluster Status: efm
VIP:
        Agent Type  Address              Agent  DB       Info
        --------------------------------------------------------------
        Witness     192.168.56.30        UP     N/A
        Standby     192.168.56.36        UP     UP
        Master      192.168.56.37        UP     UP
Allowed node host list:
        192.168.56.36 192.168.56.37 192.168.56.30
Membership coordinator: 192.168.56.30
Standby priority host list:
        192.168.56.36
Promote Status:
        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.56.37        0/440001A8
        Standby     192.168.56.36        0/440001A8
        Standby database(s) in sync with master. It is safe to promote.
[root@pgservertools efm-3.0]#

The purpose of the witness is to do an automatic failover when the primary is down. Let’s simulate a crash of our primary database by killing the corresponding process.

[root@pgserver2 tmp]# ps -ef | grep postgres
...
...
root     17529 14103  0 16:45 pts/1    00:00:00 tail -f /u90/mydata/101/log/postgresql-2018-03-06.log
postgres 20612     1  0 17:56 ?        00:00:00 /u01/app/postgres/product/10/db_1/bin/postgres -D /u90/mydata/101
postgres 20613 20612  0 17:56 ?        00:00:00 postgres: logger process
postgres 20615 20612  0 17:56 ?        00:00:00 postgres: checkpointer process
postgres 20616 20612  0 17:56 ?        00:00:00 postgres: writer process
postgres 20617 20612  0 17:56 ?        00:00:00 postgres: stats collector process
postgres 20819 20612  0 18:00 ?        00:00:00 postgres: wal writer process
postgres 20820 20612  0 18:00 ?        00:00:00 postgres: autovacuum launcher process
postgres 20821 20612  0 18:00 ?        00:00:00 postgres: archiver process   last was 00000008.history
postgres 20822 20612  0 18:00 ?        00:00:00 postgres: bgworker: logical replication launcher
postgres 20832 20612  0 18:00 ?        00:00:00 postgres: wal sender process repliuser 192.168.56.36(45827) streaming 0/440001A8
root     21481 16868  0 18:16 pts/0    00:00:00 grep --color=auto postgres
[root@pgserver2 tmp]#

And let’s execute the kill command

[root@pgserver2 tmp]# kill -9 20612

If we check the cluster status from the witness server, we can see that the master is in an UNKNOWN status

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm cluster-status efm
Cluster Status: efm
VIP:
        Agent Type  Address              Agent  DB       Info
        --------------------------------------------------------------
        Witness     192.168.56.30        UP     N/A
        Standby     192.168.56.36        UP     UP
        Idle        192.168.56.37        UP     UNKNOWN
Allowed node host list:
        192.168.56.36 192.168.56.37 192.168.56.30
Membership coordinator: 192.168.56.30
Standby priority host list:
        192.168.56.36
Promote Status:
        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Standby     192.168.56.36        0/440001A8
        No master database was found.
Idle Node Status (idle nodes ignored in XLog location comparisons):
        Address              XLog Loc         Info
        --------------------------------------------------------------
        192.168.56.37        UNKNOWN          Connection to 192.168.56.37:5436 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
[root@pgservertools efm-3.0]#

In the alert log in our standby server pgserver1, we can see that that the database is converted to a primary one a few time after.

2018-03-06 18:17:49.381 CET [18384] FATAL:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2018-03-06 18:17:49.382 CET [18380] LOG:  invalid record length at 0/440001A8: wanted 24, got 0
2018-03-06 18:17:49.387 CET [19049] FATAL:  could not connect to the primary server: could not connect to server: Connection refused
                Is the server running on host "192.168.56.37" and accepting
                TCP/IP connections on port 5436?
2018-03-06 18:17:54.404 CET [19055] FATAL:  could not connect to the primary server: could not connect to server: Connection refused
                Is the server running on host "192.168.56.37" and accepting
                TCP/IP connections on port 5436?
2018-03-06 18:17:59.406 CET [19107] FATAL:  could not connect to the primary server: could not connect to server: Connection refused
                Is the server running on host "192.168.56.37" and accepting
…
….
….
                TCP/IP connections on port 5436?
                TCP/IP connections on port 5436?
2018-03-06 18:18:34.450 CET [19128] FATAL:  could not connect to the primary server: could not connect to server: Connection refused
                Is the server running on host "192.168.56.37" and accepting
                TCP/IP connections on port 5436?
2018-03-06 18:18:39.451 CET [19134] FATAL:  could not connect to the primary server: could not connect to server: Connection refused
                Is the server running on host "192.168.56.37" and accepting
                TCP/IP connections on port 5436?
2018-03-06 18:18:44.462 CET [19135] FATAL:  could not connect to the primary server: could not connect to server: Connection refused
                Is the server running on host "192.168.56.37" and accepting
                TCP/IP connections on port 5436?
2018-03-06 18:18:49.456 CET [18380] LOG:  trigger file found: /tmp/postgresql.trigger
2018-03-06 18:18:49.456 CET [18380] LOG:  redo done at 0/44000170
2018-03-06 18:18:49.479 CET [18380] LOG:  selected new timeline ID: 9
2018-03-06 18:18:50.128 CET [18380] LOG:  archive recovery complete
2018-03-06 18:18:50.229 CET [18378] LOG:  database system is ready to accept connections

What we can confirm by querying the cluster status

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm cluster-status efm
Cluster Status: efm
VIP:
        Agent Type  Address              Agent  DB       Info
        --------------------------------------------------------------
        Witness     192.168.56.30        UP     N/A
        Master      192.168.56.36        UP     UP
        Idle        192.168.56.37        UP     UNKNOWN
Allowed node host list:
        192.168.56.36 192.168.56.37 192.168.56.30
Membership coordinator: 192.168.56.30
Standby priority host list:
        (List is empty.)
Promote Status:
        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.56.36        0/440002B8
        No standby databases were found.
Idle Node Status (idle nodes ignored in XLog location comparisons):
        Address              XLog Loc         Info
        --------------------------------------------------------------
        192.168.56.37        UNKNOWN          Connection to 192.168.56.37:5436 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
[root@pgservertools efm-3.0]#

and

[root@pgservertools efm-3.0]# /usr/edb/efm-3.0/bin/efm cluster-status efm
Cluster Status: efm
VIP:
        Agent Type  Address              Agent  DB       Info
        --------------------------------------------------------------
        Witness     192.168.56.30        UP     N/A
        Master      192.168.56.36        UP     UP
Allowed node host list:
        192.168.56.36 192.168.56.37 192.168.56.30
Membership coordinator: 192.168.56.30
Standby priority host list:
        (List is empty.)
Promote Status:
        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.56.36        0/440002B8
        No standby databases were found.
[root@pgservertools efm-3.0]#

On the old primary pgserver2 we can see the contents of the file recovery.conf automatically created by EDB Failover manager

[postgres@pgserver2 101]$ cat recovery.conf
# EDB Failover Manager
# This generated recovery.conf file prevents the db server from accidentally
# being restarted as a master since a failover or promotion has occurred
standby_mode = on
restore_command = 'echo 2>"recovery suspended on failed server node"; exit 1'
[postgres@pgserver2 101]$

To rebuild our standby database we have to edit the recovery.conf file
Conclusion
We have seen in this blog how to configure edb-efm30. Note that a virtual IP can be also configured. The official documentation can help.

 

Cet article EDB Failover Manager 3.0 and PostgreSQL 10.1 est apparu en premier sur Blog dbi services.

ODA – Reducing number of cores : Getting error DCS-10045

Sun, 2018-03-04 13:27

To perform some tests in order to determine the accurate number of cores to be used by the database application, I had to increase and decrease the number of CPU cores on an ODA using the DCS stack.  When increasing would never be a problem, decreasing number of CPU cores would fail into a DCS-10045 error.

The ODA was initialized with 6 CPU cores, and purpose was to reduce it to 4 CPU cores (of course for testing ;-) ).

[root@BApdl006060 ~]# odacli list-cpucores

Node  Cores  Modified                       Job Status
----- ------ ------------------------------ ---------------
0     36     February 23, 2018 12:22:12 AM CET Configured
0     6      February 26, 2018 11:41:06 AM CET Configured

 

Running the below command to reduce CPU cores

[root@BApdl006060 ~]# odacli update-cpucore -c 4

 

Unfortunately this failed into the error :

DCS-10045:Validation error encountered: reduction in number of cores is not supported

 

If failing into this case, the tip would be to use the “force” option.

[root@BApdl006060 ~]# odacli update-cpucore -c 4 --force
{
  "jobId" : "5d343d01-6eb1-47f1-af2b-7df3f8adab84",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "February 28, 2018 08:49:21 AM CET",
  "resourceList" : [ ],
  "description" : "CPU cores  service update",
  "updatedTime" : "February 28, 2018 08:49:21 AM CET"
}

 

Once the job is completed successfully, the ODA is running the expected 4 CPU cores.

root@BApdl006060 ~]# odacli list-cpucores

Node  Cores  Modified                       Job Status
----- ------ ------------------------------ ---------------
0     36     February 23, 2018 12:22:12 AM CET Configured
0     6      February 26, 2018 11:41:06 AM CET Configured
0     4      February 28, 2018 8:49:29 AM CET Configured

[root@BApdl006060 ~]# odacli describe-cpucore

Node  Cores  Modified                       Job Status
----- ------ ------------------------------ ---------------
0     4      February 28, 2018 8:49:29 AM CET Configured

 

Of course, knowing that reducing the CPU cores is not supported, recommendation would be to use the force option only in test environment and not in production.

 

 

 

Cet article ODA – Reducing number of cores : Getting error DCS-10045 est apparu en premier sur Blog dbi services.

Pages