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

DEPLOY HA postgres

deploy_HA.txt

About JSON in RDBMS

2 links :

JSONB PostgreSQL: How To Store & Index JSON Data (scalegrid.io)

PostgreSQL JSON Tutorial (postgresqltutorial.com)

Désactiver le mode visual sur VI

J'ai remarqué un truc bien chiant sur les nouvelles VM linux : le clic droit de la souris ne peut plus coller...!

pour y remédier :

echo "set compatible" > ~/.vimrc
echo "set mouse-=a" >> ~/.vimrc

PG_BADGER

pgbadger -j 4 --outfile $HOME/rapport_complet.html -f stderr -p '%t:%r:%u@%d:[%p]:' /pglog/postgresql-2021-09-10_000000.log

Configuration application cloud postgres

USERNAME :: postgres://<instance>/<role>/user

PASSWORD ::postgres://<instance>/<role>/password

URL :: jdbc:postgresql://<instance>.<domain>:<port>/<dbname>

[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

Service HAS

Faire repartir le service HAS.

Nettoyage : /grid/oracle/product/19.0/bin/crsctl stop has -f

Faire repartir : /grid/oracle/product/19.0/bin/crsctl start has

 

Ora2PG now supports oracle_fdw to increase the data migration speed

 

 

source : https://www.migops.com/blog/2021/07/01/ora2pg-now-supports-oracle_fdw-to-increase-the-data-migration-speed/

[PostgreSQL] Voir les tables partitionnées

WITH RECURSIVE partition_info(relid, relname, relsize, relispartition, relkind)
AS (SELECT oid AS relid, relname, pg_relation_size(oid) AS relsize, relispartition, relkind
      FROM pg_catalog.pg_class
     WHERE relkind = 'p' AND relname = 'mof_ple_part' -- Pour une table donnee.
     UNION ALL
    SELECT c.oid AS relid, c.relname AS relname, pg_relation_size(c.oid) AS relsize,
           c.relispartition AS relispartition, c.relkind AS relkind
      FROM partition_info AS p, pg_catalog.pg_inherits AS i, pg_catalog.pg_class AS c
     WHERE p.relid = i.inhparent AND c.oid = i.inhrelid AND c.relispartition)

SELECT * FROM partition_info;
 

select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
order by schema_name, table_name;