Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Ora2pg

  • POST MIGRATION ajouter un booleen


    # changer type en booleen
    alter table "<your_table>"
    alter column "<your_column>"
    set data type boolean
    using case
        when "<your_column>" = '1' then true
        when "<your_column>" = '0' then false
        else null
    end;

     

    alter database <dbname> set search_path = "$user", public, <schema> ;

    # post migration pour un schema

    for tablename in $(psql -U postgres -h <hostname> -d <dbname> -t -c "select table_name as _table from information_schema.tables t where t.table_schema='<schema>' order by _table asc"); 
    do
     echo $tablename
     psql -U postgres -h <hostname> -d <dbname> -c "analyze ${tablename};"
     psql -U postgres -h <hostname> -d <dbname> -c "select count(*) ${tablename} from ${tablename};" >> rowcount.log
    done

    # post migration pour plusieurs schemas

    #!/bin/sh
    schema="('sche1','sche2','sche3','...')"

    namespace="."
    dbname="dbname"
    host="hostname"

     for tablename in $(psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc");
            do
            echo "psql -U postgres -h $host -d $dbname -t -c "select table_schema||'.'||table_name as _table from information_schema.tables t where t.table_schema in $schema order by _table asc""
            echo $tablename
              psql -U postgres -h ${host} -d ${dbname} -c "analyze ${tablename};"
              psql -U postgres -h ${host} -d ${dbname} -c "select count(*) as nr_rows, '${tablename}' as tablename from ${tablename};" >> logs/rowcount.log
     done





    #update sequ

    1-    Récupérer la séquence sur Oracle : 
    SELECT sequence_name, last_number FROM user_sequences; 
    2-    Mettre à jour la séquence sur Postgres : 
    SELECT setval('<nom_sequence',<last_number> , true); 

  • Ora2PG script creation project

    #!/bin/sh
    #-------------------------------------------------------------------------------
    #
    # Script to create an ora2pg project
    # Author : JJY
    #
    #-------------------------------------------------------------------------------
    #set -vx
    clear
    echo " "
    echo " Welcome in the program to create a migration project. "
    echo " "
    echo " Write the working environment (dev/qa/prd) :"
    echo " "
    read env
    echo " "
    echo " Write the name of your asset :"
    echo " "
    read base
    echo " "
    mkdir -p $ORA2PG_HOME/$env
    ora2pg --project_base $ORA2PG_HOME/$env --init_project $base -c $ORA2PG_CONF
    #cp $ORA2PG_HOME/config/commonstruct.conf $ORA2PG_HOME/$env/$base/config/metadata_ora2pg.conf
    #cp $ORA2PG_HOME/config/commondata.conf $ORA2PG_HOME/$env/$base/config/data_ora2pg.conf
    echo " "
    echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
    echo " Working environment created for $base: $ORA2PG_HOME/$env/$base"
    echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
    echo " "
    echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
    echo " The file  $ORA2PG_HOME/$env/$base/config/metadata_ora2pg.conf         "
    echo " and  $ORA2PG_HOME/$env/$base/config/data_ora2pg.conf have to be fixed."
    echo " !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"

    exit 0

  • [ora2pg script] Data and metadata export (multiple schemas)

    #!/bin/sh
    #-------------------------------------------------------------------------------
    #
    # Created by joguess the Oracle database admin, version 2022
    #
    #-------------------------------------------------------------------------------
    #data export
    EXPORT_TYPE="COPY"

    SCHEMA="TITI TOTO TATA"

    namespace="."

    for etype in $(echo $EXPORT_TYPE | tr " " "n")
    do
            for eschema in $(echo $SCHEMA | tr " " "n")
            do
            ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
            ltype=`echo $ltype | sed 's/y$/ie/'`

            echo "Running: ora2pg -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/data -c $namespace/config/ora2pg.conf"
            time ora2pg -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/data -c $namespace/config/ora2pg.conf -j 8 -J 3 -L 60000
            ret=`grep "Nothing found" $namespace/data/$ltype.sql 2> /dev/null`
            if [ ! -z "$ret" ]; then
                    rm $namespace/data/$ltype.sql
            fi
            done
    done


    echo
    echo
    echo "Extraction effectuee sous :"
    echo
    echo " $namespace/data"
    echo

    exit 0

    ____________________________________



    #!/bin/sh
    #-------------------------------------------------------------------------------
    #
    # Created by joguess the Oracle database admin, version 2022
    #
    #-------------------------------------------------------------------------------
    #metadata export
    EXPORT_TYPE="SEQUENCE TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION PROCEDURE PARTITION TYPE MVIEW SYNONYM"
    SCHEMA="TITI TOTO TATA"

    namespace="."

    for etype in $(echo $EXPORT_TYPE | tr " " "n")
    do
            for eschema in $(echo $SCHEMA | tr " " "n")
            do
            ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
            ltype=`echo $ltype | sed 's/y$/ie/'`
            echo "Running: ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt"
            time ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt

            echo "Running: ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt"
            time ora2pg -t SHOW_TABLE -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/tables_$eschema.txt

            echo "Running ora2pg -t SHOW_COLUMN -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/columns_$eschema.txt"
            time ora2pg -t SHOW_COLUMN -n $eschema -c $namespace/config/ora2pg.conf > $namespace/reports/columns_$eschema.txt

            echo "Running: ora2pg -t SHOW_REPORT -n $eschema -c $namespace/config/ora2pg.conf --estimate_cost --cost_unit_value 10 --dump_as_html > $namespace/reports/assessment/assessment_$eschema.html"
            time ora2pg -t SHOW_REPORT -n $eschema -c $namespace/config/ora2pg.conf --estimate_cost --cost_unit_value 10 --dump_as_html > $namespace/reports/assessment/assessment_$eschema.html


            echo "Running: ora2pg -p -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf"
            time ora2pg -p -t $etype -o $ltype_$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf

            ret=`grep "Nothing found" $namespace/schema/$ltype.sql 2> /dev/null`
            if [ ! -z "$ret" ]; then
                    rm $namespace/schema/$ltype.sql
            fi
            done
    done


    echo
    echo
    echo "Extraction effectuee sous :"
    echo
    echo " $namespace/schema"
    echo

    exit 0



  • Assessment ora2pg

    Assessment ora2pg : 
    ora2pg -t SHOW_REPORT -c ./config/ora2pg.conf --dump_as_html --cost_unit_value 5 --estimate_cost > ./reports/report.html
    ora2pg -t SHOW_TABLE -c ./config/ora2pg.conf > ./reports/tables.txt
    ora2pg -t SHOW_COLUMN -c ./config/ora2pg.conf > ./reports/columns.txt

    Compare source VS target

    ora2pg --type TEST --count_rows --conf confia2pg.conf

  • Tuning ora2pg

    On peut gagner du temps :

    1-Si on purge 

    2-Si on exclut les tables vides

    3-Si on calcule les stats 

    4-Si on ajoute de la mémoire chez Oracle

    5-Si on utilise les options de parallélisme ora2pg

    6-Si on modifie la valeur limite par défaut des données à exporter. 

     

  • [ora2pg] Assessment

    To be able to say “Yeah, we migrate to PostgreSQL”, we have to be confident by studying the technical aspects of it.

    For that, we assess the feasibility of the migration from oracle to postgres.

    The assessments consist in validating the :

    • Metadata structure and datatypes
    • Data consistency
    • Data integrity

     

    For this, we use the free tool called ora2Pg.

    Lire la suite

  • [PostgreSQL] Ora2pg Install

    yum install oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
    yum install oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
    yum install oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
    yum install oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
    yum -y install perl-DBI
    yum install gcc
    yum install glibc-devel
    yum install perl-ExtUtils-MakeMaker -y
    yum install perl-open.noarch -y


    export ORACLE_HOME=/usr/lib/oracle/12.2/client64
    export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib

    tar -xvzf ora2pg-23.0.tar.gz
    cd ora2pg-23.0/
    perl Makefile.PL
    make && make install

    tar -xvzf DBD-Oracle-1.83.tar.gz
    cd DBD-Oracle-1.83
    perl Makefile.PL
    make && make install