Feed aggregator

REG_EXP is a problem

Tom Kyte - Sat, 2017-11-04 02:46
WHERE email IN ( select regexp_substr('one@gmail.com,two@gamil.com,three@gmail.com,four@gmail.com','[^,]+', 1, level) from dual connect by regexp_substr('one@gmail.com,two@gamil.com,three@gmail.com,four@gmail.com', '[^,]+', 1, level) is not nul...
Categories: DBA Blogs

I need to replace this query with substr and instr

Tom Kyte - Sat, 2017-11-04 02:46
Categories: DBA Blogs

Multiple query question

Tom Kyte - Sat, 2017-11-04 02:46
I have an Argos report that takes a query(s) and output a report based on those entries. My problem is there are 5 possible querys. I trying to use and/or ( I already tried the CASE function) to pulls data base on their entry. The queries are; Recei...
Categories: DBA Blogs

Generating XML files from clob field

Tom Kyte - Sat, 2017-11-04 02:46
Dear I'd like to know if it's possible to generate several XML files from a clob filed, for instance: A) The clob field is in a XML format. I've just need to run a loop to export every single row in a new XML file. B) These files need to be expo...
Categories: DBA Blogs

samplescheam.xml and samplescheam.dfb are not found in this folder......./assistants/dbca/templates

Tom Kyte - Sat, 2017-11-04 02:46
Hello Tom, I have installed Oracle DB 12.2 on my Laptop and I am trying to create PDB with SampleScheam. I am following the steps in the following link. http://holowczak.com/installing-sample-schemas-for-oracle-12c-using-the-database-configuration-...
Categories: DBA Blogs

Locking on Hash table partitions

Tom Kyte - Sat, 2017-11-04 02:46
Hi, I have a data warehouse application running to "Enqueue TX row lock contention waits". Here is the situation: Table has 50 hash partitions on point_id column. the data load process is trying to delete rows using ROWID values, it is locki...
Categories: DBA Blogs

Invalid datetime format after migrated to Oracle 12.2 client

Tom Kyte - Sat, 2017-11-04 02:46
Hi We recently upgraded to Oracle 12.2. We are not able to use existing scripts to enter data which includes time in format YYYY/MM/DD HH:MM:SS ex: 2017/11/03 17:16:31 using oracle 12.2 client to insert in to Oracle 12.2 server. We are getting error...
Categories: DBA Blogs

Exception in declration section

Tom Kyte - Sat, 2017-11-04 02:46
create or replace procedure proc_delme is n number:=1/0; begin dbms_output.put_line(n); exception when others then dbms_output.put_line(sqlerrm); end proc_delme; / If I do following then raised error is not ...
Categories: DBA Blogs

Goldengate XAG HAS

Michael Dinh - Fri, 2017-11-03 19:53

If you install GI for SI DB, then you might as well install XAG for OGG.

Imagine if there is a Vagrant to put all of this together?

[oracle@db-asm-1 xag]$ mkdir -p /u01/app/oracle/xag

[oracle@db-asm-1 xag]$ ./xagsetup.sh --install --directory /u01/app/oracle/xag
Installing Oracle Grid Infrastructure Agents on: db-asm-1

[oracle@db-asm-1 ~]$ cd /u01/app/oracle/xag/bin/

[oracle@db-asm-1 bin]$ ./agctl query releaseversion
The Oracle Grid Infrastructure Agents release version is 8.1.0

[oracle@db-asm-1 bin]$ ./agctl query deployment
The Oracle Grid Infrastructure Agents deployment is standalone

[oracle@db-asm-1 bin]$ ./agctl add goldengate --help
Adds Goldengate instance to Oracle Clusterware.

[oracle@db-asm-1 bin]$ ./agctl add goldengate ogg_amer \
> --instance_type dual --databases ora.amer.db \
> --gg_home /u01/app/oracle/amer/ogg/12.3.0_ora12c \
>  --oracle_home /u01/app/oracle/

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is not running

[oracle@db-asm-1 bin]$ ./agctl start goldengate ogg_amer

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is running on db-asm-1

[oracle@db-asm-1 bin]$ ./agctl config goldengate
XAG-212: Instance '' is not yet registered.

[oracle@db-asm-1 bin]$ ./agctl config goldengate ogg_amer
GoldenGate location is: /u01/app/oracle/amer/ogg/12.3.0_ora12c
GoldenGate instance type is: dual
ORACLE_HOME location is: /u01/app/oracle/
Databases needed: ora.amer.db
EXTRACT groups to monitor:
REPLICAT groups to monitor:
Critical EXTRACT groups:
Critical REPLICAT groups:
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no
[oracle@db-asm-1 bin]$

$ ./crs_stat.sh
The Oracle base remains unchanged with value /u01/app/oracle
NAME                                          TARGET     STATE           SERVER       STATE_DETAILS
-------------------------                     ---------- ----------      ------------ ------------------
                                              Name       Target          State        Server State
ora.CRS.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.DATA.dg                                   ONLINE     ONLINE          db-asm-1     STABLE
ora.FRA.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.LISTENER.lsnr                             ONLINE     ONLINE          db-asm-1     STABLE
ora.asm                                       ONLINE     ONLINE          db-asm-1     Started,STABLE
ora.ons                                       OFFLINE    OFFLINE         db-asm-1     STABLE
ora.amer.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.cssd                                      ONLINE     ONLINE          db-asm-1     STABLE
ora.diskmon                                   OFFLINE    OFFLINE         STABLE
ora.euro.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.evmd                                      ONLINE     ONLINE          db-asm-1     STABLE
xag.ogg_amer.goldengate                       ONLINE     ONLINE          db-asm-1     STABLE

$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.euro.db' on 'db-asm-1'
CRS-2673: Attempting to stop 'xag.ogg_amer.goldengate' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'db-asm-1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.euro.db' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.CRS.dg' on 'db-asm-1' succeeded
CRS-2677: Stop of 'xag.ogg_amer.goldengate' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.amer.db' on 'db-asm-1'
CRS-2677: Stop of 'ora.amer.db' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.DATA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.FRA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'db-asm-1'
CRS-2677: Stop of 'ora.evmd' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.asm' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'db-asm-1'
CRS-2677: Stop of 'ora.cssd' on 'db-asm-1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'db-asm-1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

GoldenGate Naming Convention P01

Michael Dinh - Fri, 2017-11-03 17:33

I had a nice discussion with teammates about standards.

It’s wrong if there are no standards or naming conventions; otherwise, let your imagination run wild.

Hence, before you embark, think about it as it will make life much easier.

For prompt: I like to know what ORACLE_SID for environment.


For Goldengate: I did it this way because there are 2 DBs / 2 GGs for the same host.

Why ora12c? There are 2 options when installing Goldengate: ORA11g|ORA12c


There was discussion ogg/gg/ggs – doesn’t really matter.

Where should it reside? I had planned for /u02 and /u03 but Vagrant was not being nice to me.

Why different mount? There are GG directories and trails which will fill up.

I like to KISS and avoid soft links.

One thing that does annoy is using $GGHOME.


Make life simple, use aliases.

$ cat .bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
# User specific environment and startup programs
export PATH=$PATH:$HOME/.local/bin:$HOME/bin
. ~/.alias

$ cat .alias

alias amer='source ~/.amer'
alias euro='source ~/.euro'
alias ggs='cd $GG_HOME'

$ cat .amer

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=amer
. oraenv
export GG_HOME=/u01/app/oracle/amer/ogg/12.3.0_ora12c
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

$ amer

The Oracle base remains unchanged with value /u01/app/oracle
$ env|egrep 'ORACLE|HOME'

$ cat .euro

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=euro
. oraenv
export GG_HOME=/u01/app/oracle/euro/ogg/12.3.0_ora12c
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

$ euro

The Oracle base remains unchanged with value /u01/app/oracle
$ env|egrep 'ORACLE|HOME'

$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version OGGCORE_12.
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

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

GGSCI (db-asm-1) 1> exit

$ grep ORA oggcore.rsp

# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g

Oracle SOA Suite 12c: Installation - Preparing the database

Dietrich Schroff - Fri, 2017-11-03 15:24
After a successful installation of Oracle 12c database the next step is to create a plugable databse (PDB).
Therefor you have to run the dbca (database creation assistant):

 The first check fails with:
[oracle@localhost ~]$ export ORACLE_SID=soasuite12c
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release Production on Sat Oct 7 17:00:21 2017

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

Enter user-name: bpeladmin
Enter password:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -1824536353
Process ID: 0
Session ID: 0 Serial number: 0This is, because the tnsnames.ora is not correct:
[oracle@localhost admin]$ cat /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (SERVICE_NAME = orcl)
You have to add this entry:
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (SERVICE_NAME = soasuite12c)
And here we go:
[oracle@localhost admin]$ sqlplus bpeladmin@soasuite12c

SQL*Plus: Release Production on Sat Oct 7 17:22:14 2017

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

Enter password:

Verbunden mit:
Oracle Database 12c Enterprise Edition Release - 64bit Production

SQL> show con_name;


ORA-15040 ORA-15042 with EXTERNAL redundancy Diskgroup

Amardeep Sidhu - Fri, 2017-11-03 12:57

A colleague was working on an ASM issue (Standalone one, Version on AIX) at one of the customer sites. Later on, I also joined him. The issue was that the customer added few news disks to an existing diskgroup. Everything went well and the rebalance kicked in. After some time, something happened and all of a sudden the diskgroup was dismounted. While trying the mount the diskgroup again, it was giving

ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "27" is missing from group number "2"

Here is the relevant text from the ASM alert log

ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 19: <strong>No such device</strong>
Additional information: -1
Additional information: 1048576
WARNING: <strong>Write Failed</strong>. group:2 disk:27 AU:1005 offset:0 size:1048576
Fri Nov 03 10:55:27 2017
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_dbw0_58983380.trc:
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 19: No such device
Additional information: -1
Additional information: 4096
WARNING: Write Failed. group:2 disk:27 AU:0 offset:16384 size:4096
NOTE: cache initiating offline of disk 27 group DATADG
NOTE: process _dbw0_+asm1 (58983380) initiating offline of disk 27.3928481273 (DISK_01) with mask 0x7e in group 2
Fri Nov 03 10:55:27 2017
WARNING: Disk 27 (DISK_01) in group 2 mode 0x7f is now being offlined
WARNING: Disk 27 (DISK_01) in group 2 in mode 0x7f is now being taken offline on ASM inst 1
NOTE: initiating PST update: grp = 2, dsk = 27/0xea27ddf9, mask = 0x6a, op = clear
ERROR: failed to copy file +DATADG.263, extent 1952
GMON updating disk modes for group 2 at 36 for pid 9, osid 58983380
ERROR: Disk 27 cannot be offlined, since diskgroup has external redundancy.
ERROR: too many offline disks in PST (grp 2)
ERROR: ORA-15080 thrown in ARB0 for group number 2
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_arb0_57672234.trc:
ORA-15080: synchronous I/O operation to a disk failed
Fri Nov 03 10:55:27 2017
NOTE: stopping process ARB0
WARNING: Disk 27 (DISK_01) in group 2 mode 0x7f offline is being aborted
WARNING: Offline of disk 27 (DISK_01) in group 2 and mode 0x7f failed on ASM inst 1
NOTE: halting all I/Os to diskgroup 2 (DATADG)
Fri Nov 03 10:55:28 2017
NOTE: cache dismounting (not clean) group 2/0xDEB72D47 (DATADG)
NOTE: messaging CKPT to quiesce pins Unix process pid: 62128816, image: oracle@tiiproddb1.murugappa.co.in (B000)
NOTE: dbwr not being msg'd to dismount
Fri Nov 03 10:55:28 2017
NOTE: LGWR doing non-clean dismount of group 2 (DATADG)
NOTE: LGWR sync ABA=124.7138 last written ABA 124.7138
NOTE: cache dismounted group 2/0xDEB72D47 (DATADG)
SQL> alter diskgroup DATADG dismount force /* ASM SERVER */ 

At this stage disk 27 was not readable even with dd. So that means something is wrong with the disk. Since it is an external redundancy diskgroup not much can be done until the disk becomes available.

Speaking to the storage team cleared the air. One that the disk had gone offline at storage level so that is why even dd was not able to read it. Two that all these disks were thin provisioned (over provisioning of the storage space to improve the utilization; similar to over provisioning of CPU cores in the Virtualization world) from the storage. This particular disk 27 was meant for some other purpose but got wrongly allocated to this diskgroup. The actual space available in the pool (of this disk) was less than what was needed. The moment disks were added to the diskgroup, the rebalance kicked in and ASM started writing data to the disk. Within few minutes space became full and the storage software took the disk offline. Since ASM couldn’t write to the disk, the diskgroup was dismounted.

Fortunately, in the same pool, there was another disk that was still unused. So the storage guy dropped that disk and it freed up some space in the pool. He brought this disk 27 online after that. Diskgroup got mounted and the rebalance kicked in again. Finally, we dropped this disk and the rebalance started again. Once the rebalance completed, disk was free to be taken offline.


Categories: BI & Warehousing

New OA Framework 12.2.5 Update 17 Now Available

Steven Chan - Fri, 2017-11-03 11:27

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface libraries and infrastructure. Since the initial release of Oracle E-Business Suite Release 12.2 in 2013, we have released a number of cumulative updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Packs. In this context, cumulative means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for Oracle E-Business Suite Release 12.2.5 is now available:

Where is this update documented?

Instructions for installing this OAF Release Update Pack are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.5 users should apply this patch.  Future OAF patches for EBS Release 12.2.5 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes 51 fixes in total, including all fixes released in previous EBS Release 12.2.5 bundle patches.

This latest bundle patch includes fixes for following bugs/issues:

  • A horizontal scroll bar appears on the page when the Title/Description of an attachment is too long.
  • There is a script error on clicking GO button on WebADI LOV window while selecting WebADI template.

Related Articles

Categories: APPS Blogs

Oracle ADF on Docker Container

Andrejus Baranovski - Fri, 2017-11-03 10:56
Want to run Oracle ADF on Docker? This is possible, I will explain how. If you are new to Docker, it may require to spend significant amount of time to get started with all different bits and pieces. I will try to explain all essential steps, so that you will get up to speed quickly.

First of all you need to have DB accessible, check my previous post explaining how to run Oracle DB on Docker - Oracle Database Docker Image in Docker Cloud (Digital Ocean). DB is required to install RCU schema for WebLogic installation with JRF files.

I have built my own Oracle Fusion Middleware Docker image using Oracle Docker images - Oracle Fusion Middleware Infrastructure on Docker.

First step is to build Oracle JDK (Server JRE) image, this is pre-requisite to build Oracle Fusion Middleware Docker image. Read through instructions documented on Oracle Fusion Middleware Infrastructure on Docker GitHub page. You should navigate to Oracle Java folder (download Oracle Docker files from GitHub link mentioned above) and copy there JDK installation file:

Run command to create JDK Docker image:


Command output:

Double check to verify if image was created successfully by running docker images command:

Let's move on to Oracle FMW image creation. Navigate to Oracle FMW folder and copy FMW infrastructure installation file (I'm installing

Move one folder up and run command:

./buildDockerImage.sh -s -v

To build Oracle FMW image. I use flag -s to skip checksum verification for installation file. You should run command from this folder:

You will see long output in the log for this command:

It installs WLS into Docker image:

Run docker images command to verify if image was created successfully:

In the next step, we will create FMW domain and extend it with ADF support. But before that we need to make sure DB details are set correctly, to be able to install RCU schema. Oracle provides infraDomain file with DB and WLS properties, make sure to set correct DB details. If properties are not correct, RCU creation will fail:

Execute docker run command to startup WLS Docker container. During first start up it will create and extend WLS domain with ADF support:

docker run -d -p 7001:7001 --name RedSamuraiWLS --env-file ./infraDomain.env.list oracle/fmw-infrastructure:

Flag -d means container will run in detached mode and we will be able to return to command prompt. Port with name is specified along with environment properties file. Make sure to reference FMW image which was created in the step above. Once control is returned back to the prompt, run docker command to check status of docker container (flag -a means to show all containers):

docker ps -a

Container should be in the running state. First startup takes longer, because it requires to setup and extend WLS domain:

Once domain is extended, you will see WebLogic starting:

Finally WebLogic should be in Running state:

Run again docker ps -a command to verify container state, it should be up and running:

Once WLS machine is up, you can navigate to Enterprise Manager through URL from outside of Docker container, for example from your host. Login to EM and you will see Admin server is up, but Managed Server is down. There is a way to startup Managed Server too, but if you want to run ADF apps for DEV environment, realistically speaking Admin server is more than enough for deployment too:

Simply delete (this cab done from EM) Managed Server and cluster, keep only Admin Server:

I have deployed sample ADF application:

This application is based on ADF BC, data source is defined too:

ADF application runs from WebLogic on Docker:

Now lets see how to push newly created container to Docker registry.

First we need to create new Docker image from Docker container. This can be done with docker commit command (pointing to container ID and specifying Docker repository name and tag):

docker commit da03e52b42a2 abaranovskis/redsamurai-wls:v1

Run docker images command to verify new image is created successfully. Next run docker login to authenticate with Docker repository. Run docker push to write image to Docker repository:

docker push abaranovskis/redsamurai-wls:v1

Commands execution sequence:

Pushed image should appear in docker repository:

Once image is in Docker online repository, we can startup online Docker container, so that WLS will be accessible online. This can be done through command line or using Docker Cloud UI interface. You can create new container by referencing image from Docker repository:

Our WLS docker container with ADF support runs on Digital Ocean:

Logs are accessible from Docker Cloud UI and you can see server status:

Alter table add column on a FDA enabled table - how to avoid the row chain effect without using the 'move' option?

Tom Kyte - Fri, 2017-11-03 08:26
Hi, We'd like to use FDA on our Oracle db for its bi-temporality feature. So far when we add a column to a table, we also perform the 'alter table T move;' + rebuild indexes, to avoid performance issues and to re-organize the row IDS. But the ...
Categories: DBA Blogs

Why differ inmemory_size in v$im_segments from used_bytes in v$inmemory_area?

Tom Kyte - Fri, 2017-11-03 08:26
Hi I'm testing In-Memory and my question is why the figures in v$im_segments differ from the used_bytes in v$inmemory_area? I have read a lot of great posts (for example https://blogs.oracle.com/in-memory/what-is-an-in-memory-compression-unit-i...
Categories: DBA Blogs

Advise for Analytics-related Workflow Automation

Tom Kyte - Fri, 2017-11-03 08:26
Hello, I work in the Analytics department where I support a team of many Data Scientists. We use Oracle Database Enterprise v11.2.0.4 as our back-end database and I have developed several automation using PL/SQL procedures, functions, etc. I...
Categories: DBA Blogs

How Result cache is managed in 12c Pluggable Database (PDB)

Tom Kyte - Fri, 2017-11-03 08:26
Hi Team, I ma having one scenario, where I am setting up my application in 3 pluggable db instances under single CDB. As per my app requirement, I have to create synonym for dbms_result_cache in all 3 PDBs. As the public synonym for dbms_result_ca...
Categories: DBA Blogs

fetch output (success/failure) status from web service

Tom Kyte - Fri, 2017-11-03 08:26
Hi, Could you please share any example to fetch web service output (i.e. success/failure) status into oracle PL/SQL procedure? The scenario is as below, we have created a stored procedure which will pass 2 input parameters from those input p...
Categories: DBA Blogs

Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication?

Yann Neuhaus - Fri, 2017-11-03 04:03

When you start using PostgreSQL 10 logical replication you might think it is a good idea to setup bi-directional replication so you end up with two or more masters that are all writable. I will not go into the details of multi master replication here (conflict resolution, …) but will show what happens when you try to do that. Lets go …

My two instances run on the same host, one on port 6000 the other one on 6001. To start I’ll create the same table in both instances:

postgres=# create table t1 ( a int primary key, b varchar(50) );
postgres=# alter table t1 replica identity using INDEX t1_pkey;
postgres=# \d+ t1
                                            Table "public.t1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
 a      | integer               |           | not null |         | plain    |              | 
 b      | character varying(50) |           |          |         | extended |              | 
    "t1_pkey" PRIMARY KEY, btree (a) REPLICA IDENTITY

Create the same publication on both sides:

postgres=# create publication my_pub for table t1;
postgres=# select * from pg_publication;
 pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete 
 my_pub  |       10 | f            | t         | t         | t
(1 row)
postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename 
 my_pub  | public     | t1
(1 row)

Create the same subscription on both sides (except for the port, of course):

postgres=# show port;
(1 row)
ppostgres=# create subscription my_sub connection 'host=localhost port=6001 dbname=postgres user=postgres' publication my_pub;
postgres=# select * from pg_subscription;
 subdbid | subname | subowner | subenabled |                      subconninfo                       | subslotname | 
   13212 | my_sub  |       10 | t          | host=localhost port=6001 dbname=postgres user=postgres | my_sub      | 
(1 row)

### second instance

postgres=# show port;
(1 row)

postgres=# create subscription my_sub connection 'host=localhost port=6000 dbname=postgres user=postgres' publication my_pub;
postgres=# select * from pg_subscription;
 subdbid | subname | subowner | subenabled |                      subconninfo                       | subslotname | 
   13212 | my_sub  |       10 | t          | host=localhost port=6000 dbname=postgres user=postgres | my_sub      | 
(1 row)

So far, so good, everything worked until now. Now lets insert a row in the first instance:

postgres=# insert into t1 (a,b) values (1,'a');
postgres=# select * from t1;
 a | b 
 1 | a
(1 row)

That seemed to worked as well as the row is there on the second instance as well:

postgres=# show port;
(1 row)

postgres=# select * from t1;
 a | b 
 1 | a
(1 row)

But: When you take a look at the log file of the first instance you’ll see something like this (which is repeated over and over again):

2017-11-03 09:56:29.176 CET - 2 - 10687 -  - @ ERROR:  duplicate key value violates unique constraint "t1_pkey"
2017-11-03 09:56:29.176 CET - 3 - 10687 -  - @ DETAIL:  Key (a)=(1) already exists.
2017-11-03 09:56:29.178 CET - 29 - 10027 -  - @ LOG:  worker process: logical replication worker for subscription 16437 (PID 10687) exited with exit code 1
2017-11-03 09:56:34.198 CET - 1 - 10693 -  - @ LOG:  logical replication apply worker for subscription "my_sub" has started

Now the second instance is constantly trying to insert the same row back to the first instance and that obviously can not work as the row is already there. So the answer to the original question: Do not try to do that, it will not work anyway.


Cet article Can I have the same table published and subscribed (bi-directional) in PostgreSQL 10 logical replication? est apparu en premier sur Blog dbi services.


Subscribe to Oracle FAQ aggregator