Oracle to Postgres

 


Round 1:

Created metadata for tables, sequences, packages

Round 2:

Created indexes

Round 3:

freeze column name to freezes

to_date function updated

GEOMETRY datatype , installed POSTGIS package to support this datatype

Round 4:

created triggers

created Functions

Migration:

bash-4.2$ pwd
/usr/edb/migrationtoolkit/etc
bash-4.2$ cat toolkit.properties
SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl
SRC_DB_USER=hr
SRC_DB_PASSWORD=hr

TARGET_DB_URL=jdbc:edb://postoffline:5444/dev
TARGET_DB_USER=enterprisedb
TARGET_DB_PASSWORD=password
bash-4.2$ 

-bash-4.2$ pwd
/usr/edb/migrationtoolkit/bin
-bash-4.2$ ls -lrt
total 724
-rwxr-xr-x. 1 root root   2439 Dec 11 13:29 runJavaApplication.sh
-rwxr-xr-x. 1 root root    544 Dec 11 13:33 runMTK.sh
-rwxr-xr-x. 1 root root 729258 Dec 11 13:33 edb-migrationtoolkit.jar
-bash-4.2$ 


./runMTK.sh HR

OUTPUT:

Running EnterpriseDB Migration Toolkit (Build 55.7.0) ...

Source database connectivity info...

conn =jdbc:oracle:thin:@oel712c:1521/ORCLPDB

user =HR

password=******

Target database connectivity info...

conn =jdbc:edb://postoffline:5444/dev

user =enterprisedb

password=******

Connecting with source Oracle database server...

Connected to Oracle, version 'Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production'

Connecting with target EDB Postgres database server...

Connected to EnterpriseDB, version '14.11.0'

Importing redwood schema HR...

Table List: 'PAY_RUN_BALANCES'

Evaluating tables for parallel data load...

Evaluating table HR.PAY_RUN_BALANCES for parallel data load...

The table hr.PAY_RUN_BALANCES has no primary or unique key, table-level parallel loading will be skipped.

Initializing Data Loader 1...

Loading Table Data in 8 MB batches...

Dropping constraints & indexes on hr.pay_run_balances before data load...

Dropping Triggers on hr.pay_run_balances before data load...

Truncating table hr.pay_run_balances before data load...

Loading Table: hr.pay_run_balances ...

[HR.PAY_RUN_BALANCES] Migrated 75851 rows.

[HR.PAY_RUN_BALANCES] Migrated 151368 rows.

[HR.PAY_RUN_BALANCES] Migrated 227030 rows.

[HR.PAY_RUN_BALANCES] Migrated 302853 rows.

[HR.PAY_RUN_BALANCES] Migrated 378595 rows.

[HR.PAY_RUN_BALANCES] Migrated 454219 rows.

[HR.PAY_RUN_BALANCES] Migrated 529905 rows.

[HR.PAY_RUN_BALANCES] Migrated 605590 rows.

[HR.PAY_RUN_BALANCES] Migrated 681322 rows.

[HR.PAY_RUN_BALANCES] Migrated 757138 rows.

[HR.PAY_RUN_BALANCES] Migrated 832724 rows.

[HR.PAY_RUN_BALANCES] Migrated 908439 rows.

[HR.PAY_RUN_BALANCES] Migrated 983977 rows.

[HR.PAY_RUN_BALANCES] Migrated 1059705 rows.

[HR.PAY_RUN_BALANCES] Migrated 1135445 rows.

[HR.PAY_RUN_BALANCES] Migrated 1211152 rows.

[HR.PAY_RUN_BALANCES] Migrated 1286760 rows.

[HR.PAY_RUN_BALANCES] Migrated 1362400 rows.

[HR.PAY_RUN_BALANCES] Migrated 1438037 rows.

[HR.PAY_RUN_BALANCES] Migrated 1513632 rows.

[HR.PAY_RUN_BALANCES] Migrated 1589191 rows.

[HR.PAY_RUN_BALANCES] Migrated 1664686 rows.

[HR.PAY_RUN_BALANCES] Migrated 1740230 rows.

[HR.PAY_RUN_BALANCES] Migrated 1815825 rows.

[HR.PAY_RUN_BALANCES] Migrated 1891533 rows.

[HR.PAY_RUN_BALANCES] Migrated 1967750 rows.

[HR.PAY_RUN_BALANCES] Migrated 2044034 rows.

[HR.PAY_RUN_BALANCES] Migrated 2120322 rows.

[HR.PAY_RUN_BALANCES] Migrated 2196156 rows.

[HR.PAY_RUN_BALANCES] Migrated 2272376 rows.

[HR.PAY_RUN_BALANCES] Migrated 2348541 rows.

[HR.PAY_RUN_BALANCES] Migrated 2424830 rows.

[HR.PAY_RUN_BALANCES] Migrated 2501086 rows.

[HR.PAY_RUN_BALANCES] Migrated 2577325 rows.

[HR.PAY_RUN_BALANCES] Migrated 2653446 rows.

[HR.PAY_RUN_BALANCES] Migrated 2730613 rows.

[HR.PAY_RUN_BALANCES] Migrated 2806649 rows.

[HR.PAY_RUN_BALANCES] Migrated 2883178 rows.

[HR.PAY_RUN_BALANCES] Migrated 2959582 rows.

[HR.PAY_RUN_BALANCES] Migrated 3036078 rows.

[HR.PAY_RUN_BALANCES] Migrated 3112406 rows.

[HR.PAY_RUN_BALANCES] Migrated 3188613 rows.

[HR.PAY_RUN_BALANCES] Migrated 3264546 rows.

[HR.PAY_RUN_BALANCES] Migrated 3339594 rows.

[HR.PAY_RUN_BALANCES] Migrated 3414615 rows.

[HR.PAY_RUN_BALANCES] Migrated 3489549 rows.

[HR.PAY_RUN_BALANCES] Migrated 3564514 rows.

[HR.PAY_RUN_BALANCES] Migrated 3639507 rows.

[HR.PAY_RUN_BALANCES] Migrated 3714216 rows.

[HR.PAY_RUN_BALANCES] Migrated 3789010 rows.

[HR.PAY_RUN_BALANCES] Migrated 3864196 rows.

[HR.PAY_RUN_BALANCES] Migrated 3939431 rows.

[HR.PAY_RUN_BALANCES] Migrated 4014465 rows.

[HR.PAY_RUN_BALANCES] Migrated 4089459 rows.

[HR.PAY_RUN_BALANCES] Migrated 4164453 rows.

[HR.PAY_RUN_BALANCES] Migrated 4239519 rows.


[HR.PAY_RUN_BALANCES] Migrated 4911808 rows.

[HR.PAY_RUN_BALANCES] Migrated 4985564 rows.

[HR.PAY_RUN_BALANCES] Migrated 5059277 rows.

[HR.PAY_RUN_BALANCES] Migrated 5132915 rows.

[HR.PAY_RUN_BALANCES] Migrated 5206655 rows.

[HR.PAY_RUN_BALANCES] Migrated 5279822 rows.

[HR.PAY_RUN_BALANCES] Migrated 5352887 rows.

[HR.PAY_RUN_BALANCES] Migrated 5425996 rows.

[HR.PAY_RUN_BALANCES] Migrated 5499169 rows.

[HR.PAY_RUN_BALANCES] Migrated 5572212 rows.

[HR.PAY_RUN_BALANCES] Migrated 5645359 rows.

[HR.PAY_RUN_BALANCES] Migrated 5718444 rows.

[HR.PAY_RUN_BALANCES] Migrated 5775868 rows.

[hr.PAY_RUN_BALANCES] Table Data Load Summary: Total Time(s): 192.725 Total Rows: 5775868 Total Size(MB): 614.292

Creating constraints, indexes and triggers after data load...

Data Load Summary: Total Time (sec): 192.725 Total Rows: 5775868 Total Size(MB): 614.292


Schema HR imported successfully.



Migration process completed successfully.

Total Elapsed Migration Time (sec): 206.602


Migration logs have been saved to /var/lib/edb/.enterprisedb/migration-toolkit/logs


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

Tables: 1 out of 1


Total objects: 1

Successful count: 1

Failed count: 0

Invalid count: 0


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

-bash-4.2$ 

Comments

Post a Comment

Popular Posts

AWR Report Analysis

Image
AWR report analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main things we general look to figure out issue. 1. 2.  3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
Read more

Goldengate 19c Installation with issue resolution

Image
  Basic Installation of GoldenGate 19c in OEL6. Tested with below requirements: Step1: Vmware requirements:   For installing OEL6.8 and Oracle DB 12c  installation, please refer URL https://venuoracledba.blogspot.com/2020/08/oracle-12c-2-node-rac-setup-in-vmware.html     Step2: After OEL6.8 installation finished. We will use /opt for goldengate installation.   Step3: unzip GG software in folder. Here unzipped in gg_software folder.     Folder fbo_ggs_Linux_x64_shiphome will create. Step4:  Enable GUI by issuing x host + command in root user. and keep open this root terminal.   Step5:  Enable GUI by issuing x host + command in oracle user.   Move to software path   execute ./runInstaller and select database version. Selected 12c version for my environment. and click Next     provide GG home, oracle home and port number and click Next.     After verifying paths and information, Click Install       Click Close.     Step6: run ggsci from GG_HOME to check GG status. Got libnnz12.so librar
Read more

Oracle 12c 2-node RAC setup in VMware Player 15 -- Part01

Image
Tried to install Oracle 12c 2-node RAC for testing purpose but due to low Hardware configuration, ended up with Single Node RAC. I got lot of issues during Installation and tried to resolve them with my best knowledge. Please find below installation steps and troubleshooting. Main issues started from Step90. you can go through quickly. Requirements Most of the times, bridge option in VMWARE will not connect with host machine. I tried below option to use internet connection from host machine. We need to cross verify vmnet8 is NAT or not. Installation Steps Step1 launch VMware and click on New Virtual Machine Step2   select OEL iso Image Step3 Provide New Virtual Machine Name Step4 Provide HDD size and select single virtual disk. Step5  Select host setting for monitor and Network as NAT Step6 New Virtual Machine is ready for launch. Step7 OEL Installation starts automatically. Press Enter Step8 Skip Disk Checking
Read more

Execution_Explain Plan

Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan. 1. EXECUTION_PLAN: select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop, object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>'; or select * from table(dbms_xplan.display_awr('<SQL_ID>')); EXPLAIN_PLAN: explain plan for <SQL_QUERY>; select * from table(dbms_xplan.display);
Read more

Active Session History Analysis

ASH  analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main events we general look to figure out issue. RAC / Cloud RDS: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Redo Generation: 14. 15.
Read more

General Queries

                                                                              Tablespace growth Size We can use view to check tablespace size greater than 80% RAC / NON-RAC / Cloud RDS: SELECT tbm.TABLESPACE_NAME,   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) as USED_SPACE_GB,   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_SIZE_GB,   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_FREE_SIZE_GB,   round(tbm.USED_PERCENT) as PERCENT_USEDFROM dba_tablespace_usage_metrics tbm      join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME where round(tbm.USED_PERCENT) > 80;"; SQL monitoring We can use to check current executing SQL's. RAC / Cloud RDS: select inst_id,status,round(elapsed_time/1000000,2) as duration_secs,sql_id,sql_plan_hash_value,username, px_maxdop as degree_of_parallelism, px_maxdop_instances as instances_used, px_is_cross_instance as cross_instance,user_io_wait_time,clust
Read more

Fragmentation & Re-organisation

Total  Savings: set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report column value new_val blksize select value from v$parameter where name = 'db_block_size' / select file_name, ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) smallest, ceil( blocks*&amp;&amp;blksize/1024/1024) currsize, ceil( blocks*&amp;&amp;blksize/1024/1024) - ceil( (nvl(hwm,1)*&amp;&amp;blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) order by savings desc / HWM query for all datafiles: col data_file_name for a50 col tablespace_name for a20 set lines 300 select tablespace_name, file_id, file_name DATA_FILE_NAME, Al
Read more

Postgresql installation offline using NGINX

  [root@localhost yumdownloaddir]# sudo  yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm Loaded plugins: langpacks, ulninfo epel-release-latest-7.noarch.rpm                                                                                                                                                            |  15 kB  00:00:00      Examining /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm: epel-release-7-14.noarch Marking /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package epel-release.noarch 0:7-14 will be installed --> Finished Dependency Resolution https://username@gmail.com:password@yum.enterprisedb.com/edb/redhat/rhel-7Server-x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found Trying other mirror. enterprisedb-enterprise/x86_64/signature                                                                                     
Read more

OEM patches & vulnerabilities

Image
  Tools: Agent: agentpatcher OMS: omspatcher coherence/weblogic: optach 1. Agent JDK update. please refer the below oracle reference page Supported Java SE Downloads on MOS (oracle.com) 2. Removing log4j-core-2.8.2.jar validate jar file exist cd $OEM_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib $OEM_BASE/agent_13.5.0.0.0/oracle_common/jdk/bin/jar tvf log4j-core-2.8.2.jar | grep JndiLookup.class output 2974 Sun Apr 02 15:24:22 AEST 2017 org/apache/logging/log4j/core/lookup/JndiLookup.class stop OMS  $OEM_BASE/middleware/bin/emctl status oms -details  $OEM_BASE/middleware/bin/emctl stop oms -all remove log4j zip -q -d log4j-core-2.8.2.jar org/apache/logging/log4j/core/lookup/JndiLookup.class validate jar file exist $OEM_BASE/agent_13.5.0.0.0/oracle_common/jdk/bin/jar tvf log4j-core-2.8.2.jar | grep JndiLookup.class start OMS  $OEM_BASE/middleware/bin/emctl status oms -details  $OEM_BASE/middleware/
Read more