Enter password for new role:\u00a0\nEnter it again:\u00a0\nCREATE ROLE dbvrep_admin ENCRYPTED \nPASSWORD 'md5e3c4e8f1b4f8e388eef4fe890d6bdb36' SUPERUSER CREATEDB \nCREATEROLE INHERIT LOGIN;\n\n<\/pre>\nWe edit the configuration file\u00a0\/u02\/pgdata\/postgresql.conf in order to allow non-localhost connections:<\/p>\n
postgres@pg1:\/u02\/pgdata\/PG1\/ [PG1] cp postgresql.conf postgresql.conf.save\npostgres@pg1:\/u02\/pgdata\/PG1\/ [PG1] sed -i \"s\/^#\\(\nlisten_addresses = '\\)localhost'\/\\1*'\\t\/\" postgresql.conf<\/pre>\nWe also enable connections on non-localhost address:<\/p>\n
postgres@pg1:\/u02\/pgdata\/PG1\/ [PG1] cp pg_hba.conf \npg_hba.conf.save\npostgres@p1:\/u02\/pgdata\/PG1\/ [PG1] echo -e \n\"host\\tall\\t\\tall\\t\\t0.0.0.0\/0\\t\\tmd5\" >> pg_hba.conf<\/pre>\ncat pg_hba.conf:<\/p>\n
\u00a0# TYPE\u00a0 DATABASE\u00a0 \u00a0 \u00a0 \u00a0 USER\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ADDRESS \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 METHOD\n# \"local\" is for Unix domain socket connections only\nlocal \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 md5\n# IPv4 local connections:\nhost\u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 127.0.0.1\/32\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 md5\nhost\u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 192.168.22.201\/24\u00a0 \u00a0 \u00a0 md5\n\u00a0# IPv6 local connections:\nhost\u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ::1\/128 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 md5\n\u00a0host\u00a0 \u00a0 all \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 barman\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 192.168.1.101\/24 \u00a0 \u00a0 \u00a0 md5\nhost\u00a0 \u00a0 replication \u00a0 \u00a0 barman_streaming 192.168.1.101\/24\u00a0 \u00a0 \u00a0 md5\n# Allow replication connections from localhost, by a user with the\n# replication privilege.\nlocal \u00a0 replication \u00a0 \u00a0 postgres\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 md5\nhost\u00a0 \u00a0 replication \u00a0 \u00a0 postgres\u00a0 \u00a0 \u00a0 \u00a0 127.0.0.1\/32\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 md5\nhost\u00a0 \u00a0 replication \u00a0 \u00a0 postgres\u00a0 \u00a0 \u00a0 \u00a0 ::1\/128 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 md5\nhost all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.0.0.0\/0\u00a0\u00a0\u00a0\u00a0\u00a0 md5<\/pre>\nWe have to restart the postgres server:<\/p>\n
postgres@pg1:\/u02\/pgdata\/PG1\/ [PG1] pgrestart\nwaiting for server to shut down.... done\nserver stopped\nserver starting\npostgres@pg1:\/u02\/pgdata\/PG1\/ [PG1] 2017-07-17 13:52:52.350 CEST\n - 1 - 3106 -\u00a0\n - @ LOG:\u00a0 redirecting log output to logging collector process\n2017-07-17 13:52:52.350 CEST - 2 - 3106 -\u00a0\n - @ HINT:\u00a0 Future log output will appear in directory \n\"\/u01\/app\/postgres\/admin\/PG1\/pg_log\".\n\u00a0\npostgres@pg_essentials_p1:\/u02\/pgdata\/PG1\/ [PG1]\u00a0\npostgres@pg_essentials_p1:\/u02\/pgdata\/PG1\/ [PG1] alias | grep pgrestart\nalias pgrestart<\/strong>='pg_ctl -D ${PGDATA} restart -m fast'\n<\/pre>\nThen we install dbvisit replicate:<\/p>\n
We download the dbvisit_replicate-2.9.00-el5.x86_64.rpm and we install it:<\/p>\n
[root@localhost software]# rpm -ivh dbvisit_replicate-2.9.00-el5.x86_64.rpm\u00a0\nPreparing...\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ################################# [100%]\nUpdating \/ installing...\n\u00a0\u00a0 1:dbvisit_replicate-2.9.00-el5 \u00a0################################# [100%]\n<\/pre>\nTo make it work properly, I had to modify the sqlnet.ora file as follows in order to avoid the following error message:<\/p>\n
ERR-11: Could not connect as dbvrep to database PSI, \nerror is ORA-24327: need explicit attach\nbefore authenticating a user (DBD ERROR: OCISessionBegin)<\/pre>\nsqlnet.ora:\nSQLNET.SQLNET_ALLOWED_LOGON_VERSION=11\nSQLNET.ALLOWED_LOGON_VERSION_CLIENT =11\nSQLNET.ALLOWED_LOGON_VERSION_SERVER =11\n<\/pre>\nBefore running dbvrep, be sure you can connect with psql from the Oracle server to the postgreSQL server\u00a0!! I needed to install a postgres client own the Oracle host and to define the PATH properly.<\/p>\n
Finally by running dbvrep on the Oracle server, you \u00a0run the setup wizard, and you enter your configuration settings, this menu is quite easy to understand. The setup wizard is defined in 4 steps:<\/p>\n
– Step 1: describe databases<\/p>\n
– Step 2: Replicate Pairs<\/p>\n
– Step 3: Replicated tables<\/p>\n
– Step 4: Process Configuration<\/p>\n
oracle@localhost:\/home\/oracle\/ora2pg\/ [PSI] dbvrep\nInitializing......done\nDbvisit Replicate version 2.9.02\nCopyright (C) Dbvisit Software Limited. All rights reserved.\nNo DDC file loaded.\nRun \"setup wizard\" to start the configuration wizard or try \"help\" \nto see all commands available.\ndbvrep><\/span> setup wizard<\/strong> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nThis wizard configures Dbvisit Replicate.\n\u00a0\nThe setup wizard creates configuration scripts, which need to be run \nafter the wizard ends. Nochanges to the databases are made before that.\n\u00a0\nThe progress is saved every time a list of databases, replications, etc. \nis shown. It will bere-read if wizard is restarted and the same DDC \nname and script path is selected.\nRun the wizard now? [Yes]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nAccept end-user license agreement? (View\/Yes\/No) [No]<\/span> yes<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\nBefore starting the actual configuration, some basic information is needed. The DDC name and\nscript path determines where all files created by the wizard go \n(and where to reread them ifwizard is rerun) and the license key \ndetermines which options are available for this\nconfiguration.\n(DDC_NAME) - Please enter a name for this replication: []<\/span> ora2pg<\/strong> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\n(LICENSE_KEY) - Please enter your license key: [(trial)]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nWhich Replicate edition do you want to trial (LTD\/XTD\/MAX): [MAX]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\n(SETUP_SCRIPT_PATH) - Please enter a directory for location of \nconfiguration scripts on this\u00a0 \u00a0 \u00a0\nmachine: [\/home\/oracle\/Documents\/ora2pg]<\/span> \/home\/oracle\/ora2pg<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\n\u00a0\nNetwork configuration files were detected on this system in these locations:\n\/u00\/app\/oracle\/network\/admin\n\/u00\/app\/oracle\/product\/12.2.0.1\/dbhome_1\/network\/admin\n(TNS_ADMIN) - Please enter TNS configuration directory for this machine:\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 [\/u00\/app\/oracle\/network\/admin]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nRead 2 described databases from previous wizard run.\n\u00a0\nStep 1 - Describe databases\n========================================\nThe first step is to describe databases used in the replication. \nThere are usually two of them\n(source and target).\n\u00a0\nFollowing databases are now configured:\n1: Oracle PSI, SYS\/***, SYSTEM\/***, dbvrep\/***, USERS\/TEMP, dbvrep\/, \nASM:No, TZ: +02:00\n2: Postgres postgres, dbvrep_admin\/***, dbvrep_admin\/***, dbvrep\/***, \n\/, dbvrep\/, ASM:n\/a, TZ:\u00a0\nEnter the number of the database to modify it, or \"add\", or \"done\": [done]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nRead 1 replication pairs from previous wizard run.\n\u00a0\nStep 2 - Replication pairs\n========================================\nThe second step is to set source and targets for each replication pair.\n\u00a0\nEnter number of replication pair to modify it, or \"add\", or \"done\": [done]<\/span> 1<\/strong> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nDo you want to \"edit\" the replication pair or \"delete\" it? [edit]<\/span> edit<\/strong> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nLet's configure the replication pair, selecting source and target.\nFollowing databases are described:\n1: PSI#DBVREP (Oracle)\n2: DBNAME=POSTGRES;HOST=PG1#DBVREP (Postgres) \n(cannot be source: not an Oracle database)\nSelect source database: [1]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nSelect target database: [2]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nWill limited DDL replication be enabled? (Yes\/No) [Yes]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nUse fetcher to offload the mining to a different server? (Yes\/No) [No]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nShould where clauses (and Event Streaming) include all columns, \nnot just changed and PK?\u00a0 \u00a0 \u00a0 (Yes\/No) [No]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nWould you like to encrypt the data across the network? (Yes\/No) [No]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nWould you like to compress the data across the network? (Yes\/No) [No]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nHow long do you want to set the network timeouts. \nRecommended range between 60-300 seconds\u00a0 \u00a0 [60]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nLock and copy the data initially one-by-one or at a single SCN?\none-by-one : Lock tables one by one and capture SCN\nsingle-scn : One SCN for all tables\nddl-only \u00a0 : Only DDL script for target objects\nresetlogs\u00a0 : Use SCN from last resetlogs operation\n(standby activation, rman incomplete\nrecovery)\nno-lock\u00a0 \u00a0 : Do not lock tables. Captures previous SCN of oldest active \ntransaction. Requires pre-requisite running of pre-all.sh script\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (one-by-one\/single-scn\/ddl-only\/resetlogs\/no-lock) [single-scn]<\/span>\u00a0\n\u00a0\nWhat data instantiation script to create?\nddl_file \u00a0 \u00a0 \u00a0 : DDL file created (APPLY.sql)\nddl_run\u00a0 \u00a0 \u00a0 \u00a0 : DDL is automatically executed on target\nload \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 : All replicated data is created and loaded automatically\nnone\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (ddl_file\/ddl_run\/load\/none) [ddl_run]<\/span> ddl-file<\/strong>\n\u00a0\n\u00a0\nFollowing replication pairs are now configured:\n1: PSI (Oracle) ==> postgres (Postgres), DDL: Yes, fetcher: No, \nprocess suffix: (no suffix),\ncompression: No, encryption: No, network timeout: 60, prepare type: \nsingle-scn,:\nddl-run\nEnter number of replication pair to modify it, or \"add\", or \"done\": [done]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nRead 1 replication pairs from previous wizard run.\n\u00a0\nStep 3 - Replicated tables\n========================================\nThe third step is to choose the schemas and tables to be replicated. \nIf the databases arereachable, the tables are checked for existence, \ndatatype support, etc., schemas are queried for tables. \nNote that all messages are merely hints\/warnings and may be ignored \nif issues are rectified before the scripts are actually executed.\n\u00a0\nFollowing tables are defined for replication pairs:\n1: PSI (Oracle) ==> postgres (Postgres), DDL: Yes, suffix: (no suffix), \nprepare: single-scn\n\u00a0 PSI(tables)\nEnter number of replication pair to modify it, or \"done\": [done]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nRead 2 replication pairs from previous wizard run.\n\u00a0\nStep 4 - Process configuration\n========================================\nThe fourth step is to configure the replication processes for each \nreplication.\n\u00a0\nFollowing processes are defined:\n1: MINE on PSI\n\u00a0 Host: cloud13c, SMTP: No, SNMP: No\n2: APPLY on postgres\n\u00a0 Host: pg1, SMTP: No, SNMP: No\nEnter number of process to modify it, or \"done\": [done]<\/span> 1<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nFully qualified name of the server for the process (usually co-located \nwith the database, unless\u00a0 mine is offloaded using fetcher): [cloud13c]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nServer type (Windows\/Linux\/Unix): [Linux]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nEnable email notifications about problems? (Yes\/No) [No]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nEnable SNMP traps\/notifications about problems? (Yes\/No) [No]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nDirectory with DDC file and default where to create log files etc. \n(recommended: same as global \u00a0 setting, if possible)? [\/home\/oracle\/ora2pg]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nFollowing settings were pre-filled with defaults or your reloaded settings:\n----------------------------------------\n[MINE_REMOTE_INTERFACE]: Network remote interface: cloud13c:7901\u00a0\n[MINE_DATABASE]: Database TNS: PSI\u00a0\n[TNS_ADMIN]: tnsnames.ora path: \/u00\/app\/oracle\/network\/admin\u00a0\n[MINE_PLOG]: Filemask for generated plogs: \/home\/oracle\/ora2pg\/mine\/%S.%E \n(%S is sequence, %T thread, %F original filename (stripped extension), \n%P process type, %N process name, %E default extension)\n[LOG_FILE]: General log file: \/home\/oracle\/ora2pg\/log\/dbvrep_%N_%D.%E\u00a0\n[LOG_FILE_TRACE]: Error traces: \n\/home\/oracle\/ora2pg\/log\/trace\/dbvrep_%N_%D_%I_%U.%E\u00a0\n\u00a0\nChecking that these settings are valid...\nDo you want to change any of the settings? [No]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nFollowing processes are defined:\n1: MINE on PSI\n\u00a0 Host: cloud13c, SMTP: No, SNMP: No\n2: APPLY on postgres\n\u00a0 Host: pg1, SMTP: No, SNMP: No\nEnter number of process to modify it, or \"done\": [done]<\/span> 2<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nFully qualified name of the server for the process (usually co-located \nwith the database, unless\u00a0 mine is offloaded using fetcher): [pg1]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nServer type (Windows\/Linux\/Unix): [Linux]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nEnable email notifications about problems? (Yes\/No) [No]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nEnable SNMP traps\/notifications about problems? (Yes\/No) [No]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nDirectory with DDC file and default where to create log files etc. \n(recommended: same as global \u00a0 setting, if possible)? [\/home\/oracle\/ora2pg]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nFollowing settings were pre-filled with defaults or your reloaded settings:\n----------------------------------------\n[APPLY_REMOTE_INTERFACE]: Network remote interface: pg1:7902\u00a0\n[APPLY_DATABASE]: Database Postgres connection string: dbname=postgres;\nhost=pg1\u00a0\n[TNS_ADMIN]: tnsnames.ora path: \/u00\/app\/oracle\/network\/admin\u00a0\n[APPLY_SCHEMA]: Dbvisit Replicate database (schema): dbvrep\u00a0\n[APPLY_STAGING_DIR]: Directory for received plogs: \/home\/oracle\/ora2pg\/apply\u00a0\n[LOG_FILE]: General log file: \/home\/oracle\/ora2pg\/log\/dbvrep_%N_%D.%E\u00a0\n[LOG_FILE_TRACE]: Error traces: \n\/home\/oracle\/ora2pg\/log\/trace\/dbvrep_%N_%D_%I_%U.%E\u00a0\n\u00a0\nChecking that these settings are valid...\nDo you want to change any of the settings? [No]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nFollowing processes are defined:\n1: MINE on PSI\n\u00a0 Host: cloud13c, SMTP: No, SNMP: No\n2: APPLY on postgres\n\u00a0 Host: pg1, SMTP: No, SNMP: No\nEnter number of process to modify it, or \"done\": [done]<\/span>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\nCreated file \/home\/oracle\/ora2pg\/ora2pg-APPLY.ddc.\nCreated file \/home\/oracle\/ora2pg\/ora2pg-MINE.ddc.\nCreated file \/home\/oracle\/ora2pg\/config\/ora2pg-setup.dbvrep.\nCreated file \/home\/oracle\/ora2pg\/config\/ora2pg-dbsetup_PSI_DBVREP.sql.\nCreated file \/home\/oracle\/ora2pg\/config\/ora2pg-dbsetup_DBNAME_POSTGRES_HOST_PG1_DBVREP.sql.\nCreated file \/home\/oracle\/ora2pg\/config\/ora2pg-grants_PSI_DBVREP.sql.\nCreated file \/home\/oracle\/ora2pg\/config\/ora2pg-grants_DBNAME_POSTGRES_HOST_PG1_DBVREP.sql.\nCreated file \/home\/oracle\/ora2pg\/config\/ora2pg-onetime.ddc.\nCreated file \/home\/oracle\/ora2pg\/start-console.sh.\nCreated file \/home\/oracle\/ora2pg\/ora2pg-run-cloud13c.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/ora2pg-cloud13c-start-MINE.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/ora2pg-cloud13c-stop-MINE.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/ora2pg-cloud13c-dbvrep-MINE.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/systemd-dbvrep-MINE_ora2pg.service.\nCreated file \/home\/oracle\/ora2pg\/scripts\/upstart-dbvrep-MINE_ora2pg.conf.\nCreated file \/home\/oracle\/ora2pg\/ora2pg-run-pg1.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/ora2pg-pg1-start-APPLY.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/ora2pg-pg1-stop-APPLY.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/ora2pg-pg1-dbvrep-APPLY.sh.\nCreated file \/home\/oracle\/ora2pg\/scripts\/systemd-dbvrep-APPLY_ora2pg.service.\nCreated file \/home\/oracle\/ora2pg\/scripts\/upstart-dbvrep-APPLY_ora2pg.conf.\nCreated file \/home\/oracle\/ora2pg\/Nextsteps.txt.\nCreated file \/home\/oracle\/ora2pg\/ora2pg-all.sh.\n============================================================================\nDbvisit Replicate wizard completed\nScript \/home\/oracle\/ora2pg\/ora2pg-all.sh created. \nThis runs all the above created scripts. Please exit out of dbvrep, \nreview and run script as current user to setup and start Dbvisit Replicate.\n============================================================================\nOptionally, the script can be invoked now by this wizard.\nRun this script now? (Yes\/No) [No]<\/span> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a0 \u00a0 dbvrep><\/span> exit<\/strong>\n\n<\/pre>\nAs it is asked at the end of the setup wizard, we\u00a0run the ora2pg_all.sh :<\/p>\n
oracle@localhost:\/home\/oracle\/ora2pg\/ [PSI] . ora2pg-all.sh\u00a0\nSetting up Dbvisit Replicate configuration\nConfigure database PSI...\nThis check fails if the DBID is not the expected one...\nOk, check passed.\nConfigure database dbname=postgres\nObject grants for database PSI...\nObject grants for database dbname=postgres\nSetting up the configuration\nInitializing......done\nDDC loaded from database (0 variables).\nDbvisit Replicate version 2.9.02\nCopyright (C) Dbvisit Software Limited. All rights reserved.\nDDC file \/home\/oracle\/ora2pg\/config\/ora2pg-onetime.ddc loaded.\nMINE: Cannot determine Dbvisit Replicate dictionary version. (no\ndictionary exists)\nAPPLY: Cannot determine Dbvisit Replicate dictionary version. (no\ndictionary exists)\ndbvrep> #clear the no-DDC-DB-available warning\ndbvrep> process clear previous warnings\ndbvrep> set ON_WARNING SKIP\nVariable ON_WARNING set to SKIP for process *.\ndbvrep> set ON_ERROR EXIT\nVariable ON_ERROR set to EXIT for process *.\ndbvrep>\u00a0\ndbvrep> # Configuring default processes\ndbvrep> choose process MINE\nProcess type MINE set to: MINE.\ndbvrep> choose process APPLY\nProcess type APPLY set to: APPLY.\ndbvrep> PROCESS SWITCH_REDOLOG\nRedo log switch requested.\ndbvrep> PROCESS SETUP MINE DROP DICTIONARY\n0 dictionary objects dropped.\ndbvrep> PROCESS SETUP MINE CREATE DICTIONARY\ndbvrep> PROCESS SETUP MINE LOAD DICTIONARY\nOldest active transaction SCN: 2054212 (no active transaction)\nSupplemental logging on database set.\ndbvrep> PROCESS SETUP APPLY DROP DICTIONARY\n0 dictionary objects dropped.\ndbvrep> PROCESS SETUP APPLY CREATE DICTIONARY\ndbvrep> PROCESS SETUP APPLY LOAD DICTIONARY\ndbvrep> PROCESS SETUP PAIR MINE AND APPLY\nApplier SCN set (start=2054228, current=2054228).\ndbvrep> SET APPLY.INSTANTIATE_SCN NOW\nVariable INSTANTIATE_SCN set to NOW for process APPLY.\ndbvrep> SET MINE._PREPARE_SUPLOG_TYPE PK\nVariable _PREPARE_SUPLOG_TYPE set to PK for process MINE.\ndbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_UNCMP #Ignore tables\ncreated by Compression Advisor\nExclude rule created.\ndbvrep> EXCLUDE CREATE TABLE %.DBMS_TABCOMP_TEMP_CMP #Ignore tables\ncreated by Compression Advisor\nExclude rule created.\ndbvrep> EXCLUDE CREATE TABLE %.SCHEDULER$_% #Ignore tables created by\nOracle scheduler (also used by schema\/full expdp\/impdp)\nExclude rule created.\ndbvrep> EXCLUDE CREATE TABLE %.CMP1$% #Ignore tables created by\nCompression Advisor since 11.2.0.4\nExclude rule created.\ndbvrep> EXCLUDE CREATE TABLE %.CMP2$% #Ignore tables created by\nCompression Advisor since 11.2.0.4\nExclude rule created.\ndbvrep> EXCLUDE CREATE TABLE %.CMP3$% #Ignore tables created by\nCompression Advisor since 11.2.0.4\nExclude rule created.\ndbvrep> EXCLUDE CREATE TABLE %.CMP4$% #Ignore tables created by\nCompression Advisor since 11.2.0.4\nExclude rule created.\ndbvrep> memory_set IGNORE_APPLY_DDL_DIFFERENCES Yes\nVariable IGNORE_APPLY_DDL_DIFFERENCES set to YES for process *.\ndbvrep> SET PREPARE_SCHEMA_EXCEPTIONS none\nVariable PREPARE_SCHEMA_EXCEPTIONS set to none for process *.\ndbvrep> PROCESS SUPPLEMENTAL LOGGING SCHEMA \"PSI\" ENABLE PRIMARY KEY\ndbvrep> PROCESS SWITCH_REDOLOG\nRedo log switch requested.\ndbvrep> PROCESS WAIT_SCN_FLIP\nWaited 1 seconds until scn_to_timestamp changed.\ndbvrep> #single-scn instantiation: lock all tables and schemas\ndbvrep> PROCESS LOCK SCHEMAS \"PSI\"\nLocking all schemas.\n...locked 2 of 2 tables from PSI schema.\nLock done.\ndbvrep> #single-scn instantiation: unlock all tables and schemas, but\nkeep the SCN\ndbvrep> PROCESS LOCK RELEASE LOCKS\nEngine locks released.\ndbvrep>\u00a0\ndbvrep> #prepare the tables (we use OFFLINE as neither MINE nor APPLY\nis running; with OFFLINE we won't wait on network timeout)\ndbvrep> PREPARE OFFLINE SCHEMA \"PSI\"\nTable PSI.EMPLOYE instantiated at SCN 2056800\nTable PSI.OFFICE instantiated at SCN 2056800\ndbvrep>\u00a0\ndbvrep> #single-scn instantiation: unlock all tables and schemas,\nforget the SCN (so it does not affect any further PREPARE statements)\ndbvrep> PROCESS LOCK CLEAR SCN\ndbvrep> PROCESS SWITCH_REDOLOG\nRedo log switch requested.\ndbvrep> #prepare script for instantiation\ndbvrep> PROCESS PREPARE_DP WRITE DDL_FILE FILE\n\/home\/oracle\/ora2pg\/APPLY.sql USERID SYSTEM\/manager@PSI\nFile \/home\/oracle\/ora2pg\/APPLY.sql has been written successfully.\nCreated DDL script \/home\/oracle\/ora2pg\/APPLY.sql.\ndbvrep> create ddcdb from ddcfile\nDDC loaded into database (430 variables).\ndbvrep> load ddcdb\nDDC loaded from database (430 variables).\ndbvrep> set ON_WARNING SKIP\nVariable ON_WARNING set to SKIP for process *.\ndbvrep> set ON_ERROR SKIP\nVariable ON_ERROR set to SKIP for process *.\nOK-0: Completed successfully.\nWARN-1850: No DDC DB available, dictionary table does not exist.\nThese steps are required after the ora2pg-all.sh script runs:\n\u00a0\n1) Create the necessary directory(ies) on the servers:\ncloud13c: \/home\/oracle\/ora2pg\npg1: \/home\/oracle\/ora2pg\n\u00a0\n2) Copy the DDC files to the server(s) where the processes will run:\npg1: \/home\/oracle\/ora2pg\/ora2pg-APPLY.ddc\ncloud13c: \/home\/oracle\/ora2pg\/ora2pg-MINE.ddc\n\u00a0\nEnsure that the parameter TNS_ADMIN (in the ddc file) is pointing to the correct TNS_ADMIN path on each of the servers.\n\u00a0\n3) Review that path to dbvrep executable is correct in the run scripts:\n\/home\/oracle\/ora2pg\/ora2pg-run-cloud13c.sh\n\/home\/oracle\/ora2pg\/ora2pg-run-pg1.sh\n\u00a0\n4) Copy the run script to the server(s) where the processes will run:\ncloud13c: \/home\/oracle\/ora2pg\/ora2pg-run-cloud13c.sh\npg1: \/home\/oracle\/ora2pg\/ora2pg-run-pg1.sh\n\u00a0\n5) Ensure firewall is open for listen interfaces 0.0.0.0:7902, 0.0.0.0:7901 used by the processes.\n\u00a0\n6) Make sure the data on apply are in sync as of time when setup was run.\nScripts for Data Pump\/export\/DDL were created as requested:\n\u00a0\nCreate referenced database links (if any) before running the scripts.\n\/home\/oracle\/ora2pg\/APPLY.sql\n\u00a0\n7) Start the replication processes on all servers:\ncloud13c: \/home\/oracle\/ora2pg\/ora2pg-run-cloud13c.sh\npg1: \/home\/oracle\/ora2pg\/ora2pg-run-pg1.sh\n\u00a0\n8) Start the console to monitor the progress:\n\/home\/oracle\/ora2pg\/start-console.sh<\/pre>\nAs explained you have to copy two files on the postgres server : \/home\/oracle\/ora2pg\/ora2pg-APPLY.ddc and \/home\/oracle\/ora2pg\/ora2pg-run-pg1.sh<\/p>\n
As I choosed the option ddl_only, we have to first create the tables on the postgres server. In order to do this we can use the APPLY:sql file from the Oracle server.<\/p>\n
The next step consist in running the MINE process on the Oracle server:<\/p>\n
oracle@localhost:\/home\/oracle\/ora2pg\/ [PSI] . ora2pg-run-cloud13c.sh\u00a0\nInitializing......done\nDDC loaded from database (430 variables).\nDbvisit Replicate version 2.9.02\nCopyright (C) Dbvisit Software Limited. All rights reserved.\nDDC file \/home\/oracle\/ora2pg\/ora2pg-MINE.ddc loaded.\nStarting process MINE...started<\/pre>\nAnd we launch the APPLy process on the postgres server:<\/p>\n
postgres@pg_essentials_p1:\/home\/oracle\/ora2pg\/ [PG1] . ora2pg-run-pg1.sh\u00a0\nInitializing......done\nDDC loaded from database (431 variables).\nDbvisit Replicate version 2.9.02\nCopyright (C) Dbvisit Software Limited. All rights reserved.\nDDC file \/home\/oracle\/ora2pg\/ora2pg-APPLY.ddc loaded.\nStarting process APPLY...Created directory \/home\/oracle\/ora2pg\/ddc_backup\nCreated directory \/home\/oracle\/ora2pg\/log\/\nCreated directory \/home\/oracle\/ora2pg\/log\/trace\/\nCreated directory \/home\/oracle\/ora2pg\/apply\nstarted<\/pre>\nInitially I had two tables in my PSI oracle database belonging to the psi schema: EMPLOYE and OFFICE. I used the APPLY.sql script to create the tables in the postgres environment.<\/p>\n
To visualize the activity we run start_console.sh on the Oracle server:<\/p>\n
oracle@localhost:\/home\/oracle\/ora2pg\/ [PSI] . start-console.sh\u00a0\nInitializing......done\nDDC loaded from database (431 variables).\nDbvisit Replicate version 2.9.02\nCopyright (C) Dbvisit Software Limited. All rights reserved.\n\u00a0\n| Dbvisit Replicate 2.9.02(MAX edition) - Evaluation License expires in 30 days\nMINE is running. Currently at plog 120 and SCN 2060066 (11\/07\/2017 15:27:57).\nAPPLY is running. Currently at plog 120 and SCN 2060021 (11\/07\/2017 15:27:45).\nProgress of replication ora2pg:MINE->APPLY: total\/this execution\n-------------------------------------------------------------------------------------------------\nPSI.EMPLOYE\/psi.employe:\u00a0 \u00a0 \u00a0 100%\u00a0 Mine:1\/1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Unrecov:0\/0 \u00a0 \u00a0 \u00a0 \u00a0 Applied:1\/1 \u00a0 \u00a0 \u00a0 \u00a0 Conflicts:0\/0 \u00a0 \u00a0 \u00a0 Last:07\/11\/2017 15:20:06\/OK\nPSI.OFFICE\/psi.office:\u00a0 \u00a0 \u00a0 \u00a0 100%\u00a0 Mine:1\/1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Unrecov:0\/0 \u00a0 \u00a0 \u00a0 \u00a0 Applied:1\/1 \u00a0 \u00a0 \u00a0 \u00a0 Conflicts:0\/0 \u00a0 \u00a0 \u00a0 Last:07\/11\/2017 15:21:36\/OK\n-------------------------------------------------------------------------------------------------\n2 tables listed.<\/pre>\nAnd we can validate that each insert in the employe or office table is replicated on the postgres server:<\/p>\n
From the postgres database;<\/p>\n
(postgres@[local]:5432) [postgres] > select * from psi.employe;\n\u00a0name\u00a0 | salary\u00a0\n-------+--------\n\u00a0Larry |\u00a0 10000\n\u00a0Bill\u00a0 | \u00a0 2000\n(2 rows)<\/pre>\nFrom the Oracle server:<\/p>\n
SQL> insert into employe values ('John', 50000);\n\u00a0\n1 row created.\n\u00a0\nSQL> commit;\n\u00a0\nCommit complete.<\/pre>\nThe console is giving us correct informations:<\/p>\n
\/ Dbvisit Replicate 2.9.02(MAX edition) - Evaluation License expires in 30 days\nMINE is running. Currently at plog 120 and SCN 2075526 (11\/07\/2017 16:44:17).\nAPPLY is running. Currently at plog 120 and SCN 2075494 (11\/07\/2017 16:44:08).\nProgress of replication ora2pg:MINE->APPLY: total\/this execution\n-------------------------------------------------------------------------------------------------\nPSI.EMPLOYE\/psi.employe:\u00a0 \u00a0 \u00a0 100%\u00a0 Mine:3\/3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Unrecov:0\/0 \u00a0 \u00a0 \u00a0 \u00a0 Applied:3\/3 \u00a0 \u00a0 \u00a0 \u00a0 Conflicts:0\/0 \u00a0 \u00a0 \u00a0 Last:07\/11\/2017 16:18:41\/OK\nPSI.OFFICE\/psi.office:\u00a0 \u00a0 \u00a0 \u00a0 100%\u00a0 Mine:3\/3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Unrecov:0\/0 \u00a0 \u00a0 \u00a0 \u00a0 Applied:3\/3 \u00a0 \u00a0 \u00a0 \u00a0 Conflicts:0\/0 \u00a0 \u00a0 \u00a0 Last:07\/11\/2017 15:37:02\/OK\n-------------------------------------------------------------------------------------------------\n2 tables listed.\n\nAnd the result is apllied on the postgres database:<\/pre>\n(postgres@[local]:5432) [postgres] > select * from psi.employe;\n\u00a0name\u00a0 | salary\u00a0\n-------+--------\n\u00a0Larry |\u00a0 10000\n\u00a0Bill\u00a0 | \u00a0 2000\n\u00a0John\u00a0 |\u00a0 50000\n(3 rows)\n\u00a0\nAs previously we have choosen the single-scn and ddl-run option, we had to run the APPLY.sql script from the Oracle server in order to create the tables on the postgres side, you can also choose in Step 2 of the configuration wizard, the load option (all replicated data is created and loaded automatically):<\/pre>\nLock and copy the data initially one-by-one or at a single SCN?\none-by-one : Lock tables one by one and capture SCN\nsingle-scn : One SCN for all tables\nddl-only \u00a0 : Only DDL script for target objects\nresetlogs\u00a0 : Use SCN from last resetlogs operation (standby activation, rman incomplete\nrecovery)\nno-lock\u00a0 \u00a0 : Do not lock tables. Captures previous SCN of oldest active transaction. Requires\npre-requisite running of pre-all.sh script\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (one-by-one\/single-scn\/ddl-only\/resetlogs\/no-lock) [single-scn]<\/span>\u00a0\n\u00a0\nWhat data instantiation script to create?\nddl_file \u00a0 \u00a0 \u00a0 : DDL file created (APPLY.sql)\nddl_run\u00a0 \u00a0 \u00a0 \u00a0 : DDL is automatically executed on target\nload \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 : All replicated data is created and loaded automatically\nnone\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (ddl_file\/ddl_run\/load\/none) [ddl_run]<\/span> load<\/strong>\nDo you want to (re-)create the tables on target or keep them (they are already created)?\u00a0 \u00a0 \u00a0 (create\/keep) [keep]<\/span> create<\/strong><\/pre>\nIn this case you can visualize that each Oracle table is replicated to the Postgres server.<\/p>\n
From the oracle server:<\/p>\n
SQL> create table salary (name varchar2(10));\u00a0\n\u00a0\nTable created.\n\u00a0\nSQL> insert into salary values ('Larry');\n\u00a0\n1 row created.\n\u00a0\nSQL> commit;\n\u00a0\nCommit complete.<\/pre>\nThe dbvist console displays correct informations:<\/p>\n
\\ Dbvisit Replicate 2.9.02(MAX edition) - Evaluation License expires in 30 days\nMINE is running. Currently at plog 135 and SCN 2246259 (11\/27\/2017 14:44:24).\nAPPLY is running. Currently at plog 135 and SCN 2246237 (11\/27\/2017 14:44:18).\nProgress of replication replic:MINE->APPLY: total\/this execution\n--------------------------------------------------------------------------------------\nREP.SALARY: \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 100%\u00a0 Mine:1\/1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Unrecov:0\/0 \u00a0 \u00a0 \u00a0 \u00a0 Applied:1\/1 \u00a0 \u00a0 \u00a0 \u00a0 Conflicts:0\/0 \u00a0 \u00a0 \u00a0 Last:27\/11\/2017 14:01:25\/OK\n---------------------------------------------------------------------------------------------\n1 tables listed.<\/pre>\nFrom the postgres server:<\/p>\n
(postgres@[local]:5432) [postgres] > select * from rep.salary;\n\u00a0name \u00a0\n-------\n\u00a0Larry\n(1 row)<\/pre>\nThe plog files generated in the postgres server contains the strings we need:<\/p>\n
The plot files are generated on the postgres server in the directory \/home\/oracle\/replic\/apply<\/p>\n
-bash-4.2$ ls\n122.plog.gz<\/strong>\u00a0 124.plog.gz<\/strong>\u00a0 126.plog\u00a0 128.plog.gz<\/strong>\u00a0 130.plog.gz<\/strong>\u00a0 132.plog.gz<\/strong>\u00a0 134.plog\n123.plog.gz<\/strong>\u00a0 125.plog.gz<\/strong>\u00a0 127.plog\u00a0 129.plog.gz<\/strong>\u00a0 131.plog.gz<\/strong>\u00a0 133.plog.gz<\/strong>\u00a0 135.plog\n-bash-4.2$ strings 135.plog | grep -l larry\n-bash-4.2$ strings 135.plog | grep -i larry\nLarry<\/strong>\n-bash-4.2$ strings 135.plog | grep -i salary\nSALARY<\/strong>\ncreate table salary<\/strong> (name varchar2(10))\nSALARY<\/strong>\nSALARY<\/strong><\/pre>\nDespite some problems at the beginning of my tests, the replication from Oracle to PostgreSQL is working fine and fast. There are many\u00a0possibilities with Dbvisit Replicate I will try to test in the following weeks.<\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
As I regularly work on Oracle and PostgreSQL, I decided to test the replication from Oracle to PostgreSQL using the Dbvisit Replicate tool. Dbivisit Replicate does not use Oracle logminer ot triggers but its own mining processes to get the changes when they are written to the redo logs. When a change appears in the […]<\/p>\n","protected":false},"author":27,"featured_media":10672,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[429,96,77],"type_dbi":[],"class_list":["post-10671","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-dbvisit-replicate","tag-oracle","tag-postgresql"],"acf":[],"yoast_head":"\n
DBVISIT from Oracle to Postgres - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DBVISIT from Oracle to Postgres\" \/>\n<meta property=\"og:description\" content=\"As I regularly work on Oracle and PostgreSQL, I decided to test the replication from Oracle to PostgreSQL using the Dbvisit Replicate tool. Dbivisit Replicate does not use Oracle logminer ot triggers but its own mining processes to get the changes when they are written to the redo logs. When a change appears in the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-11-27T14:51:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png\" \/>\n\t<meta property=\"og:image:width\" content=\"414\" \/>\n\t<meta property=\"og:image:height\" content=\"411\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"20 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"DBVISIT from Oracle to Postgres\",\"datePublished\":\"2017-11-27T14:51:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/\"},\"wordCount\":592,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png\",\"keywords\":[\"Dbvisit replicate\",\"Oracle\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/\",\"name\":\"DBVISIT from Oracle to Postgres - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png\",\"datePublished\":\"2017-11-27T14:51:43+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png\",\"width\":414,\"height\":411},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DBVISIT from Oracle to Postgres\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"DBVISIT from Oracle to Postgres - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/","og_locale":"en_US","og_type":"article","og_title":"DBVISIT from Oracle to Postgres","og_description":"As I regularly work on Oracle and PostgreSQL, I decided to test the replication from Oracle to PostgreSQL using the Dbvisit Replicate tool. Dbivisit Replicate does not use Oracle logminer ot triggers but its own mining processes to get the changes when they are written to the redo logs. When a change appears in the […]","og_url":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/","og_site_name":"dbi Blog","article_published_time":"2017-11-27T14:51:43+00:00","og_image":[{"width":414,"height":411,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png","type":"image\/png"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"20 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"DBVISIT from Oracle to Postgres","datePublished":"2017-11-27T14:51:43+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/"},"wordCount":592,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png","keywords":["Dbvisit replicate","Oracle","PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/","url":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/","name":"DBVISIT from Oracle to Postgres - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png","datePublished":"2017-11-27T14:51:43+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png","width":414,"height":411},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/dbvisit-from-oracle-to-postgres\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"DBVISIT from Oracle to Postgres"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10671"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10671\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/10672"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10671"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"dbvisit\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/dbvisit.png\")
<\/a><\/p>\n