<marquee> HOW MIGRASI MRM_PAM ORACLE 11g TO EDB AND BACKUP RESTORE POSTGRESQL VERSI 10 </marquee>

Download terlebih dahulu ojdbc dan postgresql-42.2.2 dalam bentuk jar dan pindahkan lokasinya kedalam lokasi folder java tersimpan. Dalam konteks ini lokasi java terdapat di /usr/java/jdk1.8.0_152/jre/lib/ext, copy 2 file hasil download ke lokasi tersebut.

[root@prod-postgres ~]# cd /usr/java/jdk1.8.0_152/jre/lib/ext

[root@prod-postgres ext]# mv /home/prod-postgres/ojdbc6.jar /usr/java/jdk1.8.0_152/jre/lib/ext/

[root@prod-postgres ext]# mv /home/prod-postgres/postgresql-42.2.2.jar /usr/java/jdk1.8.0_152/jre/lib/ext/

[root@prod-postgres ext]# ll

-rw-r--r--. 1 10 143 18513457 Sep 14 2017 jfxrt.jar

-rw-r--r--. 1 10 143 68965 Sep 14 2017 zipfs.jar

-rw-r--r--. 1 10 143 248726 Sep 14 2017 sunpkcs11.jar

-rw-r--r--. 1 10 143 273952 Sep 14 2017 sunjce_provider.jar

-rw-r--r--. 1 10 143 41672 Sep 14 2017 sunec.jar

-rw-r--r--. 1 10 143 2023991 Sep 14 2017 nashorn.jar

-rw-r--r--. 1 10 143 746 Sep 14 2017 meta-index

-rw-r--r--. 1 10 143 1179101 Sep 14 2017 localedata.jar

-rw-r--r--. 1 10 143 44516 Sep 14 2017 jaccess.jar

-rw-r--r--. 1 10 143 8286 Sep 14 2017 dnsns.jar

-rw-r--r--. 1 10 143 3860502 Sep 14 2017 cldrdata.jar

-rw-r--r-- 1 root root 790405 Apr 27 18:38 postgresql-42.2.2.jar

-rw-r--r-- 1 root root 2714189 Apr 27 18:38 ojdbc6.jar

[root@prod-postgres ext]# chown root:daemon postgresql-42.2.2.jar

[root@prod-postgres ext]# chown root:daemon ojdbc6.jar

[root@prod-postgres ext]# chmod +x postgresql-42.2.2.jar

[root@prod-postgres ext]# chmod +x ojdbc6.jar

Jika sudah dilakukan copy 2 file diatas dan merubah hak akses nya, maka langkah selanjutnya adalah melakukan cek untuk software migration toolkit, apakah sudah terinstall migration toolkit atau belum, dengan masuk ke lokasi dimana edb terinstall, disini edb berada dilokasi /DATA/cluster1/ :

[root@prod-postgres ext]# cd /DATA/cluster1/

[root@prod-postgres cluster1]# ll

total 7964

drwxr-xr-x 13 root daemon 4096 Mar 20 12:58 as9.6

drwxr-xr-x 5 root daemon 4096 Mar 20 12:57 connectors

drwxr-xr-x 6 root daemon 4096 Mar 20 12:58 icache

drwxr-xr-x 5 root daemon 4096 Mar 20 12:58 mtk

drwxr-xr-x 11 root daemon 4096 Mar 20 12:58 pgAdmin4

drwxr-xr-x 7 root daemon 4096 Mar 20 12:58 pgbouncer1.7

drwxr-xr-x 8 root daemon 4096 Mar 20 12:58 pgpool3.5

drwxr-xr-x 7 root daemon 4096 Mar 20 12:59 sbp

-rwx------ 1 root daemon 8037014 Mar 20 12:59 uninstall-edb-as96-meta

-rw------- 1 root root 79469 Mar 20 12:59 uninstall-edb-as96-meta.dat

Terdapat folder mtk coba kita masuk kedalam folder etc yang ada di dalam folder mtk.

[root@prod-postgres cluster1]# cd mtk/etc

[root@prod-postgres etc]# pwd

/DATA/cluster1/mtk/etc

[root@prod-postgres etc]# ll

total 8

drwxr-xr-x 2 root daemon 4096 Mar 20 12:57 sysconfig

-rw------- 1 root daemon 208 Apr 28 14:57 toolkit.properties

Lakukkan edit pada file “toolkit.properties” untuk proses configurasi migrasi dari oracle ke edb.

[root@prod-postgres etc]# vi toolkit.properties

SRC_DB_URL=jdbc:oracle:thin:@IP_ORACLE:1852:MRM_PAM

SRC_DB_USER=MRM_PAM

SRC_DB_PASSWORD=PASS

TARGET_DB_URL=jdbc:edb://IP_EDB:5432/ MARMER

TARGET_DB_USER=enterprisedb

TARGET_DB_PASSWORD=

Lakukan save dengan perintah ctrl + c → shift+: → wq! .

Kemudian install screen dengan mendownload package rpm screen di web “centos.biz.net.id/7.4.1708/os/x86_64/Packages/screen-4.1.0-0.23.20120314git3c2946.el7_2.x86_64.rpm” dan ketikan perintah untuk installasi menggunakan rpm dibawah ini.

[root@prod-postgres etc]# cd

[root@prod-postgres ~]# rpm -ivh screen-4.1.0-0.23.20120314git3c2946.el7_2.x86_64.rpm

Jika berhasil, langkah selanjutnya buat screen baru dengan nama pdf, sdf, sf dengan cara:

[root@prod-postgres ~]# cd /DATA/cluster1/mtk/bin

[root@prod-postgres bin]# screen -dmS pdf

[root@prod-postgres bin]# screen -dmS bdf

[root@prod-postgres bin]# screen -dmS bf

[root@prod-postgres bin]# screen -ls

There are screens on:

2122.bfv10 (Detached)

2036.bdfv10 (Detached)

1856.pdfv10 (Detached)

377.pts-2.prod-postgres (Detached)

2444.bf (Detached)

2334.bdf (Detached)

2208.pdf (Detached)

7 Sockets in /var/run/screen/S-root.

Kemudian ketika kita ketikan perintah screen -x [namascreen] mka akan pindah ke lokasi screen baru yang terbuat dan jalankan perintah.

[root@prod-postgres ~]# cd /DATA/cluster1/mtk/bin

[root@prod-postgres bin]# pwd

/DATA/cluster1/mtk/bin

[root@prod-postgres bin]# screen -x pdf

[root@prod-postgres bin]# ./runMTK.sh -dataOnly -tables CV_MARMER_2014 -targetSchema public MRM_PAM

Untuk meminimize ketika proses sedang berjalan di screen pdf cukup dengan menekan perintah “ctrl + ad” . INGAT JANGAN MENEKAN PERINTAH ctrl+c atau memasukkan keyword “exit” sebelum proses tersebut selesai. Jika proses selesai nanti akan memunculkan informasi seperti dibawah ini.

[PS_DOKUMEN_FILE] Migrated 908,952 rows.

[PS_DOKUMEN_FILE] Migrated 908,953 rows.

[PS_DOKUMEN_FILE] Migrated 908,954 rows.

[PS_DOKUMEN_FILE] Table Data Load Summary: Total Time(s): 20852.048 Total Rows: 908954

Data Load Summary: Total Time (sec): 20852.168 Total Rows: 908954 Total Size(MB): 0.0

Schema MRM_PAM imported successfully.

Migration process completed successfully.

Migration logs have been saved to /root/.enterprisedb/migration-toolkit/logs

******************** Migration Summary ********************

Tables: 1 out of 1

Total objects: 1

Successful count: 1

Failed count: 0

Invalid count: 0

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

[root@prod-postgres bin]#

Dan untuk mengecek apakah data rownya benar berjumalah sesuai yang tertera pada data yang ada di oracle atau tidak dengan masuk ke sql pada edb dan cek dengan menggunakan query select.

1. cv_marmer_2014 berjumalah “844366”

2. ps_dokumen_file berjumalah “908954”

3. belanja_dokumen_file berjumalah “239816”

4. belanja_file berjumalah “3949177”

[root@prod-postgres ~]# su - enterprisedb

Last login: Sen Apr 30 07:05:19 WIB 2018 on pts/15

-bash-4.2$ psql -p 5444 -d edb

psql.bin (9.6.2.7)

Type "help" for help.

edb=# \l

List of databases

--------------+--------------+----------+-------------+-------------+-----+-------------------------------

(7 rows)

edb=# \c MARMER

You are now connected to database "MARMER" as user "enterprisedb".

MARMER=# \dt

List of relations

Schema | Name | Type | Owner

--------+-----------------------------+-------+--------------

public | cv_marmer_2014 | table | enterprisedb

public | ps_dokumen_file | table | enterprisedb

public | belanja_dokumen_file | table | enterprisedb

public | belanja_file | table | enterprisedb

(4 rows)

MARMER=# select count(1) from cv_marmer_2014;

count

--------

844366

(1 row)

MARMER=# select count(1) from ps_dokumen_file;

count

--------

908954

(1 row)

MARMER=# select count(1) from belanja_dokumen_file;

count

--------

239816

(1 row)

MARMER=# select count(1) from belanja_file;

count

---------

3949177

(1 row)

MARMER=#

Karena pada migrasi langsung ke postgres ggal, maka kita lakukan ke edb dan jika tetap ingin ke postgres, cukup backup dan restore data yang ada di edb hasil migrasi. Dengan cara membuat screen dengan nama baru. Dan lakukan perintah dump.

[root@prod-postgres bin]# screen -dmS pdfv10

[root@prod-postgres bin]# screen -dmS bdfv10

[root@prod-postgres bin]# screen -dmS bfv10

[root@prod-postgres bin]# screen -ls

There are screens on:

2122.bfv10 (Detached)

2036.bdfv10 (Detached)

1856.pdfv10 (Detached)

377.pts-2.prod-postgres (Detached)

2444.bf (Detached)

2334.bdf (Detached)

6 Sockets in /var/run/screen/S-root.

[root@prod-postgres bin]# screen -x pdfv10

[root@prod-postgres bin]# screen -x sdfv10

[root@prod-postgres bin]# screen -x sfv10

Dan masukkan perintah pgdump terlebih dahulu setelah selesai baru menjalankan restore database dengan perintah psql10 karena versi postgresql disini adalah postgres versi 10. Dan pastikan rownya sama jika semua sudah selesai.

PG_DUMP pg_dump -h ip -U username -w -v --table=”nama tabel” --data-only --column-inserts NAMA DATABASE 1> /PATHSAVENEWFILESQL 2> /PATHLOGHASILDUMPTXT

PG_RESTORE psql10 -h ip -p port -U username -w -v -e -d NAMA DATABASE < /PATHHASILDUMPSQL > /PATHLOGHASILRESTORETXT

Cek syncron data atau tidak di postgresql 10 antara master di ip master dan slave ip. dikarenakan fungsi untuk cek syncron data di postgresql 10 berbeda dengan 9.x.

Master ip

[postgres@prod-postgres data]$ psql10 -p 8884

psql10 (10.3)

Type "help" for help.

postgres=# \c MARMER_DOKUMEN

You are now connected to database "MARMER_DOKUMEN" as user "postgres".

MARMER_DOKUMEN=# select pg_current_wal_lsn();

pg_current_wal_lsn

--------------------

47/EA0001B0

(1 row)

MARMER_DOKUMEN=#

atau bisa dilihat diluar dari query sql dengan cara.

[postgres@prod-postgres data]$ ps -ef | grep sender

postgres 12989 16695 0 15:03 pts/7 00:00:00 grep --color=auto sender

postgres 22484 18787 0 14:38 ? 00:00:00 postgres: wal sender process postgres IP_SLAVE(52988) streaming 47/EA0001B0

Slave ip

[postgres@prod-odgpostgres2 data]$ psql10 -p 8885

psql10 (10.3)

Type "help" for help.

postgres=# \c MARMER_DOKUMEN

You are now connected to database "MARMER_DOKUMEN" as user "postgres".

MARMER_DOKUMEN=# select pg_last_wal_receive_lsn();

pg_last_wal_receive_lsn

-------------------------

47/EA0001B0

(1 row)

MARMER_DOKUMEN=#

atau bisa dilihat diluar dari query sql dengan cara.

[postgres@ prod-odgpostgres2 data]$ ps -ef | grep receiver

postgres 30470 30155 0 15:29 pts/1 00:00:00 grep --color=auto receiver

postgres 40644 40616 0 15:03 ? 00:00:00 postgres: wal receiver process streaming 47/EA0001B0

[postgres@ prod-odgpostgres2 data]$ netstat -pltn

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -

tcp 0 0 0.0.0.0:8885 0.0.0.0:* LISTEN 40616/postgres

tcp 0 0 192.168.10.111:53 0.0.0.0:* LISTEN -

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN -

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN -

tcp 0 0 0.0.0.0:6432 0.0.0.0:* LISTEN -

tcp 0 0 0.0.0.0:5445 0.0.0.0:* LISTEN -

tcp6 0 0 :::111 :::* LISTEN -

tcp6 0 0 :::8885 :::* LISTEN 40616/postgres

tcp6 0 0 :::22 :::* LISTEN -

tcp6 0 0 ::1:631 :::* LISTEN -

tcp6 0 0 ::1:25 :::* LISTEN -

tcp6 0 0 :::9021 :::* LISTEN -

tcp6 0 0 :::9022 :::* LISTEN -

tcp6 0 0 :::6432 :::* LISTEN -

tcp6 0 0 :::5445 :::* LISTEN -

Master cek apakah ada replikasi ada yang terhubung, dapat dilakukan dengan perintah sebagai berikut.

[root@ prod-postgres ~]# su - postgres

Last login: Sen Apr 30 07:12:32 WIB 2018 on pts/15

[postgres@ prod-postgres ~]$psql10 -p 8884

psql10 (10.3)

Type "help" for help.

postgres=#\l

List of databases

---------------------------+----------+----------+-------------+-------------+-----------------------

(4 rows)

postgres=#\c MARMER_DOKUMEN

You are now connected to database "MARMER_DOKUMEN" as user "postgres".

MARMER_DOKUMEN=# \dt

List of relations

Schema | Name | Type | Owner

--------+-----------------------------+-------+----------

public | cv_marmer_2014 | table | postgres

public | ps_dokumen_file | table | postgres

public | belanja_dokumen_file | table | postgres

public | belanja_file | table | postgres

(4 rows)

MARMER_DOKUMEN=# select * from pg_stat_replication;

-------+----------+----------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+-------------+-------------+--

-----------+-------------+-----------+-----------+------------+---------------+------------

35284 | 10 | postgres | walreceiver | IP_SLAVE | prod-odgpostgres2 | 33606 | 2018-04-30 07:19:13.751707+07 | | streaming | 47/E90001B0 | 47/E90001B0 | 4

7/E90001B0 | 47/E90001B0 | | | | 0 | async

(1 row)

MARMER_DOKUMEN=# select count(1) from cv_marmer_2014;

count

--------

844366

(1 row)

MARMER_DOKUMEN=# select count(1) from ps_dokumen_file;

count

--------

908954

(1 row)

MARMER_DOKUMEN=# select count(1) from belanja_dokumen_file;

count

--------

239816

(1 row)

MARMER_DOKUMEN=# select count(1) from belanja_file;

count

---------

3949177

(1 row)

MARMER_DOKUMEN=#

TERIMA KASIH. :)

Database Administrator Postgres

Search This Blog

HOW MIGRASI MRM_PAM ORACLE 11g TO EDB AND BACKUP RESTORE POSTGRESQL VERSI 10

Comments

Post a Comment