-
Notifications
You must be signed in to change notification settings - Fork 0
Home
ujangja edited this page Oct 28, 2022
·
22 revisions
Data Flow:
oracle (source) --> xdb publisher --> xdb replication server --> xdb subscriber --> edb database (target)
Target: minimize downtime
Component:
xdb replication server --> replicate oracle db to postgres
EDB MTK (migration tool kit) --> import all other schema's objects
livecompare --> compare migrated data between oracle and edb postgres
dnf install -y libaio-devel sysstat wget elinks bzip2 perl compat-openssl10 policycoreutils-python-utils ksh libnsl make gcc bzip2 unzip java-1.8.0-openjdk
systemctl stop firewalld systemctl disable firewalld
wget https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
see ERD at: https://i.stack.imgur.com/RbULW.png
su - oracle cd /home/oracle wget https://github.com/bbrumm/oracle_sample/archive/refs/heads/main.zip
rpm -ivh oracle-database-preinstall-21c-1.0-1.el8.x86_64.rpm rpm -ivh oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
mkdir /home/oracle/oradata chown -R oracle:oinstall /home/oracle/oradata
cp /etc/sysconfig/oracle-xe-21c.conf /etc/sysconfig/oracle-xe-21c.conf.ori sed -i 's/LISTENER_PORT=/LISTENER_PORT=1521/g' /etc/sysconfig/oracle-xe-21c.conf sed -i 's/DBFILE_DEST=/DBFILE_DEST=\/home\/oracle\/oradata/g' /etc/sysconfig/oracle-xe-21c.conf sed -i 's/DB_DOMAIN=/DB_DOMAIN=demo/g' /etc/sysconfig/oracle-xe-21c.conf diff /etc/sysconfig/oracle-xe-21c.conf /etc/sysconfig/oracle-xe-21c.conf.ori
/etc/init.d/oracle-xe-21c configure
be used for SYS, SYSTEM and PDBADMIN accounts: oracle_4U Confirm the password: oracle_4U Database Information: ------------------------- Global Database Name:XE.demo System Identifier(SID):XE Pluggable database: pgdev/XEPDB1 Multitenant container database: pgdev Use https://localhost:5500/em to access Oracle Enterprise Manager for Oracle Database XE
su - oracle echo "export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE" >> .xe echo "export ORACLE_SID=XE" >> .xe echo "export PDB_NAME=XEPDB1" >> .xe echo "export PATH=$ORACLE_HOME/bin:$PATH" >> .xe echo "alias sq='sqlplus / as sysdba'" >> .xe
. .xe unzip main.zip mv '01 account.sql' 01.sql mv '02 create tables.sql' 02.sql mv '03 populate tables.sql' 03.sql mv '04 others.sql' 04.sql sed -i 's/ORCLPDB1/XEPDB1/g' 01.sql
sq alter session set container=XEPDB1; @01.sql exit sqlplus hr/hrpass@//localhost:1521/XEPDB1.demo @02.sql @03.sql @04.sql exit
sq show pdbs alter session set container=XEPDB1; grant connect,resource,create trigger,create job to hr; exit
curl -1sLf 'https://downloads.enterprisedb.com/XYZXYZTOKEN/enterprise/setup.rpm.sh' | sudo -E bash sudo dnf -y install edb-jdbc.x86_64 edb-migrationtoolkit edb-as14-server edb-xdb edb-livecompare
PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as14/bin/edb-as-14-setup initdb
systemctl start edb-as-14 systemctl enable edb-as-14
su - enterprisedb psql edb create user output ENCRYPTED PASSWORD 'outputpg'; create database output owner output; create user hr ENCRYPTED PASSWORD 'hrpasspg'; create database hr owner hr; \q
vi /var/lib/edb/as14/data/pg_hba.conf host all all 127.0.0.1/32 scram-sha-256 host all all 0.0.0.0/0 scram-sha-256 #host all all 127.0.0.1/32 ident
psql edb select pg_reload_conf(); select * from pg_hba_file_rules ; \q
psql "host=127.0.0.1 port=5444 user=hr dbname=hr password=hrpasspg" create schema hr; ALTER ROLE hr SET search_path TO hr, public; \q
su - cp /opt/oracle/product/21c/dbhomeXE/jdbc/lib/ojdbc8.jar /usr/edb/migrationtoolkit/lib/ chmod 644 /usr/edb/migrationtoolkit/lib/ojdbc8.jar vi /usr/edb/migrationtoolkit/etc/toolkit.properties SRC_DB_URL=jdbc:oracle:thin:@localhost:1521/XEPDB1.demo SRC_DB_USER=hr SRC_DB_PASSWORD=hrpass TARGET_DB_URL=jdbc:edb://localhost:5444/hr TARGET_DB_USER=hr TARGET_DB_PASSWORD=hrpasspg
cp /opt/oracle/product/21c/dbhomeXE/jdbc/lib/ojdbc8.jar /usr/edb/xdb/lib/jdbc/ cp /opt/oracle/product/21c/dbhomeXE/jdbc/lib/ojdbc8.jar /usr/lib/jvm/jre/lib/ext chmod 644 /usr/edb/xdb/lib/jdbc/ojdbc8.jar chmod 644 /usr/lib/jvm/jre/lib/ext/ojdbc8.jar cd /home java -jar /usr/edb/xdb/bin/edb-repcli.jar -version
echo "oracle" > passfile java -jar /usr/edb/xdb/bin/edb-repcli.jar -encrypt -input passfile -output passfile-encrypted cat passfile-encrypted deIuKoLKPi4=
vi /etc/edb-repl.conf admin_user=admin admin_password=deIuKoLKPi4=
su - pip3 install --user cx_Oracle --upgrade cp /etc/livecompare/template_basic.ini /home/my_project.ini vi /home/my_project.ini [General Settings] logical_replication_mode = off max_parallel_workers = 4 oracle_user_tables_only = on oracle_ignore_unsortable = on column_intersection = on force_collate = C difference_tie_breakers = oracle schema_qualified_table_names = off [Oracle Connection] technology = oracle host = localhost port = 1521 service = XEPDB1.demo user = hr password = hrpass [Postgres Connection] technology = postgresql dsn = host=127.0.0.1 port=5444 user=hr dbname=hr password=hrpasspg start_query = SET search_path = hr, public [Output Connection] dsn = host=127.0.0.1 port=5444 user=output dbname=output password=outputpg [Table Filter] schemas = schema_name = 'hr'
vi check_trigger.sql SELECT pg_namespace.nspname, pg_class.relname, pg_trigger.tgname, pg_trigger.tgenabled, pg_trigger.tgctype, pg_trigger.tgdeferrable FROM pg_trigger JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace where pg_trigger.tgisinternal='f';
vi gen_disable_trigger.sql SELECT 'alter table '||pg_namespace.nspname||'.'||pg_class.relname||' disable trigger '||pg_trigger.tgname|| ';' FROM pg_trigger JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE pg_trigger.tgisinternal='f' and pg_trigger.tgenabled <> 'D' ;
vi gen_enable_trigger.sql SELECT 'alter table '||pg_namespace.nspname||'.'||pg_class.relname||' enable trigger '||pg_trigger.tgname|| ';' FROM pg_trigger JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE pg_trigger.tgisinternal='f' and pg_trigger.tgenabled <> 'D' ;
/usr/edb/xdb/bin/runPubServer.sh & /usr/edb/xdb/bin/runSubServer.sh &
/usr/edb/xdb/bin/runRepConsole.sh
follow GUI wizards: create pub, sub, snapshot, sync
https://github.com/ujangja/demo_mig_oracle_to_edb/blob/main/xdb-gui.pdf
export JAVA_HOME=/usr/lib/jvm/jre-1.8.0-openjdk export PATH=$JAVA_HOME/bin:$PATH /usr/edb/migrationtoolkit/bin/runMTK.sh -help /usr/edb/migrationtoolkit/bin/runMTK.sh -verbose on -schemaOnly -dropSchema false \ -tables COUNTRIES,DEPARTMENTS,EMPLOYEES,JOBS,JOB_HISTORY,LOCATIONS,REGIONS \ -triggers -allViews -allProcs -allFuncs -allPackages -allSequences -allDBLinks \ -allSynonyms -allPublicSynonyms -allPrivateSynonyms -allQueues HR
psql "host=127.0.0.1 port=5444 user=hr dbname=hr password=hrpasspg" \i check_trigger.sql \! rm -f disable_trigger.sql \t \o disable_trigger.sql \i gen_disable_trigger.sql \! cat disable_trigger.sql \! rm -f enable_trigger.sql \o enable_trigger.sql \i gen_enable_trigger.sql \! cat enable_trigger.sql\ \q
psql "host=127.0.0.1 port=5444 user=hr dbname=hr password=hrpasspg" \i disable_trigger.sql \q
psql "host=127.0.0.1 port=5444 user=hr dbname=hr password=hrpasspg" \i check_trigger.sql
su - oracle . .xe sqlplus hr/hrpass@//localhost:1521/XEPDB1.demo delete from departments where DEPARTMENT_ID in (280,290); commit; insert into departments values(280,'zzz',null,1700); insert into departments values(290,'yyyy',null,1700); commit;
SELECT * FROM hr.departments e order by 1 desc;
psql "host=127.0.0.1 port=5444 user=hr dbname=hr password=hrpasspg" select * from departments order by 1 desc; \q
cd /home export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH livecompare my_project.ini --compare
cat ./lc_session_1/summary_20221024.out psql "host=127.0.0.1 port=5444 user=output dbname=output password=outputpg" select * from livecompare.vw_table_summary where session_id = 1; \q